Re: postgresql hanging (blocking) with smp kernel

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

Marcel Groner wrote:

|>Marcel Groner wrote:
|>
|>
|>>I have a problem with postgresql runnung on smp kernel.
|>>
|>>setup:
|>>
|>>master:
|>>-------
|>>- Pentium 4 (hyperthreading)
|>>- 2 GB Memory
|>>- os: fedora core 1
|>>- kernel: 2.4.22-1.2188.nptlsmp
|>>- postgresql: 7.4.3-1PGDG
|>>
|>>slave 1:
|>>--------
|>>- Pentium 4 (hyperthreading)
|>>- 2 GB Memory
|>>- os: fedora core 1
|>>- kernel: 2.4.22-1.2115.nptlsmp
|>>- postgresql: 7.4.3-1PGDG
|>>
|>>slave 2:
|>>--------
|>>- Double Xeon (with hyperthreading)
|>>- 2 GB Memory
|>>- os: fedora core 1
|>>- kernel: 2.4.22-1.2199.nptlsmp
|>>- postgresql: 7.4.3-1PGDG
|>>
|>>
|>>the "replication" is made by hand. reading from slave 1 or slave 2
|>>(balanced) and wrtiting to master, slave 1 and slave 2. Our site is a
|>>high-traffic site (the biggest dating-site in switzerland:
|>>www.swissflirt.ch) with > 1400 concurrent users and > 40'000 visits
|>>per day.
|>>
|>>master and slave 1 (with pentium 4) are working perfectly with
|>>smp-kernel. slave 2 (with double xeon) has big problems. running p.e.
|>>with kernel 2.4.22 (non smp) works also but of course only one CPU is
|>>used. when I use the smp kernel, the connections (and queries) are
|>>hanging (blocking) after some short time. Also when I shutdown the
|>>application (using java and jdbc to connect to the databases) the
|>>postgres-processes (on slave 2) keep existing while the processes on
|>>master and slave 1 shutdown propertly.
|>
|>Hanging on what ? I'm running postgres in a similar server ( the HT is
|>enabled too so is like 4 CPU)  without any problem at all.
|>
|>Show as "select * from pg_stat_activity"
|>         "select * from pg_locks"
|>
|>
|>Regards
|>Gaetano Mendola
|
|
| Hi Gaetano
|
| OK, here is the output from the two queries attached as textfile.
| Theres another issue: I'm running the database completely in the memory. I
| created a new location on the shared-memory-device (/dev/shm) with
| initlocation and created the database there. Could that also be a problem?
| on slave 1 (HT Pentium 4) it works and there the location of the database
| is also /dev/shm.
|
| Regards
| Marcel Groner


I don't think the problem is the memory device, from the result on
pg_stat_activity I see you are not running with stats_command_string = true,
I don't see the activity you have on your database so is hard only watching
at the pg_locks what is going on, Tom Lane for sure can sheds some light on it.
Enable on your configuration file the statistics collector and to
enable the command string collector so is better to spot out the problem,
after enabling it repeat the two queries above and also a "ps -eafwwww | grep post"
can help.

I'm sure however that is a client side problem.


Regards
Gaetano Mendola


























-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBRrEz7UpzwH2SGd4RAliKAJ9NDJur7M2XAMYWQ+q9WbEcZNUBtACggVAZ
78RojSPy3E2HBt6MO48qLz4=
=GRc+
-----END PGP SIGNATURE-----