List:General Discussion« Previous MessageNext Message »
From:Yu Watanabe Date:May 24 2012 2:10am
Subject:Re: Need help for performance tuning with Mysql
View as plain text  
Rick

Thank you for the reply.

>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)
>

  The page is really cool. Its very simple and easy to understand.

>2. Instead INDEXes and schema design must be studied.  Please provide:
>SHOW CREATE TABLE

  | thold_data | CREATE TABLE `thold_data` (
    `id` int(11) NOT NULL auto_increment,
    `rra_id` int(11) NOT NULL default '0',
    `data_id` int(11) NOT NULL default '0',
    `thold_hi` varchar(100) default NULL,
    `thold_low` varchar(100) default NULL,
    `thold_fail_trigger` int(10) unsigned default NULL,
    `thold_fail_count` int(11) NOT NULL default '0',
    `thold_alert` int(1) NOT NULL default '0',
    `thold_enabled` enum('on','off') NOT NULL default 'on',
    `bl_enabled` enum('on','off') NOT NULL default 'off',
    `bl_ref_time` int(50) unsigned default NULL,
    `bl_ref_time_range` int(10) unsigned default NULL,
    `bl_pct_down` int(10) unsigned default NULL,
    `bl_pct_up` int(10) unsigned default NULL,
    `bl_fail_trigger` int(10) unsigned default NULL,
    `bl_fail_count` int(11) unsigned default NULL,
    `bl_alert` int(2) NOT NULL default '0',
    `lastread` varchar(100) default NULL,
    `oldvalue` varchar(100) NOT NULL default '',
    `repeat_alert` int(10) unsigned default NULL,
    `notify_default` enum('on','off') default NULL,
    `notify_extra` varchar(255) default NULL,
    `host_id` int(10) default NULL,
    `syslog_priority` int(2) default '3',
    `cdef` int(11) NOT NULL default '0',
    PRIMARY KEY  (`id`),
    KEY `rra_id` (`rra_id`)
  ) ENGINE=MyISAM AUTO_INCREMENT=69641 DEFAULT CHARSET=latin1 |

>SHOW TABLE SIZE

  You must be mentioning about the "show table status"

mysql> show table status where name = "thold_data";
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------
+---------------------+-------------------+----------+----------------+---------+
| Name       | Engine | Version | Row_format | Rows | Avg_row_length | Data_length |
Max_data_length | Index_length | Data_free | Auto_increment | Create_time         |
Update_time         | Check_time          
| Collation         | Checksum | Create_options | Comment |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------
+---------------------+-------------------+----------+----------------+---------+
| thold_data | MyISAM |      10 | Dynamic    | 6161 |             90 |      555128 |
281474976710655 |       140288 |         0 |          70258 | 2012-05-24 10:41:47 |
2012-05-24 10:47:19 | 2012-05-24 
10:41:47 | latin1_swedish_ci |     NULL |                |         | 
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------
+---------------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

>EXPLAIN SELECT

  I have seen the following select query in the slow query log.
  I also saw update queries as well.

mysql> explain select * from thold_data where thold_enabled='on' AND data_id = 91633;
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows |
Extra       |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | thold_data | ALL  | NULL          | NULL | NULL    | NULL | 6161 |
Using where | 
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.06 sec)

If cache size tuning is not an option ,
do you think that following action would be an choice to faten the queries little bit
more?

1. depriving the database and setup as an another process. (multiple mysql processes)
2. Move the MYD, MYI, frm to ram disk (/dev/shm)

Thanks,
Yu

Rick James さんは書きました:
>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