SQLinfo.ru - Все о MySQL

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

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

Вы не зашли.

#1 12.08.2010 22:56:10

Григорий Повелец
Участник
Зарегистрирован: 12.08.2010
Сообщений: 7

Большая длительность System lock, Locked

Здравствуйте,

имеется проблема, а именно случайная длительность различных запросов раз в две-три минуты, если запрос происходит в наиболее нагруженные таблицы. К концу дня буквально каждый запрос в итоге оказывается в slow query log.

Воспользовавшись утилитой Jet Profiler, которая визуализирует данные встроенного профайлера, обнаружил, что поток длительное время пребывает в состояниях System lock, Locked. нагрузка на дисковую систему при этом крайне незначительная 2-3Мб/с, HDD использовались различные, в данный момент SAS 15k

SHOW VARIABLES
http://pastebin.com/pDAPbDLW
SHOW GLOBAL STATUS
http://pastebin.com/d4JAgDv0
my.cnf
http://pastebin.com/wZS1VYhr
Jet Profiler
http://img203.imageshack.us/img203/4429/states.png

Помогите разобраться, какие необходимы данные - предоставлю.

Неактивен

 

#2 12.08.2010 23:30:16

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

Re: Большая длительность System lock, Locked

Хм, сервер у Вас достаточно загружен. По хорошему — надо смотреть SHOW
PROCESSLIST во время того, как процессы висят Locked.

Из того, что видно из Ваших данных:
1. Вы очень много пишете по сравнению с чтениями: в среднем на один SELECT
приходится два(!) UPDATE и еще сверху половинка INSERT.
2. При всем при этом Вы используете тип таблиц MyISAM, которые не позволя-
ют делать изменения одновременно.
3. Соответственно, рабочее предположение, что приходят несколько потоков,
которые хотят изменить данные, а в результате все параллельные SELECT их
ждут.

Если я угадал, то самое простое — переделать такие нагруженные таблички
в InnoDB. Ну или, скажем, сделать реплику и снимать SELECT с нее. Еще вари-
анты: делать INSERT/UPDATE LOW PRIORITY и/или SELECT HIGH PRIORITY.
Но тут можно плохими приоритетами добиться того, что обновления вообще
будут очень редко происходить из-за выборок. Все-таки, движок предназна-
чался для данных с большим уклоном в сторону чтения.

P.S. Очень красивая у Вас утилитка, которая графики строит.
P.P.S. Кто такой Remi, который собирал MySQL? smile

Неактивен

 

#3 13.08.2010 00:40:03

Григорий Повелец
Участник
Зарегистрирован: 12.08.2010
Сообщений: 7

Re: Большая длительность System lock, Locked

Спасибо за ответ.

Насчет репликации, наверное это самый лучший вариант, но как направить приложение делать SELECT на сервер репликации я пока не догадываюсь.

Попробую вариант с InnoDb для самых загруженных таблиц.

paulus написал:

P.S. Очень красивая у Вас утилитка, которая графики строит.

Утилитка действительно красивая, на сайте производителя есть бесплатная версия.

paulus написал:

P.P.S. Кто такой Remi, который собирал MySQL? smile

Сервер mysql взят из репозитория: http://blog.famillecollet.com

Неактивен

 

#4 13.08.2010 08:43:22

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

Re: Большая длительность System lock, Locked

А почему не с mysql.com? Вроде, MySQL AB неплохо собирает свой сервер wink

Неактивен

 

#5 13.08.2010 20:01:15

Григорий Повелец
Участник
Зарегистрирован: 12.08.2010
Сообщений: 7

Re: Большая длительность System lock, Locked

Взял на всякий пакет c mysql.com, 5.1.49

С InnoDB ситуация хуже, некоторые запросы выполнялись по 30-40 секунд, некоторые моментально, не понял почему, вернул MYISAM. Реплика также не помогла, настроил на этой же машине slave, направил внешние SELECT для сборка статистики туда, с течением времени master сервер реагировал все медленнее и медленее, пока не выключил slave.

SHOW PROCESSLIST показывает ожидаемую картину, выполняется 1 update/select и 20-30 в состоянии Locked/System Lock

Заметил еще что lsof не показывает что открыты файлы таблиц. При этом:

show global status like 'open%tables%';
Open_tables 381
Opened_tables 536

iostat показывает среднюю нагузку на HDD на запись 2-3Мб/с

Уже не знаю что делать, неоправданно поменял машину на "помощнее" и таже петрушка.

Неактивен

 

#6 13.08.2010 20:31:27

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

Re: Большая длительность System lock, Locked

Не-не, я не предлагал Вам менять сервер, просто хотел узнать, почему такой.

Какие настройки InnoDB у Вас стояли?

Неактивен

 

#7 13.08.2010 20:54:36

Григорий Повелец
Участник
Зарегистрирован: 12.08.2010
Сообщений: 7

Re: Большая длительность System lock, Locked

paulus написал:

Не-не, я не предлагал Вам менять сервер, просто хотел узнать, почему такой.

Какие настройки InnoDB у Вас стояли?

Сервер то я сам поменял, на всякий.

Настройки innodb:

innodb_flush_method=O_DIRECT
innodb_additional_mem_pool_size=32M
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=8M
innodb_buffer_pool_size=2G
innodb_log_file_size=32M
innodb_thread_concurrency=16

Всте таблицы с нестатичными данными были переведены на InnoDB.

Неактивен

 

#8 13.08.2010 22:37:38

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

Re: Большая длительность System lock, Locked

Хм, а flush в ноль ставить не пробовали? При бывших табличках myisam вам, наверное, полный acid не нужен? Ну и processlist интересно посмотреть, когда тормозит.

Неактивен

 

#9 13.08.2010 23:01:54

Григорий Повелец
Участник
Зарегистрирован: 12.08.2010
Сообщений: 7

Re: Большая длительность System lock, Locked

Пробовал innodb_flush_log_at_trx_commit 0,1,2, погоды не делало. processlist на MYISAM не сохранил, но там было очевидно - 20-30 запросов, 2-3 селекта, остальные обновления, длительная блокировка.

Вот сейчас установили SSD диски, перенес туда базу, в профайлере суммарное время System lock понизилось значительно, как было - картинка в первом посте, а вот как стало.

http://img839.imageshack.us/f/statesn.png/

На SAS диске почему то System lock занимал длительное время.

Неактивен

 

#10 14.08.2010 00:34:35

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

Re: Большая длительность System lock, Locked

На myisam processlist и не интересен. Интересен на innodb, с ноликом. SSD увеличивает производительность дисковой системы, да. Но проблему не решает.

Неактивен

 

#11 14.08.2010 01:23:35

Григорий Повелец
Участник
Зарегистрирован: 12.08.2010
Сообщений: 7

Re: Большая длительность System lock, Locked

Проблему то понятно, что не решило, постараюсь сэмулировать загрузку на InnoDB, и буду смореть processlist. Обращусь за советом уже с каким-нибудь данными.

Неактивен

 

#12 17.08.2010 08:48:24

Григорий Повелец
Участник
Зарегистрирован: 12.08.2010
Сообщений: 7

Re: Большая длительность System lock, Locked

Два дня свистопляски, перевел еще раз все на InnoDB, эмулятор нагрузки мой показал стабильное снижение пропускной способности, в моем случае это где то до 800 запросов в секунду, запись : чтение где-то 2 : 1, НО при этом в несколько 12 часовых заходов не показало ни одного медленного запроса, с MYISAM получалось где-то 2000 запросов в секунду, но гарантировано были медленные запросы, они мне и все портили, простой всех потоков 2-4 секунды в ожидании снятия блокировки. Все тесты проводил без репликации.

К сожелению при включении бинарного лога, без слейва или с ним, в случае и InnoDB и MYISAM на SAS и SSD дисках, с течением времени скорость выполнения запросов понижается до неприемлимой. Не нашел что-то параметров, можно ли выставить приоритет на слейве или на мастере, чтобы подгружать данные на слейв только когда мастер "не занят",  и по поводу бин-лога, можно ли контролировать как будут сбрасываться данные, может есть настройки, аналогичные настройкам InnoDB: innodb_flush_log_at_trx_commit

Неактивен

 

#13 17.08.2010 13:14:39

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

Re: Большая длительность System lock, Locked

Параметры есть, но по умолчанию они не синкают логи
http://dev.mysql.com/doc/refman/5.1/en/ … y-log.html

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

Неактивен

 

Board footer

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