[ale] Any MySQL Experts around?

[J.M. Taylor]

I would strongly urge you *not* to store your log files in any relational 
database.  There's just no reason to do it, especially for the number of 
rows you're talking about. I've worked on writing reporting solutions for 
huge sets of log data, and I've been a systems admin and DBA (MySQL and 
Informix).  There may be a solution I've not seen but in my experience 
you're not going to get good performance doing what you plan to do.

Storing all your log files on a central server is an excellent idea.  I 
would store them as text files if you can.  What I would suggest for 
better reporting is to decide what needs reporting and write a perl script  
(most efficient afaik for text processing) to parse your files and store only 
*aggregate* data in a database.  If you're storing aggregate data, you 
can afford to store redundant data even and get fast results on 
your queries.  Use the perl script in combination with something like 
logtail (which is a small program that comes with logwatch) to keep track 
of the last time it looked at a file and what line it was on, so you can  
pick up where you left off and not have to parse the whole file again.  

MySQL can certainly handle 100,000+ inserts per day. If you do use a 
database, make absolutely sure you know what you want from your reporting  
and design your data structure for it, and design good queries and make 
full use of indexing or you will be dealing with a horribly slow mess.  
Also, I would suggest using a round-robin approach so that you query 
against only a very small amount of data from your tables holding the full 
log files -- ie, you have say a day's worth of data in your full logs before 
archiving it out to text somewhere.  You store aggregate data in other 
tables where most of the querying is done, and then archive your logs.  
Depending on the database you use you can do this all internally as a row 
is inserted -- use triggers to set up functions that will parse the row on 
insert and go ahead and store the aggregate data.  This will slow down 
your insert but probably not enough that you'll feel it at 100,000 
inserts/day if you've got enough machine.  Older MySQL can't do this, and 
I don't know if the new versions can but I wouldn't trust them. I beleive 
PGSQL can do triggers and functions, and have heard its improved its 
speed.

Regardless of what you do, the more processing you can do before you store 
your data in a database the better off you are for querying.

HTH
jenn



On Sat, 14 Feb 2004, Jonathan Glass wrote:

> I'm thinking of having all my server logs (including NT/2K/2K+3) record
> into a MySQL db for better reporting.  Does anyone know if MySQL can
> handle 100,000+ inserts per day?  Also, if it handling this many inserts,
> how good is the SELECT performance for the automated reporting?  ANy
> thoughts, pointers?
> 
> Thanks!
>