lunatechian (lunatech-ian)

If you use innodb engine exclusively, and want to audit your databases for any tables that might have an incorrect engine, use the command below -

mysql -u root -p  information_schema -e \
"select TABLE_SCHEMA,TABLE_NAME, ENGINE from TABLES where\
 ENGINE != 'Innodb' \
 and TABLE_SCHEMA not in ('mysql','information_schema');"

If you are not able to start the mysql daemon repeatedly using your linux distribution init scripts and you are ready to pull out your hair in frustration, here is a tip that might help you in finding the problem.

Try running the mysqld_safe from the command line (without using the init scripts). Try running /usr/bin/mysqld_safe -v, which should spit out some debugging information.

If that fails, try calling the mysqld daemon directly from the command line, with the "-v" option . mysqld is usually present under /usr/sbin/mysqld. mysqld can be called with --print-defaults to get the command line options it would be run with.

/usr/sbin/mysqld would have been started with the following arguments: --user=mysql --pid-file=/var/run/mysqld/mysqld.pid ..... --max_binlog_size=100M

Try adding the -v option to these options to get more verbose details. When run from command line, mysqld will not detach from console and will print debugging info that might be useful in finding the cause of the error.

I suggest using 3 terminals to figure out what is going on

• one terminal with tail -f /var/log/mysql/mysqld.err
• one terminal with tail -f /var/log/messages
• one terminal where I ran the mysqld_safe / mysqld command

The init scripts are usually good for day to day work. However, sometimes the init scripts can impede a innodb crash recovery process on a large database. Some init scripts have timeout built into them and they can kill mysql while the innodb is still trying to recover its tables.

A few days back we were trying to optimize a sql query that was using an ORDER BY clause. When we ran an EXPLAIN on the query, we saw that the query was Using filesort and Using temporary. Since this query was run quite frequently, there was a benefit in optimizing this.

I had been under the impression that if your query uses filesort and then has to use temp tables for sorting, you cannot do much to save the query. However, that is not the case. If you have an index on the column being used to do the order by , mysql will use that index for sorting. Of course, there are caveats to this, the most important one being

The key used to fetch the rows is not the same as the one used in the ORDER BY: SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

Hence, if you have a query which requires to be sorted, and it is being run quite frequently, it makes sense to add an index on the column on which you are doing the sort.

Check the slides at scribd, Getting Smart about the New World of PostgreSQL Replication. The first few slides are not specific to PostgreSQL and they define some common replication jargon very nicely.

Are you planning on storing blobs in Innodb tables? If yes, think again. According to the manual,

InnoDB stores the first 768 bytes of a VARBINARY, VARCHAR, BLOB, or TEXT column in the row, and the rest into separate pages.

This translates into slower retrieval for blobs. However, InnoDB can skip reading blobs if they are not in select column list.

One solution for this is to keep blobs in a separate myisam table and do a join when you need the blob column.

If you are a heavy user of MySQL, I think you will appreciate this list of saved bug searches. For example, check out "p1_bugs_in_5.1" or "ActiveInnoDBBugs" These saved searches are also available as rss feeds.

Thanks to Artem Russakovskii for this nice explanation of what Seconds_Behind_Master means

In order to see if a slave is lagging, execute the 'show slave status' command and look for the Seconds_Behind_Master value. The way this value is calculated can be slightly ambiguous and unclear, so I'll explain. It is simply a difference between the 2 timestamps the time of the last received (and queued up in the relay log) query that already executed on the master and the time of the currently executing query on the slave.

As he says in the post, this value is not a precise indication of the replication delay.

Links:

• MySQL Slave Delay Explained And 7 Ways To Battle It
• Other variables in Show Slave Status