Вопросы по MySQL

 
1 2 3
RU slipstream #31.07.2004 05:55
+
-
edit
 

slipstream

втянувшийся
Ещё про .. ORDER BY pid — для этого запроса сответственно можно попробовать USE INDEX (topic_id)
+
-
edit
 

Mishka

модератор
★★★

Ром, 1, 3 и 4 запросы явно просят ключа по (topic_id, queued, post_date) или кластеризации по queued и ключа по (topic_id, post_date). Дату можно и на первое место. Даже может быть будет быстрее выбираться.

2 запрос по (pid, topic_id, queued) - опять-таки, по queued можно кластиризовать. Кажется, только кластеризация в mySql не поддерживается.

Но тут важно не переусердствовать - добавление новых индексов может сильно замедлить вставку. Надо поболее статистики - какие запросы идут.
 
CA Mishka #01.08.2004 06:47  @slipstream#31.07.2004 05:55
+
-
edit
 

Mishka

модератор
★★★

slipstream>Ещё про .. ORDER BY pid — для этого запроса сответственно можно попробовать USE INDEX (topic_id)[»]

Особо не поможет - так как queued <> 1 - т.е придется пропустить 1, а все остальное читать, а потом переупорядочивать по pid. Здесь надо pid на первое место переводить. На самом деле, pid у нас наверняка serial и только растет - т.е. он выполняет функции отметок течения времени - что после чего. Вроде сообщение с большим pid должно быть запосщено позже. Это значит, что вместо сортировок по времени надо использовать сортировки по pid (и вывести его на первое место в индексах), а конкретное время использовать в качестве фильтров - для поисков или других операций, в которых важно наличие точного временного промежутка - типа все сообщения за последние три дня.
 
RU slipstream #01.08.2004 07:32
+
-
edit
 

slipstream

втянувшийся
Mishka> Особо не поможет - так как queued <> 1

Может было бы и так, но я всё это проверил сначала на себе, на mysql соответствующей версии и на 160 метровой базе ;)
Просто я это писать не стал, поскольку может ещё и от конфигурации зависеть... и ещё я поинтересовался багтракером на тему use/force index и explain.

Если у тебя свободное время есть — попробуй сделать такую тестовую таблицу и поэксперементировать:

code mysql
  1. // пару раз, чтобы в кеш засосалось
  2.  
  3. SELECT * FROM ib_posts WHERE topic_id=xx AND queued <> 1 ORDER BY pid LIMIT 50,50;
  4.  
  5. // потом
  6.  
  7. SELECT pid FROM ib_posts WHERE topic_id=xx AND queued <> 1 ORDER BY pid LIMIT 50,50;
  8.  
  9. // потом
  10.  
  11. SELECT * FROM ib_posts USE INDEX (topic_id) WHERE topic_id=xx AND queued <> 1 ORDER BY pid LIMIT 50,50;


ну и queued&lt;&gt;1 можно на =0 поменять для сравнения ;) Что получается?
Это сообщение редактировалось 01.08.2004 в 09:23
+
-
edit
 

Balancer

администратор
★★★★★
Mishka>Особо не поможет - так как queued <> 1 - т.е придется пропустить 1, а все остальное читать, а потом переупорядочивать по pid. Здесь надо pid на первое место переводить. На самом деле, pid у нас наверняка serial и только растет - т.е. он выполняет функции отметок течения времени - что после чего. Вроде сообщение с большим pid должно быть запосщено позже. Это значит, что вместо сортировок по времени надо использовать сортировки по pid (и вывести его на первое место в индексах), а конкретное время использовать в качестве фильтров - для поисков или других операций

Тут два возражения.
1. Чем возрастающее целочисленное время сильно хуже возрастающего целочисленного pid (который, кстати, не непрерывный - удалённые, например, топики и т.п.)

2. Сортировка идёт не по pid, а по времени, т.к. возможна ситуация изменения времени постинга. Например, при подклейке к нему другого с бОльшим временем и т.п.
 
RU slipstream #01.08.2004 15:15
+
-
edit
 

slipstream

втянувшийся
Ром, а ты можешь проверить время исполнения запросов в моём варианте? Именно прямо так, без explain, и не меняя ничего в базе..

У меня получалось что mysql использует индекс только во вторых двух вариантах, и соответственно разница по времени на один-два порядка, правда на два это только на запросах без далеко отстоящего LIMIT и когда таблица не в дисковом кеше.
+
-
edit
 

Balancer

администратор
★★★★★
slipstream>Ром, а ты можешь проверить время исполнения запросов в моём варианте?

Проверю чуть позже, но сложное дело. Обычно указанные запросы выполняются за десятые доли секунды и лишь в нескольких процентах случаев - те цифры, что я приводил. Тут нужна статистика. По хорошему - высокая посещаемость нужна.
 
RU slipstream #01.08.2004 19:19
+
-
edit
 

slipstream

втянувшийся
>.. и лишь в нескольких процентах случаев - те цифры, что я приводил

А, вот даже настолько редко, тогда наверное статистика без дедукции не поможет ))
Вот ещё и Rows_examined: какой-то очень маленький, а у меня этот заметный эффект на сотне тысяч

Тогда вот что интересно: включен ли log-queries-not-using-indexes и соответственно попадают ли те-же запросы в slow query log вне лимита по времени?

И на сколько сейчас лимит (long_query_time), остался 10с по умолчанию? А если снизить до трех-четырех, какое получится распределение торможения у этих запросов — только такие резкие пики в районе 10-20 или и постоянно понемногу?
Это сообщение редактировалось 01.08.2004 в 19:45
+
-
edit
 

Balancer

администратор
★★★★★
slipstream>А, вот даже настолько редко, тогда наверное статистика без дедукции не поможет ))

:)

slipstream>Вот ещё и Rows_examined: какой-то очень маленький, а у меня этот заметный эффект на сотне тысяч

Угум. На других таблицах, естественно, всё шустрее.

slipstream>Тогда вот что интересно: включен ли log-queries-not-using-indexes и соответственно попадают ли те-же запросы в slow query log вне лимита по времени?

Гм. У меня только log_slow_queries стоит.
А такого параметра и не знаю. Погляжу :)

slipstream>И на сколько сейчас лимит (long_query_time), остался 10с по умолчанию?

5 секунд у меня. Если меньше - то запросос по 2..4 сек уже слишком много :)
 
CA Mishka #03.08.2004 05:48  @slipstream#01.08.2004 07:32
+
-
edit
 

Mishka

модератор
★★★

slipstream>ну и queued&lt;&gt;1 можно на =0 поменять для сравнения ;) Что получается?[»]

Особо не пробовал. Но разница должна получиться офигительная. Для оптимизатора совпадение по двух начальным полям индекса - это указание на прямое следование индексу, и количество данных оценивается очень просто - при наличии обновленной статистики. А вот с не равно 1 - может быть похуже - в некоторых случаях может последовать отказ от следования индексу - так это указание на то, что все другие значения не фильтруются - а сортируется вообще по третьему полю - отсюда вывод может быть простой - просто перебрать таблицу. Если индексов начинающихся с первого поля несколько, то, понятны проблемы.
 
+
-
edit
 

Mishka

модератор
★★★

Balancer>Тут два возражения.
Balancer>1. Чем возрастающее целочисленное время сильно хуже возрастающего целочисленного pid (который, кстати, не непрерывный - удалённые, например, топики и т.п.)
Balancer>2. Сортировка идёт не по pid, а по времени, т.к. возможна ситуация изменения времени постинга. Например, при подклейке к нему другого с бОльшим временем и т.п.[»]

Про смену времени понял. Тогда время надо обязательно включать в индексы - тогда сортировка будет делаться не вызовом внешней сортировки, а следование по индексу.
 
+
-
edit
 

Balancer

администратор
★★★★★
Сделал
ALTER TABLE `ib_posts` ADD INDEX `pid_tid_que_date` ( `pid` , `topic_id` , `queued` , `post_date` )

Заменил все "<> 1" на "= 0".
 
+
-
edit
 

Balancer

администратор
★★★★★
Примерно то же самое.
code mysql
  1. # Query_time: 10  Lock_time: 0  Rows_sent: 15  Rows_examined: 390
  2. SELECT pid FROM ib_posts WHERE topic_id=27128 and queued=0 ORDER BY post_date asc LIMIT 180,15;
  3.  
  4. # Query_time: 6  Lock_time: 0  Rows_sent: 14  Rows_examined: 88
  5. SELECT pid FROM ib_posts WHERE topic_id=28486 and queued=0 ORDER BY post_date asc LIMIT 30,15;
  6.  
  7. # Query_time: 6  Lock_time: 0  Rows_sent: 15  Rows_examined: 390
  8. SELECT pid FROM ib_posts WHERE topic_id=27128 and queued=0 ORDER BY post_date asc LIMIT 180,15;
 
+
-
edit
 
+
-
edit
 

Balancer

администратор
★★★★★
Поставил нынче ещеночную оптимизацию всех таблиц всех БД. Лучше пока не стало :-/
 
RU slipstream #04.08.2004 22:26
+
-
edit
 

slipstream

втянувшийся
Ой.. а зачем ты сразу индексы стал добавлять? Это же была только первая версия.

Я потом предлагал ничего не трогая сделать скорее вот что: сначала посмотреть на расширенном логе (с log-queries-not-using-indexes), всегда ли используются индексы, т.е. вдруг оптимизатор не всегда но при каких-то условиях от них отказывается. Т.е. может он почти знает что делает и не обязательно такой запрос выбивается за long_query_time.

Потом посмотреть пересечение по времени медленных запросов с логами апача — какие действия форума в это время запрашивались, попробовать смоделирвать.

Потом, если причина угадана — попробовать заставить его всегда использовать индекс при помощи use/force index.

И всё это лучше сначала дома проверить, на тестовой базе.
+
-
edit
 

Balancer

администратор
★★★★★
slipstream>Я потом предлагал ничего не трогая сделать скорее вот что: сначала посмотреть на расширенном логе (с log-queries-not-using-indexes)

Попробовал.
Файл указанный в параметре (пробовал и с минусами и с подчерками) просто не создаётся. Сообщения об ошибке тоже нет.
 
+
-
edit
 

Mishka

модератор
★★★

Balancer>Сделал
Balancer>ALTER TABLE `ib_posts` ADD INDEX `pid_tid_que_date` ( `pid` , `topic_id` , `queued` , `post_date` )
Balancer>Заменил все "<> 1" на "= 0".[»]

Экспериментировать на живой базе стремно - тут slipstream прав, но все же.

Индекс для указанных SQL-й надо бы такой:
ALTER TABLE `ib_posts` ADD INDEX `pid_tid_que_date` ( `topic_id` , `queued` , `post_date` )
pid - не нужен на первом месте, т.к. он все ломает - соответствие индексу строится по where и order частям. А тут pid на первом месте, в результате индекс отбрасывается. Если pid нужен для других целей, то надо иметь отдельный индекс.
 
RU Balancer #06.08.2004 17:06  @Balancer#06.08.2004 16:59
+
-
edit
 

Balancer

администратор
★★★★★
Balancer>Файл указанный в параметре (пробовал и с минусами и с подчерками) просто не создаётся. Сообщения об ошибке тоже нет.[»]

Понял. Это флаг. Но толку с него - ноль. Весь лог забит мелкими и безобидными запросами типа
code mysql
  1. SELECT * FROM avia_top_counts WHERE 86400*count/(UNIX_TIMESTAMP()-start+1)>00000124 AND (count>50 OR UNIX_TIMESTAMP()-start>86400);
 
+
-
edit
 

Balancer

администратор
★★★★★
Mishka>pid - не нужен на первом месте

Ок. Снёс. Будем смотреть :)
(как раз сейчас обновление индекса идёт)
 
+
-
edit
 

Balancer

администратор
★★★★★
Те же уши.

code mysql
  1. # Time: 040806 17:29:52
  2. # User@Host: forum[forum] @ localhost []
  3. # Query_time: 6  Lock_time: 0  Rows_sent: 15  Rows_examined: 105
  4. SELECT pid FROM ib_posts WHERE topic_id=21423 and queued=0 ORDER BY post_date asc LIMIT 90,15;
  5. # Time: 040806 17:30:00
  6. # User@Host: forum[forum] @ localhost []
  7. # Query_time: 9  Lock_time: 0  Rows_sent: 6  Rows_examined: 261
  8. SELECT pid FROM ib_posts WHERE topic_id=26058 and queued=0 ORDER BY post_date asc LIMIT 255,15;
  9. # Time: 040806 17:30:01
  10. # User@Host: forum[forum] @ localhost []
  11. # Query_time: 6  Lock_time: 0  Rows_sent: 15  Rows_examined: 180
  12. SELECT pid FROM ib_posts WHERE topic_id=23830 and queued=0 ORDER BY post_date asc LIMIT 165,15;
  13. # Time: 040806 17:30:22
  14. # User@Host: forum[forum] @ localhost []
  15. # Query_time: 8  Lock_time: 0  Rows_sent: 2  Rows_examined: 287
  16. SELECT pid FROM ib_posts WHERE topic_id=24147 and queued=0 ORDER BY post_date asc LIMIT 285,15;
  17. # User@Host: forum[forum] @ localhost []
  18. # Query_time: 9  Lock_time: 0  Rows_sent: 12  Rows_examined: 207
  19. SELECT pid FROM ib_posts WHERE topic_id=27915 and queued=0 ORDER BY post_date asc LIMIT 195,15;
  20. # Time: 040806 17:30:32
  21. # User@Host: forum[forum] @ localhost []
  22. # Query_time: 7  Lock_time: 0  Rows_sent: 15  Rows_examined: 165
  23. SELECT pid FROM ib_posts WHERE topic_id=27128 and queued=0 ORDER BY post_date asc LIMIT 150,15;


Хотя теперь по EXPLAIN используется новый индекс!

mysql> EXPLAIN SELECT pid FROM ib_posts WHERE topic_id=27128 and queued=0 ORDER BY post_date asc LIMIT 150,15;
±---+-------------+----------+------+---------------------------+------------------+---------+-------------+------+-------------+
| id | select_type | table    | type | possible_keys            | key              | key_len | ref        | rows | Extra      |
±---+-------------+----------+------+---------------------------+------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | ib_posts | ref  | topic_id,pid_tid_que_date | pid_tid_que_date |      5 | const,const |  366 | Using where |
±---+-------------+----------+------+---------------------------+------------------+---------+-------------+------+-------------+
1 row in set (0,00 sec)

 
+
-
edit
 

Balancer

администратор
★★★★★
Чую, кардинальным решением будет сброс постингов к которым давно не было обращений в отдельную таблицу и переброс их в основную по их запросу. Благо, с INSERT ... SELECT это нынче будет сделать не так сложно.

Хотя при этом будет опять дополнителная морока с переходом на новые версии :-/
 
RU slipstream #09.08.2004 12:07
+
-
edit
 

slipstream

втянувшийся
> Понял. Это флаг. Но толку с него - ноль. Весь лог забит мелкими и безобидными запросами типа

Ром, он забит это я понимаю, очень много всякой фигни будет, но это же всего один раз за сутки снять и потом по нему искать, где нужный запрос в сочетании с Query_time: 0

Ты искал и не нашел или не искал и стер?

> Те же уши. ..
> Хотя теперь по EXPLAIN используется новый индекс!

:(

Не понимаю, зачем решается только задача общей оптимизации выборки, если она в основное время (предположительно без параллельной нагрузки на mysql) идет с адекватной скоростью?
RU Balancer #09.08.2004 12:27  @slipstream#09.08.2004 12:07
+
-
edit
 

Balancer

администратор
★★★★★
slipstream>Ты искал и не нашел или не искал и стер?

Я искал, но не нашёл. За несколько десятков секунд лог оказался забит кучей безобидных (т.е. занимающих ничтожное время) неиндексных запросов (и индексы к которым не прикрутить, чтобы так отфильтровать), на фоне которых я ничего подозрительного не нашёл. Разве что писать свой скрипт-парсер. Но пока руки до этого не доходят.

slipstream>Не понимаю, зачем решается только задача общей оптимизации выборки, если она в основное время (предположительно без параллельной нагрузки на mysql) идет с адекватной скоростью?[»]

Потому что в некоторые моменты эти выборки идут с неадекватной скоростью и нередко лочат остальные обращения. В результате чего весь форум может подвиснуть на время такой "затянутой" выборки, до 15..25 секунд.
 
+
-
edit
 

Balancer

администратор
★★★★★
MySQL администрирование базы данных
Нашёл прекрасную альтернативу старой лошадке phpMyAdmin: chive-project.com
Что интересно, на 100% возможности phpMyAdmin и Chive не пересекаются, и там, и там есть свои фишки, которых нет у конкурента, но реально ковыряться с БД из Chive много удобнее.

// Транслировано с juick.com
 

1 2 3

в начало страницы | новое
 
Поиск
Настройки
Твиттер сайта
Статистика
Рейтинг@Mail.ru