How to speed up the execution of SQL queries?

Question

MySQL

Good day.
To begin with, a little background:
There is a task to sniffer traffic, and in real time make a decision about the packet (this protocol is allowed on the network or not). The decision is made on the basis of records in the database (MySQL), a record of the form source ip, destination ip, protocol, destination port.
We use a library in C (nDPI), which sniffers the interface, parses the packet and says what protocol it is, in Python we take data from the console, parse with a regular expression, and put it in the queue for processing.
Further, in another process (multithreading), we knock on the database and look for an entry corresponding to the attributes of the package, further actions are no longer so important.
The bottleneck in processing is mysql, rummaged through the floor of the Internet, but could not find a way to speed up the execution of the request, it doesn’t work out less than 1 ms, this is a lot for our tasks, of course python is also a bottleneck, then we will rewrite it in C ++.
I tried to use redis, but more time is spent on overhead, by the way, the database is not big enough, no more than 1000 records.
Using a local cache is also not a good option, because. they can be written to the database from other places, but the data needs to be up-to-date.
I ask for help, how can I speed up the request, maybe another database or something else?

Answer 1

Using a local cache is also not a good option, because. they can be written to the database from other places, but the data needs to be up-to-date.

Use local cache. The data is obviously not updated every 1ms, for which you are fighting.
Update - all the way from trigger to kick a UDF that tells your application that the data in the database has been updated.

Answer 2

I would like to see an example of a request, maybe there is something to optimize?
Indexes created?
Arrived directly to the repository through the memcached interface?
mysqld_plugin_memcached + disabled transactions.

Answer 3

Tried to keep tables in memory (memory type)?
But most likely you will have to abandon mysql in favor of some fast and non-relational key-value storage, such as memcache.

Answer 4

Do I understand correctly that the database for you is just a repository of settings? ..
Load everything into your memory and put it in a hash table. If speed is especially needed, an ideal hash can be used (i.e., select the parameters of the hash function in such a way that there are no collisions in the table). After that, the need for a queue will also disappear - well, or already filtered packets will fall into the queue.
Update data either periodically by loading it from the database, or by "kick" from the trigger. When updating data, it is best to first build a new hash table, and then replace it atomically - this will allow you to build a new table without a time limit.

Answer 5

You can try this: there is a mysql-master with which users work, and there is a slave, which has tablets with the MEMORY type, and your traffic analyzer uses the slave.
Another hint - you can pre-generate a table, you have a lot of joins there - write a flat table into which all the data will be added according to the trigger on the insert / update. To make the select simple, like
"SELECT rules.rule_id " \
"FROM data_flow_rules_DENORMALIZED rules " \
"WHERE rules.ip_v4_address = '{src_ip_v4}' " \
"AND rules.ip_v4_address = '{dst_ip_v4}' " \
"AND rules. name = '{protocol}' " \
"AND rules.target_port = {target_port} " \
"LIMIT 1"
+ index on all fields, you have a simple equality - it will help. do not forget about index selectivity (the most selective field should be the first in the index)
By the way, if the rules.rule_id field is included last in the index, then the plate will not be accessed at all, only the index :)
Also, discover EXPLAIN https://dev .mysql.com/doc/refman/5.5/en/explain.html