У меня довольно большие проблемы с настройкой моего сервера MySQL. Я ожидал, что на моем веб-сайте будет много людей после того, как я куплю телевизионную рекламу между 17:00 и 18:00, в то время у меня на веб-сайте было более или менее 300 человек в этот период. Я пробовал много настраивать в my.cnf, но каждую ночь все хуже и хуже ... Я был бы очень признателен за помощь, чтобы выяснить, в чем моя проблема ... Моя текущая инфраструктура следующая:
Один сервер для моего сайта
Один сервер для моей БД
Вот моя текущая конфигурация 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
Если у вас есть совет, было бы здорово! Спасибо Жюльен
NB: извините за мой плохой английский
Хотя полный анализ - сложная и трудная задача, я могу предложить несколько областей из предоставленной вами информации и предупреждений в тюнере.
1) У вас 32 ГБ ОЗУ, но вы используете только 14 ГБ для MySQL на выделенной машине. Почему бы не дать больше? Предполагается, что 75% ОЗУ должно использоваться MySQL на выделенном хосте (innodb_buffer_pool_size), но вы должны сначала проверить top, vmstat или любой долгосрочный мониторинг, чтобы убедиться, что есть запас для других служб или случайных событий, таких как резервное копирование. Примечание. Linux всегда выглядит так, как будто у него «закончилась RAM», потому что он динамически использует любую свободную RAM для буферов и кеша - как показывает ваш пример.
2) innodb_flush_method - большой фактор производительности, ваш параметр закомментирован, поэтому я не могу узнать фактическое (по умолчанию) значение. Проверьте переменные сервера, чтобы узнать, что это такое. Но - его изменение оказывает большое влияние на вашу целостность в случае сбоя.
3) У вас много фрагментированных таблиц. Это может привести к потере производительности - если они сильно фрагментированы. Есть много других сообщений [1], в которых обсуждается определение того, насколько фрагментированы таблицы и требуется ли дефрагментация, но, опять же, это немного сложно с разными механизмами хранения и таблицей для каждого файла и т. Д. Чтобы дефрагментировать их, вы можете использовать оптимизацию или alter, чтобы перестроить таблицу (конечно, они заблокируют таблицы, поэтому вам нужно убедиться, что это быстро, потренировавшись или организовав отключение).
1 [Как найти и исправить фрагментированные таблицы MySQL
4) «Соединения выполнены без индексов» - это может быть вашей самой большой проблемой, хотя 131 звучит не очень высоко.
Любой запрос, который не использует / не может использовать индекс, должен проверять каждую запись (сканирование таблицы), что медленно для больших таблиц. Если вы сами не писали запросы (т.е. используете этот продукт Pretashop), возможно, вы не сможете работать над этим аспектом.
Однако для решения этой проблемы вам необходимо найти запросы, проанализировать их, а затем соответственно добавить индексы. Чтобы найти запросы, я бы начал с журнала медленных запросов: у вас не так много медленных запросов, поэтому я предполагаю, что медленный порог установлен слишком высоко? Так что если вы можете снизить это значение, начните отбирать медленные запросы и анализировать их. Используя команду EXPLAIN, вы сможете увидеть, как в JOINS отсутствуют индексы (хотя понимание EXPLAIN - это отдельная тема). Тогда добавление индексов несложно, но также имеет свои плюсы и минусы (например, вставки могут выполняться медленнее, слишком много индексов может отрицательно повлиять на производительность), плюс процесс индексирования заблокирует таблицу на время.
Также есть проблемы с метриками буфера, но в настоящее время я не знаю об этом (это то, что привело меня к этому вопросу - у меня такая же проблема с эффективностью записи журнала, которую я еще не понимаю).