[PERFORM] Questionaire: Common WAL write rates on busy servers.

[Andres Freund]

Hi,

I've lately seen more and more installations where the generation of
write-ahead-log (WAL) is one of the primary bottlenecks.  I'm curious
whether that's primarily a "sampling error" of mine, or whether that's
indeed more common.

The primary reason I'm curious is that I'm pondering a few potential
optimizations, and would like to have some guidance which are more and
which are less important.

Questions (answer as many you can comfortably answer):
- How many MB/s, segments/s do you see on busier servers?
- What generates the bulk of WAL on your servers (9.5+ can use
  pg_xlogdump --stats to compute that)?
- Are you seeing WAL writes being a bottleneck?OA
- What kind of backup methods are you using and is the WAL volume a
  problem?
- What kind of replication are you using and is the WAL volume a
  problem?
- What are your settings for wal_compression, max_wal_size (9.5+) /
  checkpoint_segments (< 9.5), checkpoint_timeout and wal_buffers?
- Could you quickly describe your workload?

Feel free to add any information you think is pertinent ;)

Greetings,

Andres Freund


-- 
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] Delete, foreign key, index usage

[Tomas Vondra]

On 04/24/2017 08:48 AM, Johann Spies wrote:


Why would the planner prefer the use the gin index and not the btree
index in this case?



You'll need to show what queries are you running - that's a quite 
important piece of information, and I don't see it anywhere in this 
thread. Seeing explain plans would also be helpful.


regards

--
Tomas Vondra  http://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: [PERFORM] Delete, foreign key, index usage

[Johann Spies]

> On Wed, Apr 5, 2017 at 6:40 AM, Johann Spies  wrote:
>>
>> On 4 April 2017 at 14:07, Johann Spies  wrote:
>>
>> > Why would that be?
>>
>> To answer my own question.  After experimenting a lot we found that
>> 9.6 uses a parallel seqscan that is actually a lot faster than using
>> the index on these large tables.

Further experimenting resulted in a solution which we do not understand:

The table 'publication'  had the field 'ut' as primary key and the ut
index was not used.

So we built an additional btree index(ut) on publication - which was
ignored as well.
Then we built a gin index(ut) on publication and now it is being used.

The same happened on the other table (belongs_to) where the btree
index was ignored by the planner but the gin-index used.

As a result our deletes runs between 25-60 times faster than earlier
with maximum of about 20 records per hour in comparison with a
maximum of 4500 earlier..

In the case of both tables the ut has a foreign key reference to
another article.

Why would the planner prefer the use the gin index and not the btree
index in this case?

Regards
Johann
-- 
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance