Cent OS 5.10 MySQL optimization

johnchristy

• April 22, 2014 07:41

hi guyz I ran MySQL tuner and here is my result : >> MySQLTuner 1.3.0 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [OK] Currently running supported MySQL version 5.5.36-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 15M (Tables: 163) [--] Data in InnoDB tables: 10M (Tables: 603) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [OK] Total fragmented tables: 0 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 5s (23 q [4.600 qps], 6 conn, TX: 21K, RX: 1K) [--] Reads / Writes: 92% / 8% [--] Total buffers: 684.0M global + 6.4M per thread (151 max threads) [!!] Maximum possible memory usage: 1.6G (109% of installed RAM) [OK] Slow queries: 0% (0/23) [OK] Highest usage of available connections: 0% (1/151) [OK] Key buffer size / total MyISAM indexes: 256.0M/2.9M [!!] Key buffer hit rate: 88.9% (9 cached / 1 reads) [!!] Query cache efficiency: 0.0% (0 cached / 12 selects) [OK] Query cache prunes per day: 0 [OK] Temporary tables created on disk: 0% (0 on disk / 2 total) [OK] Thread cache hit rate: 83% (1 created / 6 connections) [OK] Table cache hit rate: 82% (33 open / 40 opened) [OK] Open file limit used: 0% (32/3K) [OK] Table locks acquired immediately: 100% (45 immediate / 45 locks) [OK] InnoDB buffer pool / data size: 256.0M/10.7M [OK] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: MySQL started within last 24 hours - recommendations may be inaccurate Reduce your overall MySQL memory footprint for system stability Enable the slow query log to troubleshoot bad queries Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** query_cache_limit (> 1M, or use smaller result sets)
my.cnf : [myisamchk] write_buffer=2M sort_buffer_size=128M key_buffer=128M read_buffer=2M [mysqld] federated innodb_file_per_table=1 default-storage-engine=MyISAM thread_concurrency=2 local-infile=0 innodb_log_file_size=64M table_cache=128 tmpdir="/dev/shm" innodb_log_buffer_size=8M innodb_additional_mem_pool_size=20M innodb_buffer_pool_size=256M innodb_flush_log_at_trx_commit=1 read_rnd_buffer_size=4M skip-external-locking innodb_data_home_dir="/var/lib/mysql/" thread_cache_size=8 innodb_log_group_home_dir="/var/lib/mysql/" sort_buffer_size=1M key_buffer=256M innodb_data_file_path="ibdata1:10M:autoextend" innodb_lock_wait_timeout=50 max_allowed_packet=1M query_cache_size=128M myisam_sort_buffer_size=64M read_buffer_size=1M port=3306 open_files_limit=3608 [isamchk] write_buffer=2M sort_buffer_size=128M key_buffer=128M read_buffer=2M [mysqlhotcopy] interactive-timeout
My VPS RAM is 1.5, can someone point me to what are the changes i should make to my my.cnf? I use prestashop/magento etc and need good mysql performance Thanks

• 

  cPanelMichael

  • April 22, 2014 17:42

  Please ensure you let MySQL run for at least 24 hours before running the tuner to allow for accurate results. Thank you.

  0
• 

  johnchristy

  • April 24, 2014 10:41

  [quote="cPanelMichael, post: 1627412">Please ensure you let MySQL run for at least 24 hours before running the tuner to allow for accurate results. Thank you.
  Hello Michael, Kindly refer this log : >> MySQLTuner 1.3.0 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [OK] Currently running supported MySQL version 5.5.36-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 15M (Tables: 163) [--] Data in InnoDB tables: 10M (Tables: 603) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 2 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 1d 21h 11m 29s (76K q [0.468 qps], 6K conn, TX: 64M, RX: 8M) [--] Reads / Writes: 53% / 47% [--] Total buffers: 684.0M global + 6.4M per thread (151 max threads) [!!] Maximum possible memory usage: 1.6G (109% of installed RAM) [OK] Slow queries: 0% (0/76K) [OK] Highest usage of available connections: 5% (8/151) [OK] Key buffer size / total MyISAM indexes: 256.0M/3.0M [OK] Key buffer hit rate: 99.0% (81K cached / 849 reads) [OK] Query cache efficiency: 86.3% (41K cached / 48K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 757 sorts) [OK] Temporary tables created on disk: 21% (421 on disk / 1K total) [OK] Thread cache hit rate: 99% (8 created / 6K connections) [!!] Table cache hit rate: 4% (128 open / 3K opened) [OK] Open file limit used: 3% (137/3K) [OK] Table locks acquired immediately: 99% (16K immediate / 16K locks) [OK] InnoDB buffer pool / data size: 256.0M/10.7M [OK] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Reduce your overall MySQL memory footprint for system stability Increase table_cache gradually to avoid file descriptor limits Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** table_cache (> 128)
  

  0

Please sign in to leave a comment.