Re: [SPAM] Re: [PERFORM] autovacuum disk IO
Il 02/03/2016 19:40, Alvaro Herrera ha scritto: Scott Marlowe wrote: On Wed, Mar 2, 2016 at 9:11 AM, Moreno Andreo wrote: ... or maybe add some more RAM to have more disk caching (if you're on *nix) this worked for me in the past... even if IMHO it's more a temporary "patch" while upgrading (if it can't be done in a hurry) than a real solution... Oh yeah, definitely worth looking at. But RAM can't speed up writes, just reads, so it's very workload dependent. If you're IO subsystem is maxing out on writes, faster drives / IO. If it's maxing out on reads, more memory. But if your dataset is much bigger than memory (say 64GB RAM and a 1TB data store) then more RAM isn't going to be the answer. In the particular case of autovacuum, it may be helpful to create a "ramdisk" and put the stats temp file in it. Definitely. I my new server (as I've been taught here :-) ) I'm going to put stats in a ramdisk and pg_xlog in another partition. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [SPAM] Re: [PERFORM] autovacuum disk IO
Scott Marlowe wrote: > On Wed, Mar 2, 2016 at 9:11 AM, Moreno Andreo > wrote: > > ... or maybe add some more RAM to have more disk caching (if you're on > > *nix) this worked for me in the past... even if IMHO it's more a > > temporary "patch" while upgrading (if it can't be done in a hurry) than a > > real solution... > > Oh yeah, definitely worth looking at. But RAM can't speed up writes, > just reads, so it's very workload dependent. If you're IO subsystem is > maxing out on writes, faster drives / IO. If it's maxing out on reads, > more memory. But if your dataset is much bigger than memory (say 64GB > RAM and a 1TB data store) then more RAM isn't going to be the answer. In the particular case of autovacuum, it may be helpful to create a "ramdisk" and put the stats temp file in it. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [SPAM] Re: [PERFORM] autovacuum disk IO
On Wed, Mar 2, 2016 at 9:11 AM, Moreno Andreo wrote: > Il 02/03/2016 16:49, Scott Marlowe ha scritto: >> >> On Wed, Mar 2, 2016 at 8:45 AM, Scott Marlowe >> wrote: >>> >>> On Wed, Mar 2, 2016 at 8:25 AM, Artem Tomyuk >>> wrote: Hi. I've noticed that autovac. process worked more than 10 minutes, during this zabbix logged more than 90% IO disk utilization on db volume ===>29237 2016-03-02 15:17:23 EET 0 [24-1]LOG: automatic vacuum of table "lb_upr.public._reference32": index scans: 1 pages: 0 removed, 263307 remain tuples: 298 removed, 1944753 remain, 0 are dead but not yet removable buffer usage: 67814 hits, 265465 misses, 15647 dirtied avg read rate: 3.183 MB/s, avg write rate: 0.188 MB/s system usage: CPU 5.34s/6.27u sec elapsed 651.57 sec Is it possible to log autovac. io impact during it execution? Is there any way to limit or "nice" autovac. process? >>> >>> I'll assume you're running a fairly recent version of postgresql. >>> >>> There are a few settings that adjust how hard autovacuum works when >>> it's working. >>> >>> autovacuum_max_workers tells autovacuum how many threads to vacuum >>> with. Lowering this will limit the impact of autovacuum, but generally >>> the default setting of 3 is reasonable on most machines. >>> >>> autovacuum_vacuum_cost_delay sets how to wail between internal rounds. >>> Raising this makes autovacuum take bigger pauses internally. The >>> default of 20ms is usually large enough to keep you out of trouble, >>> but feel free to raise it and see if your IO utilization lowers. >>> >>> autovacuum_vacuum_cost_limit sets a limit to how much work to do >>> between the pauses set by the cost delay above. Lowering this will >>> cause autovac to do less work between pauses. >>> >>> Most of the time I'm adjusting these I'm making vacuum more >>> aggressive, not less aggressive because vacuum falling behind is a >>> problem on the large, fast production systems I work on. In your case >>> you want to watch for when autovacuum IS running, and using a tool >>> like vmstat or iostat or iotop, watch it for % utilization. You can >>> then adjust cost delay and cost limit to make it less aggressive and >>> see if your io util goes down. >>> >>> Note though that 90% utilization isn't 100% so it's not likely >>> flooding the IO. But if you say raise cost delay from 20 to 40ms, it >>> might drop to 75% or so. The primary goal here is to arrive at numbers >>> that left autovacuum keep up with reclaiming the discarded tuples in >>> the database without getting in the way of the workload. >>> >>> If your workload isn't slowing down, or isn't slowing down very much, >>> during autobvacuum then you're OK. >> >> Just to add a point here. If you're machine can't keep up with >> production load AND the job of vacuuming, then your IO subsystem is >> too slow and needs upgrading. The difference between a pair of >> spinning 7200RPM drives and a pair of enterprise class SSDs (always >> with power off safe writing etc, consumer SSDs can eat your data on >> power off) can be truly huge. I've seen improvements from a few >> hundred transactions per second to thousands of transactions per >> second by a simple upgrade like that. >> >> > ... or maybe add some more RAM to have more disk caching (if you're on > *nix) this worked for me in the past... even if IMHO it's more a > temporary "patch" while upgrading (if it can't be done in a hurry) than a > real solution... Oh yeah, definitely worth looking at. But RAM can't speed up writes, just reads, so it's very workload dependent. If you're IO subsystem is maxing out on writes, faster drives / IO. If it's maxing out on reads, more memory. But if your dataset is much bigger than memory (say 64GB RAM and a 1TB data store) then more RAM isn't going to be the answer. So as usual, to help out OP we might want to know more about his system. There's a lot of helpful tips for reporting slow queries / performance issues here: https://wiki.postgresql.org/wiki/SlowQueryQuestions -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [SPAM] Re: [PERFORM] autovacuum disk IO
Il 02/03/2016 16:49, Scott Marlowe ha scritto: On Wed, Mar 2, 2016 at 8:45 AM, Scott Marlowe wrote: On Wed, Mar 2, 2016 at 8:25 AM, Artem Tomyuk wrote: Hi. I've noticed that autovac. process worked more than 10 minutes, during this zabbix logged more than 90% IO disk utilization on db volume ===>29237 2016-03-02 15:17:23 EET 0 [24-1]LOG: automatic vacuum of table "lb_upr.public._reference32": index scans: 1 pages: 0 removed, 263307 remain tuples: 298 removed, 1944753 remain, 0 are dead but not yet removable buffer usage: 67814 hits, 265465 misses, 15647 dirtied avg read rate: 3.183 MB/s, avg write rate: 0.188 MB/s system usage: CPU 5.34s/6.27u sec elapsed 651.57 sec Is it possible to log autovac. io impact during it execution? Is there any way to limit or "nice" autovac. process? I'll assume you're running a fairly recent version of postgresql. There are a few settings that adjust how hard autovacuum works when it's working. autovacuum_max_workers tells autovacuum how many threads to vacuum with. Lowering this will limit the impact of autovacuum, but generally the default setting of 3 is reasonable on most machines. autovacuum_vacuum_cost_delay sets how to wail between internal rounds. Raising this makes autovacuum take bigger pauses internally. The default of 20ms is usually large enough to keep you out of trouble, but feel free to raise it and see if your IO utilization lowers. autovacuum_vacuum_cost_limit sets a limit to how much work to do between the pauses set by the cost delay above. Lowering this will cause autovac to do less work between pauses. Most of the time I'm adjusting these I'm making vacuum more aggressive, not less aggressive because vacuum falling behind is a problem on the large, fast production systems I work on. In your case you want to watch for when autovacuum IS running, and using a tool like vmstat or iostat or iotop, watch it for % utilization. You can then adjust cost delay and cost limit to make it less aggressive and see if your io util goes down. Note though that 90% utilization isn't 100% so it's not likely flooding the IO. But if you say raise cost delay from 20 to 40ms, it might drop to 75% or so. The primary goal here is to arrive at numbers that left autovacuum keep up with reclaiming the discarded tuples in the database without getting in the way of the workload. If your workload isn't slowing down, or isn't slowing down very much, during autobvacuum then you're OK. Just to add a point here. If you're machine can't keep up with production load AND the job of vacuuming, then your IO subsystem is too slow and needs upgrading. The difference between a pair of spinning 7200RPM drives and a pair of enterprise class SSDs (always with power off safe writing etc, consumer SSDs can eat your data on power off) can be truly huge. I've seen improvements from a few hundred transactions per second to thousands of transactions per second by a simple upgrade like that. ... or maybe add some more RAM to have more disk caching (if you're on *nix) this worked for me in the past... even if IMHO it's more a temporary "patch" while upgrading (if it can't be done in a hurry) than a real solution... Cheers Moreno. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum disk IO
On Wed, Mar 2, 2016 at 8:45 AM, Scott Marlowe wrote: > On Wed, Mar 2, 2016 at 8:25 AM, Artem Tomyuk wrote: >> Hi. >> >> I've noticed that autovac. process worked more than 10 minutes, during this >> zabbix logged more than 90% IO disk utilization on db volume >> >> ===>29237 2016-03-02 15:17:23 EET 0 [24-1]LOG: automatic >> vacuum of table "lb_upr.public._reference32": index scans: 1 >> pages: 0 removed, 263307 remain >> tuples: 298 removed, 1944753 remain, 0 are dead but not yet removable >> buffer usage: 67814 hits, 265465 misses, 15647 dirtied >> avg read rate: 3.183 MB/s, avg write rate: 0.188 MB/s >> system usage: CPU 5.34s/6.27u sec elapsed 651.57 sec >> >> Is it possible to log autovac. io impact during it execution? >> Is there any way to limit or "nice" autovac. process? > > I'll assume you're running a fairly recent version of postgresql. > > There are a few settings that adjust how hard autovacuum works when > it's working. > > autovacuum_max_workers tells autovacuum how many threads to vacuum > with. Lowering this will limit the impact of autovacuum, but generally > the default setting of 3 is reasonable on most machines. > > autovacuum_vacuum_cost_delay sets how to wail between internal rounds. > Raising this makes autovacuum take bigger pauses internally. The > default of 20ms is usually large enough to keep you out of trouble, > but feel free to raise it and see if your IO utilization lowers. > > autovacuum_vacuum_cost_limit sets a limit to how much work to do > between the pauses set by the cost delay above. Lowering this will > cause autovac to do less work between pauses. > > Most of the time I'm adjusting these I'm making vacuum more > aggressive, not less aggressive because vacuum falling behind is a > problem on the large, fast production systems I work on. In your case > you want to watch for when autovacuum IS running, and using a tool > like vmstat or iostat or iotop, watch it for % utilization. You can > then adjust cost delay and cost limit to make it less aggressive and > see if your io util goes down. > > Note though that 90% utilization isn't 100% so it's not likely > flooding the IO. But if you say raise cost delay from 20 to 40ms, it > might drop to 75% or so. The primary goal here is to arrive at numbers > that left autovacuum keep up with reclaiming the discarded tuples in > the database without getting in the way of the workload. > > If your workload isn't slowing down, or isn't slowing down very much, > during autobvacuum then you're OK. Just to add a point here. If you're machine can't keep up with production load AND the job of vacuuming, then your IO subsystem is too slow and needs upgrading. The difference between a pair of spinning 7200RPM drives and a pair of enterprise class SSDs (always with power off safe writing etc, consumer SSDs can eat your data on power off) can be truly huge. I've seen improvements from a few hundred transactions per second to thousands of transactions per second by a simple upgrade like that. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum disk IO
On Wed, Mar 2, 2016 at 8:25 AM, Artem Tomyuk wrote: > Hi. > > I've noticed that autovac. process worked more than 10 minutes, during this > zabbix logged more than 90% IO disk utilization on db volume > > ===>29237 2016-03-02 15:17:23 EET 0 [24-1]LOG: automatic > vacuum of table "lb_upr.public._reference32": index scans: 1 > pages: 0 removed, 263307 remain > tuples: 298 removed, 1944753 remain, 0 are dead but not yet removable > buffer usage: 67814 hits, 265465 misses, 15647 dirtied > avg read rate: 3.183 MB/s, avg write rate: 0.188 MB/s > system usage: CPU 5.34s/6.27u sec elapsed 651.57 sec > > Is it possible to log autovac. io impact during it execution? > Is there any way to limit or "nice" autovac. process? I'll assume you're running a fairly recent version of postgresql. There are a few settings that adjust how hard autovacuum works when it's working. autovacuum_max_workers tells autovacuum how many threads to vacuum with. Lowering this will limit the impact of autovacuum, but generally the default setting of 3 is reasonable on most machines. autovacuum_vacuum_cost_delay sets how to wail between internal rounds. Raising this makes autovacuum take bigger pauses internally. The default of 20ms is usually large enough to keep you out of trouble, but feel free to raise it and see if your IO utilization lowers. autovacuum_vacuum_cost_limit sets a limit to how much work to do between the pauses set by the cost delay above. Lowering this will cause autovac to do less work between pauses. Most of the time I'm adjusting these I'm making vacuum more aggressive, not less aggressive because vacuum falling behind is a problem on the large, fast production systems I work on. In your case you want to watch for when autovacuum IS running, and using a tool like vmstat or iostat or iotop, watch it for % utilization. You can then adjust cost delay and cost limit to make it less aggressive and see if your io util goes down. Note though that 90% utilization isn't 100% so it's not likely flooding the IO. But if you say raise cost delay from 20 to 40ms, it might drop to 75% or so. The primary goal here is to arrive at numbers that left autovacuum keep up with reclaiming the discarded tuples in the database without getting in the way of the workload. If your workload isn't slowing down, or isn't slowing down very much, during autobvacuum then you're OK. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum disk IO
Hi 2016-03-02 16:25 GMT+01:00 Artem Tomyuk : > Hi. > > I've noticed that autovac. process worked more than 10 minutes, during > this zabbix logged more than 90% IO disk utilization on db volume > > ===>29237 2016-03-02 15:17:23 EET 0 [24-1]LOG: automatic > vacuum of table "lb_upr.public._reference32": index scans: 1 > pages: 0 removed, 263307 remain > tuples: 298 removed, 1944753 remain, 0 are dead but not yet removable > buffer usage: 67814 hits, 265465 misses, 15647 dirtied > avg read rate: 3.183 MB/s, avg write rate: 0.188 MB/s > *system usage: CPU 5.34s/6.27u sec elapsed 651.57 sec* > > Is it possible to log autovac. io impact during it execution? > Is there any way to limit or "nice" autovac. process? > > Thanks to all for any help. > > maybe offtopic - there is known problem of Zabbix. Any limits for vacuum are usually way to hell. But more times the partitioning helps to Zabbix https://www.zabbix.org/wiki/Higher_performant_partitioning_in_PostgreSQL Regards Pavel
Re: [PERFORM] autovacuum vacuum creates bad statistics for planner when it log index scans: 0
tim_wilson writes: > On a 9.3.1 server , I have a key busy_table in that is hit by most > transactions running on our system. One DB's copy of this table has 60K rows > and 1/3 of that tables rows can updated every minute. > Autovacuum autovacuum_analyze_scale_factor is set 0.02, so that analyse runs > nearly every minute. But when autovacuum vacuum runs I sometimes see the > following message in logs: > LOG: automatic vacuum of table "busy_table":* index scans: 0* > pages: 0 removed, 22152 remain > tuples: 0 removed, 196927 remain > buffer usage: 46241 hits, 478 misses, 715 dirtied > avg read rate: 0.561 MB/s, avg write rate: 0.839 MB/s > system usage: CPU 0.07s/0.06u sec elapsed 6.66 sec > and the tuples remaining is then overestimated by a factor >3 , and have > seen this over estimate as large at >20 times IE 5M FWIW, I tried to reproduce this without success. There's some code in there that attempts to extrapolate the total number of live tuples when VACUUM has not scanned the entire table. It's surely plausible that that logic went off the rails ... but without a test case or at least a more specific description of the problem scenario, it's hard to know what's wrong exactly. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum and dead tuples
On 09/18/2013 10:42 AM, fburg...@radiantblue.com wrote: My colleague is under the impression that dead tuples are only cleaned up via vacuum full only, while I suggested that the autovaccum process was cleaning up these dead tuples. Is this true? You are correct. Only VACUUM FULL (or CLUSTER) physically removes dead tuples from the table, but a regular VACUUM enters them into the free space map for reuse, so they wouldn't show up in the dead_tuple_count column in pgstattuple. It's possible your colleague was confused by the physical removal versus reassignment. Keep in mind that the dead tuples are still in the table, but reusable. The free_space column and free_percent is a better description of table bloat from data turnover cleaned up by autovacuum. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum fringe case?
On Wednesday, January 23, 2013, AJ Weber wrote: > > > Is there a skinny set of instructions on loading pg_stat_statements? Or > should I just log them and review them from there? > Make sure you have installed contrib. (How you do that depends on how you installed PostgreSQL in the first place. If you installed from source, then just follow "sudo make install" with "cd contrib; sudo make install") Then, just change postgresql.conf so that shared_preload_libraries = 'pg_stat_statements' And restart the server. Then in psql run create extension pg_stat_statements ; Cheers, Jeff
Re: [PERFORM] autovacuum fringe case?
AJ Weber escribió: > On 1/23/2013 2:13 PM, Jeff Janes wrote: > >Scheduling a manual vacuum should be fine (but keep in mind that > >vacuum has very different default cost_delay settings than autovacuum > >does. If the server is completely idle that shouldn't matter, but if > >it is only mostly idle, you might want to throttle the IO a bit). But > >I certainly would not disable autovacuum without further evidence. If > >a table only needs to be vacuumed once a day and you preemptively do > >it at 3a.m., then autovac won't bother to do it itself during the day. > > So there is no point, but much risk, in also turning autovac off. > If I set autovacuum_max_workers = 1, will that effectively > single-thread it so I don't have two running at once? Maybe that'll > mitigate disk contention a little at least? If you have a single one, it will go three times as fast. If you want to make the whole thing go slower (i.e. cause less impact on your I/O system when running), crank up autovacuum_vacuum_cost_delay. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum fringe case?
On 1/23/2013 2:13 PM, Jeff Janes wrote: On Wed, Jan 23, 2013 at 8:53 AM, AJ Weber wrote: I have a server that is IO-bound right now (it's 4 cores, and top indicates the use rarely hits 25%, but the Wait spikes above 25-40% regularly). How long do the spikes last? From what I can gather, a few seconds to a few minutes. The server is running postgresql 9.0 and tomcat 6. As I have mentioned in a previous thread, I can't alter the hardware to add disks unfortunately, so I'm going to try and move postgresql off this application server to its own host, but this is a production environment, so in the meantime... Is it possible that some spikes in IO could be attributable to the autovacuum process? Is there a way to check this theory? set log_autovacuum_min_duration to 0 or some positive number, and see if the vacuums correlate with periods of io stress (from sar or vmstat, for example--the problem is that sar only takes snapshots every 10 minutes, which is too coarse if the spikes are short). I used iotop last time it was going crazy, and there were 5 postgres procs at the top of the list (and virtually nothing else) all doing a SELECT. So I'm also going to restart the DB this weekend with log-min-duration enabled. Could also be some misbehaving queries... Is there a skinny set of instructions on loading pg_stat_statements? Or should I just log them and review them from there? Would it be advisable (or even permissible to try/test) to disable autovacuum, and schedule a manual vacuumdb in the middle of the night, when this server is mostly-idle? Scheduling a manual vacuum should be fine (but keep in mind that vacuum has very different default cost_delay settings than autovacuum does. If the server is completely idle that shouldn't matter, but if it is only mostly idle, you might want to throttle the IO a bit). But I certainly would not disable autovacuum without further evidence. If a table only needs to be vacuumed once a day and you preemptively do it at 3a.m., then autovac won't bother to do it itself during the day. So there is no point, but much risk, in also turning autovac off. If I set autovacuum_max_workers = 1, will that effectively single-thread it so I don't have two running at once? Maybe that'll mitigate disk contention a little at least? Cheers, Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum fringe case?
AJ Weber wrote: > Is it possible that some spikes in IO could be attributable to > the autovacuum process? Is there a way to check this theory? Taking a look at the ps aux listing, pg_stat_activity, and pg_locks should help establish a cause, or at least rule out a number of possibilities. There is a known issue with autovacuum when it tries to reduce the size of a table which is found to be larger than it currently needs to be while other transactions try to access the table. This issue will be fixed in the next minor release for 9.0 and above. If this is the issue a manual VACUUM ANALYZE will fix things -- at least for a while. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum fringe case?
On Wed, Jan 23, 2013 at 8:53 AM, AJ Weber wrote: > I have a server that is IO-bound right now (it's 4 cores, and top indicates > the use rarely hits 25%, but the Wait spikes above 25-40% regularly). How long do the spikes last? > The > server is running postgresql 9.0 and tomcat 6. As I have mentioned in a > previous thread, I can't alter the hardware to add disks unfortunately, so > I'm going to try and move postgresql off this application server to its own > host, but this is a production environment, so in the meantime... > > Is it possible that some spikes in IO could be attributable to the > autovacuum process? Is there a way to check this theory? set log_autovacuum_min_duration to 0 or some positive number, and see if the vacuums correlate with periods of io stress (from sar or vmstat, for example--the problem is that sar only takes snapshots every 10 minutes, which is too coarse if the spikes are short). > Would it be advisable (or even permissible to try/test) to disable > autovacuum, and schedule a manual vacuumdb in the middle of the night, when > this server is mostly-idle? Scheduling a manual vacuum should be fine (but keep in mind that vacuum has very different default cost_delay settings than autovacuum does. If the server is completely idle that shouldn't matter, but if it is only mostly idle, you might want to throttle the IO a bit). But I certainly would not disable autovacuum without further evidence. If a table only needs to be vacuumed once a day and you preemptively do it at 3a.m., then autovac won't bother to do it itself during the day. So there is no point, but much risk, in also turning autovac off. Cheers, Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum fringe case?
On 23.01.2013, at 20:53, AJ Weber wrote: > I have a server that is IO-bound right now (it's 4 cores, and top indicates > the use rarely hits 25%, but the Wait spikes above 25-40% regularly). The > server is running postgresql 9.0 and tomcat 6. As I have mentioned in a > previous thread, I can't alter the hardware to add disks unfortunately, so > I'm going to try and move postgresql off this application server to its own > host, but this is a production environment, so in the meantime... > > Is it possible that some spikes in IO could be attributable to the autovacuum > process? Is there a way to check this theory? > Try iotop > Would it be advisable (or even permissible to try/test) to disable > autovacuum, and schedule a manual vacuumdb in the middle of the night, when > this server is mostly-idle? > > Thanks for any tips. I'm in a bit of a jam with my limited hardware. > > -AJ > > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum, exclude table
"Anibal David Acosta" writes: > Tables has insert (in bulk every 3 minutes) and delete one per day (delete > records older than XX days) No updates at all, just inserts and a daily delete? If so, you're wasting your time even thinking about suppressing autovacuum, because it won't fire on this table except after the daily delete, which is exactly when you need it to. Also, if you suppress autovacuum you also suppress autoanalyze, which is something that *will* fire after large inserts, and probably should. At least, this usage pattern doesn't suggest to me that it's clearly safe to run without up-to-date stats. Right offhand, I'm not convinced either that you have a problem, or that turning off autovacuum would fix it. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum, exclude table
On 12.12.2011 16:25, Anibal David Acosta wrote: I have a couple of tables with about 400millions of records increasing about 5 millions per day. I think that disabling autovac over those tables, and enabling daily manual vacuum (in some idle hour) will be better. I am right? Possibly. If the system is otherwise idle, it sounds sensible to do routine maintenance at that time. Is possible to exclude autovacuum over some tables? Sure, see http://www.postgresql.org/docs/9.1/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS ALTER TABLE foo SET (autovacuum_enabled=false, toast.autovacuum_enabled = false); It might be better, though, to let autovacuum enabled, and just do the additional manual VACUUM in the idle period. If the daily manual VACUUM is enough to keep the bloat within the autovacuum thresholds, autovacuum will never kick in. If it's not enough, then you probably want autovacuum to run. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum, exclude table
The postgres version is 9.0.4 on a Windows Server 2008 (planning to upgrade to 9.1) Tables has insert (in bulk every 3 minutes) and delete one per day (delete records older than XX days) There are not much additional relevant information. Thanks! De: Craig Ringer [mailto:ring...@ringerc.id.au] Enviado el: lunes, 12 de diciembre de 2011 11:45 a.m. Para: Anibal David Acosta CC: pgsql-performance@postgresql.org Asunto: Re: [PERFORM] autovacuum, exclude table Top-posting because this is context free: You need to provide more info for anybody to help you. Are the tables append-only or are deletes/updates also performed? Also this: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems On Dec 12, 2011 10:26 PM, "Anibal David Acosta" wrote: I have a couple of tables with about 400millions of records increasing about 5 millions per day. I think that disabling autovac over those tables, and enabling daily manual vacuum (in some idle hour) will be better. I am right? Is possible to exclude autovacuum over some tables? Thanks! Anibal
Re: [PERFORM] autovacuum, exclude table
Top-posting because this is context free: You need to provide more info for anybody to help you. Are the tables append-only or are deletes/updates also performed? Also this: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems On Dec 12, 2011 10:26 PM, "Anibal David Acosta" wrote: > I have a couple of tables with about 400millions of records increasing > about 5 millions per day. > > ** ** > > I think that disabling autovac over those tables, and enabling daily > manual vacuum (in some idle hour) will be better. > > ** ** > > I am right? > > ** ** > > Is possible to exclude autovacuum over some tables? > > ** ** > > Thanks! > > ** ** > > Anibal >
Re: [PERFORM] autovacuum, any log?
On Wed, Dec 7, 2011 at 8:34 AM, Anibal David Acosta wrote: > Hello, I have a postgres 9.0.2 installation. > > Every works fine, but in some hours of day I got several timeout in my > application (my application wait X seconds before throw a timeout). > > Normally hours are not of intensive use, so I think that the autovacuum > could be the problem. > > > > Is threre any log where autovacuum write information about it self like > “duration for each table” or any other relevante information. > > > > Another inline question, should I exclude bigger tables from autovacuum or > there are some mechanism to tell autovacuum to not run often on bigger > tables (tables with more than 400 millions of rows) More often than not not the problem will be checkpoint segments not autovacuum. log vacuum and checkpoints, and then run something like iostat in the background and keep an eye on %util to see if one or the other is slamming your IO subsystem. Default tuning for autovac is pretty conservative, to the point that it won't usually hurt your IO, but may not keep up with vaccuming, leading to table bloating. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Autovacuum Issue
On Tue, Nov 22, 2011 at 10:55 PM, J Ramesh Kumar wrote: > But the autovacuum is running frequently and it impact the performance of my > system(high CPU). You can see the autovacuum in the pg_stat_activity. Could you show us the system metrics that led you to believe it was high CPU usage? Sometimes people misinterpret the numbers from utilities like top, iostat, or vmstat, so I'd rather see them myself if you got them. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Autovacuum Issue
On 02/12/11 07:18, Robert Haas wrote: And also please share your views on my decision about disable autovacuum for my application. I am planning to run vacuum command daily on that small table which has frequent updates. Sounds like a bad plan. If the table has frequent updates vacuuming once a day will not control space bloat from dead rows... so your small table's storage will become a very large (even though there are only a few undeleted rows), and performance will become terrible. I would suggest tuning autovacuum to wakeup more frequently (c.f autovacuum_naptime parameter), so your small table stays small. Also you didn't mention what version of Postgres you are running. In 8.4 and later vacuum (hence autovacuum) is much smarter about finding dead rows to clean up, and should have less impact. You can also control the load autovacuum puts on your system (c.f autovacuum_vacuum_cost_delay parameter). regards Mark -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Autovacuum Issue
On Wed, Nov 23, 2011 at 12:55 AM, J Ramesh Kumar wrote: > Why the autovacuum is running even though, I disabled ? Am I miss anything ? As Raghavendra says, anti-wraparound vacuum will always kick in to prevent a database shutdown. > And also please share your views on my decision about disable autovacuum for > my application. I am planning to run vacuum command daily on that small > table which has frequent updates. Sounds like a bad plan. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Autovacuum Issue
On Wed, Nov 23, 2011 at 11:25 AM, J Ramesh Kumar wrote: > Hi, > > My application is performing 1600 inserts per second and 7 updates per > second. The updates occurred only in a small table which has only 6 integer > columns. The inserts occurred in all other daily tables. My application > creates around 75 tables per day. No updates/deletes occurred in those 75 > daily tables (only inserts and drop tables if older than 40 days). Since > only inserts in the daily tables, I disabled autovacuum in the conf file > and I can see it is off stat using show command. > > *sasdb=# show "autovacuum"; > autovacuum > > off > (1 row)* > > But the autovacuum is running frequently and it impact the performance of > my system(high CPU). You can see the autovacuum in the pg_stat_activity. > * > sasdb=# select current_query from pg_stat_activity where current_query > like 'autovacuum%'; > ** current_query ** > > --**--** > --- > autovacuum: VACUUM public.x**_17_Oct_11 (to prevent wraparound) > autovacuum: VACUUM public.**x**_17_Oct_11 (to prevent wraparound) > autovacuum: VACUUM public.**x**_17_Oct_11 (to prevent wraparound) > (3 rows) > > > * Its pretty clear, its to prevent tranx wrap-around. autovacuum_freeze_max_age (integer) Specifies the maximum age (in transactions) that a table's pg_class. relfrozenxid field can attain before a VACUUM operation is forced to prevent transaction ID wraparound within the table. Note that the system will launch autovacuum processes to prevent wraparound even when autovacuum is otherwise disabled. http://developer.postgresql.org/pgdocs/postgres/runtime-config-autovacuum.html --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ > **Why the autovacuum is running even though, I disabled ? Am I miss > anything ? > > And also please share your views on my decision about disable autovacuum > for my application. I am planning to run vacuum command daily on that small > table which has frequent updates. > > Thanks, > Ramesh >
Re: [PERFORM] Autovacuum running out of memory
=?UTF-8?B?QWxleGlzIEzDqi1RdcO0Yw==?= writes: > On Wed, Aug 10, 2011 at 2:54 PM, Tom Lane wrote: >> Hmph. Is there other stuff being run on the same instance? Are there a >> whole lot of active PG processes? Maybe Amazon isn't really giving you >> a whole 8GB, or there are weird address space restrictions in the EC2 >> environment. Anyway I think I'd suggest reducing shared_buffers to 1GB >> or so. > Done and that fixed it. Thanks. > Now this is counter-intuitive (so much for intuition). > Any pointers to educate myself on why more shared buffers is > detrimental? My guess is that it's an address space restriction at bottom. Postgres starts (on typical systems) with program text at the beginning of its address space, static data after that, a large hole in the middle, and stack up at the top. Then the shared memory block gets allocated somewhere in the hole, at a spot that's more or less at the whim of the OS. If a Postgres process subsequently asks for more private memory via malloc, it can only possibly get as much as the distance from the original static area to the shared memory block's position in the process's address space. So I'm thinking that the EC2 environment is giving you some lowball address for the shared memory block that's constraining the process's private memory space to just a few hundred meg, even though in a 64-bit build there's room for umpteen gigabytes. Possibly it's worth filing a bug with Amazon about how they should pick a more sensible address ... but first you should confirm that theory by looking at the process memory map (should be visible in /proc someplace). It may also be that the problem is not process-address-space related but reflects some inefficiency in EC2's overall use of RAM, possibly exacerbated by PG's request for a large shared memory block. But you'd need to find an EC2 expert to investigate that idea. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Autovacuum running out of memory
On Wed, Aug 10, 2011 at 2:54 PM, Tom Lane wrote: > Alexis Le-Quoc writes: >> On Wed, Aug 10, 2011 at 1:17 PM, Tom Lane wrote: >>> However, I find it a bit odd that you're getting this failure in what >>> appears to be a 64-bit build. That means you're not running out of >>> address space, so you must actually be out of RAM+swap. Does the >>> machine have only 4GB or so of RAM? If so, that value for >>> shared_buffers is unrealistically large; it's not leaving enough RAM for >>> other purposes such as this. > >> The box has little under 8GB (it's on EC2, a "m1.large" instance) >> There is no swap. > > Hmph. Is there other stuff being run on the same instance? Are there a > whole lot of active PG processes? Maybe Amazon isn't really giving you > a whole 8GB, or there are weird address space restrictions in the EC2 > environment. Anyway I think I'd suggest reducing shared_buffers to 1GB > or so. > Done and that fixed it. Thanks. Now this is counter-intuitive (so much for intuition). Any pointers to educate myself on why more shared buffers is detrimental? I thought they would only compete with the OS page cache. Could it be caused by the "no-overcommit" policy that I told the kernel to enforce. As far as other things running on the same instance, nothing stands out. It is a "dedicated" db instance. >>> Where did you get the above-quoted parameter settings, anyway? > >> In turn they come from High-Performance Postgresql 9.0 >> (http://www.postgresql.org/about/news.1249) > > I'm sure even Greg wouldn't claim his methods are good to more than one > or two significant digits. Agreed, they are meaningless. I just did not make the effort to automatically round the values in my ruby code. -- Alexis Lê-Quôc -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Autovacuum running out of memory
=?UTF-8?B?QWxleGlzIEzDqi1RdcO0Yw==?= writes: > On Wed, Aug 10, 2011 at 1:17 PM, Tom Lane wrote: >> However, I find it a bit odd that you're getting this failure in what >> appears to be a 64-bit build. That means you're not running out of >> address space, so you must actually be out of RAM+swap. Does the >> machine have only 4GB or so of RAM? If so, that value for >> shared_buffers is unrealistically large; it's not leaving enough RAM for >> other purposes such as this. > The box has little under 8GB (it's on EC2, a "m1.large" instance) > There is no swap. Hmph. Is there other stuff being run on the same instance? Are there a whole lot of active PG processes? Maybe Amazon isn't really giving you a whole 8GB, or there are weird address space restrictions in the EC2 environment. Anyway I think I'd suggest reducing shared_buffers to 1GB or so. >> Where did you get the above-quoted parameter settings, anyway? > In turn they come from High-Performance Postgresql 9.0 > (http://www.postgresql.org/about/news.1249) I'm sure even Greg wouldn't claim his methods are good to more than one or two significant digits. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Autovacuum running out of memory
On Wed, Aug 10, 2011 at 1:17 PM, Tom Lane wrote: > Alexis Le-Quoc writes: >> I've been hitting a "out of memory error" during autovacuum of >> relatively large tables (compared to the amount of RAM available). > >> The error message is: >> [10236]: [1-1] user=,db=,remote= ERROR: out of memory >> [10236]: [2-1] user=,db=,remote= DETAIL: Failed on request of size >> 395973594. >> [10236]: [3-1] user=,db=,remote= CONTEXT: automatic vacuum of table >> "***.public.serialized_series" > >> --- postgresql.conf (subset) >> shared_buffers = 1971421kB >> work_mem = 9857kB >> maintenance_work_mem = 752MB > > Since the memory map shows that not very much memory has been allocated > by VACUUM yet, I suspect it's failing while trying to create the work > array for remembering dead tuple TIDs. It will assume that it can use > up to maintenance_work_mem for that. (The fact that it didn't ask for > the whole 752MB probably means this is a relatively small table in > which there couldn't possibly be that many TIDs.) So the short answer > is "reduce maintenance_work_mem to something under 300MB". > > However, I find it a bit odd that you're getting this failure in what > appears to be a 64-bit build. That means you're not running out of > address space, so you must actually be out of RAM+swap. Does the > machine have only 4GB or so of RAM? If so, that value for > shared_buffers is unrealistically large; it's not leaving enough RAM for > other purposes such as this. The box has little under 8GB (it's on EC2, a "m1.large" instance) total used free sharedbuffers cached Mem: 7700 6662 1038 0 25 6078 -/+ buffers/cache:558 7142 Swap:0 0 0 There is no swap. > Where did you get the above-quoted parameter settings, anyway? They > seem a bit weird, as in written to many more decimal places than anyone > could really expect to mean anything. I have them computed by our configuration management system. Here's the logic behind it (edited from ruby): # Compute shared memory for procps page_size = getconf PAGE_SIZE phys_pages = getconf _PHYS_PAGES shmall = phys_pages shmmax = shmall * page_size shared_buffers = kb_memory_total / 4 work_mem = (kb_memory_total / max_connections / 4) maintenance_work_mem = (kb_memory_total * 100 / (1024 * 1024)) In turn they come from High-Performance Postgresql 9.0 (http://www.postgresql.org/about/news.1249) Thanks, -- Alexis Lê-Quôc -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Autovacuum running out of memory
=?UTF-8?B?QWxleGlzIEzDqi1RdcO0Yw==?= writes: > I've been hitting a "out of memory error" during autovacuum of > relatively large tables (compared to the amount of RAM available). > The error message is: > [10236]: [1-1] user=,db=,remote= ERROR: out of memory > [10236]: [2-1] user=,db=,remote= DETAIL: Failed on request of size 395973594. > [10236]: [3-1] user=,db=,remote= CONTEXT: automatic vacuum of table > "***.public.serialized_series" > --- postgresql.conf (subset) > shared_buffers = 1971421kB > work_mem = 9857kB > maintenance_work_mem = 752MB Since the memory map shows that not very much memory has been allocated by VACUUM yet, I suspect it's failing while trying to create the work array for remembering dead tuple TIDs. It will assume that it can use up to maintenance_work_mem for that. (The fact that it didn't ask for the whole 752MB probably means this is a relatively small table in which there couldn't possibly be that many TIDs.) So the short answer is "reduce maintenance_work_mem to something under 300MB". However, I find it a bit odd that you're getting this failure in what appears to be a 64-bit build. That means you're not running out of address space, so you must actually be out of RAM+swap. Does the machine have only 4GB or so of RAM? If so, that value for shared_buffers is unrealistically large; it's not leaving enough RAM for other purposes such as this. Where did you get the above-quoted parameter settings, anyway? They seem a bit weird, as in written to many more decimal places than anyone could really expect to mean anything. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum blocks the operations of other manual vacuum
Thanks for your information. I am using postgresql 8.4 and this version should have already supported HOT. The frequently updated columns are not indexed columns. So, the frequent updates should not create many dead records. I also did a small test. If I don't execute vacuum, the number of pages of the small table does not increase. However, analyzing the big table still bothers me. According current results, if the analyze operation is triggered, vacuum or HOT would not function as I expect. On Sat, Nov 20, 2010 at 12:43 PM, wrote: >> Excerpts from kuopo's message of jue nov 18 04:10:36 -0300 2010: >>> However, when I analyze the table A, the autovacuum or vacuum on the >>> table B cannot find any removable row version (the number of >>> nonremoveable row versions and pages keeps increasing). After the >>> analysis finishes, the search operations on the table B is still >>> inefficient. If I call full vacuum right now, then I can have quick >>> response time of the search operations on the table B again. > > Hi, I don't know how to fix the long VACUUM/ANALYZE, but have you tried to > minimize the growth using HOT? > > HOT means that if you update only columns that are not indexed, and if the > update can fit into the same page (into an update chain), this would not > create a dead row. > > Are there any indexes on the small table? How large is it? You've > mentioned there are about 2049 rows - that might be just a few pages so > the indexes would not be very efficient anyway. > > Try to remove the indexes, and maybe create the table with a smaller > fillfactor (so that there is more space for the updates). > > That should be much more efficient and the table should not grow. > > You can see if HOT works through pg_stat_all_tables view (columns > n_tup_upd and n_tup_hot_upd). > > regards > Tomas > > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum blocks the operations of other manual vacuum
In my experiment, I need about 1~3 min to finish the analyze operation on the big table (which depends on the value of vacuum_cost_delay). I am not surprised because this table is a really big one (now, it has over 200M records). However, the most of my concerns is the behavior of analyze/vacuum. You mentioned that the analyze-only operation cannot be optimized as the same way on optimizing vacuum. Does that mean the analyze operation on a table would unavoidably affect the vacuum proceeded on another one? If this is a normal reaction for an analyze operation, maybe I should try to lower vacuum_cost_delay or use more powerful hardware to minimize the interfered period. So, the pages for the small table would not increase quickly. Do you have any suggestion? Thanks!! On Sat, Nov 20, 2010 at 9:49 AM, Alvaro Herrera wrote: > Excerpts from kuopo's message of jue nov 18 04:10:36 -0300 2010: >> Hi, >> >> Thanks for your response. I've checked it again and found that the >> main cause is the execution of ANALYZE. As I have mentioned, I have >> two tables: table A is a big one (around 10M~100M records) for log >> data and table B is a small one (around 1k records) for keeping some >> current status. There are a lot of update operations and some search >> operations on the table B. For the performance issue, I would like to >> keep table B as compact as possible. According your suggestion, I try >> to invoke standard vacuum (not full) more frequently (e.g., once per >> min). >> >> However, when I analyze the table A, the autovacuum or vacuum on the >> table B cannot find any removable row version (the number of >> nonremoveable row versions and pages keeps increasing). After the >> analysis finishes, the search operations on the table B is still >> inefficient. If I call full vacuum right now, then I can have quick >> response time of the search operations on the table B again. > > Hmm, I don't think we can optimize the analyze-only operation the same > way we optimize vacuum (i.e. allow vacuum to proceed while it's in > progress). Normally analyze shouldn't take all that long anyway -- why > is it that slow? Are you calling it in a transaction that also does > other stuff? Are you analyzing more than one table in a single > transaction, perhaps even the whole database? > > Perhaps you could speed it up by lowering vacuum_cost_delay, if it's set > to a nonzero value. > > -- > Álvaro Herrera > The PostgreSQL Company - Command Prompt, Inc. > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum blocks the operations of other manual vacuum
Excerpts from kuopo's message of dom nov 21 11:15:52 -0300 2010: > In my experiment, I need about 1~3 min to finish the analyze operation > on the big table (which depends on the value of vacuum_cost_delay). I > am not surprised because this table is a really big one (now, it has > over 200M records). Okay. You may want to consider lowering the statistics size for all the column in that table; that would reduce analyze time, at the cost of possibly worsening the plans for that table, depending on how irregular the distribution is. See ALTER TABLE / SET STATISTICS in the documentation, and the default_statistics_target parameter in postgresql.conf. > However, the most of my concerns is the behavior of analyze/vacuum. > You mentioned that the analyze-only operation cannot be optimized as > the same way on optimizing vacuum. Does that mean the analyze > operation on a table would unavoidably affect the vacuum proceeded on > another one? That's correct. I think you can run VACUUM ANALYZE, and it would do both things at once; AFAIK this is also optimized like VACUUM is, but I admit I'm not 100% sure (and I can't check right now). > If this is a normal reaction for an analyze operation, > maybe I should try to lower vacuum_cost_delay or use more powerful > hardware to minimize the interfered period. So, the pages for the > small table would not increase quickly. I think it would make sense to have as low a cost_delay as possible for this ANALYZE. (Note you can change it locally with a SET command; no need to touch postgresql.conf. So you can change it when you analyze just this large table). -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum blocks the operations of other manual vacuum
> Excerpts from kuopo's message of jue nov 18 04:10:36 -0300 2010: >> However, when I analyze the table A, the autovacuum or vacuum on the >> table B cannot find any removable row version (the number of >> nonremoveable row versions and pages keeps increasing). After the >> analysis finishes, the search operations on the table B is still >> inefficient. If I call full vacuum right now, then I can have quick >> response time of the search operations on the table B again. Hi, I don't know how to fix the long VACUUM/ANALYZE, but have you tried to minimize the growth using HOT? HOT means that if you update only columns that are not indexed, and if the update can fit into the same page (into an update chain), this would not create a dead row. Are there any indexes on the small table? How large is it? You've mentioned there are about 2049 rows - that might be just a few pages so the indexes would not be very efficient anyway. Try to remove the indexes, and maybe create the table with a smaller fillfactor (so that there is more space for the updates). That should be much more efficient and the table should not grow. You can see if HOT works through pg_stat_all_tables view (columns n_tup_upd and n_tup_hot_upd). regards Tomas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum blocks the operations of other manual vacuum
Excerpts from kuopo's message of jue nov 18 04:10:36 -0300 2010: > Hi, > > Thanks for your response. I've checked it again and found that the > main cause is the execution of ANALYZE. As I have mentioned, I have > two tables: table A is a big one (around 10M~100M records) for log > data and table B is a small one (around 1k records) for keeping some > current status. There are a lot of update operations and some search > operations on the table B. For the performance issue, I would like to > keep table B as compact as possible. According your suggestion, I try > to invoke standard vacuum (not full) more frequently (e.g., once per > min). > > However, when I analyze the table A, the autovacuum or vacuum on the > table B cannot find any removable row version (the number of > nonremoveable row versions and pages keeps increasing). After the > analysis finishes, the search operations on the table B is still > inefficient. If I call full vacuum right now, then I can have quick > response time of the search operations on the table B again. Hmm, I don't think we can optimize the analyze-only operation the same way we optimize vacuum (i.e. allow vacuum to proceed while it's in progress). Normally analyze shouldn't take all that long anyway -- why is it that slow? Are you calling it in a transaction that also does other stuff? Are you analyzing more than one table in a single transaction, perhaps even the whole database? Perhaps you could speed it up by lowering vacuum_cost_delay, if it's set to a nonzero value. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum blocks the operations of other manual vacuum
Hi, Thanks for your response. I've checked it again and found that the main cause is the execution of ANALYZE. As I have mentioned, I have two tables: table A is a big one (around 10M~100M records) for log data and table B is a small one (around 1k records) for keeping some current status. There are a lot of update operations and some search operations on the table B. For the performance issue, I would like to keep table B as compact as possible. According your suggestion, I try to invoke standard vacuum (not full) more frequently (e.g., once per min). However, when I analyze the table A, the autovacuum or vacuum on the table B cannot find any removable row version (the number of nonremoveable row versions and pages keeps increasing). After the analysis finishes, the search operations on the table B is still inefficient. If I call full vacuum right now, then I can have quick response time of the search operations on the table B again. Any suggestions for this situation? On Tue, Nov 16, 2010 at 11:26 PM, Alvaro Herrera wrote: > Excerpts from kuopo's message of vie nov 12 05:01:24 -0300 2010: >> Hi, >> >> I have a question about the behavior of autovacuum. When I have a big >> table A which is being processed by autovacuum, I also manually use >> (full) vacuum to clean another table B. Then I found that I always got >> something like “found 0 removable, 14283 nonremovable row”. However, >> if I stop the autovacuum functionality and use vacuum on that big >> table A manually, I can clean table B (ex. found 22615 removable, 2049 >> nonremovable row). >> >> Is this correct? Why do vacuum and autovacuum have different actions? > > Vacuum full does not assume that it can clean up tuples while other > transactions are running, and that includes the (non full, or "lazy") > vacuum that autovacuum is running. Autovacuum only runs lazy vacuum; > and that one is aware that other concurrent vacuums can be ignored. > > Just don't use vacuum full unless strictly necessary. It has other > drawbacks. > > -- > Álvaro Herrera > The PostgreSQL Company - Command Prompt, Inc. > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum blocks the operations of other manual vacuum
Excerpts from kuopo's message of vie nov 12 05:01:24 -0300 2010: > Hi, > > I have a question about the behavior of autovacuum. When I have a big > table A which is being processed by autovacuum, I also manually use > (full) vacuum to clean another table B. Then I found that I always got > something like “found 0 removable, 14283 nonremovable row”. However, > if I stop the autovacuum functionality and use vacuum on that big > table A manually, I can clean table B (ex. found 22615 removable, 2049 > nonremovable row). > > Is this correct? Why do vacuum and autovacuum have different actions? Vacuum full does not assume that it can clean up tuples while other transactions are running, and that includes the (non full, or "lazy") vacuum that autovacuum is running. Autovacuum only runs lazy vacuum; and that one is aware that other concurrent vacuums can be ignored. Just don't use vacuum full unless strictly necessary. It has other drawbacks. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Autovacuum in postgres.
On Thu, May 27, 2010 at 9:01 AM, venu madhav wrote: > Thanks for the reply.. > I am using postgres 8.01 and since it runs on a client box, I > can't upgrade it. I've set the auto vacuum nap time to 3600 seconds. You've pretty much made autovac run every 5 hours with that setting. What was wrong with the original settings? Just wondering what problem you were / are trying to solve here. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Autovacuum in postgres.
Thanks for the reply.. I am using postgres 8.01 and since it runs on a client box, I can't upgrade it. I've set the auto vacuum nap time to 3600 seconds. On Thu, May 27, 2010 at 8:03 PM, Bruce Momjian wrote: > venu madhav wrote: > > Hi All, > >In my application we are using postgres which runs on an embedded > > box. I have configured autovacuum to run once for every one hour. It has > 5 > > different databases in it. When I saw the log messages, I found that it > is > > running autovacuum on one database every hour. As a result, on my > database > > autovacuum is run once in 5 hours. Is there any way to make it run it > every > > hour. > > What settings did you change to make it run every hour? Also, it will > only vacuum tables that need vacuuming. What version of Postgres are > you using? > > -- > Bruce Momjian http://momjian.us > EnterpriseDB http://enterprisedb.com >
Re: [PERFORM] Autovacuum in postgres.
Excerpts from venu madhav's message of vie may 21 05:38:43 -0400 2010: > Hi All, >In my application we are using postgres which runs on an embedded > box. I have configured autovacuum to run once for every one hour. It has 5 > different databases in it. When I saw the log messages, I found that it is > running autovacuum on one database every hour. As a result, on my database > autovacuum is run once in 5 hours. Is there any way to make it run it every > hour. If you set naptime to 12 mins, it will run on one database every 12 minutes, so once per hour for your database. This is not really the intended usage though. You will have to adjust the time if another database is created. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Autovacuum in postgres.
venu madhav wrote: > Thanks for the reply.. >I am using postgres 8.01 and since it runs on a client box, I > can't upgrade it. I've set the auto vacuum nap time to 3600 seconds. That is an older version of autovacuum that wasn't very capable. --- > On Thu, May 27, 2010 at 8:03 PM, Bruce Momjian wrote: > > > venu madhav wrote: > > > Hi All, > > >In my application we are using postgres which runs on an embedded > > > box. I have configured autovacuum to run once for every one hour. It has > > 5 > > > different databases in it. When I saw the log messages, I found that it > > is > > > running autovacuum on one database every hour. As a result, on my > > database > > > autovacuum is run once in 5 hours. Is there any way to make it run it > > every > > > hour. > > > > What settings did you change to make it run every hour? Also, it will > > only vacuum tables that need vacuuming. What version of Postgres are > > you using? > > > > -- > > Bruce Momjian http://momjian.us > > EnterpriseDB http://enterprisedb.com > > -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Autovacuum in postgres.
venu madhav wrote: > Hi All, >In my application we are using postgres which runs on an embedded > box. I have configured autovacuum to run once for every one hour. It has 5 > different databases in it. When I saw the log messages, I found that it is > running autovacuum on one database every hour. As a result, on my database > autovacuum is run once in 5 hours. Is there any way to make it run it every > hour. What settings did you change to make it run every hour? Also, it will only vacuum tables that need vacuuming. What version of Postgres are you using? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Autovacuum in postgres.
One more question " Is is expected ?" On Fri, May 21, 2010 at 3:08 PM, venu madhav wrote: > Hi All, >In my application we are using postgres which runs on an embedded > box. I have configured autovacuum to run once for every one hour. It has 5 > different databases in it. When I saw the log messages, I found that it is > running autovacuum on one database every hour. As a result, on my database > autovacuum is run once in 5 hours. Is there any way to make it run it every > hour. > > > Thank you, > Venu >
Re: [PERFORM] autovacuum strategy / parameters
On Sat, May 1, 2010 at 1:11 PM, Greg Smith wrote: > Robert Haas wrote: >> >> I don't have a stake in the ground on what the right settings are, but >> I think it's fair to say that if you vacuum OR analyze much less >> frequently than what we recommend my default, it might break. >> > > I think the default settings are essentially minimum recommended > frequencies. They aren't too terrible for the giant data warehouse case > Josh was suggesting they came from--waiting until there's 20% worth of dead > stuff before kicking off an intensive vacuum is OK when vacuum is expensive > and you're mostly running big queries anyway. And for smaller tables, the > threshold helps it kick in a little earlier. It's unlikely anyone wants to > *increase* those, so that autovacuum runs even less; out of the box it's not > tuned to run very often at all. > > If anything, I'd expect people to want to increase how often it runs, for > tables where much less than 20% dead is a problem. The most common > situation I've seen where that's the case is when you have a hotspot of > heavily updated rows in a large table, and this may match some of the > situations that Robert was alluding to seeing. Let's say you have a big > table where 0.5% of the users each update their respective records heavily, > averaging 30 times each. That's only going to result in 15% dead rows, so > no autovacuum. But latency for those users will suffer greatly, because > they might have to do lots of seeking around to get their little slice of > the data. For me it's more that my applications are typically really fast, and when they run at half-speed people think "oh, it's slow today" but they can still work and attribute the problem to their computer, or the network, or something. When they slow down by like 10x then they file a bug. I'm typically dealing with a situation where the whole database can be easily cached in RAM and the CPU is typically 90% idle, which cushions the blow quite a bit. A few months ago someone reported that "the portal was slow" and the problem turned out to be that the database was bloated by in excess of a factor a factor of 10 due to having blown out the free space map. I wasn't a regular user of that system at that time so hadn't had the opportunity to notice myself. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum strategy / parameters
On Sat, May 1, 2010 at 1:17 PM, Scott Marlowe wrote: > On Sat, May 1, 2010 at 1:08 PM, Robert Haas wrote: >> On Sat, May 1, 2010 at 12:13 PM, Scott Marlowe >> wrote: >>> On Fri, Apr 30, 2010 at 4:50 PM, Josh Berkus wrote: Which is the opposite of my experience; currently we have several clients who have issues which required more-frequent analyzes on specific tables. Before 8.4, vacuuming more frequently, especially on large tables, was very costly; vacuum takes a lot of I/O and CPU. Even with 8.4 it's not something you want to increase without thinking about the tradeoff >>> >>> Actually I would think that statement would be be that before 8.3 >>> vacuum was much more expensive. The changes to vacuum for 8.4 mostly >>> had to do with moving FSM to disk, making seldom vacuumed tables >>> easier to keep track of, and making autovac work better in the >>> presence of long running transactions. The ability to tune IO load >>> etc was basically unchanged in 8.4. >> >> What about http://www.postgresql.org/docs/8.4/static/storage-vm.html ? > > That really only has an effect no tables that aren't updated very > often. Unless you've got a whole bunch of those, it's not that big of > a deal. sigh, s/ no / on / Anyway, my real point was that the big improvements that made vacuum so much better came in 8.3, with HOT updates and multi-threaded vacuum (that might have shown up in 8.2 even) 8.3 was a huge improvement and compelling upgrade from 8.1 for me. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum strategy / parameters
On Sat, May 1, 2010 at 1:08 PM, Robert Haas wrote: > On Sat, May 1, 2010 at 12:13 PM, Scott Marlowe > wrote: >> On Fri, Apr 30, 2010 at 4:50 PM, Josh Berkus wrote: >>> Which is the opposite of my experience; currently we have several >>> clients who have issues which required more-frequent analyzes on >>> specific tables. Before 8.4, vacuuming more frequently, especially on >>> large tables, was very costly; vacuum takes a lot of I/O and CPU. Even >>> with 8.4 it's not something you want to increase without thinking about >>> the tradeoff >> >> Actually I would think that statement would be be that before 8.3 >> vacuum was much more expensive. The changes to vacuum for 8.4 mostly >> had to do with moving FSM to disk, making seldom vacuumed tables >> easier to keep track of, and making autovac work better in the >> presence of long running transactions. The ability to tune IO load >> etc was basically unchanged in 8.4. > > What about http://www.postgresql.org/docs/8.4/static/storage-vm.html ? That really only has an effect no tables that aren't updated very often. Unless you've got a whole bunch of those, it's not that big of a deal. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum strategy / parameters
On Sat, May 1, 2010 at 12:13 PM, Scott Marlowe wrote: > On Fri, Apr 30, 2010 at 4:50 PM, Josh Berkus wrote: >> Which is the opposite of my experience; currently we have several >> clients who have issues which required more-frequent analyzes on >> specific tables. Before 8.4, vacuuming more frequently, especially on >> large tables, was very costly; vacuum takes a lot of I/O and CPU. Even >> with 8.4 it's not something you want to increase without thinking about >> the tradeoff > > Actually I would think that statement would be be that before 8.3 > vacuum was much more expensive. The changes to vacuum for 8.4 mostly > had to do with moving FSM to disk, making seldom vacuumed tables > easier to keep track of, and making autovac work better in the > presence of long running transactions. The ability to tune IO load > etc was basically unchanged in 8.4. What about http://www.postgresql.org/docs/8.4/static/storage-vm.html ? ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum strategy / parameters
Greg Smith writes: > If anything, I'd expect people to want to increase how often it runs, > for tables where much less than 20% dead is a problem. The most common > situation I've seen where that's the case is when you have a hotspot of > heavily updated rows in a large table, and this may match some of the > situations that Robert was alluding to seeing. Let's say you have a big > table where 0.5% of the users each update their respective records > heavily, averaging 30 times each. That's only going to result in 15% > dead rows, so no autovacuum. But latency for those users will suffer > greatly, because they might have to do lots of seeking around to get > their little slice of the data. With a little luck, HOT will alleviate that case, since HOT updates can be reclaimed without running vacuum per se. I agree there's a risk there though. Now that partial vacuum is available, it'd be a real good thing to revisit these numbers. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum strategy / parameters
Robert Haas wrote: I don't have a stake in the ground on what the right settings are, but I think it's fair to say that if you vacuum OR analyze much less frequently than what we recommend my default, it might break. I think the default settings are essentially minimum recommended frequencies. They aren't too terrible for the giant data warehouse case Josh was suggesting they came from--waiting until there's 20% worth of dead stuff before kicking off an intensive vacuum is OK when vacuum is expensive and you're mostly running big queries anyway. And for smaller tables, the threshold helps it kick in a little earlier. It's unlikely anyone wants to *increase* those, so that autovacuum runs even less; out of the box it's not tuned to run very often at all. If anything, I'd expect people to want to increase how often it runs, for tables where much less than 20% dead is a problem. The most common situation I've seen where that's the case is when you have a hotspot of heavily updated rows in a large table, and this may match some of the situations that Robert was alluding to seeing. Let's say you have a big table where 0.5% of the users each update their respective records heavily, averaging 30 times each. That's only going to result in 15% dead rows, so no autovacuum. But latency for those users will suffer greatly, because they might have to do lots of seeking around to get their little slice of the data. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum strategy / parameters
On Fri, Apr 30, 2010 at 4:50 PM, Josh Berkus wrote: > Which is the opposite of my experience; currently we have several > clients who have issues which required more-frequent analyzes on > specific tables. Before 8.4, vacuuming more frequently, especially on > large tables, was very costly; vacuum takes a lot of I/O and CPU. Even > with 8.4 it's not something you want to increase without thinking about > the tradeoff Actually I would think that statement would be be that before 8.3 vacuum was much more expensive. The changes to vacuum for 8.4 mostly had to do with moving FSM to disk, making seldom vacuumed tables easier to keep track of, and making autovac work better in the presence of long running transactions. The ability to tune IO load etc was basically unchanged in 8.4. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum strategy / parameters
On Wed, Apr 28, 2010 at 8:20 AM, Thomas Kellerer wrote: > Rick, 22.04.2010 22:42: >> >> So, in a large table, the scale_factor is the dominant term. In a >> small table, the threshold is the dominant term. But both are taken into >> account. >> >> The default values are set for small tables; it is not being run for >> large tables. > > With 8.4 you can adjust the autovacuum settings per table... You can as well with 8.3, but it's not made by alter table but by pg_autovacuum table entries. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum strategy / parameters
On Fri, Apr 30, 2010 at 6:50 PM, Josh Berkus wrote: > Which is the opposite of my experience; currently we have several > clients who have issues which required more-frequent analyzes on > specific tables. That's all fine, but probably not too relevant to the original complaint - the OP backed off the default settings by several orders of magnitude, which might very well cause a problem with both VACUUM and ANALYZE. I don't have a stake in the ground on what the right settings are, but I think it's fair to say that if you vacuum OR analyze much less frequently than what we recommend my default, it might break. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum strategy / parameters
Josh Berkus escribió: > #autovacuum_vacuum_scale_factor = 0.2 > > This is set because in my experience, 20% bloat is about the level at > which bloat starts affecting performance; thus, we want to vacuum at > that level but not sooner. This does mean that very large tables which > never have more than 10% updates/deletes don't get vacuumed at all until > freeze_age; this is a *good thing*. VACUUM on large tables is expensive; > you don't *want* to vacuum a billion-row table which has only 100,000 > updates. Hmm, now that we have partial vacuum, perhaps we should revisit this. > It would be worth doing a DBT2/DBT5 test run with different autovac > settings post-8.4 so see if we should specifically change the vacuum > threshold. Right. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum strategy / parameters
> My guess is that the reason we run ANALYZE more frequently than vacuum > (with the default settings) is that ANALYZE is pretty cheap. In many > cases, if the statistical distribution of the data hasn't changed > much, then it's not really necessary, but it doesn't cost much either. > And for certain types of usage patterns, like time series (where the > maximum value keeps increasing) it's REALLY important to analyze > frequently. > > But having said that, on the systems I've worked with, I've only > rarely seen a problem caused by not analyzing frequently enough. On > the other hand, I've seen MANY problems caused by not vacuuming > enough. Which is the opposite of my experience; currently we have several clients who have issues which required more-frequent analyzes on specific tables. Before 8.4, vacuuming more frequently, especially on large tables, was very costly; vacuum takes a lot of I/O and CPU. Even with 8.4 it's not something you want to increase without thinking about the tradeoffs. Since I'm responsible for the current defaults, I though I'd explain the reasoning behind them. I developed and tested them while at Greenplum, so they are *not* designed for small databases. #autovacuum_vacuum_threshold = 50 #autovacuum_analyze_threshold = 50 These two are set to the minimum threshold to avoid having small tables get vacuum/analyzed continuously, but to make sure that small tables do get vacuumed & analyzed sometimes. #autovacuum_vacuum_scale_factor = 0.2 This is set because in my experience, 20% bloat is about the level at which bloat starts affecting performance; thus, we want to vacuum at that level but not sooner. This does mean that very large tables which never have more than 10% updates/deletes don't get vacuumed at all until freeze_age; this is a *good thing*. VACUUM on large tables is expensive; you don't *want* to vacuum a billion-row table which has only 100,000 updates. #autovacuum_analyze_scale_factor = 0.1 The 10% threshold for analyze is there because (a) analyze is cheap, and (b) 10% changes to a table can result in very bad plans if the changes are highly skewed towards a specific range, such as additions onto the end of a time-based table. The current postgres defaults were tested on DBT2 as well as pgbench, and in my last 2 years of consulting I've seldom found reason to touch them except on *specific* tables. So I still feel that they are good defaults. It would be worth doing a DBT2/DBT5 test run with different autovac settings post-8.4 so see if we should specifically change the vacuum threshold. Pending that, though, I think the current defaults are good enough. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum strategy / parameters
akp geek, 28.04.2010 16:37: We have 8.4, which of AUTOVACUUM PARAMETERS can be set to handle individual table? All documented here: http://www.postgresql.org/docs/current/static/sql-createtable.html -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum strategy / parameters
Check out the manual: http://www.postgresql.org/docs/8.4/static/routine-vacuuming.html#AUTOVACUUM Cheers, Ken On Wed, Apr 28, 2010 at 10:37:35AM -0400, akp geek wrote: > Hi - >don't want to side track the discussion. We have 8.4, which of > AUTOVACUUM PARAMETERS can be set to handle individual table? I ran into > bloat with small table only. Now the issue is being resolved. > > Regards > On Wed, Apr 28, 2010 at 10:20 AM, Thomas Kellerer wrote: > > > Rick, 22.04.2010 22:42: > > > > > >> So, in a large table, the scale_factor is the dominant term. In a > >> small table, the threshold is the dominant term. But both are taken into > >> account. > >> > >> The default values are set for small tables; it is not being run for > >> large tables. > >> > > > > With 8.4 you can adjust the autovacuum settings per table... > > > > > > > > > > > > -- > > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-performance > > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum strategy / parameters
Hi - don't want to side track the discussion. We have 8.4, which of AUTOVACUUM PARAMETERS can be set to handle individual table? I ran into bloat with small table only. Now the issue is being resolved. Regards On Wed, Apr 28, 2010 at 10:20 AM, Thomas Kellerer wrote: > Rick, 22.04.2010 22:42: > > >> So, in a large table, the scale_factor is the dominant term. In a >> small table, the threshold is the dominant term. But both are taken into >> account. >> >> The default values are set for small tables; it is not being run for >> large tables. >> > > With 8.4 you can adjust the autovacuum settings per table... > > > > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >
Re: [PERFORM] autovacuum strategy / parameters
Rick, 22.04.2010 22:42: So, in a large table, the scale_factor is the dominant term. In a small table, the threshold is the dominant term. But both are taken into account. The default values are set for small tables; it is not being run for large tables. With 8.4 you can adjust the autovacuum settings per table... -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum strategy / parameters
Robert Haas wrote: > Rick wrote: >> Since vacuum just recovers space, that doesn't seem to be nearly >> as critical for performance? > > That doesn't really match my experience. Without regular > vacuuming, tables and indices end up being larger than they ought > to be and contain large amounts of dead space that slows things > down. How much of an impact that ends up having depends on how > badly bloated they are and what you're trying to do, but it can > get very ugly. That has been my experience, too. When we first started using PostgreSQL, we noticed a performance hit when some small tables which were updated very frequently were vacuumed. Our knee-jerk reaction was to tune autovacuum to be less aggressive, so that we didn't get hit with the pain as often. Of course, things just got worse, because every access to that table, when vacuum hadn't been run recently, had to examine all versions of the desired row, and test visibility for each version, to find the current one. So performance fell off even worse. So we went to much more aggressive settings for autovacuum (although only slightly more aggressive than what has since become the default) and the problems disappeared. Basically, as long as small tables are not allowed to bloat, vacuuming them is so fast that you never notice it. > 8.3 and higher are better about this because of an optimization > called HOT, but there can still be problems. Agreed on both counts. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum strategy / parameters
On Thu, Apr 22, 2010 at 4:42 PM, Rick wrote: > On Apr 22, 2:55 pm, robertmh...@gmail.com (Robert Haas) wrote: >> On Wed, Apr 21, 2010 at 11:06 AM, Rick wrote: >> > I have a DB with small and large tables that can go up to 15G. >> > For performance benefits, it appears that analyze has much less cost >> > than vacuum, but the same benefits? >> >> Err, no. ANALYZE gathers statistics for the query planner; VACUUM >> clears out old, dead tuples so that space can be reused by the >> database system. >> >> > I can’t find any clear recommendations for frequencies and am >> > considering these parameters: >> >> > Autovacuum_vacuum_threshold = 5 >> > Autovacuum_analyze_threshold = 1 >> > Autovacuum_vacuum_scale_factor = 0.01 >> > Autovacuum_analyze_scale_factor = 0.005 >> >> > This appears it will result in table analyzes occurring around 10,000 >> > to 85,000 dead tuples and vacuum occuring around 50,000 to 200,000, >> > depending on the table sizes. >> >> > Can anyone comment on whether this is the right strategy and targets >> > to use? >> >> I'm not that familiar with tuning these parameters but increasing the >> default thesholds by a thousand-fold doesn't seem like a good idea. >> Small tables will never get vacuumed or analyzed at all. >> >> ...Robert >> >> -- >> Sent via pgsql-performance mailing list (pgsql-performa...@postgresql.org) >> To make changes to your >> subscription:http://www.postgresql.org/mailpref/pgsql-performance > > The problem is with the autovacuum formula: > > In a loop, autovacuum checks to see if number of dead tuples > > ((number of live tuples * autovacuum_vacuum_scale_factor) + > autovacuum_vacuum_threshold), and if > so, it runs VACUUM. If not, it sleeps. It works the same way for > ANALYZE. > > So, in a large table, the scale_factor is the dominant term. In a > small > table, the threshold is the dominant term. But both are taken into > account. > > The default values are set for small tables; it is not being run for > large tables. > The question boils down to exactly what is the max number of dead > tuples that should be allowed to accumulate before running analyze? > Since vacuum just recovers space, that doesn't seem to be nearly as > critical for performance? That doesn't really match my experience. Without regular vacuuming, tables and indices end up being larger than they ought to be and contain large amounts of dead space that slows things down. How much of an impact that ends up having depends on how badly bloated they are and what you're trying to do, but it can get very ugly. My guess is that the reason we run ANALYZE more frequently than vacuum (with the default settings) is that ANALYZE is pretty cheap. In many cases, if the statistical distribution of the data hasn't changed much, then it's not really necessary, but it doesn't cost much either. And for certain types of usage patterns, like time series (where the maximum value keeps increasing) it's REALLY important to analyze frequently. But having said that, on the systems I've worked with, I've only rarely seen a problem caused by not analyzing frequently enough. On the other hand, I've seen MANY problems caused by not vacuuming enough. Someone runs a couple of big queries that rewrite a large portion of a table several times over and, boom, problems. 8.3 and higher are better about this because of an optimization called HOT, but there can still be problems. Other people's experiences may not match mine, but the bottom line is that you need to do both of these things, and you need to make sure they happen regularly. In most cases, the CPU and I/O time they consume will be amply repaid in improved query performance. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum strategy / parameters
Rick wrote: > So, in a large table, the scale_factor is the dominant term. In a > small > table, the threshold is the dominant term. But both are taken into > account. Correct. > The default values are set for small tables; it is not being run for > large tables. So decrease the scale factor and leave threshold alone. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum strategy / parameters
On Apr 22, 2:55 pm, robertmh...@gmail.com (Robert Haas) wrote: > On Wed, Apr 21, 2010 at 11:06 AM, Rick wrote: > > I have a DB with small and large tables that can go up to 15G. > > For performance benefits, it appears that analyze has much less cost > > than vacuum, but the same benefits? > > Err, no. ANALYZE gathers statistics for the query planner; VACUUM > clears out old, dead tuples so that space can be reused by the > database system. > > > I can’t find any clear recommendations for frequencies and am > > considering these parameters: > > > Autovacuum_vacuum_threshold = 5 > > Autovacuum_analyze_threshold = 1 > > Autovacuum_vacuum_scale_factor = 0.01 > > Autovacuum_analyze_scale_factor = 0.005 > > > This appears it will result in table analyzes occurring around 10,000 > > to 85,000 dead tuples and vacuum occuring around 50,000 to 200,000, > > depending on the table sizes. > > > Can anyone comment on whether this is the right strategy and targets > > to use? > > I'm not that familiar with tuning these parameters but increasing the > default thesholds by a thousand-fold doesn't seem like a good idea. > Small tables will never get vacuumed or analyzed at all. > > ...Robert > > -- > Sent via pgsql-performance mailing list (pgsql-performa...@postgresql.org) > To make changes to your > subscription:http://www.postgresql.org/mailpref/pgsql-performance The problem is with the autovacuum formula: In a loop, autovacuum checks to see if number of dead tuples > ((number of live tuples * autovacuum_vacuum_scale_factor) + autovacuum_vacuum_threshold), and if so, it runs VACUUM. If not, it sleeps. It works the same way for ANALYZE. So, in a large table, the scale_factor is the dominant term. In a small table, the threshold is the dominant term. But both are taken into account. The default values are set for small tables; it is not being run for large tables. The question boils down to exactly what is the max number of dead tuples that should be allowed to accumulate before running analyze? Since vacuum just recovers space, that doesn't seem to be nearly as critical for performance? -Rick -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum strategy / parameters
On Wed, Apr 21, 2010 at 11:06 AM, Rick wrote: > I have a DB with small and large tables that can go up to 15G. > For performance benefits, it appears that analyze has much less cost > than vacuum, but the same benefits? Err, no. ANALYZE gathers statistics for the query planner; VACUUM clears out old, dead tuples so that space can be reused by the database system. > I can’t find any clear recommendations for frequencies and am > considering these parameters: > > Autovacuum_vacuum_threshold = 5 > Autovacuum_analyze_threshold = 1 > Autovacuum_vacuum_scale_factor = 0.01 > Autovacuum_analyze_scale_factor = 0.005 > > This appears it will result in table analyzes occurring around 10,000 > to 85,000 dead tuples and vacuum occuring around 50,000 to 200,000, > depending on the table sizes. > > Can anyone comment on whether this is the right strategy and targets > to use? I'm not that familiar with tuning these parameters but increasing the default thesholds by a thousand-fold doesn't seem like a good idea. Small tables will never get vacuumed or analyzed at all. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Autovacuum Tuning advice
Sorry, this is a “black box” application, I am bound by what they give me as far as table layout, but I fully understand the rationale. I believe this application spent its beginnings with Oracle, which explains the blanket use of VARCHAR. From: Grzegorz Jaśkiewicz [mailto:gryz...@gmail.com] Sent: Monday, March 01, 2010 6:51 AM To: Plugge, Joe R. Cc: Scott Marlowe; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Autovacuum Tuning advice storing all fields as varchar surely doesn't make: - indicies small, - the thing fly, - tables small. ...
Re: [PERFORM] Autovacuum Tuning advice
storing all fields as varchar surely doesn't make: - indicies small, - the thing fly, - tables small. ...
Re: [PERFORM] Autovacuum Tuning advice
Sorry, additional info: OS is Red Hat Enterprise Linux ES release 4 (Nahant Update 5) DISK - IBM DS4700 Array - 31 drives and 1 hot spare - RAID10 - 32MB stripe Sysctl.conf kernel.shmmax=6442450944 kernel.shmall=1887436 kernel.msgmni=1024 kernel.msgmnb=65536 kernel.msgmax=65536 kernel.sem=250 256000 32 1024 Problem Child table: This table is partitioned so that after the data has rolled past 30 days, I can just drop the table. Table "public.log_events_y2010m02" Column | Type | Modifiers ---++--- callseq | character varying(32) | not null eventid | character varying(40) | not null msgseq| character varying(32) | not null eventdate | timestamp(0) without time zone | not null hollyid | character varying(20) | ownerid | character varying(60) | spownerid | character varying(60) | applicationid | character varying(60) | clid | character varying(40) | dnis | character varying(40) | param | character varying(2000)| docid | character varying(40) | Indexes: "log_events_y2010m02_pk" PRIMARY KEY, btree (callseq, msgseq) "loev_eventid_idx_y2010m02" btree (eventid) "loev_ownerid_cidx_y2010m02" btree (ownerid, spownerid) Check constraints: "log_events_y2010m02_eventdate_check" CHECK (eventdate >= '2010-02-01'::date AND eventdate < '2010-03-01'::date) Inherits: log_events Parent Table: Table "public.log_events" Column | Type | Modifiers ---++--- callseq | character varying(32) | not null eventid | character varying(40) | not null msgseq| character varying(32) | not null eventdate | timestamp(0) without time zone | not null hollyid | character varying(20) | ownerid | character varying(60) | spownerid | character varying(60) | applicationid | character varying(60) | clid | character varying(40) | dnis | character varying(40) | param | character varying(2000)| docid | character varying(40) | Triggers: insert_log_events_trigger BEFORE INSERT ON log_events FOR EACH ROW EXECUTE PROCEDURE insert_log_events() schemaname | tablename| size_pretty | total_size_pretty ++-+--- public | log_events_y2010m02| 356 GB | 610 GB -Original Message- From: Scott Marlowe [mailto:scott.marl...@gmail.com] Sent: Monday, March 01, 2010 12:58 AM To: Plugge, Joe R. Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Autovacuum Tuning advice On Sun, Feb 28, 2010 at 8:09 PM, Plugge, Joe R. wrote: > I have a very busy system that takes about 9 million inserts per day and each > record gets updated at least once after the insert (all for the one same > table), there are other tables that get hit but not as severely. As > suspected I am having a problem with table bloat. Any advice on how to be > more aggressive with autovacuum? I am using 8.4.1. My machine has 4 Intel > Xeon 3000 MHz Processors with 8 GB of Ram. What kind of drive system do you have? That's far more important than CPU and RAM. Let's look at a two pronged attack. 1: What can you maybe do to reduce the number of updates for each row. if you do something like: update row set field1='xyz' where id=4; update row set field2='www' where id=4; And you can combine those updates, that's a big savings. Can you benefit from HOT updates by removing some indexes? Updating indexed fields can cost a fair bit more than updating indexed ones IF you have a < 100% fill factor and therefore free room in each page for a few extra rows. 2: Vacuum tuning. > > Currently I am using only defaults for autovac. This one: > #autovacuum_vacuum_cost_delay = 20ms is very high for a busy system with a powerful io subsystem. I run my production servers with 1ms to 4ms so they can keep up. Lastly there are some settings you can make per table for autovac you can look into (i.e. set cost_delay to 0 for this table), or you can turn off autovac for this one table and then run a regular vac with no cost_delay on it every minute or two. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Autovacuum Tuning advice
Joe wrote: > I have a very busy system that takes about 9 million inserts per day and each > record gets > updated at least once after the insert (all for the one same table), there > are other tables that > get hit but not as severely. As suspected I am having a problem with table > bloat. Any advice > on how to be more aggressive with autovacuum? I am using 8.4.1. My machine > has 4 Intel > Xeon 3000 MHz Processors with 8 GB of Ram. > > Currently I am using only defaults for autovac. > > shared_buffers = 768MB # min 128kB > work_mem = 1MB # min 64kB > maintenance_work_mem = 384MB Operating system ? Any messages in logs ? Greg W. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Autovacuum Tuning advice
On Sun, Feb 28, 2010 at 8:09 PM, Plugge, Joe R. wrote: > I have a very busy system that takes about 9 million inserts per day and each > record gets updated at least once after the insert (all for the one same > table), there are other tables that get hit but not as severely. As > suspected I am having a problem with table bloat. Any advice on how to be > more aggressive with autovacuum? I am using 8.4.1. My machine has 4 Intel > Xeon 3000 MHz Processors with 8 GB of Ram. What kind of drive system do you have? That's far more important than CPU and RAM. Let's look at a two pronged attack. 1: What can you maybe do to reduce the number of updates for each row. if you do something like: update row set field1='xyz' where id=4; update row set field2='www' where id=4; And you can combine those updates, that's a big savings. Can you benefit from HOT updates by removing some indexes? Updating indexed fields can cost a fair bit more than updating indexed ones IF you have a < 100% fill factor and therefore free room in each page for a few extra rows. 2: Vacuum tuning. > > Currently I am using only defaults for autovac. This one: > #autovacuum_vacuum_cost_delay = 20ms is very high for a busy system with a powerful io subsystem. I run my production servers with 1ms to 4ms so they can keep up. Lastly there are some settings you can make per table for autovac you can look into (i.e. set cost_delay to 0 for this table), or you can turn off autovac for this one table and then run a regular vac with no cost_delay on it every minute or two. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum 'stuck' ?
On Wed, Jul 29, 2009 at 12:47 PM, Doug Hunley wrote:
> When reviewing the vacuum logs, I notice that on any given day
> autovacuum only seems to touch four of the tables in one of our
> schemas (not counting toast tables). However, if I look at the
> pgstatspack output for the same day, I see that there are plenty of
> other tables receiving a high number of inserts and deletes. How can I
> tell if autovacuum is accurately choosing the tables that need its
> attention (these four tables apparently) or if autovacuum is simply
> never making it to the other tables cause its too busy with these
> tables (my suspicion)? This is on 8.3.7 with the following settings in
> postgresql.conf:
> autovacuum = on
> log_autovacuum_min_duration = 0
> autovacuum_vacuum_threshold = 250
> autovacuum_analyze_threshold = 125
> autovacuum_vacuum_scale_factor = 0.2
> autovacuum_analyze_scale_factor = 0.1
> autovacuum_naptime = 5min
>
> Any/all other information can be provided as needed. TIA, again.
Disclaimer: I am not an expert on autovacuum.
If most of the activity on your other tables is UPDATEs, and given
that you are running 8.3, it is possible that they are all HOT
updates, and vacuuming isn't much needed. In terms of figuring out
what is going on with those tables, perhaps you could try any or all
of the following:
1. Lower your autovacuum_naptime (say, to the default value instead of
five times that amount) and see if it vacuums more stuff. On a
related note, does autovacuum do stuff every time it wakes up? Or
just now and then? If the latter, it's probably fine.
2. Fire off a manual VACUUM VERBOSE on one of the other tables you
think might need attention and examine (or post) the output.
3. Get Greg Sabino Mullane's check_postgres.pl script and use it to
look for bloat. Or, low tech way that I have used, compare:
SELECT COALESCE(SUM(pg_column_size(x)), 0) AS size FROM your_table_name x
vs.
SELECT pg_relation_size('your_table_name'::regclass)
(There's probably an easy way to do better than this; maybe someone
will enlighten me?)
Also, keep in mind that vacuuming is a little like dieting. No one
particularly likes it, and there's no value (and possibly some harm)
in doing more of it than you need. If you're not getting fat (i.e.
your queries aren't running slowly) then it's probably not worth
worrying about too much.
...Robert
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum hung?
Tom Lane [...@sss.pgh.pa.us] wrote:
Oh, and don't forget the more-complete pg_locks state. We'll want all
the columns of pg_locks, not just the ones you showed before.
auto vacuum of ts_user_sessions_map has been running for > 17 hours.
This table has 2,204,488 rows. I hope that I've captured enough info.
Thanks,
Brian
cemdb=# select procpid,current_query,query_start from pg_stat_activity;
procpid | current_query
| query_start
-+-+---
8817 |
| 2009-07-09 16:48:12.656419-07
8818 | autovacuum: VACUUM public.ts_user_sessions_map
| 2009-07-09 16:48:18.873677-07
cemdb=# select
l.pid,c.relname,l.mode,l.granted,l.virtualxid,l.virtualtransaction from
pg_locks l left outer join pg_class c on c.oid=l.relation order by l.pid;
pid | relname | mode
| granted | virtualxid | virtualtransaction
---++--+-++
8818 | ts_user_sessions_map_interimsessionidindex | RowExclusiveLock
| t || 2/3
8818 | ts_user_sessions_map_appindex | RowExclusiveLock
| t || 2/3
8818 | ts_user_sessions_map_sessionidindex| RowExclusiveLock
| t || 2/3
8818 | ts_user_sessions_map |
ShareUpdateExclusiveLock | t || 2/3
8818 || ExclusiveLock
| t | 2/3| 2/3
8818 | ts_user_sessions_map_pkey | RowExclusiveLock
| t || 2/3
13706 || ExclusiveLock
| t | 6/50 | 6/50
13706 | pg_class_oid_index | AccessShareLock
| t || 6/50
13706 | pg_class_relname_nsp_index | AccessShareLock
| t || 6/50
13706 | pg_locks | AccessShareLock
| t || 6/50
13706 | pg_class | AccessShareLock
| t || 6/50
(11 rows)
[r...@rdl64xeoserv01 log]# strace -p 8818 -o /tmp/strace.log
Process 8818 attached - interrupt to quit
Process 8818 detached
[r...@rdl64xeoserv01 log]# more /tmp/strace.log
select(0, NULL, NULL, NULL, {0, 13000}) = 0 (Timeout)
read(36, "`\0\0\0\370\354\250u\1\0\0\0\34\0\264\37\360\37\4 \0\0"...,
8192) = 8192
read(36, "`\0\0\0\340\f\251u\1\0\0\0\34\0\264\37\360\37\4 \0\0\0"...,
8192) = 8192
read(36, "`\0\0\0\300,\251u\1\0\0\0\34\0\264\37\360\37\4 \0\0\0\0"...,
8192) = 8192
read(36, "`\0\0\0(L\251u\1\0\0\0\34\0\264\37\360\37\4 \0\0\0\0\264"...,
8192) = 8192
read(36, "`\0\0\0|M\251u\1\0\0\0\34\0\264\37\360\37\4 \0\0\0\0\264"...,
8192) = 8192
read(36, "`\0\0\0\\~\251u\1\0\0\0\34\0\264\37\360\37\4 \0\0\0\0\264"...,
8192) = 8192
read(36, "`\0\0\0D\234\251u\1\0\0\0\34\0\264\37\360\37\4 \0\0\0\0"...,
8192) = 8192
read(36, "`\0\0\0\34\255\251u\1\0\0\0\34\0\264\37\360\37\4 \0\0\0"...,
8192) = 8192
read(36, "`\0\0\0\4\315\251u\1\0\0\0\34\0\264\37\360\37\4 \0\0\0"...,
8192) = 8192
read(36, "`\0\0\0\234\2334x\1\0\0\0\34\0\264\37\360\37\4 \0\0\0\0"...,
8192) = 8192
read(36, "`\0\0\0\354\354\251u\1\0\0\0\34\0\264\37\360\37\4 \0\0"...,
8192) = 8192
read(36, "`\0\0\0\324\f\252u\1\0\0\0\34\0\264\37\360\37\4 \0\0\0"...,
8192) = 8192
read(36, "`\0\0\0\274,\252u\1\0\0\0\34\0\264\37\360\37\4 \0\0\0\0"...,
8192) = 8192
read(36, "`\0\0\0\244L\252u\1\0\0\0\34\0\264\37\360\37\4 \0\0\0\0"...,
8192) = 8192
read(36, "`\0\0\0008^\252u\1\0\0\0\34\0\264\37\360\37\4 \0\0\0\0"...,
8192) = 8192
read(36, "`\0\0\0,\233\252u\1\0\0\0\34\0\264\37\360\37\4 \0\0\0\0"...,
8192) = 8192
read(36, "`\0\0\0\370\330\252u\1\0\0\0\34\0\264\37\360\37\4 \0\0"...,
8192) = 8192
read(36, "`\0\0\\371\252u\1\0\0\0\34\0\270\37\360\37\4 \0\0\0"...,
8192) = 8192
read(36, "`\0\0\0\364\30\253u\1\0\0\0\34\0\264\37\360\37\4 \0\0\0"...,
8192) = 8192
read(36, "`\0\0\0\2448\253u\1\0\0\0\34\0\264\37\360\37\4 \0\0\0\0"...,
8192) = 8192
select(0, NULL, NULL, NULL, {0, 2}) = 0 (Timeout)
read(36, "`\0\0\0dX\253u\1\0\0\0\34\0\264\37\360\37\4 \0\0\0\0\264"...,
8192) = 8192
read(36, "`\0\0\0X\216\253u\1\0\0\0\34\0\264\37\360\37\4 \0\0\0\0"...,
8192) = 8192
read(36, "`\0\0\0\10\256\253u\1\0\0\0\34\0\264\37\360\37\4 \0\0\0"...,
8192) = 8192
read(36, "`\0\0\0\300\315\253u\1\0\0\0\34\0\264\37\360\37\4 \0\0"...,
8192) = 8192
read(36, "`\0\0\0\304\f\254u\1\0\0\0\34\0\264\37\360\37\4 \0\0\0"...,
8192) = 8192
read(36, "`\0\0\0\354=\254u\1\0\0\0\34\0\264\37\360\37\4 \0\0\0\0"...,
8192) = 8192
read(36, "`\0\0\0\254]\254u\1\0\0\0\34\0\264\37\360\37\4 \0\0\0\0"...,
8192) = 8192
read(36, "`\0\0\0d}\254u\1\0\0\0\34\0\270\37\360\37\4 \0\0\0\0\270"...,
81
Re: [PERFORM] autovacuum hung?
Brian Cox writes: > OK. You mentioned strace. It's got a lot of options; any in particular > that would be useful if this happens again? Oh, and don't forget the more-complete pg_locks state. We'll want all the columns of pg_locks, not just the ones you showed before. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum hung?
Brian Cox writes: > OK. You mentioned strace. It's got a lot of options; any in particular > that would be useful if this happens again? I'd just do "strace -p processID" and watch it for a little while. If it's not hung, you'll see the process issuing kernel calls at some rate or other. If it is hung, you'll most likely see something like semop(...) and it just sits there. Also, if you see nothing but a series of select()s with varying timeouts, that would suggest a stuck spinlock (although I doubt that was happening, as it would eventually timeout and report a failure). regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum hung?
Tom Lane [...@sss.pgh.pa.us] wrote: No, no, and no. What would be best is to find out what actually happened. The evidence is gone now, but if you see it again please take a closer look. OK. You mentioned strace. It's got a lot of options; any in particular that would be useful if this happens again? Brian -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum hung?
Brian Cox writes: > Dp you think it would be better to manually > vacuum these tables? If so, would it be best to disable autovacuum of > them? And while I'm at it, if you disable autovacuum of the master table > will that disable it for the actual partitions? No, no, and no. What would be best is to find out what actually happened. The evidence is gone now, but if you see it again please take a closer look. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum hung?
Tom Lane [...@sss.pgh.pa.us] wrote: They might have been blocked behind some other process that was sitting in an open transaction for some reason. The other likely cause is badly chosen autovacuum delay, but I think that was already covered. Well, after I noticed this running for a while, I shutdown the postgres port and restarted postgres. The autovacuum of these tables kicked in promptly when postgres was back up. I then let them run. So, I don't think that surmise #1 is likely. As for #2, I'm using the default. These tables get updated once a day with each row (potentially) being updated 1-24 times over many minutes to a handful of hours. Dp you think it would be better to manually vacuum these tables? If so, would it be best to disable autovacuum of them? And while I'm at it, if you disable autovacuum of the master table will that disable it for the actual partitions? > Don't assume every row in pg_locks has a join partner in pg_class. You could use an outer join ... Yes, of course. It never occurred that there could be db locks not associated with tables. Thanks, Brian -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum hung?
Brian Cox writes: > Tom Lane [...@sss.pgh.pa.us] wrote: >> Are those processes actually doing anything, or just waiting? strace >> or local equivalent would be the most conclusive check. > These must not have been hung, because they finally completed (after > 10-15 hrs - some time between 11pm and 8am). Question is why does it > take so long to do this on such a relatively small table? They might have been blocked behind some other process that was sitting in an open transaction for some reason. The other likely cause is badly chosen autovacuum delay, but I think that was already covered. >> This query isn't very helpful because it fails to show locks that are >> not directly associated with tables. > How can that (locks not directly associated...) be determined? Don't assume every row in pg_locks has a join partner in pg_class. You could use an outer join ... regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum hung?
Tom Lane [...@sss.pgh.pa.us] wrote: Are those processes actually doing anything, or just waiting? strace or local equivalent would be the most conclusive check. These must not have been hung, because they finally completed (after 10-15 hrs - some time between 11pm and 8am). Question is why does it take so long to do this on such a relatively small table? This query isn't very helpful because it fails to show locks that are not directly associated with tables. How can that (locks not directly associated...) be determined? Thanks, Brian -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum hung?
Brian Cox writes: > autovacuum has been running on 2 tables for > 5 hours. There tables are > not huge (see below). For the past ~1 hour, I've shut off all other > activity on this database. The other table being vacuumed has more rows > (1897810). Anyone have any ideas about why this is taking so long? Are those processes actually doing anything, or just waiting? strace or local equivalent would be the most conclusive check. > cemdb=# select c.oid,c.relname,l.pid,l.mode,l.granted from pg_class c > join pg_locks l on c.oid=l.relation order by l.pid; This query isn't very helpful because it fails to show locks that are not directly associated with tables. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum hung?
Alvaro Herrera [alvhe...@commandprompt.com] wrote: What's vacuum_cost_delay? #vacuum_cost_delay = 0 # 0-1000 milliseconds #vacuum_cost_page_hit = 1 # 0-1 credits #vacuum_cost_page_miss = 10 # 0-1 credits #vacuum_cost_page_dirty = 20# 0-1 credits #vacuum_cost_limit = 200# 0-1 credits so, whatever the default happens to be. Thanks, Brian -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum hung?
Brian Cox wrote: > autovacuum has been running on 2 tables for > 5 hours. There tables are > not huge (see below). For the past ~1 hour, I've shut off all other > activity on this database. The other table being vacuumed has more rows > (1897810). Anyone have any ideas about why this is taking so long? What's vacuum_cost_delay? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Autovacuum does not stay turned off
Thanks for the help. The applied solution follows. We will be taking a number of maintenance steps to manage these very high update tables which I will summarize later as I suspect we are not the only ones with this challenge. http://www.postgresql.org/docs/current/interactive/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND http://www.postgresql.org/docs/current/interactive/catalog-pg-autovacuum.html data_store=# SELECT relname, oid, age(relfrozenxid) FROM pg_class WHERE relkind = 'r'; ... hour_summary| 16392 | 252934596 percentile_metadata | 20580 | 264210966 (51 rows) data_store=# insert into pg_autovacuum values (16392,false,35000,2,35000,1,200,200,35000,5); INSERT 0 1 data_store=# insert into pg_autovacuum values (20580,false,35000,2,35000,1,200,200,35000,5); INSERT 0 1 data_store=# hubert depesz lubaczewski wrote: On Tue, Aug 26, 2008 at 10:45:31AM -0600, Jerry Champlin wrote: This makes sense. What queries can I run to see how close to the limit we are? We need to determine if we should stop the process which updates and inserts into this table until after the critical time this afternoon when we can perform the required maintenance on this table. select datname, age(datfrozenxid) from pg_database; Best regards, depesz -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Autovacuum does not stay turned off
On Tue, Aug 26, 2008 at 10:45:31AM -0600, Jerry Champlin wrote: > This makes sense. What queries can I run to see how close to the limit > we are? We need to determine if we should stop the process which > updates and inserts into this table until after the critical time this > afternoon when we can perform the required maintenance on this table. select datname, age(datfrozenxid) from pg_database; Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: [EMAIL PROTECTED] / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Autovacuum does not stay turned off
This makes sense. What queries can I run to see how close to the limit we are? We need to determine if we should stop the process which updates and inserts into this table until after the critical time this afternoon when we can perform the required maintenance on this table. hubert depesz lubaczewski wrote: On Tue, Aug 26, 2008 at 09:27:48AM -0600, Jerry Champlin wrote: Does anyone know what will cause this bahavior for autovacuum? http://www.postgresql.org/docs/current/interactive/runtime-config-autovacuum.html -> autovacuum_freeze_max_age depesz Andrew Sullivan wrote: On Tue, Aug 26, 2008 at 09:27:48AM -0600, Jerry Champlin wrote: Does anyone know what will cause this bahavior for autovacuum? You're probably approaching the wraparound limit in some database. If you think you can't afford the overhead when users are accessing the system, when are you vacuuming? A We are changing the table structure tonight. These two tables are very highly updated. The goal is to use autovacuum but not have it take 10 days to run on a 13MM record table. Thanks -Jerry
Re: [PERFORM] Autovacuum does not stay turned off
On Tue, Aug 26, 2008 at 09:27:48AM -0600, Jerry Champlin wrote: > > Does anyone know what will cause this bahavior for autovacuum? You're probably approaching the wraparound limit in some database. If you think you can't afford the overhead when users are accessing the system, when are you vacuuming? A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Autovacuum does not stay turned off
On Tue, Aug 26, 2008 at 09:27:48AM -0600, Jerry Champlin wrote: > Does anyone know what will cause this bahavior for autovacuum? http://www.postgresql.org/docs/current/interactive/runtime-config-autovacuum.html -> autovacuum_freeze_max_age depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: [EMAIL PROTECTED] / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum: use case for indenpedent TOAST table autovac settings
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Does anyone see a need for having TOAST tables be individually > configurable for autovacuum? I've finally come around to looking at > being able to use ALTER TABLE for autovacuum settings, and I'm wondering > if we need to support that case. It seems like we'll want to do it somehow. Perhaps the cleanest way is to incorporate toast-table settings in the reloptions of the parent table. Otherwise dump/reload is gonna be a mess. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] autovacuum: recommended?
On Nov 19, 2007, at 9:23 AM, Tom Lane wrote: Decibel! <[EMAIL PROTECTED]> writes: FWIW, 20k rows isn't all that big, so I'm assuming that the descriptions make the table very wide. Unless those descriptions are what's being updated frequently, I suggest you put those in a separate table (vertical partitioning). That will make the main table much easier to vacuum, as well as reducing the impact of the high churn rate. Uh, you do realize that the TOAST mechanism does that pretty much automatically? Only if the row exceeds 2k, which for a lot of applications is huge. This is exactly why I wish toast limits were configurable on a per- table basis (I know there were changes here for 8.3, but IIRC it was only for toast chunk size). -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [PERFORM] autovacuum: recommended?
On Mon, 19 Nov 2007, Jean-David Beyer wrote: I am pretty sure they will never upgrade RHEL5 to the 8.2 series because they do not do it to get new features. That's correct. I do know that if I try to use .rpms from other sources, I can get in a lot of trouble with incompatible libraries. And I cannot upgrade the libraries without damaging other programs. You're also right that this is tricky. I've written a guide that goes over the main issues involved at http://www.westnet.com/~gsmith/content/postgresql/pgrpm.htm if you ever wanted to explore this as an option. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] autovacuum: recommended?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 19 Nov 2007 08:51:42 -0500 Bill Moran <[EMAIL PROTECTED]> wrote: > > Luckily I do not seem to be troubled by the problems experienced by > > the O.P. > > > > I do know that if I try to use .rpms from other sources, I can get > > in a lot of trouble with incompatible libraries. And I cannot > > upgrade the libraries without damaging other programs. > > I think you've missed the point. > > The discussion is not that the distro is bad because it hasn't moved > from 8.1 -> 8.2. The comment is that it's bad because it hasn't > updated a major branch with the latest bug fixes. i.e. it hasn't > moved from 8.1.4 to 8.1.5. > > If this is indeed the case, I agree that such a distro isn't worth > using. I would note, and Tom would actually be a better person to expound on this that Red Hat has a tendency (at least they used to) to leave the minor number unchanged. E.g; 8.1.4 is shipped with RHEL5 They release a service update You now have 8.1.4-1.9 Or some such drivel. They do this because application vendors wet themselves in fear if they see a version change midcyle no matter how much you tell them it is just security and data fixes... /me who has dealt with 3 "enterprise" vendors on this exact issues in the last week. Sincerely, Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHQcWGATb/zqfZUUQRAtYmAJ9QKuH/mou87XCwiBoDPiw+03ST7QCfRMlb n7+IVftfOrPBd2+CKA6B1N4= =MMKO -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] autovacuum: recommended?
Bill Moran wrote: > In response to Jean-David Beyer <[EMAIL PROTECTED]>: > >> Decibel! wrote: >>> On Nov 18, 2007, at 1:26 PM, gabor wrote: hubert depesz lubaczewski wrote: > On Fri, Nov 16, 2007 at 10:40:43AM +0100, Gábor Farkas wrote: >> we are moving one database from postgresql-7.4 to postgresql-8.2.4. > any particular reason why not 8.2.5? the distribution i use only has 8.2.4 currently. >>> Then I think you need to consider abandoning your distribution's >>> packages or find a better distribution. IIRC, 8.2.5 is over 2-3 months >>> old now; there's no reason a distribution shouldn't have it at this >>> point. (Unless of course you haven't kept your distribution >>> up-to-date... ;) >> Some people run distributions such as Red Hat Enterprise Linux 5 (their >> latest); I do. postgresql that comes with that. >> >> Now once they pick a version of a program, they seldom change it. They do >> put security and bug fixes in it by back-porting the changes into the source >> code and rebuilding it. I guess for postgresql the changes were too much for >> backporting, so they upgraded from postgresql-8.1.4-1.1 that came with it >> originally and are now up to postgresql-8.1.9-1.el5. I am pretty sure they >> will never upgrade RHEL5 to the 8.2 series because they do not do it to get >> new features. >> >> Now you may think there are better distributions than Red Hat Enterprise >> Linux 5, but enough people seem to think it good enough to pay for it and >> keep Red Hat in business. I doubt they are all foolish. >> [snip] > > I think you've missed the point. I think you are right. > > The discussion is not that the distro is bad because it hasn't moved from > 8.1 -> 8.2. The comment is that it's bad because it hasn't updated a > major branch with the latest bug fixes. i.e. it hasn't moved from 8.1.4 > to 8.1.5. > > If this is indeed the case, I agree that such a distro isn't worth using. > ... and I can keep RHEL5 because they went from 8.1.4 to 8.1.9. ;-) -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 10:40:01 up 27 days, 3:58, 2 users, load average: 4.43, 4.85, 5.17 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] autovacuum: recommended?
Decibel! <[EMAIL PROTECTED]> writes: > FWIW, 20k rows isn't all that big, so I'm assuming that the > descriptions make the table very wide. Unless those descriptions are > what's being updated frequently, I suggest you put those in a > separate table (vertical partitioning). That will make the main table > much easier to vacuum, as well as reducing the impact of the high > churn rate. Uh, you do realize that the TOAST mechanism does that pretty much automatically? regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] autovacuum: recommended?
In response to Jean-David Beyer <[EMAIL PROTECTED]>: > Decibel! wrote: > > On Nov 18, 2007, at 1:26 PM, gabor wrote: > >> hubert depesz lubaczewski wrote: > >>> On Fri, Nov 16, 2007 at 10:40:43AM +0100, Gábor Farkas wrote: > we are moving one database from postgresql-7.4 to postgresql-8.2.4. > >>> any particular reason why not 8.2.5? > >> > >> the distribution i use only has 8.2.4 currently. > > > > Then I think you need to consider abandoning your distribution's > > packages or find a better distribution. IIRC, 8.2.5 is over 2-3 months > > old now; there's no reason a distribution shouldn't have it at this > > point. (Unless of course you haven't kept your distribution > > up-to-date... ;) > > Some people run distributions such as Red Hat Enterprise Linux 5 (their > latest); I do. postgresql that comes with that. > > Now once they pick a version of a program, they seldom change it. They do > put security and bug fixes in it by back-porting the changes into the source > code and rebuilding it. I guess for postgresql the changes were too much for > backporting, so they upgraded from postgresql-8.1.4-1.1 that came with it > originally and are now up to postgresql-8.1.9-1.el5. I am pretty sure they > will never upgrade RHEL5 to the 8.2 series because they do not do it to get > new features. > > Now you may think there are better distributions than Red Hat Enterprise > Linux 5, but enough people seem to think it good enough to pay for it and > keep Red Hat in business. I doubt they are all foolish. > > Luckily I do not seem to be troubled by the problems experienced by the O.P. > > I do know that if I try to use .rpms from other sources, I can get in a lot > of trouble with incompatible libraries. And I cannot upgrade the libraries > without damaging other programs. I think you've missed the point. The discussion is not that the distro is bad because it hasn't moved from 8.1 -> 8.2. The comment is that it's bad because it hasn't updated a major branch with the latest bug fixes. i.e. it hasn't moved from 8.1.4 to 8.1.5. If this is indeed the case, I agree that such a distro isn't worth using. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] autovacuum: recommended?
Decibel! wrote: > On Nov 18, 2007, at 1:26 PM, gabor wrote: >> hubert depesz lubaczewski wrote: >>> On Fri, Nov 16, 2007 at 10:40:43AM +0100, Gábor Farkas wrote: we are moving one database from postgresql-7.4 to postgresql-8.2.4. >>> any particular reason why not 8.2.5? >> >> the distribution i use only has 8.2.4 currently. > > Then I think you need to consider abandoning your distribution's > packages or find a better distribution. IIRC, 8.2.5 is over 2-3 months > old now; there's no reason a distribution shouldn't have it at this > point. (Unless of course you haven't kept your distribution > up-to-date... ;) Some people run distributions such as Red Hat Enterprise Linux 5 (their latest); I do. postgresql that comes with that. Now once they pick a version of a program, they seldom change it. They do put security and bug fixes in it by back-porting the changes into the source code and rebuilding it. I guess for postgresql the changes were too much for backporting, so they upgraded from postgresql-8.1.4-1.1 that came with it originally and are now up to postgresql-8.1.9-1.el5. I am pretty sure they will never upgrade RHEL5 to the 8.2 series because they do not do it to get new features. Now you may think there are better distributions than Red Hat Enterprise Linux 5, but enough people seem to think it good enough to pay for it and keep Red Hat in business. I doubt they are all foolish. Luckily I do not seem to be troubled by the problems experienced by the O.P. I do know that if I try to use .rpms from other sources, I can get in a lot of trouble with incompatible libraries. And I cannot upgrade the libraries without damaging other programs. -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 08:20:01 up 27 days, 1:38, 1 user, load average: 5.15, 5.20, 5.01 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] autovacuum: recommended?
> FWIW, 20k rows isn't all that big, so I'm assuming that the > descriptions make the table very wide. Unless those descriptions are > what's being updated frequently, I suggest you put those in a > separate table (vertical partitioning). That will make the main table > much easier to vacuum, as well as reducing the impact of the high > churn rate. Yes, you're right - the table is quite wide, as it's a catalogue of a pharmacy along with all the detailed descriptions and additional info etc. So I guess it's 50 MB of data or something like that. That may not seem bad, but as I already said the table grew to about 12x the size during the day (so about 500MB of data, 450MB being dead rows). This is the 'central' table of the system, and there are other quite heavily used databases as well. Add some really stupid queries on this table (for example LIKE searches on the table) and you easily end up with 100MB of permanent I/O during the day. The vertical partitioning would be overengineering in this case - we considered even that, but proper optimization of the update process (updating only those rows that really changed), along with a little bit of autovacuum tuning solved all the performance issues. Tomas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] autovacuum: recommended?
On Nov 16, 2007, at 5:56 AM, Csaba Nagy wrote:
We are doing that here, i.e. set up autovacuum not to touch big
tables,
and cover those with nightly vacuums if there is still some
activity on
them, and one weekly complete vacuum of the whole DB ("vacuum" without
other params, preferably as the postgres user to cover system tables
too).
IIRC, since 8.2 autovacuum will take note of manual vacuums so as not
to needlessly vacuum something that's been recently vacuumed
manually. In other words, you shouldn't need to disable autovac for
large tables if you vacuum them every night and their churn rate is
low enough to not trigger autovacuum during the day.
In fact we also have a few very frequently updated small tables, those
are also covered by very frequent crontab vacuums because in 8.2
autovacuum can spend quite some time vacuuming some medium sized
tables
and in that interval the small but frequently updated ones get
bloated.
This should be better with 8.3 and multiple autovacuum workers.
+1. For tables that should always remain relatively small (ie: a web
session table), I usually recommend setting up a manual vacuum that
runs every 1-5 minutes.
--
Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
smime.p7s
Description: S/MIME cryptographic signature
Re: [PERFORM] autovacuum: recommended?
On Nov 16, 2007, at 7:38 AM, [EMAIL PROTECTED] wrote: The table was quite huge (say 20k of products along with detailed descriptions etc.) and was completely updated and about 12x each day, i.e. it qrew to about 12x the original size (and 11/12 of the rows were dead). This caused a serious slowdown of the application each day, as the database had to scan 12x more data. FWIW, 20k rows isn't all that big, so I'm assuming that the descriptions make the table very wide. Unless those descriptions are what's being updated frequently, I suggest you put those in a separate table (vertical partitioning). That will make the main table much easier to vacuum, as well as reducing the impact of the high churn rate. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [PERFORM] autovacuum: recommended?
On Nov 18, 2007, at 1:26 PM, gabor wrote: hubert depesz lubaczewski wrote: On Fri, Nov 16, 2007 at 10:40:43AM +0100, Gábor Farkas wrote: we are moving one database from postgresql-7.4 to postgresql-8.2.4. any particular reason why not 8.2.5? the distribution i use only has 8.2.4 currently. Then I think you need to consider abandoning your distribution's packages or find a better distribution. IIRC, 8.2.5 is over 2-3 months old now; there's no reason a distribution shouldn't have it at this point. (Unless of course you haven't kept your distribution up- to-date... ;) -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [PERFORM] autovacuum: recommended?
hubert depesz lubaczewski wrote: On Fri, Nov 16, 2007 at 10:40:43AM +0100, Gábor Farkas wrote: we are moving one database from postgresql-7.4 to postgresql-8.2.4. any particular reason why not 8.2.5? the distribution i use only has 8.2.4 currently. gabor ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] autovacuum: recommended?
[EMAIL PROTECTED] > The table was quite huge (say 20k of products along with detailed > descriptions etc.) and was completely updated and about 12x each day, i.e. > it qrew to about 12x the original size (and 11/12 of the rows were dead). > This caused a serious slowdown of the application each day, as the > database had to scan 12x more data. The tables we had problems with are transaction-type tables with millions of rows and mostly inserts to the table ... and, eventually some few attributes being updated only on the most recent entries. I tried tuning a lot, but gave it up eventually. Vacuuming those tables took a long time (even if only a very small fraction of the table was touched) and the performance of the inserts to the table was reduced to a level that could not be accepted. By now we've just upgraded the hardware, so it could be worth playing with it again, but our project manager is both paranoid and conservative and proud of it, so I would have to prove that autovacuum is good for us before I'm allowed to turn it on again ;-) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] autovacuum: recommended?
On Fri, 2007-11-16 at 12:13 +0100, Tobias Brox wrote:
> [snip] should i use both auto-vacuum and
> > manual-vacuum?
I would say for 8.2 that's the best strategy (which might change with
8.3 and it's multiple vacuum workers thingy).
> That being said, we have some huge tables in our database and pretty
> much traffic, and got quite some performance problems when the
> autovacuum kicked in and started vacuuming those huge tables, so we're
> currently running without. Autovacuum can be tuned to not touch those
> tables, but we've chosen to leave it off.
We are doing that here, i.e. set up autovacuum not to touch big tables,
and cover those with nightly vacuums if there is still some activity on
them, and one weekly complete vacuum of the whole DB ("vacuum" without
other params, preferably as the postgres user to cover system tables
too).
In fact we also have a few very frequently updated small tables, those
are also covered by very frequent crontab vacuums because in 8.2
autovacuum can spend quite some time vacuuming some medium sized tables
and in that interval the small but frequently updated ones get bloated.
This should be better with 8.3 and multiple autovacuum workers.
For the "disable for autovacuum" part search for pg_autovacuum in the
docs.
I would say the autovacuum + disable autovacuum on big tables + nightly
vacuum + weekly vacuumdb + frequent crontab vacuum of very updated small
tables works well in 8.2. One thing which could be needed is to also
schedule continuous vacuum of big tables which are frequently updated,
with big delay settings to throttle the resources used by the vacuum. We
don't need that here because we don't update frequently our big
tables...
Cheers,
Csaba.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] autovacuum: recommended?
[Gábor Farkas - Fri at 10:40:43AM +0100] > my question is: is it recommended to use it? or in other words, should i > only use autovacuum? or it's better to use manual-vacuuming? which one > is the "way of the future" :) ? or should i use both auto-vacuum and > manual-vacuum? Nightly vacuums are great if the activity on the database is very low night time. A combination is also good, the autovacuum will benefit from the nightly vacuum. My gut feeling says it's a good idea to leave autovacuum on, regardless of whether the nightly vacuums have been turned on or not. That being said, we have some huge tables in our database and pretty much traffic, and got quite some performance problems when the autovacuum kicked in and started vacuuming those huge tables, so we're currently running without. Autovacuum can be tuned to not touch those tables, but we've chosen to leave it off. ---(end of broadcast)--- TIP 6: explain analyze is your friend
