-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Guillaume Nobiron wrote:
| Hi,
|
| Environment :
| - Fedora Core 2 (kernel 2.6.10)
| - postgresql 7.4.7
|
| I am running a huge query with several joins that needs around 900
| Mbytes of Memory (System RAM is 512 Mbytes).
|
| When the system starts to swap the postgres process, CPU consumed drops
| to around 2% (instead of around 50% on another system with kernel 2.4).
| The query was still working after more than 4 hours, spending the time
| with 98% if IO wait.
|
| So I tried to run the postmaster with the environment variable
| LD_ASSUME_KERNEL=2.4.1. With this, the query last 7 minutes !!!
|
| Why swapping is so bad with the new kernel ?
|

Hello, Guillaume.

Your swapping issue may not necessarily have to do with bad, perhaps
just slightly mistuned for your usage profile, virtual memory
management. I think /proc/sys/vm/swappiness only appeared in the 2.6
series of kernels (or late in 2.4), but it has rather significant effect
on the way kernel handles pages that are swapped out to disk, and most
importantly, those that have been swapped back in again, yet still
occupy the swap space - this new mechanism usually results in a certain
amount of memory being reserved swap cache, which leaves less where it's
more important to have it. You might want to read more about it in this
(rather lengthy) kerneltrap articles: http://kerneltrap.org/node/3000
and http://kerneltrap.org/node/3828

What we'd probably need here though (after you've verified playing with
swappiness doesn't help), is a couple of minutes worth of typical memory
manager behaviour while this transaction of yours is taking place,
especially where swapin/swapout goes mad. Try running "vmstat 5" while
it's running and see if there are any interesting patterns, also, be
sure to include enough context before and after such events (up to half
a minute on each side should do).

My guess is, you'd do well with an extra gigabyte or so of memory -
512MB really isn't much nowadays. Why make I/O even worse bottleneck
than it needs to be by forcing pages of active memory in and out?

Kind regards,
- --
~ Grega Bremec
~ gregab at p0f dot net
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFCh8XHfu4IwuB3+XoRAicxAJwI0FzZIpXpxlJlZMXVJU Jaqdj0EgCfRNuw
Dr58jtIgHDtjq/LCjd2Kr1s=
=iLle
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)