Hello, i hope my request is not spam. I want to ask for advices optimising mysql on the server (VM860) i manage. I will be updating this thread with new data untill mysql is optimisedd. So first day data: mysqld -v Server version: 5.5.36-cll Code (markup): mysqltuner.pl -------- Performance Metrics ------------------------------------------------- [--] Up for: 6d 10h 41m 2s (68M q [122.858 qps], 2M conn, TX: 1596B, RX: 11B) [--] Reads / Writes: 81% / 19% [--] Total buffers: 1.7G global + 18.6M per thread (200 max threads) [OK] Maximum possible memory usage: 5.4G (37% of installed RAM) [OK] Slow queries: 0% (27K/68M) [!!] Highest connection usage: 100% (201/200) [OK] Key buffer size / total MyISAM indexes: 8.0M/1.1G [OK] Key buffer hit rate: 95.0% (576M cached / 28M reads) [OK] Query cache efficiency: 74.0% (38M cached / 52M selects) [!!] Query cache prunes per day: 787070 [OK] Sorts requiring temporary tables: 0% (6K temp sorts / 2M sorts) [!!] Joins performed without indexes: 243830 [!!] Temporary tables created on disk: 32% (1M on disk / 3M total) [OK] Thread cache hit rate: 98% (40K created / 2M connections) [!!] Table cache hit rate: 0% (512 open / 30M opened) [OK] Open file limit used: 2% (918/43K) [OK] Table locks acquired immediately: 99% (22M immediate / 22M locks) [OK] InnoDB buffer pool / data size: 400.0M/300.5M [OK] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries Reduce or eliminate persistent connections to reduce connection usage Increasing the query_cache size over 128M may reduce performance Adjust your join queries to always utilize indexes Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_cache gradually to avoid file descriptor limits Read this before increasing table_cache over 64: * Variables to adjust: max_connections (> 200) wait_timeout (< 28800) interactive_timeout (< 28800) query_cache_size (> 334M) [see warning above] join_buffer_size (> 16.0M, or always use indexes with joins) table_cache (> 512) Code (markup): mysql tuning primer data: http://pastebin.com/ma7Xm0w5 /etc/my.cnf values thread_cache_size=12 max_connections=200 innodb_buffer_pool_size=400M join_buffer_size=16M query_cache_size=334M query_cache_limit=256M table_cache=1024 table_open_cache=512 tmp_table_size=1024M max_heap_table_size=1024M Code (markup): free -m total used free shared buffers cached Mem: 14500 6020 8479 0 0 0 -/+ buffers/cache: 6020 8479 Swap: 0 0 0 Code (markup): so i changed: table_cache to 6024M and restarted mysql. please u have any advices on commands/changes? Thank You
Most likely nobody replied here yet as your post is chaotic and seems like you first need to dig into the MySQL documentation before trying to optimize something (how would Gordon Ramsay say: learn first to walk before you run). * Without knowing the HW details it is impossible to give you exact help (also need to know what else is running) * MySQL uses storage engines - each one optimizes themself differently 1) Try to use InnoDB only (as a minimum, XtraDB or TokuDB optionally if you understand what is happening) 2) With InnoDB use approx 70-80% of available RAM (e.g. substract other daemons that are running like Apache/PHP/etc) for innodb_buffer_pool_size - and forget query_cache_* 3) table_cache=6024M does not make sense, as that directive has nothing to do with bytes 4) Use MariaDB instead of MySQL which is a drop-in replacement of MySQL - it comes with a "saner" prepackaged my.cnf that is more optimized than the one from MySQL (you will just need to set the buffer-pool-size accordingly)
I will need to stay with mysql. Im hosting various clients and new coming and they have various engines i think. I have VPS with 13GB ram burst and 4gb dedi. around 30% ram is used almost permanently...
I keep seeing posts saying to use InnoDB, but my databases query 100 times faster under MyISAM, and InnoDB doesnt support FULLTEXT index... ?
This is second run. mysqltuner.pl >> MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net> >> 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.37-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 5G (Tables: 6985) [--] Data in MRG_MYISAM tables: 21M (Tables: 3) [--] Data in InnoDB tables: 291M (Tables: 979) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [--] Data in MEMORY tables: 3M (Tables: 23) [!!] Total fragmented tables: 789 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 18d 4h 8m 29s (196M q [124.907 qps], 8M conn, TX: 5187B, RX: 30B) [--] Reads / Writes: 81% / 19% [--] Total buffers: 1.9G global + 18.6M per thread (200 max threads) [OK] Maximum possible memory usage: 5.6G (32% of installed RAM) [OK] Slow queries: 0% (17K/196M) [OK] Highest usage of available connections: 57% (115/200) [OK] Key buffer size / total MyISAM indexes: 8.0M/1.5G [OK] Key buffer hit rate: 95.2% (1B cached / 77M reads) [OK] Query cache efficiency: 76.9% (115M cached / 150M selects) [!!] Query cache prunes per day: 755056 [OK] Sorts requiring temporary tables: 0% (30K temp sorts / 8M sorts) [!!] Joins performed without indexes: 658581 [!!] Temporary tables created on disk: 35% (3M on disk / 10M total) [OK] Thread cache hit rate: 99% (66K created / 8M connections) [!!] Table cache hit rate: 0% (512 open / 91M opened) [OK] Open file limit used: 2% (959/43K) [OK] Table locks acquired immediately: 99% (56M immediate / 56M locks) [OK] InnoDB buffer pool / data size: 400.0M/291.3M [OK] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Increasing the query_cache size over 128M may reduce performance Adjust your join queries to always utilize indexes Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_cache gradually to avoid file descriptor limits Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C Variables to adjust: query_cache_size (> 512M) [see warning above] join_buffer_size (> 16.0M, or always use indexes with joins) table_cache (> 512) Code (markup): mysql tuning primer: http://pastebin.com/qmA89REM /etc/my.cnf max_connections=200 max_user_connections=18 innodb_file_per_table=1 local-infile=0 max_allowed_packet=268435456 open_files_limit=43762 ft_min_word_len=3 thread_cache_size=12 max_connections=200 innodb_buffer_pool_size=400M join_buffer_size=16M query_cache_size=512M query_cache_limit=512M table_cache=6024 table_open_cache=512 tmp_table_size=1024M max_heap_table_size=1024M Code (markup): free -m total used free shared buffers cached Mem: 17500 7031 10468 0 0 0 -/+ buffers/cache: 7031 10468 Swap: 0 0 0 Code (markup): i did not changed anything now as it appears quite ok to me