Hot Standby query cancellation and Streaming Replication integration
in [PgSql]
|
Prev: Why isn't stats_temp_directory automatically created?
Next: Hot Standby query cancellation and Streaming Replicationintegration
From: Simon Riggs on 2 Mar 2010 16:00 On Sun, 2010-02-28 at 16:56 +0100, Joachim Wieland wrote: > Now let's take a look at both scenarios from the administrators' point > of view: Well argued, agree with all of your points. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Greg Stark on 10 Mar 2010 06:38 On Wed, Mar 10, 2010 at 6:29 AM, Josh Berkus <josh(a)agliodbs.com> wrote: > Then I increased vacuum_defer_cleanup_age to 100000, which represents > about 5 minutes of transactions on the test system. This eliminated all > query cancels for the reporting query, which takes an average of 10s. > > Next is a database bloat test, but I'll need to do that on a system with > more free space than my laptop. Note that this will be heavily dependent on the use case. If you have one of those counter records that keeps being updated and gets cleaned up by HOT whenever the page fills up then you need to allow HOT to clean it up before it overflows the page or else it'll bloat the table and require a real vacuum. I think that means that a vacuum_defer_cleanup of up to about 100 or so (it depends on the width of your counter record) might be reasonable as a general suggestion but anything higher will depend on understanding the specific system. Another use case that might suprise people who are accustomed to the current behaviour is massive updates. This is the main really pessimal use case left in Postgres -- ideally they wouldn't bloat the table at all but currently they double the size of the table. People may be accustomed to the idea that they can then run vacuum and that will limit the bloat to 50%, assuming they have no (other) long-lived transactions. With vacuum_defer_cleanup that will no longer be true. It will be as if you always have a query lasting n transactions in your system at all times. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Fujii Masao on 10 Mar 2010 19:51
On Wed, Mar 10, 2010 at 3:29 PM, Josh Berkus <josh(a)agliodbs.com> wrote: > I've been playing with vacuum_defer_cleanup_age in reference to the > query cancel problem. It really seems to me that this is the way > forward in terms of dealing with query cancel for normal operation > rather than wal_standby_delay, or maybe in combination with it. Why isn't vacuum_defer_cleanup_age listed on postgresql.conf.sample? Though I also tried to test the effect of it, I was unable to find it in the conf file. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |