таблица big_table будет сканироваться по нужному индексу, но индекс будет задействован неполностью, то есть из него будет использоваться только первая колонка — вот он источник зла
Искать в индексе каджую из пар — как раз недорого что доказывает явный запрос с указанием бинд переменных и конечный результат в 5 секунд с использованием курсора по всей таблице, а вот mysql как раз ошибается именно в этом месте о чем и гласит указанный баг.
Странно, честно говоря не знаю при чем тут процедуры, мы переехали без проблем. Пересоздание процедур — да возможно, запуск — все быстро.
Я выложил все скрипты. Они успешно справлялись с задачей оптимизации как на 5.1 так и на 5.5. Попробуйте поэксперементировать самостоятельно, может заодно и разберетесь где собака зарыта.
Так же пому посоветовать set profiling = 1; и настройку performance_schema — там можно получить приличную информацию по блокировкам и дисковым чтениям.
В общем согласен, но у меня есть христоматийный пример, из личной практики, на то стоит овчинка выделки или нет.
Таблица состящая из 4-х записей была в топе по логическим чтениям (более 50% от всей БД) на HP сервере в 32 процессора и 40Gb ОЗУ.
Разработчики на СУБД Oracle (хорошая СУБД не так ли?) не закэшировали в пакеты таблицу в которой день разбивался по часам на час пик и не час пик. После оптимизации (нашел косяк не я, а мой босс, я тогда маленьким ещё был и глупым) скорость тарификации увеличилась на порядок…
Так что кривые руки железом — не исправить.
К сожалению на написание данной статьи я потратил 3 дня, ввиду того что каждый запрос выполняетя далеко не за 10 минут, с учетом того, что я знал о чем писать и как. Так что думаю через недельку напишу как сделать быстрый paging для отображения результатов многокритериального поиска по обеим множеством сущностей со связями один ко многим, при наличии миллинов записей в обеих таблицах, там постараюсь осветить проблемы limit, distinct и order by а так же партиционирования и денормализации.
Именно для того чтобы MySQL не мог читать данные из буфера я и генерировал такой большой объем записей, ибо это просто пример, неудачных решений используемых MySQL для оптимизации запросов. Наиболее характерно данные ошибки проявляются именно при такой настройке БД.
На промышленной БД у вас далеко не одна таблица и вы далеко не один пользователь. Так что все буфера будут делиться между ними в каких-то пропорциях.
Если все будут сканировать таблицу размером 10Gb то никаких ресурсов не хватит.
Где-то на хабре был замечательный пост, где всю БД засунули в оперативную память. Это конечно выход, но далеко не самый удачный.
Этой статьей и последующим циклом (если конечно попрет) я попытаюсь описать как можно заставить MySQL работать с большими базами данных, на совершенно мизерных объемах доступных ресурсов.
И вообще я как разработчик ужасно жадный до ресурсов особенно таких дорогих как ОЗУ, и пытаюсь выкраивать каждый байт.
Понимаю все возмущение автора, однако:
— почему бы не спросить у аудитора как именно можно получить указанную им информацию, ибо он же профессионал, и так как вы сотрудничаете с данной платформой — значит приносите им доход, а значит можете расчитывать на грамотную техподдержку с их стороны
— не ясен ещё один вопрос, если он отказывается отвечать, пробовали узнать какая именно сумма может развязать ему язык, судя по требованиям и по тому, что он боится раскрывать свои данные — явно что-то не так, ибо если требования адекватны — то огласки боятся глупо, наборот хорошая реклама грамотной аудиторской конторе не повредит
— у нас тоже был опыт общения с неграмотными аудиторами, они говорили много нужных и полезных вещей, давали нам так сказать консультации, а в итоге мы из-за них чуть всю архитектуру не угробили, хорошо сами головой подумали и не стали делать то, что предлагали они
— всем банкам с большой колокльни на PCI DSS, в штатах к примеру ISO стандарты банки проходят, так что вполне может оказаться, что от вас требовались данные по какому нибудь ISO 9001 или 27000, что-то типа того, а там такая мутя написана, которую трактовать можно как угодно, так что не все так просто, вы можете и ошибаться
Ибо пару раз из-за отсутствия этого параметра мы получали совершенно непонятные ошибки и результаты. Так как MySQL при работе с беззнаковыми данными результат делает тоже беззнаковым и происходит переполнение, если сделать вычитание.
Все таблицы в MySQL на движке InnoDB на сколько я понимаю являются кластерными в терминологии Oracle, т.е. неким их подобием, именно по этой причине на каждой таблице будет PK даже если вы его не заказывали, по которому будет сформирован кластер. По сему добавление колонки вызывает существенное изменение самой структуры таблицы а не только словарей.
Способ описанный выше в реально сложной ситуации помогет не сильно, особенно если одаренные разработчики навешали констрейнтов на эту горячую таблицу и сделали кучу индексов, но автор молодец, мы такую таблицу мигрировали ручками… по частям… так ничего и не предумав для автоматизации процесса :) в итоге плюнули остановили сервис и просто добавили поле :) ну постояли часок другой… ну ничего страшного… бывает
Помнится во избежании данной ситуации — мы просто создавали новую таблицу — пихали в нее новой поле и ссылку на большую. Решение кривое, но гарантированно работает без простоя.
Хотелось бы внести пару ложек дегтя в бочку меда. Начну из далека.
Как только в MySQL появились события, я очень обрадовался, так как привык работать с job в оракл и этой функциональности очень не хватало, однако. На последней конференции по MySQL (которую уже устроил Oracle) мы пообщались с разработчиками на тему, а как вам events в MySQL. На что получили крайне забавный ответ, ал-я «Вася иди сюда, расскажи как много багов у тебя в этом долбанном шедулере». Т.е. по мнению разоработчиков качество кода, который им достался в наследство, оставляет желать лучшего.
Events мы все таки заюзали. Ибо фича полезное а я верю в лучшее.
Первые проблемы начались когда MySQL 5.5.11 начал слегда подвисать из-за того, что не мог создавать новый бинарный лог, если имелись незавершенные транзакции. Механизм у нас был заюзан очень активно. Особенностью MySQL event scheduler состоит в том, что если событие не завершилось, то оно все равно стартует ещё раз, по этому страховку, защиту от дурака, надо делать самим. Изначально мы её не сделали, и при подвисании события запускались в геометрической прогрессии, убивая БД полностью.
Эту проблему мы решили, ну сами дураки — сами и исправились. Сделали что-то типа:
-----------------------------------------------------------------------------
drop event if exists ev_refresh_decline_structures;
delimiter $$
create event ev_refresh_decline_structures
on schedule every 10 second
comment 'Refresh decline data'
do
main_sql:
begin
declare v_eexl_id int(10);
call event_start(v_eexl_id, 'paynet.ev_refresh_decline_structures');
if v_eexl_id is null then leave main_sql; end if;
call refresh_decline_structures(500);
call event_finish(v_eexl_id, 'paynet.ev_refresh_decline_structures');
end
$$
delimiter ;
-----------------------------------------------------------------------------
drop procedure if exists event_start;
delimiter $$
create procedure event_start(out o_eexl_id int(10), i_event_name varchar(64))
main_sql:
begin
declare v_lock_is_set int(1);
select get_lock(i_event_name, 0)
into v_lock_is_set;
if coalesce(v_lock_is_set, 0) = 0 then
leave main_sql;
end if;
insert into event_execution_logs(event_name, start_date)
values (i_event_name, now());
set o_eexl_id = last_insert_id();
commit;
end
$$
delimiter ;
-----------------------------------------------------------------------------
drop procedure if exists event_finish;
delimiter $$
create procedure event_finish(i_eexl_id int(10), i_event_name varchar(64))
main_sql:
begin
declare v_lock_is_set int(1);
update event_execution_logs
set end_date = now()
where eexl_id = i_eexl_id;
commit;
select release_lock(i_event_name)
into v_lock_is_set;
end
$$
delimiter ;
-----------------------------------------------------------------------------
Таким образом мы запретили одновременный запуск событий, а так же стали мониторить время выполнения оных и частоту запуска.
Через месяц мы били сильно удивлены. Мы заметили, что одно событие, которые должно запускаться раз в день в 2 часа ночи, запускается через раз, через 2!!! Особенностью шедулера так же является то, что если время события прошло то оно не будет запущено ещё раз. вроде все логично, но на практике, шедулер просто ПРОПУСКАЕТ события, иногда совершенно по непонытным причинам. В качестве workaround мы переписали код процедур выполняемых событий. таким образом чтобы запускать их можно было чаще а само время реальной отработки они контролировали сами.
В общем — будьте внимательны не натыкайтесь на наши грабли.
З.Ы. ввиду всех этих непонятностей, мы приняли решение написать свой шедулер для MySQL который будет лишен всех этих неостатков на Javaб чего и вам желаем
И как заключительный штрих — им бы еще свою корпоративную армию (корп. вооруженные силы), какую законодательно было разрешено создать Газпрому и Транснефти
Я выложил все скрипты. Они успешно справлялись с задачей оптимизации как на 5.1 так и на 5.5. Попробуйте поэксперементировать самостоятельно, может заодно и разберетесь где собака зарыта.
Так же пому посоветовать set profiling = 1; и настройку performance_schema — там можно получить приличную информацию по блокировкам и дисковым чтениям.
Таблица состящая из 4-х записей была в топе по логическим чтениям (более 50% от всей БД) на HP сервере в 32 процессора и 40Gb ОЗУ.
Разработчики на СУБД Oracle (хорошая СУБД не так ли?) не закэшировали в пакеты таблицу в которой день разбивался по часам на час пик и не час пик. После оптимизации (нашел косяк не я, а мой босс, я тогда маленьким ещё был и глупым) скорость тарификации увеличилась на порядок…
Так что кривые руки железом — не исправить.
На промышленной БД у вас далеко не одна таблица и вы далеко не один пользователь. Так что все буфера будут делиться между ними в каких-то пропорциях.
Если все будут сканировать таблицу размером 10Gb то никаких ресурсов не хватит.
Где-то на хабре был замечательный пост, где всю БД засунули в оперативную память. Это конечно выход, но далеко не самый удачный.
Этой статьей и последующим циклом (если конечно попрет) я попытаюсь описать как можно заставить MySQL работать с большими базами данных, на совершенно мизерных объемах доступных ресурсов.
И вообще я как разработчик ужасно жадный до ресурсов особенно таких дорогих как ОЗУ, и пытаюсь выкраивать каждый байт.
— почему бы не спросить у аудитора как именно можно получить указанную им информацию, ибо он же профессионал, и так как вы сотрудничаете с данной платформой — значит приносите им доход, а значит можете расчитывать на грамотную техподдержку с их стороны
— не ясен ещё один вопрос, если он отказывается отвечать, пробовали узнать какая именно сумма может развязать ему язык, судя по требованиям и по тому, что он боится раскрывать свои данные — явно что-то не так, ибо если требования адекватны — то огласки боятся глупо, наборот хорошая реклама грамотной аудиторской конторе не повредит
— у нас тоже был опыт общения с неграмотными аудиторами, они говорили много нужных и полезных вещей, давали нам так сказать консультации, а в итоге мы из-за них чуть всю архитектуру не угробили, хорошо сами головой подумали и не стали делать то, что предлагали они
— всем банкам с большой колокльни на PCI DSS, в штатах к примеру ISO стандарты банки проходят, так что вполне может оказаться, что от вас требовались данные по какому нибудь ISO 9001 или 27000, что-то типа того, а там такая мутя написана, которую трактовать можно как угодно, так что не все так просто, вы можете и ошибаться
mysql> SET sql_mode = '';
mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
| 18446744073709551615 |
+-------------------------+
mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
| -1 |
+-------------------------+
http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_no_unsigned_subtraction
Ибо пару раз из-за отсутствия этого параметра мы получали совершенно непонятные ошибки и результаты. Так как MySQL при работе с беззнаковыми данными результат делает тоже беззнаковым и происходит переполнение, если сделать вычитание.
Способ описанный выше в реально сложной ситуации помогет не сильно, особенно если одаренные разработчики навешали констрейнтов на эту горячую таблицу и сделали кучу индексов, но автор молодец, мы такую таблицу мигрировали ручками… по частям… так ничего и не предумав для автоматизации процесса :) в итоге плюнули остановили сервис и просто добавили поле :) ну постояли часок другой… ну ничего страшного… бывает
Помнится во избежании данной ситуации — мы просто создавали новую таблицу — пихали в нее новой поле и ссылку на большую. Решение кривое, но гарантированно работает без простоя.
Как только в MySQL появились события, я очень обрадовался, так как привык работать с job в оракл и этой функциональности очень не хватало, однако. На последней конференции по MySQL (которую уже устроил Oracle) мы пообщались с разработчиками на тему, а как вам events в MySQL. На что получили крайне забавный ответ, ал-я «Вася иди сюда, расскажи как много багов у тебя в этом долбанном шедулере». Т.е. по мнению разоработчиков качество кода, который им достался в наследство, оставляет желать лучшего.
Events мы все таки заюзали. Ибо фича полезное а я верю в лучшее.
Первые проблемы начались когда MySQL 5.5.11 начал слегда подвисать из-за того, что не мог создавать новый бинарный лог, если имелись незавершенные транзакции. Механизм у нас был заюзан очень активно. Особенностью MySQL event scheduler состоит в том, что если событие не завершилось, то оно все равно стартует ещё раз, по этому страховку, защиту от дурака, надо делать самим. Изначально мы её не сделали, и при подвисании события запускались в геометрической прогрессии, убивая БД полностью.
Эту проблему мы решили, ну сами дураки — сами и исправились. Сделали что-то типа:
-----------------------------------------------------------------------------
drop event if exists ev_refresh_decline_structures;
delimiter $$
create event ev_refresh_decline_structures
on schedule every 10 second
comment 'Refresh decline data'
do
main_sql:
begin
declare v_eexl_id int(10);
call event_start(v_eexl_id, 'paynet.ev_refresh_decline_structures');
if v_eexl_id is null then leave main_sql; end if;
call refresh_decline_structures(500);
call event_finish(v_eexl_id, 'paynet.ev_refresh_decline_structures');
end
$$
delimiter ;
-----------------------------------------------------------------------------
drop procedure if exists event_start;
delimiter $$
create procedure event_start(out o_eexl_id int(10), i_event_name varchar(64))
main_sql:
begin
declare v_lock_is_set int(1);
select get_lock(i_event_name, 0)
into v_lock_is_set;
if coalesce(v_lock_is_set, 0) = 0 then
leave main_sql;
end if;
insert into event_execution_logs(event_name, start_date)
values (i_event_name, now());
set o_eexl_id = last_insert_id();
commit;
end
$$
delimiter ;
-----------------------------------------------------------------------------
drop procedure if exists event_finish;
delimiter $$
create procedure event_finish(i_eexl_id int(10), i_event_name varchar(64))
main_sql:
begin
declare v_lock_is_set int(1);
update event_execution_logs
set end_date = now()
where eexl_id = i_eexl_id;
commit;
select release_lock(i_event_name)
into v_lock_is_set;
end
$$
delimiter ;
-----------------------------------------------------------------------------
Таким образом мы запретили одновременный запуск событий, а так же стали мониторить время выполнения оных и частоту запуска.
Через месяц мы били сильно удивлены. Мы заметили, что одно событие, которые должно запускаться раз в день в 2 часа ночи, запускается через раз, через 2!!! Особенностью шедулера так же является то, что если время события прошло то оно не будет запущено ещё раз. вроде все логично, но на практике, шедулер просто ПРОПУСКАЕТ события, иногда совершенно по непонытным причинам. В качестве workaround мы переписали код процедур выполняемых событий. таким образом чтобы запускать их можно было чаще а само время реальной отработки они контролировали сами.
В общем — будьте внимательны не натыкайтесь на наши грабли.
З.Ы. ввиду всех этих непонятностей, мы приняли решение написать свой шедулер для MySQL который будет лишен всех этих неостатков на Javaб чего и вам желаем