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 :)

Bob tried to create a million tables in a MySQL database.

There’s not much inside MySQL to prevent you from doing this, but your file system is going to be quite a limitation. Very few file systems are happy when you attempt to cross a barrier of a few tens of thousands of directory entries, and even with InnoDB, every table in the data directory for that database will have an associated .frm file.

Not good.

Are you MySQL certified?

Are you going to the User’s Conference?

Do you want to tell those who are still considering certification, but still in doubt, how things look from the candidate perspective of things?

If so, then please get in touch with me — (carsten at mysql dot com). I’m looking for a couple of brave souls to join me during one of two certification BoFs, to answer questions from those that want to hear from others with real-world experience.

As users of stored procedures know, cursors can only work with explicit SQL statements, while they don't work with dynamic queries (there was an article by Beat Vontobel and a consequent feature request).
Trying to overcome this limitation, I came up with a somewhat useable alternative and a performance surprise. Let's go see them in due order.
The example is built against the World database provided in MySQL web site.
The method used for this test is an algorithm to compute a global checksum for a table. You may know that such a global CRC exists already, but it is only available for MyISAM tables, and as a procedure only ( …

... nice, but not quite there yet.
I mean, it has a clean look at I processed the ported Firebird Employee database of my previous posts to check it's reverse engineering abilities, everything went smooth for tables and views (and foreign keys too!), but unfortunately no triggers and stored procs where reverse engineered.
Here is the visual cronicle:

Connection to database:










After the connection MySQL Workbench does a first roundtrip to retrieve structures:











Once structures are retrieved it offers you the option to choose which database to …

The annual EclipseCon conference takes place in Santa Clara in less than 2 weeks.  In just a few years, Eclipse has gone from a niche technology to absolute domination in tools.  Not only is Eclipse a hugely popular platform for C++ and Java IDEs, it has become the basis for tools across a wide range of languages and technologies. Whether it's web development, data tools, or SOA, there's an Eclipse-based tool.  At this point, the only holdout is Sun, who has their own NetBeans tool, but it's never had the same kind of traction as Eclipse and I think they are just bitter about that. 

Having spent a number of years at Borland in the 90s, where I helped launch Delphi and other products, I have always had a soft spot for Integrated …

We're proud to have Greg Gianforte, Founder and CEO of RightNow Technologies do a keynote for us on Tuesday. He will talk about Bootstrapping: Starting an Open Source Business With Almost No Money! and yes, he is speaking from personal experience! He has written a book about it too.

In other news, we see that HP and Business Objects are both Diamond sponsors for the conference.
That's serious business ;-)

Of course it's silly, anyone considering anything like this should seriously look at their design! But someone else blogged about this, so that got me curious. He ran on Win2003 with NTFS and created InnoDB tables.

I happened to have RHEL4 box (P4) floating around, so I wrote a little PHP script:

$db = mysql_connect(':/tmp/mysql.sock','user','pwd')
     or die(mysql_errno().': '.mysql_error());
mysql_select_db('maxtables',$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);

It took 2 1/2 hours to complete on a 4.1 server (there's a 5.0 on the box but it's handling other stuff). Not too shabby and no problems …

Conference time! While I understand Frank’s feelings, there’s always hope that there will be some great “blog reports”, amazing IRC, and if at all, some form of podcasts.

However, if I was going to the MySQL Users Conference 2006, I’d probably attend things in this order:

• Monday - Arjen’s Optimizing MySQL Applications Using the Pluggable Storage Engine Architecture because this is important stuff all folk should learn about. Performance being the name of the game these days, it’d be silly to miss MySQL Performance Optimization that our very own performance guru, Peter …

... on MySQL, of course. Mark Matthews, creator of the JDBC interface for MySQL (Connector/J) and all round Java guru, will be teaching this tutorial. If you use J2EE with MySQL, you'll want to be there. I know, it's a tough choice between this and some of the other tutorials... arrange for a colleague to be at another one! ;-)

As I mentioned earlier, we expect many of the tutorials to sell out. I see some going very fast (I get weekly reports). If you really want to be in, you probably don't want to wait with registering.
…