MySQL or flat file for user logs

I'm already using MySQL for most of my site but I just need a simple, non-dynamic log of every user's page visits while logged in to my site. My main concerns are disk space, the potential impact on server speed and the speed of other MySQL requests and the increased load on the processor.

I haven't tried to do this either way but read a pretty comprehensive tutorial on a flat file log system. These logs could just be downloaded on to my hard drive every 3-4 days and stored as reference in case something goes on later on, so disk space is probably of secondary concern. Again, potential impacts on disk speed are important. Any suggestions?

re: MySQL or flat file for user logs

I would think that using MySQL would be easier on the system. Especially if you already have a connection open. It is designed for optimal performance.
Just remember to use MyISAM tables for your logs, as they are considerably faster that InnoDB tables.

Note, that I may be horribly wrong here, I have never tested this. This is just my best guess.

re: MySQL or flat file for user logs

Do you know what phpmyadmin sets up by default? I would have thought that having the increased load of queries would slow down the database and possibly affect the other queries that need to be run.

re: MySQL or flat file for user logs

I don't know about phpMyAdmin, I've never really used it.
This will of course slow down the database as the log queries will eat up resources that would otherwise be used for the other queries.
But so will writing them to the file system. My guess is that messing will the file system will eat up more resources, because, as I say, MySQL is designed for optimal performance.

re: MySQL or flat file for user logs

Alright, MySQL it is then. Thanks. If anyone else has any input, I'd be more than happy to hear it!

re: MySQL or flat file for user logs

I am logging users' hits in MySQL. The average hits are 50K/hr. I am using InnoDB to logging so that I can easily query the table as well.

What you are going to save?

re: MySQL or flat file for user logs

Hi,

if you're logging into a SQL database, just keep the log data away from other tables. As long as there's no action over tables of your portal that's affecting your log table(s) by sql join etc., the performance should be fine.

Furthermore, you could use different tables, a new one per day, rotate them, make an off-load of yestardays data over night, purge old data/tables when unneeded.

Don't know if there's such in MySQL, but DB2 has a parameter "APPEND" to tell a table always to write to its end and not to look for free space in between old rows. This speeds up also.

Regards,

Bernd