{"id":761,"date":"2006-11-22T22:02:06","date_gmt":"2006-11-22T12:02:06","guid":{"rendered":"http:\/\/www.flamingspork.com\/blog\/2006\/11\/22\/create-insert-select-drop-benchmark\/"},"modified":"2006-11-22T22:02:06","modified_gmt":"2006-11-22T12:02:06","slug":"create-insert-select-drop-benchmark","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2006\/11\/22\/create-insert-select-drop-benchmark\/","title":{"rendered":"CREATE, INSERT, SELECT, DROP benchmark"},"content":{"rendered":"<p>Inspired by <a href=\"http:\/\/www.mysqlperformanceblog.com\/2006\/11\/21\/opening-tables-scalability\/\">PeterZ&#8217;s Opening Tables scalability<\/a> post, I decided to try a little benchmark. This benchmark involved the following:<\/p>\n<ul>\n<li>Create 50,000 tables<\/li>\n<\/ul>\n<ul>\n<li>CREATE TABLE t{$i} (i int primary key)<\/li>\n<\/ul>\n<li>Insert one row into each table<\/li>\n<li>select * from each table<\/li>\n<li>drop each table<\/li>\n<p>I wanted to test file system impact on this benchmark. So, I created a new LVM volume, 10GB in size. I extracted a &#8216;make bin-dist&#8217; of a recent MySQL 5.1 tree, did a &#8220;mysql-test-run.pl &#8211;start-and-exit&#8221; and ran my script, timing real time with time.<\/p>\n<p>For a default ext3 file system creating MyISAM tables, the test took 15min 8sec.<\/p>\n<p>For a default xfs file sytem creating MyISAM tables, the test took 7min 20sec.<\/p>\n<p>For an XFS file system with a 100MB Version 2 log creating MyISAM tables, the test took 7min 32sec &#8211; which is within repeatability of the default XFS file system. So log size and version made no real difference.<\/p>\n<p>For a default reiserfs (v3) file system creating MyISAM tables, the test took 9m 44sec.<\/p>\n<p>For a ext3 file system with the dir_index option enabled creating MyISAM tables, the test took 14min 21sec.<\/p>\n<p>For an approximate measure of the CREATE performance&#8230;. ext3 and reiserfs averaged about 100 tables\/second (although after the 20,000 mark, reiserfs seemed to speed up a little). XFS\u00c2\u00a0 averaged about 333 tables\/second. I credit this to the check for if the files exist being performed by a b-tree lookup in XFS once the directory reached a certain size.<\/p>\n<p>Interestingly, DROPPING the tables was amazingly fast on ext3 &#8211; about 2500\/sec. XFS about 1000\/sec. So ext3 can destroy easier than it can create while XFS keeps up to speed with itself.<\/p>\n<p>What about InnoDB tables? Well&#8230;<\/p>\n<p>ext3(default): 21m 11s<\/p>\n<p>xfs(default): 12m 48s<\/p>\n<p>ext3(dir_index): 21m 11s<\/p>\n<p>Interestingly the create rate for XFS was around 500 tables\/second &#8211; half that of MyISAM tables.<\/p>\n<p>These are interesting results for those who use a lot of temporary tables or do lots of create\/drop tables as part of daily life.<\/p>\n<p>All tests performed on a Western Digital 250GB 7200rpm drive in a 2.8Ghz 800Mhz FSB P4 with\u00c2\u00a0 2GB memory running Ubuntu 6.10 with HT enabled.<\/p>\n<p>At the end of the test, the ibdata1 file had grown to a little over 800MB &#8211; still enough to fit in memory. If we increased this to maybe 200,000 tables (presumably about a 3.2GB file) that wouldn&#8217;t fit in cache, then the extents of XFS would probably make it perform better when doing INSERT and SELECT queries as opposed to the list of blocks that ext3 uses. This is because the Linux kernel caches the mapping of in memory block to disk block lookup making the efficiency of this in the file system irrelevant for data sets less than memory size.<\/p>\n<p>So go tell your friends: XFS is still the coolest kid on the block.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Inspired by PeterZ&#8217;s Opening Tables scalability post, I decided to try a little benchmark. This benchmark involved the following: Create 50,000 tables CREATE TABLE t{$i} (i int primary key) Insert one row into each table select * from each table &hellip; <a href=\"https:\/\/www.flamingspork.com\/blog\/2006\/11\/22\/create-insert-select-drop-benchmark\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[1,8,14],"tags":[],"class_list":["post-761","post","type-post","status-publish","format-standard","hentry","category-general","category-linux-kernel","category-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-ch","jetpack-related-posts":[{"id":3295,"url":"https:\/\/www.flamingspork.com\/blog\/2013\/04\/19\/the-merge-storage-engine-not-dead-just-resting-or-forgotten\/","url_meta":{"origin":761,"position":0},"title":"The MERGE storage engine: not dead, just resting&#8230;. or forgotten.","author":"Stewart Smith","date":"2013-04-19","format":false,"excerpt":"Following on from my fun post on Where are they now: MySQL Storage Engines, I thought I'd cover the few storage engines that are really just interfaces to a collection of things. In this post, I'm talking about MERGE. The MERGE engine was basically a multiplexer down to a number\u2026","rel":"","context":"In &quot;drizzle&quot;","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3801,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/09\/19\/mysql-architecture\/","url_meta":{"origin":761,"position":1},"title":"Some current MySQL Architecture writings","author":"Stewart Smith","date":"2014-09-19","format":false,"excerpt":"So, I've been looking around for a while (and a few times now) for any good resources that cover a bunch of MySQL architecture and technical details aimed towards the technically proficient but not MySQL literate audience. I haven't really found anything. I mean, there's the (huge and very detailed)\u2026","rel":"","context":"In &quot;mysql&quot;","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1576,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/02\/25\/no-implicit-defaults\/","url_meta":{"origin":761,"position":2},"title":"No implicit defaults","author":"Stewart Smith","date":"2009-02-25","format":false,"excerpt":"See also: MySQL Bug 43151 The MySQL Manual proudly states that you don't get implicit default values if strict mode. mysql> set sql_mode='STRICT_ALL_TABLES'; Query OK, 0 rows affected (0.00 sec) mysql> create table t1 (a int, b int, c int, d int, e int, f int, g int, h int,i\u2026","rel":"","context":"In &quot;drizzle&quot;","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1745,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/12\/09\/drizzle-frm-replacement-the-table-proto\/","url_meta":{"origin":761,"position":3},"title":"Drizzle FRM replacement: the table proto","author":"Stewart Smith","date":"2009-12-09","format":false,"excerpt":"Drizzle originally inherited the FRM file from MySQL (which inherited it from UNIREG). The FRM file stores metadata about a table; what columns it has, what type those columns are, what indexes, any default values, comments etc are all stored in the FRM. In the days of MyISAM, this worked\u2026","rel":"","context":"In &quot;drizzle&quot;","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3234,"url":"https:\/\/www.flamingspork.com\/blog\/2013\/04\/04\/refactoring-internal-temporary-tables-another-stab-at-it\/","url_meta":{"origin":761,"position":4},"title":"Refactoring Internal temporary tables (another stab at it)","author":"Stewart Smith","date":"2013-04-04","format":false,"excerpt":"A few weekends ago, I started to again look at the code in Drizzle for producing internal temporary tables. Basically, we have a few type of tables: Standard Temporary (from CREATE TEMPORARY TABLE) Temporary (from ALTER TABLE) Internal temporary (to help with query execution) If you're lucky enough to be\u2026","rel":"","context":"In &quot;code&quot;","block_context":{"text":"code","link":"https:\/\/www.flamingspork.com\/blog\/category\/code\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":800,"url":"https:\/\/www.flamingspork.com\/blog\/2007\/03\/10\/code-size-of-an-engine-versus-test-suite\/","url_meta":{"origin":761,"position":5},"title":"Code size of an engine versus test suite","author":"Stewart Smith","date":"2007-03-10","format":false,"excerpt":"If you count the lines of code in the MySQL Cluster (NDB) test suite (mysql-5.1\/storage\/ndb\/test - and exclude the old ODBC stuff) you come up with about 104000 lines of code. This is in contrast to the approximate other 350,000 lines of code for the NDB engine (excluding the handler,\u2026","rel":"","context":"In &quot;mysql&quot;","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/761","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/comments?post=761"}],"version-history":[{"count":0,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/761\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=761"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=761"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=761"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}