List:General Discussion« Previous MessageNext Message »
From:Prabhat Kumar Date:May 30 2012 8:05pm
Subject:Re: Need help for performance tuning with Mysql
View as plain text  
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 <rjames@stripped> 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.watanabe@stripped]
>  > Sent: Wednesday, May 23, 2012 7:10 PM
> > To: mysql@stripped
> > 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.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
> > >
> > >
> >
> >
> > --
> > 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

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