Re: Performance problems with a higher number of clients

On Thu, 11 Dec 2003 04:13:28 +0000
Alfranio Correia Junior <alfranio(at)lsd(dot)di(dot)uminho(dot)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(at)jefftrout(dot)com>
http://www.jefftrout.com/
http://www.stuarthamm.net/