| From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Any advice on debugging hanging postgresql-8.1.21 (many postmaster's) |
| Date: | 2010-10-04 09:44:02 |
| Message-ID: | AANLkTinE5nNuw=odxKRoYyiPaG4h71zwT-w8B5S+43My@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
Hello again,
ouch, I have these postmaster's spinning up again -
even though I've disabled the cronjob and rebooted:
top - 11:42:31 up 1:22, 2 users, load average: 9.15, 7.59, 5.03
Tasks: 135 total, 10 running, 125 sleeping, 0 stopped, 0 zombie
Cpu0 : 31.7%us, 0.5%sy, 0.0%ni, 67.4%id, 0.4%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu1 : 32.9%us, 0.7%sy, 0.0%ni, 65.8%id, 0.1%wa, 0.0%hi, 0.5%si, 0.0%st
Cpu2 : 58.8%us, 1.3%sy, 0.0%ni, 38.2%id, 1.3%wa, 0.0%hi, 0.5%si, 0.0%st
Cpu3 : 21.7%us, 0.5%sy, 0.0%ni, 77.1%id, 0.1%wa, 0.0%hi, 0.5%si, 0.0%st
Mem: 4019028k total, 1512432k used, 2506596k free, 21828k buffers
Swap: 2104496k total, 0k used, 2104496k free, 1152844k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3208 postgres 17 0 122m 14m 9m R 143.9 0.4 30:22.79 postmaster
3193 postgres 18 0 122m 14m 9m R 117.7 0.4 34:04.08 postmaster
3263 postgres 18 0 120m 13m 9m R 109.4 0.3 11:06.24 postmaster
3315 postgres 17 0 121m 13m 9.9m R 90.7 0.4 1:59.89 postmaster
3275 postgres 18 0 121m 13m 9m R 83.2 0.4 6:44.74 postmaster
3243 postgres 18 0 122m 14m 9m R 69.0 0.4 20:04.06 postmaster
3233 postgres 17 0 122m 14m 9m R 47.2 0.4 25:01.18 postmaster
3256 postgres 18 0 122m 14m 9m R 35.2 0.4 14:48.54 postmaster
On Mon, Oct 4, 2010 at 11:34 AM, Robert Gravsjö <robert(at)blogg(dot)se> wrote:
> I would connect to the db with psql and query the pg_stat_activity and the
> pg_locks views.
pref=> select * from pg_stat_activity;
datid | datname | procpid | usesysid | usename | current_query
| query_start | backend_start | client_addr |
client_port
-------+---------+---------+----------+---------+------------------------------+-------------+-------------------------------+-------------+-------------
16384 | pref | 2681 | 16385 | pref | <command string not
enabled> | | 2010-10-04 10:22:53.051483+02 | |
-1
16384 | pref | 3193 | 16385 | pref | <command string not
enabled> | | 2010-10-04 11:17:40.081829+02 | |
-1
16384 | pref | 3208 | 16385 | pref | <command string not
enabled> | | 2010-10-04 11:20:18.763179+02 | |
-1
16384 | pref | 3233 | 16385 | pref | <command string not
enabled> | | 2010-10-04 11:22:50.818701+02 | |
-1
16384 | pref | 3243 | 16385 | pref | <command string not
enabled> | | 2010-10-04 11:25:34.110573+02 | |
-1
16384 | pref | 3256 | 16385 | pref | <command string not
enabled> | | 2010-10-04 11:28:33.355689+02 | |
-1
16384 | pref | 3263 | 16385 | pref | <command string not
enabled> | | 2010-10-04 11:32:02.116546+02 | |
-1
16384 | pref | 3275 | 16385 | pref | <command string not
enabled> | | 2010-10-04 11:35:31.611361+02 | |
-1
16384 | pref | 3315 | 16385 | pref | <command string not
enabled> | | 2010-10-04 11:40:00.119433+02 | |
-1
16384 | pref | 3355 | 16385 | pref | <command string not
enabled> | | 2010-10-04 11:42:51.827796+02 | |
-1
16384 | pref | 3356 | 16385 | pref | <command string not
enabled> | | 2010-10-04 11:43:04.285152+02 | |
-1
(11 rows)
pref=> select * from pg_locks;
locktype | database | relation | page | tuple | transactionid |
classid | objid | objsubid | transaction | pid | mode |
granted
---------------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+------+-----------------+---------
relation | 16384 | 17755 | | | |
| | | 5677546 | 3275 | AccessShareLock | t
relation | 16384 | 17313 | | | |
| | | 5677546 | 3275 | AccessShareLock | t
relation | 16384 | 16858 | | | |
| | | 5677313 | 3243 | AccessShareLock | t
transactionid | | | | | 5677313 |
| | | 5677313 | 3243 | ExclusiveLock | t
relation | 16384 | 16858 | | | |
| | | 5677742 | 3315 | AccessShareLock | t
relation | 16384 | 17755 | | | |
| | | 5677395 | 3256 | AccessShareLock | t
relation | 16384 | 17716 | | | |
| | | 5677313 | 3243 | AccessShareLock | t
relation | 16384 | 17313 | | | |
| | | 5677313 | 3243 | AccessShareLock | t
relation | 16384 | 17755 | | | |
| | | 5677070 | 3193 | AccessShareLock | t
relation | 16384 | 17716 | | | |
| | | 5677234 | 3233 | AccessShareLock | t
relation | 16384 | 10342 | | | |
| | | 5677823 | 3355 | AccessShareLock | t
transactionid | | | | | 5677234 |
| | | 5677234 | 3233 | ExclusiveLock | t
relation | 16384 | 17716 | | | |
| | | 5677454 | 3263 | AccessShareLock | t
transactionid | | | | | 5677167 |
| | | 5677167 | 3208 | ExclusiveLock | t
relation | 16384 | 17716 | | | |
| | | 5677546 | 3275 | AccessShareLock | t
relation | 16384 | 17716 | | | |
| | | 5677167 | 3208 | AccessShareLock | t
relation | 16384 | 17313 | | | |
| | | 5677070 | 3193 | AccessShareLock | t
relation | 16384 | 17721 | | | |
| | | 5677167 | 3208 | AccessShareLock | t
transactionid | | | | | 5677070 |
| | | 5677070 | 3193 | ExclusiveLock | t
relation | 16384 | 16858 | | | |
| | | 5677167 | 3208 | AccessShareLock | t
relation | 16384 | 17721 | | | |
| | | 5677742 | 3315 | AccessShareLock | t
transactionid | | | | | 5677823 |
| | | 5677823 | 3355 | ExclusiveLock | t
transactionid | | | | | 5677454 |
| | | 5677454 | 3263 | ExclusiveLock | t
relation | 16384 | 16858 | | | |
| | | 5677546 | 3275 | AccessShareLock | t
relation | 16384 | 17721 | | | |
| | | 5677313 | 3243 | AccessShareLock | t
relation | 16384 | 17755 | | | |
| | | 5677454 | 3263 | AccessShareLock | t
relation | 16384 | 17716 | | | |
| | | 5677742 | 3315 | AccessShareLock | t
relation | 16384 | 17313 | | | |
| | | 5677167 | 3208 | AccessShareLock | t
relation | 16384 | 16858 | | | |
| | | 5677454 | 3263 | AccessShareLock | t
relation | 16384 | 17716 | | | |
| | | 5677070 | 3193 | AccessShareLock | t
relation | 16384 | 17721 | | | |
| | | 5677234 | 3233 | AccessShareLock | t
relation | 16384 | 17721 | | | |
| | | 5677546 | 3275 | AccessShareLock | t
relation | 16384 | 17755 | | | |
| | | 5677234 | 3233 | AccessShareLock | t
transactionid | | | | | 5677742 |
| | | 5677742 | 3315 | ExclusiveLock | t
relation | 16384 | 16858 | | | |
| | | 5677070 | 3193 | AccessShareLock | t
relation | 16384 | 17716 | | | |
| | | 5677395 | 3256 | AccessShareLock | t
relation | 16384 | 17755 | | | |
| | | 5677167 | 3208 | AccessShareLock | t
relation | 16384 | 17313 | | | |
| | | 5677742 | 3315 | AccessShareLock | t
relation | 16384 | 17313 | | | |
| | | 5677234 | 3233 | AccessShareLock | t
relation | 16384 | 16858 | | | |
| | | 5677395 | 3256 | AccessShareLock | t
relation | 16384 | 17755 | | | |
| | | 5677742 | 3315 | AccessShareLock | t
relation | 16384 | 17721 | | | |
| | | 5677070 | 3193 | AccessShareLock | t
transactionid | | | | | 5677395 |
| | | 5677395 | 3256 | ExclusiveLock | t
relation | 16384 | 17721 | | | |
| | | 5677395 | 3256 | AccessShareLock | t
relation | 16384 | 16858 | | | |
| | | 5677234 | 3233 | AccessShareLock | t
relation | 16384 | 17755 | | | |
| | | 5677313 | 3243 | AccessShareLock | t
relation | 16384 | 17313 | | | |
| | | 5677454 | 3263 | AccessShareLock | t
relation | 16384 | 17721 | | | |
| | | 5677454 | 3263 | AccessShareLock | t
transactionid | | | | | 5677546 |
| | | 5677546 | 3275 | ExclusiveLock | t
relation | 16384 | 17313 | | | |
| | | 5677395 | 3256 | AccessShareLock | t
(50 rows)
Regards
Alex
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Alexander Farber | 2010-10-04 09:48:20 | Re: Any advice on debugging hanging postgresql-8.1.21 (many postmaster's) |
| Previous Message | Arthur van der Wal | 2010-10-04 09:41:15 | Question regarding custom parser |