I log all page requests to a rotating table name based on day of
week. Logging image requests is probably a bridge too far. This
gives me 7 days grace to archive off old tables to an analysis
database.
Originally it was done to one table, but this idea didnt work too
well because a purge based on an index took way too long, plus
would leave a huge hole and an optimization job, and using
'delete from table' is not easily coordinated with any kind of
archive operation without losing records.
As you can imagine, if the table is very big, and has no index,
deleting a row would be extremely slow. I'd use a 'deleted' flag
instead... (why would you want to delete log entries?) an update
should be fast. of course you need to index based on a unique
auto_increment column.
To do anything really useful with log data of millions of rows
usually requires reading all of it via perl, perhaps each day
(table) at a time, and using hashes to accumulate summary info.
This I find is more flexible than struggling with complex selects
and indexes, plus you can use limit on the select to quickly
get the program right before pouring all the data through it.
-Justin
On Mon, Mar 12, 2001 at 02:16:10PM +1030, Timothy Aslat wrote:
> Hi All,
>
>
> I've been lurking around on this list for a few days, and searching archives and
> darn near anything else I can get my hands on. What I'm curious to find out is,
> how many people are using MySQL for logging Squid/Aapache (more Squid) and how
> did you go about doing it.
>
> I've already implimented a simple system using a perl script and the File::Tail
> module to dump the access logs into a MySQL database, and Apache/PHP to
> edit/modify/query/update it.
>
> What I've discovered is that a select query is extremely fast, but a delete
> query takes literally forever, as I haven't been able to get one to complete
> within several hours (actually left over a weekend).
>
> The system is a Celeron 600 with 512Mb ram and SCSI hard drives which I thought
> would be ample for this application, running FreeBSD 4.1 RELEASE and the version
> of MySQL is 3.23.30-gamma
>
> I'm suspecting that I should be upgrading this to 4.2 STABLE and MySQL 3.23.33
> or later, but will it actually help in this instance?
>
> Thanks in Advance
>
> Tim
>
>
> --
> | Disclaimer: | Timothy Aslat [EMAIL PROTECTED] |
> | The sender of this email is a figment | http://www.goodiesruleok.com |
> | of a deranged imagination and leaves it| Spyderweb Consulting |
> | to your own devices to decide whose... | P: 82270800 M: 0401088479 |
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--
Justin Beech http://www.dslreports.com
Phone:212-269-7052 x252 FAX inbox: 212-937-3800
mailto:[EMAIL PROTECTED] --- http://dslreports.com/contacts
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
