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?

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 '17 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. – Michael - sqlbot May 18 '17 at 10:03
up vote 1 down vote accepted

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.

Approaches

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 |          |

OPTIMIZE

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).

Summary

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.

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.