On Tue, Sep 11, 2001 at 03:17:47PM -0500, Weslee Bilodeau wrote:
> > > They average around 500 connections/second at any given time, two
> > > have a master/slave setup.
> > Connections/sec or Queries/sec? That's a lot of connections per
> > second? Can you use persistent connections? It would save a lot of
> > overhead.
> Half and half, at the moment .. We had a problem that when we
> connected to multiple database servers from PHP using persistant
> connections, it would get confused. By confused I meant 1.) It would
> 'leak' MySQL connections. Somehow it 'forgot' it had an open
> connection. 2.) It would pick the wrong server. Each server has
> specific information on it, in seperate database/tables. The
> persistant connection code would actually hand back the wrong
> database handle and the queries would fail. We had to disable
> persistant connections because of this.
Ouch. I hadn't heard about those problems before.
It's probably worth testing again, 'cause it will give you a nice
boost--especially at that connection rate.
> Our current my.cnf file:
> set-variable = key_buffer=384M
> set-variable = max_allowed_packet=1M
> set-variable = table_cache=400
> set-variable = sort_buffer=10M
> set-variable = record_buffer=10M
> set-variable = thread_cache=8
You might increase the thread_cache quite a bit. It'll bypass the
need to create/destroy threads all the time. It may not give you a
big improvement, but it can't hurt.
> The reason I'm asking is because of the MySQL page on Linux-specific
> notes. Which basically said you can have lots of threads, providing
> your key-cache is low.
Wonder what that really means... or at least what the rationale behind
Have you experimented much with they key_buffer? Any noticeable
difference if you double it or cut it in half?
> I'm trying to get as many threads as I can , and get MySQL to be
> ram-happy for speed as well.
A good plan. ;-)
> > > Any changes for this many connections/ram that would be suggested?
> > How are things looking in SHOW STATUS? Any red flags that you've
> > noticed? Any slow queries?
> Slow queries are actually watched with a fine-tooth comb. I catch
> anything slow, I first smack the programmer along side the head,
> then throw Paul's book at them. I attempt to optimize it where
> possible, or just drop thier table and force them to do it over
Hahhahahah... Well, that's good to hear, I suppose.
> The queries themselves seem to be pretty well optimized, I just
> wanna make sure the config I've got can scale. :)
I know the feeling.
> I'm used to servers with a few hundered connections, with 512 MB of
> ram .. Not what they want, which is a few thousand, with 2gb of
Having not broken the 1,000 mark myself, I don't have a lot else here
Jeremy D. Zawodny, <jzawodn@stripped>
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936
MySQL 3.23.41-max: up 7 days, processed 146,377,980 queries (241/sec. avg)