Thread: why so many open files?
i just checked what process uses the most file descriptors on my system.
it's postgresql backend. but there is something wrong:
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
pgdba 20845 0.0 2.8 57976 29160 ? Ss May22 2:20 postgres: jabberd jabberd 127.0.0.1(58449) idle
apparently it is postgresql backend started in jabberd database.
but this is list of open files:
lr-x------ 1 pgdba pgdba 64 2008-06-02 14:34 0 -> /dev/null
l-wx------ 1 pgdba pgdba 64 2008-06-02 14:34 1 -> pipe:[15372378]
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 10 -> /home/pgdba/data/base/16712/2603
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 100 -> /home/pgdba/data/base/135358/158068
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 101 -> /home/pgdba/data/base/135358/158076
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 102 -> /home/pgdba/data/base/160080/160682
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 103 -> /home/pgdba/data/base/135358/158050
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 104 -> /home/pgdba/data/base/135358/158058
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 105 -> /home/pgdba/data/base/135358/157910
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 106 -> /home/pgdba/data/base/16712/16763
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 107 -> /home/pgdba/data/base/16712/16802
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 108 -> /home/pgdba/data/base/16712/16773
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 109 -> /home/pgdba/data/base/16712/16737
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 11 -> /home/pgdba/data/base/16712/2653
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 110 -> /home/pgdba/data/base/16712/16806
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 111 -> /home/pgdba/data/base/135358/157939
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 112 -> /home/pgdba/data/base/135358/157926
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 113 -> /home/pgdba/data/base/160080/2841
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 114 -> /home/pgdba/data/base/160080/1259
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 115 -> /home/pgdba/data/base/135358/157875
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 116 -> /home/pgdba/data/base/135358/157899
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 117 -> /home/pgdba/data/base/135358/157885
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 118 -> /home/pgdba/data/base/135358/157924
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 119 -> /home/pgdba/data/base/160080/160134
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 12 -> /home/pgdba/data/base/16712/2655
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 120 -> /home/pgdba/data/base/91886/92009
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 121 -> /home/pgdba/data/base/135358/157883
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 122 -> /home/pgdba/data/base/135358/158014
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 123 -> /home/pgdba/data/base/135358/158022
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 124 -> /home/pgdba/data/base/160080/160133
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 125 -> /home/pgdba/data/base/135358/157888
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 126 -> /home/pgdba/data/base/16712/16748
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 127 -> /home/pgdba/data/base/16712/16798
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 128 -> /home/pgdba/data/base/160080/160127
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 129 -> /home/pgdba/data/base/160080/161583
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 13 -> /home/pgdba/data/base/16712/2662
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 130 -> /home/pgdba/data/base/135358/157901
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 131 -> /home/pgdba/data/base/91886/91935
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 132 -> /home/pgdba/data/base/91886/91946
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 133 -> /home/pgdba/data/base/135358/157860
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 134 -> /home/pgdba/data/base/135358/158032
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 135 -> /home/pgdba/data/base/135358/158040
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 136 -> /home/pgdba/data/base/91886/92011
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 137 -> /home/pgdba/data/base/91886/92067
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 138 -> /home/pgdba/data/base/91886/91943
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 139 -> /home/pgdba/data/base/91886/91981
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 14 -> /home/pgdba/data/base/16712/2659
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 140 -> /home/pgdba/data/base/91886/92168
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 141 -> /home/pgdba/data/base/160080/160687
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 142 -> /home/pgdba/data/base/135358/159586
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 143 -> /home/pgdba/data/base/16712/16740
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 144 -> /home/pgdba/data/base/135358/159687
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 145 -> /home/pgdba/data/base/16712/16796
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 146 -> /home/pgdba/data/base/160080/160821
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 147 -> /home/pgdba/data/base/91743/91769
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 148 -> /home/pgdba/data/base/91743/91810
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 149 -> /home/pgdba/data/base/91743/91746
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 15 -> /home/pgdba/data/base/16712/2679
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 150 -> /home/pgdba/data/base/91743/91832
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 151 -> /home/pgdba/data/base/91743/91820
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 152 -> /home/pgdba/data/base/91743/91761
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 153 -> /home/pgdba/data/base/91743/91759
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 154 -> /home/pgdba/data/base/91743/91812
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 155 -> /home/pgdba/data/base/91743/91857
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 156 -> /home/pgdba/data/base/91743/91834
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 157 -> /home/pgdba/data/base/91743/91850
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 158 -> /home/pgdba/data/base/160080/160866
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 159 -> /home/pgdba/data/base/135358/159584
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 16 -> /home/pgdba/data/base/16712/2701
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 160 -> /home/pgdba/data/base/135358/159595
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 161 -> /home/pgdba/data/base/135358/159607
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 162 -> /home/pgdba/data/base/135358/159614
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 17 -> /home/pgdba/data/base/16712/2620
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 18 -> /home/pgdba/data/base/16712/2684
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 19 -> /home/pgdba/data/base/16712/2615
l-wx------ 1 pgdba pgdba 64 2008-06-02 14:34 2 -> pipe:[15372378]
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 20 -> /home/pgdba/data/base/16712/2685
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 21 -> /home/pgdba/data/base/16712/2663
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 22 -> /home/pgdba/data/base/16712/2691
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 23 -> /home/pgdba/data/base/16712/1255
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 24 -> /home/pgdba/data/base/16712/2690
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 25 -> /home/pgdba/data/base/16712/2703
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 26 -> /home/pgdba/data/base/16712/1247
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 27 -> /home/pgdba/data/base/16712/2689
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 28 -> /home/pgdba/data/base/16712/2617
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 29 -> /home/pgdba/data/base/16712/2688
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 3 -> /home/pgdba/data/base/16712/1259
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 30 -> /home/pgdba/data/base/16712/2661
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 31 -> /home/pgdba/data/base/16712/2605
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 32 -> /home/pgdba/data/base/16712/16778
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 33 -> /home/pgdba/data/base/16712/2696
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 34 -> /home/pgdba/data/base/16712/2678
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 35 -> /home/pgdba/data/base/16712/16808
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 36 -> /home/pgdba/data/base/16712/2654
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 37 -> /home/pgdba/data/base/16712/2686
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 38 -> /home/pgdba/data/base/16712/2616
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 39 -> /home/pgdba/data/base/16712/16758
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 4 -> /home/pgdba/data/base/16712/1249
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 40 -> /home/pgdba/data/base/16712/16800
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 41 -> /home/pgdba/data/base/16712/2687
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 42 -> /home/pgdba/data/base/16712/16768
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 43 -> /home/pgdba/data/base/16712/16804
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 44 -> /home/pgdba/data/base/16712/2619
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 45 -> /home/pgdba/data/base/16712/16783
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 46 -> /home/pgdba/data/base/16712/16810
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 47 -> /home/pgdba/data/base/160080/2840
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 48 -> /home/pgdba/data/base/16712/16787
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 49 -> /home/pgdba/data/pg_xlog/000000010000000800000046
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 5 -> /home/pgdba/data/base/16712/2610
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 50 -> /home/pgdba/data/base/16712/2656
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 51 -> /home/pgdba/data/base/16712/2604
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 52 -> /home/pgdba/data/base/16712/16733
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 53 -> /home/pgdba/data/base/16712/16785
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 54 -> /home/pgdba/data/base/160080/160837
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 55 -> /home/pgdba/data/base/16712/2682
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 56 -> /home/pgdba/data/base/16712/2612
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 57 -> /home/pgdba/data/base/16712/16716
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 58 -> /home/pgdba/data/base/135358/159696
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 59 -> /home/pgdba/data/base/160080/161575
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 6 -> /home/pgdba/data/base/16712/2601
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 60 -> /home/pgdba/data/base/16712/2704
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 61 -> /home/pgdba/data/base/160080/161163
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 62 -> /home/pgdba/data/base/160080/160138
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 63 -> /home/pgdba/data/base/160080/160125
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 64 -> /home/pgdba/data/base/135358/159697
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 65 -> /home/pgdba/data/base/91886/92045
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 66 -> /home/pgdba/data/base/160080/160114
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 67 -> /home/pgdba/data/base/16712/16723
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 68 -> /home/pgdba/data/base/160080/161155
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 69 -> /home/pgdba/data/base/16712/16792
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 7 -> socket:[15372383]
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 70 -> /home/pgdba/data/base/160080/161142
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 71 -> /home/pgdba/data/base/160080/2696
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 72 -> /home/pgdba/data/base/135358/157961
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 73 -> /home/pgdba/data/base/160080/160674
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 74 -> /home/pgdba/data/base/160080/160833
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 75 -> /home/pgdba/data/base/160080/160267
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 76 -> /home/pgdba/data/base/135358/157873
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 77 -> /home/pgdba/data/base/91886/91968
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 78 -> /home/pgdba/data/base/91886/91887
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 79 -> /home/pgdba/data/base/160080/160832
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 8 -> socket:[15372648]
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 80 -> /home/pgdba/data/base/160080/160672
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 81 -> /home/pgdba/data/base/160080/160830
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 82 -> /home/pgdba/data/base/160080/160262
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 83 -> /home/pgdba/data/base/160080/160831
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 84 -> /home/pgdba/data/base/160080/161153
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 85 -> /home/pgdba/data/base/160080/160144
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 86 -> /home/pgdba/data/base/91886/92065
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 87 -> /home/pgdba/data/base/160080/161582
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 88 -> /home/pgdba/data/base/160080/161162
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 89 -> /home/pgdba/data/base/160080/160681
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 9 -> /home/pgdba/data/base/16712/2602
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 90 -> /home/pgdba/data/base/91886/92068
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 91 -> /home/pgdba/data/base/91886/92070
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 92 -> /home/pgdba/data/base/91886/91889
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 93 -> /home/pgdba/data/base/91886/91899
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 94 -> /home/pgdba/data/base/91886/91901
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 95 -> /home/pgdba/data/base/16712/16739
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 96 -> /home/pgdba/data/base/91886/91955
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 97 -> /home/pgdba/data/base/135358/157912
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 98 -> /home/pgdba/data/base/160080/2619
lrwx------ 1 pgdba pgdba 64 2008-06-02 14:34 99 -> /home/pgdba/data/base/135358/157937
hmm .. it looks bad. let's check which databases this particular backend has opened files from:
=> ls -l /proc/20845/fd/* | grep ' -> /home/pgdba/data/base/' | awk -F/ '{print $(NF-1)}' | sort | uniq -c
31 135358
34 160080
63 16712
11 91743
18 91886
these databases are:
# select oid, datname from pg_database where oid in (135358, 160080, 16712, 91743, 91886);
oid | datname
--------+---------------
16712 | jabberd
135358 | allegro
160080 | blipgo
91743 | roundcubemail
91886 | rt
(5 rows)
hmm ... why is it so?
why backend process from one database has opened files from other databases?
Best regards,
depesz
hubert depesz lubaczewski <depesz@depesz.com> writes:
> why backend process from one database has opened files from other databases?
That's not very surprising at all: a backend might have to write out a
dirty buffer in order to reclaim the buffer for re-use, and which
database the page is from doesn't enter into that.
What does seem surprising is that it's had to do that quite a lot.
Is this an old PG version without a bgwriter process? In a properly
functioning recent release, I'd expect only the bgwriter to have a
really eclectic mix of open files.
regards, tom lane
On Mon, Jun 02, 2008 at 09:38:29AM -0400, Tom Lane wrote:
> That's not very surprising at all: a backend might have to write out a
> dirty buffer in order to reclaim the buffer for re-use, and which
> database the page is from doesn't enter into that.
> What does seem surprising is that it's had to do that quite a lot.
> Is this an old PG version without a bgwriter process? In a properly
> functioning recent release, I'd expect only the bgwriter to have a
> really eclectic mix of open files.
# select version();
version
-----------------------------------------------------------------------------------------------------------------------
PostgreSQL 8.2.7 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)
(1 row)
# select name, setting from pg_settings where name ~ 'bgw';
name | setting
-----------------------+---------
bgwriter_all_maxpages | 5
bgwriter_all_percent | 0.333
bgwriter_delay | 200
bgwriter_lru_maxpages | 5
bgwriter_lru_percent | 1
(5 rows)
should i show any more information?
regards,
depesz