Почему mysqld сильно грузит процессор?

Question

[nep]

Есть:

5 интернет магазинов с небольшой посещаемостью. Хороший хостинг. Периодически огромные тормоза на сайте из-за того, что mysql грузит проц под 100%. Регламентных процедур вроде никаких нет. Проблема старая — тянется уже долго. Своей квалификации не хватает, чтобы понять в чем косяк (может вообще не в битриксе, а в настройках сервера).


Подскажите куда копать? Или порекомендуйте специалиста. Спасибо.

Comments

[Валерий Дмитриев]

У mysql нужно включить slowlog и смотреть в него. Тут только по логам можно проблему найти — по другому никак.

Answer 1

[Дмитрий Щербаков]

ну для начала конфиг mysql покажите

Comments

[nep]

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

query_cache_size=1G
query_cache_limit=2M
innodb_buffer_pool_size=4G
#innodb_buffer_pool_instances=4
innodb_additional_mem_pool_size=200M
innodb_file_io_threads=8
innodb_lock_wait_timeout=50
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=0

# this lines for DB recovery:
#max_allowed_packet=128M
#default-character-set=cp1251
#character-set-server=cp1251
#collation-server=cp1251_bin
#init-connect=«SET NAMES cp1251»

# ### skip-character-set-handshake

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log

[Дмитрий Щербаков]

1. деление на инстансы советую раскомментировать #innodb_buffer_pool_instances=4
2. Отшибайте зависшие запросы
wait_timeout = 15
interactive_timeout = 15
net_write_timeout = 15
net_read_timeout = 15
Возможно вы на этом этапе увидите ошибку
3. Мониторьте запросы самые долгие по выполнению, возможно в них спрятана цикличность которая и выжирает память

[Дмитрий Щербаков]

long_query_time = 2
log_slow_queries = /var/log/mysql/mysql-slow.log

Так ловите медленные запросы

[nep]

Спасибо!

[nep]

Вот такой запрос лидирует по частоте и по времени:

SELECT BE.ID as ID,BE.NAME as NAME,BE.PREVIEW_TEXT as PREVIEW_TEXT,BE.PREVIEW_TEXT_TYPE as PREVIEW_TEXT_TYPE
FROM
b_iblock B
INNER JOIN b_lang L ON B.LID=L.LID
INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
WHERE
1=1 AND ( ((((BE.IBLOCK_ID = '39')))) AND ((((BE.NAME LIKE '5277-9999')))) ) AND (((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)))

Такое ощущение, что это какая то битрикс компонента выполняется.

[Дмитрий Щербаков]

Хотелось бы увидеть статистику выполнения данного запроса

Answer 2

[muzzle]

А периодичность четко выражена? С бэкапами тормоза не совпадают? Объем БД какой?

Comments

[nep]

периодичность выражена слаба — никакой логики нет, с бекапами не совпадает, пожатый дамп весит под 200MB

Answer 3

[Masterme]

Под админом в момент тормозов:
SHOW PROCESSLIST
и ищите зависший запрос

Comments

[nep]

пробовал. чего то выводит, зависших вроде нет, а что дальше делать знаний не хватает.

Answer 4

[Степан]

Включите профиллинг.
Вывод сделайте себе на каждой странице и ходите по сайту, смотрите какие запросы долго выполняются.
Обычный slowlog иногда не покажет то, что у вас на странице зациклился какой либо запрос и выполняется несколько десятков раз.

Comments

[nep]

Спасибо, попробую

Answer 5

[Алексей Сундуков]

Не все хорошие хостинги одинаково полезны. VDS/VPS? Случайно возникающая нагрузка обычно означает просадку по диску.

Answer 6

[muzzle]

И mysqltuner.pl что рассказывает?

Comments

[nep]

не знал про эту утилиту. скачал, пробую запустить, расскажу.

[nep]

>> MySQLTuner 1.2.0 — Major Hayden <[e-mail]>
>> Bug reports, feature requests, and downloads at mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[!!] Successfully authenticated with no password — SECURITY RISK!

— General Statistics — [--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.95
[OK] Operating on 64-bit architecture

— Storage Engine Statistics — [--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 6M (Tables: 14)
[--] Data in InnoDB tables: 1G (Tables: 238)
[!!] BDB is enabled but isn't being used
[!!] Total fragmented tables: 1

— Security Recommendations — [!!] User 'root@localhost' has no password set.

— Performance Metrics — [--] Up for: 1d 2h 32m 39s (15M q [159.264 qps], 125K conn, TX: 31B, RX: 9B)
[--] Reads / Writes: 76% / 24%
[--] Total buffers: 5.2G global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 5.5G (34% of installed RAM)
[OK] Slow queries: 0% (0/15M)
[OK] Highest usage of available connections: 8% (8/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/8.6M
[OK] Key buffer hit rate: 100.0% (593K cached / 0 reads)
[OK] Query cache efficiency: 66.1% (8M cached / 13M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 242K sorts)
[!!] Joins performed without indexes: 12488
[!!] Temporary tables created on disk: 28% (143K on disk / 502K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 749K opened)
[OK] Open file limit used: 0% (0/1K)
[OK] Table locks acquired immediately: 99% (21M immediate / 21M locks)
[OK] InnoDB data size / buffer pool: 1.4G/4.0G

— Recommendations — General recommendations:
Add skip-bdb to MySQL configuration to disable BDB
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_cache (> 64)

Спасибо за утилиту! Уже кое что начало проясняться.
Я так полагаю следует поменять настройки на предлагаемые?

[muzzle]

Да, настройки надо подправить, но менять очень осмотрительно, с учетом доступной оперативки.

А вот эта строчка
[!!] Joins performed without indexes: 12488
намекает, что где-то недостает необходимых индексов, или криво спроектированы запросы.
Это скорее всего в log_slow_queries както отразится.

[nep]

прямых запросов к БД из битрикса вроде нет, так что если грешить, то только на него.
оперативы 16 гигов — думаю можно развернуться :)

Спасибо за ответы. Буду тюнить.

Answer 7

[betal]

Не пробовали брать конфиг который сделал сам 1C с небольшими изменениями распределения оперативной памяти?
Результат выполнения команды top, wa нормальный?
А так в bitrix не особо легкие запросы, когда писал там интернет магазин полностью отказался от инфоблоков.