CODEWITHSUNDEEP
mysqltracking
Alex
Alex

Reputation: 2040

MySQL tracking system

I have to implement a tracking system backed up by a MySQL database. The system will track many apps with at least 5 events tracked for each app (e.g. how many users clicked on link x, how many users visited page y). Some apps will have millions of users so a few thousand updates/second is not a far fetched assumption. Another component of the system will have to compute some statistical info that should be update every minute. The system should also record past values of those statistical values.

The approach a friend of mine suggested was to log every event in a log table and have a cron job that runs every minute and computes the desired info and updates a stats table.

This sounds reasonable to me. Are there better alternatives?

Thanks.

Upvotes: 1

Views: 3095

Answers (3)

Omry Yadan
Omry Yadan

Reputation: 33646

if your hits rate it too high for even insert delated into myisam table to handle, you may want to keep recent hits in memory (memcache can come in handy, or a custom daemon you can write) and process the hits from memory periodically into the database stats table (aggregated).

Upvotes: 1

Alexander Torstling
Alexander Torstling

Reputation: 18898

I would really recommend you to use an already existing log analyzer analyzing the already existing logs from your web server. One example is webalizer. Even better in my opinion is an external system such as google analytics. This works better since it will keep working with intermediate systems such as load balancers and caches in place.

Upvotes: 0

Gattster
Gattster

Reputation: 4781

I've logged to a mysql log table with a cron that crunches it.

I generally use innodb tables in my apps, but for the log table I did it as myisam and used insert DELAYED . . . queries.

Myisam doesn't provide all the goodies of innodb, but I believe it is slightly faster (for that reason).

The main thing you are worried about is database locking when your cron is running, but using "insert delayed" gets around that problem for the most part.

Upvotes: 1

Related Questions