Andy Sharp wrote:
>
> 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."
You've not mentioned your my.cnf settings (which if you've tweaked then
great).
Also are thier concurrent inserts / updates going on ?
Whats "maxing" out mysql / proccessor or Disks ? Would more memory help
(bigger disk cache) ?
Anyone given more info, and a few benchmarks, you may be able to
increase performance - are your my.cnf settings ok ? What else is
running on the box ? Is their a RAID array ? etc ....
However on a side note you have a problem in that with a dynamic site
your performance is ultimatley limited by the ammount of hardware you
can throw at the problem.
What about looking at caching any DB output locally on the webserver -
in 90% of cases like this people claim thier sites are dynamic, yet very
little is realtime, and alot can be cached locally.
Can you cache any data locally, even if the cache is refreshed every
minute or 5 minutes it may save alot of hits on the DB, yet your users
may never know the difference.
Obviously if your site needs to be realtime (games, financial etc ..)
then the above is a load of rubbish and get ready to go buy a bigger
box, or more boxes (and use replication).
Greg
>
> 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
