Все врут или почему в 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 операцию по всем партициям используемых таблиц.
    Поделиться публикацией
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама

    Комментарии 68

      +3
      Нет пределу совершенства. Как сказал мой бывший босс, любой код можно улучшать.
      А MySQL хороший бесплатный продукт в целом.
        +4
        судя по тому, что босс бывший, вы с ним рискнули не согласиться?
          +9
          Босс бывший т.к. судьба развела нас по разным странам. Он — в Минске, я — в Бостоне.
          С тем что в 99% случаев код (даже свой) можно улучшать я согласен.
          Поэтому критиковать чужой стараюсь в позитивном ключе — на примерах как нужно делать и чем это лучше.
            +3
            Вообще стараюсь не критиковать чужой код, так как прекрасно знаю как тяжело писать что-либо в большом проекте, сам не без греха, глядя на свои код годовалой давности часто в душе возмущаюсь какой придурок это написал :) но ошибки реализации описаные в статье просто заставляют волосы на моей голове встовать дыбом! Почему этого нет в документации? Ну написали, молодцы, а почему никто не знает что это работает НАСТОЛЬКО криво…
              0
              > Почему этого нет в документации? Ну написали, молодцы, а почему никто не знает что это работает НАСТОЛЬКО криво…

              Я вчера специально полезла в «Restrictions and Limitations on Partitioning». Но тут какое дело: этот нюанс надо прям целиком Вашей статьёй документировать, что не есть формат мануала. Потому что просто рекомендация «не используйте много партиций» в целом неверна, так как задачи у всех пользователей разные и для кого-то (с быстрым железом) описанные недостатки менее важны конечной задачи. IMHO, конечно.

              Кстати Information Schema будет просто с большИм количеством таблиц, индексов и прочих объектов тормозить. Это не вина partitions.
                0
                на сколько я понимаю если табличный кэш умещает все таблицы БД то таких перечитываний, при запросе таблиц INFORMATION_SCHEMA быть не должно, а что насчет парсинга запросов? т.е. я заметил что если мы к примеру запрашиваем
                select * from partitioned_table t1 where partition_key = 1
                мы читаем статистику по всем партициям данной таблицы, по идее запоминаем её в табличном кэше так как происходит их открытие, однако если следом запустить
                select * from partitioned_table t1, partitioned_table t2 where t1.partition_key = 1 and t2.partition_key = 1
                то статистика вновь будет перечитана, даже несмотря на выставление параметра innodb_stats_on_metadata = OFF
                вроде она уже в share должна быть… не хватило уже сил разобраться что там не так…
                  0
                  По поводу innodb_stats_on_metadata=OFF есть bug #60071 (статистика пересчитывается при каждом обращении к information_schema.partitions, в комментариях подробно почему), но статистика считается во время analyze table и обращений к information schema. Простых select-ов процедура её подсчёта касаться вообще не должна. Или я не поняла вопрос?
          0
          Пытались заюзать MySQL Embedded вместо QSlite'а и знаете? Это просто фееричный глюкодром, падает даже при простой попытке породить тред.
          +5
          Петр по-моему делает успешный бизнес на этом www.percona.com/prices/per-hour-prices/
            +2
            Я сейчас за себя, конечно, говорю. Мне кажется, дело ещё и в том, что просто «серебряной пули» нет. То есть «advanced performance optimization» — это способность анализировать плюс опыт.
              +2
              После конференции я могу уверенно сказать, что вся перкона — это просто саппорт тима для MySQL. И все его сотрудники делают деньги на том, что продукт сей несколько кривоват.
              На вопрос: почему вы не включили полезную фичу, которую вы написали по репликации в перкону, Петр ответил — так нам за это никто не заплатил, а времени и так в обрез. Вот найдется заказчик которому это надо — включим, а так пусть на полке пылится. Это не есть плохо, но просто показывает для чего создана компания.
              • НЛО прилетело и опубликовало эту надпись здесь
              +8
              У меня, думаю как и у многих, возник с ходу вопрос, а была ли попытка использования Postgresql (если уж сравнивать бесплатные решения)? Не холивара ради, а пользы для. Поэтому как в топике описывается выбор СУБД и возникшие в связи с этим грабли. Поэтому в итоге не очень ясно, чем закончилась история. Ежики плакали, кололись, но продолжали жрать кактус, либо же ёжики проявили архитектурную дальновидность и таки отыскали себе немножко молочка (тогда какого?).
              • НЛО прилетело и опубликовало эту надпись здесь
                  +2
                  Не увидел нигде слов «будет отличным решением». alekciy лишь хотел увидеть состояние дел с теми же партициями в pgsql. Лучше ли там, чем в mysql, или хуже.
                    +1
                    На сколько я знаю, в постгресе вообще нет встроенного партиционирования как такового. Оно достигается, во всяком случае в нашем проекте, через использование триггеров и наследования таблиц, короче «нормальные» такие костыли.
                    Мне вот товарищи ораклисты подсказывают, что вот в оракле с партиционированием всё хорошо =)
                    0
                    Ну так не Oracle или MS SQL же предлагать! И не SQLite.

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

                    Задача архитектора проекта заключается в том числе и в выборе платформ на которых все будет строиться. И тут стратегические ошибки в начале не компенсируются тактическими успехами в будущем. Если строить здание из заведомо некачественных материалов, не стоит удивляться, что завтра это здание рухнет. В общем ответ я так и не получил, но видимо имеем ежиков и кактус.
                      +4
                      Однако… Как вы видете переход проекта, работа над которым ведется ни один год, на другой сервер СУБД, в котором возможно есть такие же проблемы? Переход должен быть обоснованным, надо провести исследования, научиться эффективно писать код под выбранную платформу, выяснить её плюсы и минусы, переписать мегабайты кода с одного диалекта на другой (я уж молчу об изменении архитектуры)
                      Пока жрем кактус — ищем workaround'ы и продолжаем работать с тем что есть, изучая в фоне то, что предлагают другие разработчики СУБД.
                      PostgresQL пока единственно возможная альтернатива, пока переписали Java драйвера для работы с процедурами и переводим в тестах одну подсистему, в которой не очень много SQL кода, что выйдет — посмотрим.
                        0
                        Я полностью поддерживаю мысль о том, что переход уже работающего проекта на новую СУБД скорее всего не покроет расходы на этот переход. По крайне мере в первом приближении, потому уже смотрим по ситуации. Но исходя из статьи совершенно не очевидно, что разговор уже про работающую систему, а фраза "вы решили использовать в своем проекте партиционирование" наталкивает на мысль о старте нового проекта. Поэтому в таком контексте мои замечания вполне обоснованны.
                        +2
                        Давайте абстрагируемся.
                        Человек выяснил, что в его жигулях на заднем ходе стучит коробка. И выяснил почему. Знатие того, что в опеле оно стучит по другим причинам или не стучит вообще не поможет. У него есть жигуль.

                        Кстати, гораздо информативнее читать критический разбор движка чем в очередной раз проводить сравнения с другим набором синтетических тестов: кому надо просто учтёт такю багу.
                      +2
                      Сатрый друг луче новых двух. Честно говоря — страшно, так как в постгресе однозначно будут свои грабли. Есть мнение что не факт, что их будет меньше, но параллельно мы ведем вялотекущий проект по переходу на постгрес.
                        0
                        Имхо, грабли есть в любом проекте который ставиться под нагрузку. Просто потому, что серьезные продакшен нагрузки могут генерить единицы проектов и не всегда их опыт вливается в проект. А остальным хватает и так что есть.

                        Лично на меня пострес производил более хорошее впечатление в том числе и отсутствием какой либо, имхо, истории вокруг него. И то, что его использовали скайповцы и внесли в проект свои наработки, тоже плюс.
                      +5
                      В PostgreSQL партицирование немного «эмулируется», т.е. оно не родное, но близко к этому (если написать несколько хранимок вокруг, которые упрощают управление партициями):
                      www.postgresql.org/docs/current/interactive/ddl-partitioning.html
                      См. там раздел «Partitioning and Constraint Exclusion».

                      Но т.к. он все данные о структуре БД (в том числе — о наличии и количестве таблиц) извлекает из централизованного pg_catalog (в котором, между прочим, есть индексы), то вряд ли он открывает все-все партиции, когда делается запрос в таблице.
                        +7
                        Кстати, вот тут: stackoverflow.com/questions/6104774/how-many-table-partitions-is-too-many-in-postgres
                        говорят, что он будет линейно проверять все CHECK-и для каждой партиции, так что если их 8000, то при планировании запроса будет проверено 8000 CHECK-ов (вряд ли это дольше нескольких десятков миллисекунд), и затем только те партиции, которые попали под их CHECK-и, и будут обработаны.
                          0
                          Ну вот, когда знаешь где искать то и постгрес не идеал.
                        +1
                        Спасибо за пост!
                          –9
                          Спасибо Оракл за это.
                            +1
                            Спасибо за пост. Ссылки на конкретные куски кода это лучшие аргументы в таких обсуждениях.

                            Я понимаю, что вы НЕ хотели этого сказать, но для тех кто не в теме может сложиться ощущение, что Петр не квалифицирован. Судя по вашему описанию курс и правда был далек от «продвинутого», но вот MySQL Performance Blog и книга High Performance MySQL, к которым Петр имеет самое прямое отношение, являются просто безценными источниками информации когда дело касается MySQL. Это к слову…

                            Спросить я хотел другое. С MySQL понятно. А вы пробовали смотреть Percona Server в плане партиции? Или может сам Петр что то говорил об этом? Там все так же плохо?
                            • НЛО прилетело и опубликовало эту надпись здесь
                                0
                                Конкретно по этому вопросу не смотрел, однако ранее при возникновении критических ошибок подобного рода смотрел исходники Percona Server. Сложилось ощущение, что перкона это просто оболочка, которая никаких серьезных изменений в MySQL не привносит. Партиционирование имеет довольно размазанную по коду ошибку — не думаю что перконовцы её пофиксили.
                                Петр высококлассный специалист, его статьи в Performance Blog просто отличные, камень не в его огород а в огород организаторов конференции, которые нагло наврали про её содержание. После того как я вышел из Инфопространства — сложилось мнение что меня поимели за мои же 300$
                                +4
                                MySQL это большое сборище говнокода, которое работает неоптимально именно по той причине, что написан он крайне криво, может и из лучших соображений, но получилось то что получилось.

                                А кто-нибудь знает, на сколько качество кода в MariaDb и drizly (это форки mysql) лучше? Вопрос не про патиции, а в целом…
                                  +2
                                  drizzle это mysql с оторванными процедурами, триггерами и чем-то еще. там внутри они несколько упорядочили код, но кардинальных изменений в том что осталось я там не видел. насколько я понимаю их подход — это избавление от «проблемных фич» и достижение бОльшей стабильности именно за счет выкидявания кода, а не за счет исправления архитектуры mysql.
                                  mariadb тоже несколько причесали код, но кардинально переписывать код у них нет ни особо ресурсов ни, как следствие, желания. afaik. судя по тому что они пишут, они допиливают оптимайзер, и в смысле сабкреверей сейчас у них получше чем в mysql. конкретно про partitioning не пишут ничего, значит то же самое что и в mysql.
                                  в целом же форки сам сервер не особо развивают. percona это не надо, они другим занимаются. у mariadb насколько я понимаю ресурсов лишних нет. drizzle вообще занялся дауншифтингом;)
                                    0
                                    Наверняка не намного. Это же форки. То есть подавляющее больинство кода там все равно MySQL-вское
                                      0
                                      drizzle не форк, там другая архитектура вообще внутри и кода достаточно мало внутри наследованого
                                        0
                                        С сайта дриззла:

                                        Drizzle is a community-driven open source project that is forked from the popular MySQL database.
                                        The Drizzle team has removed non-essential code, re-factored the remaining code into a plugin-based
                                        architecture and modernized the code base moving to C++.



                                        Насколько они убрали и рефакторнули, неизвестно.
                                    +1
                                    Давно заметил, что в одной популярной российской CMS в лог медленных запросов часто попадает первый выполняемый запрос: «SHOW TABLES LIKE 'Settings'». Уж не по той ли самой причине, по которой «select count(*) from tables» может выполняться 2 часа? Интересно мнение snevsky.
                                      +1
                                      По идее все таблицы из INFORMATION_SCHEMA формируются на лету по этому нет смысла их индексировать, но что именно там происходит в конкретном случае — сказать сложно — посмотрите что там в performance_schema
                                      show processlist; -- смотрим идентификатор нужного нам процесса
                                      
                                      select *
                                        from performance_schema.threads t
                                       where t.processlist_id = 1; -- получаем идентификатор треда для трейсов
                                      
                                      select   'current' type,
                                               event_name,
                                               source,
                                               sum(timer_wait) timer_wait,
                                               count(1)
                                          from performance_schema.events_waits_current
                                         where thread_id = 25
                                      group by event_name, source
                                      union all
                                      select 'history' type,
                                             event_name,
                                             source,
                                             sum(timer_wait) timer_wait,
                                               count(1)
                                        from performance_schema.events_waits_history
                                       where thread_id = 25
                                      group by event_name, source
                                      union all
                                      select   'long' type,
                                               event_name,
                                               source,
                                               sum(timer_wait) timer_wait,
                                               count(1)
                                          from performance_schema.events_waits_history_long
                                         where thread_id = 25
                                      group by event_name, source
                                      order by timer_wait desc;
                                         -- limit 10;
                                      

                                      Рискну предположить, что причина аналогична.
                                        0
                                        Создал 18 тысяч таблиц, попробовал разные варианты. Просто Show tables выполняется секунд пять, сопровождается интенсивным чтением. Show tables like выполняется мгновенно. Select count(*) from tables выполняется секунду-полторы, но сопровождается не чтением, а записью примерно 25 мегабайт на диск. Ума не приложу, что он пишет.

                                        Видимо попадание Show tables like в лог можно отнести скорее к холодной базе во время обращения, нежели к тормозам самого запроса.
                                          0
                                          Я склонен объяснять быстрое выполнениа запроса — тем, что табличный кэш содержал все что надо, лучше для таких экспериментов — перезапускать СУБД.
                                          Холодная база понятие относительное — в первую очередь потому, что на её разогрев может не хватить ресурсов или ряд параметров выставлен не верно, и какой нибудь кэш все таки вымывается.
                                          0
                                          Насчет performance_schema я слышу впервые. Правильно я понимаю, что она появилась только в 5.5?
                                            0
                                            вроде как да
                                            сыровата конечно пока — но в решении спорных вопросов — очень даже помогает.
                                        0
                                        Мне доводилось видеть пару полезных фиксов (не по этой теме) от гугла, может они что полезного пишут. Я сейчас делаю ставки на Oracle судя по тому что сделали в 5.6 это прорыв, там куча полезных фич, которые ждут многие. Есть мнение, что они доведут это БД до ума, хотя тут есть подвох, они могут сделать все это платным, прецеденты уже появились
                                        commercial distributions of MySQL include a PAM authentication plugin that enables MySQL Server to use PAM to authenticate MySQL users
                                        Только после выхода 5.5.16 я понял почему по этой теме были такие хреновые доки, не дай бог сами пользователи напишут плагин, который мы выпускаем в следующем релизе за деньги.
                                          0
                                          Пост хороший, по делу и с конкретикой. НО, очень много передергиваний, да и в целом истерия не понятна.

                                          > Для начала посмотрите на код который открывает партиции и подумайте оно вам надо?

                                          Автор, что вас так взволновало? Впервые в жизни увидели спинлок как метод синхронизации доступа в СУБД? Или количество попыток?

                                          > Как только наступало время Ч (запуска 2-х запросов из INFORMATION_SCHEMA.PARTITIONS) мой ноут взлетал

                                          А что тут удивительного, если этим запросам необходимо перелопатить кучу данных? Вы же не будете выполнять их регулярно в production коде? «Чтобы создать новую партицию или же сделать реорганизацию существующей», вам не нужно перебирать все. Я думаю, если вы догадаетесь заменить «substring(partition_name...» на «like partition_name+...», сервер догадается как найти нужные данные более эффективно.

                                          Оптимизировать все это конечно можно и нужно. Обращение ко всем партициям таблицы когда нужно и не нужно при выполнении запросов — это очень плохо и надеюсь скоро исправят. С кэшированием словаря тоже нужно что-то делать, согласен. Сам по себе механизм партиций появился не так уж давно, ему еще нужна обкатка. Кроме того, вы ведь пользовались Community редакцией, верно? Возможно в коммерческой дела обстоят лучше?
                                            0
                                            Сам по себе механизм партиций появился не так уж давно, ему еще нужна обкатка. Кроме того, вы ведь пользовались Community редакцией, верно? Возможно в коммерческой дела обстоят лучше?

                                            угу года 2-3 как уже есть.
                                            уверен на 100% что commercial версия ничем от community в данном вопросе не отличается.
                                              0
                                              Для open-source модели (в прошлом) и фичи далеко не массового использования 2-3 года это не так уж много.
                                              0
                                              Прошу прощения, там конечно же не спинлок а обычное ожидание.
                                                0
                                                > Кроме того, вы ведь пользовались Community редакцией, верно? Возможно в коммерческой дела обстоят лучше?

                                                Коммерческая редакция собирается немножко с другими параметрами, чем Community. Partitions (которые появились в 2005 году) там работают точно также как в Community.

                                                Зато покупая Enterprise вы впридачу получаете доступ к коммерческим продуктам и поддержку.
                                                0
                                                — меня взволновало то, что вместо решения проблемы мы просто пытаемся сделать это 10 раз, вопрос конечно спорный — но я не сторонник таких решений
                                                — запрос по словарям данных должен выполняться мгновенно.
                                                Не важно что именно вы напишете в запросе, сервер СУБД все равно перечитает все партиции всех таблиц и пересоберет по нима статистику. Прочитайте тему внимательнее пожалуйста, там детально объясняется почему ваше предположение не работает. Проверка наличия партиции по словарям поднимает с диска все партиции и производит по ним динамический сэмплинг для индексов.
                                                  –1
                                                  > вместо решения проблемы мы просто пытаемся сделать это 10 раз
                                                  Предложите (и обоснуйте) ваше решение этой «проблемы». Хотим получить объект БД, а он оказывается сейчас занят. Что делать?

                                                  > запрос по словарям данных должен выполняться мгновенно
                                                  Это требование разумно, если в БД 100-1000 таблиц. Но если их 10К, и у многих по 10К партиций, то есть объемы данных действительно большие, то держать весь словарь в кэше ради редких запросов это согласитесь неразумно. Это для любой СУБД.

                                                  Разумно будет требовать, чтобы внутренние частые операции со словарем (при компиляциии/исполнении запроса например) выполнялись почти мгновенно.
                                                    0
                                                    там нет получения объекат БД — это просто открытие файла партиции…
                                                    на второй вопрос у меня даже слов нет чтоб возразить… это такой тонкий троллинг или вы реально не понимаете смысла написанного?
                                                  0
                                                  Из поста складывается впечатление, которое у меня возникло несколько ранее по опыту использования, что MySQL для серьезных высоконагруженных приложений со сложной логикой не очень пригодна. Все, что касается больших объемов данных с простой структурой, она выполняет на отлично. А вот сложно связанные данные, с партициями, синхронизацией серверов и архивированием и прочими атрибутами крупного проекта она переваривает плохо.
                                                    +1
                                                    Работая с любой БД на серьезных объемах — сталкиваешься с проблемами. Oracle постоянно валит ORA-600 как только начинаешь использовать его новые фичи под большими нагрузками. MySQL серьезная СУБД — единственно что меня возмущает — это то, что об этих явных недостатках нигде нету ни слова. Весь модуль партиций должен идти с индексом beta и описанием тех проблем, которые я осветил в этой статье.
                                                      0
                                                      А с MS-SQL2005 не приходилось сравнивать? (ну или с более новыми версиями) Раньше я часто использовал Express Edition в своих небольших проектах (она бесплатна) и Standard/Enterprise по работе.
                                                      Сейчас в основном площадками являются хостинги, на которых стопроцентно есть MySQL, иногда PostgreSQL, а MS-SQL имеет смысл только для .NET проектов, поскольку виндовые хостинги гораздо реже встречаются.
                                                        +2
                                                        Думаю главный недостаток MS SQL это та операционная система, с которой работает данная СУБД. Остальные её достоинства, какие бы они не были затмеваются этим недостатком. Я привык, что сервера работают по несколько лет без единой перезагрузки, боюсь для Windows Server это скорее исключение из правил, хотя я более чем уверен что в такой серьезной СУБД данный алгоритм реализован более оптимально.
                                                          +2
                                                          Ну не буду говорить о том, что даже мой домашний сервер на Server 2003 SP2 имеет на данный момент аптайм в 8 месяцев (просто потому что 8 месяцев назад я купил UPS) и на нем крутится много приложений, не одна БД, несколько виртуальных машин и пр., мои ПК и ноутбуки имеют аптайм по несколько месяцев и перезагружаются обычно только тогда, когда какая-то софтина требует для себя перезагрузки, потому что криво написана и не поделила с кем-то библиотеку.
                                                          Поэтому для меня устойчивость этой ОС не является проблемой — серверы также работают годами без перезагрузки. Ее недостатком является стоимость.
                                                          Могу сделать предложение что NASDAQ имеет достаточно высокую нагрузку, чтобы считать MS-SQL довольно развитой и надежной. Но я также считал и Oracle надежным, однако ваши задачи выявляют в нем проблемы, поэтому поинтересовался сравнением с MS-SQL.
                                                            0
                                                            Я не ради холиваров написал ответ, а просто пояснил свою позицию относительно Windows ОС.
                                                            Я считаю Oracle и MS SQL лидерами рынка и думаю сейчас они примерно сравнялись по функциональности и надежности, но вот к примеру в бесплатной версии Oracle нету того самого партиционирования :) не знаю чего там нет в бесплатной версии MS SQL но что-то мне подсказывает ряд ключевой функциональности там тоже отсутствует, а платить десятки тысяч долларов за лицензии — дорогова-то.
                                                              0
                                                              там ограничен размер баз, нет репликации между серверами, нет кластеризации, ну и так, «корпоративных фишек», которые нужны в запутанной серверной среде большой компании.
                                                              Холивары — это болезнь гиперактивных подростков, я задал вопрос исключительно из информационных соображений, нисколько не для спора. Мнение услышал, спасибо.
                                                            –2
                                                            > главный недостаток MS SQL это та операционная система

                                                            Так вы оказывается религиозный человек :)
                                                            Жаль, я рассчитывал на интересную техническую дискуссию.
                                                              0
                                                              Я атеист, и не придерживаюсь одной СУБД по идеологическим соображениям, но к сожалению я недостаточно знаю MS SQL — по причинам описанным выше, по этому не могу вести квалифицированную техническиую дискуссию о недостатках и достоинствах этого сервера.
                                                              Да и вообще ИМХО это удар ниже пояса, когда ты пишешь о проблемах в своей песочнице, а тебе в качестве выхода предлагают перейти в другую.
                                                                0
                                                                В данном случае вы как раз заявляете о наличии проблем в «чужой песочнице», недостаточно зная ее. :) Переходить в нее вам вроде бы никто не предлагал.

                                                                Если вы подумали, что я предлагаю обсуждать именно MS SQL, то вы ошиблись. Я сам недостаточно хорошо его знаю. Но вот о Windows я знаю достаточно, чтобы в утверждениях подобных вашему подозревать религиозный фанатизм.

                                                                Интересным для меня было бы обсуждение разных проблем, которые приходится решать разработчикам разных СУБД, и конкретные их решения.
                                                                +1
                                                                > главный недостаток MS SQL это та операционная система

                                                                Думаю, отсутствие исходиков MS SQL и Oracle — вот недостатки посерьезнее. Потому как если проблема, то лучше уж по исходникам узучать ее, как автор статьи, а не обращаться в support и ждать…
                                                        –1
                                                        по этому я предпочитаю самопальный шардинг. Нагрузку на пару лимонов юзеров тянет без проблем… Хочу испытать эдак лимонов на сто, но что-то не набрать их… Очевидно наши пиарщики плохо работют.
                                                          –2
                                                          Минусует как правило тот, кто не имеет представление ни о шардинге, ни о и нагрузках.
                                                            0
                                                            шардинг и партиционирование же разные вещи?
                                                              0
                                                              Но — одно заменяет другое
                                                          0
                                                          вообще статья замечательная — спасибо.
                                                          я тоже иногда залезаю в исходники.
                                                            +1
                                                            Ну, если быть справедливым, и вконтакте и facebook благополучно используют MySQL. Да, конечно, они там ее местами патчат под свои нужды, но это дешевле, чем писать свой движок.

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

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

                                                            И вообще, если вы такой требовательный к производительности, что мешает сделать партицирование (и менеджмент партиций) на уровне приложения? Это был бы идеальный вариант, мне кажется.
                                                              0
                                                              Наверно на уровне приложения можно написать и работу с базами на оптимизированных под приложение структурах, но как раз СУБД нужно использовать чтобы это не делать. Партиции _ОЧЕНЬ_ сильно влияют на производительность, если работают конечно, и если они заявлены но не работают, это, конечно, сильное разочарование.

                                                            Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                                            Самое читаемое