Category: mysql

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.

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

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…

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:…

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…

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…

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…