Thread: postgresql hanging (blocking) with smp kernel

postgresql hanging (blocking) with smp kernel

From
groner@puzzle.ch (Marcel Groner)
Date:
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.

I also tried with other kernel-releases (the one on the master
2.4.22-1.2188.nptlsmp and the one on the slave 1
2.4.22-1.2115.nptlsmp) but it doesnt help neither.


does anyone know what this could be?

thanks alot
marcel

Re: postgresql hanging (blocking) with smp kernel

From
Gaetano Mendola
Date:
-----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-----


Re: postgresql hanging (blocking) with smp kernel

From
"Marcel Groner"
Date:
> 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



Attachment