Two ideas that come immediately to mind--
1) Is the web server on the same machine as MySQL?
2) Have you looked at replication? You could have n slaves/webservers that
display data, and all wrtes go back to the master. There's a rather
detailed analysis in the docs that explain how to figure out the type of
benefit you'd get.
----- Original Message -----
From: "Andy Sharp" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, April 04, 2001 1:57 PM
Subject: Speed and Hardware
> Hi,
>
> I'm facing an issue with my primary mySQL server. It powers a web site,
> which loads almost 100% dynamic data. At peak times of the day, it
> seems to me that the Database is becomming maxxed. (Thus bogging the
> site)
>
> I'm aware that there are simply too many variables to completely answer
> this question - number of tables, size of tables, the queries
> themselves, system configuration, type of data, program code, etc.. but
> I'm hoping to get a ball park idea of the limitations of my current
> hardware.
>
> The system is a Pentium 850, 1Gb Ram, Ultra-II SCSI Disks, 256 Mb Cache,
> running FreeBSD 4.1 and the binary copy of Mysql for FreeBSD. (Had some
> unreleated problems with the thread libraries..)
>
> The system averages 550 Queries per second, over the course of a normal
> week. Most of these queries are what I'd describe as "average" joins
> on a table of 5000 records to two tables of about 30,000 records. If
> anything I've overindexed. the database is a total of 100Mb, and the
> system typically has 0 disk usage.
>
> During off peak times I'm getting great performance, Fast -
> responsive. During peak hours, the usage spikes to 700 to 900 Queries
> per second, the DB box doesn't thrash, it just slows down. Perviously
> fast indexed queries start getting logged as slow, and even a lookup by
> primary key id can take 2 or 3 seconds. I suspect the system is simply
> CPU bound.
>
> So, the question is this: Is the above usage, given an "average"
> database, ok code, and optimized queries, considered ok? Or should I be
> looking for some kind of configuration/query problem? Have I simply
> maxxed out my hardware?
>
> I'm sure that further tuning may be able to squeeze more speed out of
> the hardware, but I'm looking for a speed boost of double (new site
> coming on line shortly). Yet before throwing more money on hardware (and
> the time to setup replication), I'd like to get some kind of 3rd party
> information saying "Your system is performing well enough, more tweaks
> will only give you a 10 to 20 percent boost."
>
> For people running largish mySQL servers, what kind of hardware do you
> have to support a volume in the range of 2,000+ queries per second?
>
> Thanks, I appreciate any time taken in answers.
>
> --A
>
> Ps: If I've missed any good references to this in documentation, please
> let me know - I haven't found a good reference for "what is good
> performance" in any searching.
>
> ---------------------------------------------------------------------
> 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
>
---------------------------------------------------------------------
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
