Performance tuning on RedHat Enterprise Linux 3

[Tony Wasson]

On Tue, 07 Dec 2004 07:50:44 -0500, P.J. Josh Rovero
<rovero@sonalysts.com> wrote:

> There are many reports of kernel problems with memory allocation
> (too agressive) and swap issues with RHEL 3.0 on both RAID
> and non-RAID systems. I hope folks have worked through all
> those issues before blaming postgresql.

We have seen several boxes have kswapd go crazy (near 100% CPU) on
RHEL 3 boxes. Upgrading to kernel 2.4.21-4 fixed this.

Tony Wasson

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [email]majordomo@postgresql.org[/email] so that your
message can get through to the mailing list cleanly

[Paul Tillotson]

>>>Does postgres actually do multiple concurrent sorts within a single
>>>backend?
>>>
>>>

>>Certainly. Consider for example a merge join with each input being
>>sorted by an explicit sort step. DISTINCT, ORDER BY, UNION, and
>>related operators require their own sort steps in the current
>>implementation. It's not difficult to invent queries that require
>>arbitrarily large numbers of sort steps.
>>
>>

>
>Tom, in Bruce's document on performance tuning, the page titled
>"Multiple CPUs" states:
>
>"POSTGRESQL uses a multi-process model, meaning each database connection
>has its own Unix process...POSTGRESQL does not use multi-threading to
>allow a single process to use multiple CPUs."
>
>I took this to mean that PostgreSQL was not multi-threaded at all, and
>that each connection was serviced by a single, non-threaded process.
>Have I interpreted this incorrectly? Are you saying that the backend
>process actually is multi-threaded? In the example you site, multiple
>sorts could be accomplished serially in a non-threaded process.
>
>

Guy,

You understand correctly. Each process is only running one query at
once, but in terms of memory usage, several sorts are executing in
parallel.

For example, a merge join requires that both the left and right tables
be sorted; as the join is being executed, both the left and right tables
are being sorted. (Why doesn't it sort one and then the other? It
would be a waste of memory to require that one of the [sorted] tables be
kept in memory or written completely to the disk and then fetched
later. Instead, it just sorts them both as it goes along.)

However, this does mean that the amount of per-process memory being used
for sorting will not vary with the "workload" of the database or number
of people running that query (as each process only runs the query
once). The amount of per-process memory used will vary with the
complexity of the query and the plan chosen by the planner.

Paul Tillotson

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [email]majordomo@postgresql.org[/email] so that your
message can get through to the mailing list cleanly