The results and ramblings of research

phewww!

Improving VACUUM performance on PostGRES

leave a comment »

So it turns out my database cluster is really slow when vacuuming large tables. It turns out the “maintenance_work_mem” setting fixes the amount of memory available to the VACUUM thread. I viewed the setting for my DB cluster by typing

db=# show maintenance_work_mem ;

and its output was

maintenance_work_mem
———————-
16MB
(1 row)

Turns out it was set to a really low number. One fix that i found for resolving this is was to log in while setting PG_Options

$ PGOPTIONS='-c maintenance_work_mem=1GB' psql

and then

db=# show maintenance_work_mem ;

and its output was

maintenance_work_mem
———————-
1GB
(1 row)

Viola.

Advertisements

Written by anujjaiswal

October 11, 2011 at 3:05 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: