MySQL: Какую подсистему хранения и индекс выбрать?

[aetsyss]

Добрый день!
есть БОЛЬШАААААЯ таблица с треками (координатами) gps маяков.
грубо говоря:

CREATE TABLE tracks(
org_id SMALLINT(6) UNSIGNED NOT NULL,
obj SMALLINT(6) UNSIGNED NOT NULL,
mydatetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
shir DOUBLE UNSIGNED NOT NULL,
dolg DOUBLE UNSIGNED NOT NULL... ... ...)

соответственно id организации, номер маячка, датавремя посылки, широта и долгота.

необходимо, чтобы в приемлемые сроки можно было вытащить трек по определенному объекту например за сутки, т.е. что-то типа:

SELECT * FROM tracks
WHERE org_id = 12345 AND obj = 6789
AND mydatetime BETWEEN '2013-02-15 00:00:00' AND '2013-02-15 23:59:59'
ORDER BY mydatetime;

на текущий момент выбрана подсистема хранения InnoDB и создан индекс (org_id, obj, mydatetime)
тем не менее время выполнения запроса неприлично растет пропорционально объему таблицы.

что можно предпринять?

 

[WMix]

а на сколько большая и как быстро увеличивается?

 

[aetsyss]

храню по одному месяцу в отдельной таблице. за месяц набегает примерно 70-100 млн записей.
периодичность вставки: раз в 10 сек. пакетами по несколько записей.

 

[fixxxer]

показывай индексы и explain

 

[aetsyss]

индексы:
IX_tracks3: mydate, org_id, obj
UK_tracks3: org_id, obj, mydate

оба неуникальные.

explain:
id: 1
select_type: SIMPLE
type: range
possible_keya: UK_tracks3, IX_tracks3
key: UK_tracks3
key_len:19
ref: null
rows: 3144
Extra: Using where

 

[WMix]

и когда тормозит? на последний месяц (то что активно пишется) или везде тормоз?

 

[fixxxer]

AND mydatetime BETWEEN '2013-02-15 00:00:00' AND '2013-02-15 23:59:59'

а там много в этом диапазоне строк?

попробуй force index IX_tracks3, и его explain тоже дай заодно

 

[Gas]

индекс (org_id, obj, mydate) вполне нормальный для приведённого запроса, только если датчиков намного больше чем организаций, то лучше (obj, org_id, mydate),
а если все объекты лежат в одной таблице с уникальным id, то условия по org_id не надо и в индекс это поле включать не надо.

скорее всего надо тюнить настройки базы и/или железо:
выкручивай innodb_buffer_pool_size насколько можно, ну и смотри что за диски, под такие задачи чем лучше дисковая система, тем лучше - raid из ssd/sas 10K/15K.

 

[Gas]

shir DOUBLE UNSIGNED NOT NULL

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

"DECIMAL(11,7) NOT NULL" будет получше "DOUBLE UNSIGNED NOT NULL"

 

[fixxxer]

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

 

[Gas]

а я наугад и не предлагаю, описал кейсы для разных случаев.

 

[aetsyss]

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

select mydatetime, shir, dolg, speed, zaj
from tracks4
where org_id=121 and obj=51
and mydatetime BETWEEN '2013-04-19 00:00:00' and '2013-04-19 23:59:59'
ORDER by mydatetime;

время выполнения составило 24 сек. (!!!!!). это конечно жесть! хотелось бы раз в 10 меньше!
кол-во строк на выходе - 2600

на следующий месяц добавил еще один индекс - obj, mydatetime, посмотрим как будет работать. соответственно поменяется условие where

 

[aetsyss]

если добавить force index, то время сокращается примерно до 15 сек!

explain:
select_type: SIMPLE
type: range
possible_keys: IX_tracks42 (orgd, obj, mydatetime)
key: IX_tracks42
key_len: 8
ref: (null)
rows 2531
Extra: Using where

 

[Gas]

добавил еще один индекс - obj, mydatetime

думаю не стоит

точно таблица innodb? show create table `tbl`; это подтверждает?

покажи результат запроса:

show global variables like 'innodb_buffer_pool_size';

 

[aetsyss]

да, таблица точно inoodb!
buffer sieze: 570425344

 

[fixxxer]

если добавить force index, то время сокращается примерно до 15 сек!
possible_keys: IX_tracks42 (orgd, obj, mydatetime)
key: IX_tracks42
key_len: 8
ref: (null)
rows 2531
Extra: Using where

Ага, я так и думал. Но все равно много. Если часто надо дергать именно по дате, имеет смысл ввести поле mydate и делать индекс по нему.
В mariadb, кстати, это можно сделать виртуальным столбцом.

Ненужные индексы имеет смысл потом почикать - надо понимать, что чем больше индексов, тем тяжелее операции вставки/обновления/удаления.

 

[Gas]

Мне кажется, что всё упирается в дисковую систему.
Индекса (orgd, obj, mydatetime) должно хватать,
но да, как сказал fixxxer, можно ввести поле mydate DATE и сделать индекс (orgd, obj, mydate), хотя я не думаю что скорость сильно увеличится, тем более наверное появится using filesort, так как всё равно сортировать надо с учётом времени, а не только даты.
Можно сделать поле bigint + индекс и в него пихать половину md5(organization_id, object_id, date) и делать поиск по нему (8 байт на индекс, против 16 текущих), но это всё костыли, остаётся проблема с сортировкой по mydatetime, усложняется код, а прирост по скорости не ясно будет или нет.

Посоветовал бы увеличивать innodb_buffer_pool_size, 512MB не много для системы с сотнями миллионами записей.
Размер, конечно, зависит от имеющейся памяти и прочих задач, выполняемых сервером.
Не знаю какие диски на сервере, но промониторить дисковую активность и жужжать руководству про необходимость покупки sas 10/15k или ssd (ну и памяти заодно, чтоб выкручивать innodb_buffer_pool_size).
Можно конечно при первом обращении, получив все записи, прогонять их через алгоритм Дугласа-Пекера, чтоб уменьшить количество записей и класть в отдельное хранилище (таблицу, redis, файл) и при повторных запросах оттуда тянуть одним куском, но стоит ли это делать, зависит от имеющегося железа и частоты обращений к этим данным.

 

[Gas]

Кстати, а кинь всё-таки полный show create table таблицы с координатами.
Какой там кластерный индекс у таблицы, нет автоинкрементного поля?
Если нет, то возможно при этом запросе диск делает намного больше random seek'ов для чтения почти последовательных данных, чем если бы был автоинкремент и возможно стоит его добавить.

 

[fixxxer]

появится using filesort, так как всё равно сортировать надо с учётом времени, а не только даты

А точно надо?

Я бы тогда начал на таблички бить по датам.

 

[aetsyss]

CREATE TABLE tracks4(
org_id SMALLINT(6) UNSIGNED NOT NULL,
obj SMALLINT(6) UNSIGNED NOT NULL,
mydatetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
shir DOUBLE UNSIGNED NOT NULL,
dolg DOUBLE UNSIGNED NOT NULL,
speed SMALLINT(6) UNSIGNED NOT NULL,
zaj CHAR(1) NOT NULL,
batin CHAR(2) NOT NULL,
batout CHAR(2) NOT NULL,
status CHAR(2) NOT NULL,
gsm CHAR(2) NOT NULL,
sensor1 CHAR(2) NOT NULL,
temp CHAR(5) NOT NULL,
canlog_status CHAR(2) NOT NULL,
canlog_security_flags CHAR(2) NOT NULL,
canlog_engine_runtime CHAR(8) NOT NULL,
canlog_mileage CHAR(8) NOT NULL,
canlog_fuel_consumption CHAR(8) NOT NULL,
canlog_fuel_level CHAR(4) NOT NULL,
INDEX IX_tracks4 (mydatetime, org_id, obj),
INDEX IX_tracks42 (org_id, obj, mydatetime)
)
ENGINE = INNODB
AVG_ROW_LENGTH = 106
CHARACTER SET cp1251
COLLATE cp1251_general_ci


автоинкрементного поля нету