Добрый день!

Есть БД информации о сетевых пакетах:
+-------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+----------------+
| ID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| in_port | smallint(5) unsigned | YES | MUL| NULL | |
| dl_src | bigint(20) unsigned | YES | | NULL | |
| dl_dst | bigint(20) unsigned | YES | | NULL | |
| dl_vlan | smallint(5) unsigned | YES | | NULL | |
| dl_vlan_pcp | tinyint(3) unsigned | YES | | NULL | |
| dl_type | smallint(5) unsigned | YES | | NULL | |
| nw_tos | tinyint(3) unsigned | YES | | NULL | |
| nw_proto | tinyint(3) unsigned | YES | | NULL | |
| nw_src | int(10) unsigned | YES | | NULL | |
| nw_dst | int(10) unsigned | YES | | NULL | |
| tp_src | smallint(5) unsigned | YES | | NULL | |
| tp_dst | smallint(5) unsigned | YES | | NULL | |
+-------------+----------------------+------+-----+---------+----------------+

В базу постоянно заносятся/считываются записи, причем большой блок для записи накопить нельзя - реалтайм.

Есть индекс по всем полям.

Запросы идут вида:
SELECT ID FROM Table WHERE in_port=1 AND dl_src=100500 AND ...;
INSERT INTO Table (in_port, dl_src, ...) VALUES (1, 100500, ...);
Осуществляет их единственный клиент.

Проблема:
Даже на пустой базе выполнение каждого из запросов длится 0.3-0.5мс.

Система:
Ubuntu 12.04, Core i5-460M, 4GB RAM, HDD.

Вопрос:
Реально ли на MySQL-базе добиться производительности хотя бы в 0.1мс на запрос, и если да, то как?

0.
mysql> show index from FlowMatch;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| FlowMatch | 0 | PRIMARY | 1 | ID | A | 10474 | NULL | NULL | | BTREE | | |
| FlowMatch | 0 | flow | 1 | in_port | A | NULL | NULL | NULL | YES | BTREE | | |
| FlowMatch | 0 | flow | 2 | dl_src | A | NULL | NULL | NULL | YES | BTREE | | |
| FlowMatch | 0 | flow | 3 | dl_dst | A | NULL | NULL | NULL | YES | BTREE | | |
| FlowMatch | 0 | flow | 4 | dl_vlan | A | NULL | NULL | NULL | YES | BTREE | | |
| FlowMatch | 0 | flow | 5 | dl_vlan_pcp | A | NULL | NULL | NULL | YES | BTREE | | |
| FlowMatch | 0 | flow | 6 | dl_type | A | NULL | NULL | NULL | YES | BTREE | | |
| FlowMatch | 0 | flow | 7 | nw_tos | A | NULL | NULL | NULL | YES | BTREE | | |
| FlowMatch | 0 | flow | 8 | nw_proto | A | NULL | NULL | NULL | YES | BTREE | | |
| FlowMatch | 0 | flow | 9 | nw_src | A | NULL | NULL | NULL | YES | BTREE | | |
| FlowMatch | 0 | flow | 10 | nw_dst | A | NULL | NULL | NULL | YES | BTREE | | |
| FlowMatch | 0 | flow | 11 | tp_src | A | NULL | NULL | NULL | YES | BTREE | | |
| FlowMatch | 0 | flow | 12 | tp_dst | A | NULL | NULL | NULL | YES | BTREE | | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
14 rows in set (0.00 sec)

1.
SELECT ID FROM FlowMatch WHERE in_port <=> 16 AND dl_src <=> 28784195780549 AND dl_dst <=> 248752301972048 AND dl_vlan <=> 65535 AND dl_vlan_pcp <=> NULL AND dl_type <=> 2048 AND nw_tos <=> 0 AND nw_proto <=> 1 AND nw_src <=> 167772176 AND nw_dst <=> 167772163 AND tp_src <=> 0 AND tp_dst <=> 0 LIMIT 1;

INSERT INTO FlowMatch (in_port, dl_src, dl_dst, dl_vlan, dl_vlan_pcp, dl_type, nw_tos, nw_proto, nw_src, nw_dst, tp_src, tp_dst) VALUES (16, 28784195780549, 248752301972048, 65535, NULL, 2048, 0, 1, 167772176, 167772163, 0, 0); SELECT LAST_INSERT_ID();



explain SELECT ID FROM FlowMatch WHERE in_port <=> 16 AND dl_src <=> 28784195780549 AND dl_dst <=> 248752301972048 AND dl_vlan <=> 65535 AND dl_vlan_pcp <=> NULL AND dl_type <=> 2048 AND nw_tos <=> 0 AND nw_proto <=> 1 AND nw_src <=> 167772176 AND nw_dst <=> 167772163 AND tp_src <=> 0 AND tp_dst <=> 0 LIMIT 1;
+----+-------------+-----------+------+---------------+------+---------+-------------------------------------------------------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+-------------------------------------------------------------------------------+------+-------------+
| 1 | SIMPLE | FlowMatch | ref | flow | flow | 53 | const,const,const,const,const,const,const,const,const,const,const,const | 1 | Using where |
+----+-------------+-----------+------+---------------+------+---------+-------------------------------------------------------------------------------+------+-------------+

//INSERT
mysql> show profile for query 37;
+------------------------------+----------+
| Status | Duration |
+------------------------------+----------+
| starting | 0.000107 |
| checking permissions | 0.000014 |
| Opening tables | 0.000030 |
| System lock | 0.000014 |
| init | 0.000032 |
| update | 0.000094 |
| Waiting for query cache lock | 0.000008 |
| update | 0.000019 |
| end | 0.000007 |
| query end | 0.000007 |
| closing tables | 0.000033 |
| freeing items | 0.000024 |
| logging slow query | 0.000012 |
| cleaning up | 0.000008 |
+------------------------------+----------+
Total: 0.0004

//SELECT
mysql> show profile for query 39;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000047 |
| Waiting for query cache lock | 0.000013 |
| checking query cache for query | 0.000163 |
| checking permissions | 0.000024 |
| Opening tables | 0.000040 |
| System lock | 0.000024 |
| Waiting for query cache lock | 0.000034 |
| init | 0.000081 |
| optimizing | 0.000046 |
| statistics | 0.000178 |
| preparing | 0.000033 |
| executing | 0.000009 |
| Sending data | 0.000074 |
| end | 0.000012 |
| query end | 0.000009 |
| closing tables | 0.000013 |
| freeing items | 0.000017 |
| Waiting for query cache lock | 0.000007 |
| freeing items | 0.000019 |
| Waiting for query cache lock | 0.000020 |
| freeing items | 0.000008 |
| storing result in query cache | 0.000009 |
| logging slow query | 0.000007 |
| cleaning up | 0.000009 |
+--------------------------------+----------+
Total: 0.00089

3.
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.34-0ubuntu0.12.04.1
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 16)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 3

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 7h 19m 39s (14M q [546.619 qps], 242 conn, TX: 1B, RX: 2B)
[--] Reads / Writes: 68% / 32%
[--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 597.8M (16% of installed RAM)
[OK] Slow queries: 0% (2/14M)
[OK] Highest usage of available connections: 6% (10/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/1.7G
[OK] Key buffer hit rate: 99.8% (180M cached / 317K reads)
[!!] Query cache efficiency: 7.5% (612K cached / 8M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts)
[OK] Temporary tables created on disk: 25% (13K on disk / 53K total)
[OK] Thread cache hit rate: 95% (10 created / 242 connections)
[OK] Table cache hit rate: 20% (57 open / 284 opened)
[OK] Open file limit used: 7% (80/1K)
[OK] Table locks acquired immediately: 99% (11M immediate / 11M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Variables to adjust:
query_cache_limit (> 1M, or use smaller result sets)

ScareCrow,

С InnoDB выходит еще хуже. Я работаю с БД из-под питона, для корректного сохранения мне приходится после каждого INSERT ставить COMMIT. В результате, каждый INSERT выполняется 5мс.

LIMIT Работает корректно, поскольку у меня каждая запись уникальна. Или дело не в этом?


miksoft,

Увеличение размера key buffer не дало прироста. Я специально почистил таблицу, чтобы была максимальная скорость, поэтому пока все умещается.

P.S. С autocommit'ом скорость та же.

miksoftТ.е. индекс по таблице гарантированно умещается в 16 Мбайт, а другие таблицы в это время не используются?

Кстати, почему поле ID не включено в индекс, в который входят остальные поля?

И еще - вы пробовали пересмотреть перечень/типы/размеры полей? Нужни ли они все? Реально ли нужны bigint поля? Почему дублируются поля *_src и *_dst ?

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

Для InnoDB, кстати, тоже innodb_buffer_pool_size надо проверить и, скорее всего, увеличить. В нем кэшируются и индексы, и таблицы InnoDB.

Да, но на всякий случай я увеличил размер памяти под индексы.

Потому что оно не часть индекса.
Юзкейс следующий:
Сохранить информацию о пакете;
Добыть ID по информации;

Затем этот ID используется как внешний ключ для выборки из других таблиц. Вроде:
SELECT * FROM OtherTable WHERE match_ID = (SELECT ID FROM FlowMatch WHERE ...);

Да, MAC-адрес менее чем в BIGINT не запихнешь. *_src и *_dst - Адреса источника и назначения.

Я пытался уменьшить индекс, но большого профита это не принесло. Вроде, в районе 10-20%.

mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+

miksoftLantameSELECT * FROM OtherTable WHERE match_ID = (SELECT ID FROM FlowMatch WHERE ...);В такой схеме до тех пор, пока таблица MyISAM и поле ID не входит в основной индекс, придется лазить в содержимое таблицы. А с учетом того, что таблицы в MyISAM почти не кэшируются, это физическое чтение с диска, т.е. 15-20 мс как минимум.

Поясните, я не понял.
Ведь если я включу ID в индекс, то выборка по (in_port, dl_src, ...) не будет использовать индекс.

Очень странные результаты выходят.
При удалении/уменьшении размера индекса и SELECT и INSERT выполняются дольше.
Добавление ID в индекс стабилизирует время INSERT'a.
Выходит где-то 0.5мс на запрос в пустой таблице.
В полной (~10M) SELECT за столько же, INSERT деградирует до 0.8мс примерно.
Отличия MyISAM от InnoDB в пределах погрешности.