Привет, обстановка такая:
Машина - CPU 16 ядер, 64гб ОЗУ, 80гб SSD
Проект - Laravel, nodeJS, Centrifuge, MySQL (все таблицы InnoDB), реал тайм приложение, в БД индексы проставлены, БД нормализована, больше всего запросов по SELECT, но Update, Insert, Delete тоже есть, однако селектов существенно больше, кроме того, их вызывают не только пользователи, они вызываются всегда в фоновом режиме самим приложением.
Проблема - при большом наплыве онлайна (700+), начинает виснуть всё, CPU машины уходит в 70+% загрузки и база не отвечает какое-то время.
Вопрос - что можно сделать с конфигурацией mySQL? Мы вообще не трогали конфиг, кроме как InnoDB Buffer Pool выставили на ~80% от ОЗУ, судя по рекомендациям гайдов, в остальном ничего не трогали особо. Кто что может подсказать? Если что, можно связаться лично, за помощь вознаграждение!
Как вы поняли, что проставили индексы правильно? Много не значит хорошо. Как вы поняли, что база правильно нормализована? explain запросов делали? Включили лог для записи медленных запросов? Слишком большие таблицы есть? Если да, поможет горизонтальный шардинг.
kafkiansky, Самая большая таблица 1.7млн строк, весом 1.4гб. Про индексы я знаю со слов человека, с которым общался про нашу бд, в целях разобраться в вопросе (и да, я сказал, что они проставлены, а не проставлены правильно, я не могу утверждать, знаю только то, что они точно есть там, где нужны). Да и в целом - код писал опытный ларавельщик, всё написано качественно, просто на более серьезных стадиях развития проекта начались проблемы из-за количества пользователей. У проекта нет сейчас поддержки от кодера, который его писал.
Mrk0tA, для веб сервера например, могу сказать по собственному опыту, что неправильно, криво или ещё как то не оптимально настроенный веб сервер будет создавать дикую нагрузку и съедать ресурсы сервера, не оставляя mysql ничего
Разворачивайте проект локально, устанавливаете какой-нибудь https://laravel.com/docs/7.x/telescope, смотрите, сколько происходит запросов на каждый урл, оптимизируете через изучение результата explain, например. Таблицу весом в 1,4 гб можно перенести на отдельный сервер или разделить на два сервера, но тогда будут трудности с получением и записью данных.
kafkiansky, Я так не думаю, потому я здесь и задал вопрос насчет бд, потому что я предполагаю, что он мог здесь меньше знаний иметь, именно поэтому на развитии оно вылазит. Ты либо помоги и скажи го разберемся, либо не пиши и не выясняй что я думаю и что знаю. Я не кодер, я пришел за помощью с вопросом и дал все данные, которые знаю.
kafkiansky, Это уже совет по делу, за него спасибо. Выше Сергей написал про веб сервер, может ли в этом быть правда, что nginx ест и не дает бд места? При том, что 300-500 онлайна и без долбежки запросов в бд (например, когда люди толпой вводят промокод) всё работает стабильно?
kafkiansky, Меня не Алёша зовут, да и не Алёша я, в том понимании, в каком ты написал, как раз таки - да, мы не знакомы, так что не суди, и не знакомиться я пришел. Лишь указал, что с комментом "Щас бы думать..", совсем неверно ты определился, я до этого всё по делу писал.
может ли в этом быть правда, что nginx ест и не дает бд места? При том, что 300-500 онлайна и без долбежки запросов в бд (например, когда люди толпой вводят промокод) всё работает стабильно?
Все может быть, но без профилирования сказать точно нельзя. В любом случае я бы начал с оптимизирования запросов. Как я уже сказал, mysql позволяет выставить сессию, в которой она будет писать медленные запросы в базу (характеристику медленности определяете сами, например, 0,5 секунд уже более чем достаточно, чтобы оптимизировать запрос). Но если лень, то просто вкладывайтесь в железо.
Mrk0tA, там будет не один запрос, но да, мы делаем так. Только slow_query_log, если не ошибаюсь, по умолчанию пишет в файл. Но лучше начать с бОльших цифр, с 1-й секунды, например. А то вдруг у вас там прям совсем все плохо. Хотя бы отбросите этот вариант.
Включите performance schema понаблюдайте за работой приложения , и изходя из этих данных уже оптимизируйте , вот ссылка вам для ознакомления https://m.habr.com/ru/post/351740/
Александр Сисюкин, спасибо за совет, будем изучать! А хорошего программиста не так просто найти, проект коммерческий, многие нровятся кинуть на деньги, потому сейчас сами изучаем все, на базе того, что уже построено.
Antonio Solo, а вы знаете чтоли бюджеты? Рассуждать про жадность не зная опыта, ситуации и бюджетов - странное дело. Ещё скажите мне, что выдав любому человеку достаточную зарплату на тот момент, он никогда не захочет больше)
Antonio Solo, 1.4 Gb это ни о чем. Надо смотреть проект.
Бывает, что разработчики кеш отключают, чтобы изменения сразу видны были, и забывают включить обратно.
Mrk0tA, Смотрите какой у вас стоит кеш
File System
Database
Memcached
APC
Redis
Memory (Arrays)
Файловый будет по умолчанию в папке /storage/cache наверное, не знаю если меняется по версиям ларавель
Почитай про кэширование маршрутов, запросов, конфигов в ларавель.
Вопрос закрыт, проблема была в конфигурации MySQL, как я и предполагал, а не в индексах, или построении запросов.
Изменили под наш конфиг машины данные параметры MySQL:
1) max_allowed_packet увеличили (до 64мб)
2) key_buffer_size увеличен (до 64мб)
3) innodb_buffer_pool_size значительно уменьшен (до 4гб)
4) max_connections значительно увеличены (до 10000)
5) query_cache_size увеличен (до 16мб)
А также добавили и включили файл подкачки, но он пока не задействован, т.е конфиг сделал главное дело.
Включить логирование медленных запросов MySQL, после чего оптимизировать, добавить индексы.
Обратить внимание на то, что индексы могут приводить и к падению производительности, так что неиспользуемые лучше удалять.
Убедиться, что не просто индексы проставлены, а проставлены правильно.
Включить логирование atop на несколько дней, чтобы была возможность понаблюдать, что происходить в системе, когда "начинает виснуть всё". Может быть, вообще не в MySQL дело?
В Laravel запросы делаются к базе с помощью Active Record - бывает так, что вместо JOIN в базу летит гора запросов циклом - это не оптимально.
Как уже выше писали, нужно использовать кеширование - это ощутимо снизит нагрузку.
0) Это теперь предстоит узнать, сейчас впервые столкнулся с данным названием.
1) DigitalOcean. Изначально была капля из 2 ядра 4 озу, расширяли просто только эти параметры, не затрагивая ссд, это можно сделать, на самом деле, работаем - да, с докером
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 17h 14m 12s (6M q [109.235 qps], 424K conn, TX: 10G, RX: 926M)
[--] Reads / Writes: 92% / 8%
[--] Binary logging is disabled
[--] Physical Memory : 62.9G
[--] Max MySQL memory : 48.3G
Can't exec "ps": No such file or directory at /usr/bin/mysqltuner line 1141 (#1)
(W exec) A system(), exec(), or piped open call could not execute the
named program for the indicated reason. Typical reasons include: the
permissions were wrong on the file, the file wasn't found in
$ENV{PATH}, the executable in question was compiled for another
architecture, or the #! line in a script points to an interpreter that
can't be run for similar reasons. (Or maybe your system doesn't support
#! at all.)
[--] Other process memory: 0B
[--] Total buffers: 48.0G global + 1.1M per thread (256 max threads)
[--] P_S Max memory usage: 72B
Can't exec "ps": No such file or directory at /usr/bin/mysqltuner line 1141 (#1)
(W exec) A system(), exec(), or piped open call could not execute the
named program for the indicated reason. Typical reasons include: the
permissions were wrong on the file, the file wasn't found in
$ENV{PATH}, the executable in question was compiled for another
architecture, or the #! line in a script points to an interpreter that
can't be run for similar reasons. (Or maybe your system doesn't support
#! at all.)
Can't exec "ps": No such file or directory at /usr/bin/mysqltuner line 1141 (#1)
(W exec) A system(), exec(), or piped open call could not execute the
named program for the indicated reason. Typical reasons include: the
permissions were wrong on the file, the file wasn't found in
$ENV{PATH}, the executable in question was compiled for another
architecture, or the #! line in a script points to an interpreter that
can't be run for similar reasons. (Or maybe your system doesn't support
#! at all.)
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 48.1G (76.40% of installed RAM)
[OK] Maximum possible memory usage: 48.3G (76.82% of installed RAM)
Can't exec "ps": No such file or directory at /usr/bin/mysqltuner line 1141 (#1)
(W exec) A system(), exec(), or piped open call could not execute the
named program for the indicated reason. Typical reasons include: the
permissions were wrong on the file, the file wasn't found in
$ENV{PATH}, the executable in question was compiled for another
architecture, or the #! line in a script points to an interpreter that
can't be run for similar reasons. (Or maybe your system doesn't support
#! at all.)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/6M)
[OK] Highest usage of available connections: 7% (19/256)
[OK] Aborted connections: 0.00% (6/424557)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 483K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 6% (168 on disk / 2K total)
[OK] Thread cache hit rate: 99% (49 created / 424K connections)
[OK] Table cache hit rate: 98% (471 open / 478 opened)
[OK] Open file limit used: 0% (24/1M)
[OK] Table locks acquired immediately: 100% (2K immediate / 2K locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is enabled.
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 48.0G/1.6G
[!!] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk : 384 for 8 Buffer Pool Instance(s)
[OK] innodb_buffer_pool_size is aligned with value innodb_buffer_pool_chunk_size and innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (3497937698 hits/ 3497953416 total)
[OK] InnoDB Write log efficiency: 92.32% (542216 hits/ 587340 total)
[OK] InnoDB log waits: 0.00% (0 waits / 45124 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.
Variables to adjust:
innodb_buffer_pool_instances(=48)
какой ресурс уходит ? если только CPU - то с уверенностью 99% можно сказать что это проблема отсутствия индексов, в частности выполнение операций над join-ами без индексов или вообще не индексируемых операция в запросах. подход один - поиск тяжелых запросов и их оптимизация.
если и диск тоже - тут сложнее, надо детально смотреть из-за чего она его напрягает.
Средний
