{"id":612,"date":"2006-03-09T23:32:00","date_gmt":"2006-03-09T13:32:00","guid":{"rendered":""},"modified":"2006-03-09T23:37:08","modified_gmt":"2006-03-09T13:37:08","slug":"a-million-tables","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2006\/03\/09\/a-million-tables\/","title":{"rendered":"A million tables"},"content":{"rendered":"

Arjen’s MySQL Community Journal – A million tables<\/a><\/p>\n

$ time ~\/mysql_create_table_torture
\n50234<\/p>\n

real 6m11.927s
\nuser 0m2.347s
\nsys 0m1.578s
\n(i hit ctrl-c at 50,000 as i did want to get back to real work).<\/p>\n

No sign of slowdown. Assume it would take about 60mins on XFS. Seems to be metadata limited here… disk going constant, not CPU.<\/p>\n

Of course the real benefit with XFS will be sane lookup times.<\/p>\n

Delete wasn’t bad -\u00c2\u00a0 under 2 mins.<\/p>\n

Also would be better on a less abused FS than my laptop :)<\/p>\n","protected":false},"excerpt":{"rendered":"

Arjen’s MySQL Community Journal – A million tables $ time ~\/mysql_create_table_torture 50234 real 6m11.927s user 0m2.347s sys 0m1.578s (i hit ctrl-c at 50,000 as i did want to get back to real work). No sign of slowdown. Assume it would … Continue reading →<\/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":[14],"tags":[],"class_list":["post-612","post","type-post","status-publish","format-standard","hentry","category-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-9S","jetpack-related-posts":[{"id":761,"url":"https:\/\/www.flamingspork.com\/blog\/2006\/11\/22\/create-insert-select-drop-benchmark\/","url_meta":{"origin":612,"position":0},"title":"CREATE, INSERT, SELECT, DROP benchmark","author":"Stewart Smith","date":"2006-11-22","format":false,"excerpt":"Inspired by PeterZ'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 drop each table I wanted to test file system impact\u2026","rel":"","context":"In "General"","block_context":{"text":"General","link":"https:\/\/www.flamingspork.com\/blog\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":759,"url":"https:\/\/www.flamingspork.com\/blog\/2006\/11\/13\/disk-allocation-xfs-ndb-disk-data-and-more\/","url_meta":{"origin":612,"position":1},"title":"Disk allocation, XFS, NDB Disk Data and more…","author":"Stewart Smith","date":"2006-11-13","format":false,"excerpt":"I've talked about disk space allocation previously, mainly revolving around XFS (namely because it's what I use, a sensible choice for large file systems and large files and has a nice suite of tools for digging into what's going on).Most people write software that just calls write(2) (or libc things\u2026","rel":"","context":"In "linux-kernel"","block_context":{"text":"linux-kernel","link":"https:\/\/www.flamingspork.com\/blog\/category\/linux-kernel\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":874,"url":"https:\/\/www.flamingspork.com\/blog\/2007\/07\/31\/gah-o_direct-24-non-xfs-stab-stab\/","url_meta":{"origin":612,"position":2},"title":"gah… O_DIRECT…. 2.4…. non xfs… stab stab","author":"Stewart Smith","date":"2007-07-31","format":false,"excerpt":"* dchinner hands MacPlusG3 a bigger knife.... (on #xfs yesterday)","rel":"","context":"In "linux-kernel"","block_context":{"text":"linux-kernel","link":"https:\/\/www.flamingspork.com\/blog\/category\/linux-kernel\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":788,"url":"https:\/\/www.flamingspork.com\/blog\/2007\/02\/07\/timeout-units\/","url_meta":{"origin":612,"position":3},"title":"timeout units","author":"Stewart Smith","date":"2007-02-07","format":false,"excerpt":"Following a discussion on mythtv on #xfs (as you do), and a wondering of \"hrrm... i wonder what unit that timeout is\" with some NDB code I wish to make the following announcement: All timeout values in NDB related APIs will now be given in centijiffies of the server system.\u2026","rel":"","context":"In "mysql"","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":2145,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/09\/29\/lca-miniconf-call-for-papers-data-storage-databases-filesystems-cloud-storage-sql-and-nosql\/","url_meta":{"origin":612,"position":4},"title":"LCA Miniconf Call for Papers: Data Storage: Databases, Filesystems, Cloud Storage, SQL and NoSQL","author":"Stewart Smith","date":"2010-09-29","format":false,"excerpt":"This miniconf aims to cover many of the current methods of data storage and retrieval and attempt to bring order to the universe. We're aiming to cover what various systems do, what the latest developments are and what you should use for various applications. We aim for talks from developers\u2026","rel":"","context":"In "drizzle"","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":515,"url":"https:\/\/www.flamingspork.com\/blog\/2005\/11\/29\/disk-space-allocation-part-4-allocating-an-extent\/","url_meta":{"origin":612,"position":5},"title":"disk space allocation (part 4: allocating an extent)","author":"Stewart Smith","date":"2005-11-29","format":false,"excerpt":"For XFS, in normal operation, an extent is only allocated when data has to be written to disk. This is called delayed allocation. If we are extending a file by 50MB - that space is deducted from the total free space on the filesystem, but no decision on where to\u2026","rel":"","context":"In "linux-kernel"","block_context":{"text":"linux-kernel","link":"https:\/\/www.flamingspork.com\/blog\/category\/linux-kernel\/"},"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\/612","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=612"}],"version-history":[{"count":0,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/612\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=612"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=612"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=612"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}