Dear lazyweb,
it’s well known that PostgreSQL is not multi-threaded, but runs multiple instances that can run on different CPUs/cores.
We have lately bought a dual quad core system as our new database server and everything is neat and fine as long I’m testing the database locally via socket or TCP on either IP and the database scales about all 8 cores:
postgres@dhcp-140:~$ /usr/lib/postgresql/8.3/bin/pgbench -h 192.168.1.140 -c 8 -t 30000 testdb
Cpu0 : 24.1%us, 3.1%sy, 0.0%ni, 70.9%id, 0.0%wa, 0.0%hi, 1.9%si, 0.0%st
Cpu1 : 24.5%us, 4.8%sy, 0.0%ni, 69.0%id, 0.0%wa, 0.0%hi, 1.8%si, 0.0%st
Cpu2 : 17.7%us, 3.2%sy, 0.0%ni, 77.3%id, 0.0%wa, 0.0%hi, 1.9%si, 0.0%st
Cpu3 : 13.5%us, 2.3%sy, 0.0%ni, 83.0%id, 0.0%wa, 0.0%hi, 1.3%si, 0.0%st
Cpu4 : 22.0%us, 5.3%sy, 0.0%ni, 70.9%id, 0.0%wa, 0.0%hi, 1.9%si, 0.0%st
Cpu5 : 23.5%us, 4.2%sy, 0.0%ni, 70.0%id, 0.0%wa, 0.0%hi, 2.3%si, 0.0%st
Cpu6 : 28.3%us, 4.3%sy, 0.0%ni, 63.7%id, 0.0%wa, 0.0%hi, 3.7%si, 0.0%st
Cpu7 : 23.9%us, 4.3%sy, 0.0%ni, 69.1%id, 0.0%wa, 0.0%hi, 2.8%si, 0.0%st
PID USER PR NI VIRT RES SHR S %CPU P %MEM TIME+ COMMAND
3551 postgres 20 0 98.2m 18m 16m R 11 1 0.2 0:05.30 postgres
3553 postgres 20 0 98.2m 18m 16m R 11 3 0.2 0:05.38 postgres
3546 postgres 20 0 98.2m 18m 16m S 7 7 0.2 0:05.52 postgres
3547 postgres 20 0 98.2m 18m 16m S 7 0 0.2 0:04.22 postgres
3548 postgres 20 0 98.2m 18m 16m S 7 2 0.2 0:04.62 postgres
3549 postgres 20 0 98.2m 18m 16m S 7 4 0.2 0:04.92 postgres
3550 postgres 20 0 98.2m 18m 16m S 7 0 0.2 0:05.84 postgres
3552 postgres 20 0 98.2m 18m 16m S 5 6 0.2 0:04.96 postgres
Column P marks the last used processor that process was running on. As you can see, postgres runs basically on all cores. But when I try to access the database from a remote host something strange happens:
Cpu0 : 22.5%us, 3.7%sy, 0.0%ni, 72.8%id, 0.0%wa, 0.0%hi, 0.9%si, 0.0%st
Cpu1 : 17.8%us, 2.8%sy, 0.0%ni, 79.1%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st
Cpu2 : 5.6%us, 1.3%sy, 0.0%ni, 92.7%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st
Cpu3 : 18.7%us, 3.2%sy, 0.0%ni, 76.5%id, 0.0%wa, 0.0%hi, 1.6%si, 0.0%st
Cpu4 : 1.0%us, 0.3%sy, 0.0%ni, 98.3%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st
Cpu5 : 27.0%us, 2.5%sy, 0.0%ni, 69.8%id, 0.0%wa, 0.0%hi, 0.6%si, 0.0%st
Cpu6 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu7 : 34.1%us, 3.7%sy, 0.0%ni, 57.0%id, 0.0%wa, 0.6%hi, 4.6%si, 0.0%st
PID USER PR NI VIRT RES SHR S %CPU P %MEM TIME+ COMMAND
3580 postgres 20 0 98.2m 18m 16m S 13 7 0.2 0:03.56 postgres
3582 postgres 20 0 98.2m 18m 16m S 12 1 0.2 0:03.54 postgres
3586 postgres 20 0 98.2m 18m 16m S 11 7 0.2 0:03.34 postgres
3587 postgres 20 0 98.2m 18m 16m S 11 5 0.2 0:03.36 postgres
3581 postgres 20 0 98.2m 18m 16m S 11 5 0.2 0:03.46 postgres
3583 postgres 20 0 98.2m 18m 16m S 11 7 0.2 0:03.40 postgres
3584 postgres 20 0 98.2m 18m 16m R 11 3 0.2 0:03.36 postgres
3585 postgres 20 0 98.2m 18m 16m S 11 1 0.2 0:03.34 postgres
Postgres runs only on all core with an odd cpuid, that is core 1, 3, 5 and 7. This is reproducible and doesn’t change when I connect more then one remote client or by raising the -c parameter. I’m running Lenny:
dhcp-140:~# dpkg -l | grep postgres
ii postgresql-8.3 8.3.3-1 object-relational SQL database, version 8.3
ii postgresql-client-8.3 8.3.3-1 front-end programs for PostgreSQL 8.3
ii postgresql-client-common 88 manager for multiple PostgreSQL client versi
ii postgresql-common 88 PostgreSQL database-cluster manager
ii postgresql-contrib-8.3 8.3.3-1 additional facilities for PostgreSQL
So, dear lazyweb, when you have some tips to debug or solve the issue, please comment! I don’t know yet if this is a bug and when, if it’s in Postgres or maybe in the kernel (2.6.25-2-amd64)?