Есть БД на mysql. Основная нагрузка -- чтение. Работает под немаленькой нагрузкой (160 запросов в секунду).
Иногда происходит вставка в таблицу (не больше 50 раз в сутки). И операция вставки на холодную происходит около минуты! Если сразу же сделать ещё вставку, то она проходит уже быстрее (несколько миллисекунд).
Все таблицы в innodb, на каждую по паре файлов.
Куда нужно смотреть, что надо подтюнить?
Вот анализ mysqltuner.pl:
>> MySQLTuner 1.2.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] Logged in using credentials from debian maintenance account.
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.43-0+deb7u1-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in InnoDB tables: 96G (Tables: 342)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 74
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 14d 21h 29m 57s (214M q [166.564 qps], 6M conn, TX: 456B, RX: 146B)
[--] Reads / Writes: 94% / 6%
[--] Total buffers: 9.3G global + 18.6M per thread (151 max threads)
[OK] Maximum possible memory usage: 12.0G (18% of installed RAM)
[OK] Slow queries: 0% (6K/214M)
[OK] Highest usage of available connections: 56% (85/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/2.4M
[OK] Key buffer hit rate: 100.0% (26M cached / 1 reads)
[OK] Query cache efficiency: 74.7% (148M cached / 198M selects)
[!!] Query cache prunes per day: 1326155
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 20M sorts)
[!!] Joins performed without indexes: 1047995
[OK] Temporary tables created on disk: 14% (1M on disk / 8M total)
[OK] Thread cache hit rate: 99% (574 created / 6M connections)
[OK] Table cache hit rate: 94% (937 open / 994 opened)
[OK] Open file limit used: 2% (49/2K)
[OK] Table locks acquired immediately: 100% (111M immediate / 111M locks)
[!!] InnoDB data size / buffer pool: 96.9G/8.0G
-------- 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
Variables to adjust:
query_cache_size (> 1G) [see warning above]
join_buffer_size (> 16.0M, or always use indexes with joins)
innodb_buffer_pool_size (>= 96G)
Средний
Средний
