Hi, I need you to help me in improving performance of mysql. I have a very busy site with lots of SQL quires. I have Intel Xeon server with 24GB of RAM (Linux server). Below are the errors (Marked RED) in my phpmyadmin sql Status Slow_queries 287 Handler_read_rnd 2,311.11 M Handler_read_rnd_next 3,398.82 M Qcache_lowmem_prunes 1,649 k Created_tmp_disk_tables 977 k Sort_merge_passes 16 k Opened_tables 81 k Table_locks_waited 11 M Below is my my.cnf [mysqld] max_connections = 7000 long_query_time = 10 safe-show-database key_buffer = 226M myisam_sort_buffer_size = 126M join_buffer_size = 32M read_buffer_size = 32M sort_buffer_size = 32M table_cache = 9999 thread_cache_size = 512 interactive_timeout = 60 wait_timeout = 60 connect_timeout = 60 max_allowed_packet = 4M max_connect_errors = 10 query_cache_limit = 10M query_cache_size = 10M query_cache_type = 1 tmp_table_size = 512M safe-show-database skip-innodb tmpdir = /tmpfs [mysqld_safe] open_files_limit = 8000 [mysqldump] quick max_allowed_packet = 4M [myisamchk] key_buffer = 128M sort_buffer = 64M read_buffer = 64M write_buffer = 64M [mysqlhotcopy] interactive-timeout Now what settings do I need to change? Thanks
Get mysqltuner(http://blog.mysqltuner.com/) and run it after 24hours, it'll give you a pretty good idea on what needs to be changed
Hi Sarsh, Thanks for your help. I run the script and got the result below [--] Up for: 6d 20h 34m 31s (228M q [385.355 qps], 9M conn, TX: 613M, RX: 2B) [--] Reads / Writes: 50% / 50% [--] Total buffers: 262.0M global + 96.4M per thread (7000 max threads) [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability [!!] Maximum possible memory usage: 659.5G (5548% of installed RAM) [OK] Slow queries: 0% (353/228M) [OK] Highest usage of available connections: 3% (255/7000) [OK] Key buffer size / total MyISAM indexes: 226.0M/11.2M [OK] Key buffer hit rate: 100.0% (9B cached / 342K reads) [OK] Query cache efficiency: 82.8% (145M cached / 176M selects) [!!] Query cache prunes per day: 318207 [OK] Sorts requiring temporary tables: 0% (23K temp sorts / 7M sorts) [OK] Temporary tables created on disk: 12% (1M on disk / 8M total) [OK] Thread cache hit rate: 99% (255 created / 9M connections) [!!] Table cache hit rate: 0% (332 open / 111K opened) [OK] Open file limit used: 1% (472/35K) [!!] Table locks acquired immediately: 77% Now what should I really need to change?
ohh thanks...got it Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** query_cache_size (> 10M) table_cache (> 9999)