объясните с производительностью mysql

[glam]

объясните с производительностью mysql

Всем привет!
Сейчас на моем сайте ключевая проблема - производительность mysql. Не смотря на то, что отдача страниц стоит на отдельном сервере, а отдача файлов - на другом, и посетителей мало - отдача страниц тормозит. Посмотрел в top - mysqld сильно грузит систему, постоянно 0% idle. Я начал вспоминать что я делал, в итоге грохнул наобум созданные индексы таблиц. Сейчас вроде отпустило немного, но все равно работа очень тормозная. Хочу вплотную сесть и разобраться в вопросе, в том числе и оптимизировать запросы. Поэтому прошу вас направить на путь истинный - что читать, в чем разбираться, а на что можно забить.
И еще несколько вопросов:
1) От размера бд mysql может тормозить? За месяц работы статистики учета скачиваний накопилось миллион строк в таблице. Я не говорю про другие таблицы...
2) С индексами таблиц разбираться, они весомо влияют на скорость работы?
3) У меня очень много запросов типа select * from `some_table` where `file_id` = `id`. Звездочку убирать и сотавлять только нужные поля, сильно влияет на производительность? Запросы такого типа как-то оптимизируются?
4) какое-то кегширование можно сделать или нет? Стоит оно этого вообще?

-~{}~ 31.01.08 02:33:

Да, машина на которой крутятся странички и ysql - тупая, селерон. Может все дело в тупости машины? ))

 

[zerkms]

в мануале по mysql есть целая огромная глава по оптимизации

 

[glam]

Вот именно что огромная. Мне нужен практический опыт - что читать, изучать и использовать, а что - нафиг не нужно.

 

[zerkms]

Вот именно что огромная. Мне нужен практический опыт - что читать, изучать и использовать, а что - нафиг не нужно.

очередной искатель панацеи??

 

[magic]

Можешь почитать http://www.mysqlperformanceblog.com

 

[fixxxer]

для начала надо научиться пользоваться командой explain

 

[Wicked]

Для начала неплохо бы проанализировать, насколько у тебя идет активная запись в бд. Write bound и read bound работы с бд оптимизируются по-разному.

В любом случае, для начала нужно включать slow log, чтобы он тебе хотя бы собирал медленные запросы. Медленные запросы потом агрегируются с помощью утилиты mysqlsla, и из них выявляются те, которыми стоит заняться в первую очередь.

После того, как ты найдешь такие медленные запросы, начинаешь их анализировать с помощью sql-команды explain (селекты - напрямую, update и delete - предварительно переделывая в селекты). Explain дает много пищи для размышления над тем, почему какой-то запрос работает медленно, а это обычно наводит на мысли, как их можно исправить.

1) От размера бд mysql может тормозить? За месяц работы статистики учета скачиваний накопилось миллион строк в таблице. Я не говорю про другие таблицы...

Может. Но при эффективном использовании индексов трудозатраты на одну выборку растут в логарифмической зависимости.

2) С индексами таблиц разбираться, они весомо влияют на скорость работы?

На скорость выборок ОЧЕНЬ весомо, но опять же, если они используются и используются эффективно.
На скорость апдейтов и удалений - ОЧЕНЬ весомо в плане выборки тех записей, которые ты собираешься апдейтить или удалять. И немного замедляют за счет того, что на вставках, апдейтах и удалениях приходится апдейтить больше индексов.

3) У меня очень много запросов типа select * from `some_table` where `file_id` = `id`.

wtf?

Звездочку убирать и сотавлять только нужные поля, сильно влияет на производительность?

Звездочки, как правило убираются. Все зависит от того, сколько, какого типа и какого объема лишней информации выбирается. Если тебе нужно узнать один id по другому id, то вытаскивать вместе с этим всякие блобы по мегабайту каждый - неоправданный оверхед.

Запросы такого типа как-то оптимизируются?

Да. Возвращаемся к explain'у

4) какое-то кегширование можно сделать или нет? Стоит оно этого вообще?

Обычно это определяется несколькими факторами. Самый главный - это соотношение hits/misses, который говорит о том, насколько какой-то конкретный кэш эффективно работает. Также важны и другие характеристики кэша:
* Сложность вычислений кэшируемых значений. Нет смысла хранить пары $str => md5($str), потому что их куда проще посчитать на лету.
* Размер занимаемой памяти. Например, не имеет смысла хранить для каждого юзера список профайлов его друзей целиком. Лучше только список id, по которым, в свою очередь, доставать уже конкретные профайлы друзей из того же кэша...
* Простота инвалидации кэша: насколько просто при изменении состояния системы очистить относящиеся к изменению закэшированные параметры. Полагаться на автоматическое протухание по времени можно далеко не всегда.

 

[fixxxer]

>> slow log
по поводу slow query log - там есть маленькая проблемка, что им без особых ухищрений собираются только очень медленные запросы (> 2 сек), что позволяет увидеть только те запросы где уже совсем задница
варианта два - взять патчик, который пробегал на mysqlperformanceblog, либо собирать статистику прямо в class mysql или что там у тебя.

про memcached есть неплохая для введения в тему презентация http://softsearch.ru/i/download/mmug.pdf

 

[zerkms]

fixxxer

только очень медленные запросы (> 2 сек)

а не больше 1 ли?

 

[fixxxer]

если 1 поставть то в лог попадает что угодно из-за грубости используемого метода измерения, хотя может уже и поправили

 

[glam]

Спасибо, пока ничего не понял, понял только, что я ничего не знаю про mysql .
Буду разбираться, направление понятно. Огромное спасибо за подробные ответы!

 

[Сергей Тарасов]

Автор оригинала: glam
Спасибо, пока ничего не понял, понял только, что я ничего не знаю про mysql .
Буду разбираться, направление понятно. Огромное спасибо за подробные ответы!

Если нет желания и возможности разбираться и есть хорошее железо, можно просто удвоить(утроить и т.п.) ресурсы, отдаваемые MySQL.

 

[Wicked]

Сергей Тарасов

Если нет желания и возможности разбираться и есть хорошее железо, можно просто удвоить(утроить и т.п.) ресурсы, отдаваемые MySQL.

Помогает далеко не всегда, ибо джоин таблички на 1000000 записей самой к себе без использования индексов убьет любой сервер. Это я утрирую, но все же...

 

[Сергей Тарасов]

Автор оригинала: Wicked
Сергей Тарасов

Помогает далеко не всегда, ибо джоин таблички на 1000000 записей самой к себе без использования индексов убьет любой сервер. Это я утрирую, но все же...

Согласен.

 

[glam]

Начал анализировать код и запросы - ой ой ой ))) Какой кошмар. Вообще говоря не оптимальная структура бд. При другой структуре запросы были намного проще.
Нашел в одном очень часто используемом месте LIKE, хотя там достаточно обычного =. Поменял, все работает, и чуть быстрее.
Вот этот запрос (вместо знака "=" стоял LIKE)
$query = "SELECT * FROM some_table WHERE `property` = '$property' ORDER BY `name` ASC";
Как создать правильно индекс по полю `property`? Поле имеет тип 'text'. Данные реально хранятся не очень длинные, до 1000 символов. Стандартный индекс создать не получается, пишет ошибку, а полнотекстовый индекс не поможет для него другой запрос писать вроде нужно (с match)?

-~{}~ 01.02.08 01:32:

Про мощность сервера - у меня мало посетителей и сильные тормоза. Значит корявый код. Сейчас анализировал - действительно корявый Так что сначала нужно весь код и запросы вычистить.

 

[Mr_Max]

создать не получается, пишет ошибку

Естественно. Поле ведь -

text

-~{}~ 01.02.08 00:49:

месте LIKE, хотя там достаточно обычного =

Ага.
И юзер у тебя в состоянии ввести (если их юзер, конечно вводит)

до 1000 символов.

 

[glam]

Сделал fulltext индекс и запрос match against. Работать работает, посмотрим на производительность.

 

[Wicked]

SELECT * FROM some_table WHERE `property` = '$property' ORDER BY `name` ASC

как правило, оптимизируется с помощью составного индекса (`property`, `name`):

id  select_type       table type  possible_keys  key  key_len    ref  rows       Extra
 1       SIMPLE  some_table   ref           p_n  p_n      XXX  const  YYY  Using where

Вообще не нужно бояться во время оптимизации запросов создавать много лишних индексов в разных комбинациях - (`name`), (`property`), (`name`, `property`), (`property`, `name`) и смотреть с помощью EXPLAIN, который из них выберет оптимизатор запросов. После лишние удаляются.

 

[zerkms]

Wicked

Вообще не нужно бояться во время оптимизации запросов создавать много лишних индексов в разных комбинациях - (`name`), (`property`), (`name`, `property`), (`property`, `name`) и смотреть с помощью EXPLAIN, который из них выберет оптимизатор запросов. После лишние удаляются.

напрашивается продолжение мысли: "написание тулзы, которая генерит разные запросы + индексы и выбирает из них те, которые предпочёл мускул" )

 

[glam]

Разобрался в чем было дело. Все оказалось круче Систему убивало огромное число маленьких ненужных запросов.
На самом деле мой лайк выполнялся менее чем за 0,0004 сек. А вот его результаты выводились почти 1 сек!!! Начал ковырять - неоптимизированный код. Для каждой строки результата запроса с лайк вызывалась ф-я. ей передавался айди записи. Ф-я опять делала селект по айди. И не один, а много - долго рассказывать. причем в этой ф-и есть вложенные вызовы других ф-й. им тоже передается айди. В итоге дерево ненужных вызовов. Все переписал к чертям. Всем ф-ям насквозь помимо айди передаю результаты первого главного запроса. Код выглядит сейчас безобразно, скорость отдачи выросла фантастически - сейчас ответ печатается за 0,07 сек вместо 0,89 сек. Причем там еще очень много чего подровнять еще можно.
Кстати в главном запросе вернулся к лайк.
Всем огромное спасибо, особенно Wicked !