List:General Discussion« Previous MessageNext Message »
From:Rick James Date:May 23 2012 4:28pm
Subject:RE: Need help for performance tuning with Mysql
View as plain text  
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

Thread
Need help for performance tuning with Mysql Yu Watanabe23 May
  • Re: Need help for performance tuning with MysqlYu Watanabe23 May
    • Re: Need help for performance tuning with MysqlTsubasa Tanaka23 May
      • Re: Need help for performance tuning with MysqlYu Watanabe23 May
        • Re: Need help for performance tuning with MysqlTsubasa Tanaka23 May
          • Re: Need help for performance tuning with MysqlAndrew Moore23 May
            • Re: Need help for performance tuning with MysqlAnanda Kumar23 May
  • Re: Need help for performance tuning with MysqlJohnny Withers23 May
    • Re: Need help for performance tuning with MysqlYu Watanabe24 May
  • RE: Need help for performance tuning with Mysql Rick James23 May
    • Re: Need help for performance tuning with MysqlYu Watanabe24 May
      • Re: Need help for performance tuning with MysqlAlex Schaft24 May
        • Re: Need help for performance tuning with MysqlAlex Schaft24 May
          • Re: Need help for performance tuning with MysqlYu Watanabe24 May
      • RE: Need help for performance tuning with MysqlRick James24 May
        • Re: Need help for performance tuning with MysqlPrabhat Kumar30 May