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 take about 60mins on XFS. Seems to be metadata limited here… disk going constant, not CPU.
Of course the real benefit with XFS will be sane lookup times.
Delete wasn’t bad -Â under 2 mins.
Also would be better on a less abused FS than my laptop :)
Hey, send me the script and I’ll see what time I come up with…
Sure, the script is:
$ cat /home/stewart/mysql_create_table_torture
#!/usr/bin/php -f
< ?php $db = mysql_connect('127.0.0.1:9306','root') or die(mysql_errno().': '.mysql_error()); mysql_select_db('test',$db) or die(mysql_errno().': '.mysql_error()); for ($i = 1; $i <= 1000000; $i++) { print "\r{$i}\t"; $query = "CREATE TABLE t{$i} (i TINYINT) ENGINE=MyISAM"; mysql_query($query) or die(mysql_errno().': '.mysql_error()); } mysql_close($db); ?>
I took a perl script to basically throw a million table names like that into a file…
Threw that at a PostgreSQL 7.4.8 instance I had on a fractional part of an AIX p570 cluster; that took a little less than 31 minutes.
It pretty much corresponds to the joint cost of:
a) Creating a file in the filesystem (JFS2, of course), plus
b) Adding an entry to each of probably half a dozen system tables
This would seem to be something where any reasonably modern + reasonably competently designed database running on a (RM+RCD) filesystem should do this reasonably quickly…
Of course, what idiot would:
a) Count this as being important as anything other than a silly exercise, or
b) Actually try to use a million tables…