Check performance with script : http://mysqltuner.pl/mysqltuner.pl - Variable tunning http://hackmysql.com/mysqlidxchk - Unused Index
Thanks, Prabhat On Thu, May 24, 2012 at 4:32 PM, Rick James <rja...@yahoo-inc.com> wrote: > 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 > > -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat