I’d like to share my first actual success on mysql tuning, after having spent a couple of days reading everything I could about the matter (and still waiting for the books to arrive).
From what I’ve seen a very important point on DB optimization is the right table design, followed by the right queries and finally optimizing DB parameters. Since I don’t know enough yet about optimal DB design I’ll skip that phase (tho I’ll definetively accomplish it during the next weeks/months) and examining some queries.
After enabling log_slow_queries, one of the first queries popping out continuously was the following:
SELECT *, inet_ntoa(src_ip) as aux_src_ip, inet_ntoa(dst_ip) as aux_dst_ip FROM event_tmp order by id desc limit 1;
Ugly, ain’t it ?
BREAK
A little bit of explanation about the event_tmp table and how we use it may come handy to understand this.
Introduction
After stumbling across Digg spy some time ago, it seemed like a nifty feature to add to ossim. A real time event viewer. So that’s what we started to do.
Shortly after starting we already had performance issues, since basically we had to aggregate lots of information from many unrelated tables, and do this every second. So we wrote a cache table:
And this it how it looks like:
(image removed, broken link, I'm very sorry. DK.)
mysql> desc event_tmp;
+-----------------+------------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+-------------------+-------+
| id | bigint(20) | NO | PRI | | |
| timestamp | timestamp | NO | | CURRENT_TIMESTAMP | |
| sensor | text | NO | | | |
| interface | text | NO | | | |
| type | int(11) | NO | | | |
| plugin_id | int(11) | NO | | | |
| plugin_sid | int(11) | NO | | | |
| plugin_sid_name | varchar(255) | YES | | NULL | |
| protocol | int(11) | YES | | NULL | |
| src_ip | int(10) unsigned | YES | | NULL | |
| dst_ip | int(10) unsigned | YES | | NULL | |
| src_port | int(11) | YES | | NULL | |
| dst_port | int(11) | YES | | NULL | |
| priority | int(11) | YES | | 1 | |
| reliability | int(11) | YES | | 1 | |
| asset_src | int(11) | YES | | 1 | |
| asset_dst | int(11) | YES | | 1 | |
| risk_a | int(11) | YES | | 1 | |
| risk_c | int(11) | YES | | 1 | |
| alarm | tinyint(4) | YES | | 1 | |
| filename | varchar(255) | YES | | NULL | |
| username | varchar(255) | YES | | NULL | |
| password | varchar(255) | YES | | NULL | |
| userdata1 | varchar(255) | YES | | NULL | |
| userdata2 | varchar(255) | YES | | NULL | |
| userdata3 | varchar(255) | YES | | NULL | |
| userdata4 | varchar(255) | YES | | NULL | |
| userdata5 | varchar(255) | YES | | NULL | |
| userdata6 | varchar(255) | YES | | NULL | |
| userdata7 | varchar(255) | YES | | NULL | |
| userdata8 | varchar(255) | YES | | NULL | |
| userdata9 | varchar(255) | YES | | NULL | |
+-----------------+------------------+------+-----+-------------------+-------+
This table would have all the needed information so we could write a nice scrolling real time event viewer.
The actual implementation is like a ring buffer, you specify how many events you want to keep in that table at max and the server will take care that the table doesn’t get too big. (10000 being the default).
The code
Getting back to the previous query, it was our quick & dirty attempt at getting the last row out of that table. Let’s see what it actually does:
mysql> explain SELECT *, inet_ntoa(src_ip) as aux_src_ip, inet_ntoa(dst_ip) as aux_dst_ip FROM event_tmp order by id desc limit 1;
+----+-------------+-----------+-------+---------------+---------+---------+------+-------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+------+-------+-------+
| 1 | SIMPLE | event_tmp | index | NULL | PRIMARY | 8 | NULL | 10001 | |
+----+-------------+-----------+-------+---------------+---------+---------+------+-------+-------+
1 row in set (0.00 sec)
Well, that’s pretty bad. Having to traverse 10000 rows in order to get one out of it ? what if our tmp table had millions ? no good.
So, rethinking this, since id is autoincremental anyway, we could just fetch the highest one…
mysql> explain select max(id) from event_tmp;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)
Much better. No single row needs to be accessed since we just use the index/key column. So let’s finish up the query simulating the original one:
mysql> explain select *, inet_ntoa(src_ip) as aux_src_ip, inet_ntoa(dst_ip) as aux_dst_ip from event_tmp where id = (select max(id) from event_tmp);
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+------------------------------+
| 1 | PRIMARY | event_tmp | const | PRIMARY | PRIMARY | 8 | const | 1 | |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+------------------------------+
2 rows in set (0.00 sec)
mysql>
Fantastic, now we only need to query 1 row, regardless of how many tmp rows we might have in there. We could easily remove the limiting code from the server and just get a cleanup process chop the table every once in a while.
Conclusion
If you’re an SQL expert you might not be impressed by this at all, but for me who I’m just taking my first steps deeper into all of this it’s been a nice feeling of accomplishment, and an extra motivation push for further delving into this matter.
BTW, I’m trying out the MySQL Enterprise Dashboard and am seriously considering buying support from them next year, it’s been very helpful so far. I’d like to post a screenshot but I don’t think I’m allowed by the EULA I (of course) haven’t read, so check out the following link: Automating MySQL best practices management.
