SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 20.06.2008 16:48:19

slakey
Участник
Зарегистрирован: 20.06.2008
Сообщений: 16

Блокировки таблиц

Есть база данных, достаточно интенсивно используемая (более 1000 запросов в секунду). Иногда (почти не зависимо от нагрузки - почти, потому что глубокой ночью, например, ни разу такого не было) база намертво повисает.
Я захожу суперпользователем, смотрю processlist - почти у всех запросов статус Locked. У запросов, которые висят дольше всего - статус "freeing items" (вот сейчас их было 8 штук и все селекты). Причём помогает от этой беды - только перезапуск сервера.
Стоит опция wait_timeout = 600, но даже если висит оно больше 600 секунд - соединения не отваливаются.

Все таблицы на MyISAM, сервер - 5.1.24-rc, ОС (если это важно) - FreeBSD 7.0. Но такая проблема наблюдалась и на mysql4 и на FreeBSD 6.0.
Подскажите, что с такой бедой делать - пока что долгих простоев удавалось избежать, но хотелось бы от этих "freeing items" избавиться..

Неактивен

 

#2 20.06.2008 17:40:17

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Блокировки таблиц

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

Неактивен

 

#3 20.06.2008 18:04:06

slakey
Участник
Зарегистрирован: 20.06.2008
Сообщений: 16

Re: Блокировки таблиц

Ну... возможно, на дисковую часть нагрузка и есть, но она ведь должна быть достаточно стабильна.
То, что база не залезает в своп - в этом я уверен. И память ещё свободная есть.. На сервере вообще крутится только mysql, так что вся нагрузка только от него.
Да и с дисковой частью там тоже, вроде как всё должно быть неплохо - специально поставили аппаратный RAID1.

Неактивен

 

#4 20.06.2008 19:01:16

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Блокировки таблиц

Буквально сегодня видел, как база, которой выделено 12 гигабайт ОЗУ, сожрала 16
и выела еще 2 в свопе smile

Что касается стабильности - совершенно необязательно. ОС может вести себя часто
достаточно непредсказуемо. Например, она может решить "вдруг" зафлашить 10 гигабайт
файлового кэша на диск. Лучше помониторить дисковую активность, например, iostat -kx 1.

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

Кстати, сделайте в следующий раз show processlist в файлик - может оказаться что-то совсем
прозаичное типа бэкапа, который залочил табличку и медленно читает из нее данные wink

Неактивен

 

#5 20.06.2008 19:24:08

slakey
Участник
Зарегистрирован: 20.06.2008
Сообщений: 16

Re: Блокировки таблиц

Просто насколько я помню freebsd если своп использовал, он потом пишет в топе, что сколько-то там "used". А у меня там всё чисто пока..

А RAID1 должен вообще-то давать увеличенную скорость чтения, что мне как раз интересно, поскольку у меня больше селектов.

show processlist в файлике есть, вдумчиво читал - ничего не обнаружил..

Сейчас решил попытаться, раз такие подозрения на диск, продублировать часть табличек и сделать их memory. Но тут надо ещё решить проблему с поддержанием их в актуальном состоянии в случае рестарта сервера
Так что, возможно, проблему удастся решить таким образом..

Неактивен

 

#6 21.06.2008 10:31:46

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Блокировки таблиц

Нашел багу, которая очень похожа на Ваш случай:
http://bugs.mysql.com/bug.php?id=21074

Я так понимаю, дело в неэффективном алгоритме освобождения и выделения блоков query_cache. Судя по описанию в Вашей версии проблема должна быть исправлена, но патч решает проблему симптоматически (делает, чтобы запросы не ждали больше какого-то периода), но не устраняет причину. Попробуйте отключить query_cache (key_buffer также может быть причиной). Можете ли воспользоваться таким же подходом, как сабмиттер баги - убить mysql и посмотреть stack trace?

Неактивен

 

#7 23.06.2008 13:07:48

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Блокировки таблиц

RAID1 никогда не дает более высокую скорость чтения, т.к. читает с двух дисков
всегда, чтобы гарантировать целостность данных. Увеличение скорости дает RAID0
(или комбинации типа RAID10).

iostatом не смотрели?

Неактивен

 

#8 23.06.2008 17:17:16

slakey
Участник
Зарегистрирован: 20.06.2008
Сообщений: 16

Re: Блокировки таблиц

rgbeast написал:

Нашел багу, которая очень похожа на Ваш случай:
http://bugs.mysql.com/bug.php?id=21074

Я так понимаю, дело в неэффективном алгоритме освобождения и выделения блоков query_cache. Судя по описанию в Вашей версии проблема должна быть исправлена, но патч решает проблему симптоматически (делает, чтобы запросы не ждали больше какого-то периода), но не устраняет причину. Попробуйте отключить query_cache (key_buffer также может быть причиной). Можете ли воспользоваться таким же подходом, как сабмиттер баги - убить mysql и посмотреть stack trace?

У меня эти таблицы повисают намертво... сама база не восстанавливается. И кроме того, у меня подвисает не всё - запросы типа select 1 у меня выполняются. query_cache пока жалко - я вижу, он используется у меня. А key_buffer ещё жальче - у меня таблицы большие, индексы тоже сильно растут к концу дня. Я так понимаю, что если я уменьшу key_buffer меньше чем все мои индексы, то начнёт подтормаживать, верно? stack trace могу посмотреть, но как-нибудь с утра mysql положу.

paulus написал:

iostatом не смотрели?

Смотрел.. видел вот такую картину (это iostat -x 1)

                        extended device statistics
device     r/s   w/s    kr/s    kw/s wait svc_t  b
amrd0      0.0   0.0     0.0     0.0    0   0.0   0
amrd0      0.0   0.0     0.0     0.0    0   0.0   0
amrd0      0.0   0.0     0.0     0.0    0   0.0   0
amrd0      1.0   1.0    16.0    16.0    0   7.0   1
amrd0      0.0  12.0     0.0   319.7    0   3.2   1
amrd0      0.0  41.0     0.0   863.1    0   8.6   2
amrd0      0.0  33.0     0.0   703.3    0   7.0   1
amrd0      0.0  62.9     0.0  1342.6    0  10.5   3
amrd0      1.0  81.9    16.0  1456.6  930   9.8   4
amrd0      0.0 555.4     0.0 10469.4  913 546.1 102
amrd0      0.0 630.4     0.0 11668.2  282 1344.8  98
amrd0      0.0 281.7     0.0  5138.8    0 1635.2  53
amrd0      0.0   0.0     0.0     0.0    0   0.0   0

Вот этот вот всплеск записи - каждые 30 секунд. Всё, что у меня могло повторяться каждые 30 секунд - отключил - всё равно ровно каждые 30 секунд всплеск.. даже не знаю на что думать.

Отредактированно slakey (23.06.2008 17:17:37)

Неактивен

 

#9 23.06.2008 17:32:27

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Блокировки таблиц

Т.е. он реально упирается в диск при этом, судя по размеру очереди ожидания.

Давайте еще раз пройдемся по предыстории - таблички MyISAM или InnoDB? Если
InnoDB - не маленький ли размер лога в памяти? Если MyISAM - достаточно ли памяти
для сортировки/ключей?

Такое ощущение, что он копит какое-то количество данных в памяти, после чего
выплескивает все скопом на диск. Если памяти дать больше - часть данных можно
будет вообще не скидывать на диск (временные данные), а нужные данные можно
будет раскидывать по времени более равномерно.

Опять-таки, это предположение. Я бы смотрел в лог запросов, скорее всего, что-то
в нем подскажет, что не так.

Неактивен

 

#10 23.06.2008 17:39:09

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Блокировки таблиц

Ага, перечитал и увидел слово MyISAM smile Про логи мысль, значит, была неверной.
Размеры key_buffer_size / sort_buffer_size нормальные? Query cache не пишется на
диск, поэтому тормозит не он (если, конечно, это не бага). В MyISAM торможение
по диску может быть в следующих случаях:

1. SELECT без ключей. По большой таблице или большим количеством запросов. Убивает
любой сервер. Находится логом запросов.

2. INSERT с ключами. При маленьком размере key_buffer и больших таблицах может
очень тормозить (в том числе и селекты, т.к. INSERT имеет более высокий приоритет
и селекты будут ждать).

По поводу второго варианта - насколько большой размер таблицы (а лучше даже - какой
размер индекса), есть ли огромные таблицы?

Неактивен

 

#11 23.06.2008 19:28:19

slakey
Участник
Зарегистрирован: 20.06.2008
Сообщений: 16

Re: Блокировки таблиц

insert делается в таблицу, в которой к концу суток более 2 миллионов записей (в полночь создаётся новая таблица), сама таблица весит 60 метров, а ещё 120 метров - индексы для неё (лишних индексов, с моей точки зрения, там нет). Это самая большая таблица, есть и другие, но они используются не так интенсивно. К этой же таблице у меня до 100 запросов в секунду на наличие уже подобных полей, но, насколько я понимаю, непосредственно до таблицы дело даже не доходит, потому что всё что нужно уже есть в одном из индексов.
Вставляю данные я только insert delayed, поэтому тут, я думаю не должно быть проблемы..
key_buffer_size у меня был 384M. Сейчас увеличил до 768 - абсолютно та же картина.
sort_buffer_size я не менял - он у меня 512К, но я и не сортирую никакие данные.
Запросы у меня однотипные, поэтому если бы какой-то тормозил, то я бы уже знал.
После того, как сделал поменьше время "медленного запроса" - запросы стали попадать туда почаще, но они там вполне стандартные и их очень мало по отношению к общему количеству.

Неактивен

 

#12 23.06.2008 19:55:21

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Блокировки таблиц

2 миллиона строк в сутки - это 23 запроса в секунду в среднем. На вставку. Попробуйте
сделать новую табличку в InnoDB, Вы будете удивлены результатом smile

P.S. Если решитесь - не забудьте, что у InnoDB отдельная память (innodb_buffer_pool_size)
и память логов (innodb_log_buffer_size). И я обычно ставлю innodb_file_per_table +
innodb_flush_log_at_trx_commit=0 - помогает в администрировании smile

Неактивен

 

#13 23.06.2008 20:13:42

slakey
Участник
Зарегистрирован: 20.06.2008
Сообщений: 16

Re: Блокировки таблиц

Хм.. ну можно попробовать.
Честно говоря, я считал, что это основное преимущество MyISAM в быстром чтении. И 23 запроса - это в среднем только.. вот например, в 00:00:01 - сегодня было 160 вставок.. а селектов из этой же таблицы - примерно столько же, даже чуть больше. InnoDB это всё ещё потянет? А если будет в два раза больше? Я могу продублировать часть этой таблицы табличкой в memory - тогда селектов будет в сотни раз меньше - такой вариант, наверное, сильно облегчит жизнь базе?

Неактивен

 

#14 23.06.2008 20:24:20

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Блокировки таблиц

Да, основное преимущество - в быстром чтении. А Вам нужна быстрая запись sad

InnoDB - очень хорошее хранилище, при комбинации чтение/запись оно безусловно
выиграет у MyISAM.

Кстати, "старые" данные не обязательно хранить в том же формате, например, их
можно сконвертировать в MyISAM и потом сжать с помощью myisampack.

Неактивен

 

#15 24.06.2008 00:16:27

slakey
Участник
Зарегистрирован: 20.06.2008
Сообщений: 16

Re: Блокировки таблиц

Ну вот.. новую таблицу создал уже в InnoDB - действительно, с диском ситуация стала намного лучше.

                        extended device statistics
device     r/s   w/s    kr/s    kw/s wait svc_t  b
amrd0      0.0 207.8     0.0  3324.6    0   1.4  11
amrd0      0.0 197.8     0.0  3164.8    1   0.6  12
amrd0      0.0 187.8     0.0  3005.0    0   0.6  12
amrd0      0.0 193.8     0.0  3100.9    0   0.6  12
amrd0      0.0 205.8     0.0  3292.6    0   0.6  13
amrd0      0.0 207.8     0.0  3324.6    1   0.6  13
amrd0      0.0 197.8     0.0  3164.8    0   0.6  12
amrd0      0.0 227.8     0.0  3644.3    1   0.6  14
amrd0      0.0 197.8     0.0  3164.7    0   0.6  12

Посмотрю как будет с производительностью, когда таблица вырастет. Если у меня innodb_buffer_pool_size заведомо больше всех моих таблиц вместе взятых, то, наверное, при чтении у меня и никаких затыков со скоростью не будет? Вообще, есть ли смысл те таблички на memory, которые я создал (и обновляю их раз в минуту актуальными данными) перевести на InnoDB и рассчитывать, что потерь в скорости не будет, потому что таблицы будут читаться из памяти?

Неактивен

 

#16 24.06.2008 13:11:30

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Блокировки таблиц

InnoDB, в отличие от MyISAM, хранит не только ключи в памяти, но и сами данные.
Так что, возможно, Вам даже не нужно будет вспоминать о MEMORY-табличках.

InnoDB начнет тормозить на больших таблицах, по тем же причинам, что и MyISAM -
сортировка индексов, но Вы выиграете кучу ресурсов на том, что не блокируете
таблицы, поэтому SELECTы не должны сильно тормозить.

P.S. Но 120 мегабайт - это не большая таблица, разумеется.

Неактивен

 

#17 24.06.2008 13:39:39

slakey
Участник
Зарегистрирован: 20.06.2008
Сообщений: 16

Re: Блокировки таблиц

Спасибо. Я очень долго не хотел переходить на InnoDB, опасаясь сильных потерь в производительности.
Надеюсь, изначальная проблема тоже будет решена таким образом.

Неактивен

 

#18 24.06.2008 17:49:16

slakey
Участник
Зарегистрирован: 20.06.2008
Сообщений: 16

Re: Блокировки таблиц

Извините, я позволю себе задать вопросы уже по InnoDB, но в этой же теме.
Выяснилось, что у меня ещё и count периодически используется и висит этот count по 3 секунды. Сейчас повесил триггер, который увеличивает счётчик количества записей , но мне эта идея не очень нравится.
Как "дешевле" получится - чтобы висел триггер и писал счётчик в левую таблицу или добавить одно поле auto_increment и из него брать max(id)?
Мне кажется, что триггер каждый раз это напряжно.

Неактивен

 

#19 24.06.2008 18:33:36

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Блокировки таблиц

Если у Вас точно строчки не удаляются, то это безусловно решит Вашу проблему.
max(id) будет выбираться со скоростью сопоставимой со скоростью выборки из
отдельной таблицы (разумеется, при наличии ключа на id, но это ведь будет так
в случае auto_increment), так что триггеры будут создавать действительно _в данном
случае_ лишнюю нагрузку. Если же строки будут удаляться, то триггеры сделают
действительно солидное ускорение производительности.

Если решите создавать поле - подумайте, может быть, стоит сделать из него primary key -
короткие основные ключи на таблице очень нравятся InnoDB.

Неактивен

 

#20 24.06.2008 18:37:56

slakey
Участник
Зарегистрирован: 20.06.2008
Сообщений: 16

Re: Блокировки таблиц

Да, строчки не удаляются.
Просто везде почему-то дают совет именно про триггеры, я подумал, может быть есть ещё какой-то подвох.
Спасибо.

Неактивен

 

#21 24.06.2008 19:01:46

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Блокировки таблиц

Подвох только в том, что если строчки удаляются, то количество не соответствует max(id).

Кстати, если Вам нужно не точное, а приблизительное количество строк, то можете использовать
SHOW TABLE STATUS LIKE 'tablename' - эта операция выполняется очень быстро и возвращает
приблизительное количество строк.

Неактивен

 

#22 24.06.2008 19:05:04

slakey
Участник
Зарегистрирован: 20.06.2008
Сообщений: 16

Re: Блокировки таблиц

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

Неактивен

 

#23 01.07.2008 02:57:32

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Блокировки таблиц

paulus написал:

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

скорость чтения все таки увеличивается, так как чтение идет в несколько потоков со всех накопителей (при отсутствии записи опять же, хотя это уже тонкости реализации контроллеров).

Неактивен

 

#24 01.07.2008 12:10:33

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Блокировки таблиц

Хмм.. открыл википедию и с интересом для себя обнаружил фразу, что бывают контроллеры,
которые дают прирост скорости чтения (для меня все равно остается загадкой - как, если
нужно обеспечить целостность данных). Лично я с такими контроллерами не сталкивался
ни разу, но спасибо, что рассказали smile

Кстати, если знаете, как обеспечивается целостность при увеличении скорости, - поделитесь?

Неактивен

 

#25 01.07.2008 13:00:30

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Блокировки таблиц

Ну я вообще то не спец, но думаю, что RAID 1 может теоретически выдавать при чтении удвоенную скорость по сравнению с одним таким же винтом.

Логика подсказывает следующую элементарную схему. Допустим на контроллер приходит запрос на блок данных D. Контроллер разбивает этот блок на два A и B, которые читает с двух винтов одновременно. "Сверки" при этом не происходит. Если один из блоков A или B не считался, соответствующий носитель метится как битый и выбывает из процесса с вытекающими. Контроль происходит _при записи_, т.е. пока запись одного и того же блока данных не произведена на каждый диск зеркала оно не считается рабочим.

ну и плюс аппаратный кэш еще чуток добавляет

Отредактированно Shopen (01.07.2008 13:02:38)

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson