Re: Queries becoming slow under heavy load

От: mark
Тема: Re: Queries becoming slow under heavy load
Дата: ,
Msg-id: 01ed01cbbdca$c216aae0$464400a0$@com
(см: обсуждение, исходный текст)
Ответ на: Re: Queries becoming slow under heavy load  (Ivan Voras)
Список: pgsql-performance

Скрыть дерево обсуждения

Queries becoming slow under heavy load  ("Anne Rosset", )
 Re: Queries becoming slow under heavy load  (Andy Colson, )
 Re: Queries becoming slow under heavy load  ("Kevin Grittner", )
 Re: Queries becoming slow under heavy load  ("Ing. Marcos Ortiz Valmaseda", )
 Re: Queries becoming slow under heavy load  (Ivan Voras, )
  Re: Queries becoming slow under heavy load  ("mark", )
 FW: Queries becoming slow under heavy load  ("Anne Rosset", )
  Re: FW: Queries becoming slow under heavy load  (Shaun Thomas, )
   Re: FW: Queries becoming slow under heavy load  (Scott Marlowe, )
  Re: FW: Queries becoming slow under heavy load  (Scott Marlowe, )
   Re: FW: Queries becoming slow under heavy load  ("Anne Rosset", )
    Re: FW: Queries becoming slow under heavy load  (Shaun Thomas, )
     Re: FW: Queries becoming slow under heavy load  (Mladen Gogala, )
     Re: FW: Queries becoming slow under heavy load  ("Anne Rosset", )
      Re: FW: Queries becoming slow under heavy load  (Scott Marlowe, )

> -----Original Message-----
> From:  [mailto:pgsql-performance-
> ] On Behalf Of Ivan Voras
> Sent: Wednesday, January 26, 2011 6:25 AM
> To: 
> Subject: Re: [PERFORM] Queries becoming slow under heavy load
>
> On 25/01/2011 22:37, Anne Rosset wrote:
> > Hi,
> >
> > We are running some performances tests.  With a lot of concurrent
> > access,  queries get very slow. When there is no load, those queries
> run
> > fast.
>
> As others said, you need to stat how many concurrent clients are
> working
> on the database and also the number of logical CPUs (CPU cores,
> hyperthreading) are present in the machine. So far, as a rule of thumb,
> if you have more concurrent active connections (i.e. doing queries, not
> idling), you will experience a sharp decline in performance if this
> number exceeds the number of logical CPUs in the machine.
>
Depending on what version the OP is running - I didn't see where a version was givin - if there is a "lot" number of
idleconnections it can affect things as well. Tom indicated to me this should be "much better" in 8.4 and later.  


<slight deviation to put idle connections overhead in prespecive>
We cut our idle connections from 600+ to a bit over 300 and saw a good drop in box load and query responsiveness.
(stillget large user cpu load spikes though when a few hundred idle connection processes are woken open because they
allappear to be sleeping on the same semaphore and one of them has work to do. ) 

(yeah I know get a pooler, to bad only bouncer seems to pool out idle connections with transaction pooling but then I
loseprepared statements... I am still working on that part and getting off 8.3. yes our app tried to do its own quasi
connectionpooling. When we deployed the app on a few hundred boxes the error of this choice years ago when this app
livedon much fewer machines is now pretty evident.) 

>
> --
> Sent via pgsql-performance mailing list (pgsql-
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance



В списке pgsql-performance по дате сообщения:

От: Scott Marlowe
Дата:
Сообщение: Re: FW: Queries becoming slow under heavy load
От: Michael Kohl
Дата:
Сообщение: High load,