[devel] Mysqld CPU usage at www.sisyphus.ru

[devel] Mysqld CPU usage at www.sisyphus.ru

[Grigory Batalov]

Здравствуйте!

Недавно заметил, что поиск пакетов на www.sisyphus.ru тормозит.
Это из-за того, что по нажатию "искать" mysqld на 6 секунд
съедает 80-99% CPU (если результат не закеширован).
Вроде бы, раньше такого не было.

Есть ли соображения, советы?

Re: [devel] Mysqld CPU usage at www.sisyphus.ru

[Alexey I. Froloff]

[-- Attachment #1: Type: text/plain, Size: 199 bytes --]

On Tue, Sep 01, 2009 at 05:08:06PM +0400, Grigory Batalov wrote:
> Есть ли соображения, советы?
Перейти на PostgreSQL?  Тогда mysqld точно ресурсы жрать не будет
;-)

-- 
Regards,
Sir Raorn.

[-- Attachment #2: Digital signature --]
[-- Type: application/pgp-signature, Size: 197 bytes --]

Re: [devel] Mysqld CPU usage at www.sisyphus.ru

[Alex Gorbachenko]

[-- Attachment #1: Type: text/plain, Size: 202 bytes --]

On Tue, 1 Sep 2009 17:08:06 +0400
Grigory wrote:

>Есть ли соображения, советы?

innodb ? если да, то OPTIMIZE TABLE как давно делался ?

-- 
np: 

[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 198 bytes --]

Re: [devel] Mysqld CPU usage at www.sisyphus.ru

[Michael Shigorin]

On Tue, Sep 01, 2009 at 05:08:06PM +0400, Grigory Batalov wrote:
> Недавно заметил, что поиск пакетов на www.sisyphus.ru тормозит.
> Это из-за того, что по нажатию "искать" mysqld на 6 секунд
> съедает 80-99% CPU (если результат не закеширован).
> Вроде бы, раньше такого не было.  Есть ли соображения, советы?

Можно потюнить query cache из общих соображений, но боюсь,
по-хорошему надо включать slow log, ловить этот запрос и 
explain'ить его руками.

-- 
 ---- WBR, Michael Shigorin <mike@altlinux.ru>
  ------ Linux.Kiev http://www.linux.kiev.ua/

Re: [devel] Mysqld CPU usage at www.sisyphus.ru

[Grigory Batalov]

On Tue, 1 Sep 2009 17:11:59 +0400
Alex Gorbachenko wrote:

> >Есть ли соображения, советы?
> 
> innodb ? если да, то OPTIMIZE TABLE как давно делался ?

Сделал сейчас, но что-то разницы не заметно.

mysql> optimize table srpm;
+---------------+----------+----------+----------+
| Table         | Op       | Msg_type | Msg_text |
+---------------+----------+----------+----------+
| alt_stat.srpm | optimize | status   | OK       | 
+---------------+----------+----------+----------+
1 row in set (0.15 sec)

mysql> optimize table rpm;
+--------------+----------+----------+----------+
| Table        | Op       | Msg_type | Msg_text |
+--------------+----------+----------+----------+
| alt_stat.rpm | optimize | status   | OK       | 
+--------------+----------+----------+----------+
1 row in set (2.16 sec)

mysql> optimize table maintainers;
+----------------------+----------+----------+----------+
| Table                | Op       | Msg_type | Msg_text |
+----------------------+----------+----------+----------+
| alt_stat.maintainers | optimize | status   | OK       | 
+----------------------+----------+----------+----------+
1 row in set (0.03 sec)

Re: [devel] Mysqld CPU usage at www.sisyphus.ru

[Alex Gorbachenko]

[-- Attachment #1: Type: text/plain, Size: 305 bytes --]

On Tue, 1 Sep 2009 17:32:25 +0400
Grigory wrote:

>> innodb ? если да, то OPTIMIZE TABLE как давно делался ?
>
>Сделал сейчас, но что-то разницы не заметно.

тогда запрос и explain от него - в студию.

-- 
np: 

[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 198 bytes --]

Re: [devel] Mysqld CPU usage at www.sisyphus.ru

[Grigory Batalov]

On Tue, 1 Sep 2009 17:40:18 +0400
Alex Gorbachenko wrote:

> >> innodb ? если да, то OPTIMIZE TABLE как давно делался ?
> >
> >Сделал сейчас, но что-то разницы не заметно.
> 
> тогда запрос и explain от него - в студию.

Вот такие запросы тормозят:

SELECT DISTINCT s.name, s.version, s.rel, m.packager, s.summary, s.repo
FROM srpm as s, maintainers as m, rpm as r WHERE  r.srpm = s.name AND
m.mail=s.packager AND ( s.name RLIKE 'unichrome'  OR  s.summary RLIKE
'unichrome'  OR  s.description RLIKE 'unichrome'  OR  r.namen RLIKE
'unichrome' ) AND s.repo='Sisyphus' AND r.repo='Sisyphus' ORDER BY 1
ASC LIMIT 0,20;

Может быть, заменить RLIKE на MATCH AGAINST ? Будет быстрее?

Таблицы невелики:

mysql> select count(*) from srpm;
+----------+
| count(*) |
+----------+
|    38240 | 
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from rpm;
+----------+
| count(*) |
+----------+
|   109232 | 
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from maintainers;
+----------+
| count(*) |
+----------+
|      250 | 
+----------+
1 row in set (0.01 sec)

Re: [devel] Mysqld CPU usage at www.sisyphus.ru

[Mikhail Yakshin]

>> тогда запрос и explain от него - в студию.
>
> Вот такие запросы тормозят:
>
> SELECT DISTINCT s.name, s.version, s.rel, m.packager, s.summary, s.repo
> FROM srpm as s, maintainers as m, rpm as r WHERE  r.srpm = s.name AND
> m.mail=s.packager AND ( s.name RLIKE 'unichrome'  OR  s.summary RLIKE
> 'unichrome'  OR  s.description RLIKE 'unichrome'  OR  r.namen RLIKE
> 'unichrome' ) AND s.repo='Sisyphus' AND r.repo='Sisyphus' ORDER BY 1
> ASC LIMIT 0,20;
>
> Может быть, заменить RLIKE на MATCH AGAINST ? Будет быстрее?

Во-первых, проверить индексы, по которым производится JOIN. Имеет
смысл выписать это в явный "INNER JOIN ON что-то":

> WHERE  r.srpm = s.name AND m.mail=s.packager

Во-вторых, такой RLIKE скорее всего вообще не использует индексы и
запрос превращается в FULL SCAN. Проверить индексы, по которым
делается RLIKE и заменить его либо на LIKE 'запрос%', либо на
равенство.

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

-- 
WBR, Mikhail Yakshin

Re: [devel] Mysqld CPU usage at www.sisyphus.ru

[Vitaly Lipatov]

On 1 сентября 2009, Grigory Batalov wrote:
> Здравствуйте!
>
> Недавно заметил, что поиск пакетов на www.sisyphus.ru
> тормозит. Это из-за того, что по нажатию "искать" mysqld на 6
> секунд съедает 80-99% CPU (если результат не закеширован).
> Вроде бы, раньше такого не было.
>
> Есть ли соображения, советы?
Запрос не использует индексы, поэтому совершается полный перебор 
таблицы для выборки. Не сильно тормозит только из-за того, что 
вся таблица целиком влезла в память.
У меня сложилось впечатление, что mysql очень слаб в части 
условий с множественными сравнениями (для каждого сравнения он 
бы использовал индекс, но если их несколько, начинается полный 
перебор). Выход я вижу только в разбитии запроса на более 
простые (возможно с использованием вложенных запросов).

P.S. Мнение стороннего оптимизатора базы новостного агенства :)


-- 
С уважением,
Виталий Липатов
Санкт-Петербург
GNU! ALT Linux Team! WINE! LaTeX! LyX! http://freesource.info

Re: [devel] Mysqld CPU usage at www.sisyphus.ru

[Grigory Batalov]

On Tue, 1 Sep 2009 19:03:00 +0400
Mikhail Yakshin wrote:

> > SELECT DISTINCT s.name, s.version, s.rel, m.packager, s.summary, s.repo
> > FROM srpm as s, maintainers as m, rpm as r WHERE  r.srpm = s.name AND
> > m.mail=s.packager AND ( s.name RLIKE 'unichrome'  OR  s.summary RLIKE
> > 'unichrome'  OR  s.description RLIKE 'unichrome'  OR  r.namen RLIKE
> > 'unichrome' ) AND s.repo='Sisyphus' AND r.repo='Sisyphus' ORDER BY 1
> > ASC LIMIT 0,20;
> >
> > Может быть, заменить RLIKE на MATCH AGAINST ? Будет быстрее?
> 
> Во-первых, проверить индексы, по которым производится JOIN. Имеет
> смысл выписать это в явный "INNER JOIN ON что-то":
> 
> > WHERE  r.srpm = s.name AND m.mail=s.packager
> 
> Во-вторых, такой RLIKE скорее всего вообще не использует индексы и
> запрос превращается в FULL SCAN. Проверить индексы, по которым
> делается RLIKE и заменить его либо на LIKE 'запрос%', либо на
> равенство.
> 
> MATCH AGAINST потребует полнотекстовых индексов, да и будет возвращать
> совсем не то, что хочется, как я понял.

Индексов на summary, description сейчас вообще нет.

RLIKE используется для поиска любого совпадения в name, summary,
description. 'запрос%' сработает только на совпадение в начале
строки.

Re: [devel] Mysqld CPU usage at www.sisyphus.ru

[Aleksey Avdeev]

[-- Attachment #1: Type: text/plain, Size: 397 bytes --]

Grigory Batalov пишет:
...
> RLIKE используется для поиска любого совпадения в name, summary,
> description. 'запрос%' сработает только на совпадение в начале
> строки.

   А если '%запрос%'? (Или LIKE непоймёт такого?)

-- 

С уважением. Алексей.



[-- Attachment #2: OpenPGP digital signature --]
[-- Type: application/pgp-signature, Size: 552 bytes --]

Re: [devel] Mysqld CPU usage at www.sisyphus.ru

[Grigory Batalov]

On Tue, 01 Sep 2009 23:21:22 +0400
Aleksey Avdeev wrote:

> > RLIKE используется для поиска любого совпадения в name, summary,
> > description. 'запрос%' сработает только на совпадение в начале
> > строки.
> 
>    А если '%запрос%'? (Или LIKE непоймёт такого?)

Так это почти что RLIKE 'запрос', учитывая, что регулярные выражения
в запросе не используются.

Re: [devel] Mysqld CPU usage at www.sisyphus.ru

[Денис Смирнов]

[-- Attachment #1: Type: text/plain, Size: 585 bytes --]

On Tue, Sep 01, 2009 at 05:08:06PM +0400, Grigory Batalov wrote:

GB> Недавно заметил, что поиск пакетов на www.sisyphus.ru тормозит.
GB> Это из-за того, что по нажатию "искать" mysqld на 6 секунд
GB> съедает 80-99% CPU (если результат не закеширован).
GB> Вроде бы, раньше такого не было.
GB> Есть ли соображения, советы?

apt-get install postgresql8.3-server
apt-get remove MySQL-server

Оптимизатор запросов у постгреса гораздо серьезнее.

-- 
С уважением, Денис

http://freesource.info
----------------------------------------------------------------------------

[-- Attachment #2: Digital signature --]
[-- Type: application/pgp-signature, Size: 197 bytes --]

Re: [devel] Mysqld CPU usage at www.sisyphus.ru

[Mikhail Yakshin]

>> > SELECT DISTINCT s.name, s.version, s.rel, m.packager, s.summary, s.repo
>> > FROM srpm as s, maintainers as m, rpm as r WHERE  r.srpm = s.name AND
>> > m.mail=s.packager AND ( s.name RLIKE 'unichrome'  OR  s.summary RLIKE
>> > 'unichrome'  OR  s.description RLIKE 'unichrome'  OR  r.namen RLIKE
>> > 'unichrome' ) AND s.repo='Sisyphus' AND r.repo='Sisyphus' ORDER BY 1
>> > ASC LIMIT 0,20;
>> >
>> > Может быть, заменить RLIKE на MATCH AGAINST ? Будет быстрее?
>>
>> Во-первых, проверить индексы, по которым производится JOIN. Имеет
>> смысл выписать это в явный "INNER JOIN ON что-то":
>>
>> > WHERE  r.srpm = s.name AND m.mail=s.packager
>>
>> Во-вторых, такой RLIKE скорее всего вообще не использует индексы и
>> запрос превращается в FULL SCAN. Проверить индексы, по которым
>> делается RLIKE и заменить его либо на LIKE 'запрос%', либо на
>> равенство.
>>
>> MATCH AGAINST потребует полнотекстовых индексов, да и будет возвращать
>> совсем не то, что хочется, как я понял.
>
> Индексов на summary, description сейчас вообще нет.
>
> RLIKE используется для поиска любого совпадения в name, summary,
> description. 'запрос%' сработает только на совпадение в начале
> строки.

Подавляющее большинство SQL-реализаций (включая MySQL) не умеет
использовать для поисковых запросов типа '%запрос%' (будь они сделаны
через такой LIKE, через RLIKE, через REGEXP или заданы каким-то иным
способом) индексы, т.е. такой запрос будет всегда приводить к FULL
SCAN.

Вообще задача быстрого поиска произвольной подстроки в некоем
количестве строк - весьма нетривиальна и универсального решения до сих
пор толком нет. Пожалуй, наиболее близко к нему продвинулись Google в
Code Search, но они, редиски, далеко не все свои наработки публикуют.

По сути, в современном SQL способов готовых не так много, все много -
все они в той или иной степени компромиссны:

1) Искать по полнотекстовым индексам => огребаем необходимость строить
и поддерживать эти индексы и всякие слабо совместимые с практической
задачей поиска пакетов по названием ограничения. Например, "libapr1"
по запросу "apr" в полнотекстовом индексе найти малореально. При
построении индексов "libapr1" будет разбито на 2лексемы "libapr" и
"1". Первая будет заиндексирована, вторая выкинута из-за маленькой
длины и, как следствие, высокой частотности. С другой стороны - для
summary этот способ применить может быть и можно.

2) Остановиться волевым решением на поиске по началу вхождения - LIKE
'запрос%'. Субъективно - мне кажется, для пакетов этого должно быть
вполне достаточно.

3) Жить с FULL SCANом, но перестроить запрос таким образом, чтобы FULL
SCAN производился по небольшому количеству записей - что-то типа
O(table1) + O(table2) + O(table3). Сейчас, возможно, запрос
производится по что-то типа O(table1)*O(table2)*O(table3) записей -
потому, что оптимизатор решает делать сначала FULL OUTER JOIN, а потом
уже отсекать из него нужное путем применения WHERE-фразы. Я уже описал
способы решения - что стоит для начала WHERE (условия join'а)
переписать в явные условия join'а. Если не поможет - тогда разделить
это на 3 отдельных запроса (просто вытаскивающие IDшники) + один
запрос, делающий SELECT * FROM table1 INNER JOIN table2 INNER JOIN
table3 WHERE id IN (вытащенные IDшники).

Если задача - "зафиксить побыстрее" - то вариант #3, как мне кажется,
упрется в минут пять работы и, скорее всего, даст результаты в виде
снижения 5-6 секунд до 200-300-400 мс. С моей точки зрения, впрочем,
200-300-400 мс - это непозволительно много для веб-приложения, но
YMMV.

-- 
WBR, Mikhail Yakshin

Re: [devel] Mysqld CPU usage at www.sisyphus.ru

[Денис Смирнов]

[-- Attachment #1: Type: text/plain, Size: 902 bytes --]

On Wed, Sep 02, 2009 at 01:47:33AM +0400, Mikhail Yakshin wrote:
 
MY> Если задача - "зафиксить побыстрее" - то вариант #3, как мне кажется,
MY> упрется в минут пять работы и, скорее всего, даст результаты в виде
MY> снижения 5-6 секунд до 200-300-400 мс. С моей точки зрения, впрочем,
MY> 200-300-400 мс - это непозволительно много для веб-приложения, но
MY> YMMV.

Можно предложить еще один вариант -- создать отдельную таблицу, по которой
и будет производиться поиск. В этом случае такая таблица может целиком
жить в кэше. Или даже сделать временную таблицу (ту самую что всегда живет
в памяти).

Если изобретать "круто и быстро", то никто не запрещает в том же постгресе
написать _свой_ лексический разбор, который будет адекватен для имен
пакетов.

-- 
С уважением, Денис

http://freesource.info
----------------------------------------------------------------------------


[-- Attachment #2: Digital signature --]
[-- Type: application/pgp-signature, Size: 197 bytes --]

Re: [devel] Mysqld CPU usage at www.sisyphus.ru

[Mikhail Yakshin]

2009/9/2 Денис Смирнов <mithraen@altlinux.ru>:
> MY> Если задача - "зафиксить побыстрее" - то вариант #3, как мне кажется,
> MY> упрется в минут пять работы и, скорее всего, даст результаты в виде
> MY> снижения 5-6 секунд до 200-300-400 мс. С моей точки зрения, впрочем,
> MY> 200-300-400 мс - это непозволительно много для веб-приложения, но
> MY> YMMV.
>
> Можно предложить еще один вариант -- создать отдельную таблицу, по которой
> и будет производиться поиск. В этом случае такая таблица может целиком
> жить в кэше. Или даже сделать временную таблицу (ту самую что всегда живет
> в памяти).

Это уже из серии вариантов "нужно что-то делать руками". По большому
счету - при желании - даже префиксно-суффиксные деревья как-то
делаются на SQL: в отдельную таблицу выгружаются все возможные префисы
и суффиксы (развернутые), затем запрос на поиск превращается в SELECT
с JOINом этих 2 таблиц с LIKE 'начало префикса или развернутого
суффикса%'.

> Если изобретать "круто и быстро", то никто не запрещает в том же постгресе
> написать _свой_ лексический разбор, который будет адекватен для имен
> пакетов.

Как минимум, сомневаюсь в том, что это сильно быстро; как максимум -
выражаю сомнения в том, что какой-либо полнотекстовый поиск будет
адекватен для имен пакетов.

-- 
WBR, Mikhail Yakshin