|
Задавайте вопросы, мы ответим
Вы не зашли.
Произошла такая странная ситуация... Вот, значит, некая БД, с которой работает приложение. Время на исполнение некоего конкретного запроса колеблется от 30 до 50 секунд. Количество строк в результате в районе 400000. И вдруг, однажды, этот же запрос при прочих равных отработал менее чем за секунду. Так повторилось несколько раз и опять время стало большим. Собственно, теперь ситуация какая - я вижу что время выборки можно существенно уменьшить, но не понимаю как. Настройками немного поигрался туда-сюда, но должного эффекта не добился. Подскажите, пожалуйста, куда копнуть? Какими настройками поиграться? Может быть дополнительные факторы, связанные с настройками компьтера или операционки?
Отредактированно Lem0nti (06.08.2008 11:46:31)
Неактивен
Если этот аблолютно тот же запрос, то мог сработать кэш запросов.
Посмотрите
show VARIABLES LIKE 'query_cache_size';
Неактивен
variable _name value
query_cache_size 0
О как ... полагаю его надо увеличить?
Неактивен
Да, имеет смысл увеличить. Но кэш работает только если повторяется один и тот же запрос, а используемые таблицы между запроосами не менялись.
Неактивен
О каких объёмах этого кэша может идти речь?
Неактивен
Все зависит от объема запросов, которые будут в нем храниться. Можно установить для начала 16M, а потом смотреть насколько он используется
SHOW STATUS LIKE 'Q%';
Неактивен
variable _name value
Qcache_free_blocks 1
Qcache_free_memory 16681088
Qcache_hits 6
Qcache_inserts 39
Qcache_lowmem_prunes 0
Qcache_not_cached 237
Qcache_queries_in_cache 31
Qcache_total_blocks 81
Questions 7977848
Вот такие результаты. Скорость действительно заметно увеличилась. Правильно ли я понимаю эти результаты в том ключе, что можно кэш поставить поменьше?
Неактивен
Посмторите после некоторого периода работы сервера, если значительный объем памяти останется свободным, то можно уменьшить.
Также посмотрите на SHOW STATUS LIKE 'key%', возможно надо увеличить key_buffer или загрузить в память некоторые ключи таблиц, см. http://sqlinfo.ru/articles/info/3.html
Неактивен
Ещё кратенький вопрос по поводу кэша запросов - если ли какие-либо рекомендации по его максимальному значению? Вот у меня на серваке пусть гиг оперативки, могу ли я поставить кэш запросов, например, 500 метров и быть уверенным что этого хватит на энное количество лет вперёд?
Неактивен
Кэш на годы вперед ставить не имеет смысла, лучше периодически смотреть на статус. Большой кэш поставить никто не запрещает, конечно.
Неактивен
Если не касаться кеширования запросов, то относительно Вашей ситуации рискну предположить следующее:
На своем примере расскажу.
Была у меня "таблица - поле -запрос". И поле входило в несколько составных ключей.
Причем как крайнее левое поле составного индекса входило только в один.
В mysql перед выполнением запросов оптимизатор запросов выбирает каким ключем воспользоваться чтобы поработать с полем. Ясное дело, что он старается выбрать оптимальный вариант. Причем! выбор индекса из нескольких может варьироваться от выполнения к выполнению запроса (в зависимости от таких факторов как размер таблиц и наверняка других (например, наличие уже подкаченного индекса в кеше)).
ТАк вот, в моем случае я попробовал с помощью FORCE INDEX сам указать какой индекс следует использовать и скорость запроса выросла.
Т.е. оптимизатор не выбирал в моем случае лучший вариант. Т.е. он выбирал вариант в котором переборок было меньше скорость все равно была ниже.
SELECT ... concat(ds1.name,ds2.name) ... FROM data_storage ds1 FORCE INDEX (index1), data_storage ds2 FORCE INDEX (index1) WHERE ...
Как я понимаю это было связано с тем, что index1 в моем случае всего один раз загружался в память и использовался 2 раза, хотя не был оптимальным для всех задач выборки, но лишние переборы оказались быстрее чем подкачка index2 с диска.
В итоге: может быть скорость выполнения вашего запроса так изменилась, потому что оптимизатор выбрал другой ключ несколько раз, а потом вернулся к первому варианту.
Если у вас несколько клюей попробуйте поиграть с FORCE INDEX
Неактивен
Кстати, можно конкретный индекс загрузить в память, можно даже отдельный кэш для него создать, см. http://sqlinfo.ru/articles/info/3.html
Неактивен
Спасибо, обязательно попробую и с индексами тоже. Правда у меня нет сложностей в духе нескольких составных ключей на таблицу.
Неактивен
Здрасьте ещё раз 
. Продолжу мучить вас растерянными вопросами.
Вот спустя несколько дней работы с кэшем в 16 мегабайт, время исполнения запросов опять увеличилось. Делаю SHOW STATUS LIKE 'Q%' ... , вижу что значение Qcache_free_memory достаточно близко к максимуму. Попробовал увеличить кэш вдвое - эффекта не дало. Затем, согласно рекомендациям из материала по использованию кэшей индексов, создал именованый кэш, в который подгрузил индексы самой большой таблицы. Эффект это возымело, но и породило один важный вопросик: подскажите, пожалуйста, как и что мне прописать в my.ini, чтобы на старте создавался этот кэш и происходила загрузка в него индексов таблицы? Может быть сохранить в некий sql-файл и запускать его при старте? Но опять же - не умею, научите, пожалуйста.
Неактивен
Что-то я совсем потерялся... Буквально через пару часов от былой прыти не осталось и следа. И кэширование индексов тоже не помогает. Я и так-то не особо понимал, а теперь вообще голова кругом... Ведь увеличение размера кэша, кэширование индексов, это должно ускорять. Однако на данный момент всё опять вернулось в состояние недельной давности.
Ещё момент - нормально ли получить такой ответ сервера при исполнении 'cache index...':
Query OK, -1 rows affected (0 ms)
???
Отредактированно Lem0nti (11.08.2008 15:08:14)
Неактивен
в my.cnf следует написать
init-file=/путь/к/sql/файлу
Кэши это далеко не все, следует работать над самими запросами. См. Общий обзор подходов к оптимизации MySQL
Неактивен
С запросами-то понятно. Но во всех случаях он один. Сейчас вопрос стоит о настройках. Мне совсем не понятно почему работоспособность опять ухудшилась. Совсем не понятно почему я сейчас, опять загружая индекс в кэш, не имею никакого эффекта. Сейчас пасусь на Mysql.com в попытках вытащить ещё какой-нибудь инфы.
Кстати, таблицы InnoDb. Нет ли тут какой-нибудь специфики против таблиц MyISAM?
Неактивен
Есть специфика, key_buffer только для таблиц MyISAM.
Для InnoDB обший буфер для ключей и данных innodb_buffer_pool_size
Неактивен
Хм ... как мне тогда понимать предыдущие выигрыши в скорости? Сейчас проверил на всякий случай ещё раз - все таблицы, участвующие в запросе, имеют тип InnoDB.
Неактивен
Lem0nti написал:
Хм ... как мне тогда понимать предыдущие выигрыши в скорости?
Случайность. Либо связаны с перезапуском сервера.
Неактивен
Кстати query_cache - кэш запросов - работает как для MyISAM, так и для InnoDB
Неактивен
А что за запрос-то? (если не секрет, конечно).
Неактивен
Приводить целиком его тут не буду. Вяжется 8 таблиц... Чеки+события чеков+товары+кассиры+кассы+карты ... в таком духе.
Полагаю, вы хотели бы порекомендовать оптимизировать сам запрос? Это возможно. НО! Если я вижу что есть очевидные пути ускорения работы без вмешательства в сам софт, то, пока что, буду идти таким путём. Тем более что в скором времени многое в софтине будет по другому, и не хочется серьёзно модифицировать версию вот-вот станущую неактуальной.
Неактивен
Оптимизация запроса - очень сильная часть и в некоторых случаях без нее продвинуться довольно сложно. Внешние действия - увеличение кэшей, но кэши работают не во всех случаях и есть много причин, по которым они могут не работать или не быть эффективными (например, частые обновления одной из таблиц в запросе приводят к тому, что query_cache неэффективен, а обращения к разным частям больших таблиц делают неэффективным buffer_pool)
Неактивен
Статья в тему: Кэширование запросов в MySQL
Неактивен
