innodb_flush_log_at_trx_commit=0 - насколько опасно?

MySQL, PostgreSQL, InterBaseSQL etc

Модераторы: Art.i, vasya

innodb_flush_log_at_trx_commit=0 - насколько опасно?

Сообщение g0xff » Пн дек 28, 2015 4:21 pm

Только при innodb_flush_log_at_trx_commit=0 запросы обрабатываются в нармальном времени. 0.50мс в среднем скрипт обрабатывает, при 2 больше секунды.
Насколько это опасно? и как это работает на самом деле, в инете толком овета нет.

1) Как часто идет запись измененных данных из кэша на диск и идет ли она вообще?
2) Если не идет, как можно вручную записывать к примеру через крон раз в минуту? может через flush table, пока не могу понять точно для чего этот flush нужен.
3) Что лучше для производительности innodb_flush_method=O_DSYNC или O_DIRECT
4) Какие параметры стоит еще повысить, чтобы работало быстрее или [Innodb_row_lock_time_avg:128] это нормальный показатель и можно дальше не заморачиваться?

Надо сделать, чтобы максимально быстро работало и безопасно при сбоях(можно пожертвовать данными минутной давности).
g0xff
 
Сообщений: 73
Зарегистрирован: Вт май 25, 2010 1:27 am

Re: innodb_flush_log_at_trx_commit=0 - насколько опасно?

Сообщение swg » Пн дек 28, 2015 4:52 pm

У меня на мастерах 1 - синхронный сброс, минимальная производительность, наибольшая надежность; на нескольких подчиненных 0 (но не уверен, что это имеет особый эффект для слэйва). Более безопасно 1, менее безопасно - 2 потеря данных при крэше\а в случае VDS: внезапной перезагрузке корневого сервера высока. По умолчанию 1. Как бы гуглится:
http://ruhighload.com/post/%D0%9A%D0%B0 ... trx_commit

2) Для синхронизации. Маловероятно что требуется для работы пользовательских скриптов.
3) Для производительности, наверное, O_DIRECT, т.е. работа напрямую, без кэширований со стороны ОС. Не использую, не вникал, а что там по умолчанию?
4) Не заморачивался. Сервера разные, структуры баз разные, использование разное. Зашел посмотреть. MySQL сервер работает 11 дней, 15 часов... Запросов в секунду 704. Innodb_row_lock_time_avg = 47 Innodb_row_lock_time_max = 131
swg
флудит форум
 
Сообщений: 2386
Зарегистрирован: Сб окт 07, 2006 9:09 am
Откуда: NNov

Re: innodb_flush_log_at_trx_commit=0 - насколько опасно?

Сообщение g0xff » Пн дек 28, 2015 5:21 pm

у меня все на одном сервере, да и то только одну базу использую под innodb, хотя в нее чаще всего обращаются и такой производительноти нет, хотя сервер хороший с 16гигов оперативки и Core i7 3.8ГГц .. innodb_flush_log_at_trx_commit при 2 у меня жутко запросы долго выполняются, при 1 вообще нереально ждать, только при 0 быстро работает и то показатели как видишь в разы хуже чем у тебя. При запросов в сек 251 выдает 161 Среднее время ожидания блокировки строк.

Раньше всегда работал в myisam но уже при таком колво запросов чувствуются задержки, пришлось перейти на innodb. Вот теперь хз как настроить правельно и что меня ждет.
g0xff
 
Сообщений: 73
Зарегистрирован: Вт май 25, 2010 1:27 am

Re: innodb_flush_log_at_trx_commit=0 - насколько опасно?

Сообщение g0xff » Пн дек 28, 2015 5:35 pm

Innodb_buffer_pool_pages_dirtyДокументация 437 Текущее количество "грязных" страниц. - что это за парамент? что это грязные страницы?
g0xff
 
Сообщений: 73
Зарегистрирован: Вт май 25, 2010 1:27 am

Re: innodb_flush_log_at_trx_commit=0 - насколько опасно?

Сообщение swg » Пн дек 28, 2015 5:42 pm

Не знаю, что ждет... Переполнение файлов журнала, например...
А разумные методы оптимизации скриптов, типа логирования медленных запросов и их explain ни к чему ни привели?
p.s. Это тоже с Core i7 16Gb оперативки; только HDD. База 7Гб, "динамическая часть" около 2, в смысле, что 5Гб - справочники, к которым только select.

Innodb_buffer_pool_pages_dirty - это нормально, значит изменения ещё не записаны на диск (но записаны в журнал)
http://dev.mysql.com/doc/refman/5.7/en/ ... dirty_page
Последний раз редактировалось swg Пн дек 28, 2015 5:48 pm, всего редактировалось 1 раз.
swg
флудит форум
 
Сообщений: 2386
Зарегистрирован: Сб окт 07, 2006 9:09 am
Откуда: NNov

Re: innodb_flush_log_at_trx_commit=0 - насколько опасно?

Сообщение g0xff » Пн дек 28, 2015 5:47 pm

скрипты как по мне оптимизированные, индексы проставлены, join нет в скриптах, там простые зпросы типа select id,name,lvl from user where sid=? and ip=? and st>?
При myisam выполнялось все быстро, просто при 250 запросов в сек встановились в очередь, но задержки больше 500мс не было. т.е. по сути сейчас такаеже скорость как и с innodb.

Я перед тем как сюда вопрос задать пролазил инет, англиского не знаю и этих кратких ответов на офф сайте не все понимаю. Мне эти ссылке мало помогают.

Скинь плз свой конфиг.
Последний раз редактировалось g0xff Пн дек 28, 2015 5:51 pm, всего редактировалось 2 раз(а).
g0xff
 
Сообщений: 73
Зарегистрирован: Вт май 25, 2010 1:27 am

Re: innodb_flush_log_at_trx_commit=0 - насколько опасно?

Сообщение g0xff » Пн дек 28, 2015 5:49 pm

#skip-innodb
innodb_additional_mem_pool_size = 32M
innodb_buffer_pool_size = 8G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_data_home_dir = /var/lib/mysql
innodb_write_io_threads = 8
innodb_read_io_threads = 8
#innodb_force_recovery=1
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 0
#innodb_fast_shutdown
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_log_group_home_dir = /var/lib/mysql
innodb_max_dirty_pages_pct = 90
innodb_flush_method=O_DSYNC
innodb_lock_wait_timeout = 30
g0xff
 
Сообщений: 73
Зарегистрирован: Вт май 25, 2010 1:27 am

Re: innodb_flush_log_at_trx_commit=0 - насколько опасно?

Сообщение swg » Пн дек 28, 2015 5:51 pm

А кто сказал, что innoDB даст большую скорость на selectах в общем случае?
Join - это не что-то запрещенное, что роняет производительность. в общем случае, ваше st> может быть слабым местом. Но это только гадание. Надо проверять всё explain. Смотреть, где сортировки, где провоцируются последовательные чтения, а не по индексам и т.д.
swg
флудит форум
 
Сообщений: 2386
Зарегистрирован: Сб окт 07, 2006 9:09 am
Откуда: NNov

Re: innodb_flush_log_at_trx_commit=0 - насколько опасно?

Сообщение g0xff » Пн дек 28, 2015 5:58 pm

select 796 k 428.448 k 55.99%
set option 298 k 160.646 k 20.99%
update 258 k 139.023 k 18.17%
-------
И когда идет update, пять селектов ждут и получается тармаза в часпик.

Стоит индекс на st создавать, когда другие две переменые под индексами и они уже выводят одну запись, мне только надо проверить дату больше или меньше она или проще тога в пхп проверить? т.к. st часто изменяется и пересоздавать на это индекс я думаю не то.
g0xff
 
Сообщений: 73
Зарегистрирован: Вт май 25, 2010 1:27 am

Re: innodb_flush_log_at_trx_commit=0 - насколько опасно?

Сообщение swg » Пн дек 28, 2015 6:06 pm

Код: выделить все
innodb_buffer_pool_size = 4G
innodb_log_file_size = 1G
innodb_log_buffer_size = 256M
innodb_data_home_dir = [не /var/lib/mysql, другой диск]
innodb_data_file_path = ibdata1:1000M:autoextend
Всё остальное по умолчанию, т.е. в my.cnf ничего не дописывалось.
swg
флудит форум
 
Сообщений: 2386
Зарегистрирован: Сб окт 07, 2006 9:09 am
Откуда: NNov

Re: innodb_flush_log_at_trx_commit=0 - насколько опасно?

Сообщение swg » Пн дек 28, 2015 6:11 pm

Смотря какой update. По primary key - будет лочится одна строка и selectы ждать не будут. Если типа update ... st>123, то это жестко, если строк >10млн, например.
Если это что-то типа биллинга, то можно делать select, тех, что должны быть update; записывать свой журнал транзакций и медленно обрабатывать его, используя только PRIMARY KEY, тогда тормозов не будет. Если запрос критичен, то маркировать пользователя ждать и ставить в приоритет его. Такая очередь у меня на rabbitMQ.
swg
флудит форум
 
Сообщений: 2386
Зарегистрирован: Сб окт 07, 2006 9:09 am
Откуда: NNov

Re: innodb_flush_log_at_trx_commit=0 - насколько опасно?

Сообщение g0xff » Пн дек 28, 2015 6:20 pm

swg писал(а):По primary key - будет лочится одна строка и selectы ждать не будут.

Это в myisam? и обязательно делать по primary key , чтобы селект не ждал или можно и по другому индексу?
g0xff
 
Сообщений: 73
Зарегистрирован: Вт май 25, 2010 1:27 am

Re: innodb_flush_log_at_trx_commit=0 - насколько опасно?

Сообщение swg » Пн дек 28, 2015 6:26 pm

Это про InnoDB. 1 абзац прочтите (ну и дальше), я это имел в виду.
http://habrahabr.ru/post/238513/
По primary не обязательно, просто выполняется быстрее; когда вы сначала их собрали (идентификаторы), добавили в очередь, а потом обрабатываете. Но это не выход, так можно делать только в специфичных задачах. Я это просто как пример оптимизации рассказал.
swg
флудит форум
 
Сообщений: 2386
Зарегистрирован: Сб окт 07, 2006 9:09 am
Откуда: NNov

Re: innodb_flush_log_at_trx_commit=0 - насколько опасно?

Сообщение g0xff » Пн дек 28, 2015 6:36 pm

Можете пояснить мне про комаду flush, что она делает? записывает даные на диск и стоит ее выпонялнять раз в минуту при innodb_flush_log_at_trx_commit=0 или он и так за такое время сам по себе записывает на диск?
Потомучто как я понял чем больше это ставишь innodb_log_file_size = 1G innodb_log_buffer_size = 256M, тем реже запись на диск. А для меня раз в минуту записывать на диск былобы достаточно.
g0xff
 
Сообщений: 73
Зарегистрирован: Вт май 25, 2010 1:27 am

Re: innodb_flush_log_at_trx_commit=0 - насколько опасно?

Сообщение swg » Пн дек 28, 2015 6:47 pm

Не, всё что log_file - это журнал. Сначала операция записывается в журнал транзакций (и кэш памяти), потом в данные.
Запись на диск всё равно будет. Просто у меня с такими значениями dirty page куда больше вашего.
После крэша у меня дольше восстанавливаться будет. Т.е. считает данные и начнет применять журнал.
У меня журнал на одном диске, данные на другом, как минимум из-за этого есть прирост скорости.
---
Простым языком. У вас таблица users (id, login, password). Вы делаете insert into users (...) values (...). Он запишет это в журнал (на диск), запомнит в памяти, но пока не тронет данные (если есть другие запросы). Появляется dirty page. потом она сбросится, когда нагрузки не будет. Это очень упрощенно.
p.s. Примерно, что я вам и говорил можно сделать самому (на тему пользовательской оптимизации); залогировать, что надо сделать, а делать потом.
swg
флудит форум
 
Сообщений: 2386
Зарегистрирован: Сб окт 07, 2006 9:09 am
Откуда: NNov

След.

Вернуться в Базы данных

Кто сейчас на форуме

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 3