CREATE TABLE `price_detail` (
`accomodation` int(11) NOT NULL,
`date` datetime NOT NULL,
`price` int(11) NOT NULL,
`room_id` int(11) NOT NULL,
KEY `idx_price_price` (`price`),
KEY `idx_price_date` (`date`),
KEY `idx_price_accomodation` (`accomodation`)
)
Предположим, что у нас в ту таблицу закладывается 3 миллиона строк на день. То есть нормально, чтобы уже сегодня нам пришдется работать с 3*365=1095 миллионов записей на будущий год. Причем в базе данных это — единственная таблица с таким количеством записей.
К таблице будут приходить простейшие запросы типа
SELECT
`price`
FROM
`price_detail`
WHERE
`accomodation`=1
AND
`date` BETWEEN '2008-01-01 12:00:00.000' AND '2008-01-15 12:00:00.000'
80% запросов охватывают период до двух недель.
Изначально наша идея была разбить таблицу на partitions понедельно. Разбили. Приведеный запрос отрабатывает за время от 20 секунд (MacBook Pro, 2 GB RAM, настройки mysql сервера почти не менялись). А нужно — менее секунды
Собственно вопрос А что делать-то
1. Может лучше использовать merge table?
2. Поможет ли cover index по колонкам (date, accomodation, price) (или в другом порядке)
3. Что подкрутить в настройках?
Здравствуйте, Mamut, Вы писали:
M>Есть тупая до ужаса таблица M>
M>CREATE TABLE `price_detail` (
M> `accomodation` int(11) NOT NULL,
M> `date` datetime NOT NULL,
M> `price` int(11) NOT NULL,
M> `room_id` int(11) NOT NULL,
M> KEY `idx_price_price` (`price`),
M> KEY `idx_price_date` (`date`),
M> KEY `idx_price_accomodation` (`accomodation`)
M>)
M>
M>Предположим, что у нас в ту таблицу закладывается 3 миллиона строк на день. То есть нормально, чтобы уже сегодня нам пришдется работать с 3*365=1095 миллионов записей на будущий год. Причем в базе данных это — единственная таблица с таким количеством записей.
Эт что ж за организация такая, у которой каждый день меняется цена на 3 000 000 помещений (accoMModation)?!
Re: [MySQL] Дизайн большой таблицы + настройка сервера
Mamut пишет:
> 1. Может лучше использовать merge table?
надо пробовать, желательно уже на production железе. Но волшебства не ждите.
> 2. Поможет ли cover index по колонкам (date, accomodation, price) (или в > другом порядке)
в указанном запросе нужен индекс [accomodation, date] — добавить дату в
idx_price_accomodation. Ну и посмотреть кардинальность существующих
индексов на реальных данных и explain что говорит.
> 3. Что подкрутить в настройках?
query cache включить в первую очередь и дать ему достаточно памяти
ну и innodb_buffer_pool_size увеличить можно.
самый простой способ — погонять сутки под реальной нагрузкой, а потом
посмотреть сервер статус из свежего phpMyAdmin-а, он довольно дельные
советы дает
PS: по поводу дизайна — поддерживаю pkarklin, но велосипед ваш — вам
виднее
Здравствуйте, Mamut, Вы писали:
M> Предположим, что у нас в ту таблицу закладывается 3 миллиона строк на день.
... M> 80% запросов охватывают период до двух недель.
Я правильно понял, что 80% запросов возвращают по 3 млн * 14 дней = 42 млн записей и эти 42 млн записей должны быть получены за время менее секунды?
Если правильно, то. Прикинем (грубо) размер одной строки 4 + 8 + 4 + 4 = 20 байт. 42 млн записей * 20 байт = 801 MB.
Т.е. ты хочешь поднять с винта 800 метров в секунду, и при этом наложить на них поиск по полю `accomodation` (т.к. в плане запроса наверняка сначала будет идти выборка по дате, а потом по accomodation)?
А ты получаешь 800МБ / 20 с = 40 метров в секунду, что где-то близко по скорости чтения с винта в идеальных условиях.
M> Собственно вопрос А что делать-то
Если я нигде не ошибся выше, то бей базу на жесткие диски (RAID, Stripe) — упрешься в скорость работы шины/контроллера (правда, достигнуть времени в 1с все равно не получится).
M>Предположим, что у нас в ту таблицу закладывается 3 миллиона строк на день. То есть нормально, чтобы уже сегодня нам пришдется работать с 3*365=1095 миллионов записей на будущий год. Причем в базе данных это — единственная таблица с таким количеством записей.
Худший вариант не прокатит По новым вводным данным строк будет намного меньше
Их буде 162 000 в день. То бишь 1 134 000 в неделю и 59 130 000 в год
Здравствуйте, Anton Batenev, Вы писали:
AB>Если я нигде не ошибся выше, то бей базу на жесткие диски (RAID, Stripe) — упрешься в скорость работы шины/контроллера (правда, достигнуть времени в 1с все равно не получится).
В догонку. MySQL для типа таблиц MyISAM может сам делить таблицу на чанки (которые, в свою очередь, можно раскидать по разным дискам (которые, в свою очередь, могут быть рэйдами)).
Максимальное значение RAID_CHUNKS составляет 255, что составляет почти 5 лет, разбитых по одной неделе. Осталось, подобрать RAID_CHUNKSIZE, такой, который бы вмещал одну неделю в один чанк и раскидать на 2 винта (ну или 1 день в 1 чанк и раскидать на 7 винтов и один винт для файла с индексами — в общем, варианты).
Re: [MySQL] Дизайн большой таблицы + настройка сервера
Что такое `accomodation` ?
> К таблице будут приходить простейшие запросы типа > > SELECT > `price` > FROM > `price_detail` > WHERE > `accomodation`=1 > AND > `date` BETWEEN '2008-01-01 12:00:00.000' AND '2008-01-15 12:00:00.000' > > 80% запросов охватывают период до двух недель. > > Изначально наша идея была разбить таблицу на partitions понедельно. > Разбили.
Думаю, зря.
Ответьте себе на один вопрос:
сколько % таблицы в записях приходится на условие
`accomodation`=1
AND
`date` BETWEEN '2008-01-01 12:00:00.000' AND '2008-01-15 12:00:00.000'
за две недели ?
Сколько записей это будет ?
И кому нужно будет такое кол-во записей ?
Приведеный запрос отрабатывает за время от 20 секунд (MacBook > Pro, 2 GB RAM, настройки mysql сервера почти не менялись). А нужно — > менее секунды
Гы, а если это невозможно ? Что значит нужно ?
> 2. Поможет ли cover index по колонкам (date, accomodation, price) (или в > другом порядке)
Вам бы над кластерный индекс по (date, accomodation) или наоборот, в зависимости
от того, какое поле более селективно. На один день — 3 млн. А на один accomodation ?
> 3. Что подкрутить в настройках?
Да в общем это не главное. Запросы главнее.
