Загрузка CPU сервера на 680% MSQL после обновления.

[webdigi 0]

Всем доброго времени.

Сайт https://khersonline.net/

Выделенный сервер(6ядер, 8гб, места на ССД - много), РНР 7,2, MSQL -5,7. БД - около  200К новостей и около 120к комментариев? 30 категорий

Суть проблемы - стояла версия 12.1 с кодировкой СР1251. Решили перейти на официальную и купили 14,3. Отключили сайт, сделали бекапы БД(на данный момент - они больше недоступны и откатится нет возможности), перекачали скрипт в директорию, залогинились, запустился процесс обновления. 

Обновления прошли без ошибок. Встала 14.3.

Однако нагрузка на машине(утилита ТОР) не меньше 230% именно на MSQL (МАХ - 680%). Соответственно сервер оффается, и проблема повторяется после перезапуска.

При этом на сайте находиться 200 человек он-лайн.

Что пробовалось - откат на версию 12,1 - нагрузка падает на сервере до 12-15%, поэтапное поднятие до версии 14,1. Проблемы возникают после версии 13.0.

Понимаю что проблема с БД,  поэтому пробовал перестроение выполнять, оптимизация БД - не проходит, вывяливается с ошибкой MSQL.

Теперь все обновления вываливаются с ошибками что такие поля уже присутствуют в БД.  Проблема еще в том, что даже со старой БД такая же ситуация. 

Заранее спасибо за помощь.

 

[celsoft 6 094]

1 час назад, webdigi сказал:

стояла версия 12.1 с кодировкой СР1251

 

1 час назад, webdigi сказал:

Проблемы возникают после версии 13.0.

В этот момент происходит смена кодировки таблиц на utf8_mb4 а движок таблиц MySQL  c MyISAM на InnoDB. У вас же настройки MySQL сервера по всей видимости заточен на работу MyISAM таблиц а не на InnoDB. Что скорее всего приводит к постоянным свопам, нехватке выделенной памяти и т.д. Соответственно вам нужно заново сконфигурировать настройки MySQL сервера таким образом чтобы он был полностью оптимизирован на работу с InnoDB а не с MyISAM

[webdigi 0]

Доброго дня. 

Спасибо за ответ!

Я такой вариант тоже предполагал. Поэтому, вроде-бы файлик mysql.cnf поправил(насколько хватило понимания). Файлик в спойлере, может кто увидит что не так?

[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock
default-character-set = utf8

[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking
sql_mode	= ''
key_buffer_size		= 256M
max_allowed_packet	= 2048M
thread_stack		= 256K
thread_cache_size       = 12
#myisam-recover         = BACKUP
max_connections        = 500
#table_cache            = 64
#thread_concurrency     = 10
query_cache_limit	= 1M
query_cache_size        = 256M
general_log_file        = /var/log/mysql/mysql.log
general_log             = 1
log-bin-trust-function-creators = 1
long_query_time 	= 2
slow_query_log =/var/log/mysql/slow.log
log_queries_not_using_indexes=ON
#server-id		= 1
#log_bin		= /var/log/mysql/mysql-bin.log
#expire_logs_days	= 10
max_binlog_size         = 100M
#binlog_do_db		= include_database_name
#binlog_ignore_db	= include_database_name
# chroot = /var/lib/mysql/
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 8
innodb_page_cleaners = 8
innodb_flush_log_at_trx_commit=2
character-set-server = utf8

[mysqldump]
quick
quote-names
max_allowed_packet	= 16M
default-character-set = utf8

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition
default-character-set = utf8

[isamchk]
key_buffer_size		= 16M

!includedir /etc/mysql/conf.d/

При нагрузке до 200 пользователей еще-кое-как держится, а больше 300 уже падает.

ОТкатился на 12 версию...

[celsoft 6 094]

02.08.2021 в 09:23, webdigi сказал:

Выделенный сервер(6ядер, 8гб

 Кстати это мало. Помните что кодировка UTF-8 требует в 4 раза больше памяти, нежели cp1251. потому как в cp1251 символ занимает 1 байт, а  utf8mb4 уже четыре.

А настройки у вас не потимальные под вашу конфигурацию это точно. Вам нужен хороший сисадмин для тюнинга. Т.к. например слишком большой innodb_buffer_pool_instances, для вашей конфигурации, а это своп.

Как стартовый пример возмите статью https://itprospb.ru/2021/03/optimizatsiya-proizvoditelnosti-mysql/

[celsoft 6 094]

Нужно очень четко понимать, что все значения ставятся не абы как, а являются расчетными относительно всех параметров и всего серверного ПО. Например innodb_buffer_pool_size должен быть 50% от свободной оперативной памяти, не используемым другим серверным ПО. Вы поставили 2 гб, а у вас всего 8 гб, а сколько при этом всего вы навыделяли для апача, nginx, сколько процессов под них и т.д. и т.п. Может они у вас по своим настройкам могут "отьедать" все 8. Понимаете в чем суть? Все не наобум ставится, их все нужно расчитать. Поэтому системный администратор для анализа настроек всего серверного ПО нужен.

[webdigi 0]

я не говорю что я имею большой опыт в конфигурации серверов, однако опыт есть. 

Все эти настройки это результаты тестирования на наилучшую работоспособность. только при этом конфиге у меня в тор - load average: 3.89, 3.36, 2.85 и загрузка msql менее 90%.

с параметром innodb_buffer_pool_size - согласен, тут немного переборщил, до этого стоял innodb_buffer_pool_size     = 3072M

Все настройки апача и nginx дефолтные. 

Кроме того для настройки и анализа использовались н-топ, mysqltuner,  куча РТФМ по настройке мускула и оптимизации ДЛЕ. 

на этом сервере лежит только этот сайт. на соседнем лежит такой же с БД более 12гб и и онлайном 300-500 уников, и нормально крутится. Но он начинал работать сразу с 13.1 версии. конфиг мускула смотрел оттуда. БД - менял местами, проблемы остаются. Я все же считаю что проблема пошла при перестроении БД и изменении кодовой страницы. 

вообщем я думаю смысл понятен - надо для начала вернуть БД в божеский вид до версии 12 и кодовой страницы СР1251, потом ее конверировать и смотреть. Другого выхода я просто не вижу. Я не думаю что проблема в движке, ностройки сервака  и по дефолту работают на 13-14 версиях. остается только БД. 

Если будут желающие поковырять конфиги - готов сотрудничать.

[celsoft 6 094]

1 час назад, webdigi сказал:

на этом сервере лежит только этот сайт. на соседнем лежит такой же с БД более 12гб и и онлайном 300-500 уников, и нормально крутится. Но он начинал работать сразу с 13.1 версии.

Вообще нет никакой разницы. Начинал сразу или обновлялся. Структура БД и код DLE одинакова не зависимо от этого. При условии конечно если ее не модифицировали как то вручную, например через сторонние модули. Но тут можно просто сравнить структуру таблиц, используя install.php там структура всех таблиц описана.

[webdigi 0]

поднял ресурсы на сервере. 12 ядер, 16 оперативы

на 14.3 работает, но примерно через каждые минут 15 начинает ложить базу.

выяснил что в это время идет запрос:

 SELECT p.id, p.autor, p.date, p.short_story, CHAR_LENGTH(p.full_story) as full_story, p.xfields, p.title, p.categor

может есть варианты как его оптимизировать?

[celsoft 6 094]

Это не запрос, а часть запроса, а важное находится именно в конце а не в начале. Приводите полный запрос. Судя по началу запроса это просто показ кратких новостей. Видя полный запрос я вам смогу сказать является ли он оптимальным или какие либо настройки скрипта можно отключить для того чтобы запрос стал проще и быстрее.

[Хоббит 35]

Лол, что-то подобное у меня тоже было когда новостей стало слишком много, причем несколько раз и каждый раз причины по разному. Сперва вот такой запрос:

SELECT p.id, p.autor, p.date, p.short_story, CHAR_LENGTH(p.full_story) as full_story, p.xfields, p.title, p.category, p.alt_name, p.comm_num, p.allow_comm, p.fixed, p.tags, e.news_read, e.allow_rate, e.rating, e.vote_num, e.votes, e.view_edit, e.editdate, e.editor, e.reason FROM " . PREFIX . "_post p {$cat_join}LEFT JOIN " . PREFIX . "_post_extras e ON (p.id=e.news_id) 

Мб и дальше страдал бы если оперативка 16 GB. Онлайн 300-400, увеличил ОЗУ до 48 и почти половина занято БД, как последний вариант попробуйте увеличить оперативку)) 

Есть еще такой запрос:

SELECT category, COUNT(*) AS count FROM dle_post WHERE approve=1 GROUP BY category;

Выполняется 2 минуты. Еще мы убрали approve=1 из запросов, лишний раз сканирует таблицу.

Изменено 6 августа 2021 пользователем Хоббит

[skapunker 71]

Не проще базу данных слить в облако, например на RDS или в какое нибудь яндекс облако? И будет вам счастье.