List:General Discussion« Previous MessageNext Message »
From:Alex Schaft Date:May 24 2012 5:37am
Subject:Re: Need help for performance tuning with Mysql
View as plain text  
On 2012/05/24 04:10, Yu Watanabe wrote:
> 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 |
> 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?
You are selecting a record based on the value of data_id and
thold_enabled, but don't have an index on either? Add an index for both.
If data_id is unique, then you would only need an index on that.

Alex
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