Memory usage during vacuum

Shelby Cain <alyandon@yahoo.com> writes:

I'm trying to keep postgresql's memory usage
under 40 megs under all conditions so that other
services/applications don't grind to a halt due to
swapping. Is there any way to achieve my goal?

Don't use VACUUM FULL. The vacuum_mem setting only limits the space
consumed by plain VACUUM --- VACUUM FULL needs to keep track of all the
free space in the table, and will eat as much memory as it has to to do
that.

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

How about plugging in more memory ?
40MB seems a bit low for a database server footprint - well, certainly depends
on what you do.
But if your machine starts swapping with an extra 40 MB of memory consumption
I'd say the machine is undersized for the application. I usually have around
500 MB free memory with everything running. Memory is cheap nowadays...

On Thursday 25 March 2004 08:15 am, Tom Lane wrote:

Shelby Cain <alyandon@yahoo.com> writes:

I'm trying to keep postgresql's memory usage
under 40 megs under all conditions so that other
services/applications don't grind to a halt due to
swapping. Is there any way to achieve my goal?

Don't use VACUUM FULL. The vacuum_mem setting only limits the space
consumed by plain VACUUM --- VACUUM FULL needs to keep track of all the
free space in the table, and will eat as much memory as it has to to do
that.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

- --
UC

- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFAYw22jqGXBvRToM4RAghbAKCbXZ9avDIMwpxOyo3g+iyoTmJNSQCgkk3n
2a8HrY9gxBNMk/2iwLrnHEA=
=TZob
-----END PGP SIGNATURE-----

I apologize for my original post being unclear. I'm
running "vacuum analyze" and seeing the behavior
mentioned. Does specifying the analyze option imply
"vacuum full"?

On a hunch I just ran analyze <really big table> and
the backend's memory usage soared up to 100+ megs. I
suspect that means it isn't the vacuum but the analyze
that is eating all my precious ram. :)

Any tips on minimizing the memory footprint during
analyze (ie: backing off the 300 setting that I'm
currently using) or is this just something I'll have
to live with?

Regards,

Shelby Cain

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

Don't use VACUUM FULL. The vacuum_mem setting only
limits the space
consumed by plain VACUUM --- VACUUM FULL needs to
keep track of all the
free space in the table, and will eat as much memory
as it has to to do
that.

regards, tom lane

__________________________________
Do you Yahoo!?
Yahoo! Finance Tax Center - File online. File on time.
http://taxes.yahoo.com/filing.html

I agree in principle that the solution is to run on a
server with more memory instead of my local
development box. However, I'm not going to be able to
simply request that additional memory be installed as
these are "standard" boxes that IT distributes to
employees.

Regardless, I'm more curious about whether I was
overlookign a setting that could reduce the memory
footprint during a vacuum analyze cycle than about
getting it reduced. If it becomes a major pain I'll
simply run the thing on off hours while I'm not at
work. :)

Regards,

Shelby Cain

--- "Uwe C. Schroeder" <uwe@oss4u.com> wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

How about plugging in more memory ?
40MB seems a bit low for a database server footprint
- well, certainly depends
on what you do.
But if your machine starts swapping with an extra 40
MB of memory consumption
I'd say the machine is undersized for the
application. I usually have around
500 MB free memory with everything running. Memory
is cheap nowadays...

On Thursday 25 March 2004 08:15 am, Tom Lane wrote:

Shelby Cain <alyandon@yahoo.com> writes:

I'm trying to keep postgresql's memory usage
under 40 megs under all conditions so that other
services/applications don't grind to a halt due

to

swapping. Is there any way to achieve my goal?

Don't use VACUUM FULL. The vacuum_mem setting

only limits the space

consumed by plain VACUUM --- VACUUM FULL needs to

keep track of all the

free space in the table, and will eat as much

memory as it has to to do

that.

regards, tom lane

---------------------------(end of

broadcast)---------------------------

TIP 9: the planner will ignore your desire to

choose an index scan if your

joining column's datatypes do not match

- --
UC

- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFAYw22jqGXBvRToM4RAghbAKCbXZ9avDIMwpxOyo3g+iyoTmJNSQCgkk3n

2a8HrY9gxBNMk/2iwLrnHEA=
=TZob
-----END PGP SIGNATURE-----

__________________________________
Do you Yahoo!?
Yahoo! Finance Tax Center - File online. File on time.
http://taxes.yahoo.com/filing.html

Shelby Cain <alyandon@yahoo.com> writes:

I apologize for my original post being unclear. I'm
running "vacuum analyze" and seeing the behavior
mentioned. Does specifying the analyze option imply
"vacuum full"?

No; I just figured you were probably using FULL without saying so.
However ...

On a hunch I just ran analyze <really big table> and
the backend's memory usage soared up to 100+ megs.

... the issue is obviously ANALYZE and not VACUUM at all. What
statistics targets are you using? It's hard to believe ANALYZE
would eat that much space unless it's being asked for a really
large target.

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thursday 25 March 2004 09:12 am, Shelby Cain wrote:

I agree in principle that the solution is to run on a
server with more memory instead of my local
development box. However, I'm not going to be able to
simply request that additional memory be installed as
these are "standard" boxes that IT distributes to
employees.

Too bad that.

Regardless, I'm more curious about whether I was
overlookign a setting that could reduce the memory
footprint during a vacuum analyze cycle than about
getting it reduced. If it becomes a major pain I'll
simply run the thing on off hours while I'm not at
work. :)

That's what I would do. Depending on how many transactions (inserts/deletes)
you do a day, it might be perfectly fine to just run a vacuum full analyze
every night via cron. You can reduce the per connection settings (max
connections allowed as well as shared buffers), but that may have a rather
significant impact on performance. So it's probably best to leave those
settings alone (when developing you don't want to wait a minute per query)
and rather do the cleanup at night, or maybe an additional one during lunch
(if needed).

Regards,

Shelby Cain

--- "Uwe C. Schroeder" <uwe@oss4u.com> wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

How about plugging in more memory ?
40MB seems a bit low for a database server footprint
- well, certainly depends
on what you do.
But if your machine starts swapping with an extra 40
MB of memory consumption
I'd say the machine is undersized for the
application. I usually have around
500 MB free memory with everything running. Memory
is cheap nowadays...

On Thursday 25 March 2004 08:15 am, Tom Lane wrote:

Shelby Cain <alyandon@yahoo.com> writes:

I'm trying to keep postgresql's memory usage
under 40 megs under all conditions so that other
services/applications don't grind to a halt due

to

swapping. Is there any way to achieve my goal?

Don't use VACUUM FULL. The vacuum_mem setting

only limits the space

consumed by plain VACUUM --- VACUUM FULL needs to

keep track of all the

free space in the table, and will eat as much

memory as it has to to do

that.

regards, tom lane

---------------------------(end of

broadcast)---------------------------

TIP 9: the planner will ignore your desire to

choose an index scan if your

joining column's datatypes do not match

- --
UC

- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFAYw22jqGXBvRToM4RAghbAKCbXZ9avDIMwpxOyo3g+iyoTmJNSQCgkk3n

2a8HrY9gxBNMk/2iwLrnHEA=
=TZob
-----END PGP SIGNATURE-----

__________________________________
Do you Yahoo!?
Yahoo! Finance Tax Center - File online. File on time.
http://taxes.yahoo.com/filing.html

- --
UC

- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFAYxazjqGXBvRToM4RAh1RAKCV9ozDim5gBWYdX+VBdyiT8Ych1QCgyqZb
Eo6UT9r2K2z6TE1jQpg/PEU=
=4ogO
-----END PGP SIGNATURE-----

Currently my default is 300 (yes - very large I know)
but overriding default_statistics_target with a value
of 1 and re-running vacuum analyze on the same large
table results in no change in maximum memory
consumption during the process that I can see. It
should be noted that I see this behavior only with one
table. All other tables, even relatively large ones
only result in a backend that grows to consume about
30 megabytes.

Here is some sample output:

c1scain=# set default_statistics_target=300;
SET
c1scain=# vacuum verbose analyze inventory_txns;
INFO: vacuuming "public.inventory_txns"
INFO: index "idx_inventory_txns_txndate" now contains
957655 row versions in 5770 pages
DETAIL: 1686 index pages have been deleted, 1686 are
currently reusable.
CPU 0.51s/0.21u sec elapsed 5.20 sec.
INFO: index "idx_inventory_txns_locitemnbr" now
contains 957655 row versions in 4752 pages
DETAIL: 0 index pages have been deleted, 0 are
currently reusable.
CPU 0.53s/0.09u sec elapsed 2.73 sec.
INFO: "inventory_txns": found 0 removable, 957655
nonremovable row versions in 39549 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 15914 unused item pointers.
0 pages are entirely empty.
CPU 2.16s/0.60u sec elapsed 15.76 sec.
INFO: analyzing "public.inventory_txns"
INFO: "inventory_txns": 39549 pages, 150000 rows
sampled, 944729 estimated total rows

I notice that postgresql decided to sample 150000
rows.

Now, when I try the following:

c1scain=# set default_statistics_target=1;
SET
c1scain=# vacuum verbose analyze inventory_txns;
INFO: vacuuming "public.inventory_txns"
INFO: index "idx_inventory_txns_txndate" now contains
957655 row versions in 5770 pages
DETAIL: 1686 index pages have been deleted, 1686 are
currently reusable.
CPU 0.37s/0.15u sec elapsed 5.29 sec.
INFO: index "idx_inventory_txns_locitemnbr" now
contains 957655 row versions in 4752 pages
DETAIL: 0 index pages have been deleted, 0 are
currently reusable.
CPU 0.54s/0.09u sec elapsed 2.93 sec.
INFO: "inventory_txns": found 0 removable, 957655
nonremovable row versions in 39549 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 15914 unused item pointers.
0 pages are entirely empty.
CPU 2.21s/0.51u sec elapsed 16.37 sec.
INFO: analyzing "public.inventory_txns"
INFO: "inventory_txns": 39549 pages, 150000 rows
sampled, 944729 estimated total rows
VACUUM

It still decided to sample 150000 rows. Am I missing
something obvious here? Shouldn't fewer rows be
sampled when I set the collection target to 1?

Regards,

Shelby Cain

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

... the issue is obviously ANALYZE and not VACUUM at
all. What
statistics targets are you using? It's hard to
believe ANALYZE
would eat that much space unless it's being asked
for a really
large target.

__________________________________
Do you Yahoo!?
Yahoo! Finance Tax Center - File online. File on time.
http://taxes.yahoo.com/filing.html

Shelby Cain <alyandon@yahoo.com> writes:

It still decided to sample 150000 rows. Am I missing
something obvious here? Shouldn't fewer rows be
sampled when I set the collection target to 1?

The sample size is 300 rows times the largest per-column analysis
target, where default_statistics_target is used if the recorded
per-column setting is -1. I would say that you have set a target of 500
for at least one of the columns of that table, using ALTER TABLE SET
STATISTICS. Try this to see which:

select attname, attstattarget from pg_attribute
where attrelid = 'table_name_here'::regclass;

regards, tom lane

I had thought that I had dropped and reloaded this
table but apparently I hadn't and I had set the
statistics target for one column to 500 while
experimenting. Resetting it to -1 and running with a
default of 300 gets ~ 70 megs memory footprint during
the analyze now.

Thanks Tom for indulging my curiosity on the matter.
I've learned something that I didn't readily pick up
from reading the documentation.

Regards,

Shelby Cain

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

Shelby Cain <alyandon@yahoo.com> writes:

It still decided to sample 150000 rows. Am I

missing

something obvious here? Shouldn't fewer rows be
sampled when I set the collection target to 1?

The sample size is 300 rows times the largest
per-column analysis
target, where default_statistics_target is used if
the recorded
per-column setting is -1. I would say that you have
set a target of 500
for at least one of the columns of that table, using
ALTER TABLE SET
STATISTICS. Try this to see which:

select attname, attstattarget from pg_attribute
where attrelid = 'table_name_here'::regclass;

regards, tom lane

__________________________________
Do you Yahoo!?
Yahoo! Finance Tax Center - File online. File on time.
http://taxes.yahoo.com/filing.html