------_=_NextPart_001_01C3584E.5FF65B60
Content-Type: text/plain;
charset="iso-8859-1"
I've gone through the mailing list archives, read the appropriate parts of
the manual and searched the internet and still have questions. What I'm
running into is a serious performance problem with a fairly large (by my
standards) database. My system setup is as follows:
Dual processor Sun E250 w/1.5GB RAM running Solaris 8 (with all current
patches)
18GB dedicated drive/17GB data partition for the database
I'm running MySQL 4.0.12 standard with the Large my.cnf in place. I'm not
running the Huge my.cnf because I'm running other applications on the
server.
Other software running on the server consists of:
Apache
120 iterations of MRTG - one for each of the devices I have to monitor. The
MRTG RRD databases are on a separate drive from MySQL and MRTG doesn't
contribute seriously to the system load.
Various shell scripts and Perl scripts triggered by cron to automate damn
near everything. :)
My db application is a program called RTG - it's a replacement for MRTG that
uses MySQL on the backend - http://rtg.sourceforge.net. BTW, both of these
applications (if you're not familiar with them) are designed to monitor
traffic through routers and switches. MRTG uses a program called RRD (Round
Robin Database) on the backend that does data averaging for any data over 24
hour old. RTG scales better for large networks and doesn't do data
averaging. It keeps live data to the limit of your drive space.
Since starting up RTG not quite 3 months ago, my database has grown to over
10GB in size. If I can get the drive space to keep the 2 years worth of
data I want to, my database will be edging upward toward 100GB. There are
currently 682 tables ranging in size from 2 KB/1 record to around 310
MB/7570511 records depending on the number of ports on a router and how much
traffic flows through it.
I'm running around 2500 queries/minute - 99+% of them inserts. My cpu load
is minimal - around .40 nominally but it may go as high as 1.80 or so when
handling multiple large retrievals. If I look at the iostats output for my
server, the drive controller bandwidth utilization is around 30-40% during
normal operation but immediately jumps to 100% utilization during retrieves.
Retrieval is done via Perl scripts or from Perl/PHP WebPages.
I'm trying to get a RAID array loaded with striped drives to hang off the
server but until I can talk someone into signing the check, I need to do
anything I can to improve performance.
Does anyone have any suggestions? Preferably ones that don't involve money?
If it would help, I _might_ be able to coerce the IT group into giving me
more RAM but an E250 will only hold 2GB RAM and I'm already at 1.5GMB. I've
modified the RTG table structure to use different indexes and modified the
Perl/PHP scripts to suit those changes and gotten significant performance
boosts from that. What I really need are system tweaks and configuration
changes to improve performance.
Jack
Jack Coxen
IP Network Engineer
TelCove
712 North Main Street
Coudersport, PA 16915
814-260-2705
------_=_NextPart_001_01C3584E.5FF65B60--
