List:General Discussion« Previous MessageNext Message »
From:Ananda Kumar Date:May 23 2012 10:35am
Subject:Re: Need help for performance tuning with Mysql
View as plain text  
Hi,
How much ever tuning you do at my.cnf will not help much, if you do not
tune your sql's.

Your first priority should be tune sql's, which will give you good
performance even with decent memory allocations and other settings

regards
anandkl

On Wed, May 23, 2012 at 3:45 PM, Andrew Moore <eroomydna@stripped> wrote:

> Yu,
>
> The upgrade to 5.5 that Jonny advises should NOT your first action. If
> MySQL is mis-configured on 5.0 it will likely be misconfigured on 5.1 and
> 5.5. Test your application thoroughly on the new version before heeding
> that advice. Read the change logs and known bugs. Running the upgrade might
> seem painless but if you have some legacy feature in place then things will
> not work how you may expect them to.
>
> Review your needs and see if a switch to innodb storage engine will give
> you any performance gain. The locking differences alone might make this
> worthwhile. TEST it.
>
> You did not state your data and index size. You will benefit from having
> enough RAM so that your 'working' data set fits to memory. This isn't
> possible/practical for large data but if you have a 5G dataset and 8G
> available memory you might not need to rush out and spend money.
>
> If you're heavily using MyISAM, review and tune the MyISAM related buffers.
> If you are working mostly with InnoDB tune those variables. Measure, change
> measure again. It might be an iterative process but you will learn lots
> along the way.
>
> Good luck.
>
> Andy
>
> On Wed, May 23, 2012 at 5:44 AM, Tsubasa Tanaka <yoku0825@stripped>
> wrote:
>
> > Hello, Yu-san,
> > (へろへろな英語で申し訳ないです)
> >
> > Can I think that you already tweaked Index on the tables?
> > if you yet,please create apt indexes.
> >
> > MyISAM caches only Index without data.
> > i take way for decreasing disk seek,
> >  1) create more indexes on the tables,if the tables doesn't update quite
> > often.
> >   including data into index forcibly.
> >   this makes slow for insert and update,and this is dirty idea,i think.
> >    (よくSELECTされるカラムをINDEXに含めてしまいます。
> >    ただし、SELECT *
> FROMで呼ばれることが多い場合には使えない上に
> >    かなり美しくない策です。。)
> >  2) tune filesystem and disk drive parameter for datadir.
> >   MyISAM table's data caches only in the filesystem cache.
> >   But i regret that i don't have knowledge around filesystem.
> >
> > あまり力になれなくて申し訳ないです。
> >
> > regards,
> >
> >
> > ts. tanaka//
> >
> > 2012/5/23 Yu Watanabe <yu.watanabe@stripped>:
> > > Hello Tsubasa.
> > >
> > > Thank you for the reply. (返信ありがとうございます。)
> > >
> > > Our high loaded DB are both INNODB and MyISAM.
> > > Espicially , on MyISAM.
> > >
> > > I will consider the tuning of innodb_buffer_pool_size as well.
> > >
> > > Do you know the tips for how to tune the disk access for MyISAM?
> > >
> > > Thanks,
> > > Yu
> > >
> > > Tsubasa Tanaka さんは書きました:
> > >>Hello,
> > >>
> > >>I seem your mysqld doesn't use enough memory.
> > >>
> > >>>>Date       Time      CPU%  RSS     VSZ
> > >>>>2012/5/22  21:00:39  109   294752  540028
> > >>
> > >>if your mysqld uses InnoDB oftenly,
> > >>edit innodb_buffer_pool_size in you my.cnf.
> > >>
> > >>
> >
>
> http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size
> > >>
> > >>
> > >>>>table_cache
> > >>>>thread_cache_size
> > >>>>tmp_table_size
> > >>>>max_heap_table_size
> > >>>>
> > >>>>but made not much difference.
> > >>
> > >>It is solution for only sql's large result,i think.
> > >>if you doesn't recognize that problem causes large result,
> > >>you should approach other way,too.
> > >>
> > >>regards,
> > >>
> > >>
> > >>ts. tanaka//
> > >>
> > >>2012/5/23 Yu Watanabe <yu.watanabe@stripped>:
> > >>> Also following is the free command result.
> > >>>
> > >>>             total       used       free     shared    buffers
> > cached
> > >>> Mem:       8162380    7843676     318704          0      95632
> >  5970892
> > >>> -/+ buffers/cache:    1777152    6385228
> > >>> Swap:      8032492      23560    8008932
> > >>>
> > >>> Thanks,
> > >>> Yu
> > >>>
> > >>>
> > >>> Yu Watanabe さんは書きました:
> > >>>>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
> > >>>
> > >>
> > >>--
> > >>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
> > >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    http://lists.mysql.com/mysql
> >
> >
>

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