>
> so follow the advice above. we need to see pg_stat_activity, and/or
> pg_locks while your test is running (especially take note of pg_lock
> records with granted=f)
Attached.
The database is named de. The process with procpid 3728 has the SQL query
for my "main" thread--the one that reads the 12,000,000 rows one by one.
procpid 6272 was handling the queries from the ~22 threads, although at the
time this was taken, it was idle. But if I monitor it, I can see the queries
of tables B and C going through it.
I am not clear what to read into pg_locks except that the "main" thread
(3728's query) sure has a lot of locks! But all 3728 is doing is reading
rows from table A, nothing else.
Aren
de=# SELECT * FROM pg_locks;
locktype | database | relation | page | tuple | virtualxid |
transactionid | classid | objid | objsubid | virtualtransaction | pid |
mode | granted
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+------------------+---------
relation | 18801 | 20333 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 10985 | | | |
| | | | 7/27 | 6492 | AccessShareLock | t
virtualxid | | | | | 7/27 |
| | | | 7/27 | 6492 | ExclusiveLock | t
relation | 18801 | 20013 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 20329 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 20391 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 20007 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 20001 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 20390 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 20339 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
virtualxid | | | | | 5/9 |
| | | | 5/9 | 3728 | ExclusiveLock | t
relation | 18801 | 20317 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 20019 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 20398 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 20389 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
virtualxid | | | | | 6/601815 |
| | | | 6/601815 | 6272 | ExclusiveLock | t
relation | 18801 | 20025 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 20337 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 19983 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 19977 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 19995 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 19971 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 20031 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 20393 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 20125 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 20341 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 20335 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
transactionid | | | | | |
13836733 | | | | 6/601815 | 6272 |
ExclusiveLock | t
relation | 18801 | 20331 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 20397 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 20394 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 20396 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 20319 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 20321 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 20392 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 20388 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 19989 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 20323 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
relation | 18801 | 53814 | | | |
| | | | 6/601815 | 6272 | RowExclusiveLock | t
relation | 18801 | 20395 | | | |
| | | | 5/9 | 3728 | AccessShareLock | t
(40 rows)
de=# SELECT * FROM pg_stat_activity;
datid | datname | procpid | usesysid | usename | application_name
| client_addr | client_port | backend_start | xact_start
| query_start | waiting | current_query
-------+----------+---------+----------+----------+-----------------------------+-------------+-------------+----------------------------+----------------------------+----------------------------+---------+----------------------------------------
11874 | postgres | 1968 | 10 | postgres | pgAdmin III - Server Status
| ::1 | 49617 | 2011-05-09 20:54:35.377-05 |
| 2011-05-09 20:58:54.073-05 | f | <IDLE>
11874 | postgres | 3904 | 10 | postgres | pgAdmin III - Browser
| ::1 | 49585 | 2011-05-09 20:53:39.473-05 |
| 2011-05-09 20:53:39.945-05 | f | <IDLE>
18801 | de | 6492 | 10 | postgres | psql
| ::1 | 49653 | 2011-05-09 20:56:57.678-05 | 2011-05-09
20:58:54.509-05 | 2011-05-09 20:58:54.509-05 | f | SELECT * FROM
pg_stat_activity;
18801 | de | 6272 | 10 | postgres |
| 127.0.0.1 | 49622 | 2011-05-09 20:54:54.97-05 |
| 2011-05-09 20:58:54.509-05 | f | <IDLE>
18801 | de | 3728 | 10 | postgres |
| 127.0.0.1 | 49621 | 2011-05-09 20:54:53.459-05 | 2011-05-09
20:54:53.716-05 | 2011-05-09 20:54:53.716-05 | f | SELECT * FROM
"raw"."TxDPS all arrests"
18801 | de | 6748 | 10 | postgres | pgAdmin III - Browser
| ::1 | 49588 | 2011-05-09 20:53:51.838-05 |
| 2011-05-09 20:54:31.131-05 | f | <IDLE>
(6 rows)
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
