Re: [PERFORM] Extreme high load averages

From: Martin Foster <martin(at)ethereal-realms(dot)org>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>, PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: [PERFORM] Extreme high load averages
Date: 2003-07-06 10:31:52
Message-ID: 3F07FA98.70009@ethereal-realms.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice pgsql-performance

Richard Huxton wrote:

> On Sunday 06 Jul 2003 5:54 am, Martin Foster wrote:
>
>>The only time that I have ever seen load averages of 30 or more under
>>OpenBSD is when one of my scripts goes wild. However, I can say that
>>I am also seeing these load averages under PostgreSQL 7.3.2 after a
>>migration to it from MySQL.
>
> [snip]
>
>>However, the system
>>handled 250 concurrent users without a singular problem, while under
>>Postgres with new scripts using functions, referential integrity,
>>transactions and lighter code, the system starts to buckle at even less
>>then 70 users.
>
> [snip]
>
>>PIII 1Ghz, 1GB
>>SDRAM, 2 IDE 20GB drives.
>>
>>I have changed settings to take advantage of the memory. So the
>>following settings are of interest:
>> shared_buffers = 16384
>> wal_buffers = 256
>> sort_mem = 16384
>> vacuum_mem = 32768
>
>
> You do know that sort_mem is in kB per sort (not per connection, but per sort
> being done by a connection). That's 16MB per sort you've allowed in main
> memory, or for 70 concurrent sorts up to 1.1GB of memory allocated to
> sorting. You're not going into swap by any chance?
>
> Might want to try halving shared_buffers too and see what happens.
>
> I don't know the *BSDs myself, but do you have the equivalent of iostat/vmstat
> output you could get for us? Also a snapshot of "top" output? People are
> going to want to see:
> - overall memory usage (free/buffers/cache/swap)
> - memory usage per process
> - disk activity (blocks in/out)
>
>>From that lot, someone will be able to point towards the issue, I'm sure.

Actually, no I did not. Which is probably why it was as high as it is.
When looking at the PostgreSQL Hardware Performance Tuning page, it
seems to imply that you should calculate based on RAM to give it an
appropriate value.

http://www.postgresql.org/docs/aw_pgsql_book/hw_performance/node8.html

I dropped that value, and will see if that helps. The thing is, the
system always indicated plenty of memory available. Even when at a 30
load level the free memory was still roughly 170MB.

Tomorrow will be a good gage to see if the changes will actually help
matters. And if they do not, I will include vmstat, iostat, and top
as requested.

Thanks!

Martin Foster
Creator/Designer Ethereal Realms
martin(at)ethereal-realms(dot)org

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Shridhar Daithankar 2003-07-06 10:34:48 Re: Extreme high load averages
Previous Message Martin Foster 2003-07-06 10:26:21 Re: Extreme high load averages

Browse pgsql-performance by date

  From Date Subject
Next Message Shridhar Daithankar 2003-07-06 10:34:48 Re: Extreme high load averages
Previous Message Martin Foster 2003-07-06 10:26:21 Re: Extreme high load averages