how can i tune mysql ?

Portal Home > Knowledgebase > Articles Database > how can i tune mysql ?

Posted by ttgt, 09-11-2011, 04:54 AM
Hi, my cloudlinux/cpanel server get high load about 10 on q8400, i check it under ssh/top, top - 2:52:30 up 100 days, 13:18, 1 user, load average: 10.05, 9.97, 9.74 Tasks: 255 total, 1 running, 253 sleeping, 0 stopped, 1 zombie Cpu(s): 70.8%us, 26.1%sy, 0.0%ni, 2.8%id, 0.0%wa, 0.1%hi, 0.2%si, 0.0%st Mem: 4026972k total, 3767184k used, 259788k free, 306456k buffers Swap: 8193108k total, 172k used, 8192936k free, 2137952k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 7038 mysql 15 0 551m 215m 4848 S 363.5 5.5 8078:03 /usr/sbin/mysqld and the "free" result: total used free shared buffers cached Mem: 4026972 3771144 255828 0 306900 2139816 -/+ buffers/cache: 1324428 2702544 Swap: 8193108 172 8192936 is any way to let my mysql get better performance ? thanx
Posted by CrocWeb, 09-11-2011, 05:43 AM
How is your /etc/my.cnf currently configured?
Posted by ttgt, 09-11-2011, 05:48 AM
Hi, it is as following,thanx [mysqld] safe-show-database local-infile=0 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock skip-locking query_cache_limit=1M query_cache_size=32M query_cache_type=1 max_user_connections=25 max_connections=500 interactive_timeout=50 wait_timeout=50 connect_timeout=50 thread_cache_size=128 key_buffer=16M join_buffer=1M max_allowed_packet=24M table_cache=1024 record_buffer=1M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock skip-locking query_cache_limit=1M query_cache_size=32M query_cache_type=1 max_user_connections=25 max_connections=500 interactive_timeout=50 wait_timeout=50 connect_timeout=50 thread_cache_size=128 key_buffer=16M join_buffer=1M max_allowed_packet=24M table_cache=1024 record_buffer=1M sort_buffer_size=2M read_buffer_size=2M max_connect_errors=10 thread_concurrency=8 myisam_sort_buffer_size=64M server-id=1 #[mysql.server] #user=mysql #basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/lib/mysql/mysql.pid open_files_limit=8192 #[mysqldump] #quick #max_allowed_packet=16M [mysql] no-auto-rehash [isamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M [myisamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M [mysqlhotcopy] interactive-timeout
Posted by LinuxSecurityExpert, 09-12-2011, 06:40 PM
Increase your key index size to about half of your server space RAM, enable MySQL slow logging and check the slow log to see if any queries need indexes, and if you are using InnoDB make sure that you're InnoDB pool size is as large as your server can handle. If your server begins to swap because of the settings back them off a little bit. There many a MySQL optimizing documents out there, you might search around for a MySQL optimizations and the terms "show status like '%key%'".
Posted by netmultiple, 09-12-2011, 11:45 PM
Did you try mysqltuner?
Posted by Geoff Winans, 09-13-2011, 07:23 PM
MySQL Tuner is fine, but cannot take into account your use-case. Is this a shared environment? Single database for a single site? What version of MySQL? Do you use mostly MyISAM or InnoDB tables? There are quite a few things to review here in order to come up with a really optimal configuration.
Posted by ttgt, 09-20-2011, 03:41 AM
Hi,it is shared hosting server,mysql 5.*.thanx
Posted by LinuxSecurityExpert, 09-20-2011, 04:06 AM
Unfortunately it is hard to tune MySQL in a shared environment. Most hosting providers tweak the configuration for the broadest possible set of needs, but if you have a specific configuration that you need to change, for which your user does not have access, you may be able to post a support ticket and get the changes that you need.
Posted by Syslint, 09-20-2011, 04:16 AM
You may also need to check the busy databases and optimize the schema too.
Posted by sosys, 09-20-2011, 06:04 AM
try run this command when mysql is busy: mysql -e "show full processlist"

Add to Favourites Print this Article