My understanding is that a new RDS instance will "page in" blocks from the snapshot on an as-needed basis, as is described for EC2 volumes here.

This is currently causing me pain: I'm running a large query on a new test instance; it should take 10-15 minutes to run, but it's been going for the last hour. This instance has 1,000GB storage, so 3,000 IOPS, but I'm seeing < 100 IOPS combined in the console (and occasionally < 20 read IOPS).

Normally I'd use mysqldump to take a complete database backup and send it to /dev/null -- but that takes 12-18 hours. I have in the past done table-scan queries over multiple tables at a time, in the hope that these IOs will happen in parallel.

Does anyone know of a better way to warm up the volume?

3 Answers 3


As it appears there's no simpler approach than what I've already been using, I decided to evaluate these approaches.

For a test environment, I spun up one instance per approach: an r3.large (2 VCPU, 15 GB RAM), using the same snapshot for each. These instances have 1,000 GB of disk, so should be able to maintain 3,000 IOPS.

The database as a whole contains several hundred tables, ranging from a few hundred rows to several hundred million rows (a couple of tables that are used primarily for logging, but may be involved in some reporting queries).

I picked two tables for evaluation: our "users" table, which contains 20 million rows and is excessively wide, and a "linkage" table that also contains 20MM rows but only two columns.

After loading, I ran two queries against the users table: one that forced a table-scan by summing a non-indexed numeric field, and one that performed an aggregate operation against an indexed column (which should traverse the entire index). I didn't run queries against the linkage table because it didn't seem to provide more information.

All timings are in the format H:MM:SS (hours:minutes:seconds), and are from a single run. I also tracked read and write IOPS, using Cloudwatch metrics (generally averaged over 5-15 minutes).

Our database uses MySQL, but I believe the general approaches are relevant for any DBMS.


Dumping the table to /dev/null

mysqldump CONNECTION_OPTIONS --compress DATABASE TABLES > /dev/null

The mysqldump program is used for backing up databases or individual tables. It retrieves all table data and writes it to StdOut, along with DDL to recreate the table and its indexes.

Since I don't care about actually backing up the table, I redirect the output to /dev/null. Since I don't want to be held up by the network, I use the --compress option. Even so, I ran on a same-AZ EC2 instance to keep all network traffic within the Amazon data center.

The main drawback of this approach is that it's not going to touch the index blocks.

                          Users      Linkage  
| time to touch blocks | 00:53:38 | 00:03:02 |
| read IOPS            | < 150    | 150+     |
| table-scan           | 00:01:29 |          |
| index aggregate      | 00:00:15 |          |

Forced table-scan query

Much like the test query, this approach was a simple select that aggregated data from a non-indexed field. I chose a different field for the "touch" query versus the "test" query.

As with the dump operation, this accessed only table data blocks. I could extend it to access index blocks via some form of index aggregate, but I think that's less relevant for my needs.

                          Users      Linkage  
| time to touch blocks | 00:59:12 | 00:03:31 |
| read IOPS            | 150      | 150      |
| table-scan           | 00:02:04 |          |
| index aggregate      | 00:00:19 |          |


The OPTIMIZE TABLE command will rebuild an InnoDB table and index, freeing space in the process. It is specific to MySQL, but I would consider the Postgres VACUUM command to be similar, and I'm sure there are equivalent commands for other database systems.

This is possibly an unfair test for our large table, as it is subject to many updates and has no doubt never been optimized in its multi-year life. If we regularly optimized, perhaps the numbers would be lower.

                          Users      Linkage  
| time to touch blocks | 02:01:36 | 00:03:44 |
| read IOPS            | 100      | 150      |
| write IOPS           | 500+     | 1000+    |
| table-scan           | 00:00:05 |          |
| index aggregate      | 00:00:01 |          |

You'll note that I added a line for write IOPS. Also, those query times are not misprints: they're more than an order of magnitude faster than the others. I suspect this is because the blocks were cached in memory (I probably should have rebooted the instance between touching blocks and executing queries).


OPTIMIZE is dramatically slower for the large table and uses too many write IOPS. However, if I was on Postgres then VACUUM might a valid choice, assuming that the source database was regularly vacuumed.

The difference between an all-rows select and mysqldump was minor, and possibly due to network or VM load. However, mysqldump is far easier to execute, because the all-rows select requires some thought to pick an appropriate query.

After running these tests I brought up a new instance and started 10 concurrent mysqldump sessions (randomly dividing tables between them). Some observations:

  • Most sessions were complete within 6 hours. There were a couple that ran to 7, and one (with several large tables) that was > 8.
  • CPU remained at roughly 40% for the entire time. I suspect this was entirely due to compression.
  • Read IOPS slowly increased from ~400 to ~600 (using a 15-minute averaging period), with a spike to > 1000 near the end.

As I've given some more thought to the issue, I've come to believe that my pain is due primarily to the fact that I'm using this instance for testing reporting loads. If it were an OLTP instance, I suspect that I could transition it into service with minimal pain (albeit slower performance). However, the same pain will affect read replicas, perhaps moreso because you'd only bring up another replica in response to heavy system load.

Long-term, I can only hope that Amazon will see fit to add a "fast init" operation that touches the volume's blocks in parallel.


Unfortunately the only way to force blocks to be paged-in is to fetch them, and you've already hit on multiple ways to do that.

  • Right. I was hoping, however, for someone to provide a better approach to touching all the blocks. For example, while mysqldump is easy, it's sequential and probably doesn't touch index blocks. The table-scan is parallel, but maybe there's a better approach that's less labor-intensive (starting up a new session for each scan). BTW, thanks for fixing the typos; I thought I had them all (auto-correct FTL).
    – kdgregory
    May 17, 2017 at 16:56
  • There's no secret weapon, this is essentially true. I once wrote a utility that would auto-generate queries to full-scan each table and full-scan each index, and if I remember right, it even parsed the output of EXPLAIN, to make sure each query it had written actually triggered the intended behavior... then ran them with GNU parallel and the CLI... I haven't needed it since I wrote it, so I don't actually know what its potential is to improve the situation. May 18, 2017 at 10:03

I've been using a very simple script that will generate a query which I'm pretty sure should touch all of the data and index blocks on your database:


for TBL in $($MYSQL_CMD -e "show tables;" | sort); do
        while IFS= read -r IDX_DATA; do
            Key_name=$(echo $IDX_DATA | cut -d " " -f3)
            Seq_in_index=$(echo $IDX_DATA | cut -d " " -f4)
            Column_name=$(echo $IDX_DATA | cut -d " " -f5)

            if [[ $Seq_in_index -ne 1 ]]; then

            printf 'SELECT COUNT(*) FROM `%s`.`%s` WHERE CRC32(`%s`) = 1;\n' "$SCHEMA_NAME" "$TBL" "$Column_name"
        done <<< "$ALL_IDX"

This will generate queries like so:

SELECT COUNT(*) FROM `db`.`tb1` WHERE CRC32(`id`) = 1;
SELECT COUNT(*) FROM `db`.`tb2` WHERE CRC32(`col1`) = 1;
SELECT COUNT(*) FROM `db`.`tb2` WHERE CRC32(`col2`) = 1;

To speed up the process, you can run the queries in parallel. This is faster than OPTIMIZE because you don't incur writes in the process and should touch all blocks, contrary to the approaches with mysqldump that only touch data blocks.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .