Several MySQL performance questions


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 --opt --master-data --all-databases | /opt/lps/current/bin/ --user=lps

In, 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.

I can only really provide an answer for 1) at the moment:

Does the new table have an additional field? More fields + indexes = increased UPDATE / INSERT times, plus more usage of the MyISAM key buffer.

Do you use key compression on your non-numerical type columns? This will reduce space / index size.

How big are your buffers?

Thanks for your help.
We just the integer uid in the new table:

  • Each entry of old table is about 90 bytes, most of columns are string (varchar).
  • The entry of new table has a new integer column.

We use the default key compression. It seems the numeric key is not compressed and non-numeric key is compressed. I don’t know which buffer you are asking. How can I get the number of it?

Even with the non-numeric key compression, I think the integer index should not be slower than non-numeric index. I think even if it’s not quite faster than compressed non-numeric key, the integer key should always be the fastest.

Thanks again.

So your index size is around 800Mb, but your key buffer size is ONLY ~8Mb?

How much RAM does this machine have? Surely you can afford to give the buffers more than 8Mb?