Спроектировать таблицу БД GPS трекера

Question

[enchikiben]

Добрый день!
Подскажите пожалуйста как лучше спроектировать таблицу для хранения координат.
В сутки с одной цели предполагается около 3000 записей (~ 90000 в месяц, ~ 1 мил. год).
Целей может быть много, сейчас все храниться в одной таблице порядка 3 мил. записей, и все очень жутко тормозит (индекс по времени и по цели), сервер VPS c 1 гб. Встала задача улучшить.
Я додумался только хранить для каждой цели координаты в отдельной таблице.
И думаю можно ли разделить дату и время на разные поля?
И подскажите какой размер у типа point? я ни где не нашел.

Подскажите гуру баз данных.

Спасибо.

Answer 1

[Андрей Буров]

Может имеет смысл старые/неактуальные данные пихать в архив(в другую таблицу)?

Comments

[enchikiben]

А какой смысл? Из той таблицы в случае просмотра истории надо тоже будет поднимать данные. Можно конечно разбивать и все такое…

[Андрей Буров]

А историю так часто смотрят?

[enchikiben]

этого сказать не могу…

Answer 2

[anitspam]

все очень жутко тормозит

а вы можете в цифрах привести, что именно тормозит и насколько? если это mysql тормозит, то можно посмотреть в логи, какие именно запросы.
например у меня был запрос с тремя join, выполнялся 5-10 секунд, я разделил его на два запроса и теперь данные выбираются гораздо быстрее (не попадают в mysql-slow.log).

Разбивать данные кстати тоже можно. После сбора статистики о том, какие данные просматривают. Например, за прошлый месяц могут смотреть каждый день, а за прошлый год совсем не смотреть.

Короче, для подсказок хорошо бы знать, что уже есть (дамп таблицы) и что не устраивает (пример запроса и сколько времени он выполняется).

Comments

[enchikiben]

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

[anitspam]

select count(*) from archive_hour;

4025922

select * from archive_hour where device_id = 11 and archive_date >= '2013-01-01' and archive_date <= '2013-02-01';

745 rows in set (0.00 sec)

select * from archive_hour where device_id = 11 and archive_date >= '2012-01-01' and archive_date <= '2013-02-01';

9257 rows in set (0.20 sec)

explain select * from archive_hour where device_id = 11 and archive_date >= '2012-01-01' and archive_date <= '2013-02-01';
+----+-------------+--------------+-------+------------------+---------+---------+------+-------+-------------+
| id | select_type | table        | type  | possible_keys    | key     | key_len | ref  | rows  | Extra       |
+----+-------------+--------------+-------+------------------+---------+---------+------+-------+-------------+
|  1 | SIMPLE      | archive_hour | range | PRIMARY,max_hour | PRIMARY | 12      | NULL | 12121 | Using where |
+----+-------------+--------------+-------+------------------+---------+---------+------+-------+-------------+

show create table archive_hour;
+--------------+--------------------------------------------------------------------------------------------------------
| Table        | Create Table
+--------------+--------------------------------------------------------------------------------------------------------
| archive_hour | CREATE TABLE `archive_hour` (
  `device_id` int(11) NOT NULL,
  `archive_date` datetime NOT NULL,
   blablabla // 20 значений double
  PRIMARY KEY (`device_id`,`archive_date`),
  KEY `max_hour` (`device_id`,`archive_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (MONTH(archive_date))
(PARTITION p01 VALUES LESS THAN (2) ENGINE = InnoDB,
 PARTITION p02 VALUES LESS THAN (3) ENGINE = InnoDB,
...
 PARTITION p12 VALUES LESS THAN (13) ENGINE = InnoDB,
 PARTITION pmaxval VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 

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

[enchikiben]

а у вас какое железо? и нет ли каких нибудь специфических настроек mysql?

[anitspam]

fastvps.ru/vds/ ovz-3
из настроек пожалуй
skip-networking

Answer 3

[rPman]

Помимо партиционирования, я могу посоветовать из разряда 'вредных советов' (но лично так делал для более простых форматов хранения таблиц — myisam, с ними проще восстанавливать базу, вот с innodb я не очень в курсе, как легко будет поднять базу если индексный файл будет потерян)…

Разделите данные и индексы по разным файлам (а точнее физическим разделам, если конечно ваш тарифный план хостинга позволяет это). Индексы обычно занимают меньше места но основные тормоза при записи создают именно они (так как в них происходит перезапись порядка log(n) объемом данных (конечно все сложнее, много читается как из таблицы с данными так и из таблицы с индексами) и эта запись сервером баз данных делается в синхронном режиме.

Само по себе размещение индексов таблиц баз данных на более быстром (те же ssd) носителе заметно ускоряют работу, но я так понимаю это не ваш случай и предлагаю схитрить за счет понижения надежности, отключить синхронизацию записи данных для соответствующих файлов. Сделать это можно, разместив эти файлы на рам-диске (tmpfs) либо отформатировав их раздел под ext4 и примонтировать с опциями -o noatime,async,data=writeback.

Но прежде чем радоваться заметному повышению работу базы данных, рекомендую сначала протестировать и подготовить скрипты поднятия базы данных после нештатного выключения сервера (данные в tmpfs теряются безвозвратно, так же как некоторые данные для разделов, смонтированных с data=writeback). Так как на ненадежном хранилище размещаются не данные а индексы, то их потеря не критична (вопрос только во времени, требуемом для их полной перестройки).

Answer 4

[Николай Турнавиотов]

Как минимум понять размер базы, посчитать память, требуемую для SQL'я и поменять конфигурацию VPSки
ну а дальше — комплексный тюнинг апаратной и программной части сервера

Comments

[enchikiben]

Cейчас таблица с точками 600 мб, у VPS 1 гиг памяти, ubuntu.

[Николай Турнавиотов]

Кхм, как бы так сказать, убунту по дефолту — не очень оптимизирован под такие задачи. я бы посмотрел в сторону как минимум пересобрать ядро там, или поднастроить через sysctl, выключить нафиг всё лишнее и скорее всего пересобрать mysql.
еще кстати можно посмотреть в сторону mongodb

[anitspam]

а вы могли бы посоветовать (или написать :) ) статью, чтобы вы сделали как сисадмин для лучше производительности для стандартных debain squeeze (wheezy), mysql 5.5 (5.6), nginx, php-fpm?
как считаются различные требуемые параметры и как на их основе настраивать систему.

дело в том, что большинство статей в интернетах
1. Как настроить всё по умолчанию.
2. Как настроить высокую производительность (в основе мощное железо и как следствие не особо подходят для начинающих)
3. Настройка какого-либо компонента устаревшей версии.

[Николай Турнавиотов]

anitspam это нереально большой объем работы, поэтому найти полностью и сразу нереально. это как просить специалиста по фундаменту здания, например, рассчитать пропускную способность канализационных труб.

Answer 5

[Alex Kheben]

Возможно я ошибаюсь и результаты тестов, которые я проводил весьма субъективные, НО.
Данные имели журнальный характер и простая выборка из таблицы MyISAM была быстрее, чем в Innodb.
И по формату времени «datetime», я стараюсь использовать везде timestamp. Может вам тоже стоит попробовать потестировать производительность с ним, вдруг будет быстрее?)
И ещё один момент «archive_date >= '2013-01-01' and archive_date <= '2013-02-01'». Вот так «archive_date between '2013-01-01' and '2013-02-01'», на мой взгляд, выглядит как-то кошернее. Но я не супер спец, конечно и поэтому, возможно, в чём-то ошибаюсь.

Comments

[Alex Kheben]

Комментарий для вот этого поста habrahabr.ru/qa/34969/#comment_168047
Случайно пихнул в общую ветку)

[anitspam]

по поводу производительности снова нужно так сказать «мясо» — версии ПО, дамп таблицы, примеры запросов. Вполне возможно, что для mysql 5.1 будут одни результаты, для 5.5 — другие.
datetime использую, потому что с ним удобней смотреть выборку «select * from archive». не совсем представляю, как такую выборку смотреть с timestamp.
и between не используется, потому что запрос строится как

$db->select()
                    ->from(...)
                    ->where('device_id = ?', $device_id)
                    ->where('data_date >= ?', $date1)
                    ->where('data_date <= ?', $date2)
                    ->order('data_date');

[Alex Kheben]

>> datetime использую, потому что с ним удобней смотреть выборку «select * from archive». не совсем представляю, как такую выборку смотреть с timestamp.
Так же.

>> и between не используется, потому что запрос строится как
Ох уж эти ООП маньяки, всё делают только для того, чтобы упростить себе жизнь. ИМХО, собирать запрос самому лучше, т.к. гибкость увеличивается. Тем более когда собираешь запрос самостоятельно можно использовать user defined переменные и т.п.
И где-то на хабре, помню, проскакивала мысль, что лучше использовать select field1,field2,… from table вместо select * from table. Это, конечно, крохи, в производительности с этим может и можно выиграть, но совсем чуть-чуть.