From domenico@xtecnica.com Fri Jun 11 13:55:56 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 324ABD1B1A1 for ; Fri, 11 Jun 2004 10:55:56 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 22232-06 for ; Fri, 11 Jun 2004 13:55:58 +0000 (GMT) Received: from xtecnica.com (host154-218.pool80206.interbusiness.it [80.206.218.154]) by svr1.postgresql.org (Postfix) with ESMTP id 55BA8D1B1B9 for ; Fri, 11 Jun 2004 10:55:50 -0300 (ADT) Received: from DOMENICO2K by xtecnica.com (MDaemon.PRO.v7.1.0.R) with ESMTP id md50000021078.msg for ; Fri, 11 Jun 2004 15:53:59 +0200 Message-ID: <003901c44fbb$a6f02090$70c8007e@xtecnica.it> Reply-To: "Domenico Sgarbossa" From: "Domenico Sgarbossa" To: Subject: Problems with vacuum! Date: Fri, 11 Jun 2004 15:54:44 +0200 Organization: XTECNICA srl MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0036_01C44FCC.6A2FB290" X-Priority: 1 X-MSMail-Priority: High X-Mailer: Microsoft Outlook Express 5.50.4927.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4927.1200 X-Authenticated-Sender: domenico@xtecnica.com X-Spam-Processed: xtecnica.com, Fri, 11 Jun 2004 15:53:59 +0200 (not processed: message from valid local sender) X-MDRemoteIP: 126.0.200.112 X-Return-Path: domenico@xtecnica.com X-MDaemon-Deliver-To: pgsql-performance@postgresql.org X-MDAV-Processed: xtecnica.com, Fri, 11 Jun 2004 15:54:00 +0200 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.2 tagged_above=0.0 required=5.0 tests=HTML_50_60, HTML_MESSAGE, X_MSMAIL_PRIORITY_HIGH, X_PRIORITY_HIGH X-Spam-Level: * X-Archive-Number: 200406/125 X-Sequence-Number: 7183 This is a multi-part message in MIME format. ------=_NextPart_000_0036_01C44FCC.6A2FB290 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'm running postgrSQL 7.2 on a linux Red Hat 8.0 box with 2GB of RAM=20 When I boot-up the system , this is the TOP situation: 11:59am up 4 min, 1 user, load average: 0.37, 0.26, 0.11 77 processes: 74 sleeping, 3 running, 0 zombie, 0 stopped CPU states: 0.3% user, 0.7% system, 0.0% nice, 98.8% idle Mem: 1031020K av, 177808K used, 853212K free, 0K shrd, 14744K bu= ff Swap: 2096472K av, 0K used, 2096472K free 67828K ca= ched After I've done a vacuum , the situation is: =20 12:04pm up 8 min, 1 user, load average: 0.22, 0.23, 0.12 78 processes: 76 sleeping, 2 running, 0 zombie, 0 stopped CPU states: 2.5% user, 1.9% system, 0.0% nice, 95.4% idle Mem: 1031020K av, 1016580K used, 14440K free, 0K shrd, 18624K bu= ff Swap: 2096472K av, 0K used, 2096472K free 833896K ca= ched As you see the memory used by vacuum isn't released anymore? Anyone know wh= y? My statement is: vacuumdb --analyze dbname My pg paramaters on postgresql.conf are: # # Shared Memory Size # shared_buffers =3D 2048 # 2*max_connections, min 16 max_fsm_relations =3D 100 # min 10, fsm is free space map max_fsm_pages =3D 10000 # min 1000, fsm is free space map max_locks_per_transaction =3D 64 # min 10 wal_buffers =3D 8 # min 4 # # Non-shared Memory Sizes # sort_mem =3D 512 # min 32 vacuum_mem =3D 8192 # min 1024 Did anyone know why this happend?=20 Distinti Saluti Sgarbossa Domenico X Tecnica S.R.L. www.xtecnica.com Tel: 049/9409154 - 049/5970297 Fax: 049/9400288 ------=_NextPart_000_0036_01C44FCC.6A2FB290 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I'm running postgrSQL 7.2 on a linux Red H= at 8.0=20 box with 2GB of RAM
When I boot-up the system , this is the TO= P=20 situation:

11:59am  up 4 min,  1 user,  load average: 0.37, 0.26,=20 0.11

77 processes= : 74=20 sleeping, 3 running, 0 zombie, 0 stopped

CPU states:<= SPAN=20 style=3D"mso-spacerun: yes">  0.3% user,  0.7% system,  0.0% nice, 98.8%=20 idle

Mem:  1031020K av,  177808K used,  853212K free,       0K= =20 shrd,   14744K=20 buff

Swap: 209647= 2K=20 av,      = =20 0K used, 2096472K free        = ;          =20 67828K cached

After I've done a vacuum , the situation= =20 is:
 
&n= bsp;

 12:04pm  up 8 min,  1 user,  load average: 0.22, 0.23,=20 0.12

78 processes= : 76=20 sleeping, 2 running, 0 zombie, 0 stopped

CPU states:<= SPAN=20 style=3D"mso-spacerun: yes">  2.5% user,  1.9% system,  0.0% nice, 95.4%=20 idle

Mem:  1031020K av, 1016580K used,   14440K free,       0K= =20 shrd,   18624K=20 buff

Swap: 209647= 2K=20 av,      = =20 0K used, 2096472K free        = ;         =20 833896K cached

As you see the memory used by vacuum isn't released anymore? Anyone kn= ow=20 why?
 
My statement is: vacuumdb --analyze dbname
My pg paramaters on postgresql.conf are:
 
#
#       Shared Memory=20 Size
#
shared_buffers =3D 2048      &nb= sp; #=20 2*max_connections, min 16
max_fsm_relations =3D 100    # = min 10,=20 fsm is free space map
max_fsm_pages =3D 10000    &nb= sp; #=20 min 1000, fsm is free space map
max_locks_per_transaction =3D 64 # min= =20 10
wal_buffers =3D=20 8            # min= =20 4
 
#
#       Non-shared Memory=20 Sizes
#
sort_mem =3D=20 512            = #=20 min 32
vacuum_mem =3D=20 8192          # min 1024
Did anyone know why this happend?

Distinti Saluti
 
Sgarbossa Domenico
X Tecnica S.R.L.
= www.xtecnica.com
Tel: 049/9409154 -= =20 049/5970297
Fax: 049/9400288
------=_NextPart_000_0036_01C44FCC.6A2FB290-- From revoohc@sermonaudio.com Fri Jun 11 14:40:13 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4B738D1B1A1 for ; Fri, 11 Jun 2004 11:40:13 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 52277-05 for ; Fri, 11 Jun 2004 14:40:08 +0000 (GMT) Received: from mail.astudios.com (unknown [66.49.77.3]) by svr1.postgresql.org (Postfix) with ESMTP id 9024BD1B169 for ; Fri, 11 Jun 2004 11:39:59 -0300 (ADT) thread-index: AcRPwplQC8MaU931TJOaNVqM6SmWlA== Received: from [10.4.2.252] ([66.194.26.150]) by mail.astudios.com with Microsoft SMTPSVC(6.0.3790.0); Fri, 11 Jun 2004 10:44:28 -0400 Message-ID: <40C9C43F.2060402@sermonaudio.com> Content-Class: urn:content-classes:message Importance: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.132 Date: Fri, 11 Jun 2004 10:39:59 -0400 From: "Chris Hoover" User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7) Gecko/20040514 X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Domenico Sgarbossa" Cc: Subject: Re: Problems with vacuum! References: <003901c44fbb$a6f02090$70c8007e@xtecnica.it> In-Reply-To: <003901c44fbb$a6f02090$70c8007e@xtecnica.it> Content-Type: text/plain; charset="ISO-8859-1"; format=flowed Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 11 Jun 2004 14:44:28.0421 (UTC) FILETIME=[991BEB50:01C44FC2] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/126 X-Sequence-Number: 7184 Domenico Sgarbossa wrote: > I'm running postgrSQL 7.2 on a linux Red Hat 8.0 box with 2GB of RAM > When I boot-up the system , this is the TOP situation: > > 11:59am up 4 min, 1 user, load average: 0.37, 0.26, 0.11 > > 77 processes: 74 sleeping, 3 running, 0 zombie, 0 stopped > > CPU states: 0.3% user, 0.7% system, 0.0% nice, 98.8% idle > > Mem: 1031020K av, 177808K used, 853212K free, 0K shrd, > 14744K buff > > Swap: 2096472K av, 0K used, 2096472K free > 67828K cached > > After I've done a vacuum , the situation is: > > > > 12:04pm up 8 min, 1 user, load average: 0.22, 0.23, 0.12 > > 78 processes: 76 sleeping, 2 running, 0 zombie, 0 stopped > > CPU states: 2.5% user, 1.9% system, 0.0% nice, 95.4% idle > > Mem: 1031020K av, 1016580K used, 14440K free, 0K shrd, > 18624K buff > > Swap: 2096472K av, 0K used, 2096472K free > 833896K cached > > As you see the memory used by vacuum isn't released anymore? Anyone > know why? > > My statement is: vacuumdb --analyze dbname > My pg paramaters on postgresql.conf are: > > # > # Shared Memory Size > # > shared_buffers = 2048 # 2*max_connections, min 16 > max_fsm_relations = 100 # min 10, fsm is free space map > max_fsm_pages = 10000 # min 1000, fsm is free space map > max_locks_per_transaction = 64 # min 10 > wal_buffers = 8 # min 4 > > # > # Non-shared Memory Sizes > # > sort_mem = 512 # min 32 > vacuum_mem = 8192 # min 1024 > Did anyone know why this happend? > > Distinti Saluti > > Sgarbossa Domenico > X Tecnica S.R.L. > www.xtecnica.com > Tel: 049/9409154 - 049/5970297 > Fax: 049/9400288 Your vacuum memory was released. However, the linux kernel likes to keep most memory in the cached buffer instead of totally freeing it. The guru's behind the kernel figured out they get better performance this way. HTH, Chris From domenico@xtecnica.com Fri Jun 11 14:54:30 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 83382D1B169 for ; Fri, 11 Jun 2004 11:54:30 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 52888-10 for ; Fri, 11 Jun 2004 14:54:28 +0000 (GMT) Received: from xtecnica.com (host154-218.pool80206.interbusiness.it [80.206.218.154]) by svr1.postgresql.org (Postfix) with ESMTP id 2BFE2D1B18F for ; Fri, 11 Jun 2004 11:54:23 -0300 (ADT) Received: from DOMENICO2K by xtecnica.com (MDaemon.PRO.v7.1.0.R) with ESMTP id md50000021096.msg for ; Fri, 11 Jun 2004 16:52:34 +0200 Message-ID: <004c01c44fc3$d7cdeaf0$70c8007e@xtecnica.it> Reply-To: "Domenico Sgarbossa" From: "Domenico Sgarbossa" To: "Rosser Schwarz" , References: <003c01c44fbd$c84a6af0$2500fa0a@CardServices.TCI.com> Subject: Re: [BULK] Problems with vacuum! Date: Fri, 11 Jun 2004 16:53:22 +0200 Organization: XTECNICA srl MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 1 X-MSMail-Priority: High X-Mailer: Microsoft Outlook Express 5.50.4927.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4927.1200 X-Authenticated-Sender: domenico@xtecnica.com X-Spam-Processed: xtecnica.com, Fri, 11 Jun 2004 16:52:34 +0200 (not processed: message from valid local sender) X-MDRemoteIP: 126.0.200.112 X-Return-Path: domenico@xtecnica.com X-MDaemon-Deliver-To: pgsql-performance@postgresql.org X-MDAV-Processed: xtecnica.com, Fri, 11 Jun 2004 16:52:35 +0200 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.0 tagged_above=0.0 required=5.0 tests=X_MSMAIL_PRIORITY_HIGH, X_PRIORITY_HIGH X-Spam-Level: * X-Archive-Number: 200406/127 X-Sequence-Number: 7185 Thanks for your advice! As I immagine the system released dinamically the shared memory .... the problem is that when , after vacuum, users runs my application (ERP) and the system swap on disk so the global performance decrease very fast until the system is so slow that I need to reboot the server. It seems that the cached memory isn't released by the system... so the system begin to swap to disk! The swap problem is very important, it makes useless run vacuum 'cause after vacuum the system begin to swap.... so my application runs very slow! Could you explain me better your final advice please? > Watching this "cached" value over normal usage of your machine to get > a running average is how you come up with your effective_cache_size > configuration directive. what does this mean pratically? thanks! Distinti Saluti Sgarbossa Domenico X Tecnica S.R.L. www.xtecnica.com Tel: 049/9409154 - 049/5970297 Fax: 049/9400288 ----- Original Message ----- From: "Rosser Schwarz" To: "'Domenico Sgarbossa'" ; Sent: Friday, June 11, 2004 4:09 PM Subject: RE: [BULK] [PERFORM] Problems with vacuum! > while you weren't looking, Domenico Sgarbossa wrote: > > Compare the "cached" values in each run of top. > > Before: 67828K cached > > After: 833896K cached > > The kernel is caching your tables for you. That memory isn't actually > being -used-, in the active, "a process has claimed this memory" sense. > If a process comes along that needs more memory than free, the kernel > will flush some of those cached pages (to swap?) to make room for > whatever the memory requirements of the newcomer are. > > Watching this "cached" value over normal usage of your machine to get > a running average is how you come up with your effective_cache_size > configuration directive. > > /rls > > -- > Rosser Schwarz > Total Card, Inc. > > > From doug@mcnaught.org Fri Jun 11 15:03:56 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1EC7ED1B1CB for ; Fri, 11 Jun 2004 12:03:56 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 61164-08 for ; Fri, 11 Jun 2004 15:03:54 +0000 (GMT) Received: from main.gmane.org (main.gmane.org [80.91.224.249]) by svr1.postgresql.org (Postfix) with ESMTP id A959ED1B1A1 for ; Fri, 11 Jun 2004 12:03:53 -0300 (ADT) Received: from list by main.gmane.org with local (Exim 3.35 #1 (Debian)) id 1BYnZP-0000mO-00 for ; Fri, 11 Jun 2004 17:03:51 +0200 Received: from sp-260-1.net4.netcentrix.net ([4.21.254.118]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Fri, 11 Jun 2004 17:03:51 +0200 Received: from doug by sp-260-1.net4.netcentrix.net with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Fri, 11 Jun 2004 17:03:51 +0200 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-performance@postgresql.org From: Doug McNaught Subject: Re: [BULK] Problems with vacuum! Date: Fri, 11 Jun 2004 11:03:48 -0400 Lines: 14 Message-ID: <87pt8616i3.fsf@asmodeus.mcnaught.org> References: <003c01c44fbd$c84a6af0$2500fa0a@CardServices.TCI.com> <004c01c44fc3$d7cdeaf0$70c8007e@xtecnica.it> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@sea.gmane.org X-Gmane-NNTP-Posting-Host: sp-260-1.net4.netcentrix.net User-Agent: Gnus/5.1002 (Gnus v5.10.2) Emacs/20.7 (gnu/linux) Cancel-Lock: sha1:y4KkfkG8JUBSW+SnmQbUzOZ/omQ= X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/128 X-Sequence-Number: 7186 "Domenico Sgarbossa" writes: > Thanks for your advice! > > It seems that the cached memory isn't released by the system... so the > system begin to swap to disk! If you really think this is true, there should be a process that is holding on to the memory. Use 'ps' to find that process. I also noticed that your shared_buffers setting seemed a bit low--you might want to increase it. -Doug From dmh@airdefense.net Mon Jun 14 15:26:07 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8A0FBD1B175 for ; Mon, 14 Jun 2004 12:26:07 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 08536-08 for ; Mon, 14 Jun 2004 15:26:02 +0000 (GMT) Received: from airdefense.net (unknown [64.25.5.183]) by svr1.postgresql.org (Postfix) with ESMTP id C40E6D1B169 for ; Mon, 14 Jun 2004 12:26:00 -0300 (ADT) Received: from ([172.16.0.100]) by ironmail.airdefense.net with ESMTP ; Mon, 14 Jun 2004 11:25:35 -0400 Received: from kaos.securitymatrix.com ([172.16.0.101]) by localhost.localdomain (8.11.6/8.11.6) with ESMTP id i5ED6ls15979 for ; Mon, 14 Jun 2004 09:06:47 -0400 Subject: Re: [BULK] Problems with vacuum! From: Dawn Hollingsworth To: pgsql-performance@postgresql.org In-Reply-To: <87pt8616i3.fsf@asmodeus.mcnaught.org> References: <003c01c44fbd$c84a6af0$2500fa0a@CardServices.TCI.com> <004c01c44fc3$d7cdeaf0$70c8007e@xtecnica.it> <87pt8616i3.fsf@asmodeus.mcnaught.org> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 (1.0.8-9.7x.1) Date: 14 Jun 2004 11:24:07 -0400 Message-Id: <1087226647.1775.14.camel@kaos> Mime-Version: 1.0 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/146 X-Sequence-Number: 7204 We actually found that in the 2.4-20 kernel for RedHat there was a known issue that was causing cached memory to not be reused and our box started to swap also. http://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=89226 This may be what you are experiencing if your using the same kernel. Dawn Hollingsworth Senior Director R&D AirDefense, Inc. On Fri, 2004-06-11 at 11:03, Doug McNaught wrote: > "Domenico Sgarbossa" writes: > > > Thanks for your advice! > > > > It seems that the cached memory isn't released by the system... so the > > system begin to swap to disk! > From domenico@xtecnica.com Fri Jun 18 13:55:47 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C72CFD1B4E5 for ; Fri, 18 Jun 2004 10:55:47 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 30042-09 for ; Fri, 18 Jun 2004 13:55:43 +0000 (GMT) Received: from xtecnica.com (host154-218.pool80206.interbusiness.it [80.206.218.154]) by svr1.postgresql.org (Postfix) with ESMTP id 79088D1B267 for ; Fri, 18 Jun 2004 10:55:37 -0300 (ADT) Received: from DOMENICO2K by xtecnica.com (MDaemon.PRO.v7.1.0.R) with ESMTP id md50000022350.msg for ; Fri, 18 Jun 2004 15:54:48 +0200 Message-ID: <011101c4553b$c1967130$70c8007e@xtecnica.it> Reply-To: "Domenico Sgarbossa" From: "Domenico Sgarbossa" To: References: <003c01c44fbd$c84a6af0$2500fa0a@CardServices.TCI.com><004c01c44fc3$d7cdeaf0$70c8007e@xtecnica.it> <87pt8616i3.fsf@asmodeus.mcnaught.org> <1087226647.1775.14.camel@kaos> Subject: Re: [BULK] Problems with vacuum! Date: Fri, 18 Jun 2004 15:54:20 +0200 Organization: XTECNICA srl MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 1 X-MSMail-Priority: High X-Mailer: Microsoft Outlook Express 5.50.4927.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4927.1200 X-Authenticated-Sender: domenico@xtecnica.com X-Spam-Processed: xtecnica.com, Fri, 18 Jun 2004 15:54:48 +0200 (not processed: message from valid local sender) X-MDRemoteIP: 126.0.200.112 X-Return-Path: domenico@xtecnica.com X-MDaemon-Deliver-To: pgsql-performance@postgresql.org X-MDAV-Processed: xtecnica.com, Fri, 18 Jun 2004 15:54:52 +0200 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.0 tagged_above=0.0 required=5.0 tests=X_MSMAIL_PRIORITY_HIGH, X_PRIORITY_HIGH X-Spam-Level: * X-Archive-Number: 200406/188 X-Sequence-Number: 7246 The problems still remains... I've tried to change shmax to 128 mb (i've got 2 GB of ram), then the others parameter are set as follow: shared_buffers = 8096 # 2*max_connections, min 16 max_fsm_relations = 500 # min 10, fsm is free space map max_fsm_pages = 15000 # min 1000, fsm is free space map max_locks_per_transaction = 64 # min 10 wal_buffers = 8 # min 4 # # Non-shared Memory Sizes # sort_mem = 1024 # min 32 vacuum_mem = 16384 # min 1024 I've scheduled 2 tasks nightly: vacuumdb --analyze dbname pg_dump -x -f dbname.dmp dbname so when the users go home, i've got something like 15/20000kb free ram, the rest is cached and 0kb of swap... It seems that when pg_dump starts the cached memory isn't released so the system begin to swap, then the system does the same with vacuum..... even if there 1.8 gb of cached ram (that is'nt released anymore....) very strange! anyone could explain mw why this happend? Distinti Saluti Sgarbossa Domenico X Tecnica S.R.L. www.xtecnica.com Tel: 049/9409154 - 049/5970297 Fax: 049/9400288 From tgl@sss.pgh.pa.us Fri Jun 18 15:11:10 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3732CD1B188 for ; Fri, 18 Jun 2004 12:11:10 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 93573-06 for ; Fri, 18 Jun 2004 15:11:05 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id E033AD1B170 for ; Fri, 18 Jun 2004 12:11:04 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i5IFB4MP020555; Fri, 18 Jun 2004 11:11:04 -0400 (EDT) To: "Domenico Sgarbossa" Cc: pgsql-performance@postgresql.org Subject: Re: [BULK] Problems with vacuum! In-reply-to: <011101c4553b$c1967130$70c8007e@xtecnica.it> References: <003c01c44fbd$c84a6af0$2500fa0a@CardServices.TCI.com><004c01c44fc3$d7cdeaf0$70c8007e@xtecnica.it> <87pt8616i3.fsf@asmodeus.mcnaught.org> <1087226647.1775.14.camel@kaos> <011101c4553b$c1967130$70c8007e@xtecnica.it> Comments: In-reply-to "Domenico Sgarbossa" message dated "Fri, 18 Jun 2004 15:54:20 +0200" Date: Fri, 18 Jun 2004 11:11:03 -0400 Message-ID: <20554.1087571463@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/193 X-Sequence-Number: 7251 "Domenico Sgarbossa" writes: > so when the users go home, i've got something like 15/20000kb free ram, the > rest is cached and 0kb of swap... > It seems that when pg_dump starts the cached memory isn't released so the > system begin to swap, A sane kernel should drop disk buffers rather than swapping. We heard recently about a bug in some versions of the Linux kernel that cause it to prefer swapping to discarding disk cache, though. It sounds like that's what you're hitting. Look into newer kernels ... regards, tom lane From smarlowe@qwest.net Fri Jun 18 18:06:38 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1A5EBD1B37D for ; Fri, 18 Jun 2004 15:06:38 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 12581-07 for ; Fri, 18 Jun 2004 18:06:36 +0000 (GMT) Received: from mpls-qmqp-03.inet.qwest.net (mpls-qmqp-03.inet.qwest.net [63.231.195.114]) by svr1.postgresql.org (Postfix) with SMTP id 7C44FD1B345 for ; Fri, 18 Jun 2004 15:06:33 -0300 (ADT) Received: (qmail 16678 invoked by uid 0); 18 Jun 2004 17:37:57 -0000 Received: from mpls-pop-03.inet.qwest.net (63.231.195.3) by mpls-qmqp-03.inet.qwest.net with QMQP; 18 Jun 2004 17:37:57 -0000 Received: from 63-227-127-37.dnvr.qwest.net (HELO ?10.0.0.2?) (63.227.127.37) by mpls-pop-03.inet.qwest.net with SMTP; 18 Jun 2004 18:06:33 -0000 Date: Fri, 18 Jun 2004 12:07:20 -0600 Message-Id: <1087582040.28062.13.camel@localhost.localdomain> From: "Scott Marlowe" To: "Tom Lane" Cc: "Domenico Sgarbossa" , pgsql-performance@postgresql.org Subject: Re: [BULK] Problems with vacuum! In-Reply-To: <20554.1087571463@sss.pgh.pa.us> References: <003c01c44fbd$c84a6af0$2500fa0a@CardServices.TCI.com> <004c01c44fc3$d7cdeaf0$70c8007e@xtecnica.it> <87pt8616i3.fsf@asmodeus.mcnaught.org> <1087226647.1775.14.camel@kaos> <011101c4553b$c1967130$70c8007e@xtecnica.it> <20554.1087571463@sss.pgh.pa.us> Content-Type: text/plain Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/200 X-Sequence-Number: 7258 On Fri, 2004-06-18 at 09:11, Tom Lane wrote: > "Domenico Sgarbossa" writes: > > so when the users go home, i've got something like 15/20000kb free ram, the > > rest is cached and 0kb of swap... > > It seems that when pg_dump starts the cached memory isn't released so the > > system begin to swap, > > A sane kernel should drop disk buffers rather than swapping. We heard > recently about a bug in some versions of the Linux kernel that cause it > to prefer swapping to discarding disk cache, though. It sounds like > that's what you're hitting. Look into newer kernels ... This was a common problem in the linux 2.4 series kernels, but has supposedly been fixed in the 2.6 kernels. Having lots of memory and turning off swap will "fix" the problem in 2.4, but if you run out of real mem, you're hosed. From jd@commandprompt.com Fri Jun 18 18:48:54 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AE68AD1B19C for ; Fri, 18 Jun 2004 15:48:54 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 33025-07 for ; Fri, 18 Jun 2004 18:48:51 +0000 (GMT) Received: from hosting.commandprompt.com (128.commandprompt.com [207.173.200.128]) by svr1.postgresql.org (Postfix) with ESMTP id B446AD1B18F for ; Fri, 18 Jun 2004 15:48:49 -0300 (ADT) Received: from [192.168.1.80] (dsl093-038-087.pdx1.dsl.speakeasy.net [66.93.38.87]) (authenticated) by hosting.commandprompt.com (8.11.6/8.11.6) with ESMTP id i5IImdo16120; Fri, 18 Jun 2004 11:48:39 -0700 Message-ID: <40D338BC.7080705@commandprompt.com> Date: Fri, 18 Jun 2004 11:47:24 -0700 From: "Joshua D. Drake" Organization: Command Prompt, Inc. User-Agent: Mozilla Thunderbird 0.6 (X11/20040502) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Scott Marlowe Cc: Tom Lane , Domenico Sgarbossa , pgsql-performance@postgresql.org Subject: Re: [BULK] Problems with vacuum! References: <003c01c44fbd$c84a6af0$2500fa0a@CardServices.TCI.com> <004c01c44fc3$d7cdeaf0$70c8007e@xtecnica.it> <87pt8616i3.fsf@asmodeus.mcnaught.org> <1087226647.1775.14.camel@kaos> <011101c4553b$c1967130$70c8007e@xtecnica.it> <20554.1087571463@sss.pgh.pa.us> <1087582040.28062.13.camel@localhost.localdomain> In-Reply-To: <1087582040.28062.13.camel@localhost.localdomain> Content-Type: multipart/mixed; boundary="------------000004040304000206060909" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.5 tagged_above=0.0 required=5.0 tests=RAZOR2_CF_RANGE_11_50, RAZOR2_CHECK X-Spam-Level: * X-Archive-Number: 200406/203 X-Sequence-Number: 7261 This is a multi-part message in MIME format. --------------000004040304000206060909 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Hello, I would have to double check BUT I believe this is fixed in later 2.4.x kernels as well. If you don't want to go through the hassle of 2.6 (although it really is a nice kernel) then upgrade to 2.4.26. Sincerely, Joshau D. Drake Scott Marlowe wrote: > On Fri, 2004-06-18 at 09:11, Tom Lane wrote: > >>"Domenico Sgarbossa" writes: >> >>>so when the users go home, i've got something like 15/20000kb free ram, the >>>rest is cached and 0kb of swap... >>>It seems that when pg_dump starts the cached memory isn't released so the >>>system begin to swap, >> >>A sane kernel should drop disk buffers rather than swapping. We heard >>recently about a bug in some versions of the Linux kernel that cause it >>to prefer swapping to discarding disk cache, though. It sounds like >>that's what you're hitting. Look into newer kernels ... > > > This was a common problem in the linux 2.4 series kernels, but has > supposedly been fixed in the 2.6 kernels. Having lots of memory and > turning off swap will "fix" the problem in 2.4, but if you run out of > real mem, you're hosed. > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL --------------000004040304000206060909 Content-Type: text/x-vcard; charset=utf8; name="jd.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="jd.vcf" begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:jd@commandprompt.com title:Consultant tel;work:503-667-4564 tel;fax:503-210-0034 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard --------------000004040304000206060909-- From smarlowe@qwest.net Fri Jun 18 18:58:04 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E2FC6D1B215 for ; Fri, 18 Jun 2004 15:58:04 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 44063-05 for ; Fri, 18 Jun 2004 18:58:00 +0000 (GMT) Received: from mpls-qmqp-01.inet.qwest.net (mpls-qmqp-01.inet.qwest.net [63.231.195.112]) by svr1.postgresql.org (Postfix) with SMTP id 23ABFD1B1A0 for ; Fri, 18 Jun 2004 15:57:58 -0300 (ADT) Received: (qmail 26657 invoked by uid 0); 18 Jun 2004 18:57:50 -0000 Received: from unknown (63.231.195.5) by mpls-qmqp-01.inet.qwest.net with QMQP; 18 Jun 2004 18:57:50 -0000 Received: from 63-227-127-37.dnvr.qwest.net (HELO ?10.0.0.2?) (63.227.127.37) by mpls-pop-05.inet.qwest.net with SMTP; 18 Jun 2004 18:57:45 -0000 Date: Fri, 18 Jun 2004 12:58:27 -0600 Message-Id: <1087585107.28062.16.camel@localhost.localdomain> From: "Scott Marlowe" To: "Joshua D. Drake" Cc: "Tom Lane" , "Domenico Sgarbossa" , pgsql-performance@postgresql.org Subject: Re: [BULK] Problems with vacuum! In-Reply-To: <40D338BC.7080705@commandprompt.com> References: <003c01c44fbd$c84a6af0$2500fa0a@CardServices.TCI.com> <004c01c44fc3$d7cdeaf0$70c8007e@xtecnica.it> <87pt8616i3.fsf@asmodeus.mcnaught.org> <1087226647.1775.14.camel@kaos> <011101c4553b$c1967130$70c8007e@xtecnica.it> <20554.1087571463@sss.pgh.pa.us> <1087582040.28062.13.camel@localhost.localdomain> <40D338BC.7080705@commandprompt.com> Content-Type: text/plain Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/202 X-Sequence-Number: 7260 I believe it was more like the kernel was tuned to make it less common, but certain things can still trigger it. I know the problem was still there in the 2.4.24 on the last server I was playing with, but it was a lot less of a problem than it had been under 2.4.9 on an earlier machine with the same basic amount of memory. On Fri, 2004-06-18 at 12:47, Joshua D. Drake wrote: > Hello, > > I would have to double check BUT I believe this is fixed in later 2.4.x > kernels as well. If you don't want to go through the hassle of 2.6 > (although it really is a nice kernel) then upgrade to 2.4.26. > > Sincerely, > > Joshau D. Drake > > Scott Marlowe wrote: > > On Fri, 2004-06-18 at 09:11, Tom Lane wrote: > > > >>"Domenico Sgarbossa" writes: > >> > >>>so when the users go home, i've got something like 15/20000kb free ram, the > >>>rest is cached and 0kb of swap... > >>>It seems that when pg_dump starts the cached memory isn't released so the > >>>system begin to swap, > >> > >>A sane kernel should drop disk buffers rather than swapping. We heard > >>recently about a bug in some versions of the Linux kernel that cause it > >>to prefer swapping to discarding disk cache, though. It sounds like > >>that's what you're hitting. Look into newer kernels ... > > > > > > This was a common problem in the linux 2.4 series kernels, but has > > supposedly been fixed in the 2.6 kernels. Having lots of memory and > > turning off swap will "fix" the problem in 2.4, but if you run out of > > real mem, you're hosed. > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > From domenico@xtecnica.com Tue Jun 22 13:34:52 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B3C0CD1B199 for ; Tue, 22 Jun 2004 10:34:52 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 40787-06 for ; Tue, 22 Jun 2004 13:34:53 +0000 (GMT) Received: from xtecnica.com (host154-218.pool80206.interbusiness.it [80.206.218.154]) by svr1.postgresql.org (Postfix) with ESMTP id 32981D1B191 for ; Tue, 22 Jun 2004 10:34:47 -0300 (ADT) Received: from DOMENICO2K by xtecnica.com (MDaemon.PRO.v7.1.1.R) with ESMTP id md50000022851.msg for ; Tue, 22 Jun 2004 15:34:54 +0200 Message-ID: <004d01c4585d$83bc1200$70c8007e@xtecnica.it> Reply-To: "Domenico Sgarbossa" From: "Domenico Sgarbossa" To: References: <003c01c44fbd$c84a6af0$2500fa0a@CardServices.TCI.com> <004c01c44fc3$d7cdeaf0$70c8007e@xtecnica.it> <87pt8616i3.fsf@asmodeus.mcnaught.org> <1087226647.1775.14.camel@kaos> <011101c4553b$c1967130$70c8007e@xtecnica.it> <20554.1087571463@sss.pgh.pa.us> <1087582040.28062.13.camel@localhost.localdomain> <40D338BC.7080705@commandprompt.com> <1087585107.28062.16.camel@localhost.localdomain> Subject: Re: [BULK] Problems with vacuum! Date: Tue, 22 Jun 2004 15:33:33 +0200 Organization: XTECNICA srl MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 1 X-MSMail-Priority: High X-Mailer: Microsoft Outlook Express 5.50.4927.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4927.1200 X-Authenticated-Sender: domenico@xtecnica.com X-Spam-Processed: xtecnica.com, Tue, 22 Jun 2004 15:34:54 +0200 (not processed: message from valid local sender) X-MDRemoteIP: 126.0.200.112 X-Return-Path: domenico@xtecnica.com X-MDaemon-Deliver-To: pgsql-performance@postgresql.org X-MDAV-Processed: xtecnica.com, Tue, 22 Jun 2004 15:34:54 +0200 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.0 tagged_above=0.0 required=5.0 tests=X_MSMAIL_PRIORITY_HIGH, X_PRIORITY_HIGH X-Spam-Level: * X-Archive-Number: 200406/223 X-Sequence-Number: 7281 As you suggets I've tried to upgrade to the kernel 2.4.28, but it seems that nothing change! I built a new machine with Red Hat 8 (kernel 2.4.28) a 1GB RAM using the same parameters i've been used before. After the boot, i've got 800Mb of free memory, if a launch a pg_dump then the system swap (only 1 or 2 mb.....) and the free memory become 20mb. Now, if I tried a vacuumdb the system use partially the cached memory then begin to swap again.... with 700mb of cached memory it's very strange that the system swap again.... anyone know why this happend? Distinti Saluti Sgarbossa Domenico X Tecnica S.R.L. www.xtecnica.com Tel: 049/9409154 - 049/5970297 Fax: 049/9400288 ----- Original Message ----- From: "Scott Marlowe" To: "Joshua D. Drake" Cc: "Tom Lane" ; "Domenico Sgarbossa" ; Sent: Friday, June 18, 2004 8:58 PM Subject: Re: [PERFORM] [BULK] Problems with vacuum! > I believe it was more like the kernel was tuned to make it less common, > but certain things can still trigger it. I know the problem was still > there in the 2.4.24 on the last server I was playing with, but it was a > lot less of a problem than it had been under 2.4.9 on an earlier machine > with the same basic amount of memory. > > On Fri, 2004-06-18 at 12:47, Joshua D. Drake wrote: > > Hello, > > > > I would have to double check BUT I believe this is fixed in later 2.4.x > > kernels as well. If you don't want to go through the hassle of 2.6 > > (although it really is a nice kernel) then upgrade to 2.4.26. > > > > Sincerely, > > > > Joshau D. Drake > > > > Scott Marlowe wrote: > > > On Fri, 2004-06-18 at 09:11, Tom Lane wrote: > > > > > >>"Domenico Sgarbossa" writes: > > >> > > >>>so when the users go home, i've got something like 15/20000kb free ram, the > > >>>rest is cached and 0kb of swap... > > >>>It seems that when pg_dump starts the cached memory isn't released so the > > >>>system begin to swap, > > >> > > >>A sane kernel should drop disk buffers rather than swapping. We heard > > >>recently about a bug in some versions of the Linux kernel that cause it > > >>to prefer swapping to discarding disk cache, though. It sounds like > > >>that's what you're hitting. Look into newer kernels ... > > > > > > > > > This was a common problem in the linux 2.4 series kernels, but has > > > supposedly been fixed in the 2.6 kernels. Having lots of memory and > > > turning off swap will "fix" the problem in 2.4, but if you run out of > > > real mem, you're hosed. > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 4: Don't 'kill -9' the postmaster > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > >