Обсуждение: DB is slow until DB is reloaded
Hi all,
I've got a fairly small DB (~850MB when pg_dump'ed) running on PgSQL
v8.1.11 a CentOS 5.3 x86_64 Xen-based virtual machine. The filesystem is
ext3 on LVM with 32MB extents. It's about the only real resource-hungry
VM on the server.
It slows down over time and I can't seem to find a way to get the
performance to return without doing a dump and reload of the database.
I've tried manually running 'VACUUM FULL' and restarting the postgresql
daemon without success.
For example, here is an actual query before the dump and again after the
dump (sorry for the large query):
-=] Before the dump/reload [=-
server@iwt=> EXPLAIN ANALYZE SELECT lor_order_type, lor_order_date,
lor_order_time, lor_isp_agent_id, lor_last_modified_date,
lor_isp_order_number, lor_instr_for_bell_rep, lor_type_of_service,
lor_local_voice_provider, lor_dry_loop_instr, lor_termination_location,
lor_req_line_speed, lor_server_from, lor_rate_band,
lor_related_order_nums, lor_related_order_types, lor_activation_date,
lor_cust_first_name, lor_cust_last_name, lor_req_activation_date,
lor_street_number, lor_street_number_suffix, lor_street_name,
lor_street_type, lor_street_direction, lor_location_type_1,
lor_location_number_1, lor_location_type_2, lor_location_number_2,
lor_postal_code, lor_municipality, lor_province, lor_customer_group,
lor_daytime_phone, lor_daytime_phone_ext, lod_value AS circuit_number
FROM line_owner_report LEFT JOIN line_owner_data ON (lor_id=lod_lo_id
AND lod_variable='ISPCircuitNumber1') WHERE lor_lo_id=514;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Hash Left Join (cost=2115.43..112756.81 rows=8198 width=1152) (actual
time=1463.311..1463.380 rows=1 loops=1)
Hash Cond: ("outer".lor_id = "inner".lod_lo_id)
-> Seq Scan on line_owner_report (cost=0.00..108509.85 rows=8198
width=1124) (actual time=1462.810..1462.872 rows=1 loops=1)
Filter: (lor_lo_id = 514)
-> Hash (cost=2112.85..2112.85 rows=1033 width=36) (actual
time=0.421..0.421 rows=5 loops=1)
-> Bitmap Heap Scan on line_owner_data (cost=9.61..2112.85
rows=1033 width=36) (actual time=0.274..0.378 rows=5 loops=1)
Recheck Cond: (lod_variable = 'ISPCircuitNumber1'::text)
-> Bitmap Index Scan on lod_variable_index
(cost=0.00..9.61 rows=1033 width=0) (actual time=0.218..0.218 rows=5
loops=1)
Index Cond: (lod_variable = 'ISPCircuitNumber1'::text)
Total runtime: 1463.679 ms
(10 rows)
-=] After the dump/reload [=-
server@iwt=> EXPLAIN ANALYZE SELECT lor_order_type, lor_order_date,
lor_order_time, lor_isp_agent_id, lor_last_modified_date,
lor_isp_order_number, lor_instr_for_bell_rep, lor_type_of_service,
lor_local_voice_provider, lor_dry_loop_instr, lor_termination_location,
lor_req_line_speed, lor_server_from, lor_rate_band,
lor_related_order_nums, lor_related_order_types, lor_activation_date,
lor_cust_first_name, lor_cust_last_name, lor_req_activation_date,
lor_street_number, lor_street_number_suffix, lor_street_name,
lor_street_type, lor_street_direction, lor_location_type_1,
lor_location_number_1, lor_location_type_2, lor_location_number_2,
lor_postal_code, lor_municipality, lor_province, lor_customer_group,
lor_daytime_phone, lor_daytime_phone_ext, lod_value AS circuit_number
FROM line_owner_report LEFT JOIN line_owner_data ON (lor_id=lod_lo_id
AND lod_variable='ISPCircuitNumber1') WHERE lor_lo_id=514;
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=10.84..182.57 rows=5 width=1152) (actual
time=1.980..2.083 rows=1 loops=1)
-> Seq Scan on line_owner_report (cost=0.00..70.05 rows=5
width=1124) (actual time=1.388..1.485 rows=1 loops=1)
Filter: (lor_lo_id = 514)
-> Bitmap Heap Scan on line_owner_data (cost=10.84..22.47 rows=3
width=36) (actual time=0.562..0.562 rows=0 loops=1)
Recheck Cond: (("outer".lor_id = line_owner_data.lod_lo_id)
AND (line_owner_data.lod_variable = 'ISPCircuitNumber1'::text))
-> BitmapAnd (cost=10.84..10.84 rows=3 width=0) (actual
time=0.552..0.552 rows=0 loops=1)
-> Bitmap Index Scan on lod_id_index (cost=0.00..4.80
rows=514 width=0) (actual time=0.250..0.250 rows=126 loops=1)
Index Cond: ("outer".lor_id =
line_owner_data.lod_lo_id)
-> Bitmap Index Scan on lod_variable_index
(cost=0.00..5.80 rows=514 width=0) (actual time=0.262..0.262 rows=5 loops=1)
Index Cond: (lod_variable = 'ISPCircuitNumber1'::text)
Total runtime: 2.576 ms
(11 rows)
Any idea on what might be causing the slowdown? Is it likely
filesystem related or am I missing for maintenance step?
Thanks!
Madi
On 04/01/2010 7:10 PM, Madison Kelly wrote:
> Hi all,
>
> I've got a fairly small DB (~850MB when pg_dump'ed) running on PgSQL
> v8.1.11 a CentOS 5.3 x86_64 Xen-based virtual machine. The filesystem is
> ext3 on LVM with 32MB extents. It's about the only real resource-hungry
> VM on the server.
>
> It slows down over time and I can't seem to find a way to get the
> performance to return without doing a dump and reload of the database.
> I've tried manually running 'VACUUM FULL' and restarting the postgresql
> daemon without success.
>
> For example, here is an actual query before the dump and again after the
> dump (sorry for the large query):
>
> -=] Before the dump/reload [=-
> server@iwt=> EXPLAIN ANALYZE SELECT lor_order_type, lor_order_date,
> lor_order_time, lor_isp_agent_id, lor_last_modified_date,
> lor_isp_order_number, lor_instr_for_bell_rep, lor_type_of_service,
> lor_local_voice_provider, lor_dry_loop_instr, lor_termination_location,
> lor_req_line_speed, lor_server_from, lor_rate_band,
> lor_related_order_nums, lor_related_order_types, lor_activation_date,
> lor_cust_first_name, lor_cust_last_name, lor_req_activation_date,
> lor_street_number, lor_street_number_suffix, lor_street_name,
> lor_street_type, lor_street_direction, lor_location_type_1,
> lor_location_number_1, lor_location_type_2, lor_location_number_2,
> lor_postal_code, lor_municipality, lor_province, lor_customer_group,
> lor_daytime_phone, lor_daytime_phone_ext, lod_value AS circuit_number
> FROM line_owner_report LEFT JOIN line_owner_data ON (lor_id=lod_lo_id
> AND lod_variable='ISPCircuitNumber1') WHERE lor_lo_id=514;
> QUERY
> PLAN
>
------------------------------------------------------------------------------------------------------------------------------------------
>
> Hash Left Join (cost=2115.43..112756.81 rows=8198 width=1152) (actual
> time=1463.311..1463.380 rows=1 loops=1)
> Hash Cond: ("outer".lor_id = "inner".lod_lo_id)
> -> Seq Scan on line_owner_report (cost=0.00..108509.85 rows=8198
> width=1124) (actual time=1462.810..1462.872 rows=1 loops=1)
> Filter: (lor_lo_id = 514)
> -> Hash (cost=2112.85..2112.85 rows=1033 width=36) (actual
> time=0.421..0.421 rows=5 loops=1)
> -> Bitmap Heap Scan on line_owner_data (cost=9.61..2112.85
> rows=1033 width=36) (actual time=0.274..0.378 rows=5 loops=1)
> Recheck Cond: (lod_variable = 'ISPCircuitNumber1'::text)
> -> Bitmap Index Scan on lod_variable_index
> (cost=0.00..9.61 rows=1033 width=0) (actual time=0.218..0.218 rows=5
> loops=1)
> Index Cond: (lod_variable =
> 'ISPCircuitNumber1'::text)
> Total runtime: 1463.679 ms
> (10 rows)
>
> -=] After the dump/reload [=-
> server@iwt=> EXPLAIN ANALYZE SELECT lor_order_type, lor_order_date,
> lor_order_time, lor_isp_agent_id, lor_last_modified_date,
> lor_isp_order_number, lor_instr_for_bell_rep, lor_type_of_service,
> lor_local_voice_provider, lor_dry_loop_instr, lor_termination_location,
> lor_req_line_speed, lor_server_from, lor_rate_band,
> lor_related_order_nums, lor_related_order_types, lor_activation_date,
> lor_cust_first_name, lor_cust_last_name, lor_req_activation_date,
> lor_street_number, lor_street_number_suffix, lor_street_name,
> lor_street_type, lor_street_direction, lor_location_type_1,
> lor_location_number_1, lor_location_type_2, lor_location_number_2,
> lor_postal_code, lor_municipality, lor_province, lor_customer_group,
> lor_daytime_phone, lor_daytime_phone_ext, lod_value AS circuit_number
> FROM line_owner_report LEFT JOIN line_owner_data ON (lor_id=lod_lo_id
> AND lod_variable='ISPCircuitNumber1') WHERE lor_lo_id=514;
> QUERY
> PLAN
>
-----------------------------------------------------------------------------------------------------------------------------------------
>
> Nested Loop Left Join (cost=10.84..182.57 rows=5 width=1152) (actual
> time=1.980..2.083 rows=1 loops=1)
> -> Seq Scan on line_owner_report (cost=0.00..70.05 rows=5
> width=1124) (actual time=1.388..1.485 rows=1 loops=1)
> Filter: (lor_lo_id = 514)
> -> Bitmap Heap Scan on line_owner_data (cost=10.84..22.47 rows=3
> width=36) (actual time=0.562..0.562 rows=0 loops=1)
> Recheck Cond: (("outer".lor_id = line_owner_data.lod_lo_id)
> AND (line_owner_data.lod_variable = 'ISPCircuitNumber1'::text))
> -> BitmapAnd (cost=10.84..10.84 rows=3 width=0) (actual
> time=0.552..0.552 rows=0 loops=1)
> -> Bitmap Index Scan on lod_id_index (cost=0.00..4.80
> rows=514 width=0) (actual time=0.250..0.250 rows=126 loops=1)
> Index Cond: ("outer".lor_id =
> line_owner_data.lod_lo_id)
> -> Bitmap Index Scan on lod_variable_index
> (cost=0.00..5.80 rows=514 width=0) (actual time=0.262..0.262 rows=5
> loops=1)
> Index Cond: (lod_variable =
> 'ISPCircuitNumber1'::text)
> Total runtime: 2.576 ms
> (11 rows)
>
> Any idea on what might be causing the slowdown? Is it likely
> filesystem related or am I missing for maintenance step?
You'll notice that in the first query your row estimates are off by
several orders of magnitude, where in the second query they are much
more accurate.
I'm guessing that the data is changing a fair bit over time
(inserts/updates/deletes) and you are not ANALYZEing regularly (and
probably not VACUUMing regularly either).
Try regular VACUUM ANALYZE DATABASE rather than VACUUM FULL or a
dump/reload.
Cheers,
Gary.
Madison Kelly wrote: > Hi all, > > I've got a fairly small DB... > > It slows down over time and I can't seem to find a way to get the > performance to return without doing a dump and reload of the database... Some questions: Is autovacuum running? This is the most likely suspect. If not, things will bloat and you won't be getting appropriate "analyze" runs. Speaking of which, what happens if you just run "analyze"? And as long as you are dumping and reloading anyway, how about version upgrading for bug reduction, performance improvement, and cool new features. Cheers, Steve
Steve Crawford wrote: > Madison Kelly wrote: >> Hi all, >> >> I've got a fairly small DB... >> >> It slows down over time and I can't seem to find a way to get the >> performance to return without doing a dump and reload of the database... > > Some questions: > > Is autovacuum running? This is the most likely suspect. If not, things > will bloat and you won't be getting appropriate "analyze" runs. Speaking > of which, what happens if you just run "analyze"? > > And as long as you are dumping and reloading anyway, how about version > upgrading for bug reduction, performance improvement, and cool new > features. > > Cheers, > Steve > Yup, I even tried manually running 'VACUUM FULL' and it didn't help. As for upgrading; a) I am trying to find a way around the dump/reload. I am doing it as a "last resort" only. b) I want to keep the version in CentOS' repo. I'd not tried simply updating the stats via ANALYZE... I'll keep an eye on performance and if it starts to slip again, I will run ANALYZE and see if that helps. If there is a way to run ANALYZE against a query that I am missing, please let me know. Madi
> Yup, I even tried manually running 'VACUUM FULL' and it didn't help. As > for upgrading; VACUUM FULL is usually considered a bad idea. What you probably want to do instead is CLUSTER, followed by ANALYZE. Basically, VACUUM makes the indexes smaller (but doesn't reclaim much space from the tables themselves). VACUUM FULL reclaims space from the tables, but bloats the indexes. > a) I am trying to find a way around the dump/reload. I am doing it as a > "last resort" only. > b) I want to keep the version in CentOS' repo. > Postgres is pretty easy to build from source. It's nicely self-contained, and won't bite you with dependency hell. So don't be too wary of compiling it. Richard
On 04/01/2010 8:30 PM, Madison Kelly wrote: > Steve Crawford wrote: >> Madison Kelly wrote: >>> Hi all, >>> >>> I've got a fairly small DB... >>> >>> It slows down over time and I can't seem to find a way to get the >>> performance to return without doing a dump and reload of the >>> database... >> >> Some questions: >> >> Is autovacuum running? This is the most likely suspect. If not, >> things will bloat and you won't be getting appropriate "analyze" >> runs. Speaking of which, what happens if you just run "analyze"? >> >> And as long as you are dumping and reloading anyway, how about >> version upgrading for bug reduction, performance improvement, and >> cool new features. >> >> Cheers, >> Steve >> > > Yup, I even tried manually running 'VACUUM FULL' and it didn't help. > As for upgrading; > VACUUM FULL is not the same as VACUUM ANALYZE FULL. You shouldn't need the FULL option amyway. > a) I am trying to find a way around the dump/reload. I am doing it as > a "last resort" only. > b) I want to keep the version in CentOS' repo. > > I'd not tried simply updating the stats via ANALYZE... I'll keep an > eye on performance and if it starts to slip again, I will run ANALYZE > and see if that helps. If there is a way to run ANALYZE against a > query that I am missing, please let me know. > From your queries it definitely looks like its your stats that are the problem. When the stats get well out of date the planner is choosing a hash join because it thinks thousands of rows are involved where as only a few are actually involved. Thats why, with better stats, the second query is using a loop join over very few rows and running much quicker. Therefore it's ANALYZE you need to run as well as regular VACUUMing. There should be no need to VACUUM FULL at all as long as you VACUUM and ANALYZE regularly. Once a day may be enough, but you don't say how long it takes your database to become "slow". You can VACUUM either the whole database (often easiest) or individual tables if you know in more detail what the problem is and that only certain tables need it. Setting up autovacuum may well be sufficient. Cheers, Gary. > Madi >
Gary Doades wrote: > From your queries it definitely looks like its your stats that are the > problem. When the stats get well out of date the planner is choosing a > hash join because it thinks thousands of rows are involved where as only > a few are actually involved. Thats why, with better stats, the second > query is using a loop join over very few rows and running much quicker. > > Therefore it's ANALYZE you need to run as well as regular VACUUMing. > There should be no need to VACUUM FULL at all as long as you VACUUM and > ANALYZE regularly. Once a day may be enough, but you don't say how long > it takes your database to become "slow". > > You can VACUUM either the whole database (often easiest) or individual > tables if you know in more detail what the problem is and that only > certain tables need it. > > Setting up autovacuum may well be sufficient. > > Cheers, > Gary. That explains things, thank you! For the record; It was taking a few months for the performance to become intolerable. I've added CLUSTER -> ANALYZE -> VACUUM to my nightly routine and dropped the VACUUM FULL call. I'll see how this works. Cheers! Madi
On Mon, 2010-01-04 at 15:53 -0500, Madison Kelly wrote: > Gary Doades wrote: > > From your queries it definitely looks like its your stats that are the > > problem. When the stats get well out of date the planner is choosing a > > hash join because it thinks thousands of rows are involved where as only > > a few are actually involved. Thats why, with better stats, the second > > query is using a loop join over very few rows and running much quicker. > > > > Therefore it's ANALYZE you need to run as well as regular VACUUMing. > > There should be no need to VACUUM FULL at all as long as you VACUUM and > > ANALYZE regularly. Once a day may be enough, but you don't say how long > > it takes your database to become "slow". > > > > You can VACUUM either the whole database (often easiest) or individual > > tables if you know in more detail what the problem is and that only > > certain tables need it. > > > > Setting up autovacuum may well be sufficient. > > > > Cheers, > > Gary. > > That explains things, thank you! > > For the record; It was taking a few months for the performance to become > intolerable. I've added CLUSTER -> ANALYZE -> VACUUM to my nightly > routine and dropped the VACUUM FULL call. I'll see how this works. I think you are going down the wrong route here - you should be looking at preventative maintenance instead of fixing it after its broken. Ensure that autovacuum is running for the database (assuming that you are on a relatively modern version of PG), and possibly tune it to be more aggressive (we can help). This will ensure that the condition never comes up. ps - if you do go with the route specify, no need to VACUUM after the CLUSTER. CLUSTER gets rid of the dead tuples - nothing for VACUUM to do. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp.
Madison Kelly <linux@alteeve.com> wrote: > I've added CLUSTER -> ANALYZE -> VACUUM to my nightly > routine and dropped the VACUUM FULL call. The CLUSTER is probably not going to make much difference once you've eliminated bloat, unless your queries do a lot of searches in the sequence of the index used. Be sure to run VACUUM ANALYZE as one statement, not two separate steps. -Kevin
Kevin Grittner wrote: > Madison Kelly <linux@alteeve.com> wrote: > >> I've added CLUSTER -> ANALYZE -> VACUUM to my nightly >> routine and dropped the VACUUM FULL call. > > The CLUSTER is probably not going to make much difference once > you've eliminated bloat, unless your queries do a lot of searches in > the sequence of the index used. Be sure to run VACUUM ANALYZE as > one statement, not two separate steps. > > -Kevin Ah, noted and updated, thank you. Madi
Brad Nicholson wrote:
> I think you are going down the wrong route here - you should be looking
> at preventative maintenance instead of fixing it after its broken.
>
> Ensure that autovacuum is running for the database (assuming that you
> are on a relatively modern version of PG), and possibly tune it to be
> more aggressive (we can help).
>
> This will ensure that the condition never comes up.
>
> ps - if you do go with the route specify, no need to VACUUM after the
> CLUSTER. CLUSTER gets rid of the dead tuples - nothing for VACUUM to
> do.
>
I wanted to get ahead of the problem, hence my question here. :) I've
set this to run at night ('iwt' being the DB in question):
su postgres -c "psql iwt -c \"VACUUM ANALYZE VERBOSE\"
I will keep an eye on the output for a little while (it appends to a
log) and see what it says. Also, I read that CLUSTER can mess up back
ups as it makes tables look empty while running. If the above doesn't
seem to help, I will swap out the VACUUM and run a CLUSTER before the
ANALYZE and see how that works.
Madi
Madison Kelly wrote: > Steve Crawford wrote: >> Madison Kelly wrote: >>> Hi all, >>> >>> I've got a fairly small DB... >>> >>> It slows down over time and I can't seem to find a way to get the >>> performance to return without doing a dump and reload of the >>> database... >> > Yup, I even tried manually running 'VACUUM FULL' and it didn't help. That's because VACUUM reclaims space (er, actually marks space that is available for reuse) while ANALYZE refreshes the statistics that the planner uses. > As for upgrading; > > a) I am trying to find a way around the dump/reload. I am doing it as > a "last resort" only. Agreed - it is the last resort. But since you were doing it I was just suggesting that you could combine with a upgrade and get more benefits. > b) I want to keep the version in CentOS' repo. Depends on reasoning. If you absolutely require a fully vanilla particular version of CentOS for some reason then fine. But telling CentOS to use the PostgreSQL Development Group pre-built releases for CentOS is a very easy one-time process (it's what I do on my CentOS machines). From memory (but read to end for warnings): Download the setup rpm: wget http://yum.pgsqlrpms.org/reporpms/8.4/pgdg-centos-8.4-1.noarch.rpm Install it: rpm -i pgdg-centos-8.4-1.noarch.rpm Note: This does not install PostgreSQL - it just updates your repository list to add the repository containing PostgreSQL binaries. Now make sure that you get your updates from PostgreSQL, not CentOS: Edit /etc/yum.repos.d/CentOS-Base.repo and add "exclude=postgresql*" to the [base] and [updates] sections. Now you can use "yum" as normal and you will get PostgreSQL 8.4 and updates thereto rather than using 8.1. BUT!! I have only done this on new installs. I have not tried it on an already running machine. As always, test first on a dev machine and do your pre-update dump using the new version of the pg_dump utilities, not the old ones. Cheers, Steve > > > I'd not tried simply updating the stats via ANALYZE... I'll keep an > eye on performance and if it starts to slip again, I will run ANALYZE > and see if that helps. If there is a way to run ANALYZE against a > query that I am missing, please let me know. If you stick with 8.1x, you may want to edit postgresql.conf and change default_statistics_target to 100 if it is still at the previous default of 10. 100 is the new default setting as testing indicates that it tends to yield better query plans with minimal additional overhead. Cheers, Steve
Madison Kelly wrote:
>
> I wanted to get ahead of the problem, hence my question here. :)
> I've set this to run at night ('iwt' being the DB in question):
>
> su postgres -c "psql iwt -c \"VACUUM ANALYZE VERBOSE\"
And why not the vacuumdb command?:
su postgres -c "vacuumdb --analyze --verbose iwt"
But this is duct-tape and bailing-wire. You REALLY need to make sure
that autovacuum is running - you are likely to have much better results
with less pain.
Cheers,
Steve
Steve Crawford wrote: > Madison Kelly wrote: >> Steve Crawford wrote: >>> Madison Kelly wrote: >>>> Hi all, >>>> >>>> I've got a fairly small DB... >>>> >>>> It slows down over time and I can't seem to find a way to get the >>>> performance to return without doing a dump and reload of the >>>> database... >>> >> Yup, I even tried manually running 'VACUUM FULL' and it didn't help. > That's because VACUUM reclaims space (er, actually marks space that is > available for reuse) while ANALYZE refreshes the statistics that the > planner uses. > >> As for upgrading; >> >> a) I am trying to find a way around the dump/reload. I am doing it as >> a "last resort" only. > Agreed - it is the last resort. But since you were doing it I was just > suggesting that you could combine with a upgrade and get more benefits. >> b) I want to keep the version in CentOS' repo. > Depends on reasoning. If you absolutely require a fully vanilla > particular version of CentOS for some reason then fine. But telling > CentOS to use the PostgreSQL Development Group pre-built releases for > CentOS is a very easy one-time process (it's what I do on my CentOS > machines). From memory (but read to end for warnings): > > Download the setup rpm: > wget http://yum.pgsqlrpms.org/reporpms/8.4/pgdg-centos-8.4-1.noarch.rpm > > Install it: > rpm -i pgdg-centos-8.4-1.noarch.rpm > > Note: This does not install PostgreSQL - it just updates your repository > list to add the repository containing PostgreSQL binaries. Now make sure > that you get your updates from PostgreSQL, not CentOS: > > Edit /etc/yum.repos.d/CentOS-Base.repo and add "exclude=postgresql*" to > the [base] and [updates] sections. > > Now you can use "yum" as normal and you will get PostgreSQL 8.4 and > updates thereto rather than using 8.1. > > BUT!! I have only done this on new installs. I have not tried it on an > already running machine. As always, test first on a dev machine and do > your pre-update dump using the new version of the pg_dump utilities, not > the old ones. > > Cheers, > Steve > >> >> >> I'd not tried simply updating the stats via ANALYZE... I'll keep an >> eye on performance and if it starts to slip again, I will run ANALYZE >> and see if that helps. If there is a way to run ANALYZE against a >> query that I am missing, please let me know. > If you stick with 8.1x, you may want to edit postgresql.conf and change > default_statistics_target to 100 if it is still at the previous default > of 10. 100 is the new default setting as testing indicates that it tends > to yield better query plans with minimal additional overhead. > > Cheers, > Steve I think for now, I will stick with 8.1, but I will certainly try out your repo edit above on a test machine and see how that works out. I am always reticent to change something as fundamental as postgres without "good reason". I guess I am a fan of "if it ain't broke...". :) As for the edit to postgresql.conf, I've made the change. Thanks for the detailed input on that. Madi
Steve Crawford wrote:
> Madison Kelly wrote:
>>
>> I wanted to get ahead of the problem, hence my question here. :)
>> I've set this to run at night ('iwt' being the DB in question):
>>
>> su postgres -c "psql iwt -c \"VACUUM ANALYZE VERBOSE\"
>
> And why not the vacuumdb command?:
>
> su postgres -c "vacuumdb --analyze --verbose iwt"
>
>
> But this is duct-tape and bailing-wire. You REALLY need to make sure
> that autovacuum is running - you are likely to have much better results
> with less pain.
>
> Cheers,
> Steve
As for why '-c ...', I guess it was just a matter of which command came
to mind first. :) Is there a particular benefit to using the 'vacuumdb'
wrapper?
As for autovacuum, I assumed (yes, I know) that all v8.x releases
enabled it by default. How would I confirm that it's running or not?
Madi
On Mon, Jan 4, 2010 at 2:57 PM, Madison Kelly <linux@alteeve.com> wrote: > As for autovacuum, I assumed (yes, I know) that all v8.x releases enabled it > by default. How would I confirm that it's running or not? I believe it's not enabled by default in 8.1-land, and is as of 8.2 and later. Whether it's running or not, try "SELECT * FROM pg_autovacuum;". If that returns the null set, it's not doing anything, as it hasn't been told it has anything to do. IME, however, if you really want to benefit from the autovacuum daemon, you probably do want to be on something more recent than 8.1. (And, yes, this is a bit of the pot calling the kettle black: I have a mixed set of 8.1 and 8.3 hosts. Autovacuum is only running on the latter, while the former are queued for an upgrade.) rls -- :wq
Madison Kelly wrote: > I think for now, I will stick with 8.1, but I will certainly try out > your repo edit above on a test machine and see how that works out. I > am always reticent to change something as fundamental as postgres > without "good reason". I guess I am a fan of "if it ain't broke...". :) PostgreSQL has many fundamental limitations that cannot be resolved no matter what you do in 8.1 that are fixed in later versions. The default behavior for the problem you're having has been massively improved by updates made in 8.2, 8.3, and 8.4. 8.1 can certainly be considered broken in regards to its lack of good and automatic VACUUM and ANALYZE behavior, and you're just seeing the first round of issues in that area. Every minute you spend applying temporary fixes to the fundamental issues is time you could be better spending toward upgrading instead. -- Greg Smith 2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.com
On Mon, Jan 4, 2010 at 3:13 PM, Greg Smith <greg@2ndquadrant.com> wrote: > Madison Kelly wrote: >> >> I think for now, I will stick with 8.1, but I will certainly try out your >> repo edit above on a test machine and see how that works out. I am always >> reticent to change something as fundamental as postgres without "good >> reason". I guess I am a fan of "if it ain't broke...". :) > > PostgreSQL has many fundamental limitations that cannot be resolved no > matter what you do in 8.1 that are fixed in later versions. The default > behavior for the problem you're having has been massively improved by > updates made in 8.2, 8.3, and 8.4. 8.1 can certainly be considered broken > in regards to its lack of good and automatic VACUUM and ANALYZE behavior, > and you're just seeing the first round of issues in that area. Every minute > you spend applying temporary fixes to the fundamental issues is time you > could be better spending toward upgrading instead. Also, the HOT updates in 8.3 made a compelling case for us to update, and if the OP is suffering from table bloat, HOT might help a lot.
Rosser Schwarz wrote: > On Mon, Jan 4, 2010 at 2:57 PM, Madison Kelly <linux@alteeve.com> wrote: >> As for autovacuum, I assumed (yes, I know) that all v8.x releases enabled it >> by default. How would I confirm that it's running or not? > > I believe it's not enabled by default in 8.1-land, and is as of 8.2 > and later. Whether it's running or not, try "SELECT * FROM > pg_autovacuum;". If that returns the null set, it's not doing > anything, as it hasn't been told it has anything to do. > > IME, however, if you really want to benefit from the autovacuum > daemon, you probably do want to be on something more recent than 8.1. > (And, yes, this is a bit of the pot calling the kettle black: I have a > mixed set of 8.1 and 8.3 hosts. Autovacuum is only running on the > latter, while the former are queued for an upgrade.) > > rls You are right, autovacuum is not running after all. From your comment, I am wondering if you'd recommend I turn it on or not? If so, given that I doubt I will upgrade any time soon, how would I enable it? I suppose I could google that, but google rarely shares gotcha's. :) Madi
Scott Marlowe wrote: > On Mon, Jan 4, 2010 at 3:13 PM, Greg Smith <greg@2ndquadrant.com> wrote: >> Madison Kelly wrote: >>> I think for now, I will stick with 8.1, but I will certainly try out your >>> repo edit above on a test machine and see how that works out. I am always >>> reticent to change something as fundamental as postgres without "good >>> reason". I guess I am a fan of "if it ain't broke...". :) >> PostgreSQL has many fundamental limitations that cannot be resolved no >> matter what you do in 8.1 that are fixed in later versions. The default >> behavior for the problem you're having has been massively improved by >> updates made in 8.2, 8.3, and 8.4. 8.1 can certainly be considered broken >> in regards to its lack of good and automatic VACUUM and ANALYZE behavior, >> and you're just seeing the first round of issues in that area. Every minute >> you spend applying temporary fixes to the fundamental issues is time you >> could be better spending toward upgrading instead. > > Also, the HOT updates in 8.3 made a compelling case for us to update, > and if the OP is suffering from table bloat, HOT might help a lot. > These are certainly compelling reasons for me to try upgrading... I will try a test upgrade on a devel server tomorrow using Steve's repo edits. Madi
Madison Kelly wrote: > > You are right, autovacuum is not running after all. From your comment, > I am wondering if you'd recommend I turn it on or not?... > > I see you are considering an upgrade but FWIW on your 8.1 instance, my remaining 8.1 server has been running for years with it on. Read up on it at: http://www.postgresql.org/docs/8.1/static/maintenance.html#AUTOVACUUM Basically you need to turn on some stats stuff so autovacuum can determine when to run (in postgresql.conf): stats_start_collector = on stats_row_level = on And you need to enable autovacuum (in postgresql.conf): autovacuum = on autovacuum_naptime = 300 # time between autovacuum runs, in secs Then you can tune it if you need to but at least it will be looking for things that are vacuumworthy every 5 minutes. Cheers, Steve
+Madison Kelly wrote: > You are right, autovacuum is not running after all. From your comment, I > am wondering if you'd recommend I turn it on or not? If so, given that I > doubt I will upgrade any time soon, how would I enable it? I suppose I > could google that, but google rarely shares gotcha's. :) Most of the pain of a Postgres upgrade is the dump/reload step. But you've already had to do that several times, so whythe hesitation to upgrade? Upgrading Postgres to the latest release, even if you have to do it from the source code,takes almost no time at all compared to the time you've already burned trying to solve this problem. Do the upgrade,you won't regret it. Craig
On Mon, 2010-01-04 at 20:02 -0800, Craig James wrote: > +Madison Kelly wrote: > > You are right, autovacuum is not running after all. From your comment, I > > am wondering if you'd recommend I turn it on or not? If so, given that I > > doubt I will upgrade any time soon, how would I enable it? I suppose I > > could google that, but google rarely shares gotcha's. :) > > Most of the pain of a Postgres upgrade is the dump/reload step. But you've already had to do that several times, so whythe hesitation to upgrade? Upgrading Postgres to the latest release, even if you have to do it from the source code,takes almost no time at all compared to the time you've already burned trying to solve this problem. Actually, the biggest pain going beyond 8.2 is the change to implicit casting. > Do the upgrade, you won't regret it. Agree. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp.
CLUSTER also does *nothing at all* to a table unless you have chosen an index to CLUSTER on. Its not as simple as switchingfrom VACUUM or VACUUM FULL to CLUSTER. Does CLUSTER also REINDEX? I seem to recall reducing the size of my indexes by REINDEXing after a CLUSTER, but it was a whileago and I could have been mistaken. -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Kevin Grittner Sent: Monday, January 04, 2010 1:04 PM To: Madison Kelly; Gary Doades Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com> wrote: > I've added CLUSTER -> ANALYZE -> VACUUM to my nightly > routine and dropped the VACUUM FULL call. The CLUSTER is probably not going to make much difference once you've eliminated bloat, unless your queries do a lot of searches in the sequence of the index used. Be sure to run VACUUM ANALYZE as one statement, not two separate steps. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Scott Carey wrote: > CLUSTER also does *nothing at all* to a table unless you have chosen an index to CLUSTER on. Its not as simple as switchingfrom VACUUM or VACUUM FULL to CLUSTER. > > Does CLUSTER also REINDEX? I seem to recall reducing the size of my indexes by REINDEXing after a CLUSTER, but it was awhile ago and I could have been mistaken. AFAIK CLUSTER builds a new copy of the table, and new indexes for it, then swaps them into the old table and index's place. -- Craig Ringer