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.
« Posts under mysql
SHOW COLUMNS FROM MyDatabase.MyTable
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.
SHOW TABLE STATUS FROM MyDatabaseName;
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 Create_options: 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
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.