Hi,
We use MySQL as DB in our product. Recently we found there are some performance issues in our product, so we are performing some DB testing and optimization. We found some issues in our testing and we don’t know how to explain them. Could anyone help to look at them? We are looking for your help eek:
The testing environment is SUN X2100 server, Redhat Linux 3 update 8 and MySQL 4.1.13.
1. Integer index slower than string index
There is a huge MyISAM table with about 100M entries. To improve the access efficiency of this table, we are planning to change index of this table. Currently the index is
partitionID (varchar (36), fixed value of 9 characters string in our testing)
-
userID (varchar (36), 10 digits numberic string in testing)
-
timestamp
We want to introduce a new interger uID to replace partitionID + userID, so the new index will be:
uid (integer)
- timestamp
But in our testing, access the table with new table is slower than old one. The ‘query’ operation is a little (10%) slower, and ‘update’ operation is similar. We can’t find any reason that the interger index is slower than string index. Do you have any idea?
Btw, the testing was performed by a single thread in sequential mode. The testing result is repeatable.
2. Peak of response time
In out testing, we found there are perodically peak response time. For example, the first 10 queries only spent 5ms as average, but the 11th queries spent 30ms. Then another fast 10 queries and one slow query follows.
Since we disabled MySQL cache through set query_cache_size to 0, we suspect this peak is caused by OS cache. Do you have other idea?
3. High I/O wait when innoDB table syncs
Recently we found that when we write a innoDB table to master D-node quickly, the I/O wait time of slave D node is higher than master DB. It’s 80% I/O wait CPU usage on master DB and about 90% I/O wait on slave.
That table was MyIsam table before, we just found this issue when we change it to InnoDB table. And if changes the storage back to MyIsam, the I/O wait CPU usage will go down again.
4. Error 2013:
Lost connection to MySQL server during query when dumping table user_location at row: 7076
We use the following command to dump the data form master node to slave node, but every time we got error 2013 after it running about 2.5 hours.
/opt/lps/current/mysql/bin/mysqldump --user=lps -h lcm-dev14-n1-int.qd.lucent.com --opt --master-data --all-databases | /opt/lps/current/bin/mysql_client.sh --user=lps --host=acm-st14-n1-int.qd.lucent.com
In mysql_client.sh, the mysql script will be invoked.
In master DB, there is one big table with MyIsam storage-user_log; there are about 12M records in this table. The replication for that big table is finished correctly. After that table, when began to replicate another small table user_location with 10K records, this error is returned. If I truncate the big table user_log, the replication can be finished without any error.