Yufei Zhu's Weblog

MySQL MyISAM performance tuning on T2000

The default storage engine of MySQL is MyISAM.  Unlike InnoDB,  it stores each MyISAM table in three files, the schema file(.frm), the data file(.MYD) and the index file(.MYI). It only supports filesystem. It does table level locking. 

Compared to InnoDB, MyISAM doesn't have its own data buffer cache, it only has index buffer cache, the key buffer (variable key_buffer_size).  We need to use file system buffer cache for data cache for MyISAM tables.

 
Here are a few steps of how to tune MySQL MyISAM performance on T2000 or other Solaris platforms

• There is a very good performance feature in MySQL 5.1 beta  that allows MyISAM using mmap memory instead of malloced buffers. If you experienced very high mutex contention in earlier MySQL releases,  you can get a huge performance improvments.  How do you know if you have this problem?

• If you are using MySQL 5.0 or earlier version, during the test you can collect "lockstat sleep 1" and if you see ufs_lockfs_begin and ufs_lockfs_end in the callers column. You are likely to get the performance boost by going to the latest 5.1. You need to have  "myisam_use_mmap=1" in /etc/my.cnf to be able to use this feature. This is an example of lockstat output that has the performance issue.
  

Adaptive mutex spin: 140294 events in 1.145 seconds (122505 events/sec)

Count indv cuml rcnt     spin Lock                   Caller                  
-------------------------------------------------------------------------------
31341  22%  22% 0.00        9 0x600052c4d10          ufs_lockfs_end+0x70     
30952  22%  44% 0.00       10 0x600052c4d10          ufs_lockfs_begin+0xe4   

• You can use libumem or libmtmalloc to get scalable memory allocation performance on multi-threaded MySQL.  The way to do this is before mysql is started,  setenv LD_PRELOAD_64 /usr/lib/sparcv9/libumem.so.  This example is for 64bit MySQL. For 32bit MySQL you can do setenv LD_PRELOAD /usr/lib/libumem.so

• Since we have to use filesystem buffer cache to cache data for MyISAM tables, it is important to tune segmap_percent in /etc/system. The default segmap_percent is 12% on solaris, that means you can only get to use 12% of the system memory for filesystem buffer cache.  It depends on your database size, but setting it too high could lead low memory on the system causing excessive paging.  In our case, we set it to 80%. In /etc/system, set segmap_percent=80. You need to reboot the system to make it effective.

• key cache is important feature for MyISAM to cache index blocks that allows multiple threads to access key buffer simultaneously. You can check the performance of key cache by doing "show status" and look at what is the ratio of key_reads and key_read_requests. It should be less than 1%.  Otherwise, you might need to increase key_buffer_size. The maximum for key_buffer_size is 4G however you can create multiple key cache (The size limit of 4GB applies to each cache individually, not as a group.) That will also help on the situation where access to one key cache structure does not block access to the other key cache
  

• Making sure there is no io bottleneck. Collect  "iostat -xtcnz 5" on the system and look at that what is the disk response time (column asvc_t in milliseconds) and what is the average outstanding ios(column actv).
  

• On solaris systems, you can use "prstat -Lmc" to monitor  the active processes on the systems.
  

Posted at 12:43PM Aug 07, 2006 by yufei in Sun  |  Comments[0]