Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

2017-07-24 Thread Peter Geoghegan
On Mon, Jul 24, 2017 at 10:50 AM, Joshua D. Drake wrote: > Does this suggest that we don't have a cleanup problem but a fragmentation > problem (or both at least for the index)? Having an index that is almost > twice the uncleaned up size isn't that uncommon. As Tom

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

2017-07-24 Thread Joshua D. Drake
On 07/23/2017 12:03 PM, Joshua D. Drake wrote: As you can see even with aggressive vacuuming, over a period of 36 hours life gets increasingly miserable. The largest table is: postgres=# select pg_size_pretty(pg_total_relation_size('bmsql_order_line')); pg_size_pretty

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

2017-07-23 Thread Joshua D. Drake
Hello, I changed the test to run for 6 hours at a time regardless of number of transactions. I also changed the du command to only look at the database (previously wal logs were included). This is the clearest indication of the problem I have been able to produce. Again, this is with 128

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

2017-07-21 Thread Joshua D. Drake
On 07/20/2017 08:58 PM, Joshua D. Drake wrote: On 07/19/2017 07:57 PM, Tom Lane wrote: Peter Geoghegan writes: Test 1: 55G/srv/main TPS:955 Test 2: 112G/srv/main TPS:531 (Not sure what happened here, long checkpoint?) Test 3: 109G/srv/main TPS:868

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

2017-07-21 Thread Joshua D. Drake
On 07/20/2017 11:54 PM, Sokolov Yura wrote: On 2017-07-21 06:58, Joshua D. Drake wrote: On 07/19/2017 07:57 PM, Tom Lane wrote: Peter Geoghegan writes: -- Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc PostgreSQL Centered full stack support,

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

2017-07-21 Thread Sokolov Yura
On 2017-07-21 06:58, Joshua D. Drake wrote: On 07/19/2017 07:57 PM, Tom Lane wrote: Peter Geoghegan writes: My argument for the importance of index bloat to the more general bloat problem is simple: any bloat that accumulates, that cannot be cleaned up, will probably accumulate

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

2017-07-20 Thread Mark Kirkwood
On 21/07/17 15:58, Joshua D. Drake wrote: On 07/19/2017 07:57 PM, Tom Lane wrote: Peter Geoghegan writes: My argument for the importance of index bloat to the more general bloat problem is simple: any bloat that accumulates, that cannot be cleaned up, will probably accumulate

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

2017-07-20 Thread Joshua D. Drake
On 07/19/2017 07:57 PM, Tom Lane wrote: Peter Geoghegan writes: My argument for the importance of index bloat to the more general bloat problem is simple: any bloat that accumulates, that cannot be cleaned up, will probably accumulate until it impacts performance quite

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

2017-07-20 Thread Peter Geoghegan
On Thu, Jul 20, 2017 at 7:45 AM, Claudio Freire wrote: >> For the purposes of this discussion, I'm mostly talking about >> duplicates within a page on a unique index. If the keyspace owned by >> an int4 unique index page only covers 20 distinct values, it will only >> ever

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

2017-07-20 Thread Jeff Janes
On Thu, Jul 20, 2017 at 6:28 AM, Stephen Frost wrote: > Greetings, > > * Sokolov Yura (y.soko...@postgrespro.ru) wrote: > > I wrote two days ago about vacuum ring buffer: > > https://www.postgresql.org/message-id/8737e9bddb82501da1134f021bf492 > 9a%40postgrespro.ru > > > >

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

2017-07-20 Thread Claudio Freire
On Thu, Jul 20, 2017 at 12:08 AM, Peter Geoghegan wrote: >> The traditional >> wisdom about btrees, for instance, is that no matter how full you pack >> them to start with, the steady state is going to involve something like >> 1/3rd free space. You can call that bloat if you want,

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

2017-07-20 Thread Joshua D. Drake
On 07/20/2017 06:28 AM, Stephen Frost wrote: It's not clear off-hand how much that would improve this case, as the database size appears to pretty quickly get beyond the OS memory size (and only in the first test is the DB starting size less than system memory to begin with). FYI, I will be

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

2017-07-20 Thread Stephen Frost
Greetings, * Sokolov Yura (y.soko...@postgrespro.ru) wrote: > I wrote two days ago about vacuum ring buffer: > https://www.postgresql.org/message-id/8737e9bddb82501da1134f021bf4929a%40postgrespro.ru > > Increasing Vacuum's ring buffer to size of Bulk Writer's one reduces > autovacuum time in

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

2017-07-20 Thread Sokolov Yura
On 2017-07-20 05:52, Masahiko Sawada wrote: On Thu, Jul 20, 2017 at 8:24 AM, Stephen Frost wrote: * Tom Lane (t...@sss.pgh.pa.us) wrote: "Joshua D. Drake" writes: > At PGConf US Philly last week I was talking with Jim and Jan about > performance.

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

2017-07-19 Thread Peter Geoghegan
On Wed, Jul 19, 2017 at 7:57 PM, Tom Lane wrote: > Peter Geoghegan writes: >> My argument for the importance of index bloat to the more general >> bloat problem is simple: any bloat that accumulates, that cannot be >> cleaned up, will probably accumulate until

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

2017-07-19 Thread Tom Lane
Peter Geoghegan writes: > My argument for the importance of index bloat to the more general > bloat problem is simple: any bloat that accumulates, that cannot be > cleaned up, will probably accumulate until it impacts performance > quite noticeably. But that just begs the question:

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

2017-07-19 Thread Masahiko Sawada
On Thu, Jul 20, 2017 at 8:24 AM, Stephen Frost wrote: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> "Joshua D. Drake" writes: >> > At PGConf US Philly last week I was talking with Jim and Jan about >> > performance. One of the items that came up is that

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

2017-07-19 Thread Peter Geoghegan
On Wed, Jul 19, 2017 at 3:54 PM, Alvaro Herrera wrote: >> Index bloat is a general problem that B-Trees have in all other major >> systems, but I think that PostgreSQL has a tendency to allow indexes >> to become progressively more bloated over time, in a way that it

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

2017-07-19 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: > "Joshua D. Drake" writes: > > At PGConf US Philly last week I was talking with Jim and Jan about > > performance. One of the items that came up is that PostgreSQL can't run > > full throttle for long periods of time. The long and

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

2017-07-19 Thread Joe Conway
On 07/19/2017 03:29 PM, Tom Lane wrote: > "Joshua D. Drake" writes: >> At PGConf US Philly last week I was talking with Jim and Jan about >> performance. One of the items that came up is that PostgreSQL can't run >> full throttle for long periods of time. The long and

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

2017-07-19 Thread Alvaro Herrera
Peter Geoghegan wrote: > Index bloat is a general problem that B-Trees have in all other major > systems, but I think that PostgreSQL has a tendency to allow indexes > to become progressively more bloated over time, in a way that it often > can never recover from [1]. Interesting assertion.

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

2017-07-19 Thread Tom Lane
"Joshua D. Drake" writes: > At PGConf US Philly last week I was talking with Jim and Jan about > performance. One of the items that came up is that PostgreSQL can't run > full throttle for long periods of time. The long and short is that no > matter what, autovacuum

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

2017-07-19 Thread Peter Geoghegan
On Wed, Jul 19, 2017 at 3:11 PM, Joshua D. Drake wrote: > The good news is, PostgreSQL is not doing half bad against 128 connections > with only 16vCPU. The bad news is we more than doubled our disk size without > getting reuse or bloat under control. The concern here is

[HACKERS] autovacuum can't keep up, bloat just continues to rise

2017-07-19 Thread Joshua D. Drake
Hello, At PGConf US Philly last week I was talking with Jim and Jan about performance. One of the items that came up is that PostgreSQL can't run full throttle for long periods of time. The long and short is that no matter what, autovacuum can't keep up. This is what I have done: Machine: