Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
Why do you think I should go through a file before loading into the database? Just so you know, the web server will rely on the database for things like which webapps exist and ACLs and to receive commands for certain maintenance functions. –  George Bailey Feb 2 '11 at 21:23
1  
Just because you state that table locking and index maintenance during normal operations is a concern. This allows the components of your system to play to their strengths; files are good at being append-only, databases are good at bulk-loading (during which time you can do your indexing) then random-access. If real-time access to access logs is not a concern, setting the web server to rotate logs hourly then bulk-loading the previous log is easy to do and light on resources. –  Gaius Feb 2 '11 at 22:35
    
Thank you for pointing this out. It is very interesting, but I am sorry to say that we would probably do much better without that delay. I may use the replication answer as you suggested and I may write to files and have a separate process to transfer to a database, but it would have to be more of a streaming process than a batch process. (for example you can see what tail -f does). We would rotate the files but read them before they are completed and rotated off. Basically read them as they are growing and when a new file is added, start reading that one and eventually delete the other one. –  George Bailey Feb 4 '11 at 21:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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