Вот информация о моем сервере Giga Dedicated 6-Core:
Меня интересовала оптимизация сервера mysql. У меня будут десятки баз данных, совокупность их будет размером в несколько гигабайт.
Итак, я спрашиваю мнение my.cnf переменные размеры.
Вот мой my.cnf:
key_buffer_size = 1024M
table_cache = 1024
sort_buffer_size = 32M
read_buffer_size = 32M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 128M
thread_cache_size = 16
query_cache_size= 64M
query_cache_limit = 1M
thread_concurrency = 8
innodb_buffer_pool_size = 500M
innodb_additional_mem_pool_size = 128M
max_connections=250
А вот журнал mysqltuner:
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
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.14
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 7G (Tables: 104)
[--] Data in InnoDB tables: 530M (Tables: 213)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 213
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 4s (9 q [2.250 qps], 6 conn, TX: 10K, RX: 695)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 1.7G global + 80.4M per thread (250 max threads)
[!!] Maximum possible memory usage: 21.3G (90% of installed RAM)
[OK] Slow queries: 0% (0/9)
[OK] Highest usage of available connections: 0% (1/250)
[!!] Key buffer size / total MyISAM indexes: 1.0G/7.2G
[!!] Key buffer hit rate: 50.0% (6 cached / 3 reads)
[!!] Query cache efficiency: 0.0% (0 cached / 4 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: 78% (26 open / 33 opened)
[OK] Open file limit used: 0% (18/2K)
[OK] Table locks acquired immediately: 100% (36 immediate / 36 locks)
[!!] Connections aborted: 16%
[!!] InnoDB data size / buffer pool: 530.1M/500.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
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
Your applications are not closing MySQL connections properly
Variables to adjust:
key_buffer_size (> 7.2G)
query_cache_limit (> 1M, or use smaller result sets)
innodb_buffer_pool_size (>= 530M)
Какая конфигурация лучше всего подходит для моего сервера? Каково ваше мнение, предложение, опыт?
Обновить:
Я немного поправил настройки my.cnf. Они здесь:
key_buffer_size = 1024M
table_cache = 1024
sort_buffer_size = 10M
join_buffer_size = 10M
read_buffer_size = 10M
read_rnd_buffer_size = 10M
myisam_sort_buffer_size = 128M
thread_cache_size = 16
query_cache_size= 64M
query_cache_limit = 1M
-#Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 128M
max_connections=250
И ответы на запросы, предлагающие key_buffer_size и innodb_buffer_pool_size:
+---------------------------------------------------+
| recommended_innodb_buffer_pool_size |
+---------------------------------------------------+
| 1129M |
+---------------------------------------------------+
and
+----------------------------------------+
| recommended_key_buffer_size |
+----------------------------------------+
| 4M |
+----------------------------------------+
Рекомендуемый_key_buffer_size слишком мало? Как вы думаете, какие настройки достаточно хороши? Что меня тоже беспокоит, так это max_connections = 250? Этого достаточно?
Веб-сайт, который я размещаю, имеет около 18000 посещений и 70000 просмотров страниц за один день.
И вот что говорит mysqltuner об этих настройках:
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.5.14
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1M (Tables: 114)
[--] Data in InnoDB tables: 530M (Tables: 219)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 221
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 18h 32m 2s (11K q [0.179 qps], 461 conn, TX: 7M, RX: 1M)
[--] Reads / Writes: 80% / 20%
[--] Total buffers: 2.2G global + 40.2M per thread (250 max threads)
[OK] Maximum possible memory usage: 12.0G (51% of installed RAM)
[OK] Slow queries: 0% (0/11K)
[OK] Highest usage of available connections: 8% (20/250)
[OK] Key buffer size / total MyISAM indexes: 1.0G/3.5M
[OK] Key buffer hit rate: 99.9% (688K cached / 510 reads)
[OK] Query cache efficiency: 57.3% (4K cached / 7K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 234 sorts)
[!!] Temporary tables created on disk: 34% (661 on disk / 1K total)
[OK] Thread cache hit rate: 95% (20 created / 461 connections)
[OK] Table cache hit rate: 98% (611 open / 622 opened)
[OK] Open file limit used: 14% (329/2K)
[OK] Table locks acquired immediately: 100% (6K immediate / 6K locks)
[OK] InnoDB data size / buffer pool: 530.2M/1.0G
-------- 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
Variables to adjust:
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
Буду очень признателен за вашу помощь. Я знаю, что вам нужно прочитать много текста, но я просто пытаюсь чему-то научиться.
Вам необходимо установить MyISAM Key Cache и InnoDB Buffer Pool для размещения имеющихся у вас данных. Я опубликовал два запроса в DBA StackExchange, чтобы вычислить рекомендуемый размер ни для одной из этих кешей..
Для MyISAM, поскольку кэшируются только страницы индекса, этот запрос будет рекомендовать размер key_buffer_size на основе суммы ваших файлов .MYI и ограничить его до 4G, если он превышает 4G:
SELECT CONCAT (ROUND (KBS / POWER (1024, IF (PowerOfTwo <0,0, IF (PowerOfTwo> 3,0, PowerOfTwo))) + 0,4999), SUBSTR ('KMG', IF (PowerOfTwo <0,0, IF (PowerOfTwo> 3,0, PowerOfTwo)) + 1,1)) Recommended_key_buffer_size
ОТ (ВЫБЕРИТЕ НАИМЕНЕЕ (МОЩНОСТЬ (2,32), KBS1) KBS ИЗ
(ВЫБРАТЬ СУММ (длина_индекса) KBS1 FROM information_schema.tables
WHERE engine = 'MyISAM' И table_schema НЕ В ('information_schema', 'mysql')) AA
) A, (ВЫБРАТЬ 2 PowerOfTwo) B;
Для InnoDB, поскольку страницы данных и индекса кэшируются, этот запрос будет рекомендовать размер innodb_buffer_pool_size на основе суммы ваших данных и страниц индекса:
SELECT CONCAT (ROUND (KBS / POWER (1024, IF (PowerOfTwo <0,0, IF (PowerOfTwo> 3,0, PowerOfTwo))) + 0,49999), SUBSTR ('KMG', IF (PowerOfTwo <0,0, IF (PowerOfTwo> 3,0, PowerOfTwo)) + 1,1)) рекомендуется_innodb_buffer_pool_size
FROM (SELECT SUM (data_length + index_length) KBS FROM information_schema.tablesM
ГДЕ engine = 'InnoDB') A,
(ВЫБЕРИТЕ 2 PowerOfTwo) B;
Согласно вашему отображению mysqltuner.pl, у вас есть около 24 ГБ ОЗУ, 530 МБ id InnoDB, 7,2 ГБ индексов MyISAM. Независимо от рекомендаций, просто руководствуйтесь здравым смыслом, задав для innodb_buffer_pool_size значение около 530 МБ, но меньше 1 ГБ. Так как MyISAM не использует память в определенных случаях, вы можете оставить key_buffer_size равным 1024M, потому что mysqltuner.pl говорит, что 90% RAM используется соединениями с БД. ОС тоже нужна оперативная память.
Я бы снизил их:
[mysqld]
join_buffer_size = 4M
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 4M
После этого перезапустите mysql и запустите mysqltuner.pl. Ваша цель здесь - получить максимально возможное использование памяти менее 80%, чтобы у ОС была передышка для памяти.
CAVEAT: Прошу прощения за столбец под названием «PowerOfTwo». Его действительно следует называть PowerOf1024. Цель PowerOfTwo - отображать рекомендуемые ответы в ГБ, МБ, КБ