Warning: this material is outdated, a set of empirical tests was under works but never saw the light

It happens, once that one does something wrong without knowing where he is making a mistake..
This is what happened yesterday when I used mysqlslap test bench on an Amazon RDS machine.

The problem is that mysqlslap does not measure query performance, rather connection performance. It is heavily influenced by latency and networking speed. Now, consider that US <-> EU means around 100ms of latency. You multiply it by the amount of packets needed to transfer the test data-set and here you are: mysqlslap thinks inserts take seconds in place of milliseconds.

Thus, I created an AMI on the same AWS zone US-East-1.

Test machine: small instance AMI (1.7Gb RAM), mysql client 5.1.40

RDS machine: 1 small RDS machine (1.7Gb RAM)

I run the tests with a modified DB Parameters Group in order to speed-up BinLog writers (anyway the test has a small data-set, improvements are marginal):

rds-modify-db-parameter-group mydbpg --parameters "name=binlog_cache_size, value=1288490188, method=immediate
rds-modify-db-parameter-group mydbpg --parameters "name=max_binlog_cache_size, value=268435456, method=pending-reboot"
rds-modify-db-parameter-group mydbpg --parameters "name=innodb_buffer_pool_size, value=1210612736, method=pending-reboot"
rds-modify-db-parameter-group mydbpg --parameters "name=innodb_log_buffer_size, value=16777216, method=pending-reboot"

The test generates a table of 20 char columns and 20 int columns. Here is the screendump:

# mysqlslap -h kaukana.cqwri4lh0op8.us-east-1.rds.amazonaws.com -u XXXXX -p --auto-generate-sql --concurrency=1,10,20 --number-of-queries=40 --number-char-cols=20 --number-int-cols=20 --commit=1 -vv
Building Create Statements for Auto
Building Query Statements for Auto
Parsing engines to use.
Enter password:
Starting Concurrency Test
Loading Pre-data
Generating primary key list
Generating stats
Benchmark
 Average number of seconds to run all queries: 0.745 seconds
 Minimum number of seconds to run all queries: 0.745 seconds
 Maximum number of seconds to run all queries: 0.745 seconds
 Number of clients running queries: 1
 Average number of queries per client: 40

Loading Pre-data
Generating primary key list
Generating stats
Benchmark
 Average number of seconds to run all queries: 0.192 seconds
 Minimum number of seconds to run all queries: 0.192 seconds
 Maximum number of seconds to run all queries: 0.192 seconds
 Number of clients running queries: 10
 Average number of queries per client: 4

Loading Pre-data
Generating primary key list
Generating stats
Benchmark
 Average number of seconds to run all queries: 0.278 seconds
 Minimum number of seconds to run all queries: 0.278 seconds
 Maximum number of seconds to run all queries: 0.278 seconds
 Number of clients running queries: 20
 Average number of queries per client: 2

The overhead introduced by network delays between the two machines is negligible (Amazon uses a Gbit-class infrastructure internally). This is probably the best result you can get anyway: Amazon EC2 reroutes ” automagically” public DNS names to resolve to internal addresses, in this way packets do not leave AWS infrastructure and you don’t pay for traffic!!

So, how good is this? You can compare it with a virtual machine I use in my lab cofigured as following:

• single virtual core
• 1.5Ghz
• 1GB ram
• SATA Raid-0 disks
• Gbit network

Tuja:~ gabriele$ mysqlslap -h XX.XX.XX.XX -u XXXX -p --auto-generate-sql --concurrency=1,10,20 --number-of-queries=40 --number-char-cols=20 --number-int-cols=20 --commit=1 -vv
Building Create Statements for Auto
Building Query Statements for Auto
Parsing engines to use.
Enter password:
Starting Concurrency Test
Loading Pre-data
Generating primary key list
Generating stats
Benchmark
 Average number of seconds to run all queries: 0.499 seconds
 Minimum number of seconds to run all queries: 0.499 seconds
 Maximum number of seconds to run all queries: 0.499 seconds
 Number of clients running queries: 1
 Average number of queries per client: 40

Loading Pre-data
Generating primary key list
Generating stats
Benchmark
 Average number of seconds to run all queries: 0.258 seconds
 Minimum number of seconds to run all queries: 0.258 seconds
 Maximum number of seconds to run all queries: 0.258 seconds
 Number of clients running queries: 10
 Average number of queries per client: 4

Loading Pre-data
Generating primary key list
Generating stats
Benchmark
 Average number of seconds to run all queries: 0.297 seconds
 Minimum number of seconds to run all queries: 0.297 seconds
 Maximum number of seconds to run all queries: 0.297 seconds
 Number of clients running queries: 20
 Average number of queries per client: 2

Seems to me that RDS team decided not to stripe their disks, I will need a bigger data-set to prove it. More tests and conclusions to come in a post in the near future.