Re: my.cnf optimization
Here's all the buffer variables: mysql show variables like '%buffer%'\G *** 1. row *** Variable_name: bulk_insert_buffer_size Value: 8388608 *** 2. row *** Variable_name: innodb_buffer_pool_awe_mem_mb Value: 0 *** 3. row *** Variable_name: innodb_buffer_pool_size Value: 8388608 *** 4. row *** Variable_name: innodb_log_buffer_size Value: 1048576 *** 5. row *** Variable_name: join_buffer_size Value: 131072 *** 6. row *** Variable_name: key_buffer_size Value: 402653184 *** 7. row *** Variable_name: myisam_sort_buffer_size Value: 67108864 *** 8. row *** Variable_name: net_buffer_length Value: 16384 *** 9. row *** Variable_name: preload_buffer_size Value: 32768 *** 10. row *** Variable_name: read_buffer_size Value: 67104768 *** 11. row *** Variable_name: read_rnd_buffer_size Value: 67104768 *** 12. row *** Variable_name: sort_buffer_size Value: 67108856 12 rows in set (0.00 sec) I'll bump innodb_buffer_pool_size to 2G and see how that goes. Thanks for the tips, if there's additional innodb tuning parameters folks tend to hit first I'd be glad to try them as well. -- Ryan Schwartz On Sep 4, 2008, at 8:16 AM, Johnny Withers wrote: If you do have a fair about of innodb tables you can increase performance by increasing the size of innodb_buffer_pool_size. According to your status output, you are currently using the entire buffer pool: *** 137. row *** Variable_name: Innodb_buffer_pool_pages_free Value: 0 It seems to be set small anyway: Variable_name: Innodb_buffer_pool_pages_data Value: 501 It also seems that you do have alot of innodb data: *** 151. row *** Variable_name: Innodb_data_read Value: 27743085907968 Again, i don't know what you have innodb_buffer_pool_size set to, but you have plenty of RAM, I'd set it to about 4.5GB and see if that helps. I also don't know mucha bout OS X and your hardware.. is it 64bit? If it is not 64bit, you probably can't use 4.5GB as the size of your buffer pool. -johnny On 9/3/08, Ryan Schwartz [EMAIL PROTECTED] wrote: All, We're seeing a huge surge in our qps and I'd like to make sure we're tuned as well as we can be. I'm wondering if I've got some variables maybe set too large (is that even possible?) ? We do have a fair bit of innodb, so perhaps I should add some non-defaults there, but I'm not so sure where to start with that. Hardware is an Apple Xserve, 2x Quad-Core Intel @ 3Ghz, 32GB RAM, 3x 280 GB SAS drives in Raid-5 config, OS is Mac OS X 10.5.4 and here's my my.cnf: [billie:~] admin$ egrep -v '^$|^#' /etc/my.cnf [client] port= 3306 socket = /var/mysql/mysql.sock [mysqld] port= 3306 socket = /var/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 50M table_cache = 2048 sort_buffer_size = 64M read_buffer_size = 64M read_rnd_buffer_size = 64M myisam_sort_buffer_size = 64M thread_cache_size = 100 query_cache_size = 64M thread_concurrency = 16 skip-thread-priority max_connections = 750 old-passwords innodb_file_per_table innodb_flush_log_at_trx_commit=1 sync_binlog=1 log-slow-queries long_query_time=2 log_queries_not_using_indexes log-bin=mysql-bin server-id = 4 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout SHOW STATUS\G output follows my sig below... My devs are adding indexes where the slow query log is pointing them, but any suggestions on how better to tune things up would be much appreciated. I'm not sure what else to tune here but we're getting bursts of 1200+ queries per second regularly and seeing things slow down significantly. Best, -- Ryan Schwartz mysql SHOW STATUS\G *** 1. row *** Variable_name: Aborted_clients Value: 1656 *** 2. row *** Variable_name: Aborted_connects Value: 3 *** 3. row *** Variable_name: Binlog_cache_disk_use Value: 276 *** 4. row
Re: my.cnf optimization
On Thu, Sep 4, 2008 at 12:15 AM, Ryan Schwartz [EMAIL PROTECTED] wrote: We're seeing a huge surge in our qps and I'd like to make sure we're tuned as well as we can be. I'm wondering if I've got some variables maybe set too large (is that even possible?) ? We do have a fair bit of innodb, so perhaps I should add some non-defaults there, but I'm not so sure where to start with that. It's not really possible to give good tuning advice without knowing about how you use the database and how your machine is currently responding. However, you can get some good started advice from the sample my.cnf files that come with MySQL and you can get a copy of the High Performance MySQL book for a good primer on what to look for. You can also find conference presentations by Peter Zaitsev that summarize some of the advice in the book. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf optimization
Ryan Schwartz wrote mysql show variables like '%buffer%'\G *** 1. row *** *** 3. row *** Variable_name: innodb_buffer_pool_size Value: 8388608 *** 4. row *** Variable_name: innodb_log_buffer_size Value: 1048576 I'll bump innodb_buffer_pool_size to 2G and see how that goes. Thanks for the tips, if there's additional innodb tuning parameters folks tend to hit first I'd be glad to try them as well. -- Ryan Schwartz Hi ryan. As pointed by Johnny, it is difficult to give optimization advise without exactly knowing the performance of your machine. I'm assuming you are using the machine as Database Server and not running application (Web/other) on the same. (And you are using InnoDB as engine) I would suggest keeping innodb_buffer_pool_size pretty high (+20G) Please read up here : http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/ Also if possible get a copy of High performance MySQL and go through it as it covers many good techniques for high performance MySQL setup. Some of the default InnoDB settings are horribly wrong from high performance point of view. Can you post your complete my.cnf on pastebin or somewhere ? Regards, Ranjeet Walunj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf optimization
On Sep 4, 2008, at 1:48 PM, Ranjeet Walunj wrote: Hi ryan. As pointed by Johnny, it is difficult to give optimization advise without exactly knowing the performance of your machine. I'm assuming you are using the machine as Database Server and not running application (Web/other) on the same. (And you are using InnoDB as engine) I would suggest keeping innodb_buffer_pool_size pretty high (+20G) This is a dedicated MySQL server - nothing else running on it at all, so all that RAM is up for grabs. Mysqld is running in 64 bits, and after bumping innodb_buffer_pool_size to 4G our performance concerns are completely gone - I'll ramp that up after doing a bit more research on InnoDB tuning. Please read up here : http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/ Also if possible get a copy of High performance MySQL and go through it as it covers many good techniques for high performance MySQL setup. I'll have to crack open my copy - haven't read through it in a while, and quite honestly I had forgot to make any adjustments on the InnoDB side of things because when I inherited the old MySQL server we were on the devs were mostly using MyISAM tables. Some of the default InnoDB settings are horribly wrong from high performance point of view. Can you post your complete my.cnf on pastebin or somewhere ? http://pastebin.com/m2ebec4f6 includes everything in my.cnf but comments and blank lines, SHOW STATUS\G, SHOW INNODB STATUS\G, AND SHOW VARIABLES\G All your help is much appreciated - I just wonder if there's not been a simple script set up by someone to autogen my.cnf based on system variables like available RAM, etc? Surely there's some general recommendations depending on those specific system things, rather than just copy my-huge.cnf and modify... -- Ryan Schwartz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf optimization
On Thu, Sep 4, 2008 at 3:23 PM, Ryan Schwartz [EMAIL PROTECTED] wrote: I'll have to crack open my copy - haven't read through it in a while If you have the first edition, I recommend getting the newer one. It has a lot more tuning info. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my.cnf optimization
All, We're seeing a huge surge in our qps and I'd like to make sure we're tuned as well as we can be. I'm wondering if I've got some variables maybe set too large (is that even possible?) ? We do have a fair bit of innodb, so perhaps I should add some non-defaults there, but I'm not so sure where to start with that. Hardware is an Apple Xserve, 2x Quad-Core Intel @ 3Ghz, 32GB RAM, 3x 280 GB SAS drives in Raid-5 config, OS is Mac OS X 10.5.4 and here's my my.cnf: [billie:~] admin$ egrep -v '^$|^#' /etc/my.cnf [client] port= 3306 socket = /var/mysql/mysql.sock [mysqld] port= 3306 socket = /var/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 50M table_cache = 2048 sort_buffer_size = 64M read_buffer_size = 64M read_rnd_buffer_size = 64M myisam_sort_buffer_size = 64M thread_cache_size = 100 query_cache_size = 64M thread_concurrency = 16 skip-thread-priority max_connections = 750 old-passwords innodb_file_per_table innodb_flush_log_at_trx_commit=1 sync_binlog=1 log-slow-queries long_query_time=2 log_queries_not_using_indexes log-bin=mysql-bin server-id = 4 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout SHOW STATUS\G output follows my sig below... My devs are adding indexes where the slow query log is pointing them, but any suggestions on how better to tune things up would be much appreciated. I'm not sure what else to tune here but we're getting bursts of 1200+ queries per second regularly and seeing things slow down significantly. Best, -- Ryan Schwartz mysql SHOW STATUS\G *** 1. row *** Variable_name: Aborted_clients Value: 1656 *** 2. row *** Variable_name: Aborted_connects Value: 3 *** 3. row *** Variable_name: Binlog_cache_disk_use Value: 276 *** 4. row *** Variable_name: Binlog_cache_use Value: 6416113 *** 5. row *** Variable_name: Bytes_received Value: 134 *** 6. row *** Variable_name: Bytes_sent Value: 70104 *** 7. row *** Variable_name: Com_admin_commands Value: 0 *** 8. row *** Variable_name: Com_alter_db Value: 0 *** 9. row *** Variable_name: Com_alter_table Value: 0 *** 10. row *** Variable_name: Com_analyze Value: 0 *** 11. row *** Variable_name: Com_backup_table Value: 0 *** 12. row *** Variable_name: Com_begin Value: 0 *** 13. row *** Variable_name: Com_call_procedure Value: 0 *** 14. row *** Variable_name: Com_change_db Value: 0 *** 15. row *** Variable_name: Com_change_master Value: 0 *** 16. row *** Variable_name: Com_check Value: 0 *** 17. row *** Variable_name: Com_checksum Value: 0 *** 18. row *** Variable_name: Com_commit Value: 0 *** 19. row *** Variable_name: Com_create_db Value: 0 *** 20. row *** Variable_name: Com_create_function Value: 0 *** 21. row *** Variable_name: Com_create_index Value: 0 *** 22. row *** Variable_name: Com_create_table Value: 0 *** 23. row *** Variable_name: Com_create_user Value: 0 *** 24. row *** Variable_name: Com_dealloc_sql Value: 0 *** 25. row *** Variable_name: Com_delete Value: 0 *** 26. row *** Variable_name: Com_delete_multi Value: 0 *** 27. row *** Variable_name: Com_do Value: 0 *** 28. row *** Variable_name: Com_drop_db Value: 0 *** 29. row *** Variable_name:
Re: my.cnf optimization question ..
On Tue, Sep 11, 2001 at 03:17:47PM -0500, Weslee Bilodeau wrote: They average around 500 connections/second at any given time, two have a master/slave setup. Connections/sec or Queries/sec? That's a lot of connections per second? Can you use persistent connections? It would save a lot of overhead. Half and half, at the moment .. We had a problem that when we connected to multiple database servers from PHP using persistant connections, it would get confused. By confused I meant 1.) It would 'leak' MySQL connections. Somehow it 'forgot' it had an open connection. 2.) It would pick the wrong server. Each server has specific information on it, in seperate database/tables. The persistant connection code would actually hand back the wrong database handle and the queries would fail. We had to disable persistant connections because of this. Ouch. I hadn't heard about those problems before. It's probably worth testing again, 'cause it will give you a nice boost--especially at that connection rate. Our current my.cnf file: [mysqld] skip-locking set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=400 set-variable= sort_buffer=10M set-variable= record_buffer=10M set-variable= thread_cache=8 You might increase the thread_cache quite a bit. It'll bypass the need to create/destroy threads all the time. It may not give you a big improvement, but it can't hurt. The reason I'm asking is because of the MySQL page on Linux-specific notes. Which basically said you can have lots of threads, providing your key-cache is low. Wonder what that really means... or at least what the rationale behind it is. Have you experimented much with they key_buffer? Any noticeable difference if you double it or cut it in half? I'm trying to get as many threads as I can , and get MySQL to be ram-happy for speed as well. A good plan. ;-) Any changes for this many connections/ram that would be suggested? How are things looking in SHOW STATUS? Any red flags that you've noticed? Any slow queries? Slow queries are actually watched with a fine-tooth comb. I catch anything slow, I first smack the programmer along side the head, then throw Paul's book at them. I attempt to optimize it where possible, or just drop thier table and force them to do it over again. Hahhahahah... Well, that's good to hear, I suppose. The queries themselves seem to be pretty well optimized, I just wanna make sure the config I've got can scale. :) I know the feeling. I'm used to servers with a few hundered connections, with 512 MB of ram .. Not what they want, which is a few thousand, with 2gb of ram. Having not broken the 1,000 mark myself, I don't have a lot else here to say. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 7 days, processed 146,377,980 queries (241/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: my.cnf optimization question ..
They average around 500 connections/second at any given time, two have a master/slave setup. Connections/sec or Queries/sec? That's a lot of connections per second? Can you use persistent connections? It would save a lot of overhead. Half and half, at the moment .. We had a problem that when we connected to multiple database servers from PHP using persistant connections, it would get confused. By confused I meant 1.) It would 'leak' MySQL connections. Somehow it 'forgot' it had an open connection. 2.) It would pick the wrong server. Each server has specific information on it, in seperate database/tables. The persistant connection code would actually hand back the wrong database handle and the queries would fail. We had to disable persistant connections because of this. We last tested about 5 months ago, so the problem could very well be gone. It was however a true pain in the rear to replicate. Right now, persistant connections are I really hope, but unsure. Care to share the config file? With that much RAM, there are probably some things you can tweak to make optimal use of the 2GB RAM. But if you've done a lot of that, I won't bother pointing them out (obviously). Our current my.cnf file: [mysqld] skip-locking set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=400 set-variable= sort_buffer=10M set-variable= record_buffer=10M set-variable= thread_cache=8 set-variable= thread_concurrency=4 set-variable= myisam_sort_buffer_size=64M set-variable= max_connections=3072 set-variable= open_files_limit=6144 set-variable= long_query_time=4 tmpdir=/data/tmp log-bin user=mysql binlog-do-db=blahblahblah binlog-do-db=blahblahblahblah server-id=1 log=/usr/local/var/mysqld.log log-slow-queries=/usr/local/var/slow-queries.log pid-file=/usr/local/var/mysqld.pid [mysql] no-auto-rehash [isamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout [safe_mysqld] err-log=/usr/local/var/mysqld.err The reason I'm asking is because of the MySQL page on Linux-specific notes. Which basically said you can have lots of threads, providing your key-cache is low. I'm trying to get as many threads as I can , and get MySQL to be ram-happy for speed as well. Any changes for this many connections/ram that would be suggested? How are things looking in SHOW STATUS? Any red flags that you've noticed? Any slow queries? Slow queries are actually watched with a fine-tooth comb. I catch anything slow, I first smack the programmer along side the head, then throw Paul's book at them. I attempt to optimize it where possible, or just drop thier table and force them to do it over again. The queries themselves seem to be pretty well optimized, I just wanna make sure the config I've got can scale. :) I'm used to servers with a few hundered connections, with 512 MB of ram .. Not what they want, which is a few thousand, with 2gb of ram. Jeremy Thanks for any help, Weslee - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
my.cnf optimization question ..
Basic (maybe?) question on some optimal variables for MySQLd's my.cnf configuration .. Right now, I have three MySQL servers, each with 2 GB ram, dual-CPU P3 1ghz. They average around 500 connections/second at any given time, two have a master/slave setup. MySQL is prettty much the only thing running, nice'd at -20 on a Patched Linux 2.4.9 along w/patched glibc 2.2.4 (LinuxThreads patches). Its run smoothly when I tested it w/~4000 connections and didn't skip a beat. The question basically goes with, I'm expecting to get around 1-2,000 connections/second on average on the systems, using a modified 'my-huge.cnf' from the distribution. No InnoDB, BDB, or overly huge blobs being used. On average each query joins w/about 4 tables on primary/unique indexes where possible. Any changes for this many connections/ram that would be suggested? Thanks, Weslee - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: my.cnf optimization question ..
On Mon, Sep 10, 2001 at 04:54:57PM -0500, Weslee Bilodeau wrote: Basic (maybe?) question on some optimal variables for MySQLd's my.cnf configuration .. Right now, I have three MySQL servers, each with 2 GB ram, dual-CPU P3 1ghz. Nice. :-) They average around 500 connections/second at any given time, two have a master/slave setup. Connections/sec or Queries/sec? That's a lot of connections per second? Can you use persistent connections? It would save a lot of overhead. MySQL is prettty much the only thing running, nice'd at -20 on a Patched Linux 2.4.9 along w/patched glibc 2.2.4 (LinuxThreads patches). Its run smoothly when I tested it w/~4000 connections and didn't skip a beat. The question basically goes with, I'm expecting to get around 1-2,000 connections/second on average on the systems, using a modified 'my-huge.cnf' from the distribution. Care to share the config file? With that much RAM, there are probably some things you can tweak to make optimal use of the 2GB RAM. But if you've done a lot of that, I won't bother pointing them out (obviously). On average each query joins w/about 4 tables on primary/unique indexes where possible. Any changes for this many connections/ram that would be suggested? How are things looking in SHOW STATUS? Any red flags that you've noticed? Any slow queries? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 4 days, processed 103,895,494 queries (249/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
