atppp's Blog

I recently did some performance testing with a few big tables on mysql/5.0.45 under CentOS and noticed a sharp drop in query speed a few minutes after restarting mysql. More tests showed mysql/5.0.51a or newer don’t have this problem. OK, I guess a picture is worth a thousand words… (SELECT queries only, and also mysql buffer size is huge, so disk I/O is minimal during test.)

It’s most likely due to mysql bug 21075 (fixed in mysql/5.0.50). Unfortunately mysql/5.0.45 is the version that ships with CentOS 5.3 and some other Redhat-derived Linux distro. Consider upgrading if you are using mysql/5.0.45 and your mysql server appears to be heavily CPU-bound.

Some early WordPress databases use raw Latin1 encoding to represent UTF-8 characters. The following seems to be an O.K. way to convert those databases to native UTF-8 databases (at least it works for me, Mysql 5.0.45):

1. Stop/restrict web service.
2. Dump the database:

   $ mysqldump --default-character-set=latin1 --databases wordpress > m.sql

3. Change encoding settings in m.sql, including table default encoding and client communication encoding:

   $ replace "CHARSET=latin1" "CHARSET=utf8" \
       "SET NAMES latin1" "SET NAMES utf8" < m.sql > m2.sql

4. Import the “converted” sql:

   $ mysql < m2.sql

5. Add the following lines to WordPress wp-config.php

   define('DB_CHARSET', 'utf8');
   define('DB_COLLATE', '');

6. Reopen web service. That’s it.

Note: For future convenience, you might as well change the database default encoding to utf8.

Ref to WordPress documentation for details: here and here.