Re: Performance problems with a higher number of clients

On Thu, 11 Dec 2003 04:13:28 +0000
Alfranio Correia Junior <alfranio@lsd.di.uminho.pt> wrote:

>   r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs
>   us sy  id
>   2 29  1 106716   9576   7000 409876  32 154  5888  1262  616  1575
>   8 12  80

On linux I've found as soon as it has to swap its oh-so-wonderful VM
brings the machine to a screeching halt.


> sort_mem = 10240
>
Here's a big problem

This gives _EACH SORT_ 10MB (No more, no less) to play with.
10MB * 500 connections == 5000MB in one case..  Some queries may
have more sort steps. It is possible 1 connection could be using
30-40MB of sort_mem. You'll need to bring that value down to prevent
swapping.

If you have a few "common" queries that are run a lot check out hte
explain analyze. You can see about how much sort_mem you'll need. Look
in the sort step. it should tell you the width and the # of rows.
Multiply those. That is sort of how much memory you'll need (I'd round
it up a bit)

If under normal workload your DB is swapping you have problems. You'll
need to either tune your config or get bigger hardware.  You may want to
also consider an OS that deals with that situation a bit better.

good luck.

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/