Monthly ArchiveSeptember 2009
Databases & WebApps & scalability hacking 08 Sep 2009 09:00 am
Benchmarking Inserts on Drizzle and MySQL
I’m not comparing apples to apples yet… but out of the box, drizzle does inserts faster than MySQL using the same table type, InnoDB.
Here’s what I’m comparing:
drizzle r1126 configured with defaults, and
MySQL 5.1.38 configured with
./configure --prefix=/usr/local/mysql --with-extra-charsets=complex \ --enable-thread-safe-client --enable-local-infile --enable-shared \ --with-plugins=partition,innobase
which is really nothing complicated.
SQL query caching is turned off on both database servers. Both are using the InnoDB engine plug-in.
I’m running these benchmarks on a MacBook Pro 2.4 GHz Intel Core 2 Duo with 2GB 1067 MHz DDR3 RAM.
I wrote benchmarking software about 2 years ago to test partitions but I’ve since abstracted the code to be database agnostic.
You can get the benchmarking code at Github.
At the command-line, you type:
php build_tables.php 10000 4 drizzle
where 10000 is the number of rows allocated total, and 4 is the number of partitions for those rows.
You can type the same thing for mysql:
php build_tables.php 10000 4 mysql
and get interesting results.
Here’s what I got:
MySQL
bash-3.2$ php build_tables.php 10000 4 mysql Elapsed time between Start and Test_Code_Partition: 13.856538 last table for php partition: users_03 Elapsed time between No_Partition and Code_Partition: 14.740206 ------------------------------------------------------------- marker time index ex time perct ------------------------------------------------------------- Start 1252376759.26094100 - 0.00% ------------------------------------------------------------- No_Partition 1252376773.11747900 13.856538 48.45% ------------------------------------------------------------- Code_Partition 1252376787.85768500 14.740206 51.54% ------------------------------------------------------------- Stop 1252376787.85815000 0.000465 0.00% ------------------------------------------------------------- total - 28.597209 100.00% ------------------------------------------------------------- 20000 rows inserted...
drizzle
bash-3.2$ php build_tables.php 10000 4 drizzle Elapsed time between Start and Test_Code_Partition: 7.502141 last table for php partition: users_03 Elapsed time between No_Partition and Code_Partition: 7.072367 ------------------------------------------------------------- marker time index ex time perct ------------------------------------------------------------- Start 1252376733.68141500 - 0.00% ------------------------------------------------------------- No_Partition 1252376741.18355600 7.502141 51.47% ------------------------------------------------------------- Code_Partition 1252376748.25592300 7.072367 48.52% ------------------------------------------------------------- Stop 1252376748.25627400 0.000351 0.00% ------------------------------------------------------------- total - 14.574859 100.00% ------------------------------------------------------------- 20000 rows inserted...
MySQL: 699 inserts per second
drizzle: 1372 inserts per second
As far as inserts go, drizzle is about 2 times faster out of the box than MySQL.
TechBiz & WebApps 07 Sep 2009 01:02 pm
If You Miss tr.im use j.mp
I’ve been using j.mp for two weeks now and it’s filled the void that tr.im left after going out of business.
Long Live j.mp.
