Re: How to extend innodb files?
2010/9/28 Jangita jang...@jangita.com: I do not think there is anything wrong with having one huge file is there? We have one innodb file of 85GB on ext3. Is there? but how about the problem on the file has been increasing continuously? 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: MySQL Community Server 5.1.51 has been released
Yeah I have been using the latest 5.1.51 one. # ./mysqld -V 100928 17:07:55 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead. ./mysqld Ver 5.1.51-log for pc-linux-gnu on i686 (MySQL Community Server (GPL)) Thanks! 2010/9/28 Karen Langford karen.langf...@oracle.com: Dear MySQL users, MySQL Community Server 5.1.51, a new version of the popular Open Source Database Management System, has been released. MySQL 5.1.51 is recommended for use on production systems. For an overview of what's new in MySQL 5.1, please see http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to extend innodb files?
2010/9/28 Krishna Chandra Prajapati prajapat...@gmail.com: Hi Vokern, I suggest to have a single ibdata1 file and use innodb_file_per_table to have multiple .ibd tables. Can I upgrade to innodb_file_per_table smoothly? thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to extend innodb files?
Hello, Currently I have the setting: innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G;ibdata4:10G:autoextend Because the last file of ibdata4 is very large (more than 50G), if I want extend the data to more files, for example, ibdata5, ibdata6... how to do it? 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
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
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
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
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