List:General Discussion« Previous MessageNext Message »
From:Jeremy Zawodny Date:September 13 2001 4:59am
Subject:Re: my.cnf optimization question ..
View as plain text  
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:
> [mysqld]
> skip-locking
> 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
it is.

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
> again.

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
> ram.

Having not broken the 1,000 mark myself, I don't have a lot else here
to say.

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)
my.cnf optimization question ..Weslee Bilodeau10 Sep
  • Re: my.cnf optimization question ..Jeremy Zawodny11 Sep
  • Re: my.cnf optimization question ..Weslee Bilodeau11 Sep
    • error 1064Gene Gurevich12 Sep
      • Re: error 1064Gerald Clark12 Sep
        • Re: error 1064Gene Gurevich12 Sep
          • mysql to oracle conversionGene Gurevich12 Sep
            • Re: mysql to oracle conversionPaul DuBois12 Sep
    • Re: my.cnf optimization question ..Jeremy Zawodny13 Sep