{"id":630,"date":"2009-09-08T09:00:40","date_gmt":"2009-09-08T17:00:40","guid":{"rendered":"http:\/\/www.codebelay.com\/blog\/?p=630"},"modified":"2009-09-08T09:20:18","modified_gmt":"2009-09-08T17:20:18","slug":"benchmarking-insert-on-drizzle-and-mysql","status":"publish","type":"post","link":"https:\/\/www.codebelay.com\/blog\/2009\/09\/08\/benchmarking-insert-on-drizzle-and-mysql\/","title":{"rendered":"Benchmarking Inserts on Drizzle and MySQL"},"content":{"rendered":"

I’m not comparing apples to apples yet… but out of the box, drizzle<\/a> does inserts faster than MySQL<\/a> using the same table type, InnoDB<\/a>.<\/p>\n

Here’s what I’m comparing:
\ndrizzle r1126 configured with defaults, and
\nMySQL 5.1.38 configured with <\/p>\n

\n
\r\n.\/configure --prefix=\/usr\/local\/mysql --with-extra-charsets=complex \\\r\n--enable-thread-safe-client --enable-local-infile --enable-shared \\\r\n--with-plugins=partition,innobase\r\n<\/pre>\n<\/div>\n
which is really nothing complicated.<\/p>\n
SQL query caching is turned off on both database servers. Both are using the InnoDB engine plug-in.<\/p>\n
I’m running these benchmarks on a MacBook Pro 2.4 GHz Intel Core 2 Duo with 2GB 1067 MHz DDR3 RAM.<\/p>\n
I wrote benchmarking software about 2 years ago to test partitions<\/a> but I’ve since abstracted the code to be database agnostic.<\/p>\n
You can get the benchmarking code at Github<\/a>.<\/p>\n
At the command-line, you type:<\/p>\n
\nphp build_tables.php 10000 4 drizzle\n<\/div>\n
where 10000 is the number of rows allocated total, and 4 is the number of partitions for those rows.<\/p>\n
You can type the same thing for mysql:<\/p>\n
\nphp build_tables.php 10000 4 mysql\n<\/div>\n
and get interesting results.<\/p>\n
Here’s what I got:<\/p>\n
MySQL<\/h3>\n
\r\nbash-3.2$ php build_tables.php 10000 4 mysql\r\nElapsed time between Start and Test_Code_Partition: 13.856538\r\nlast table for php partition: users_03\r\nElapsed time between No_Partition and Code_Partition: 14.740206\r\n-------------------------------------------------------------\r\nmarker           time index            ex time         perct   \r\n-------------------------------------------------------------\r\nStart            1252376759.26094100   -                0.00%\r\n-------------------------------------------------------------\r\nNo_Partition     1252376773.11747900   13.856538       48.45%\r\n-------------------------------------------------------------\r\nCode_Partition   1252376787.85768500   14.740206       51.54%\r\n-------------------------------------------------------------\r\nStop             1252376787.85815000   0.000465         0.00%\r\n-------------------------------------------------------------\r\ntotal            -                     28.597209      100.00%\r\n-------------------------------------------------------------\r\n20000 rows inserted...\r\n<\/pre>\n
drizzle<\/h3>\n
\r\nbash-3.2$ php build_tables.php 10000 4 drizzle\r\nElapsed time between Start and Test_Code_Partition: 7.502141\r\nlast table for php partition: users_03\r\nElapsed time between No_Partition and Code_Partition: 7.072367\r\n-------------------------------------------------------------\r\nmarker           time index            ex time         perct   \r\n-------------------------------------------------------------\r\nStart            1252376733.68141500   -                0.00%\r\n-------------------------------------------------------------\r\nNo_Partition     1252376741.18355600   7.502141        51.47%\r\n-------------------------------------------------------------\r\nCode_Partition   1252376748.25592300   7.072367        48.52%\r\n-------------------------------------------------------------\r\nStop             1252376748.25627400   0.000351         0.00%\r\n-------------------------------------------------------------\r\ntotal            -                     14.574859      100.00%\r\n-------------------------------------------------------------\r\n20000 rows inserted...\r\n<\/pre>\n
MySQL: 699 inserts per second
\ndrizzle: 1372 inserts per second
\nAs far as inserts go, drizzle is about 2 times faster out of the box than MySQL.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[312,288,33],"tags":[313],"class_list":["post-630","post","type-post","status-publish","format-standard","hentry","category-databases","category-scalability-hacking","category-webapps","tag-mysql-drizzle-benchmarking"],"_links":{"self":[{"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/posts\/630","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/comments?post=630"}],"version-history":[{"count":0,"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/posts\/630\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/media?parent=630"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/categories?post=630"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/tags?post=630"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}