Partitioning visitor logs database I have set up a table that logs every page view a visitor makes. This is going to result in a table with about 50,000 entries per week.

To keep things manageable and performing well I am intending on moving the content of the table to an archive each week or month.

I have a couple of questions, the first is how often would be sensible to move the contents?

Secondly what is the best way to move the contents?

This is a mysql database and the table has 11 columns mostly int or varchar averaging 80 characters.

When it comes to the archive should that be a series of tables or again split but maybe to a lesser degree?
As for how often, I would say that depends entirely on your situation. e.g. How much data do you need to access, how much disk space do you have, how far back will you need to go.

I have a similar process here for similar data, which I partition by month, and then at the start of each month I simply have a scheduled Event that runs and drops the oldest Partition, and creates a new one. This is quick and simple, and keeps the disk space relatively constant. Depending on your current table structure this may or may not be so easy to do.

If you wanted to keep the data, you could simply add a new partititon to the top at the start of the month, creating one huge table that remains searchable, but is held on the system as individual files. If you are using MySQL 5.6 you can even select by partition (a good index also helps).

see How to split a MySQL table file? for a similar discussion.

The other option, if you want to keep things accessible, (which I also do with another system) is to simple run a scheduled event at the start of the month to create a new empty table (CREATE TABLE tablename LIKE..., and rename the old one e.g. tablename_201505. I also mainstain another table to keep track of which months data is in each table for easy refenrence by our applications.