 Several MySQL performance questions [message #3302] |
Tue, 15 July 2008 04:53  |
 Debi Wang
Messages: 3
Registered: July 2008
Location: China |
Junior Member |
|
|
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 
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.
|
|
|
| Re: Several MySQL performance questions [message #3304 is a reply to message #3302 ] |
Wed, 16 July 2008 07:11  |
Speeple
Messages: 91
Registered: August 2006 |
Member |
|
|
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?
Martin Gallagher | Speeple: The latest news
|
|
|
|
| Re: Several MySQL performance questions [message #3311 is a reply to message #3307 ] |
Thu, 17 July 2008 06:54 |
Speeple
Messages: 91
Registered: August 2006 |
Member |
|
|
Yes, from all my experience INT types tend to be faster.
Try this to get the size of cache for MyISAM indexes:
SHOW VARIABLES LIKE 'key_buffer_size'
That's the size in bytes.
Then you can guess the optimum value (making sure it's with limits of your available RAM) by doing:
SHOW TABLE STATUS WHERE Engine='MyISAM';
The sum of all the "Index_length" columns would be your optimum key_buffer size (plus a few megs).
Martin Gallagher | Speeple: The latest news
|
|
|
|
| Re: Several MySQL performance questions [message #3321 is a reply to message #3302 ] |
Mon, 21 July 2008 06:38 |
Speeple
Messages: 91
Registered: August 2006 |
Member |
|
|
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?
Martin Gallagher | Speeple: The latest news
|
|
|
Members
Search
Help
Register
Login
Home
