Обсуждение: postgresql hanging (blocking) with smp kernel
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
-----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-----
> 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