Re: Performance tuning MySQL

["Flood Randy Capt AFCA/GCF" <randy.flood@scott.af.mil>]

Hi all, 

It's not that it is slow at writing or reading in general.  It's that if
you have two processes and one of them is writing, and the other it
reading, the one that is reading will have to wait even if it is reading
a record that is not currently being written (if it is reading from the
same table).  So, the only place you really run into trouble is where
you have a situation where you are almost constantly writing.  

But, in an intrusion detection system, you may very well be constantly
writing. 

I worked on a system that logs traffic.  Basically, it summarises TCP
connections into a mysql database, and then generates reports by running
querries on that database.  The current system captures stuff to raw
data files and then sucks those files into a database later. I wanted to
make a (really soft) real-time version of this.  I found some
discussions of Postgress and MySQL that suggested that the table-level
locking of mySQL would make this inefficient compared to Postgress.  I
never implemented the real-time version to find out though.  

It would be good to know if you implement your system with MySQL and it
works well as it would have some bearing on something I want to
eventually tackle.  
      
Randy
        

-----Original Message-----
From: Brune, Charlie [mailto:Charlie.Brune@anheuser-busch.com]
Sent: Monday, September 24, 2001 10:52 AM
To: 'silug-discuss@silug.org'
Subject: Re: Performance tuning MySQL


I have been *amazed* at how quickly MySQL does writes.  Reads are pretty
speedy, too!  8-)

Anyway, I don't know if the latest version of MySQL does row-level
locking,
but if you're writing records guaranteed not to have duplicate keys
(like
date + reallyaccuratetime), they you wouldn't care if you locked
anything
or not.

Just a thought.  Your mileage will vary.  8-)

Regards,
Charlie

-----Original Message-----
From: Flood Randy Capt AFCA/GCF [mailto:randy.flood@scott.af.mil]
Sent: Monday, September 24, 2001 10:42 AM
To: silug-discuss@silug.org
Subject: Re: Performance tuning MySQL



OK.  MySQL is best for when you have infrequent writes but frequent
reads because it does table-level locking (last time I checked, but hey,
mabey they've improved it since then).  This makes sense for most
web-based systems because you have a lot more reads than writes.  

For an intrusion detection system, this might be really bad
performance-wise.  I mean, are you planning on writing an entry for
every packet, or every tcp connection, or what?  If you are writing all
the time, then any process trying to read is going to spend a lot of
time waiting around.  

You might want to consider a different database if you are going to have
really frequent writes.  I believe that Postgress supports record-level
locking.  

I really like MySQL though.

Randy
          

-----Original Message-----
From: Tighe [mailto:emry@accessus.net]
Sent: Monday, September 24, 2001 10:24 AM
To: silug-discuss@silug.org
Subject: Performance tuning MySQL


Hi all,
	I was wondering if anyone had some tips or hints about
performance
tuning a MySQL database.  This is going to be a piece of our IDS system
here in teh office so it looks like it is going to be hit pretty hard.
Is
there anything on the software side that can be done for some
performance
improvements?  Just wondering.

TIghe

-- 
Tighe Schlottog		Sys Admin at large
/emry\"@"/accessus.net\
                             ook ook
"Mr. Wizard, I think I'd rather be a coot than a hacker. Yeah, sure,
every
now and then a giant pink-haired ape would come running after me and 
chase me into the lake, but really, could it be that much worse? I'd
have
a tiny little brain and wouldn't be expected to worry about anything." 
						-jwz from www.jwz.org

-
To unsubscribe, send email to majordomo@silug.org with
"unsubscribe silug-discuss" in the body.
-
To unsubscribe, send email to majordomo@silug.org with
"unsubscribe silug-discuss" in the body.
-
To unsubscribe, send email to majordomo@silug.org with
"unsubscribe silug-discuss" in the body.
-
To unsubscribe, send email to majordomo@silug.org with
"unsubscribe silug-discuss" in the body.