Thread: BUG #12469: pg_locks shows locks held by pids not found i n pg_stat_activity or ps
BUG #12469: pg_locks shows locks held by pids not found i n pg_stat_activity or ps
From
kop@meme.com
Date:
The following bug has been logged on the website:
Bug reference: 12469
Logged by: Karl O. Pinc
Email address: kop@meme.com
PostgreSQL version: 9.1.14
Operating system: LInux, Scientific Linux 6.5 (a RH clone)
Description:
Hi,
Sorry, I've no idea if I can repdoduce this bug. Thought it better to
report it as it is happening.
I found that after running a large transaction (for days, it's still
running) and then running out of shared memory that pg_locks reports locks
held by pids that do not seem to exist, either in ps output or in
pg_stat_activity. There are continued reports of running out of shared
memory.
===================================
[root@papio ~]# uname -a
Linux papio.biology.duke.edu 2.6.32-504.1.3.el6.x86_64 #1 SMP Tue Nov 11
14:19:04 CST 2014 x86_64 x86_64 x86_64 GNU/Linux
===================================
Posgres 9.1.14
Name : postgresql91-server
Arch : x86_64
Version : 9.1.14
Release : 1PGDG.rhel6
Repo : installed
>From repo : pgdg91
===================================
[root@papio ~]# cat /proc/meminfo
MemTotal: 1922372 kB
MemFree: 102532 kB
Buffers: 10036 kB
Cached: 1356744 kB
SwapCached: 35172 kB
Active: 1199912 kB
Inactive: 440760 kB
Active(anon): 835828 kB
Inactive(anon): 290936 kB
Active(file): 364084 kB
Inactive(file): 149824 kB
Unevictable: 0 kB
Mlocked: 0 kB
SwapTotal: 4128764 kB
SwapFree: 3720176 kB
Dirty: 176 kB
Writeback: 0 kB
AnonPages: 246972 kB
Mapped: 784712 kB
Shmem: 852852 kB
Slab: 124912 kB
SReclaimable: 57264 kB
SUnreclaim: 67648 kB
KernelStack: 1848 kB
PageTables: 23728 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
WritebackTmp: 0 kB
CommitLimit: 5089948 kB
Committed_AS: 2234120 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 274364 kB
VmallocChunk: 34359449592 kB
HardwareCorrupted: 0 kB
AnonHugePages: 10240 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
DirectMap4k: 8192 kB
DirectMap2M: 2088960 kB
===================================
[root@papio ~]# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 37
model name : Intel(R) Xeon(R) CPU E5-2697 v2 @ 2.70GHz
stepping : 1
microcode : 1063
cpu MHz : 2899.999
cache size : 30720 KB
physical id : 0
siblings : 1
core id : 0
cpu cores : 1
apicid : 0
initial apicid : 0
fpu : yes
fpu_exception : yes
cpuid level : 11
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat
pse36 clflush dts mmx fxsr sse sse2 ss syscall nx rdtscp lm constant_tsc up
arch_perfmon pebs bts xtopology tsc_reliable nonstop_tsc aperfmperf
unfair_spinlock pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 popcnt aes hypervisor
lahf_lm ida arat epb pln pts dts
bogomips : 5799.99
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:
===================================
This is a virtual machine. (I believe a vmware machine but am not
certain.)
===================================
I began a transaction 4 days ago. (More on this below.)
After getting the logs (yesterday):
[root@papio ~]# grep 18715 /var/log/databases.log
Jan 8 17:04:38 papio postgres[18715]: [2-1] 2015-01-08 17:04:38 EST
babase_test_chado_install (babase_admin) ERROR: out of shared memory
Jan 8 17:04:38 papio postgres[18715]: [2-2] 2015-01-08 17:04:38 EST
babase_test_chado_install (babase_admin) HINT: You might need to increase
max_pred_locks_per_transaction.
Jan 8 17:04:38 papio postgres[18715]: [2-3] 2015-01-08 17:04:38 EST
babase_test_chado_install (babase_admin) CONTEXT: SQL statement "SELECT 1
FROM ONLY "chado"."cvterm" x WHERE "cvterm_id" OPERATOR(pg_catalog.=) $1 FOR
SHARE OF x"
Jan 8 17:04:38 papio postgres[18715]: [2-4] 2015-01-08 17:04:38 EST
babase_test_chado_install (babase_admin) STATEMENT: commit
I began getting reports of out of shared memory errors.
Now I find that pg_locks shows locks held by pids that do not seem to exist,
either in ps or in pg_stat_activity.
===================================
babase_vcf=# select * from pg_stat_activity;
datid | datname | procpid | usesysid | usename | application_name
| c
lient_addr | client_hostname | client_port | backend_start |
xact_start | query_start | waiting |
current_query
---------+------------+---------+----------+--------------+------------------+--
-----------+-----------------+-------------+-------------------------------+----
---------------------------+-------------------------------+---------+----------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------
46044 | po_db | 6297 | 46043 | po_user |
| 1
27.0.0.1 | | 57878 | 2015-01-09 13:37:54.577923+03 |
| 2015-01-09 18:41:03.42406+03 | f |
<IDLE>
46044 | po_db | 28449 | 46043 | po_user |
| 1
27.0.0.1 | | 57847 | 2015-01-09 07:13:23.899885+03 |
| 2015-01-09 11:44:34.721515+03 | f |
<IDLE>
46044 | po_db | 28441 | 46043 | po_user |
| 1
27.0.0.1 | | 57845 | 2015-01-09 07:13:16.500897+03 |
| 2015-01-09 09:50:31.955463+03 | f |
<IDLE>
46044 | po_db | 26611 | 46043 | po_user |
| 1
27.0.0.1 | | 57840 | 2015-01-09 06:03:09.593727+03 |
| 2015-01-09 16:09:29.334678+03 | f |
<IDLE>
46044 | po_db | 25181 | 46043 | po_user |
| 1
27.0.0.1 | | 57838 | 2015-01-09 04:43:39.672989+03 |
| 2015-01-09 17:25:17.589691+03 | f |
<IDLE>
46044 | po_db | 3565 | 46043 | po_user |
| 1
27.0.0.1 | | 57240 | 2015-01-08 16:28:25.785561+03 |
| 2015-01-09 08:52:24.472508+03 | f |
<IDLE>
46044 | po_db | 25102 | 46043 | po_user |
| 1
27.0.0.1 | | 57836 | 2015-01-09 04:39:02.954888+03 |
| 2015-01-09 14:35:50.325124+03 | f |
<IDLE>
46044 | po_db | 22473 | 46043 | po_user |
| 1
27.0.0.1 | | 57834 | 2015-01-09 02:15:47.525914+03 |
| 2015-01-09 02:15:47.53616+03 | f |
<IDLE>
46044 | po_db | 17825 | 46043 | po_user |
| 1
27.0.0.1 | | 57755 | 2015-01-09 00:25:27.765374+03 |
| 2015-01-09 11:30:33.854107+03 | f |
<IDLE>
3566740 | babase_vcf | 23544 | 26963 | kop |
| :
:1 | | 60529 | 2015-01-06 08:38:44.065732+03 |
201
5-01-06 08:38:44.072167+03 | 2015-01-09 19:50:34.182578+03 | f |
SELECT fe
ature.feature_id AS feature_feature_id, feature.dbxref_id AS
feature_dbxref_id,
feature.organism_id AS feature_organism_id, feature.name AS feature_name,
featur
e.uniquename AS feature_uniquename, feature.residues AS feature_residues,
featur
e.seqlen AS feature_seqlen, feature.md5checksum AS feature_md5checksum,
feature.
type_id AS feature_type_id, feature.is_analysis AS feature_is_analysis,
feature.
is_obsolete AS feature_is_obsolete, feature.timeaccessioned AS
feature_timeacces
sioned, feature.timelastmodified AS feature_timelastmodified +
| | | | |
|
| | | |
| | | FROM
feat
ure JOIN featureloc ON featureloc.feature_id = feature.feature_id JOIN
feature_r
elationship ON feature_relationship.subject_id = feature.feature_id
+
| | | | |
|
| | | |
| | | WHERE
fea
ture.is_obsolete = false AND feature.dbxref_id = 165177 AND
featureloc.srcfeatur
e_id = 79489 AND featureloc.fmin = 96655480 AND featureloc.fmax = 96655481
AND f
eatureloc.locgroup = 0 AND featureloc.rank = 1 AND
feature_relationship.type_id
= 43037 AND feature_relationship.object_id = 2425993
3566740 | babase_vcf | 12177 | 16384 | babase_admin | psql
| :
:1 | | 34725 | 2015-01-09 18:47:46.623932+03 |
201
5-01-09 19:50:34.444996+03 | 2015-01-09 19:50:34.444996+03 | f |
select *
from pg_stat_activity;
(11 rows)
===================================
[root@papio ~]# ps ax
PID TTY STAT TIME COMMAND
1 ? Ss 0:23 /sbin/init
2 ? S 0:00 [kthreadd]
3 ? S 0:00 [migration/0]
4 ? S 0:32 [ksoftirqd/0]
5 ? S 0:00 [stopper/0]
6 ? S 0:04 [watchdog/0]
7 ? S 2:55 [events/0]
8 ? S 0:00 [cgroup]
9 ? S 0:00 [khelper]
10 ? S 0:00 [netns]
11 ? S 0:00 [async/mgr]
12 ? S 0:00 [pm]
13 ? S 0:11 [sync_supers]
14 ? S 0:10 [bdi-default]
15 ? S 0:00 [kintegrityd/0]
16 ? S 29:29 [kblockd/0]
17 ? S 0:00 [kacpid]
18 ? S 0:00 [kacpi_notify]
19 ? S 0:00 [kacpi_hotplug]
20 ? S 0:00 [ata_aux]
21 ? S 0:00 [ata_sff/0]
22 ? S 0:00 [ksuspend_usbd]
23 ? S 0:00 [khubd]
24 ? S 0:00 [kseriod]
25 ? S 0:00 [md/0]
26 ? S 0:00 [md_misc/0]
27 ? S 0:00 [linkwatch]
29 ? S 0:01 [khungtaskd]
30 ? S 10:44 [kswapd0]
31 ? SN 0:00 [ksmd]
32 ? SN 0:42 [khugepaged]
33 ? S 0:00 [aio/0]
34 ? S 0:00 [crypto/0]
42 ? S 0:00 [kthrotld/0]
43 ? S 0:00 [pciehpd]
45 ? S 0:00 [kpsmoused]
46 ? S 0:00 [usbhid_resumer]
47 ? S 0:00 [deferwq]
81 ? S 0:00 [kdmremove]
82 ? S 0:00 [kstriped]
143 ? S 0:00 [scsi_eh_0]
144 ? S 0:00 [scsi_eh_1]
151 ? S 0:00 [scsi_eh_2]
152 ? S 0:00 [vmw_pvscsi_wq_2]
276 ? S 0:00 [kdmflush]
278 ? S 0:00 [kdmflush]
301 ? S 2:23 [jbd2/dm-0-8]
302 ? S 0:00 [ext4-dio-unwrit]
383 ? S<s 0:00 /sbin/udevd -d
570 ? S 0:42 [vmmemctl]
668 ? S 0:00 [kdmflush]
669 ? S 0:00 [kdmflush]
672 ? S 0:00 [kdmflush]
706 ? S 2:10 [flush-253:2]
737 ? S 0:00 [jbd2/sda1-8]
738 ? S 0:00 [ext4-dio-unwrit]
739 ? S 2:57 [jbd2/dm-2-8]
740 ? S 0:00 [ext4-dio-unwrit]
741 ? S 0:05 [jbd2/dm-3-8]
742 ? S 0:00 [ext4-dio-unwrit]
743 ? S 0:04 [jbd2/dm-4-8]
744 ? S 0:00 [ext4-dio-unwrit]
779 ? S 0:04 [kauditd]
834 ? S 1:19 [flush-253:0]
1027 ? Ss 0:00 /sbin/dhclient -1 -q -cf
/etc/dhcp/dhclient-eth0.conf -
1078 ? S<sl 0:34 auditd
1094 ? Sl 1:35 /sbin/rsyslogd -i /var/run/syslogd.pid -c 5
1106 ? Ssl 0:01 dbus-daemon --system
1125 ? Ss 18:30 /usr/sbin/openvpn --daemon --writepid
/var/run/openvpn/
1132 ? S 0:06 /usr/sbin/openvpn --daemon --writepid
/var/run/openvpn/
1139 ? Ss 2:39 /usr/sbin/openvpn --daemon --writepid
/var/run/openvpn/
1168 ? Ssl 0:14 hald
1169 ? S 0:00 hald-runner
1198 ? S 0:00 hald-addon-input: Listening on /dev/input/event2
/dev/i
1216 ? S 0:00 hald-addon-acpi: listening on acpi kernel
interface /pr
1395 ? S 38:33 /usr/sbin/vmtoolsd
1427 ? Ss 0:31 /usr/sbin/sshd
1466 ? Ss 0:14 /usr/sbin/nrpe -c /etc/nagios/nrpe.cfg -d
1542 ? Ss 0:11 /usr/libexec/postfix/master
1552 ? S 0:06 qmgr -l -t fifo -u
1570 ? Ss 2:18 /usr/sbin/httpd
1592 ? Ssl 3:17 /usr/sbin/qpidd --data-dir /var/lib/qpidd
--daemon
1681 ? Ss 0:34 crond
1692 ? Ss 0:00 /usr/sbin/atd
1706 ? Ss 0:03 /usr/sbin/certmonger -S -p
/var/run/certmonger.pid
1714 ? Ss 0:50 /usr/bin/swatch --daemon --tail-args -F -n0 -c
/etc/swa
1715 ? S 0:04 /usr/bin/tail -F -n0 /var/log/httpd/error_log
1723 tty1 Ss+ 0:00 /sbin/mingetty /dev/tty1
1725 tty2 Ss+ 0:00 /sbin/mingetty /dev/tty2
1727 tty3 Ss+ 0:00 /sbin/mingetty /dev/tty3
1729 tty4 Ss+ 0:00 /sbin/mingetty /dev/tty4
1733 tty5 Ss+ 0:00 /sbin/mingetty /dev/tty5
1734 ? S< 0:00 /sbin/udevd -d
1735 ? S< 0:00 /sbin/udevd -d
1737 tty6 Ss+ 0:00 /sbin/mingetty /dev/tty6
2925 ? S 0:08 /usr/sbin/httpd
3565 ? Ss 0:00 postgres: po_user po_db 127.0.0.1(57240) idle
3719 ? Sl 3:54 wsgi-moin
3720 ? Sl 3:38 wsgi-moin
3721 ? Sl 3:19 wsgi-moin
3722 ? Sl 3:42 wsgi-moin
3723 ? Sl 3:20 wsgi-moin
6297 ? Ss 0:00 postgres: po_user po_db 127.0.0.1(57878) idle
10753 pts/0 T 0:00 top
11588 ? S 0:03 /usr/sbin/httpd
12120 ? S 0:03 /usr/sbin/httpd
12175 pts/0 S+ 0:00 psql -U babase_admin babase_vcf
12177 ? Ss 0:00 postgres: babase_admin babase_vcf ::1(34725) idle
12246 ? S 0:00 pickup -l -t fifo -u
12377 pts/3 S 0:00 sudo su -
12382 pts/3 S 0:00 su -
12383 pts/3 S 0:00 -bash
12699 ? Ss 0:00 sshd: kop [priv]
12702 ? S 0:00 sshd: kop@pts/3
12703 pts/3 Ss 0:00 -bash
13201 ? Z 0:00 [sh] <defunct>
13202 ? Z 0:00 [logger] <defunct>
13350 ? Z 0:00 [sh] <defunct>
13351 ? Z 0:00 [logger] <defunct>
13366 ? S 0:00 CROND
13368 ? Ss 0:00 /bin/sh -c moin --config-dir=/var/www/babasewiki
--wiki
13370 ? D 0:16 /usr/bin/python /usr/bin/moin
--config-dir=/var/www/bab
13385 ? S 0:00 /usr/sbin/httpd
13394 pts/3 R+ 0:00 ps ax
13691 pts/1 S+ 0:08 emacs -nw
16313 ? S 0:02 /usr/sbin/httpd
16446 ? S 0:08 /usr/sbin/httpd
16544 ? Ss 0:00 sshd: kop [priv]
16547 ? S 0:02 sshd: kop@pts/0
16548 pts/0 Ss 0:00 -bash
16647 ? S 0:02 /usr/sbin/httpd
17280 ? S 0:03 /usr/sbin/httpd
17402 ? D 0:02 /usr/sbin/httpd
17825 ? Ss 0:00 postgres: po_user po_db 127.0.0.1(57755) idle
18440 ? Ss 0:00 sshd: kop [priv]
18443 ? S 0:00 sshd: kop@pts/1
18444 pts/1 Ss 0:00 -bash
18850 ? Ss 0:00 sshd: kop [priv]
18858 ? S 0:00 sshd: kop@pts/2
18859 pts/2 Ss+ 0:00 -bash
19424 ? S 0:02 /usr/sbin/httpd
22473 ? Ss 0:00 postgres: po_user po_db 127.0.0.1(57834) idle
23522 ? Ss 0:00 SCREEN
23523 pts/5 Ss 0:00 /bin/bash
23538 pts/5 S+ 0:00 /bin/bash
23539 pts/5 S+ 0:00 gzip -d -c GATK_norefDB_samtools.vcf.gz
23540 pts/5 S+ 227:44 python3 /home/kop/babase/bin/chado-vcf-load
--dbname=ba
23544 ? Rs 4406:47 postgres: kop babase_vcf ::1(60529) SELECT
25102 ? Ss 0:00 postgres: po_user po_db 127.0.0.1(57836) idle
25181 ? Ss 0:00 postgres: po_user po_db 127.0.0.1(57838) idle
26002 ? S 0:46 /usr/pgsql-9.1/bin/postmaster -p 5432 -D
/var/lib/pgsql
26007 ? Ss 0:13 postgres: logger process
26009 ? Ss 2:38 postgres: writer process
26010 ? Ss 3:39 postgres: wal writer process
26011 ? Ss 5:35 postgres: autovacuum launcher process
26012 ? Ss 16:36 postgres: stats collector process
26611 ? Ss 0:00 postgres: po_user po_db 127.0.0.1(57840) idle
27108 pts/0 T 0:00 man ipset
27111 pts/0 T 0:00 sh -c (cd "/usr/share/man" && (echo ".pl 1100i";
/usr/b
27112 pts/0 T 0:00 sh -c (cd "/usr/share/man" && (echo ".pl 1100i";
/usr/b
27116 pts/0 T 0:00 /usr/bin/less -is
27718 pts/0 T 0:00 man iptables
27720 pts/0 T 0:00 sh -c /usr/bin/unlzma -c -d
/var/cache/man/cat8/iptable
27722 pts/0 T 0:00 /usr/bin/less -is
27824 pts/0 T 0:00 less /etc/init.d/iptables
28441 ? Ss 0:00 postgres: po_user po_db 127.0.0.1(57845) idle
28449 ? Ss 0:00 postgres: po_user po_db 127.0.0.1(57847) idle
29476 ? SN 11:35 /usr/bin/python /usr/bin/denyhosts.py --daemon
--config
===================================
babase_vcf=# select pid, locktype, database, pg_database.datname, count(*)
from pg_locks join pg_database on (pg_database.oid = pg_locks.database)
group by pid, locktype, database, pg_database.datname order by pid,
locktype, database;
pid | locktype | database | datname | count
-------+----------+----------+---------------------------+-------
3565 | page | 46044 | po_db | 5
3565 | relation | 46044 | po_db | 2
3565 | tuple | 46044 | po_db | 3
6297 | page | 46044 | po_db | 28
6297 | relation | 46044 | po_db | 16
6297 | tuple | 46044 | po_db | 59
12177 | relation | 3566740 | babase_vcf | 1
13419 | relation | 158366 | babase | 1
13424 | relation | 158366 | babase | 1
17060 | page | 3593095 | babase_test_chado_install | 160
17060 | tuple | 3593095 | babase_test_chado_install | 53
17151 | page | 158366 | babase | 178
17151 | relation | 158366 | babase | 18
17151 | tuple | 158366 | babase | 111
17172 | page | 158366 | babase | 44
17172 | relation | 158366 | babase | 6
17181 | page | 158366 | babase | 45
17181 | relation | 158366 | babase | 6
17181 | tuple | 158366 | babase | 1
17278 | page | 158366 | babase | 69
17278 | relation | 158366 | babase | 6
17278 | tuple | 158366 | babase | 8
17339 | page | 158366 | babase | 91
17339 | relation | 158366 | babase | 7
17339 | tuple | 158366 | babase | 24
17342 | page | 158366 | babase | 1
17342 | relation | 158366 | babase | 2
17360 | page | 158366 | babase | 1
17360 | tuple | 158366 | babase | 1
17366 | page | 158366 | babase | 1
17366 | tuple | 158366 | babase | 1
17368 | page | 158366 | babase | 44
17368 | relation | 158366 | babase | 5
17368 | tuple | 158366 | babase | 5
17396 | relation | 158366 | babase | 1
17401 | relation | 158366 | babase | 1
17411 | relation | 158366 | babase | 1
17432 | relation | 158366 | babase | 1
17447 | page | 158366 | babase | 61
17447 | relation | 158366 | babase | 10
17447 | tuple | 158366 | babase | 1
17466 | page | 158366 | babase | 68
17466 | relation | 158366 | babase | 10
17466 | tuple | 158366 | babase | 2
17504 | page | 158366 | babase | 85
17504 | relation | 158366 | babase | 14
17504 | tuple | 158366 | babase | 2
17514 | page | 158366 | babase | 133
17514 | relation | 158366 | babase | 26
17514 | tuple | 158366 | babase | 2
17530 | page | 158366 | babase | 68
17530 | relation | 158366 | babase | 10
17530 | tuple | 158366 | babase | 2
17576 | page | 158366 | babase | 4
17576 | tuple | 158366 | babase | 6
17584 | page | 158366 | babase | 4
17584 | tuple | 158366 | babase | 6
17588 | page | 158366 | babase | 21
17588 | relation | 158366 | babase | 4
17588 | tuple | 158366 | babase | 8
17607 | page | 158366 | babase | 83
17607 | relation | 158366 | babase | 10
17607 | tuple | 158366 | babase | 2
17712 | page | 158366 | babase | 76
17712 | relation | 158366 | babase | 10
17712 | tuple | 158366 | babase | 1
17715 | page | 158366 | babase | 120
17715 | relation | 158366 | babase | 10
17715 | tuple | 158366 | babase | 5
17718 | page | 158366 | babase | 107
17718 | relation | 158366 | babase | 10
17718 | tuple | 158366 | babase | 6
17723 | page | 158366 | babase | 120
17723 | relation | 158366 | babase | 10
17723 | tuple | 158366 | babase | 5
17825 | page | 46044 | po_db | 15
17825 | relation | 46044 | po_db | 5
17825 | tuple | 46044 | po_db | 7
17848 | page | 158366 | babase | 50
17848 | relation | 158366 | babase | 5
17864 | page | 158366 | babase | 50
17864 | relation | 158366 | babase | 5
17990 | relation | 861547 | babase_test | 1
17994 | page | 158366 | babase | 50
17994 | relation | 158366 | babase | 5
17999 | page | 158366 | babase | 50
17999 | relation | 158366 | babase | 5
18006 | page | 3593095 | babase_test_chado_install | 1
18028 | page | 3593095 | babase_test_chado_install | 9
18028 | relation | 3593095 | babase_test_chado_install | 2
18028 | tuple | 3593095 | babase_test_chado_install | 6
18055 | page | 158366 | babase | 50
18055 | relation | 158366 | babase | 5
18061 | page | 158366 | babase | 50
18061 | relation | 158366 | babase | 5
18069 | page | 158366 | babase | 50
18069 | relation | 158366 | babase | 5
18078 | page | 158366 | babase | 50
18078 | relation | 158366 | babase | 5
18099 | page | 158366 | babase | 50
18099 | relation | 158366 | babase | 5
18103 | page | 158366 | babase | 50
18103 | relation | 158366 | babase | 5
18703 | page | 3593095 | babase_test_chado_install | 327
18703 | relation | 3593095 | babase_test_chado_install | 66
18703 | tuple | 3593095 | babase_test_chado_install | 78
18715 | page | 3593095 | babase_test_chado_install | 3999
18715 | relation | 3593095 | babase_test_chado_install | 597
18715 | tuple | 3593095 | babase_test_chado_install | 929
19369 | page | 158366 | babase | 1
19369 | tuple | 158366 | babase | 1
19377 | page | 158366 | babase | 1
19377 | tuple | 158366 | babase | 1
19998 | page | 158366 | babase | 1
19998 | tuple | 158366 | babase | 1
22473 | page | 46044 | po_db | 16
22473 | relation | 46044 | po_db | 4
22473 | tuple | 46044 | po_db | 4
23544 | page | 3566740 | babase_vcf | 24
23544 | relation | 3566740 | babase_vcf | 77
23544 | tuple | 3566740 | babase_vcf | 23
25181 | page | 46044 | po_db | 19
25181 | relation | 46044 | po_db | 4
25181 | tuple | 46044 | po_db | 4
26611 | page | 46044 | po_db | 18
26611 | relation | 46044 | po_db | 2
26611 | tuple | 46044 | po_db | 8
| page | 46044 | po_db | 84
| page | 158366 | babase | 2252
| page | 3566740 | babase_vcf | 3
| page | 3593095 | babase_test_chado_install | 24
| relation | 46044 | po_db | 22
| relation | 158366 | babase | 73
| relation | 3566740 | babase_vcf | 9
| relation | 3593095 | babase_test_chado_install | 236
| tuple | 46044 | po_db | 121
| tuple | 158366 | babase | 1168
| tuple | 3566740 | babase_vcf | 24
| tuple | 3593095 | babase_test_chado_install | 23
(139 rows)
===================================
babase_test_chado_install=# select pg_locks.locktype, pg_database.datname,
pg_class.relname, pg_locks.page, pg_locks.tuple, pg_locks.virtualxid,
pg_locks.transactionid, pg_locks.classid, pg_locks.objid, pg_locks.objsubid,
pg_locks.virtualtransaction, pg_locks.pid, pg_locks.mode, pg_locks.granted
from pg_locks left outer join pg_database on (pg_database.oid =
pg_locks.database) left outer join pg_class on (pg_class.oid =
pg_locks.relation) where pid = 18715;
locktype | datname | relname
| page | tuple | virtualxid | transactionid | classid | objid | objsubid
| v
irtualtransaction | pid | mode | granted
----------+---------------------------+-----------------------------------------
----+------+-------+------------+---------------+---------+-------+----------+--
------------------+-------+------------+---------
page | babase_test_chado_install | dbxref_c1
| 5 | | | | | |
| 3
/127449 | 18715 | SIReadLock | t
relation | babase_test_chado_install | cvterm_idx3
| | | | | | |
| 3
/127401 | 18715 | SIReadLock | t
relation | babase_test_chado_install | cvterm_idx3
| | | | | | |
| 3
/127529 | 18715 | SIReadLock | t
page | babase_test_chado_install | cvtermsynonym_c1
| 1 | | | | | |
| 3
/127517 | 18715 | SIReadLock | t
page | babase_test_chado_install | dbxref
| 0 | | | | | |
| 3
/127537 | 18715 | SIReadLock | t
page | babase_test_chado_install |
cvtermprop_cvterm_id_type_id_value_rank_
key | 1 | | | | | |
| 3
/127379 | 18715 | SIReadLock | t
page | babase_test_chado_install |
cvtermprop_cvterm_id_type_id_value_rank_
key | 1 | | | | | |
| 3
/127564 | 18715 | SIReadLock | t
page | babase_test_chado_install | db_c1
| 1 | | | | | |
| 3
/127429 | 18715 | SIReadLock | t
page | babase_test_chado_install | db_c1
| 1 | | | | | |
| 3
/127557 | 18715 | SIReadLock | t
page | babase_test_chado_install | dbxref_pkey
| 2 | | | | | |
| 3
/127422 | 18715 | SIReadLock | t
page | babase_test_chado_install | dbxref_pkey
| 2 | | | | | |
| 3
/127550 | 18715 | SIReadLock | t
page | babase_test_chado_install | dbxref
| 1 | | | | | |
| 3
/127586 | 18715 | SIReadLock | t
page | babase_test_chado_install | cvterm_dbxref_c1
| 1 | | | | | |
| 3
/127420 | 18715 | SIReadLock | t
page | babase_test_chado_install | cvterm_dbxref_c1
| 1 | | | | | |
| 3
/127548 | 18715 | SIReadLock | t
page | babase_test_chado_install | dbxref_idx3
| 2 | | | | | |
| 3
/127494 | 18715 | SIReadLock | t
page | babase_test_chado_install | dbxref_idx3
| 2 | | | | | |
| 3
/127647 | 18715 | SIReadLock | t
page | babase_test_chado_install | cv_pkey
| 1 | | | | | |
| 3
/127466 | 18715 | SIReadLock | t
page | babase_test_chado_install | cv_pkey
| 1 | | | | | |
| 3
/127618 | 18715 | SIReadLock | t
page | babase_test_chado_install | cvtermsynonym_idx1
| 2 | | | | | |
| 3
/127670 | 18715 | SIReadLock | t
tuple | babase_test_chado_install | db
| 0 | 44 | | | | |
| 3
/127429 | 18715 | SIReadLock | t
tuple | babase_test_chado_install | db
| 0 | 44 | | | | |
| 3
/127557 | 18715 | SIReadLock | t
page | babase_test_chado_install | cvtermsynonym_c1
| 4 | | | | | |
| 3
/127471 | 18715 | SIReadLock | t
page | babase_test_chado_install | dbxref
| 3 | | | | | |
| 3
/127654 | 18715 | SIReadLock | t
relation | babase_test_chado_install | cvterm_pkey
| | | | | | |
| 3
/127489 | 18715 | SIReadLock | t
page | babase_test_chado_install | dbxref_pkey
| 1 | | | | | |
| 3
/127351 | 18715 | SIReadLock | t
page | babase_test_chado_install | dbxref_pkey
| 1 | | | | | |
| 3
/127479 | 18715 | SIReadLock | t
page | babase_test_chado_install | dbxref_pkey
| 1 | | | | | |
| 3
/127632 | 18715 | SIReadLock | t
page | babase_test_chado_install | dbxref_c1
| 1 | | | | | |
| 3
/127383 | 18715 | SIReadLock | t
tuple | babase_test_chado_install | dbxref
| 1 | 57 | | | | |
| 3
/127437 | 18715 | SIReadLock | t
page | babase_test_chado_install | dbxref
| 2 | | | | | |
| 3
/127545 | 18715 | SIReadLock | t
page | babase_test_chado_install | cvtermsynonym_idx1
| 1 | | | | | |
| 3
/127638 | 18715 | SIReadLock | t
page | babase_test_chado_install | dbxref_c1
| 2 | | | | | |
| 3
/127364 | 18715 | SIReadLock | t
page | babase_test_chado_install | db_pkey
| 1 | | | | | |
| 3
/127355 | 18715 | SIReadLock | t
page | babase_test_chado_install | db_pkey
| 1 | | | | | |
| 3
/127483 | 18715 | SIReadLock | t
page | babase_test_chado_install | db_pkey
| 1 | | | | | |
| 3
/127636 | 18715 | SIReadLock | t
tuple | babase_test_chado_install | cv
| 0 | 11 | | | | |
| 3
/127461 | 18715 | SIReadLock | t
tuple | babase_test_chado_install | cv
| 0 | 11 | | | | |
| 3
/127613 | 18715 | SIReadLock | t
page | babase_test_chado_install | dbxref_idx3
| 1 | | | | | |
| 3
/127508 | 18715 | SIReadLock | t
page | babase_test_chado_install | dbxref_idx3
| 1 | | | | | |
| 3
/127661 | 18715 | SIReadLock | t
page | babase_test_chado_install | dbxref_c1
| 4 | | | | | |
| 3
/127348 | 18715 | SIReadLock | t
page | babase_test_chado_install | dbxref_c1
| 5 | | | | | |
| 3
/127422 | 18715 | SIReadLock | t
relation | babase_test_chado_install | cvterm_idx3
| | | | | | |
| 3
/127374 | 18715 | SIReadLock | t
relation | babase_test_chado_install | cvterm_idx3
| | | | | | |
| 3
/127502 | 18715 | SIReadLock | t
relation | babase_test_chado_install | cvterm_idx3
| | | | | | |
| 3
/127655 | 18715 | SIReadLock | t
page | babase_test_chado_install | cvtermsynonym_c1
| 1 | | | | | |
| 3
/127490 | 18715 | SIReadLock | t
page | babase_test_chado_install | dbxref
| 0 | | | | | |
| 3
/127510 | 18715 | SIReadLock | t
page | babase_test_chado_install | dbxref
| 0 | | | | | |
| 3
/127663 | 18715 | SIReadLock | t
page | babase_test_chado_install |
cvtermprop_cvterm_id_type_id_value_rank_
key | 1 | | | | | |
| 3
/127352 | 18715 | SIReadLock | t
page | babase_test_chado_install |
cvtermprop_cvterm_id_type_id_value_rank_
key | 1 | | | | | |
| 3
/127633 | 18715 | SIReadLock | t
page | babase_test_chado_install | db_c1
| 1 | | | | | |
| 3
/127402 | 18715 | SIReadLock | t
page | babase_test_chado_install | db_c1
| 1 | | | | | |
| 3
/127530 | 18715 | SIReadLock | t
page | babase_test_chado_install | dbxref_pkey
| 2 | | | | | |
| 3
/127449 | 18715 | SIReadLock | t
page | babase_test_chado_install | dbxref_pkey
| 2 | | | | | |
| 3
/127560 | 18715 | SIReadLock | t
page | babase_test_chado_install | dbxref_pkey
| 2 | | | | | |
| 3
/127602 | 18715 | SIReadLock | t
tuple | babase_test_chado_install | dbxref
| 0 | 35 | | | | |
| 3
/127352 | 18715 | SIReadLock | t
page | babase_test_chado_install | dbxref
| 1 | | | | | |
| 3
/127534 | 18715 | SIReadLock | t
page | babase_test_chado_install | cvterm_dbxref_c1
| 1 | | | | | |
| 3
/127447 | 18715 | SIReadLock | t
page | babase_test_chado_install | cvterm_dbxref_c1
| 1 | | | | | |
| 3
/127585 | 18715 | SIReadLock | t
page | babase_test_chado_install | cvterm_dbxref_c1
| 1 | | | | | |
| 3
/127600 | 18715 | SIReadLock | t
page | babase_test_chado_install | dbxref_idx3
| 2 | | | | | |
| 3
/127521 | 18715 | SIReadLock | t
page | babase_test_chado_install | cv_pkey
| 1 | | | | | |
| 3
/127365 | 18715 | SIReadLock | t
page | babase_test_chado_install | cv_pkey
| 1 | | | | | |
| 3
/127493 | 18715 | SIReadLock | t
page | babase_test_chado_install | cv_pkey
| 1 | | | | | |
| 3
/127646 | 18715 | SIReadLock | t
<output deliberatly truncated here>
===================================
The long running transaction is in the babase_vcf db, started Mon,
2015-01-05.
Server pid 23544, client pid 23540.
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
23544 postgres 20 0 1272m 750m 735m R 96.4 40.0 4417:57 postmaster
23540 kop 20 0 236m 18m 2060 S 2.7 1.0 228:03.31 python3
This large transaction is attempting to load 727,163,525 bytes of text (a
VCF file) by parsing and inserting (using INSERT, via sqlalchemy) into about
10 tables, selecting from the 10 tables and 3 others for a total of 13
tables.
===================================
[root@papio ~]# cat /var/lib/pgsql/9.1/data/postgresql.conf
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The "=" is optional.) Whitespace may be used. Comments are introduced
with
# "#" anywhere on a line. The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default
values.
# Re-commenting a setting is NOT sufficient to revert it to the default
value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal. If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pg_ctl reload". Some
# parameters, which are marked below, require a server shutdown and restart
to
# take effect.
#
# Any parameter can also be given as a command-line option to the server,
e.g.,
# "postgres -c log_connections=on". Some parameters can be changed at run
time
# with the "SET" SQL command.
#
# Memory units: kB = kilobytes Time units: ms = milliseconds
# MB = megabytes s = seconds
# GB = gigabytes min = minutes
# h = hours
# d = days
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.
#data_directory = 'ConfigDir' # use data in another directory
# (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
# (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
# (change requires restart)
# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = '(none)' # write an extra PID file
# (change requires restart)
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
# (change requires restart)
## Listen on the vpn connection too.
#listen_addresses = 'localhost,172.16.3.1'
# Now that we're supporting ssl listen on all addresses.
listen_addresses = '*'
#port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
# Note: Increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction).
#superuser_reserved_connections = 3 # (change requires restart)
#unix_socket_directory = '' # (change requires restart)
#unix_socket_group = '' # (change requires restart)
#unix_socket_permissions = 0777 # begin with 0 to use octal notation
# (change requires restart)
unix_socket_permissions = 0770 # octal -- allow only Unix postgres and root
#bonjour = off # advertise server via Bonjour
# (change requires restart)
#bonjour_name = '' # defaults to the computer name
# (change requires restart)
# - Security and Authentication -
#authentication_timeout = 1min # 1s-600s
#ssl = off # (change requires restart)
ssl = on
#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' # allowed SSL ciphers
# (change requires restart)
#ssl_renegotiation_limit = 512MB # amount of data between renegotiations
#password_encryption = on
#db_user_namespace = off
# Kerberos and GSSAPI
#krb_server_keyfile = ''
#krb_srvname = 'postgres' # (Kerberos only)
#krb_caseins_users = off
# - TCP Keepalives -
# see "man 7 tcp" for details
#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
# 0 selects the system default
#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
# 0 selects the system default
#tcp_keepalives_count = 0 # TCP_KEEPCNT;
# 0 selects the system default
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
# - Memory -
#shared_buffers = 32MB # min 128kB
# # (change requires restart)
# Make shared buffers 40% of ram per the doc's recommendation.
# We've got 2G of ram, so...
shared_buffers = 800MB
#temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
# (change requires restart)
# Note: Increasing max_prepared_transactions costs ~600 bytes of shared
memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.
#work_mem = 1MB # min 64kB
work_mem = 80MB
#maintenance_work_mem = 16MB # min 1MB
maintenance_work_mem = 100MB
#max_stack_depth = 2MB # min 100kB
max_stack_depth = 8500kB # ulimit -s is 10240
# - Kernel Resource Usage -
#max_files_per_process = 1000 # min 25
# (change requires restart)
#shared_preload_libraries = '' # (change requires restart)
# - Cost-Based Vacuum Delay -
#vacuum_cost_delay = 0ms # 0-100 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 1-10000 credits
# - Background Writer -
#bgwriter_delay = 200ms # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers scanned/round
# - Asynchronous Behavior -
#effective_io_concurrency = 1 # 1-1000. 0 disables prefetching
effective_io_concurrency = 4 # Pretend we've 4 drives on the VM.
#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
# - Settings -
#wal_level = minimal # minimal, archive, or hot_standby
# (change requires restart)
#fsync = on # turns forced synchronization on or off
#synchronous_commit = on # synchronization level; on, off, or local
#wal_sync_method = fsync # the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync (default on Linux)
# fsync
# fsync_writethrough
# open_sync
#full_page_writes = on # recover from partial page writes
#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
# (change requires restart)
# VCF tuning #
# We need big wal buffers for mongo vcf file loading.
# However, 16MB is the segment size, at which a flush is forced.
# With concurrency it might be nice to have it larger, but
# at the moment we've only 2GB of ram so....
wal_buffers = 16MB
#wal_writer_delay = 200ms # 1-10000 milliseconds
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
# Cut down on log messages when copying databases.
#checkpoint_segments = 12
# VCF tuning #
checkpoint_segments = 32 # Every 512MB-ish
#checkpoint_timeout = 5min # range 30s-1h
# VCF tuning #
# Allow writing to go on more of the time.
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
checkpoint_completion_target = 0.85 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s # 0 disables
# - Archiving -
#archive_mode = off # allows archiving to be done
# (change requires restart)
#archive_command = '' # command to use to archive a logfile segment
#archive_timeout = 0 # force a logfile segment switch after this
# number of seconds; 0 disables
#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------
# - Master Server -
# These settings are ignored on a standby server
#max_wal_senders = 0 # max number of walsender processes
# (change requires restart)
#wal_sender_delay = 1s # walsender cycle time, 1-10000 milliseconds
#wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables
#vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed
#replication_timeout = 60s # in milliseconds; 0 disables
#synchronous_standby_names = '' # standby servers that provide sync rep
# comma-separated list of application_name
# from standby(s); '*' = all
# - Standby Servers -
# These settings are ignored on a master server
#hot_standby = off # "on" allows queries during recovery
# (change requires restart)
#max_standby_archive_delay = 30s # max delay before canceling queries
# when reading WAL from archive;
# -1 allows indefinite delay
#max_standby_streaming_delay = 30s # max delay before canceling queries
# when reading streaming WAL;
# -1 allows indefinite delay
#wal_receiver_status_interval = 10s # send replies at least this often
# 0 disables
#hot_standby_feedback = off # send info from standby to prevent
# query conflicts
#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
# - Planner Method Configuration -
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on
# - Planner Cost Constants -
#seq_page_cost = 1.0 # measured on an arbitrary scale
#random_page_cost = 4.0 # same scale as above
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
#effective_cache_size = 128MB
effective_cache_size = 600MB
# - Genetic Query Optimizer -
#geqo = on
#geqo_threshold = 12
#geqo_effort = 5 # range 1-10
#geqo_pool_size = 0 # selects default based on effort
#geqo_generations = 0 # selects default based on effort
#geqo_selection_bias = 2.0 # range 1.5-2.0
#geqo_seed = 0.0 # range 0.0-1.0
# - Other Planner Options -
#default_statistics_target = 100 # range 1-10000
# We don't update tables a lot, so make this bigger.
default_statistics_target = 1000
#constraint_exclusion = partition # on, off, or partition
#cursor_tuple_fraction = 0.1 # range 0.0-1.0
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit
# JOIN clauses
#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
# - Where to Log -
#log_destination = 'stderr' # Valid values are combinations of
# stderr, csvlog, syslog, and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.
log_destination = 'syslog' # See below
# This is used when logging to stderr:
logging_collector = on # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
# (change requires restart)
# These are only used if logging_collector is on:
log_directory = 'pg_log' # directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%a.log' # log file name pattern,
# can include strftime() escapes
#log_file_mode = 0600 # creation mode for log files,
# begin with 0 to use octal notation
log_truncate_on_rotation = on # If on, an existing log file with the
# same name as the new log file will be
# truncated rather than appended to.
# But such truncation only occurs on
# time-driven rotation, not on restarts
# or size-driven rotation. Default is
# off, meaning append to existing files
# in all cases.
log_rotation_age = 1d # Automatic rotation of logfiles will
# happen after that time. 0 disables.
log_rotation_size = 0 # Automatic rotation of logfiles will
# happen after that much log output.
# 0 disables.
# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
syslog_facility = 'LOCAL4' # Duke uses this
#syslog_ident = 'postgres'
#silent_mode = off # Run server silently.
# DO NOT USE without syslog or
# logging_collector
# (change requires restart)
# - When to Log -
#client_min_messages = notice # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# log
# notice
# warning
# error
#log_min_messages = warning # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic
#log_min_error_statement = error # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic (effectively off)
#log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements
# and their durations, > 0 logs only
# statements running at least this number
# of milliseconds
# - What to Log -
#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_error_verbosity = default # terse, default, or verbose messages
#log_hostname = off
#log_line_prefix = ''
log_line_prefix = '%t %d (%u) ' # special values:
# %a = application name
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = process ID
# %t = timestamp without milliseconds
# %m = timestamp with milliseconds
# %i = command tag
# %e = SQL state
# %c = session ID
# %l = session line number
# %s = session start timestamp
# %v = virtual transaction ID
# %x = transaction ID (0 if none)
# %q = stop here in non-session
# processes
# %% = '%'
# e.g. '<%u%%%d> '
#log_lock_waits = off # log lock waits >= deadlock_timeout
#log_statement = 'none' # none, ddl, mod, all
#log_temp_files = -1 # log temporary files equal or larger
# than the specified size in kilobytes;
# -1 disables, 0 logs all temp files
#log_timezone = '(defaults to server environment setting)'
#------------------------------------------------------------------------------
# RUNTIME STATISTICS
#------------------------------------------------------------------------------
# - Query/Index Statistics Collector -
#track_activities = on
#track_counts = on
#track_functions = none # none, pl, all
#track_activity_query_size = 1024 # (change requires restart)
#update_process_title = on
#stats_temp_directory = 'pg_stat_tmp'
# - Statistics Monitoring -
#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
#autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
# (change requires restart)
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before
vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before
analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced
vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
# - Statement Behavior -
#search_path = '"$user",public' # schema names
#default_tablespace = '' # a tablespace name, '' uses the default
#temp_tablespaces = '' # a list of tablespace names, '' uses
# only default tablespace
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
default_transaction_isolation = 'serializable'
#default_transaction_read_only = off
#default_transaction_deferrable = off
#session_replication_role = 'origin'
#statement_timeout = 0 # in milliseconds, 0 is disabled
#vacuum_freeze_min_age = 50000000
#vacuum_freeze_table_age = 150000000
#bytea_output = 'hex' # hex, escape
#xmlbinary = 'base64'
#xmloption = 'content'
# - Locale and Formatting -
datestyle = 'iso, mdy'
#intervalstyle = 'postgres'
#timezone = '(defaults to server environment setting)'
#timezone_abbreviations = 'Default' # Select the set of available time
zone
# abbreviations. Currently, there are
# Default
# Australia
# India
# You can create your own file in
# share/timezonesets/.
#extra_float_digits = 0 # min -15, max 3
#client_encoding = sql_ascii # actually, defaults to database
# encoding
# These settings are initialized by initdb, but they can be changed.
lc_messages = 'C' # locale for system error message
# strings
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C' # locale for number formatting
lc_time = 'C' # locale for time formatting
# default configuration for text search
default_text_search_config = 'pg_catalog.english'
# - Other Defaults -
#dynamic_library_path = '$libdir'
#local_preload_libraries = ''
#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------
#deadlock_timeout = 1s
#max_locks_per_transaction = 64 # min 10
# (change requires restart)
# Note: Each lock table slot uses ~270 bytes of shared memory, and there
are
# max_locks_per_transaction * (max_connections + max_prepared_transactions)
# lock table slots.
#max_pred_locks_per_transaction = 64 # min 10
# (change requires restart)
#------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------
# - Previous PostgreSQL Versions -
#array_nulls = on
#backslash_quote = safe_encoding # on, off, or safe_encoding
#default_with_oids = off
#escape_string_warning = on
#lo_compat_privileges = off
#quote_all_identifiers = off
#sql_inheritance = on
#standard_conforming_strings = on
#synchronize_seqscans = on
# - Other Platforms and Clients -
#transform_null_equals = off
#------------------------------------------------------------------------------
# ERROR HANDLING
#------------------------------------------------------------------------------
#exit_on_error = off # terminate session on any error?
#restart_after_crash = on # reinitialize after backend crash?
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
#custom_variable_classes = '' # list of custom variable class names
===================================
[root@papio ~]# cat /etc/sysctl.conf
# Kernel sysctl configuration file for Red Hat Linux
#
# For binary values, 0 is disabled, 1 is enabled. See sysctl(8) and
# sysctl.conf(5) for more details.
# Controls IP packet forwarding
net.ipv4.ip_forward = 0
# Controls source route verification
net.ipv4.conf.default.rp_filter = 1
# Do not accept source routing
net.ipv4.conf.default.accept_source_route = 0
# Controls the System Request debugging functionality of the kernel
kernel.sysrq = 0
# Controls whether core dumps will append the PID to the core filename.
# Useful for debugging multi-threaded applications.
kernel.core_uses_pid = 1
# Controls the use of TCP syncookies
net.ipv4.tcp_syncookies = 1
# Disable netfilter on bridges.
net.bridge.bridge-nf-call-ip6tables = 0
net.bridge.bridge-nf-call-iptables = 0
net.bridge.bridge-nf-call-arptables = 0
# Controls the default maxmimum size of a mesage queue
kernel.msgmnb = 65536
# Controls the maximum size of a message, in bytes
kernel.msgmax = 65536
# Controls the maximum shared segment size, in bytes
kernel.shmmax = 68719476736
# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 4294967296
Re: BUG #12469: pg_locks shows locks held by pids not found i n pg_stat_activity or ps
From
Kevin Grittner
Date:
"kop@meme.com" <kop@meme.com> wrote: > I found that after running a large transaction (for days, it's still > running) and then running out of shared memory that pg_locks reports locks > held by pids that do not seem to exist, either in ps output or in > pg_stat_activity. That is not a bug. For details see either of the below links: http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/storage/lmgr/README-SSI;hb=master http://vldb.org/pvldb/vol5/p1850_danrkports_vldb2012.pdf The short explanation is that SIReadLocks on a serializable transaction may need to be kept until overlapping transactions terminate. You can minimize this by flagging transactions which will not modify data as READ ONLY. If a READ ONLY transaction is expected to run for a very long time, it is wise to also flag it as DEFERRABLE. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: BUG #12469: pg_locks shows locks held by pids not found i n pg_stat_activity or ps
From
"Karl O. Pinc"
Date:
On Fri, 9 Jan 2015 18:07:35 +0000 (UTC) Kevin Grittner <kgrittn@ymail.com> wrote: > "kop@meme.com" <kop@meme.com> wrote: > > > I found that after running a large transaction (for days, it's still > > running) and then running out of shared memory that pg_locks > > reports locks held by pids that do not seem to exist, either in ps > > output or in pg_stat_activity. > > That is not a bug. For details see either of the below links: > > http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/storage/lmgr/README-SSI;hb=master > > http://vldb.org/pvldb/vol5/p1850_danrkports_vldb2012.pdf > > The short explanation is that SIReadLocks on a serializable > transaction may need to be kept until overlapping transactions > terminate. Ok. Thanks. The transactions/locks are in separate databases. I would think that in most cases that a transaction in one database cannot have any effect on a transaction in another database. It would be nice if pg were clever enough to isolate transactions within databases. Regards, Karl <kop@meme.com> Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein
Re: BUG #12469: pg_locks shows locks held by pids not found i n pg_stat_activity or ps
From
Kevin Grittner
Date:
Karl O. Pinc <kop@meme.com> wrote: > Kevin Grittner <kgrittn@ymail.com> wrote: >> The short explanation is that SIReadLocks on a serializable >> transaction may need to be kept until overlapping transactions >> terminate. > > The transactions/locks are in separate databases. I would think > that in most cases that a > transaction in one database cannot have any effect on a > transaction in another database. > > It would be nice if pg were clever enough to isolate transactions > within databases. Good point. That would complicate the logic a bit, but it might be worth it. I'll make a note to look at that as a potential enhancement. Thanks! -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: BUG #12469: pg_locks shows locks held by pids not found i n pg_stat_activity or ps
From
Marko Tiikkaja
Date:
On 2015-01-09 19:07, Kevin Grittner wrote: > "kop@meme.com" <kop@meme.com> wrote: >> I found that after running a large transaction (for days, it's still >> running) and then running out of shared memory that pg_locks reports locks >> held by pids that do not seem to exist, either in ps output or in >> pg_stat_activity. > > That is not a bug. I find that really surprising. What if the pid gets reused and you have entries in pg_locks with the pid that aren't actually held by that backend? That's enough to drive anyone having to debug that mess insane. Can we not clear the pids or something, at least at backend exit? .marko
Re: BUG #12469: pg_locks shows locks held by pids not found i n pg_stat_activity or ps
From
"Karl O. Pinc"
Date:
On Fri, 9 Jan 2015 18:38:36 +0000 (UTC)
Kevin Grittner <kgrittn@ymail.com> wrote:
> Karl O. Pinc <kop@meme.com> wrote:
> > It would be nice if pg were clever enough to isolate transactions
> > within databases.
>
> Good point. That would complicate the logic a bit, but it might be
> worth it. I'll make a note to look at that as a potential
> enhancement. Thanks!
Rather than figure it out dynamically at transaction commit/lock
release time you could have those operations that alter things
common to all dbs (the postgres db?) add a flag to the transaction
itself. Then the actual commit/lock release is fast. And it's
not (I suppose) all that often that things change that are
"cross-database data" so them down very slightly shouldn't
be an issue.
Just a thought. (I'd know whether it makes sense if I'd
looked at the code. ;-)
Thanks for the attention.
Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
Re: BUG #12469: pg_locks shows locks held by pids not found i n pg_stat_activity or ps
From
Kevin Grittner
Date:
Marko Tiikkaja <marko@joh.to> wrote: > On 2015-01-09 19:07, Kevin Grittner wrote: >> "kop@meme.com" <kop@meme.com> wrote: >>> I found that after running a large transaction (for days, it's still >>> running) and then running out of shared memory that pg_locks reports locks >>> held by pids that do not seem to exist, either in ps output or in >>> pg_stat_activity. >> >> That is not a bug. > > I find that really surprising. What if the pid gets reused and you have > entries in pg_locks with the pid that aren't actually held by that > backend? That's enough to drive anyone having to debug that mess insane. > > Can we not clear the pids or something, at least at backend exit? Indeed we could. There was some discussion of whether it would be better to leave the pid in pg_locks so it could be matched up against log entries which led up to the lock, or whether it should be removed in case it lasted long enough to survive PID wrap-around and thereby possibly confuse someone looking at the lock information. (It is just an "informational" column, not one used to manage the serializable logic, so its presence or absence doesn't affect the correctness of serializable behavior.) The argument that it was more useful to leave it there than to remove it held sway. It would be trivial to remove it on commit, but that would eliminate its usefulness where there isn't any possibility of ambiguity. To remove it on connection close would require scanning a list for matches, so I think it would add an O(N^2) performance hit to closing a connection. I guess the question is whether PID wraparound occurs while a single database transaction is open often enough to make it worthwhile add that overhead. Also keep in mind that there might still be server logs with the pid which could usefully be matched against this value, even after PID wraparound -- it's just that if the same pid had been reused for a new connection you would have an ambiguous reference. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company