monline

This took me almost an hour to figure out. Using MySQL server v5.0.51a (to be exact, the package version was "5.0.51a-24+lenny2~bpo40+1-log" on a Debian Etch server and using the backports repository) I tried to create a new table from the results of a query (you know: CREATE TABLE ... SELECT ...). I watched the MySQL process state as it changed from "copying tmp table" to "sorting" and then to "sending data". However during the latter I did not see any significant CPU or I/O usage and the SQL statement never finished. I waited a very long time and nothing.

The information_schema in MySQL 5.x gives you the opportunity to dig into the database metadata. Unfortunately it's slow like hell. I've attempted to write a query that returns all foreign keys that have no index defined on their columns. It seems that information_schema needs a lot more tuning, because my query results in the following error message:

I've created a set of SQL queries that can be run on the information_schema of a MySQL 5.x+ server to compare the structure of two databases and create the SQL commands needed to convert the structure of one database to the structure of the other. Use them at your own risk. They worked for me, but they might not cover all aspects of MySQL database comparison. I've tested them on an 5.0.51 MySQL server running on Debian 5.x.

By default MySQL stores InnoDB tables and indexes in a single file called "ibdata1" (which is on Debian in the /var/lib/mysql directory). Unfortunately this file is never "recycled", thus once space is allocated to it, it's never released (see this feature request). Not even after dropping the database that held the given table. However there's an InnoDB option called innodb_file_per_table that allows you to store each InnoDB table in a separate file (just as MyISAM does) and after dropping the table the file is removed and thus the occupied space is recycled.

Xaprb published a nice method for creating sequentially increasing identifiers for groups of records in a query.

A few notes on a couple of gotchas with using batch inserts in Hibernate with a MySQL database.

It's a video of the "Scaling MySQL at YouTube" presentation from MySQL Conference & Expo 2007 by Paul Tuckfield, the lead MySQL DBA at YouTube. Here you can download an MP3 version too.

This blog entry contains links to two PDFs on Wikipedia's architecture and how they dealt with the LAMP stack. One is Wikipedia: Site internals, configuration, code examples and management issues (the workbook), the other is a presentation on Wikimedia Architecture. The letter is lighter on the topic, easier to digest if you've only 5 minutes.

A properly tuned MySQL database with a well written application should not experience severe locking (ie. a query locks some tables and other queries have to wait til the locking query finishes). To check whether you've locking problems you should do the following ...

This is again a script that people could write easily if they understood some shell scripting, but it is quite well implemented with logging and all and maybe spares a couple of minutes/hours of your time.

You should use it with crontab on a daily basis. For details take a look at the desc. of the MySQL backup script.