MySQL MyISAM Table Locking and Delayed Inserts for Webserver Access Logs

Question

We will install a webserver that writes access logs to a MySQL table. Access logs of course will get really really big, so this will be a separate MySQL instance from the regular data and we will more librally delete the binlogs.

We will also have to delete older items from the table which means delete commands and optimize commands that will make the inserts wait.

Is there a way to have delayed inserts that are in a preserved order and that will occasionally tolerate many items in the delayed insert queue?

If the inserts will get mixed up then it is not a show stopper. We can use timestamps and milliseconds for sorting.

Also, can we prevent wasteful growth of index files? Perhaps we can optimize the indexes similar to optimizing the data files? Would requiring the indexes to be fixed width prevent data rotation waste problems?

Answer 1

FWIW, I think this is the wrong way to do this; unless you need to do real-time reporting on webserver access write the log as a file and bulk-load it.

Having said that, there is a way to do what you want, which if I understand it correctly is to queue up pending inserts while doing maintenance on your database. In other words, decoupling the webserver's INSERT operation from the actual INSERT in the database in some sort of buffer. The way to do it using two instances, one that is directly written to in which you will use the blackhole storage engine, and one that you will replicate to, which will be your "real" database. The pending inserts will live in the binlogs; you simply stop replicating to the slave, perform your maintenance, then resume replication again and the slave/real database catches up. The slave is available for SELECTs for your reporting. You can even multi-master for load balancing, or have multiple slaves and switch between them during maintenance activities.