Re: aging method

I'll try to answer both of your posts in this one.  

We run mod_log_sql in our production environment so my recommendations are based on experiences we have had dealing with just under 2 million hits a day being recorded to a single database.

1) For all of this I am assuming that you are using MySQL as a database back end.

2) Use one single access_log table.  If you ever plan on getting over 100,000 hits a day I would say it's manditory that you use InnoDB due to table locking issues.  If you use MyISAM and multiple tables you will experience lots and lots of overhead as MySQL opens and closes file descriptors.  Even if you set the number of allowed file descriptors in MySQL to a high value you will eventually exceed this limit as you add more hosts.  I think InnoDB may even suffer from this in a slightly diminished capacity.

3) Keep about 5 days worth of data in your main access_log table.  This will cover long weekends with a little room to spare.

4) Archive off the data from your main access_log to able to something like access_log_20030716.  This table should be a MyISAM format table.  We do this from 00:00:00  to 23:59:59 GMT for each day.  Once you have determined that all of the data from a specific day has been inserted into it's table then run myisampack on the the MyISAM table you just finished creating. (Please refer to the mysql.com site for this as it's fairly complicated to get correct on a Linux system due to some lack of file locking on that environment).  We also run a cron job that archives off the data to the access_log_DATE once a minute.  Be sure you have some scheme of making sure that the web servers have written all of their data to the database server before doing this.  We have a scheme involving a seperate table that we keep track of time stamps for different web servers and their last updates.

Archiving is as simple as:
"CREATE TABLE acces_log_DATE blah blah... TYPE=MyISAM" once a day

and from cron every minute (with appropriate checks to make sure you have all of the data):
INSERT INTO TABLE access_log_DATE SELECT * FROM access_log WHERE time_stamp < some_time AND time_stamp > some_time - 60;


5) When deleting from the database delete in blocks of like 5000 rows at a time.  This will minimized the impact of failed transactions killing the IO on the machine.

6) Do not have mod_log_sql write directly to the database.  In stead give mod_log_sql fake credentials for logging into the database server, and have it log to /tmp/sql_preserve instead.  Because of how mod_log_sql works (one connection per httpd child) you can quickly exhaust resources on your DB server by allowing that many connections at once.  We had ~300 open connections at any given time, and exceeded our 1000 connection limit a few times in production.

Our solution to this was to write a python program that renamed /tmp/sql_preserve once a minute and then executed each line in that file writing it to the database.  This made one connection per web server to the database server.  To avoid having to parse the sql_preserve log in order to find the last time_stamp to update our "up to date" table (step 4 - archiving) just use the timestamp from the /tmp/sql-preserve file -- should be within 1 second.  

Another reason for doing this was that when writing directly to the database server we encountered a hardware failure that made MySQL connections hang thus hanging mod_log_sql causing apache to wig out and our load balancer dropped each web server from the pool causing our entire datacenter to effectively be down.  By logging to /tmp/sql_preserve and having a seperate not critical process do the logging for us we can prevent this from happening again.

7)  We also allow our customers to download their web logs.  Do not write directly from the database to the browser.  You will time out when you exceed X number of rows in your table.  Instead we implemented a queue system where our customers could request logs for a given day and then a seperate process would create them in the back ground and e-mail the customer when the logs were ready for download.

8) When you start to run out of disk space you can just copy of old access_log_DATE tables off onto CD or just delete them as needed.  The great thing about myisampacked tables is that they are read only and they also consist of just 3 files thus making them easy to archive on CD if they don't get too large.



On 2003.07.16 08:55 Paul Chvostek wrote:
> And on the topic of aging, what are some strategies for this?
> 
> When storing 'combined' to a file with CustomLog, I run a script that
> (daily, weekly or monthly depending on site traffic) renames the log
> file using standard syslog naming and compresses it with bzip2.  With
> this much compression, I don't have a problem storing scads of old data
> (in case the user decides to switch log analysis programs), but I will
> obviously not be able to bzip2 mysql table records.
> 
> Is the solution simply to buy a bigger hard disk, or have folks
> developed strategies for moving less-frequently-used to elsewhere?
> 
> (I could always just take old data, save it to a file in 'combined'
> format and bzip2 it, but ... well, maybe that's the solution.)
> 
> -- 
>   Paul Chvostek                                             <[email protected]>
>   Operations / Abuse / Whatever
>   it.canada, hosting and development                   http://www.it.ca/
> 
> __________________________________________________________________
> Reminder: to unsubscribe, send email to <[email protected]>
> with the words "unsubscribe mod_log_sql" in the body (w/o quotes).
> The module homepage is http://www.grubbybaby.com/mod_log_sql/
> 
__________________________________________________________________
Reminder: to unsubscribe, send email to <[email protected]>
with the words "unsubscribe mod_log_sql" in the body (w/o quotes).
The module homepage is http://www.grubbybaby.com/mod_log_sql/