« Posts under mysql

Qcache_not_cached should be small

If not, you’re wasting overhead on checking and invalidating the query cache, for a large/busy query cache, it gets more expensive. Thus, the lesson is to know which query should not be cached and explicitly use “SELECT SQL_NO_CACHE …” in your SQL statement. It means tables with many UPDATES/INSERTS should not use query caching.


That operation will always require a disk read, which is rather expensive, especially for larger tables. For example, for a 500MB table in my test, it takes 0.23 seconds just for this query. I thought the table schema should not depend on its size. Guess there is something more technical than my basic understanding.

Duplicate value under Binlog_Do_DB

If you see Binlog_Do_DB show duplicate values and you mention them only once in my.cnf. For example: db1,db2,db1,db2. You would suspect that something is reading your my.cnf twice. That would be correct as it’s a MySQL bug #20748

MySQL – SHOW commands are expensive

It’s convenient to display information about a table, running status, server configurations. However, it is expensive! A simple “SHOW COLUMNS FROM SomeTable” create a disk-based temporary table!!! The same for “SHOW STATUS LIKE ‘Created_%'”. Lesson: avoid these if you don’t want to see “Created_tmp_disk_tables” and “Created_tmp_tables” counters spinning. It’s not a bug but can mislead in debugging. A patch is available on 5.1.12 (not for 5.0.x though). But the lesson here is to avoid frequent calls to SHOW commands. A better solution is to have MySQL support query caching not just for SELECT… but also for SHOW COLUMNS. These queries do not change that often and can be cached effectively. You could write your own cache but having it done MySQL query caching would be a plus as well.


Issue with MySQL replication

For some reasons I have not been able to pin point exactly which piece goes wrong, if a master has too many slaves, it will have quite a few issues. 1) lighttpd regularly get backend overloaded errors; 2) ping to this server will fluctuate drastically. And I don’t think it’s that many slaves.


Run that query if you would like to know basic details about a database, especially the overhead to see if you would need to optimize it.

Name: AdSpeedDB
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 4676
Max_data_length: 281474976710655
Index_length: 3072
Data_free: 4676
Auto_increment: 221
Create_time: 2007-08-01 17:15:24
Update_time: 2008-06-03 07:26:13
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Comment: Ad Server

MySQL replication problem with 5.0.46-48

For CentOS 4.x, if you want MySQL 5.0.x then you would probably use CentOS Plus. And the current MySQL distribution goes with it is often 5.0.46 or 5.0.48, they both have a problem when acting as a slave replication. At every reboot, the replication breaks because the relay log and some other important files is saved into /var/run/mysqld, which get erased every reboot. Not very nice and the bug has been filed with MySQL. To fix, you need to explicitly set the correct file path:

# to fix mysql replication problem every reboot
# Source: http://arjen-lentz.livejournal.com/115899.html
relay-log = /var/lib/mysql/relay-bin
relay-log-index = /var/lib/mysql/relay-bin.index
relay-log-info-file = /var/lib/mysql/relay-bin.info

MySQL upgrade breaks replication

Upgrade from 4.1.x and 5.0.22 to 5.0.42 and going forward (5.0.46, 5.0.48) in CentOS breaks replication. The error log now placed as /var/run/mysqld/mysqld.err instead of into the default datadir and the name is mysqld.err instead of .err, is it a MySQL bug?

mysql> start slave;
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

071005 13:06:38 [ERROR] Failed to open the relay log ‘/var/run/mysqld/mysqld-relay-bin.000074’ (relay_log_pos 598368274)
071005 13:06:38 [ERROR] Could not find target log during relay log initialization

MySQL replication & a full disk

To avoid the nasty mess because of a full hard drive, make sure your log files are in control. Things run really wild without diskspace!

If you’re running MySQL replication, run “PURGE MASTER LOGS TO ‘yourbinlogname-bin.00x'” or “PURGE MASTER LOGS BEFORE ‘2007-05-30 10:10:10’;” to safely remove the logs before X. Make sure it’s a number at or before the active log file shown in “SHOW MASTER STATUS”. You can run this safely with active replication.