Все врут или почему в MySQL лучше не использовать партиции

Начиная с версии 5.1 в MySQL появилась такая полезная фича как партиции. Конечно же большинство разработчиков БД сразу не побрезговали ей воспользоваться. Спустя пару лет работы я наконец пожал плоды всей ущербности реализации этой технологии специалистами MySQL AB …

Небольшое лирическое отступление.

5 октября некая компания под названием Highload++ пригласила на конференцию Петра Зайцева, для проведения интенсивного однодневного тренинга для разработчиков MySQL. Человек вроде солидный, да и в описании было явно указано

Предлагаемый тренинг представляет собой курс повышенной сложности
…
Предлагаемый курс представляет собой интенсивный однодневный тренинг. Слушателям будут предоставлены персональные компьютеры с ОС Linux. Время на выполнение заданий будет ограничено.

Ох ничего себе думаю, что-то новенькое, задания какие-то, рассмотрение наиболее эффективных архитектур … надо идти! Наконец то думаю уму разуму учить будут. Каково же было мое удивление, когда про MySQL на конференции заговорили лишь только во второй половине дня, никаких заданий не было, да и ноутов с линухами ясно дело тоже не дали. Ну да я не гордый посидим и так, думаю, послушаем, но когда после обеда мы дошли до того что оптимизация MySQL может быть достигнута путем изучения планов выполнения запросов, и в зале разгорелась дискуссия — о том как правильно читать планы запросов, я не выдержал. На очередном кофе брейке подошел к Петру и спросил, а будет ли на конференции (за последние оставшиеся два часа) реально продвинутая информация для разработчиков БД, на что получил замечательный ответ. Что-то вроде

Вообще продвинутых курсов по оптимизации MySQL не существует в природе. Причиной сего недоразумения является то, что основная масса курсов предназначена для штатов, а там разработкой занимаются “индусы” и им для этого глубоких знаний не требуется.

От оно значит как.
Еще крайне понравилась мысль, высказанная Петром, о том, что MySQL это большое сборище говнокода, которое работает неоптимально именно по той причине, что написан он крайне криво, может и из лучших соображений, но получилось то что получилось. И как бы вы не оптимизировали свой сервер БД, вы рано или поздно столкнетесь с тем, что у вас наблюдается деградация производительности именно из-за того, что в исходниках сплошные баги и архитектурные просчеты. Единственным возможным способом уйти от этих проблем является использование больших объемов оперативной памяти (512 Gb +) и SSD дисков (как было показано на коференции лучше всего для этого подойдут 2 Tb c 10000 циклами перезаписи, с их скоростью записи вы убьете их минимум за 3 года).
Конечно, я не сторонник решения проблем путем накидывания железа, вместо использования более эффективной архитектуры, но проблемы с партициями показали всю правоту его слов.

Начало

Итак, вы решили использовать в своем проекте партиционирование.
Для начала посмотрите на код который открывает партиции и подумайте оно вам надо?

	is_part = strstr(norm_name, "#P#");
retry:
	/* Get pointer to a table object in InnoDB dictionary cache */
	ib_table = dict_table_get(norm_name, TRUE);
	
	if (NULL == ib_table) {
		if (is_part && retries < 10) {
			++retries;
			os_thread_sleep(100000);
			goto retry;
		}

ТВОЮ МАТЬ!!!
Ну с десятой то попытки оно 100% открыться!
Основные зачади, которые вы скорее всего хотите решить, это:

• архивирование (к примеру в 99% случаев вам нужны данные только за последний месяц, тогда есть смысл использовать месячное партиционирование, скидывая каждый месяц старые данные в архив)
• data warehousing (вы решили сделать OLAP систему на базе MySQL и вместо отсутствующих там OLAP кубов вы используете кучу предагрегированных таблиц с часовыми, дневными, месячными и годовыми партициями)
• иные случаи (рассматривать не буду так как они более экзотичны и по самой важной классификации, которая заставляет забыть о партициях в MySQL до версии 5.6 эти случаи попадают в первый два пункта)

Такое разбиение я выбрал лишь по одному критерию — количество партиций. В первом случае их вряд ли более 100 во втором же их 10000+.
Как вы знаете MySQL умеет работать с ОЧЕНЬ большим количеством таблиц — 100000 это примерный моральный порог, при котором стоит задуматься об изменении архитектуры. Значит по всей логике десятки тысяч партиций для него не должно быть проблемой. К сожалению это не так.
Ранее я работал с СУБД которая все свои метаданные держит в оперативной памяти и запросы по словарю не вызывают у неё затруднений, по этой причине, при реализации саппорт утилит для партиционирования, я не побоялся сделать запрос из INFORMATION_SCHEMA.PARTITIONS. Это было роковой ошибкой … Каким образом мы можем узнать какие именно партиции есть в данной таблице:
— вести данный список самостоятельно
— выполнить пару запросов (на примере дневных партиций)

    select count(1)
      into v_av_partitions_count
      from information_schema.partitions
     where table_name = i_table_name
           and substring(partition_name, length(i_table_name) + 2) =
                cast(date_format(i_data_date, '%Y%m%d') as signed)
           and table_schema = database();

    select min(partition_name), min(partition_description)
      into v_future_partition, v_future_partition_desc
      from information_schema.partitions
     where table_name = i_table_name
           and substring(partition_name, length(i_table_name) + 2) >
                cast(date_format(i_data_date, '%Y%m%d') as signed)
           and table_schema = database();

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

Хьюстон у нас проблемы

Мои проблемы начались, когда я залил себе структуру БД (порядка 7000 пустых партиций) и поставил на event_scheduler проверку партиций каждые пол часа. Как только наступало время Ч (запуска 2-х запросов из INFORMATION_SCHEMA.PARTITIONS) мой ноут взлетал, выдавая максимально возможную нагрузку по дискам в течении 3-5 минут. Странно подумал я … у меня же пустая БД. Покопавшись я понял, что надо вникать как же именно работает вызов запросов из этой несчастной таблицы. PERFORMANCE_SCHEMA меня уверила в том, что я читаю 7000 фалов, при чем ввиду того, что БД у меня достаточно урезанная я делаю эти чтения с диска. Открыв исходники я понял в чем собственно проблема. Словарь данных — не хранится в оперативной памяти. Когда вы делаете запрос из таблицы описывающей партиции происходят следующие операции:

• не важно что именно вы запросили — с диска читается описание всех таблиц вместе со всеми партициями
• если это таблицы INNODB то статистика по ним отсутствует (а для отображения этого представления нужна приблизительная статистика по количеству записей в каждой партиции и некоторые характеристики индекса типа кардинальности и иже с ними) значит мы читаем не только описание партиции но и делаем динамический семплинг индексов
• но это ещё не все, так как скорее всего у вас включен адаптивный кэш, который хранит часть индексов в виде хэш мапы для их более быстрого использования

Таким образом одним запросом из словаря данных мы

• вымываем табличный кэш — разделяемый для всех потоков
• вымываем кэш буферов — используемый всеми сессиями
• вымываем адаптивный кэш — запись и чтение которого контролируется всего одной блокировкой для обеспечения его консистетности

Вот к примеру трейс снятый gdb во время запроса по TABLE_ONE

Thread 6 (Thread 0x7fccff1be700 (LWP 2561)):
#0  
0x00007fcd3af4c493 in pread64 () from /lib/libpthread.so.0
...
#5  
fil_io (type=10, sync=<value optimized out>, space_id=<value optimized out>, zip_size=<value optimized out>, block_offset=<value optimized out>, byte_offset=<value optimized out>, len=16384, buf=0x7fcd1a0b4000, 
    message=0x7fcd08a6e800) at /home/mcshadow/svn/mysql-5.5.15/storage/innobase/fil/fil0fil.c:4481
#6  
0x00000000007e527a in buf_read_page_low (err=0x7fccff1b85c8, sync=1, mode=<value optimized out>, space=52456, zip_size=0, unzip=<value optimized out>, tablespace_version=312, offset=4)
    at /home/mcshadow/svn/mysql-5.5.15/storage/innobase/buf/buf0rea.c:148
...
#9  
0x00000000007bb6f1 in btr_block_get_func (index=0x7fccf4adfb78, mtr=0x4000) at /home/mcshadow/svn/mysql-5.5.15/storage/innobase/include/btr0btr.ic:55
...
#14 
0x00000000007f1edf in dict_table_get (table_name=<value optimized out>, inc_mysql_count=1) at /home/mcshadow/svn/mysql-5.5.15/storage/innobase/dict/dict0dict.c:753
#15 
0x0000000000776533 in ha_innobase::open (this=0x7fccf4128130, name=0x7fccff1b9230 "./DATABASE/TABLE_TWO#P#TABLE_TWO_20100823", mode=<value optimized out>, test_if_locked=<value optimized out>)
    at /home/mcshadow/svn/mysql-5.5.15/storage/innobase/handler/ha_innodb.cc:3737
...
#18 
0x000000000064716d in handler::ha_open (this=0x28, table_arg=<value optimized out>, name=0x7fccf4076d08 "./DATABASE/TABLE_TWO", mode=2, test_if_locked=26128432) at /home/mcshadow/svn/mysql-5.5.15/sql/handler.cc:2150
...
#23 
0x000000000052d6a4 in open_normal_and_derived_tables (thd=0x28, tables=0x7fccf4027968, flags=4294967295) at /home/mcshadow/svn/mysql-5.5.15/sql/sql_base.cc:5489
...
#25 
0x00000000005aa51f in get_all_tables (thd=<value optimized out>, tables=<value optimized out>, cond=<value optimized out>) at /home/mcshadow/svn/mysql-5.5.15/sql/sql_show.cc:3781
...

Увидев столь плачевный результат я конечно же крайне расстроился, так как покопавшись в исходниках я понял, что исправить это просто — не выйдет. Но это было далеко не все.

Чем дальше в лес тем больше дров

Все мануалы учат нас, что в MySQL есть две схемы для оптимизации работы с партиционированными таблицами.
— partition pruning статическая линковка партиций на этапе парсинга запроса, для разбора используется предикативный анализ только на основании ограничений для партиционированной таблицы, будет работать between, больше, меньше или же равно
— partition selection динамическая линковка на этапе выполнения, будут использованы только предикаты по условию равно
Детально останавливаться на этом не буду — это тема отдельной статьи. Вкратце — первый случай это когда партиция явно указана в самом запросе, второй случай — это когда партиция может быть вычислена на этапе выполнения запроса на основании данных из другой таблицы. Как вы понимаете второй лучше не юзать — ибо если у вас поменяется план, то вы пролетитесь по всем партициям, именно по этой причине везде где только можно я использую первый способ определения партиций, как наиболее быстрый и надежный, пологая что будут прочитаны только те партиции — которые указаны в запросе … наивный.
И так где жу тут засада? Засада собственно в том, что partition pruning выполняется после открытия таблиц, всех таблиц участвующих в запросе. И не важно что вы делаете SELECT, INSERT, UPDATE MySQL произведет следующие манипуляции:

• откроет все партиции всех таблиц участвующих в запросе
• повесит на каждую партицию блокировку на чтение по правилам используемого движка
• если это INNODB произведет расчет статистики по таблице и её индексам не забыв при этом повесить X блокировку — ну как же без этого
• и только после всего этого будет произведен partition pruning

Не верите? Вставьте код

    char*		is_part = NULL;
	
	is_part = strstr(table->name, "#P#");
	
	if (is_part) {
		ut_print_timestamp(stderr);
		fprintf(stderr,
			"  TRACE dict_update_statistics %s %s\n",
			table->name,
			only_calc_if_missing_stats?"true":"false");
	}

в начале процедуры dict_update_statistics которая вычисляет статистику и результаты вас удивят. При чем при дефолтных настройка СУБД пересчет статистики не кэшируется, а производится каждый раз когда вы пытаетесь получить метаданные.
В свете сказанного выше радует закрытие бага #19588

Ложка меда в бочку дегтя

Скоро выйдет MySQL 5.6 там обещают кучу всего полезного как то
— статический словарь для INFORMATION_SCHEMA (уж не знаю будет ли там представление partitions но очень на это надеюсь)
— конструкция select * from table partition (p1) при использовании которой partition pruning будет отрабатывать раньше чем открытие таблиц и установка блокировок

Выводы или как с этим жить

— если в вашей архитектуре много партиций 1000+ лучше одумайтесь и пересмотрите архитектуру, партиционный движок для MySQL все ещё не вышел из песочницы
— выставьте параметр innodb_stats_on_metadata = OFF это позволит не перечитывать статистику по партициям на каждый запрос, хотя это все равно будет происходить, но не так часто
— увеличьте ulimit -n до максимально возможного количества партиций в вашей системе умножив на 2
— увеличьте параметр table_open_cache до максимально возможного количества партиций в вашей системе умножив на 2 это позволит вам предотвратить вымывание табличного кэша
— если вы работаете с таблицами INNODB посмотрите в сторону включения параметра myisam_use_mmap, MyISAM кэширует только индексы, а кэширование файлов данных оставляет операционной системе, по этому при повышенной нагрузке ваши словари лягут на диск, что конечно же нежелательно.
— не делайте запросов к таблице INFORMATION_SCHEMA.PARTITIONS хотя как вы понимаете это вас все равно не спасет от тормозов и блокировок на любую DML операцию по всем партициям используемых таблиц.