Mysql log rollover / replication

[eluser]Eric Brown[/eluser]
I have a stats app on a LAMP setup. I'm writing all of the requests to a table "request_log". Nightly, I copy the prior day's data to a temp table and then run my reporting aggregations.

Clearly this was ok at the beginning, but now I am approaching 10 million rows from my tests and when I am fully live, I expect about 10 million rows per day, not per month.

Any suggestions for either rolling over the data daily or making this more efficient so I can scale?

One thing that I would like to do is implement mysql replication so that the aggregations take place on the slave server and don't mess up the live queries coming in. I've read about setting this up including to set one server up with odd auto incremented keys and the other even so that I don't have duplicate ids should there be a failover. Any thoughts on how to do this best too?

Mysql is out of date and I need upgrade so I have yet another reason for needing a master/master setup.

[eluser]michalsn[/eluser]
Consider use of MongoDB. Here is a nice example: http://codesanity.net/2010/05/mongodb-codeigniter-logs/

[eluser]Eric Brown[/eluser]
Thanks for the suggestion. Changing our architecture isn't possible right now, although a future consideration.