Re: Abnormal performance difference between Postgres and MySQL

On Wed, Feb 25, 2009 at 12:05 PM, Farhan Husain <russoue(at)gmail(dot)com> wrote:
>
> On Wed, Feb 25, 2009 at 12:58 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>
>> Just start up psql and type:
>>
>> show work_mem;
>
> I did it, it does not show anything.

Did you remember the ; symbol?

> Here is what I have got from the config
> file:
>
> shared_buffers = 32MB                   # min 128kB or max_connections*16kB
>                                         # (change requires restart)

Assuming your machine has more than a few hundred megs of ram in it,
this is kinda small. Generally setting it to 10 to 25% of total
memory is about right.

> work_mem = 1792MB                               # min 64kB

That's crazy high. work_mem is the amount of memory a single sort can
use. Each query can have multiple sorts. So, if you have 10 users
running 10 sorts, you could use 100*1792MB memory.

Look to set it into the 1 to 16Meg. If testing shows a few queries
can do better with more work_mem, then look at setting it higher for
just that one connection.