Using DELAY_KEY_WRITE in MySQL: Should I use it? Is there anything else I need to change?

Question

I have a logging table that stores the user id, date/time, table name, record id, query type (insert, update, delete, etc), and the full SQL that was run. There are a couple keys on this table, but they don't need to be updated right away. Typically the table is used to eitehr check for problems (someone made a mistake or there was a system error) or for people to review changes people made. But usually these are not done immediately after a save or they are only done once a week or once a day.

Is turning on DELAY_KEY_WRITE on a table like this would be useful?

Is there anything else that I need to enable on MySQL or within or add to my code? I was reading here that you need to use the startup parameter --myisam-recover. Is this the case?

Answer 1

Possibly, but why? If the entries here are generated by people typing (as it appears) there's no way you're going to overload a machine made after 1985 or so, unless you're using a netbook or your cellphone as the database server.

If you are tight enough on performance that you are looking at doing things like this, I'd suggest you first do some profiling to see were the problems actually are. If you aren't worrying about performance, what's the motivation for looking into DELAY_KEY_WRITE?

A few more thoughts in response to your comments:

• 1000s of records a minute is on the order of millions of records a day. How are people going to "review" them?
• That's on the order of gigabytes a month. What are your retention policies going to be?
• Does the log actually need to be indexed, or even stored in a table? Might the system logging facilities be more appropriate?
• Would logging the information in separate places (a debug log, a review queue, a summary table, etc.) help?
• It's unlikely that your system will actually be producing meaningful log information at this rate. How realistic are your load estimates, and how much of this is going to to be redundant / useless?