6

Is there any technique/advice in order to optimize the performance of the queries below?

According to my needs, my average db size will be approximately 30mill. rows per day, so every second less, will make huge difference.

My DB engine is innoDB and I'm using 1 core CPU, with 2GB RAM.

Exec Time : 7,5 sec (5,5 mil. rows)

SELECT vpn_group, username, from_interface_addr_ip
FROM SystemEventsR
WHERE (timestamp > ( NOW( ) - INTERVAL 10 MINUTE ) AND 
SysLogTagflag=1 AND 
username !='')
GROUP BY username

Exec Time : 88,4 sec (5,7 mil. rows)

SELECT syslogtagid, DeviceReportedTime, class, definition, SystemEventsR.SysLogTag,
COUNT(id) AS Records, 
explanation, action 
FROM SystemEventsR,oa_syslogtags
WHERE  (SystemEventsR.SysLogTag = oa_syslogtags.syslogtag AND 
flagid = 1 AND DATE(timestamp) = DATE(NOW()))
GROUP BY SystemEventsR.SysLogTag 
ORDER BY Records DESC

Any help would be appreciated.

2
  • 4
    I think the long and short of it is going to be "have the right indexes" -- ensure your tables are indexed on everything you group, order or search based on. Jun 19, 2013 at 21:29
  • 5
    30M rows/day? Are you sure? This will give you 1 billion rows in a single month. You'll need much more hardware resources for sure, even with the right indexes.
    – setsuna
    Jun 20, 2013 at 18:42

2 Answers 2

4

There are useful tips in the mysql online documentation:

http://dev.mysql.com/doc/refman/5.7/en/optimization.html

It's worth reading about indexing, like James Green has suggested.

Also you may wish to use the keyword "EXPLAIN" to look at the query plan to find potential bottlenecks. This will highlight which indexes are being used in your query. To make sure you're query plan is correct you will need to keep updating index statistics on your database.

Hope this helps and let me know if you find anything!

Many Thanks,

Simon.

1
  • Good call on EXPLAIN, I always forget that Jun 19, 2013 at 22:45
2

Your queries are only on the latest data. You may try horizontal partitioning. It is supported in MySQL 5.1.

Another way is to maintain a "horizontal partitioning" manually. For example, you may create a table "SystemEventsR_archive", and run some queries as below every day.

REPLACE INTO SystemEventsR_archive SELECT * FROM SystemEventsR;
DELETE FROM SystemEventsR WHERE timestamp < ( NOW() - INTERVAL 1 DAY );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.