100% CPU --> A slow query. Tuning will not help. Period. 1. There are only a few things worth tuning -- see http://mysql.rjweb.org/doc.php/memory (they don't include the ones you tried)
2. Instead INDEXes and schema design must be studied. Please provide: SHOW CREATE TABLE SHOW TABLE SIZE EXPLAIN SELECT ... > -----Original Message----- > From: Yu Watanabe [mailto:yu.watan...@jp.fujitsu.com] > Sent: Tuesday, May 22, 2012 7:07 PM > To: mysql@lists.mysql.com > Subject: Need help for performance tuning with Mysql > > Hello all. > > I would like to ask for advice with performance tuning with MySQL. > > Following are some data for my server. > > CPU : Xeon(TM) 2.8GHz (2CPUs - 8core total) > Memory : 8GB > OS : RHEL 4.4 x86_64 > MySQL : MySQL 5.0.50sp1-enterprise > > Attached file > # my.cnf.txt : my.cnf information > # mysqlext_20120522131034.log : variable and status information from > mysqladmin > > I have 2 database working with high load. > > I wanted to speed up my select and update queries not by optimizing the > query itself but tuning the my.cnf. > > I have referred to following site, > http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html > > and read "Hiperformance Mysql vol.2" , > and increased the following values, > > table_cache > thread_cache_size > tmp_table_size > max_heap_table_size > > but made not much difference. > > According to the ps and sar result > > *1 PS result > Date Time CPU% RSS VSZ > 2012/5/22 21:00:39 109 294752 540028 > > *2 SAR > Average CPU user 25% > sys 5% > io 3% > > I assume that MySQL can work more but currently not. > > I am considersing to off load 1 high load database to seperate process > and make MySQL work in multiple process. > > It would be a great help if people in this forum can give us an adivice > for the tuning. > > Best Regards, > Yu Watanabe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
