Thread: Re: [BULK] Problems with vacuum!
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" <rschwarz@totalcardinc.com> To: "'Domenico Sgarbossa'" <domenico@xtecnica.com>; <pgsql-performance@postgresql.org> 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. > > >
"Domenico Sgarbossa" <domenico@xtecnica.com> 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
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" <domenico@xtecnica.com> 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! >
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
"Domenico Sgarbossa" <domenico@xtecnica.com> 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
On Fri, 2004-06-18 at 09:11, Tom Lane wrote: > "Domenico Sgarbossa" <domenico@xtecnica.com> 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.
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" <domenico@xtecnica.com> 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 >
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" <domenico@xtecnica.com> 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
Attachment
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" <smarlowe@qwest.net> To: "Joshua D. Drake" <jd@commandprompt.com> Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; "Domenico Sgarbossa" <domenico@xtecnica.com>; <pgsql-performance@postgresql.org> 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" <domenico@xtecnica.com> 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 > >