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.watanabe@stripped]
> Sent: Tuesday, May 22, 2012 7:07 PM
> To: mysql@stripped
> 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
