Skip to main content

MySQL optimization

cobysan
Hello, Looking into optimize our DB (mysql, mainly isam, 165 tables, 100M rows, 30 GB, queries per second avg: 251.118) tuning-primer results - -- MYSQL PERFORMANCE TUNING PRIMER -- - By: Matthew Montgomery - MySQL Version 5.1.73-log x86_64 Uptime = 28 days 19 hrs 20 min 26 sec Avg. qps = 249 Total Questions = 621945607 Threads Connected = 18 Server has been running for over 48hrs. It should be safe to follow these recommendations To find out more information on how each of these runtime variables effects performance visit: [url=http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html]MySQL :: MySQL 5.1 Reference Manual :: 5.1.4 Server System Variables Visit > MySQLTuner 1.2.0 - Major Hayden >> Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major >> Run with '--help' for additional options and output filtering -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.73-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 25G (Tables: 475) [--] Data in InnoDB tables: 2G (Tables: 21) [!!] Total fragmented tables: 55 - Removed - -------- Performance Metrics ------------------------------------------------- [--] Up for: 28d 19h 40m 22s (622M q [249.897 qps], 18M conn, TX: 416B, RX: 123B) [--] Reads / Writes: 84% / 16% [--] Total buffers: 4.2G global + 24.4M per thread (600 max threads) [!!] Maximum possible memory usage: 18.4G (118% of installed RAM) [OK] Slow queries: 0% (2K/622M) [OK] Highest usage of available connections: 65% (390/600) [OK] Key buffer size / total MyISAM indexes: 2.0G/6.4G [OK] Key buffer hit rate: 99.7% (42B cached / 108M reads) [OK] Query cache efficiency: 32.6% (169M cached / 519M selects) [!!] Query cache prunes per day: 16985 [OK] Sorts requiring temporary tables: 0% (22K temp sorts / 10M sorts) [!!] Joins performed without indexes: 7245 [OK] Temporary tables created on disk: 25% (5M on disk / 22M total) [OK] Thread cache hit rate: 99% (24K created / 18M connections) [!!] Table cache hit rate: 0% (600 open / 414K opened) [OK] Open file limit used: 12% (1K/8K) [OK] Table locks acquired immediately: 98% (437M immediate / 441M locks) [!!] InnoDB data size / buffer pool: 2.3G/1.5G -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Reduce your overall MySQL memory footprint for system stability Increasing the query_cache size over 128M may reduce performance Adjust your join queries to always utilize indexes Increase table_cache gradually to avoid file descriptor limits Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** query_cache_size (> 156M) [see warning above] join_buffer_size (> 128.0K, or always use indexes with joins) table_cache (> 4096) innodb_buffer_pool_size (>= 2G)
Any help will be greatly appreciated ! Thank you

Comments

4 comments

Date Votes
  • cPanelMichael
    Hello :) I just wanted to point out that MySQL 5.1 is end of life: [url=http://blog.cpanel.net/mysql-5-1-end-of-life/]MySQL 5.1 End Of Life | cPanel Blog Thank you.
    0
  • thinkbot
    can you run mysqltuner.pl and post the output plus, add contents of my.cnf [COLOR="silver">- - - Updated - - - innodb_buffer_pool_size= 2.5G max_connections = 400 sort_buffer_size=256K read_buffer_size=128K read_rnd_buffer_size=256K and restart
    0
  • cobysan
    hi, don't you think these are too low values for the buffers ? our db server is AMD Dual x8 Opteron 6128, 8GB RAM, CentOs 6 - 64Bit current my.cnf - max_connections = 600 safe-show-database key_buffer = 2G max_allowed_packet = 64M table_cache = 4096 sort_buffer_size = 8M read_buffer_size = 8M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M query_cache_size = 156M tmp_table_size = 512M max_heap_table_size = 512M thread_cache_size = 32 wait_timeout = 120 connect_timeout = 10 interactive_timeout = 600 innodb_buffer_pool_size = 1536M Thank you for your help
    0
  • cobysan
    Hi Your suggestion works great ! thank you so much - New mysqltuner results >> MySQLTuner 1.2.0 - Major Hayden > Bug reports, feature requests, and downloads at /http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.73-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 26G (Tables: 476) [--] Data in InnoDB tables: 2G (Tables: 22) [!!] Total fragmented tables: 42 -------- Performance Metrics ------------------------------------------------- [--] Up for: 3d 14h 45m 44s (80M q [259.191 qps], 2M conn, TX: 49B, RX: 16B) [--] Reads / Writes: 82% / 18% [--] Total buffers: 5.2G global + 1.0M per thread (400 max threads) [OK] Maximum possible memory usage: 5.5G (35% of installed RAM) [OK] Slow queries: 0% (99/80M) [!!] Highest connection usage: 88% (354/400) [OK] Key buffer size / total MyISAM indexes: 2.0G/6.1G [OK] Key buffer hit rate: 99.7% (4B cached / 12M reads) [OK] Query cache efficiency: 30.4% (20M cached / 66M selects) [!!] Query cache prunes per day: 53194 [OK] Sorts requiring temporary tables: 8% (124K temp sorts / 1M sorts) [OK] Temporary tables created on disk: 25% (815K on disk / 3M total) [OK] Thread cache hit rate: 99% (2K created / 2M connections) [!!] Table cache hit rate: 1% (680 open / 51K opened) [OK] Open file limit used: 13% (1K/8K) [OK] Table locks acquired immediately: 98% (56M immediate / 57M locks) [OK] InnoDB data size / buffer pool: 2.0G/2.5G -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Reduce or eliminate persistent connections to reduce connection usage Increasing the query_cache size over 128M may reduce performance Increase table_cache gradually to avoid file descriptor limits Variables to adjust: max_connections (> 400) wait_timeout (< 120) interactive_timeout (< 400) query_cache_size (> 156M) [see warning above] table_cache (> 4096)
    thank you Coby
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post