my.cnf optimization, need help

Portal Home > Knowledgebase > Articles Database > my.cnf optimization, need help

Posted by whdev, 10-07-2008, 01:10 AM
Hello, my mysql server's server load is going off the root, so would appreciate some help here: Server: AMD Opteron 8212 (Quad Core), 8 GB RAM my.cnf: Extended status:
Posted by steveks, 10-07-2008, 01:25 AM
I believe a lot of things has to be modified. max_connect_errors=10000, key_buffer=768M seems to be high. Do you have any control panel ? Just give a try with this script wget http://www.day32.com/MySQL/tuning-primer.sh and try to adjust variables. If problem continues, better to contact your host or DC to check it.
Posted by steveks, 10-07-2008, 01:26 AM
Also, execute the below command and look for any databases or user stealing CPU usage. mysqladmin proc stat -i 3
Posted by ragubhat, 10-07-2008, 01:34 AM
http://wiki.mysqltuner.com/MySQLTuner
Posted by persianwhois, 10-07-2008, 02:27 AM
Edit your configuration to:
Posted by zuborg, 10-07-2008, 07:21 AM
2whdev: your config is fine, but I would recommend to raise query_cache_size and thread_concurrency limits: query_cache_limit=4M query_cache_size=256M thread_concurrency=8
Posted by Karl Austin, 10-07-2008, 09:20 AM
Why would he want to raise his query cache limits when he has free blocks still? 75QPS/average should not be killing your server. I'd be looking at your data structures and seeing where you've got badly structured tables/indexes and queries one of the telling things is: | Created_tmp_disk_tables | 26 | | Created_tmp_files | 3418 | | Created_tmp_tables | 625 | Lots of temporary files and tables created. You really don't want to be seeing that happening.
Posted by whdev, 10-07-2008, 10:59 PM
Any idea how to find queries that are causing the temporary files and tables created? Thanks for the feedback!
Posted by xeno007, 10-08-2008, 04:25 AM
Hi all, I am reading this forum for very long time but this time I had to ask for help. I reselled webhosting for 3 years and time came to get a dedicated server. So I got one. Specs: Core2Duo E6630 (2x2.4GHz) 4GB RAM 500 GB HDD I installed fresh CentOS 5.2 Lates WHM/cPanel and transfered one of my clients websites to this dedicated server. Since then, the Load Average very rare drops under 1.00... Most of the time is above, almost 2.00 although on the server nothing "fun" happens, but that hosted website. The website is small, last month it had ~3500 unique visitors which visited ~600.000 pages (you probably call them clicks ). The thing is that on shared environment where 100+ websites are hosted, on that server the load didn't go above 2. And here it's he only and the load is so high. In WHM >> System Health >> Show Current CPU Usage, I can see that the only process which is using alot of CPU is this one /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.domain.com.pid --skip-external-locking When I pasted this it was using 63.8% of the CPU I think the mysql needs some optimization. My current my.cnf is as follows (which is a real mess I guess, please dont yell at me [mysqld] max_connections = 800 key_buffer = 36M myisam_sort_buffer_size = 64M join_buffer_size = 2M read_buffer_size = 2M sort_buffer_size = 3M table_cache = 1024 thread_cache_size = 286 interactive_timeout = 25 wait_timeout = 1800 connect_timeout = 10 max_allowed_packet = 16M max_connect_errors = 10 query_cache_limit = 1M query_cache_size = 16M query_cache_type = 1 tmp_table_size = 16M skip-innodb [mysqld_safe] open_files_limit = 8192 [mysqldump] quick max_allowed_packet = 16M [myisamchk] key_buffer = 64M sort_buffer = 64M read_buffer = 16M write_buffer = 16M PLEASE help me optimize it. It will take some minutes of your life and you will help someone doesn't know much, just like you did some time ago. Thanks alot in advance.
Posted by zuborg, 10-08-2008, 07:01 AM
Install 'mytop' - 'top' for mysql I'm using following method: - open mysql client - type 'show full processlist;' , press 'enter' - press 'up' arrow, then 'enter' again You'll currently processed requests, select some and check how properly it's indexed or composed at all.

Add to Favourites Print this Article