Re: Need help for performance tuning with Mysql

2012-05-30 Thread Prabhat Kumar
)? -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

Re: Need help for performance tuning with Mysql

2012-05-24 Thread Yu Watanabe
Alex Thank you for the advice. Probably, we will put index (key) on both columns. Thanks, Yu Alex Schaft さんは書きました: On 2012/05/24 07:37, Alex Schaft wrote: 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

Re: Need help for performance tuning with Mysql

2012-05-24 Thread Yu Watanabe
Johnny Thank you for the reply. Second, make sure your Innodb buffer pool is allocating as much ram as possible. I'd even go as far as adding another 8gb of ram to the server. The buffer pool setting is going to give you the best performance increase. The problem is mainly on MyISAM engine.

RE: Need help for performance tuning with Mysql

2012-05-24 Thread Rick James
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

Re: Need help for performance tuning with Mysql

2012-05-23 Thread Andrew Moore
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

Re: Need help for performance tuning with Mysql

2012-05-23 Thread Ananda Kumar
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

RE: Need help for performance tuning with Mysql

2012-05-23 Thread 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

Re: Need help for performance tuning with Mysql

2012-05-23 Thread Yu Watanabe
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

Re: Need help for performance tuning with Mysql

2012-05-23 Thread Alex Schaft
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

Re: Need help for performance tuning with Mysql

2012-05-23 Thread Alex Schaft
On 2012/05/24 07:37, Alex Schaft wrote: 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 On second thought, an index on thold_enabled

Re: Need help for performance tuning with Mysql

2012-05-22 Thread Yu Watanabe
Also following is the free command result. total used free sharedbuffers cached Mem: 81623807843676 318704 0 956325970892 -/+ buffers/cache:17771526385228 Swap: 8032492 235608008932 Thanks, Yu Yu

Re: Need help for performance tuning with Mysql

2012-05-22 Thread 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.

Re: Need help for performance tuning with Mysql

2012-05-22 Thread Johnny Withers
I don't see any attachments. First, I would upgrade to 5.5 as 5.0 is very old. The upgrade process is painless. Second, make sure your Innodb buffer pool is allocating as much ram as possible. I'd even go as far as adding another 8gb of ram to the server. The buffer pool setting is going to give

Re: Need help for performance tuning with Mysql

2012-05-22 Thread Yu Watanabe
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 さんは書きました:

Re: Need help for performance tuning with Mysql

2012-05-22 Thread Tsubasa Tanaka
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.