Партиционирование в mySQL

[igorsia76]

Подскажите пожалуйста вот по какому вопросу: с появлением mySQL 5.1 начало рекламироваться партиционирование, как средство значительно повысить производительность. К сожалению нигде не могу найти информацию как партиционируются индексы таблицы

Comments

[igorsia76]

Вы либо не прочитали мой вопрос либо просто плохо представляете проблематику данной области.
1. Вы про "LOAD DATA CONCURRENT" что знаете? (а у меня про него указано). Эта конструкция работает только с MyISAM. Если Вы знаете другой способ вливать данные частями в течение суток количествами в пару другую сотен миллионов записей без снижения скорости выполнения select запросов расскажите. Буду весьма обязан.
2. Для одного индекса партиционирование работает, я знаю. Меня интересует как влияет партиционирование по одному индексу на другие. У меня сложилось впечатление что хреново.
3. Информации ТАМ нет. Это узкопрактический вопрос. Вам предоставить информацию для моделирования чтобы у себя посмотреть? Скажите что надо.
4. Эксплейны на что? Селекты работают без вопросов. Боевые данные из базы к сожелению предоставить не могу.
Смоделировать ситуацию можно например на такой таблице:

CREATE TABLE `b01` (
`ip` char(15) DEFAULT NULL,
`mac` char(17) DEFAULT NULL,
`ip_time` char(6) DEFAULT NULL,
`ip_port` char(10) DEFAULT NULL,
KEY `ix_ip` (`ip`),
KEY `ix_mac` (`mac`),
KEY `ix_port` (`ip_port`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=1000000000;

переменные, которые сильно влияют на скорость заливки
sort_buffer_size=4G; буфер сортировки желательно делать не меньше размера вливаемых данных
key_buffer_size=10G; этот буфер желательно делать не меньше размера файла индексов, который должен в результате получиться, главное чтобы процесс mysqld не занял памяти больше чем есть физически. Такие переменные можно применять для сервера с 16ГБ ОЗУ.

Очень наглядно проиллюстрирует проблему заливка данных по одному миллиону записей из текстового файла с разделителями. Сначала данные будут "влетать" в таблицу, потом время на добавление данных будет увеличиваться больше и больше и, наконец система должна будет дойти до описанного мной состояния - процессор нагружается все меньше и меньше, дисковая подсистема почти не задействована.

Селекты из этой таблицы идут по одному из трех ключей -
select * from b01 where ip='192.168.1.5';
select * from b01 where ip_port='6501';
select * from b01 where mac='01:02:03:1A:2B:3C';

[kostja_osipov]

0) Версия MySQL? Репликация включена или нет? Все эти вещи надо писать сразу, если хотите чтобы Вам помогли.
1) Использовать InnoDB. В InnoDB LOAD DATA будет concurrent и без ключевого слова CONCURRENT. Если вам не нужны гарантии, ослабьте требования к консистентности InnoDB, такие как doublewrite buffer, innodb_flush_log_at_trx_commit, и т.д.
2) Влияет в какой ситуации? Для какого запроса?
3) Версия, конфигурация, схема данных, примеры живых запросов. Без этого - гадание на кофейной гуще. Вы даже не потрудились у казать partition by клаузу!
4) Если SELECT'ы работают без вопросов, то зачем Вам partitioning? Схема данных выглядит, мягко говоря, странно, т.к. непонятно почему для ip и port используется не int32 и int16 (int и shortint соответственно), а строковые поля (видимо, не научились делать range search по ip представленному в виде int)

Ваш предварительный диагноз: Всё торможение происходит при перестроении MyISAM индексов на каждую вставляемую строку. Всё это описано в manual'е:
http://dev.mysql.com/doc/refman/5.5/en/optimizing-myisam-bulk-data-loading.html

В 5.6 есть полезная команда alter table exchange data with partition. В Вашем случае, скорее всего, не partitioning нужен, а (hate to say that), MyISAMMRG таблицы. But beware: here be dragons.

[igorsia76]

0. 5.0 и 5.1 репликации нет. CentOS 4.5, 5.5 сервер HP DL 585
1. Попробую завтра. Результаты предоставлю.
2. На запросы НЕ ВЛИЯЕТ. Влияет на добавление данных. При партиционировании таблицы как по диапазону ip_time(поле без индексов) так и по полю с индексом время заливки данных увеличивалось.
3. Я указал исходную таблицу.
пробовал
PARTITION BY RANGE( ip_time ) (
PARTITION p0 VALUES LESS THAN (1200),
PARTITION p1 VALUES LESS THAN (1600),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (2400),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
диапазоны выбраны исходя из примерно одинакого количества записей в каждой партиции
и
PARTITION BY KEY(ix_ip)
PARTITIONS 4;

4. Структура данных дана для примера. К сожалению реальные данные дать не могу.
Кстати, насколько я заметил при натурных испытаниях, даже при следовании Вашим рекомендациям выигрыш незаметен.

Мануал я читал. Конкурирующих вставок у меня нет и быть не может.

MyISAMMRG рассматривался как последний вариант перед тем как застрелиться у вставших колом серверов. Если бы такая таблица была одна на всю базу, а не две в сутки за 3 года, такой вариант был бы вполне неплох. В моем же случае я рискую получить катастрофическое увеличение количества используемых файловых дискрипторов. Только не спрашивайте зачем мне это нужно и где я это храню.
Шардинг тоже, к сожалению, не рассматривается.

РЕЗЮМЕ. Попробую InnoDB. Если он не будет блокировать селекты и будет вливать данные не медленнее попробую перейти на него.

[kostja_osipov]

В 5.0, 5.1, 4.1, 4.0, 3.23 и даже, по-моему, 3.22 репликация есть :-)

[igorsia76]

"нет" всмысле "не пользуюсь"

[kostja_osipov]

Молодец что взяли 5.5

[igorsia76]

5.5 это версия цнтоса. майскл 5.1
опасаюсь переходить на 5.5 - еще не знаю что они там сломали
при переходе с 4.1 на 5.0 и с 5.0 на 5.1 стабильно ломался LOAD DATA CONCURRENT
каждый раз фиксов ждал год