Hello! my dedicated 16gb ram/ i7 cpu i starting to acting strange! mysql takes to much cpu until server stop react, about fragments tables i used optimize etcetc etc but number keeps going bigger mysqltuner report Code: -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.31 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 39M (Tables: 620) [--] Data in InnoDB tables: 60M (Tables: 1297) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [--] Data in MEMORY tables: 0B (Tables: 65) [!!] Total fragmented tables: 1300 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 11s (82 q [7.455 qps], 12 conn, TX: 587K, RX: 10K) [--] Reads / Writes: 96% / 4% [--] Total buffers: 1.4G global + 10.3M per thread (151 max threads) [OK] Maximum possible memory usage: 2.9G (24% of installed RAM) [OK] Slow queries: 0% (0/82) [OK] Highest usage of available connections: 1% (2/151) [OK] Key buffer size / total MyISAM indexes: 1.0G/22.2M [!!] Key buffer hit rate: 93.0% (759 cached / 53 reads) [!!] Query cache efficiency: 4.5% (3 cached / 67 selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 27 sorts) [!!] Temporary tables created on disk: 43% (20 on disk / 46 total) [OK] Thread cache hit rate: 83% (2 created / 12 connections) [OK] Table cache hit rate: 85% (40 open / 47 opened) [OK] Open file limit used: 3% (40/1K) [OK] Table locks acquired immediately: 99% (109 immediate / 110 locks) [!!] Connections aborted: 16% [OK] InnoDB data size / buffer pool: 60.8M/128.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Enable the slow query log to troubleshoot bad queries When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Your applications are not closing MySQL connections properly Variables to adjust: query_cache_limit (> 6M, or use smaller result sets) tmp_table_size (> 128M) max_heap_table_size (> 128M) my.cnf Code: [mysqld] # Settings user and group are ignored when systemd is used (fedora >= 15). # If you need to run mysqld under different user or group, # customize your systemd unit file for mysqld according to the user=mysql datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Semisynchronous Replication # uncomment next line on MASTER ;plugin-load=rpl_semi_sync_master=semisync_master.so # uncomment next line on SLAVE ;plugin-load=rpl_semi_sync_slave=semisync_slave.so # Others options for Semisynchronous Replication ;rpl_semi_sync_master_enabled=1 ;rpl_semi_sync_master_timeout=10 ;rpl_semi_sync_slave_enabled=1 ;performance_schema query_cache_limit= 6M query_cache_size= 96M tmp_table_size= 128M max_heap_table_size= 128M thread_cache_size= 8 table_cache= 512 join_buffer_size= 90K key_buffer_size= 1024M max_allowed_packet= 64M sort_buffer_size= 4M read_buffer_size= 4M read_rnd_buffer_size = 2m myisam_sort_buffer_size = 64m tmp_table_size = 128m query_cache_type = 1 wait_timeout = 300 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid log_slow_queries=/var/log/mysql/slow-query.log long_query_time=20 log_queries_not_using_indexes=YES
