Re: Scalability in postgres

Thanks David...

2009/5/28 David Rees <drees76(at)gmail(dot)com>

> On Thu, May 28, 2009 at 11:50 AM, Fabrix <fabrixio1(at)gmail(dot)com> wrote:
> > Monitoring (nmon, htop, vmstat) see that everything is fine (memory, HD,
> > eth, etc) except that processors regularly climb to 100%.
>
> What kind of load are you putting the server under when this happens?
>

I have many windows clients connecting to the database for odbc, they do
select, insert and update data. All these operations are all answer very
quickly in less than 1 second are well optimized, but when processors go up
to 100% All queries go up from 10 to 18 seconds, and are the same type of
operations when this happends.

> > I can see that the processes are waiting for CPU time:
> >
> > procs -----------memory---------- ---swap-- -----io---- --system--
> -----cpu------
> > r b swpd free buff cache si so bi bo in cs us sy
> id wa st
> > 0 0 0 47119688 117420 17142044 0 0 0 200 1545 1810 1
> 1 98 0 0
> > 318 0 0 47116464 117440 17142052 0 0 0 532 1416 2396 1
> 0 99 0 0
> > 500 0 0 47115224 117440 17142052 0 0 0 0 1118 322144
> 91 5 4 0 0
> > 440 0 0 47114728 117440 17142044 0 0 0 0 1052 333137
> 90 5 5 0 0
> > 339 0 0 47114484 117440 17142048 0 0 0 0 1061 337528
> 85 4 11 0 0
> > 179 0 0 47114112 117440 17142048 0 0 0 0 1066 312873
> 71 4 25 0 0
> > 5 1 0 47122180 117468 17142028 0 0 192 3128 1958 136804
> 23 2 75 1 0
> > 3 0 0 47114264 117476 17142968 0 0 608 5828 2688 4684 7
> 2 89 2 0
>
> Wow, that's some serious context-switching right there - 300k context
> switches a second mean that the processors are spending a lot of their
> time fighting for CPU time instead of doing any real work.
>
> It appears that you have the server configured with a very high number
> of connections as well? My first suggestion would be to look at a way
> to limit the number of active connections to the server at a time
> (pgPool or similar).

yes, i have max_connections = 5000
can lower, but at least i need 3500 connections

>
>
> -Dave
>