Форум пользователей MySQL

ZeT · 14.01.2013 00:35:01

Здравствуйте. Имеется нагруженный блог на вордпрессе, посещалка от 2 до 5 тыс. человек одновременно.
Всё это дело крутится на двух серверах

Web:
CPU:2x Intel Xeon E5-2430
RAM: 32Gb RAM
HDD: 2x3000Gb SATA

и

MySQL:
CPU:2x Intel Xeon E5-2430
RAM: 24Gb RAM
HDD: 2x600Gb SAS

(на одном веб сервер, на другом Mysql) общаются эти сервера через локаль - 1 Гб/с
Всё прооптимизировано и работает отлично, если бы не одно но...
Время от времени, буквально на минуту - две резко возрастает загрузка процессора (сервер, там где mysql)- 100% все ядра (а их не мало)
Ну и как следствие - скрипты выполняются очень долго, более 30 секунд. Соответсвенно и сайт получается недоступен.
Происходит это без каких либо закономерностей на ровном месте, даже при минимальной посещалке.
В slow-log при этом вываливаются абсолютно все запросы, и выполняются они более 5 секунд...

Cмотрел чем в это время занимается процессор с помощью SHOW FULL PROCESSLIST; (результат прикрепил в файле)
Также прикрепил конфиг mysql (my.cnf). Больше на этом сервере ничего не установлено.

Отредактированно ZeT (14.01.2013 00:40:56)

Прикрепленные файлы:

attach.zip, Размер: 27,937 байт, Скачано: 625

rgbeast · 14.01.2013 01:41:06

Очень много запросов в состоянии "Copying to tmp table", при этом другие в состоянии "Locked". Нужно разобраться почему они выполняются долго и всегда ли или только в определенной ситуации. Для сокращения числа возможных блокировок можно подумать над переходом на Innodb, если это применимо.

ZeT · 14.01.2013 02:25:56

rgbeast написал:
Очень много запросов в состоянии "Copying to tmp table",

Это плохо? Временные таблички у меня распологаются на tmpfs в оперативной памяти.
Как избежать попадания их в эти таблички?

rgbeast написал:
можно подумать над переходом на Innodb

InnoDB говорите? Думаете будет лучше? Да, это уменьшит кол-во блокировок, но ведь основную проблему это не исправит.
Нагрузка на процессор вовзрастает до максимум, отсюда и долгое выполнение запросов и уже и долгое выполнение скриптов.
Может быть где-то косяк в настройке? Версия сервера 5.1.

Допустим рассмотрим переход на иннодб, не подскажите оптимальные настройки для моего сервера? Буду благодарен.

Отредактированно ZeT (14.01.2013 02:26:38)

rgbeast · 14.01.2013 02:46:38

Плохо не создание временных таблиц, а то что много таких запросов являются медленными (выполняются много секунд), из-за чего другие запросы блокируются. Значит во временную таблицу копируется слишком много данных - нужно подумать как оптимизировать именно его. Для Innodb см параметры innodb_buffer_pool_size и innodb_flush_log_at_trx_commit, а также ботанический определитель проблем производительности MySQL http://sqlinfo.ru/a/i/rubtsov_mysqlbot.ppt

В первую очередь нужно разобраться с медленным запросом.

ZeT · 14.01.2013 03:00:14

Медленные запросы как следствие этой нагрузки. Так их нет.

rgbeast написал:
нужно подумать как оптимизировать именно его.

не совсем понял, кого?

rgbeast · 14.01.2013 03:04:30

Запросы типа этого

SELECT   wp_yASdr_posts.ID FROM wp_yASdr_posts  INNER JOIN wp_yASdr_term_relationships ON (wp_yASdr_posts.ID = wp_yASdr_term_relationships.object_id) INNER JOIN wp_yASdr_term_relationships AS tt1 ON (wp_yASdr_posts.ID = tt1.object_id) WHERE 1=1  AND wp_yASdr_posts.ID NOT IN (225637,314874,314915,314878,314880,314865,314866,314368,314171) AND ( wp_yASdr_term_relationships.term_taxonomy_id IN (4) AND tt1.term_taxonomy_id IN (43,56,57,58) ) AND wp_yASdr_posts.post_type = 'post' AND (wp_yASdr_posts.post_status = 'publish') GROUP BY wp_yASdr_posts.ID ORDER BY wp_yASdr_posts.post_date DESC LIMIT 0, 4

В присланном SHOW PROCESSLIST время выполнения у них указано несколько секунд, следовательно они являются медленными.

ZeT · 14.01.2013 03:05:24

> В присланном SHOW PROCESSLIST время выполнения у них указано несколько секунд, следовательно они являются медленными.
Я же говорю, медленные запросы идут как следствие нагрузки. До этого, таких не возникает.
Или же вы считаете, что нагрузка возрастает именно из за этого? из за медленных запросов? и это не следствие, а наоборот причина?

Также интересует, как и для MyISAM так и для InnoDB параметры thread_concrurency - что лучше тут ставить?
В интернете везде советуют разное, но как понял я этот параметр ставится равным кол-ву потоков процессора?

Отредактированно ZeT (14.01.2013 03:06:59)

rgbeast · 14.01.2013 03:15:43

Если запрос принципиально быстрый, то он будет выполняться несколько секунд только при перегруженности диска. Запрос не похож визуально на быстрый и, может быть, в обычной ситуации он исполняется 0.1 секунду, что тоже долго. Это надо исследовать и запрос, по возможности, оптимизировать. Может быть весь 99% ресурса процессора использовано запросами такого типа.

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

ZeT · 14.01.2013 08:48:46

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

ZeT · 22.01.2013 18:33:28

Как и думали медленные запросы это не причина, а следствие нагрузки.
Начали искать эти запросы в коде движка и оптимизировать его... Избавились от парочки, стало полегче.
Также включили лог запросов, не использующих индексы и тут всплыли проблемы.

Query_time: 0.305594  Lock_time: 0.000027 Rows_sent: 1  Rows_examined: 1275369

SET timestamp=1358862980;

SELECT post_id FROM wp_yASdr_postmeta WHERE meta_value = '2013/01/KASKOV.jpg' LIMIT 1;

# Query_time: 0.104206  Lock_time: 0.000074 Rows_sent: 1  Rows_examined: 71898

SET timestamp=1358863631;

SELECT COUNT(*) FROM wp_yASdr_terms AS t INNER JOIN wp_yASdr_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('post_tag') AND (t.name LIKE '%Р’Р»Р°СЃРµРЅРєРѕ%');

# Query_time: 0.055950  Lock_time: 0.000052 Rows_sent: 8  Rows_examined: 36017

SET timestamp=1358863631;

SELECT t.*, tt.* FROM wp_yASdr_terms AS t INNER JOIN wp_yASdr_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('post_tag') AND (t.name LIKE '%Р’Р»Р°СЃРµРЅРєРѕ%') ORDER BY t.name ASC LIMIT 20;

# Query_time: 0.008616  Lock_time: 0.000031 Rows_sent: 4  Rows_examined: 37356

SET timestamp=1358864246;

SELECT comment_approved, COUNT( * ) AS num_comments FROM wp_yASdr_comments  GROUP BY comment_approved;

Индексы расставлены, а запросы не используют их преимущества. Видимо расставлены некорректно. Помогите исправить ситуацию.

Табличка: wp_yASdr_terms

Структура:

term_id    bigint(20)        UNSIGNED    AUTO_INCREMENT

name    varchar(200)    utf8_general_ci

slug    varchar(200)    utf8_general_ci

term_group    bigint(10)

term_order    int(4)

Индексы:

Первичный, уникальный    Поле: term_id

Уникальный     slug    Поле: slug

name        Поле: name    

Табличка: wp_yASdr_term_taxonomy

Структура:

term_taxonomy_id    bigint(20)        UNSIGNED    AUTO_INCREMENT

term_id    bigint(20)        UNSIGNED    

taxonomy    varchar(32)

description    longtext

parent    bigint(20)        UNSIGNED

count    bigint(20)

Индексы:

Первичный, уникальный    Поле: term_taxonomy_id

Уникальный term_id_taxonomy    Поля: term_id, taxonomy

taxonomy    Поле: taxonomy

Табличка: wp_yASdr_postmeta

Структура:

meta_id    bigint(20)        UNSIGNED    AUTO_INCREMENT

post_id    bigint(20)        UNSIGNED

meta_key    varchar(255)

meta_value    longtext

Индексы:

Первичный, уникальный    Поле: meta_id    

post_id        Поле: post_id

meta_key    Поле: meta_key

В принципе сохранены стандартные индексы самого вордпресса, может быть дело не в этом?

P.S. что значит Rows_examined ? сколько строк было результатом запроса?

Отредактированно ZeT (22.01.2013 18:38:09)

Форум пользователей MySQL

#1 14.01.2013 00:35:01

Wordpress+MySQL переодическая загрузка процессора

#2 14.01.2013 01:41:06

Re: Wordpress+MySQL переодическая загрузка процессора

#3 14.01.2013 02:25:56

Re: Wordpress+MySQL переодическая загрузка процессора

rgbeast написал:

rgbeast написал:

#4 14.01.2013 02:46:38

Re: Wordpress+MySQL переодическая загрузка процессора

#5 14.01.2013 03:00:14

Re: Wordpress+MySQL переодическая загрузка процессора

rgbeast написал:

#6 14.01.2013 03:04:30

Re: Wordpress+MySQL переодическая загрузка процессора

#7 14.01.2013 03:05:24

Re: Wordpress+MySQL переодическая загрузка процессора

#8 14.01.2013 03:15:43

Re: Wordpress+MySQL переодическая загрузка процессора

#9 14.01.2013 08:48:46

Re: Wordpress+MySQL переодическая загрузка процессора

#10 22.01.2013 18:33:28

Re: Wordpress+MySQL переодическая загрузка процессора

Board footer