Skip site navigation (1) Skip section navigation (2)

Re: Deleted WAL files held open by backends in Linux

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Deleted WAL files held open by backends in Linux
Date: 2009-11-30 19:37:40
Message-ID: 4B13CAA4020000250002CDDF@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2009-11-30 19:46:36
Subject: Re: Deleted WAL files held open by backends in Linux
Previous:From: Peter EisentrautDate: 2009-11-30 19:27:37
Subject: Re: [PATCH] Add solaris path for docbook COLLATEINDEX

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group