-
![[image]](https://www.balancer.ru/cache/uploads/images/1247/128x128-crop/1247620-2885312134_1e80bd8362_o.jpg)
Вопросы по MySQL
Теги:
slipstream
slipstream
втянувшийся
Ещё про .. ORDER BY pid — для этого запроса сответственно можно попробовать USE INDEX (topic_id)
инфо
инструменты
Mishka
Ром, 1, 3 и 4 запросы явно просят ключа по (topic_id, queued, post_date) или кластеризации по queued и ключа по (topic_id, post_date). Дату можно и на первое место. Даже может быть будет быстрее выбираться.
2 запрос по (pid, topic_id, queued) - опять-таки, по queued можно кластиризовать. Кажется, только кластеризация в mySql не поддерживается.
Но тут важно не переусердствовать - добавление новых индексов может сильно замедлить вставку. Надо поболее статистики - какие запросы идут.
2 запрос по (pid, topic_id, queued) - опять-таки, по queued можно кластиризовать. Кажется, только кластеризация в mySql не поддерживается.
Но тут важно не переусердствовать - добавление новых индексов может сильно замедлить вставку. Надо поболее статистики - какие запросы идут.
slipstream>Ещё про .. ORDER BY pid — для этого запроса сответственно можно попробовать USE INDEX (topic_id)[»]
Особо не поможет - так как queued <> 1 - т.е придется пропустить 1, а все остальное читать, а потом переупорядочивать по pid. Здесь надо pid на первое место переводить. На самом деле, pid у нас наверняка serial и только растет - т.е. он выполняет функции отметок течения времени - что после чего. Вроде сообщение с большим pid должно быть запосщено позже. Это значит, что вместо сортировок по времени надо использовать сортировки по pid (и вывести его на первое место в индексах), а конкретное время использовать в качестве фильтров - для поисков или других операций, в которых важно наличие точного временного промежутка - типа все сообщения за последние три дня.
Особо не поможет - так как queued <> 1 - т.е придется пропустить 1, а все остальное читать, а потом переупорядочивать по pid. Здесь надо pid на первое место переводить. На самом деле, pid у нас наверняка serial и только растет - т.е. он выполняет функции отметок течения времени - что после чего. Вроде сообщение с большим pid должно быть запосщено позже. Это значит, что вместо сортировок по времени надо использовать сортировки по pid (и вывести его на первое место в индексах), а конкретное время использовать в качестве фильтров - для поисков или других операций, в которых важно наличие точного временного промежутка - типа все сообщения за последние три дня.
slipstream
втянувшийся
Mishka> Особо не поможет - так как queued <> 1
Может было бы и так, но я всё это проверил сначала на себе, на mysql соответствующей версии и на 160 метровой базе
Просто я это писать не стал, поскольку может ещё и от конфигурации зависеть... и ещё я поинтересовался багтракером на тему use/force index и explain.
Если у тебя свободное время есть — попробуй сделать такую тестовую таблицу и поэксперементировать:
ну и queued<>1 можно на =0 поменять для сравнения Что получается?
Может было бы и так, но я всё это проверил сначала на себе, на mysql соответствующей версии и на 160 метровой базе
Просто я это писать не стал, поскольку может ещё и от конфигурации зависеть... и ещё я поинтересовался багтракером на тему use/force index и explain.
Если у тебя свободное время есть — попробуй сделать такую тестовую таблицу и поэксперементировать:
code mysql
- // пару раз, чтобы в кеш засосалось
- // потом
- // потом
ну и queued<>1 можно на =0 поменять для сравнения
Что получается?
Это сообщение редактировалось 01.08.2004 в 09:23
Mishka>Особо не поможет - так как queued <> 1 - т.е придется пропустить 1, а все остальное читать, а потом переупорядочивать по pid. Здесь надо pid на первое место переводить. На самом деле, pid у нас наверняка serial и только растет - т.е. он выполняет функции отметок течения времени - что после чего. Вроде сообщение с большим pid должно быть запосщено позже. Это значит, что вместо сортировок по времени надо использовать сортировки по pid (и вывести его на первое место в индексах), а конкретное время использовать в качестве фильтров - для поисков или других операций
Тут два возражения.
1. Чем возрастающее целочисленное время сильно хуже возрастающего целочисленного pid (который, кстати, не непрерывный - удалённые, например, топики и т.п.)
2. Сортировка идёт не по pid, а по времени, т.к. возможна ситуация изменения времени постинга. Например, при подклейке к нему другого с бОльшим временем и т.п.
Тут два возражения.
1. Чем возрастающее целочисленное время сильно хуже возрастающего целочисленного pid (который, кстати, не непрерывный - удалённые, например, топики и т.п.)
2. Сортировка идёт не по pid, а по времени, т.к. возможна ситуация изменения времени постинга. Например, при подклейке к нему другого с бОльшим временем и т.п.
slipstream
втянувшийся
Ром, а ты можешь проверить время исполнения запросов в моём варианте? Именно прямо так, без explain, и не меняя ничего в базе..
У меня получалось что mysql использует индекс только во вторых двух вариантах, и соответственно разница по времени на один-два порядка, правда на два это только на запросах без далеко отстоящего LIMIT и когда таблица не в дисковом кеше.
У меня получалось что mysql использует индекс только во вторых двух вариантах, и соответственно разница по времени на один-два порядка, правда на два это только на запросах без далеко отстоящего LIMIT и когда таблица не в дисковом кеше.
slipstream>Ром, а ты можешь проверить время исполнения запросов в моём варианте?
Проверю чуть позже, но сложное дело. Обычно указанные запросы выполняются за десятые доли секунды и лишь в нескольких процентах случаев - те цифры, что я приводил. Тут нужна статистика. По хорошему - высокая посещаемость нужна.
Проверю чуть позже, но сложное дело. Обычно указанные запросы выполняются за десятые доли секунды и лишь в нескольких процентах случаев - те цифры, что я приводил. Тут нужна статистика. По хорошему - высокая посещаемость нужна.
slipstream
втянувшийся
>.. и лишь в нескольких процентах случаев - те цифры, что я приводил
А, вот даже настолько редко, тогда наверное статистика без дедукции не поможет ))
Вот ещё и Rows_examined: какой-то очень маленький, а у меня этот заметный эффект на сотне тысяч
Тогда вот что интересно: включен ли log-queries-not-using-indexes и соответственно попадают ли те-же запросы в slow query log вне лимита по времени?
И на сколько сейчас лимит (long_query_time), остался 10с по умолчанию? А если снизить до трех-четырех, какое получится распределение торможения у этих запросов — только такие резкие пики в районе 10-20 или и постоянно понемногу?
А, вот даже настолько редко, тогда наверное статистика без дедукции не поможет ))
Вот ещё и Rows_examined: какой-то очень маленький, а у меня этот заметный эффект на сотне тысяч
Тогда вот что интересно: включен ли log-queries-not-using-indexes и соответственно попадают ли те-же запросы в slow query log вне лимита по времени?
И на сколько сейчас лимит (long_query_time), остался 10с по умолчанию? А если снизить до трех-четырех, какое получится распределение торможения у этих запросов — только такие резкие пики в районе 10-20 или и постоянно понемногу?
Это сообщение редактировалось 01.08.2004 в 19:45
slipstream>А, вот даже настолько редко, тогда наверное статистика без дедукции не поможет ))
slipstream>Вот ещё и Rows_examined: какой-то очень маленький, а у меня этот заметный эффект на сотне тысяч
Угум. На других таблицах, естественно, всё шустрее.
slipstream>Тогда вот что интересно: включен ли log-queries-not-using-indexes и соответственно попадают ли те-же запросы в slow query log вне лимита по времени?
Гм. У меня только log_slow_queries стоит.
А такого параметра и не знаю. Погляжу
slipstream>И на сколько сейчас лимит (long_query_time), остался 10с по умолчанию?
5 секунд у меня. Если меньше - то запросос по 2..4 сек уже слишком много
slipstream>Вот ещё и Rows_examined: какой-то очень маленький, а у меня этот заметный эффект на сотне тысяч
Угум. На других таблицах, естественно, всё шустрее.
slipstream>Тогда вот что интересно: включен ли log-queries-not-using-indexes и соответственно попадают ли те-же запросы в slow query log вне лимита по времени?
Гм. У меня только log_slow_queries стоит.
А такого параметра и не знаю. Погляжу
slipstream>И на сколько сейчас лимит (long_query_time), остался 10с по умолчанию?
5 секунд у меня. Если меньше - то запросос по 2..4 сек уже слишком много
slipstream>ну и queued<>1 можно на =0 поменять для сравнения Что получается?[»]
Особо не пробовал. Но разница должна получиться офигительная. Для оптимизатора совпадение по двух начальным полям индекса - это указание на прямое следование индексу, и количество данных оценивается очень просто - при наличии обновленной статистики. А вот с не равно 1 - может быть похуже - в некоторых случаях может последовать отказ от следования индексу - так это указание на то, что все другие значения не фильтруются - а сортируется вообще по третьему полю - отсюда вывод может быть простой - просто перебрать таблицу. Если индексов начинающихся с первого поля несколько, то, понятны проблемы.
Что получается?[»]Особо не пробовал. Но разница должна получиться офигительная. Для оптимизатора совпадение по двух начальным полям индекса - это указание на прямое следование индексу, и количество данных оценивается очень просто - при наличии обновленной статистики. А вот с не равно 1 - может быть похуже - в некоторых случаях может последовать отказ от следования индексу - так это указание на то, что все другие значения не фильтруются - а сортируется вообще по третьему полю - отсюда вывод может быть простой - просто перебрать таблицу. Если индексов начинающихся с первого поля несколько, то, понятны проблемы.
Balancer>Тут два возражения.
Balancer>1. Чем возрастающее целочисленное время сильно хуже возрастающего целочисленного pid (который, кстати, не непрерывный - удалённые, например, топики и т.п.)
Balancer>2. Сортировка идёт не по pid, а по времени, т.к. возможна ситуация изменения времени постинга. Например, при подклейке к нему другого с бОльшим временем и т.п.[»]
Про смену времени понял. Тогда время надо обязательно включать в индексы - тогда сортировка будет делаться не вызовом внешней сортировки, а следование по индексу.
Balancer>1. Чем возрастающее целочисленное время сильно хуже возрастающего целочисленного pid (который, кстати, не непрерывный - удалённые, например, топики и т.п.)
Balancer>2. Сортировка идёт не по pid, а по времени, т.к. возможна ситуация изменения времени постинга. Например, при подклейке к нему другого с бОльшим временем и т.п.[»]
Про смену времени понял. Тогда время надо обязательно включать в индексы - тогда сортировка будет делаться не вызовом внешней сортировки, а следование по индексу.
Примерно то же самое.
code mysql
- # Query_time: 10 Lock_time: 0 Rows_sent: 15 Rows_examined: 390
- # Query_time: 6 Lock_time: 0 Rows_sent: 14 Rows_examined: 88
- # Query_time: 6 Lock_time: 0 Rows_sent: 15 Rows_examined: 390
Поставил нынче ещеночную оптимизацию всех таблиц всех БД. Лучше пока не стало :-/
slipstream
втянувшийся
Ой.. а зачем ты сразу индексы стал добавлять? Это же была только первая версия.
Я потом предлагал ничего не трогая сделать скорее вот что: сначала посмотреть на расширенном логе (с log-queries-not-using-indexes), всегда ли используются индексы, т.е. вдруг оптимизатор не всегда но при каких-то условиях от них отказывается. Т.е. может он почти знает что делает и не обязательно такой запрос выбивается за long_query_time.
Потом посмотреть пересечение по времени медленных запросов с логами апача — какие действия форума в это время запрашивались, попробовать смоделирвать.
Потом, если причина угадана — попробовать заставить его всегда использовать индекс при помощи use/force index.
И всё это лучше сначала дома проверить, на тестовой базе.
Я потом предлагал ничего не трогая сделать скорее вот что: сначала посмотреть на расширенном логе (с log-queries-not-using-indexes), всегда ли используются индексы, т.е. вдруг оптимизатор не всегда но при каких-то условиях от них отказывается. Т.е. может он почти знает что делает и не обязательно такой запрос выбивается за long_query_time.
Потом посмотреть пересечение по времени медленных запросов с логами апача — какие действия форума в это время запрашивались, попробовать смоделирвать.
Потом, если причина угадана — попробовать заставить его всегда использовать индекс при помощи use/force index.
И всё это лучше сначала дома проверить, на тестовой базе.
slipstream>Я потом предлагал ничего не трогая сделать скорее вот что: сначала посмотреть на расширенном логе (с log-queries-not-using-indexes)
Попробовал.
Файл указанный в параметре (пробовал и с минусами и с подчерками) просто не создаётся. Сообщения об ошибке тоже нет.
Попробовал.
Файл указанный в параметре (пробовал и с минусами и с подчерками) просто не создаётся. Сообщения об ошибке тоже нет.
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 нужен для других целей, то надо иметь отдельный индекс.
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 нужен для других целей, то надо иметь отдельный индекс.
Balancer>Файл указанный в параметре (пробовал и с минусами и с подчерками) просто не создаётся. Сообщения об ошибке тоже нет.[»]
Понял. Это флаг. Но толку с него - ноль. Весь лог забит мелкими и безобидными запросами типа
Понял. Это флаг. Но толку с него - ноль. Весь лог забит мелкими и безобидными запросами типа
code mysql
- SELECT * FROM avia_top_counts WHERE 86400*count/(UNIX_TIMESTAMP()-start+1)>00000124 AND (count>50 OR UNIX_TIMESTAMP()-start>86400);
Mishka>pid - не нужен на первом месте
Ок. Снёс. Будем смотреть
(как раз сейчас обновление индекса идёт)
Ок. Снёс. Будем смотреть
(как раз сейчас обновление индекса идёт)
Те же уши.
Хотя теперь по 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)
code mysql
- # Time: 040806 17:29:52
- # User@Host: forum[forum] @ localhost []
- # Query_time: 6 Lock_time: 0 Rows_sent: 15 Rows_examined: 105
- # Time: 040806 17:30:00
- # User@Host: forum[forum] @ localhost []
- # Query_time: 9 Lock_time: 0 Rows_sent: 6 Rows_examined: 261
- # Time: 040806 17:30:01
- # User@Host: forum[forum] @ localhost []
- # Query_time: 6 Lock_time: 0 Rows_sent: 15 Rows_examined: 180
- # Time: 040806 17:30:22
- # User@Host: forum[forum] @ localhost []
- # Query_time: 8 Lock_time: 0 Rows_sent: 2 Rows_examined: 287
- # User@Host: forum[forum] @ localhost []
- # Query_time: 9 Lock_time: 0 Rows_sent: 12 Rows_examined: 207
- # Time: 040806 17:30:32
- # User@Host: forum[forum] @ localhost []
- # Query_time: 7 Lock_time: 0 Rows_sent: 15 Rows_examined: 165
Хотя теперь по 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)
Чую, кардинальным решением будет сброс постингов к которым давно не было обращений в отдельную таблицу и переброс их в основную по их запросу. Благо, с INSERT ... SELECT это нынче будет сделать не так сложно.
Хотя при этом будет опять дополнителная морока с переходом на новые версии :-/
Хотя при этом будет опять дополнителная морока с переходом на новые версии :-/
slipstream
втянувшийся
> Понял. Это флаг. Но толку с него - ноль. Весь лог забит мелкими и безобидными запросами типа
Ром, он забит это я понимаю, очень много всякой фигни будет, но это же всего один раз за сутки снять и потом по нему искать, где нужный запрос в сочетании с Query_time: 0
Ты искал и не нашел или не искал и стер?
> Те же уши. ..
> Хотя теперь по EXPLAIN используется новый индекс!
Не понимаю, зачем решается только задача общей оптимизации выборки, если она в основное время (предположительно без параллельной нагрузки на mysql) идет с адекватной скоростью?
Ром, он забит это я понимаю, очень много всякой фигни будет, но это же всего один раз за сутки снять и потом по нему искать, где нужный запрос в сочетании с Query_time: 0
Ты искал и не нашел или не искал и стер?
> Те же уши. ..
> Хотя теперь по EXPLAIN используется новый индекс!
Не понимаю, зачем решается только задача общей оптимизации выборки, если она в основное время (предположительно без параллельной нагрузки на mysql) идет с адекватной скоростью?
slipstream>Ты искал и не нашел или не искал и стер?
Я искал, но не нашёл. За несколько десятков секунд лог оказался забит кучей безобидных (т.е. занимающих ничтожное время) неиндексных запросов (и индексы к которым не прикрутить, чтобы так отфильтровать), на фоне которых я ничего подозрительного не нашёл. Разве что писать свой скрипт-парсер. Но пока руки до этого не доходят.
slipstream>Не понимаю, зачем решается только задача общей оптимизации выборки, если она в основное время (предположительно без параллельной нагрузки на mysql) идет с адекватной скоростью?[»]
Потому что в некоторые моменты эти выборки идут с неадекватной скоростью и нередко лочат остальные обращения. В результате чего весь форум может подвиснуть на время такой "затянутой" выборки, до 15..25 секунд.
Я искал, но не нашёл. За несколько десятков секунд лог оказался забит кучей безобидных (т.е. занимающих ничтожное время) неиндексных запросов (и индексы к которым не прикрутить, чтобы так отфильтровать), на фоне которых я ничего подозрительного не нашёл. Разве что писать свой скрипт-парсер. Но пока руки до этого не доходят.
slipstream>Не понимаю, зачем решается только задача общей оптимизации выборки, если она в основное время (предположительно без параллельной нагрузки на mysql) идет с адекватной скоростью?[»]
Потому что в некоторые моменты эти выборки идут с неадекватной скоростью и нередко лочат остальные обращения. В результате чего весь форум может подвиснуть на время такой "затянутой" выборки, до 15..25 секунд.
Balancer
Нашёл прекрасную альтернативу старой лошадке phpMyAdmin: chive-project.com
Что интересно, на 100% возможности phpMyAdmin и Chive не пересекаются, и там, и там есть свои фишки, которых нет у конкурента, но реально ковыряться с БД из Chive много удобнее.
// Транслировано с juick.com
Что интересно, на 100% возможности phpMyAdmin и Chive не пересекаются, и там, и там есть свои фишки, которых нет у конкурента, но реально ковыряться с БД из Chive много удобнее.
// Транслировано с juick.com
- Balancer [15.04.2012 12:25]: Перенос сообщений из MongoDB. Кто-то уже пробовал?
Copyright © Balancer 1997..2019
Создано 01.02.2004
Связь с владельцами и администрацией сайта: anonisimov@gmail.com, rwasp1957@yandex.ru и admin@balancer.ru.
Создано 01.02.2004
Связь с владельцами и администрацией сайта: anonisimov@gmail.com, rwasp1957@yandex.ru и admin@balancer.ru.
