Mysql – MySQL engine for in-memory tables that support row locks

Here are a few of the things I've run into with MySQL optimization:

If you see that your database server (the MySQL processes in particular) are using the majority of the CPU time, it probably means that you are either missing indexes or you need to optimize queries.

Turn on the slow query logging and pursue those slow queries to find out how they can be optimized. Use "explain" with the slow queries to find out why they are slow. The MySQL documentation has several sections about how to interpret these results.

Use memcached to cache the result of any queries you can for as long as you can. This is extremely fast, and able to cache many things that the internal MySQL query caching cannot, but can only be used in cases where you know the data can be cached long-term or you manually expire the cache.

Set up "munin" on the database system to begin collecting information about the system utilization and database query load.

Make sure your system has enough RAM that you aren't swapping, and hopefully also enough that you do few if any disc reads (use vmstat or munin to monitor this). Meaning that the data is served out of MySQL or disc buffer caches.

Sean

Yes, MySQL locks the table completely whilst it does the ALTER TABLE statement. Most of this time is spent in physically copying the table, which is why it is recommended you put all your necessary changes together in the one ALTER TABLE statement.

There are several approaches to mitigating this problem on a live database if you can't get a decent maintenance window.

First of all, many environments have timeouts of several minutes waiting for a table to become available for their query and don't know (and it's really difficult to check) just why the table is locked. I've used this quirk of a live website to do changes to tables. On a site I used to look after, I figured we had an allowance of about 7 minutes before someone started noticing. :-) It helps to make sure your boss is on your side.

Another way to do it is to do the select-insert-rename trick. This works well if the table either has fairly low UPDATE frequency, or is purely the target of INSERTs. The basic steps are to copy the table's schema, make the necessary changes, craft up a statement to do a INSERT...SELECT from the old to the new, and rename the tables (do the rename in one statement). You also need prepare ahead of time a statement to copy over any "new" records that got added or updated between the SELECT and the RENAME. I also did this a few times in a past job.

However, there are come caveats:

• You will almost certainly have problems if the source table is MyISAM, unless perhaps the table is almost never written to. This is because of the way MyISAM tables lock. It works better with InnoDB tables because it can still be read whilst your big INSERT...SELECT is running.
• You need a foolproof way of figuring out records that have been added or changed between the SELECT and the RENAME. For tables that are only used for INSERT, use the auto_increment column. For tables that get UPDATEs, you will need a reliable last-modified column.

Other ways to tackle this problem involve modifying slaves and failing the application over. This is more closely tied up with how your databases replicate. I also haven't done this one myself, so I can't describe exact steps.

Finally, there are a dozen server settings you can twiddle and several more that are much harder to change that will affect how long it takes to copy a table. The sort buffer is one, but also how much memory MySQL is allowed to use is another. (Remember that you can set a lot of those per connection, too, rather than setting some of them high globally.) When dealing with a lot of data, MySQL has a 'tipping point' effect where things are fairly linear up to a certain size, and then go to hell suddenly. It often comes up with complex queries working with a lot of data and is related to internal temporary table sizes and how much memory it is allowed to use, but it can come up with table alterations because they involve re-indexing the data. That is one reason why giving a database more memory is almost always a good thing.