pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDB is slow until DB is reloaded
25+ messages / 12 participants
[nested] [flat]
* DB is slow until DB is reloaded
@ 2010-01-04 19:10 Madison Kelly <linux@alteeve.com>
2010-01-04 19:25 ` Re: DB is slow until DB is reloaded Gary Doades <gpd@gpdnet.co.uk>
2010-01-04 19:31 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 2 replies; 25+ messages in thread
From: Madison Kelly @ 2010-01-04 19:10 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: DB is slow until DB is reloaded
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
@ 2010-01-04 19:25 ` Gary Doades <gpd@gpdnet.co.uk>
1 sibling, 0 replies; 25+ messages in thread
From: Gary Doades @ 2010-01-04 19:25 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: DB is slow until DB is reloaded
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
@ 2010-01-04 19:31 ` Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 20:30 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
1 sibling, 1 reply; 25+ messages in thread
From: Steve Crawford @ 2010-01-04 19:31 UTC (permalink / raw)
To: Madison Kelly <linux@alteeve.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: DB is slow until DB is reloaded
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 19:31 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
@ 2010-01-04 20:30 ` Madison Kelly <linux@alteeve.com>
2010-01-04 20:39 ` Re: DB is slow until DB is reloaded Richard Neill <rn214@cam.ac.uk>
2010-01-04 20:40 ` Re: DB is slow until DB is reloaded Gary Doades <gpd@gpdnet.co.uk>
2010-01-04 21:43 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 3 replies; 25+ messages in thread
From: Madison Kelly @ 2010-01-04 20:30 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: DB is slow until DB is reloaded
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 19:31 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 20:30 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
@ 2010-01-04 20:39 ` Richard Neill <rn214@cam.ac.uk>
2 siblings, 0 replies; 25+ messages in thread
From: Richard Neill @ 2010-01-04 20:39 UTC (permalink / raw)
To: Madison Kelly <linux@alteeve.com>; +Cc: Steve Crawford <scrawford@pinpointresearch.com>; pgsql-performance
> 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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: DB is slow until DB is reloaded
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 19:31 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 20:30 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
@ 2010-01-04 20:40 ` Gary Doades <gpd@gpdnet.co.uk>
2010-01-04 20:53 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2 siblings, 1 reply; 25+ messages in thread
From: Gary Doades @ 2010-01-04 20:40 UTC (permalink / raw)
To: Madison Kelly <linux@alteeve.com>; +Cc: pgsql-performance
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
>
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: DB is slow until DB is reloaded
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 19:31 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 20:30 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 20:40 ` Re: DB is slow until DB is reloaded Gary Doades <gpd@gpdnet.co.uk>
@ 2010-01-04 20:53 ` Madison Kelly <linux@alteeve.com>
2010-01-04 21:00 ` Re: DB is slow until DB is reloaded Brad Nicholson <bnichols@ca.afilias.info>
2010-01-04 21:03 ` Re: DB is slow until DB is reloaded Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 2 replies; 25+ messages in thread
From: Madison Kelly @ 2010-01-04 20:53 UTC (permalink / raw)
To: Gary Doades <gpd@gpdnet.co.uk>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: DB is slow until DB is reloaded
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 19:31 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 20:30 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 20:40 ` Re: DB is slow until DB is reloaded Gary Doades <gpd@gpdnet.co.uk>
2010-01-04 20:53 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
@ 2010-01-04 21:00 ` Brad Nicholson <bnichols@ca.afilias.info>
2010-01-04 21:33 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
1 sibling, 1 reply; 25+ messages in thread
From: Brad Nicholson @ 2010-01-04 21:00 UTC (permalink / raw)
To: Madison Kelly <linux@alteeve.com>; +Cc: Gary Doades <gpd@gpdnet.co.uk>; pgsql-performance
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.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: DB is slow until DB is reloaded
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 19:31 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 20:30 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 20:40 ` Re: DB is slow until DB is reloaded Gary Doades <gpd@gpdnet.co.uk>
2010-01-04 20:53 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 21:00 ` Re: DB is slow until DB is reloaded Brad Nicholson <bnichols@ca.afilias.info>
@ 2010-01-04 21:33 ` Madison Kelly <linux@alteeve.com>
2010-01-04 21:53 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 1 reply; 25+ messages in thread
From: Madison Kelly @ 2010-01-04 21:33 UTC (permalink / raw)
To: Brad Nicholson <bnichols@ca.afilias.info>; +Cc: Gary Doades <gpd@gpdnet.co.uk>; pgsql-performance
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: DB is slow until DB is reloaded
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 19:31 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 20:30 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 20:40 ` Re: DB is slow until DB is reloaded Gary Doades <gpd@gpdnet.co.uk>
2010-01-04 20:53 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 21:00 ` Re: DB is slow until DB is reloaded Brad Nicholson <bnichols@ca.afilias.info>
2010-01-04 21:33 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
@ 2010-01-04 21:53 ` Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 21:57 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
0 siblings, 1 reply; 25+ messages in thread
From: Steve Crawford @ 2010-01-04 21:53 UTC (permalink / raw)
To: Madison Kelly <linux@alteeve.com>; +Cc: Brad Nicholson <bnichols@ca.afilias.info>; Gary Doades <gpd@gpdnet.co.uk>; pgsql-performance
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: DB is slow until DB is reloaded
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 19:31 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 20:30 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 20:40 ` Re: DB is slow until DB is reloaded Gary Doades <gpd@gpdnet.co.uk>
2010-01-04 20:53 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 21:00 ` Re: DB is slow until DB is reloaded Brad Nicholson <bnichols@ca.afilias.info>
2010-01-04 21:33 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 21:53 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
@ 2010-01-04 21:57 ` Madison Kelly <linux@alteeve.com>
2010-01-04 22:06 ` Re: DB is slow until DB is reloaded Rosser Schwarz <rosser.schwarz@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Madison Kelly @ 2010-01-04 21:57 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: Brad Nicholson <bnichols@ca.afilias.info>; Gary Doades <gpd@gpdnet.co.uk>; pgsql-performance
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: DB is slow until DB is reloaded
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 19:31 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 20:30 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 20:40 ` Re: DB is slow until DB is reloaded Gary Doades <gpd@gpdnet.co.uk>
2010-01-04 20:53 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 21:00 ` Re: DB is slow until DB is reloaded Brad Nicholson <bnichols@ca.afilias.info>
2010-01-04 21:33 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 21:53 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 21:57 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
@ 2010-01-04 22:06 ` Rosser Schwarz <rosser.schwarz@gmail.com>
2010-01-05 00:34 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
0 siblings, 1 reply; 25+ messages in thread
From: Rosser Schwarz @ 2010-01-04 22:06 UTC (permalink / raw)
To: Madison Kelly <linux@alteeve.com>; +Cc: Steve Crawford <scrawford@pinpointresearch.com>; Brad Nicholson <bnichols@ca.afilias.info>; Gary Doades <gpd@gpdnet.co.uk>; pgsql-performance
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: DB is slow until DB is reloaded
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 19:31 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 20:30 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 20:40 ` Re: DB is slow until DB is reloaded Gary Doades <gpd@gpdnet.co.uk>
2010-01-04 20:53 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 21:00 ` Re: DB is slow until DB is reloaded Brad Nicholson <bnichols@ca.afilias.info>
2010-01-04 21:33 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 21:53 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 21:57 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 22:06 ` Re: DB is slow until DB is reloaded Rosser Schwarz <rosser.schwarz@gmail.com>
@ 2010-01-05 00:34 ` Madison Kelly <linux@alteeve.com>
2010-01-05 01:10 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-05 04:02 ` Re: DB is slow until DB is reloaded Craig James <craig_james@emolecules.com>
0 siblings, 2 replies; 25+ messages in thread
From: Madison Kelly @ 2010-01-05 00:34 UTC (permalink / raw)
To: Rosser Schwarz <rosser.schwarz@gmail.com>; +Cc: Steve Crawford <scrawford@pinpointresearch.com>; Brad Nicholson <bnichols@ca.afilias.info>; Gary Doades <gpd@gpdnet.co.uk>; pgsql-performance
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: DB is slow until DB is reloaded
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 19:31 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 20:30 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 20:40 ` Re: DB is slow until DB is reloaded Gary Doades <gpd@gpdnet.co.uk>
2010-01-04 20:53 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 21:00 ` Re: DB is slow until DB is reloaded Brad Nicholson <bnichols@ca.afilias.info>
2010-01-04 21:33 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 21:53 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 21:57 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 22:06 ` Re: DB is slow until DB is reloaded Rosser Schwarz <rosser.schwarz@gmail.com>
2010-01-05 00:34 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
@ 2010-01-05 01:10 ` Steve Crawford <scrawford@pinpointresearch.com>
1 sibling, 0 replies; 25+ messages in thread
From: Steve Crawford @ 2010-01-05 01:10 UTC (permalink / raw)
To: Madison Kelly <linux@alteeve.com>; +Cc: Rosser Schwarz <rosser.schwarz@gmail.com>; Brad Nicholson <bnichols@ca.afilias.info>; Gary Doades <gpd@gpdnet.co.uk>; pgsql-performance
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: DB is slow until DB is reloaded
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 19:31 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 20:30 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 20:40 ` Re: DB is slow until DB is reloaded Gary Doades <gpd@gpdnet.co.uk>
2010-01-04 20:53 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 21:00 ` Re: DB is slow until DB is reloaded Brad Nicholson <bnichols@ca.afilias.info>
2010-01-04 21:33 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 21:53 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 21:57 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 22:06 ` Re: DB is slow until DB is reloaded Rosser Schwarz <rosser.schwarz@gmail.com>
2010-01-05 00:34 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
@ 2010-01-05 04:02 ` Craig James <craig_james@emolecules.com>
2010-01-05 13:17 ` Re: DB is slow until DB is reloaded Brad Nicholson <bnichols@ca.afilias.info>
1 sibling, 1 reply; 25+ messages in thread
From: Craig James @ 2010-01-05 04:02 UTC (permalink / raw)
To: Madison Kelly <linux@alteeve.com>; +Cc: pgsql-performance
+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 why the 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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: DB is slow until DB is reloaded
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 19:31 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 20:30 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 20:40 ` Re: DB is slow until DB is reloaded Gary Doades <gpd@gpdnet.co.uk>
2010-01-04 20:53 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 21:00 ` Re: DB is slow until DB is reloaded Brad Nicholson <bnichols@ca.afilias.info>
2010-01-04 21:33 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 21:53 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 21:57 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 22:06 ` Re: DB is slow until DB is reloaded Rosser Schwarz <rosser.schwarz@gmail.com>
2010-01-05 00:34 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-05 04:02 ` Re: DB is slow until DB is reloaded Craig James <craig_james@emolecules.com>
@ 2010-01-05 13:17 ` Brad Nicholson <bnichols@ca.afilias.info>
0 siblings, 0 replies; 25+ messages in thread
From: Brad Nicholson @ 2010-01-05 13:17 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: Madison Kelly <linux@alteeve.com>; pgsql-performance
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 why the 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.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: DB is slow until DB is reloaded
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 19:31 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 20:30 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 20:40 ` Re: DB is slow until DB is reloaded Gary Doades <gpd@gpdnet.co.uk>
2010-01-04 20:53 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
@ 2010-01-04 21:03 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-04 21:28 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-06 01:04 ` Re: DB is slow until DB is reloaded Scott Carey <scott@richrelevance.com>
1 sibling, 2 replies; 25+ messages in thread
From: Kevin Grittner @ 2010-01-04 21:03 UTC (permalink / raw)
To: Madison Kelly <linux@alteeve.com>; Gary Doades <gpd@gpdnet.co.uk>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: DB is slow until DB is reloaded
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 19:31 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 20:30 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 20:40 ` Re: DB is slow until DB is reloaded Gary Doades <gpd@gpdnet.co.uk>
2010-01-04 20:53 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 21:03 ` Re: DB is slow until DB is reloaded Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-01-04 21:28 ` Madison Kelly <linux@alteeve.com>
1 sibling, 0 replies; 25+ messages in thread
From: Madison Kelly @ 2010-01-04 21:28 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Gary Doades <gpd@gpdnet.co.uk>; pgsql-performance
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: DB is slow until DB is reloaded
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 19:31 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 20:30 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 20:40 ` Re: DB is slow until DB is reloaded Gary Doades <gpd@gpdnet.co.uk>
2010-01-04 20:53 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 21:03 ` Re: DB is slow until DB is reloaded Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-01-06 01:04 ` Scott Carey <scott@richrelevance.com>
2010-01-06 01:16 ` Re: DB is slow until DB is reloaded Craig Ringer <craig@postnewspapers.com.au>
1 sibling, 1 reply; 25+ messages in thread
From: Scott Carey @ 2010-01-06 01:04 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Madison Kelly <linux@alteeve.com>; Gary Doades <gpd@gpdnet.co.uk>; +Cc: pgsql-performance
CLUSTER also does *nothing at all* to a table unless you have chosen an index to CLUSTER on. Its not as simple as switching from 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 while ago 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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: DB is slow until DB is reloaded
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 19:31 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 20:30 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 20:40 ` Re: DB is slow until DB is reloaded Gary Doades <gpd@gpdnet.co.uk>
2010-01-04 20:53 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 21:03 ` Re: DB is slow until DB is reloaded Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-06 01:04 ` Re: DB is slow until DB is reloaded Scott Carey <scott@richrelevance.com>
@ 2010-01-06 01:16 ` Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 0 replies; 25+ messages in thread
From: Craig Ringer @ 2010-01-06 01:16 UTC (permalink / raw)
To: Scott Carey <scott@richrelevance.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Madison Kelly <linux@alteeve.com>; Gary Doades <gpd@gpdnet.co.uk>; 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 switching from 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 while 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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: DB is slow until DB is reloaded
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 19:31 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 20:30 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
@ 2010-01-04 21:43 ` Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 21:54 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2 siblings, 1 reply; 25+ messages in thread
From: Steve Crawford @ 2010-01-04 21:43 UTC (permalink / raw)
To: Madison Kelly <linux@alteeve.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: DB is slow until DB is reloaded
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 19:31 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 20:30 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 21:43 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
@ 2010-01-04 21:54 ` Madison Kelly <linux@alteeve.com>
2010-01-04 22:13 ` Re: DB is slow until DB is reloaded Greg Smith <greg@2ndquadrant.com>
0 siblings, 1 reply; 25+ messages in thread
From: Madison Kelly @ 2010-01-04 21:54 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: DB is slow until DB is reloaded
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 19:31 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 20:30 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 21:43 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 21:54 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
@ 2010-01-04 22:13 ` Greg Smith <greg@2ndquadrant.com>
2010-01-04 22:51 ` Re: DB is slow until DB is reloaded Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Greg Smith @ 2010-01-04 22:13 UTC (permalink / raw)
To: Madison Kelly <linux@alteeve.com>; +Cc: Steve Crawford <scrawford@pinpointresearch.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: DB is slow until DB is reloaded
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 19:31 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 20:30 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 21:43 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 21:54 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 22:13 ` Re: DB is slow until DB is reloaded Greg Smith <greg@2ndquadrant.com>
@ 2010-01-04 22:51 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-01-05 00:36 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
0 siblings, 1 reply; 25+ messages in thread
From: Scott Marlowe @ 2010-01-04 22:51 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Madison Kelly <linux@alteeve.com>; Steve Crawford <scrawford@pinpointresearch.com>; pgsql-performance
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.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: DB is slow until DB is reloaded
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 19:31 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 20:30 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 21:43 ` Re: DB is slow until DB is reloaded Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 21:54 ` Re: DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 22:13 ` Re: DB is slow until DB is reloaded Greg Smith <greg@2ndquadrant.com>
2010-01-04 22:51 ` Re: DB is slow until DB is reloaded Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-01-05 00:36 ` Madison Kelly <linux@alteeve.com>
0 siblings, 0 replies; 25+ messages in thread
From: Madison Kelly @ 2010-01-05 00:36 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Greg Smith <greg@2ndquadrant.com>; Steve Crawford <scrawford@pinpointresearch.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 25+ messages in thread
end of thread, other threads:[~2010-01-06 01:16 UTC | newest]
Thread overview: 25+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-01-04 19:10 DB is slow until DB is reloaded Madison Kelly <linux@alteeve.com>
2010-01-04 19:25 ` Gary Doades <gpd@gpdnet.co.uk>
2010-01-04 19:31 ` Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 20:30 ` Madison Kelly <linux@alteeve.com>
2010-01-04 20:39 ` Richard Neill <rn214@cam.ac.uk>
2010-01-04 20:40 ` Gary Doades <gpd@gpdnet.co.uk>
2010-01-04 20:53 ` Madison Kelly <linux@alteeve.com>
2010-01-04 21:00 ` Brad Nicholson <bnichols@ca.afilias.info>
2010-01-04 21:33 ` Madison Kelly <linux@alteeve.com>
2010-01-04 21:53 ` Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 21:57 ` Madison Kelly <linux@alteeve.com>
2010-01-04 22:06 ` Rosser Schwarz <rosser.schwarz@gmail.com>
2010-01-05 00:34 ` Madison Kelly <linux@alteeve.com>
2010-01-05 01:10 ` Steve Crawford <scrawford@pinpointresearch.com>
2010-01-05 04:02 ` Craig James <craig_james@emolecules.com>
2010-01-05 13:17 ` Brad Nicholson <bnichols@ca.afilias.info>
2010-01-04 21:03 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-04 21:28 ` Madison Kelly <linux@alteeve.com>
2010-01-06 01:04 ` Scott Carey <scott@richrelevance.com>
2010-01-06 01:16 ` Craig Ringer <craig@postnewspapers.com.au>
2010-01-04 21:43 ` Steve Crawford <scrawford@pinpointresearch.com>
2010-01-04 21:54 ` Madison Kelly <linux@alteeve.com>
2010-01-04 22:13 ` Greg Smith <greg@2ndquadrant.com>
2010-01-04 22:51 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-01-05 00:36 ` Madison Kelly <linux@alteeve.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox