Get the latest security news in your inbox.

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 ?

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:

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:

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.