Re: document for mysql performance improvement
If this is a dedicated MySQL box, i would increase the InnoDB buffer pool setting in my.cnf to at least 8GB, 12 would be even better (you did say you have 16GB of ram in the machine I believe). Also, what is the output of: show status like '%tmp%'; JW On Wed, Sep 22, 2010 at 8:01 PM, Vokern vok...@gmail.com wrote: 2010/9/23 Johnny Withers joh...@pixelated.net Can you show us the output of: show status like '%innodb%' JW Sure. mysql show status like '%innodb%'; +---++ | Variable_name | Value | +---++ | Innodb_buffer_pool_pages_data | 262143 | | Innodb_buffer_pool_pages_dirty| 7219 | | Innodb_buffer_pool_pages_flushed | 376090524 | | Innodb_buffer_pool_pages_free | 0 | | Innodb_buffer_pool_pages_misc | 1 | | Innodb_buffer_pool_pages_total| 262144 | | Innodb_buffer_pool_read_ahead_rnd | 385466 | | Innodb_buffer_pool_read_ahead_seq | 1304599| | Innodb_buffer_pool_read_requests | 19253892075| | Innodb_buffer_pool_reads | 142749467 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 3491971805 | | Innodb_data_fsyncs| 32809939 | | Innodb_data_pending_fsyncs| 0 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes| 0 | | Innodb_data_read | 4013196644352 | | Innodb_data_reads | 147753642 | | Innodb_data_writes| 440467519 | | Innodb_data_written | 12643997136896 | | Innodb_dblwr_pages_written| 376090524 | | Innodb_dblwr_writes | 5464581| | Innodb_log_waits | 6599 | | Innodb_log_write_requests | 490350909 | | Innodb_log_writes | 201315186 | | Innodb_os_log_fsyncs | 13605257 | | Innodb_os_log_pending_fsyncs | 0 | | Innodb_os_log_pending_writes | 0 | | Innodb_os_log_written | 319623115776 | | Innodb_page_size | 16384 | | Innodb_pages_created | 6050545| | Innodb_pages_read | 244945432 | | Innodb_pages_written | 376090524 | | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 594325 | | Innodb_row_lock_time_avg | 154| | Innodb_row_lock_time_max | 27414 | | Innodb_row_lock_waits | 3857 | | Innodb_rows_deleted | 2170086| | Innodb_rows_inserted | 550876090 | | Innodb_rows_read | 15529216710| | Innodb_rows_updated | 142880071 | +---++ 42 rows in set (0.00 sec) -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: document for mysql performance improvement
Hi, If its an IO problem the first and easiest thing to do is (probably) look at your disk subsystem. You can easily achieve higher disk IO by increasing the number of disks and implementing something like RAID1+0. Or you can be logical about it and try to determine whether the IO performance is a symptom or a cause. If there are queries that don't have good indexes, add correct indexes is a smarter solution than add disks. Indeed, even the IO usage can be a red herring. I suggest a more systematic approach to the problem, such as Method R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
Can you show us the output of: show status like '%innodb%' JW On Tue, Sep 21, 2010 at 10:11 PM, vokern vok...@gmail.com wrote: And this is the innodb file size, does this matter for performance? $ du -h ibdata* 11G ibdata1 11G ibdata2 11G ibdata3 59G ibdata4 2010/9/22 vokern vok...@gmail.com: This is piece of the setting in my.cnf: set-variable = innodb_buffer_pool_size=4G set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_flush_log_at_trx_commit=2 set-variable = innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G;ibdata4:10G:autoextend key_buffer = 1024M sort_buffer = 1M read_buffer = 1M max_allowed_packet = 1M thread_stack= 192K thread_cache_size = 8 max_heap_table_size = 64M myisam-recover = BACKUP max_connections= 800 query_cache_limit = 1M query_cache_size= 16M the disk: # fdisk -l Disk /dev/sda: 598.0 GB, 597998698496 bytes 255 heads, 63 sectors/track, 72702 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Disk identifier: 0x0004158f from iostat -x: Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.79 309.57 31.06 50.98 1306.74 2860.71 50.80 0.293.59 0.97 7.93 dm-0 0.00 0.000.560.42 4.49 3.40 8.00 0.33 338.96 1.14 0.11 The db is still slow. Thanks for the future helps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: document for mysql performance improvement
2010/9/23 Johnny Withers joh...@pixelated.net Can you show us the output of: show status like '%innodb%' JW Sure. mysql show status like '%innodb%'; +---++ | Variable_name | Value | +---++ | Innodb_buffer_pool_pages_data | 262143 | | Innodb_buffer_pool_pages_dirty| 7219 | | Innodb_buffer_pool_pages_flushed | 376090524 | | Innodb_buffer_pool_pages_free | 0 | | Innodb_buffer_pool_pages_misc | 1 | | Innodb_buffer_pool_pages_total| 262144 | | Innodb_buffer_pool_read_ahead_rnd | 385466 | | Innodb_buffer_pool_read_ahead_seq | 1304599| | Innodb_buffer_pool_read_requests | 19253892075| | Innodb_buffer_pool_reads | 142749467 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 3491971805 | | Innodb_data_fsyncs| 32809939 | | Innodb_data_pending_fsyncs| 0 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes| 0 | | Innodb_data_read | 4013196644352 | | Innodb_data_reads | 147753642 | | Innodb_data_writes| 440467519 | | Innodb_data_written | 12643997136896 | | Innodb_dblwr_pages_written| 376090524 | | Innodb_dblwr_writes | 5464581| | Innodb_log_waits | 6599 | | Innodb_log_write_requests | 490350909 | | Innodb_log_writes | 201315186 | | Innodb_os_log_fsyncs | 13605257 | | Innodb_os_log_pending_fsyncs | 0 | | Innodb_os_log_pending_writes | 0 | | Innodb_os_log_written | 319623115776 | | Innodb_page_size | 16384 | | Innodb_pages_created | 6050545| | Innodb_pages_read | 244945432 | | Innodb_pages_written | 376090524 | | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 594325 | | Innodb_row_lock_time_avg | 154| | Innodb_row_lock_time_max | 27414 | | Innodb_row_lock_waits | 3857 | | Innodb_rows_deleted | 2170086| | Innodb_rows_inserted | 550876090 | | Innodb_rows_read | 15529216710| | Innodb_rows_updated | 142880071 | +---++ 42 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
document for mysql performance improvement
Hello, We are using mysql-5.1 with innodb engine for a web 2.0 application. But we found that the performance is not that good, i.e, the IO load sometime is high, the query is timeout. We run ubuntu server Linux, with apt-get for installing mysql. So is there any good document for improving mysql performance? Thanks. Regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
Yes we have changed some arguments in my.cnf like key_buffer_size, sort_buffer_size etc. 2010/9/21 Machiel Richards machiel.richa...@gmail.com: Good day There is quite a lot of documentation available for MySQL performance management. However, a quick question on this matter. Have you changed any of the default buffer and cache sizes as yet? Regards Machiel -Original Message- From: vokern vok...@gmail.com To: mysql@lists.mysql.com Subject: document for mysql performance improvement Date: Tue, 21 Sep 2010 20:37:49 +0800 Hello, We are using mysql-5.1 with innodb engine for a web 2.0 application. But we found that the performance is not that good, i.e, the IO load sometime is high, the query is timeout. We run ubuntu server Linux, with apt-get for installing mysql. So is there any good document for improving mysql performance? Thanks. Regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
Yep. There's rather extensive documentation on http://www.mysql.com. You'll need to read it and compare to the metrics you're taking off your own server, draw conclusions and apply them to your setup. You *are* pulling metrics, aren't you, and not hoping for some magic wand to make it all happen ? On Tue, Sep 21, 2010 at 2:37 PM, vokern vok...@gmail.com wrote: Hello, We are using mysql-5.1 with innodb engine for a web 2.0 application. But we found that the performance is not that good, i.e, the IO load sometime is high, the query is timeout. We run ubuntu server Linux, with apt-get for installing mysql. So is there any good document for improving mysql performance? Thanks. Regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: document for mysql performance improvement
If its an IO problem the first and easiest thing to do is (probably) look at your disk subsystem. You can easily achieve higher disk IO by increasing the number of disks and implementing something like RAID1+0. What is your current disk configuration? Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
2010/9/21 a.sm...@ukgrid.net: If its an IO problem the first and easiest thing to do is (probably) look at your disk subsystem. You can easily achieve higher disk IO by increasing the number of disks and implementing something like RAID1+0. What is your current disk configuration? The disk is exactly Raid10. The CPU is two 2.5G*4, totally 16G memory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: document for mysql performance improvement
I find this quite good http://www.mysqlperformanceblog.com/ Send your my.cnf and maybe we could look at it and pick anything that would help. Jangita | +254 76 918383 | MSN Y!: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -Original Message- From: vokern [mailto:vok...@gmail.com] Sent: 21 September 2010 2:38 PM To: mysql@lists.mysql.com Subject: document for mysql performance improvement Hello, We are using mysql-5.1 with innodb engine for a web 2.0 application. But we found that the performance is not that good, i.e, the IO load sometime is high, the query is timeout. We run ubuntu server Linux, with apt-get for installing mysql. So is there any good document for improving mysql performance? Thanks. Regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jang...@jangita.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
Quoting vokern vok...@gmail.com: The disk is exactly Raid10. The CPU is two 2.5G*4, totally 16G memory. And how many disks do you have, and what type (SATA/SAS/FC etc) what RPM? To improve IO you can add more disks, or upgrade to faster disks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
Thank you all for the kind helps. I will check them and if still have problems I will come back. 2010/9/21 Machiel Richards machiel.richa...@gmail.com: http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/ http://www.mysql.com/why-mysql/performance/ http://www.debianhelp.co.uk/mysqlperformance.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
2010/9/21 a.sm...@ukgrid.net: Quoting vokern vok...@gmail.com: The disk is exactly Raid10. The CPU is two 2.5G*4, totally 16G memory. And how many disks do you have, and what type (SATA/SAS/FC etc) what RPM? To improve IO you can add more disks, or upgrade to faster disks. Two disks with SAS driver, 15K rpm. BTW, we are running a TTServer before mysql for caching the query, is this better for performance? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
Quoting vokern vok...@gmail.com: Two disks with SAS driver, 15K rpm. Ok so you have fast disks, but with only 2 disks it is normal you will be quite restricted by DISK IO. By adding more in multiples of 2 and stripping across all you achieve RAID1+0 and higher max IO... Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
The disk is exactly Raid10. The CPU is two 2.5G*4, totally 16G memory. Two disks with SAS driver, 15K rpm. RAID-10 with 2 disks? or do you mean RAID0 or RAID1??? Can't see how you would get RAID10, minimum of 6 disks for that, no? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
Mysql tuner is a very useful tool to pull metrics http://blog.mysqltuner.com/ On 09/21/2010 05:48 AM, Jangita wrote: I find this quite good http://www.mysqlperformanceblog.com/ Send your my.cnf and maybe we could look at it and pick anything that would help. Jangita | +254 76 918383 | MSN Y!: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -Original Message- From: vokern [mailto:vok...@gmail.com] Sent: 21 September 2010 2:38 PM To: mysql@lists.mysql.com Subject: document for mysql performance improvement Hello, We are using mysql-5.1 with innodb engine for a web 2.0 application. But we found that the performance is not that good, i.e, the IO load sometime is high, the query is timeout. We run ubuntu server Linux, with apt-get for installing mysql. So is there any good document for improving mysql performance? Thanks. Regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
Quoting Johan De Meersman vegiv...@tuxera.be: Your raid controller is lying to you - you can't have RAID10 with just two disks :-p Don't worry about that, though - it's a good enough config. Good enough? If he is genuinely saturating the disk with IO (as he states the problem is IO) then it isnt good enough. But perhaps that needs a bit more analysis before we really know... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
On Tue, Sep 21, 2010 at 4:03 PM, a.sm...@ukgrid.net wrote: Quoting Johan De Meersman vegiv...@tuxera.be: Your raid controller is lying to you - you can't have RAID10 with just two disks :-p Don't worry about that, though - it's a good enough config. Good enough? If he is genuinely saturating the disk with IO (as he states the problem is IO) then it isnt good enough. But perhaps that needs a bit more analysis before we really know... It takes quite some doing to fill a 15krpm RAID-1 with IO, all the way through all the controller, OS and SQL caches. A lot more investigating is needed before concluding that the symptom is indeed the cause. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: document for mysql performance improvement
Also, mailing list doesn't want to distribute attachments :-) Here's a link to the metrics view I was on about earlier: http://www.tuxera.be/mysqlstats.zip On Tue, Sep 21, 2010 at 4:08 PM, Johan De Meersman vegiv...@tuxera.bewrote: On Tue, Sep 21, 2010 at 4:03 PM, a.sm...@ukgrid.net wrote: Quoting Johan De Meersman vegiv...@tuxera.be: Your raid controller is lying to you - you can't have RAID10 with just two disks :-p Don't worry about that, though - it's a good enough config. Good enough? If he is genuinely saturating the disk with IO (as he states the problem is IO) then it isnt good enough. But perhaps that needs a bit more analysis before we really know... It takes quite some doing to fill a 15krpm RAID-1 with IO, all the way through all the controller, OS and SQL caches. A lot more investigating is needed before concluding that the symptom is indeed the cause. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: document for mysql performance improvement
Hi, Check your slow queries first. Large full scans can cause unwanted disk io. Do you use MyISAM or InnoDB? From your status, you seem to have intensive MyISAM locking. Peter On 09/21/2010 04:10 PM, Johan De Meersman wrote: Also, mailing list doesn't want to distribute attachments :-) Here's a link to the metrics view I was on about earlier: http://www.tuxera.be/mysqlstats.zip On Tue, Sep 21, 2010 at 4:08 PM, Johan De Meersmanvegiv...@tuxera.bewrote: On Tue, Sep 21, 2010 at 4:03 PM,a.sm...@ukgrid.net wrote: Quoting Johan De Meersmanvegiv...@tuxera.be: Your raid controller is lying to you - you can't have RAID10 with just two disks :-p Don't worry about that, though - it's a good enough config. Good enough? If he is genuinely saturating the disk with IO (as he states the problem is IO) then it isnt good enough. But perhaps that needs a bit more analysis before we really know... It takes quite some doing to fill a 15krpm RAID-1 with IO, all the way through all the controller, OS and SQL caches. A lot more investigating is needed before concluding that the symptom is indeed the cause. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
This is piece of the setting in my.cnf: set-variable = innodb_buffer_pool_size=4G set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_flush_log_at_trx_commit=2 set-variable = innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G;ibdata4:10G:autoextend key_buffer = 1024M sort_buffer = 1M read_buffer = 1M max_allowed_packet = 1M thread_stack= 192K thread_cache_size = 8 max_heap_table_size = 64M myisam-recover = BACKUP max_connections= 800 query_cache_limit = 1M query_cache_size= 16M the disk: # fdisk -l Disk /dev/sda: 598.0 GB, 597998698496 bytes 255 heads, 63 sectors/track, 72702 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Disk identifier: 0x0004158f from iostat -x: Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.79 309.57 31.06 50.98 1306.74 2860.71 50.80 0.293.59 0.97 7.93 dm-0 0.00 0.000.560.42 4.49 3.40 8.00 0.33 338.96 1.14 0.11 The db is still slow. Thanks for the future helps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
And this is the innodb file size, does this matter for performance? $ du -h ibdata* 11G ibdata1 11G ibdata2 11G ibdata3 59G ibdata4 2010/9/22 vokern vok...@gmail.com: This is piece of the setting in my.cnf: set-variable = innodb_buffer_pool_size=4G set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_flush_log_at_trx_commit=2 set-variable = innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G;ibdata4:10G:autoextend key_buffer = 1024M sort_buffer = 1M read_buffer = 1M max_allowed_packet = 1M thread_stack = 192K thread_cache_size = 8 max_heap_table_size = 64M myisam-recover = BACKUP max_connections = 800 query_cache_limit = 1M query_cache_size = 16M the disk: # fdisk -l Disk /dev/sda: 598.0 GB, 597998698496 bytes 255 heads, 63 sectors/track, 72702 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Disk identifier: 0x0004158f from iostat -x: Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.79 309.57 31.06 50.98 1306.74 2860.71 50.80 0.29 3.59 0.97 7.93 dm-0 0.00 0.00 0.56 0.42 4.49 3.40 8.00 0.33 338.96 1.14 0.11 The db is still slow. Thanks for the future helps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
