Use up too much memory - Mailing list pgsql-admin

After upgrade from 9.2.8 to 9.3.5 using pg_upgrade, my system getting
VERY slow and use up so much memory.
My server (1) has 64GB and the system info is
uname -a
Linux xxxx server(1) #1 SMP Tue Sep 16 20:50:52 EDT 2014 x86_64 x86_64
x86_64 GNU/Linux
I noticed memory showing from top while the query running.
Here is my test.

explain analyze select count(1), archiveset from filemeta_archiveset
join file using(fileid) group by archiveset order by archiveset limit 100;
                                      QUERY PLAN


-------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=5066451.18..5066451.43 rows=100 width=4) (actual
time=433089.049..433089.099 rows=100 loops=1)
   ->  Sort  (cost=5066451.18..5066451.68 rows=200 width=4) (actual
time=433089.048..433089.069 rows=100 loops=1)
         Sort Key: filemeta_archiveset.archiveset
         Sort Method: top-N heapsort  Memory: 29kB
         ->  HashAggregate  (cost=5066441.53..5066443.53 rows=200
width=4) (actual time=433088.680..433088.794 rows=210 loops=1)
               ->  Merge Join  (cost=1.13..4585983.85 rows=96091536
width=4) (actual time=10.755..351990.060 rows=112546262 loops
=1)
                     Merge Cond: (file.fileid = filemeta_archiveset.fileid)
                     ->  Index Only Scan using pk_file on file
(cost=0.56..1210105.41 rows=60354056 width=4) (actual time=0.089.
.31370.848 rows=69534634 loops=1)
                           Heap Fetches: 10109
                     ->  Index Only Scan using pk_filemeta_archiveset on
filemeta_archiveset  (cost=0.57..2023849.11 rows=9609153
6 width=8) (actual time=10.659..204479.160 rows=112546262 loops=1)
                           Heap Fetches: 20838
 Total runtime: 433089.229 ms
top
RES, SHR %MEM keep increasing up to 7g/7g/11%.

Running the same query in omitestdb1 that has 16GM memory.
RES, %MEM keep increasing up to 4g/25%, but SHR stay around the same 21m.

--------------
When I tested another server(2) that has 16GB memory
 uname -a
Linux server (2) #1 SMP Tue Sep 16 20:50:52 EDT 2014 x86_64 x86_64
x86_64 GNU/Linux
Running the same query in omitestdb1 that has 16GM memory.
RES, %MEM keep increasing up to 4g/25%, but SHR stay around the same 21m.

explain analyze select count(1), archiveset from filemeta_archiveset
join file using(fileid) group by archiveset order by archiveset limit 100;

                              QUERY
PLAN


----------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=6913959.86..6913960.11 rows=100 width=4) (actual
time=838028.991..838029.165 rows=100 loops=1)
   ->  Sort  (cost=6913959.86..6913960.36 rows=200 width=4) (actual
time=838028.987..838029.052 rows=100 loops=1)
         Sort Key: filemeta_archiveset.archiveset
         Sort Method: top-N heapsort  Memory: 29kB
         ->  HashAggregate  (cost=6913950.21..6913952.21 rows=200
width=4) (actual time=838020.068..838020.273 rows=210 loops=1)
               ->  Hash Join  (cost=1661635.32..6385260.00
rows=105738042 width=4) (actual time=159685.489..719192.506
rows=111667154 loops=1)
                     Hash Cond: (filemeta_archiveset.fileid = file.fileid)
                     ->  Seq Scan on filemeta_archiveset
(cost=0.00..1551483.42 rows=105738042 width=8) (actual
time=6.108..88091.149 rows=111667154 loops=1)
                     ->  Hash  (cost=1492080.70..1492080.70
rows=13564370 width=4) (actual time=159664.278..159664.278 rows=68663927
loops=1)
                           Buckets: 2097152  Batches: 2 (originally 1)
Memory Usage: 2097153kB
                           ->  Seq Scan on file  (cost=0.00..1492080.70
rows=13564370 width=4) (actual time=0.071..67769.429 rows=68663927 loops=1)
 Total runtime: 838239.912 ms

My question is why SHR say the same in server(2) but not server(1).

May be we load the whole library into memory when the query run.


I had google the definition of the term top saying:
VIRT stands for the virtual size of a process, which is the sum of
memory it is actually using, memory it has mapped into itself (for
instance the video card’s RAM for the X server), files on disk that have
been mapped into it (most notably shared libraries), and memory shared
with other processes. VIRT represents how much memory the program is
able to access at the present moment.

RES stands for the resident size, which is an accurate representation of
how much actual physical memory a process is consuming. (This also
corresponds directly to the %MEM column.) This will virtually always be
less than the VIRT size, since most programs depend on the C library.

SHR indicates how much of the VIRT size is actually sharable (memory or
libraries). In the case of libraries, it does not necessarily mean that
the entire library is resident. For example, if a program only uses a
few functions in a library, the whole library is mapped and will be
counted in VIRT and SHR, but only the parts of the library file
containing the functions being used will actually be loaded in and be
counted under RES.

--
Best regards,

Alex Lai  (:-)
OMP SIPS DBA ADNET Systems, Inc.
7515 Mission Drive,
Suite A100 Lanham, MD 20706
301 352-4657 (phone)
301 352-0437 (fax)
mlai@sesda3.com