Storage Engine for Logging

[aunquarra]

I'm about to produce a pretty extensive set of log tables on a project, and I'm evaluating what storage engine to use.

 

My gut instinct is InnoDB. I don't need transactions, but the row-level locking makes me feel more comfortable in an INSERT-heavy scenario like logging. That said, these won't be set-and-forget logs; I'll be querying them regularly. So the performance of MyISAM comes to mind...

 

Anyway, I did some Googling and found some folks recommending Archive. On the surface, it looks like a good fit. Row-level locking; INSERT and SELECT only (no need to UPDATE or DELETE logs); compression... So far, so good. Looks like I might be playing with a new engine, which is fun for me (for some reason).

 

Then I see that it doesn't support indexes and I flinch.  :-\

 

I read that it performs well with SELECTs pertaining to timestamps, which is great. Undoubtedly, I'll be querying based on timestamps regularly. But I'll also have a user_id column and other similar relationally relevant IDs that I'll want to query based on just as often (perhaps more often).

 

I'm imagining querying a massive MyISAM or InnoDB table with no indexes and it's a scary prospect. I would hope that Archive might have features to combat that, but I can't find any examples. It almost seems like Archive is great for logs if you're doing overall log analysis, but not so much for granular analysis.

 

So, all that said, what storage engine would you recommend for logs that will be SELECTed regularly knowing that SELECTs will likely fall into one of the following scenarios:

 

• by timestamp
  
• full-table query
  
• per user analysis (SUM() of certain columns where user_id='123')
  
• per event (I'll check the credit_card_transactions_log table by transaction_id, for example)
  

[fenway]

Well, short of crash recovery, you'll get no benefit from InnoDB here -- even with table-level locks, you have concurrent insert with MyISAM.  And INSERT DELAYED can help you here, too.

 

No, nothing you can do about indexes with ARCHIVE.

 

Other options include using compressed MyISAM and MERGE all of the tables -- keep the most recent one uncompressed so it's not read-only.  Sort of the best of both worlds.

 

Partitioning is technically an option, e.g. on user_id, but that's not pretty.