I'm running Snort in conjunction with MySQL for logging, which is generating ENORMOUS datasets (currently the event table is over 2.5 million, I don't know exactly how much because it only goes up to 2.5 million before it clunks out from using too much memory).

Unfortunately, this data is not very useful anymore because I can't pull it out anywhere else (the stored procedure causes the server to crash).

My question is, is there a way to optimize MySQL for these huge datasets or is this beyond the technical capabilities of MySQL and I need to go to something like Oracle, MS SQL, or PostgreSQL?

We have both an Oracle and a MS SQL Server instance but both of these are business critical production servers and it would be very bad news to knock either one of those offline or inhibit their abilities.

Any thoughts on the matter?

  • What version of MySQL? Also, what spec is the server? I have had a shade over 6 million records in MySQL on a desktop class machine (and I will admit to tweaking none of the config) and the performance was alright, considering it was doing lots of other stuff too. Oct 4, 2010 at 20:58
  • Version 5.1 and its on a VM on our ESX cluster. Its no slouch as far as storage (Enterprise iSCSI SAN) and processing power (multiple modern generation Xeons).
    – tearman
    Oct 4, 2010 at 21:02
  • Also, what storage engine are you using? Oct 4, 2010 at 21:02
  • InnoDB is the storage engine
    – tearman
    Oct 4, 2010 at 21:06
  • innodb_buffer_pool, innodb_flush_method, and innodb_flush_log_at_trx_commit would be good places to start.
    – BMDan
    Oct 5, 2010 at 2:10

3 Answers 3


like others say - 2.5M is not a huge number of rows. look at your schema design - can it be that your reporting runs full table scans where indexes can be used [warning: introducing new indexing will decrease insert performance].

did you try to optimize innodb? make sure that at least indexes fit in buffer pool memory. try mysqltuner.pl or if you have more time - dive into mysqlperformanceblog.com.

  • Unfortunately I didn't design the schema and changing it would require a rewrite of the software (barnyard2) so I don't have much control there.
    – tearman
    Oct 5, 2010 at 3:01
  • @tearman you can still add indices without changing the software. it all depends on what sort of queries are run. maybe you can move older data to other table that is less used and just generate daily/weekly reports from it?
    – pQd
    Oct 5, 2010 at 6:41
  • I've changed around how the indices works and added more resources available to the machine, and tweaked some of its parameters as requested. We'll see how it goes.
    – tearman
    Oct 6, 2010 at 21:46
  • Thanks for the mysqltuner.pl suggestion. It helped me figure out which directives I needed to adjust. May 5, 2011 at 20:08

2.5 million records should be no problem. Sharing the schema would help. Also, mysqltuner.pl (mentioned in another answer) will warn you about some my.cnf issues - such as innodb_buffer_pool being smaller than the size of your indexes. Definitely run that. innodb_buffer_pool should be set as high as possible.

If you have any TEXT columns, any queries that involve scanning a lot of rows will perform much better if you move those columns out into a separate table. Even better, use InnoDB plugin, Percona Server, or MariaDB and enable compression for those new text column tables.


Maybe innodb is not the best choice for logs?

I've got a centralized syslog server and it's set up so that every month the data goes to a different/new table and there is a view with all these tables joined. The old logs are then compressed with myisampack so they take up a lot less space, are read quicker and become read-only. It works very fast.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.