Opening tables!

There’s one bottleneck in MySQL/InnoDB that ultimately sucks. It sucked in 4.0, sucked in 5.0, sucks in 5.1 with newest InnoDB plugin. Opening tables has been a bottleneck on machines that have thousands of tables all the time (as LOCK_open is being held during the process), and while there was a table being opened, everything else would stall on the machine.

It can simply take hours on such systems just to open tables – and the major portion of time spent is randomly diving into InnoDB tables to populate index statistics. It obviously sounds like low hanging fruit – as statistics aren’t needed while you are opening a table, they’re needed just for querying the table.

So, I threw in few thousand tables to my machine, and tried opening them with ten connections. Standard InnoDB code was opening 13.5 tables a second. After spending few minutes and moving (this is pure prototype, not suitable for production) statistic collection post ha_innodb::open(), I noticed performance increase.

Tables were opened at 105-a-second speed. A bit better, ~8x better.

Merry Christmas, MySQL!

About these ads
This entry was posted in mysql and tagged , . Bookmark the permalink.

3 Responses to Opening tables!

  1. Matt Montgomery says:

    This is Bug #49463 (http://bugs.mysql.com/bug.php?id=49463)
    I’ve linked your patch to this report.

  2. Bill Karwin says:

    Well done! That’s a great analysis and a great fix.

  3. Pingback: Opening tables v2! « domas mituzas: vaporware, inc.

Comments are closed.