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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
