Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> You sure it's not creating any temp tables? You didn't mention
> revoking TEMP privilege.
They have not been revoked, but I am sure the software publisher
doesn't explicitly create any, and I'd be very surprised if the
monitoring software did. The tables are small enough that it's hard
to believe that the 50MB work_mem would spill to disk, either (if
that matters).
> I can think of one code path that could result in a genuinely
> read-only session having to write WAL: if it's forced to flush
> dirty buffers in order to read in other pages, and such a buffer
> was dirtied by as-yet-uncommitted transactions, it might have to
> flush WAL to be allowed to write the dirty buffer. But I think
> you'd have had to dial back the bgwriter to the point of
> uselessness before this would be a common occurrence.
#bgwriter_delay = 200ms
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 4.0
>> At a minimum, we should add the extra 16MB per connection that
>> might be taken on the WAL file system to the calculations people
>> should do when sizing that, just in case someone is trying to
>> cut that thin while planning on using a lot of connections.
>
> In the first place, this is a complete non-issue except on Windows
> --- on other platforms we can rename and recycle the files even
> if they're being held open. I rather doubt anyone would think
> they could predict a Windows machine's disk usage that accurately
> anyway. In the second place, for each backend to be holding open
> a different dead WAL file strains the limits of credulity.
> Telling people to assume 16MB * max_connections would be a gross
> overestimate.
Pretty much every read only JDBC connection seems to be holding open
a deleted WAL file on my Linux box, but it would take pretty
pessimal timing for each connection to be holding open a different
one -- I see that many connections share a deleted WAL file.
project-db:~ # cat /proc/version
Linux version 2.6.16.60-0.39.3-smp (geeko(at)buildhost) (gcc version 4.1.2
20070115 (SUSE Linux)) #1 SMP Mon May 11 11:46:34 UTC 2009
project-db:~ # lsof | grep deleted
postgres 2189 ccefcirsa 20u REG 8,3
16777216 1610613340
/var/pgsql/data/ccefcir/pg_xlog/000000010000000A00000010 (deleted)
postgres 2195 ccefcirsa 43u REG 8,3
16777216 1610613340
/var/pgsql/data/ccefcir/pg_xlog/000000010000000A00000010 (deleted)
postgres 2511 jdashcirsa 1u CHR 136,5
7 /dev/pts/5 (deleted)
postgres 2511 jdashcirsa 2u CHR 136,5
7 /dev/pts/5 (deleted)
postgres 2514 jdashcirsa 3u REG 8,3
16777216 1610631538
/var/pgsql/data/jdashcir/pg_xlog/000000010000001100000076 (deleted)
postgres 2812 ccsa 3u REG 8,3
16777216 3763808807
/var/pgsql/data/epayment/pg_xlog/000000020000009B00000094 (deleted)
postgres 3647 ccefsa 1u CHR 136,5
7 /dev/pts/5 (deleted)
postgres 3647 ccefsa 2u CHR 136,5
7 /dev/pts/5 (deleted)
postgres 3650 ccefsa 3u REG 8,3
16777216 669441097
/var/pgsql/data/ccef/pg_xlog/000000020000004B000000C5 (deleted)
postgres 4266 milwaukeeifsa 3u REG 8,3
16777216 2708846433
/var/pgsql/data/milwaukeeif/pg_xlog/000000020000006B00000014 (deleted)
postgres 4270 juryscansa 3u REG 8,3
16777216 1073742165
/var/pgsql/data/juryscan/pg_xlog/000000020000001A000000DB (deleted)
postgres 6100 juryscansa 41u REG 8,3
16777216 1073742165
/var/pgsql/data/juryscan/pg_xlog/000000020000001A000000DB (deleted)
postgres 6105 juryscansa 35u REG 8,3
16777216 1073742167
/var/pgsql/data/juryscan/pg_xlog/000000020000001A0000007E (deleted)
postgres 6113 juryscansa 43u REG 8,3
16777216 1073742166
/var/pgsql/data/juryscan/pg_xlog/000000020000001A0000001D (deleted)
postgres 6119 juryscansa 59u REG 8,3
16777216 1073742165
/var/pgsql/data/juryscan/pg_xlog/000000020000001A000000DB (deleted)
postgres 6121 juryscansa 62u REG 8,3
16777216 1073742165
/var/pgsql/data/juryscan/pg_xlog/000000020000001A000000DB (deleted)
postgres 6126 juryscansa 33u REG 8,3
16777216 1073742167
/var/pgsql/data/juryscan/pg_xlog/000000020000001A0000007E (deleted)
postgres 7997 washrptcirsa 13u REG 8,3
16777216 2688967689
/var/pgsql/data/washrptcir/pg_xlog/000000010000002B000000E4 (deleted)
postgres 8170 washrptcirsa 23u REG 8,3
16777216 2688967689
/var/pgsql/data/washrptcir/pg_xlog/000000010000002B000000E4 (deleted)
postgres 8687 washrptcirsa 11u REG 8,3
16777216 2688967689
/var/pgsql/data/washrptcir/pg_xlog/000000010000002B000000E4 (deleted)
postgres 8720 washrptcirsa 3u REG 8,3
16777216 2688967689
/var/pgsql/data/washrptcir/pg_xlog/000000010000002B000000E4 (deleted)
postgres 8769 washrptcirsa 6u REG 8,3
16777216 2688967689
/var/pgsql/data/washrptcir/pg_xlog/000000010000002B000000E4 (deleted)
postgres 10093 washrptcirsa 18u REG 8,3
16777216 2688967689
/var/pgsql/data/washrptcir/pg_xlog/000000010000002B000000E4 (deleted)
postgres 10313 ccsagsa 3u REG 8,3
16777216 3758753714
/var/pgsql/data/ccsag/pg_xlog/0000000300000035000000EE (deleted)
postgres 10660 datasourcesa 50u REG 8,3
16777216 3250939657
/var/pgsql/data/datasource/pg_xlog/0000000100000004000000C9 (deleted)
postgres 10661 datasourcesa 23u REG 8,3
16777216 3250939657
/var/pgsql/data/datasource/pg_xlog/0000000100000004000000C9 (deleted)
postgres 10666 datasourcesa 20u REG 8,3
16777216 3250939679
/var/pgsql/data/datasource/pg_xlog/0000000100000004000000B3 (deleted)
postgres 10667 datasourcesa 20u REG 8,3
16777216 3250939679
/var/pgsql/data/datasource/pg_xlog/0000000100000004000000B3 (deleted)
postgres 11049 formssa 3u REG 8,3
16777216 1879057748
/var/pgsql/data/forms/pg_xlog/000000010000000B000000C1 (deleted)
postgres 11209 milwaukeeifsa 19u REG 8,3
16777216 2690371279
/var/pgsql/data/milwaukeeif/pg_xlog/000000020000006900000018 (deleted)
postgres 11217 milwaukeeifsa 131u REG 8,3
16777216 2690371279
/var/pgsql/data/milwaukeeif/pg_xlog/0000000200000069000000DE (deleted)
postgres 11221 milwaukeeifsa 34u REG 8,3
16777216 2690371282
/var/pgsql/data/milwaukeeif/pg_xlog/000000020000006A00000051 (deleted)
postgres 11223 milwaukeeifsa 16u REG 8,3
16777216 2690371282
/var/pgsql/data/milwaukeeif/pg_xlog/000000020000006A00000051 (deleted)
postgres 11659 jdashccsa 6u REG 8,3
16777216 401111458
/var/pgsql/data/jdashcc/pg_xlog/000000020000006400000080 (deleted)
postgres 12949 jdashccsa 22u REG 8,3
16777216 268437280
/var/pgsql/data/jdashcc/pg_xlog/0000000200000065000000BE (deleted)
postgres 12976 formssa 31u REG 8,3
16777216 1879057738
/var/pgsql/data/forms/pg_xlog/000000010000000F00000082 (deleted)
postgres 12978 formssa 23u REG 8,3
16777216 1879057738
/var/pgsql/data/forms/pg_xlog/000000010000000F00000082 (deleted)
postgres 12980 formssa 34u REG 8,3
16777216 1879057738
/var/pgsql/data/forms/pg_xlog/000000010000000F00000082 (deleted)
postgres 12992 formssa 35u REG 8,3
16777216 1879057738
/var/pgsql/data/forms/pg_xlog/000000010000000F00000082 (deleted)
postgres 12993 formssa 25u REG 8,3
16777216 1879057748
/var/pgsql/data/forms/pg_xlog/000000010000000E000000F4 (deleted)
postgres 12994 formssa 24u REG 8,3
16777216 1879057738
/var/pgsql/data/forms/pg_xlog/000000010000000F00000082 (deleted)
postgres 14359 jdashccsa 22u REG 8,3
16777216 401111459
/var/pgsql/data/jdashcc/pg_xlog/0000000200000065000000BD (deleted)
postgres 15397 washrptcirsa 26u REG 8,3
16777216 2688967664
/var/pgsql/data/washrptcir/pg_xlog/000000010000002900000079 (deleted)
postgres 16422 ccsagsa 11u REG 8,3
16777216 3758531497
/var/pgsql/data/ccsag/pg_xlog/0000000300000030000000ED (deleted)
postgres 16490 q409sa 3u REG 8,3
16777216 3490994896
/var/pgsql/data/codesq409/pg_xlog/000000010000000B00000056 (deleted)
postgres 16533 q309sa 3u REG 8,3
16777216 1073749053
/var/pgsql/data/codesq309/pg_xlog/0000000100000007000000A4 (deleted)
postgres 16721 ccsa 27u REG 8,3
16777216 3763808825
/var/pgsql/data/epayment/pg_xlog/000000020000009B00000068 (deleted)
postgres 16725 ccsa 25u REG 8,3
16777216 3763808807
/var/pgsql/data/epayment/pg_xlog/000000020000009B00000094 (deleted)
postgres 16729 ccsa 17u REG 8,3
16777216 3763808824
/var/pgsql/data/epayment/pg_xlog/000000020000009A000000F0 (deleted)
postgres 16734 ccsa 34u REG 8,3
16777216 3763808807
/var/pgsql/data/epayment/pg_xlog/000000020000009B000000FA (deleted)
postgres 16741 ccsa 18u REG 8,3
16777216 3763808825
/var/pgsql/data/epayment/pg_xlog/000000020000009B00000068 (deleted)
postgres 16747 ccsa 38u REG 8,3
16777216 3763808807
/var/pgsql/data/epayment/pg_xlog/000000020000009B000000FA (deleted)
postgres 18316 ccefsa 13u REG 8,3
16777216 669441097
/var/pgsql/data/ccef/pg_xlog/000000020000004B000000C5 (deleted)
postgres 20568 washrptsa 3u REG 8,3
16777216 1463
/var/pgsql/data/washrpt/pg_xlog/000000020000004700000011 (deleted)
postgres 23040 milwaukeeifsa 47u REG 8,3
16777216 2690371282
/var/pgsql/data/milwaukeeif/pg_xlog/000000020000006A00000051 (deleted)
nscd 23041 root 7u REG 8,2
217016 1459569 /var/run/nscd/dbnP5QN9 (deleted)
nscd 23041 root 8r REG 8,2
217016 1459569 /var/run/nscd/dbnP5QN9 (deleted)
postgres 23322 ccefcirsa 1u CHR 136,0
2 /dev/pts/0 (deleted)
postgres 23322 ccefcirsa 2u CHR 136,0
2 /dev/pts/0 (deleted)
postgres 23339 milwaukeeifsa 136u REG 8,3
16777216 2708846433
/var/pgsql/data/milwaukeeif/pg_xlog/000000020000006B00000014 (deleted)
postgres 23603 jawebsa 1u CHR 136,5
7 /dev/pts/5 (deleted)
postgres 23603 jawebsa 2u CHR 136,5
7 /dev/pts/5 (deleted)
postgres 24367 mssa 34u REG 8,3
16777216 805314300 /var/pgsql/data/ms/pg_xlog/000000010000000A00000058
(deleted)
postgres 24368 mssa 37u REG 8,3
16777216 805314303 /var/pgsql/data/ms/pg_xlog/000000010000000A00000059
(deleted)
postgres 24923 encryptsa 335u REG 8,3
16777216 805310179
/var/pgsql/data/encrypt/pg_xlog/000000020000003E00000023 (deleted)
postgres 24925 encryptsa 27u REG 8,3
16777216 805310179
/var/pgsql/data/encrypt/pg_xlog/000000020000003E00000023 (deleted)
postgres 24931 encryptsa 16u REG 8,3
16777216 805310179
/var/pgsql/data/encrypt/pg_xlog/000000020000003E00000023 (deleted)
postgres 24933 encryptsa 26u REG 8,3
16777216 805310179
/var/pgsql/data/encrypt/pg_xlog/000000020000003E00000023 (deleted)
postgres 24942 encryptsa 16u REG 8,3
16777216 805310179
/var/pgsql/data/encrypt/pg_xlog/000000020000003E00000023 (deleted)
postgres 26267 washrptcirsa 1u CHR 136,3
5 /dev/pts/3 (deleted)
postgres 26267 washrptcirsa 2u CHR 136,3
5 /dev/pts/3 (deleted)
postgres 26270 washrptcirsa 3u REG 8,3
16777216 2688967703
/var/pgsql/data/washrptcir/pg_xlog/000000010000002B00000070 (deleted)
postgres 27403 milwaukeeifsa 30u REG 8,3
16777216 2708846433
/var/pgsql/data/milwaukeeif/pg_xlog/000000020000006B00000014 (deleted)
postgres 28916 encryptsa 3u REG 8,3
16777216 805314462
/var/pgsql/data/encrypt/pg_xlog/000000020000003C000000E8 (deleted)
postgres 29217 mssa 3u REG 8,3
16777216 805314300 /var/pgsql/data/ms/pg_xlog/000000010000000A00000010
(deleted)
postgres 30151 datasourcesa 3u REG 8,3
16777216 3250939659
/var/pgsql/data/datasource/pg_xlog/000000010000000400000094 (deleted)
I'll test a bit to see what it takes to get a JDBC connection to
open a WAL file for a connection.
-Kevin