Thanks. I got tired of answering the same questions about buffer_pool and
key_buffer over and over on forums.mysql.com; now I just point people at that
page.
INT(1) and INT(50) are identical -- and take 4 bytes. See TINYINT, BIGINT,
etc. Also, UNSIGNED is probably wanted in more places than you have it.
555KB is not very big. But a table scan (as indicated by the EXPLAIN) costs
something.
select * -- Is this what you are really fetching? If not, we can
discuss a "covering" index.
from thold_data
where thold_enabled='on' -- 2-valued flag? Not likely to be useful in an
index, esp. not by itself
AND data_id = 91633; -- Probably the best bet.
Recommend:
INDEX(data_id) -- or UNIQUE, if it is unique
INDEX(data_id, thold_enabled) -- or the opposite order; this probably would
not be noticeable better.
`notify_default` enum('on','off') default NULL
Did you really mean to have 3 values (on, off, NULL)?
> -----Original Message-----
> From: Yu Watanabe [mailto:yu.watan...@jp.fujitsu.com]
> Sent: Wednesday, May 23, 2012 7:10 PM
> To: mysql@lists.mysql.com
> Subject: Re: Need help for performance tuning with Mysql
>
> 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
> mysql> 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.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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
