Re: Opteron/FreeBSD/PostgreSQL performance poor

Hi Stephen,

Thanks for your input. My follow ups are interleaved below ...

Stephen Frost wrote:
> * andy rost (andy(dot)rost(at)noaa(dot)gov) wrote:
>
>>We're in the process of porting from Informix 9.4 to PostgreSQL 8.1.3.
>>Our PostgreSQL server is an AMD Opteron Dual Core 275 with two 2.2 Ghz
>>64-bit processors. There are two internal drives and an external
>>enclosure containing 14 drives (configured as 7 pairs of mirrored drives
>>- four pairs for table spaces, one pair for dbcluster, two pairs for
>>point in time recovery). The operating system is FreeBSD 6.0-RELEASE #10
>
>
> Not sure it matters, but is the mirroring done with a hardware
> controller or in software?
>

I'll have to check on this when our system administrator returns
tomorrow. I performed a quick test while the server was under load by
moving a couple of Gigs of data while running iostat.I was getting disk
I/O rates of about 125 KB per transaction, 250 transactions per second,
and 35 Mg per second on all drives.

>
>>shared_buffers = 125000 # min 16 or max_connections*2,
>>8KB each
>>temp_buffers = 1000 # min 100, 8KB each
>>max_prepared_transactions = 0 # can be 0 or more
>># note: increasing max_prepared_transactions costs ~600 bytes of shared
>>memory
>># per transaction slot, plus lock space (see max_locks_per_transaction).
>>work_mem = 10000 # min 64, size in KB
>>maintenance_work_mem = 50000 # min 1024, size in KB
>>max_stack_depth = 500000 # in 100, size in KB
>> # ulimit -a or ulimit -s
>
>
> These seem kind of.. backwards... Just an example of one system I've
> got shows:
>
> shared_buffers = 10000
> work_mem = 32768
> maintenance_work_mem = 65535
>
> Defaults for the rest. This is more of a data-warehouse than an OLTP,
> so I'm sure these aren't perfect for you, but you might try playing with
> them some.

Originally shared_buffers was set to 32768. I set it to its current
value out of desperations (newby response).

>
>
>># - Free Space Map -
>>max_fsm_pages = 600000 # min max_fsm_relations*16, 6
>>bytes each
>
>
> This seems somewhat hgih from the default of 20,000, but for a very
> frequently changing database it may make sense.
>

This value is based on the output from VACUUM ANALYZE

>
>>archive_command = 'archive_wal -email -txtmsg "%p" "%f"' #
>>command to use
>
>
> Are WALs being archived very frequently? Any idea if this takes much
> time? I wouldn't really think it'd be an issue, but might be useful to
> know.
>

Yes, about 100 times per hour. No, I don't think it takes much time

>
>>effective_cache_size = 27462 # typically 8KB each
>
>
> This seems like it might be a little low... How much memory do you have
> in the system? Then again, with your shared_mem set so high, perhaps
> it's not that bad, but it might make sense to swap those two settings,
> or at least that'd be a more common PG setup.

Oops, forgot to mention that we have 6 Gigs of memory. This value was
set based on sysctl -n vfs.hibufspace / 8192

>
>
>>random_page_cost = 2 # units are one sequential page
>
>
> That's quite a bit lower than the default of 4... May make sense for
> you but it's certainly something to look at.
>

This value set per web page entitiled "Annotated POSTGRESQL.CONF Guide
for PostgreSQL"

>
>>We're running an OLTP database with a small number of connections (<50)
>>performing mostly reads and inserts on modest sized tables (largest is <
>>2,000,000 records).
>>
>>The symptoms are:
>>
>>a) All 4 CPUs are nearly always 0% idle;
>>b) The system load level is nearly always in excess of 20;
>
>
> At a guess I'd say that the system is doing lots of sequential scans
> rather than using indexes, and that's why the processes are ending up in
> a disk-wait state, which makes the load go up. Have you looked at the
> plans which are being generated for the most common queries to see what
> they're doing?

We thought of that too. However, executing:
select * from pg_stat_user_tables
suggests that we are using indexes where needed. We confirmed this by
checking and running manually queries reported by
select * from pg_stat_activity
while the server is suffering

>
> I'd also wonder if the shared_mem setting isn't set *too* high and
> causing problems with the IPC or something... Not something I've heard
> of (generally, going up with shared_mem doesn't degrade performance,
> just doesn't improve it) but might be possible.
>

Possible I suppose but we had the same trouble while the server was
configured with 32768 buffers

>
>>We VACUUM ANALYZE user databases every four hours. We VACUUM template1
>>every 4 hours. We make a copy of the current WAL every minute. We create
>>a PIT recovery archive daily daily. None of these, individually seem to
>>place much strain on the server.
>
>
> This doesn't sound too bad at all. How long do the vacuum's run for?
> If it's 3 hours, then that might start to be an issue with disk I/O
> contention...
>

VACUUM ANALYZE lasts about an hour and fifteen minutes

>
>>Hopefully I've supplied enough information to start diagnosing the
>>problem. Any ideas, thoughts, suggestions are greatly appreciated ...
>
>
> Just my 2c, hopefully you'll get some better answers too. :)
>

Again, many thanks. Is this the proper mail list for this problem or
should I also be addressing the administation mail list as well?

> Thanks,
>
> Stephen

--
--------------------------------------------------------------------------------
Andrew Rost
National Operational Hydrologic Remote Sensing Center (NOHRSC)
National Weather Service, NOAA
1735 Lake Dr. West, Chanhassen, MN 55317-8582
Voice: (952)361-6610 x 234
Fax: (952)361-6634
andy(dot)rost(at)noaa(dot)gov
http://www.nohrsc.noaa.gov
--------------------------------------------------------------------------------