Как настроить сервер MySQL, когда ожидается большой пик использования
У меня довольно большие проблемы при попытке настроить мой MySQL сервер. Я ожидал, что на моем веб-сайте будет много людей после того, как я куплю телевизионную рекламу между 17:00 и 18:00, у меня в это время на веб-сайте было более или менее 300 человек. Я много пробовал настраивать в my.cnf, но с каждой ночью все хуже и хуже .. Я был бы очень признателен за помощь, чтобы выяснить, в чем моя проблема ... Моя текущая инфраструктура следующая:
Один сервер для моего веб-сайта
- на нем установлен apache2
- 6c / 12t - Intel (R) Xeon (R) CPU E5-1650 v2 @ 3.50GHz
- 64GB RAM - DDR3 ECC 1600 МГц
- Диск - HardRaid + 3x480 Go SSD
Один сервер для моей БД
- Mysql 5.5.38 на нем
- 4c / 8t - Intel (R) Xeon (R) CPU E5-1620 v2 @ 3,70 ГГц
- 32 ГБ ОЗУ - DDR3 ECC 1600 МГц
- Диск - SoftRaid 3x160 Go SSD
Вот моя текущая конфигурация my.cnf:
innodb_file_per_table = 1
innodb_buffer_pool_instances = 13
innodb_buffer_pool_size = 13375M
innodb_open_files = 300
innodb_thread_concurrency = 0
#innodb_read_io_threads = 8
#innodb_write_io_threads = 8
#innodb_flush_method = O_DIRECT
#join_buffer_size = 30M
#sort_buffer_size = 10M
#read_buffer_size = 10M
wait_timeout = 180
interactive_timeout = 180
max_connections = 250
max_heap_table_size = 256M
tmp_table_size = 256M
table_cache = 20000
table_definition_cache = 20000
#table_open_cache = 20000
query_cache_type = 0
И результаты mysqltuner:
>> MySQLTuner 1.6.9 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Performing tests on 127.0.0.1:3306
[OK] Logged in using credentials passed on the command line
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.38-0+wheezy1-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 32M (Tables: 126)
[--] Data in InnoDB tables: 10G (Tables: 1503)
[!!] Total fragmented tables: 359
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1m 59s (82K q [695.832 qps], 335 conn, TX: 538M, RX: 34M)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Total buffers: 13.8G global + 2.7M per thread (300 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 13.9G (44.07% of installed RAM)
[OK] Maximum possible memory usage: 14.6G (46.49% of installed RAM)
[OK] Slow queries: 0% (1/82K)
[OK] Highest usage of available connections: 3% (11/300)
[OK] Aborted connections: 0.60% (2/335)
[OK] Query cache is disabled by default due to mutex contention.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 14K sorts)
[!!] Joins performed without indexes: 131
[OK] Temporary tables created on disk: 5% (927 on disk / 17K total)
[OK] Thread cache hit rate: 96% (11 created / 335 connections)
[OK] Table cache hit rate: 25% (1K open / 6K opened)
[OK] Open file limit used: 0% (301/40K)
[OK] Table locks acquired immediately: 100% (221K immediate / 221K locks)
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (763K used / 4M cache)
[OK] Key buffer size / total MyISAM indexes: 4.0M/10.6M
[OK] Read Key buffer hit rate: 100.0% (562K cached / 0 reads)
[OK] Write Key buffer hit rate: 100.0% (14K cached / 0 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 13.1G/10.7G
[OK] InnoDB buffer pool instances: 13
[!!] InnoDB Used buffer: 5.30% (45409 used/ 855985 total)
[OK] InnoDB Read buffer efficiency: 99.95% (85810161 hits/ 85850600 total)
[!!] InnoDB Write Log efficiency: 61.42% (519 hits/ 845 total)
[OK] InnoDB log waits: 0.00% (0 waits / 326 writes)
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Adjust your join queries to always utilize indexes
Variables to adjust:
join_buffer_size (> 128.0K, or always use indexes with joins)
Загрузка процессора довольно низкая (от 1 до 4), когда люди находятся на сайте в течение нескольких секунд. Хуже того, когда много людей приходит одновременно, загрузка процессора составляет 40 или 50, это проблема, и я не понимаю, какой параметр я мог бы настроить, чтобы этого избежать.
RAM: Я думаю, что RAM здесь достаточно. На сервере 32 ГБ, которые даже не используются больше, когда на сайте больше людей. Он составляет 15-20% и не перемещается в течение дня .. Я не понимаю, почему ОЗУ не перемещается, моя настоящая цель - поместить максимум моих запросов в память, чтобы избежать удара по диску.
В часы пик здесь free -m :
total used free shared buffers cached
Mem: 32202 25261 6940 0 917 18794
-/+ buffers/cache: 5549 26652
Swap: 1532 21 1511
Я использую Prestashop для своего веб-сайта, и у меня есть довольно большая таблица ... Я архивирую и усекаю большую часть это в эти выходные. Например:
SELECT COUNT(*) FROM ps_connections; => 1 330 373
SELECT COUNT(*) FROM ps_guest; => 6 970 248
Если у вас есть совет, было бы здорово! Спасибо Жюльен
Примечание: извините за мой плохой английский
Полный анализ - сложная и трудная задача, но из предоставленной информации и предупреждений в тюнере могу предложить несколько областей.
1) У вас 32 Гб оперативной памяти, но на выделенной машине используется только 14 Гб для MySQL. Почему бы не дать больше? Предлагается 75% оперативной памяти использовать MySQL на выделенном хосте (innodb_buffer_pool_size), но сначала нужно проверить top, vmstat или любой долгосрочный мониторинг, чтобы убедиться, что есть свободное место для других сервисов, или случайных событий, таких как резервное копирование. Обратите внимание, что Linux всегда выглядит так, как будто у него "закончилась оперативная память", потому что он динамически использует любую свободную оперативную память для буферов и кэша - как показывает ваш пример.
2) innodb_flush_method - большой фактор производительности, ваша настройка закомментирована, поэтому я не могу знать фактическое (по умолчанию) значение. Проверьте переменные сервера, чтобы узнать, что это такое. Но - изменение его сильно влияет на вашу целостность в случае выхода из строя.
3) У вас много фрагментированных таблиц. Это может привести к потере производительности - если они сильно фрагментированы. Есть много других постов[1], где обсуждается, насколько фрагментированы таблицы, нужна ли дефрагментация, но опять же, это немного сложно с разными движками хранения, table-per-файлами и т.п. Для их дефрагментации вы можете использовать оптимизацию или модификацию для перестройки таблицы (конечно, это заблокирует таблицы, так что вам нужно убедиться, что это быстро, потренировавшись или организовав остановку).
1[Как найти и исправить фрагментированные таблицы MySQL
4) "Присоединение, выполненное без индексов" - это может быть вашей самой большой проблемой, хотя 131 звучит не очень высоко.
Любой запрос, не использующий/не использующий индекс, должен проверять каждую запись (сканирование таблицы), что медленно для больших таблиц. Если вы сами не написали запросы (т.е. используете этот продукт Pretashop), то, возможно, вы не сможете работать над этим аспектом.
Однако, чтобы решить эту проблему, вам нужно найти запросы, проанализировать их, а затем добавить индексы соответствующим образом. Чтобы найти запросы, я бы начал с медленного журнала запросов: у вас не так уж много медленных запросов, так что, думаю, медленный порог установлен слишком высоко? Так что если вы можете его снизить, то начинайте снимать медленные запросы и анализировать их. Используя команду EXPLAIN, вы должны уметь видеть, как JOINS пропускают индексы (хотя понимание EXPLAIN - это еще одна целая тема). Тогда добавление индексов является простым, но также имеет свои плюсы и минусы (например, вставки могут работать медленнее, слишком много индексов может отрицательно сказаться на производительности), плюс процесс индексации заблокирует таблицу на время.
Есть также проблемы в метриках буфера, но это в настоящее время за пределами моих знаний (вот что привело меня к этому вопросу - у меня та же проблема с эффективностью записи логов, которую я пока не понимаю)
.