Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade

[Gunther]

I confess I don't do dump or any backups much other than file system 
snapshots.


But when I do, I don't like how long it takes.

I confess my database is big, I have about 200 GB. But still, dumping it 
should not take 48 hours (and running) while the system is 75% idle and 
reads are at 4.5 MB/s when the system sustains over 100 MB/s during 
processing of table scan and hash join queries.


Something is wrong with the dump thing. And no, it's not SSL or 
whatever, I am doing it on a local system with local connections. 
Version 9.5 something.


regards,
-Gunther


On 11/23/2017 4:26, Henrik Cednert (Filmlance) wrote:



On 22 Nov 2017, at 22:07, Patrick KUI-LI > wrote:


Hello,

I had this behaviors when the upgraded pg 9.5 was on ssl mode by default.

So i deactivated ssl mode in postgresql.conf. That's all.

Regards,

Patrick




Hello

And you just uncommented the  'ssl = off' line in the config for this?

Is this default behaviour different from 8.4? Is there a 'show running 
config' for pgsql?


I tried that in the test vm and didn't really give me a significant 
difference.


COMPRESSION LEVEL: 6, SSL ON
real82m33.744s
user60m55.069s
sys3m3.375s

COMPRESSION LEVEL: 6, SSL OFF
real76m31.083s
user61m23.282s
sys1m23.341s

Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

[Ulf Lohbrügge]

I could reproduce part of the things I described earlier in this thread. A
guy named Andriy Senyshyn mailed me after reading this thread here (he
could somehow not join the mailing list) and observed a difference when
issuing "SET ROLE" as user postgres and as a non-superuser.

When I connect as superuser postgres to mydb and execute a "SET ROLE"
things are pretty fast:


$ PGOPTIONS='-c client-min-messages=DEBUG5' psql -U postgres mydb
DEBUG:  CommitTransaction
DEBUG:  name: unnamed; blockState:   STARTED; state: INPROGR,
xid/subid/cid: 0/1/0, nestlvl: 1, children:
psql (9.6.6)
Type "help" for help.

magicline=# \timing
Timing is on.
magicline=# SET ROLE tenant1337;
DEBUG:  StartTransactionCommand
DEBUG:  StartTransaction
DEBUG:  name: unnamed; blockState:   DEFAULT; state: INPROGR,
xid/subid/cid: 0/1/0, nestlvl: 1, children:
DEBUG:  ProcessUtility
DEBUG:  CommitTransactionCommand
DEBUG:  CommitTransaction
DEBUG:  name: unnamed; blockState:   STARTED; state: INPROGR,
xid/subid/cid: 0/1/0, nestlvl: 1, children:
SET
Time: 0.968 ms
magicline=>


When I connect as user admin (non-superuser with NOINHERIT attribute) to
mydb, the first "SET ROLE" statement is always quite slow in comparison to
the former "SET ROLE" statement executed by superuser postgres:


$ PGOPTIONS='-c client-min-messages=DEBUG5' psql -U admin mydb
DEBUG:  CommitTransaction
DEBUG:  name: unnamed; blockState:   STARTED; state: INPROGR,
xid/subid/cid: 0/1/0, nestlvl: 1, children:
psql (9.6.6)
Type "help" for help.

magicline=> \timing
Timing is on.
magicline=> SET ROLE tenant1337;
DEBUG:  StartTransactionCommand
DEBUG:  StartTransaction
DEBUG:  name: unnamed; blockState:   DEFAULT; state: INPROGR,
xid/subid/cid: 0/1/0, nestlvl: 1, children:
DEBUG:  ProcessUtility
DEBUG:  rehashing catalog cache id 8 for pg_auth_members; 17 tups, 8 buckets
DEBUG:  rehashing catalog cache id 8 for pg_auth_members; 33 tups, 16
buckets
DEBUG:  rehashing catalog cache id 8 for pg_auth_members; 65 tups, 32
buckets
DEBUG:  rehashing catalog cache id 8 for pg_auth_members; 129 tups, 64
buckets
DEBUG:  rehashing catalog cache id 8 for pg_auth_members; 257 tups, 128
buckets
DEBUG:  rehashing catalog cache id 8 for pg_auth_members; 513 tups, 256
buckets
DEBUG:  rehashing catalog cache id 8 for pg_auth_members; 1025 tups, 512
buckets
DEBUG:  CommitTransactionCommand
DEBUG:  CommitTransaction
DEBUG:  name: unnamed; blockState:   STARTED; state: INPROGR,
xid/subid/cid: 0/1/0, nestlvl: 1, children:
SET
Time: 31.858 ms
magicline=>


Subsequent "SET ROLE" calls in the above session of user admin are pretty
fast (below 1 ms).

I further wonder what those log statements "rehashing catalog cache..." do
and if they are the cause of the slow execution.

So this does not reproduce my observed query times >2000ms but is maybe a
hint for other things that might be worth looking into.

Regards,
Ulf

2017-11-08 10:31 GMT+01:00 Ulf Lohbrügge :

> 2017-11-08 0:45 GMT+01:00 Tom Lane :
>
>> =?UTF-8?Q?Ulf_Lohbr=C3=BCgge?=  writes:
>> > I just ran "check_postgres.pl --action=bloat" and got the following
>> output:
>> > ...
>> > Looks fine, doesn't it?
>>
>> A possible explanation is that something is taking an exclusive lock
>> on some system catalog and holding it for a second or two.  If so,
>> turning on log_lock_waits might provide some useful info.
>>
>> regards, tom lane
>>
>
> I just checked my configuration and found out that "log_lock_waits" was
> already enabled.
>
> Unfortunately there is no log output of locks when those long running "SET
> ROLE" statements occur.
>
> Regards,
> Ulf
>
>

Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

[Tom Lane]

=?UTF-8?Q?Ulf_Lohbr=C3=BCgge?=  writes:
> I could reproduce part of the things I described earlier in this thread. A
> guy named Andriy Senyshyn mailed me after reading this thread here (he
> could somehow not join the mailing list) and observed a difference when
> issuing "SET ROLE" as user postgres and as a non-superuser.

This isn't particularly surprising in itself.  When we know that the
session user is a superuser, SET ROLE just succeeds immediately.
Otherwise we have to determine whether the SET is allowed, ie, is
the session user a member of the specified role.

It looks like the first time such a question is asked within a session,
we build and cache a list of all the roles the session user is a member
of (directly or indirectly).  That's what's taking the time here ---
apparently in your test case, the "admin" role is a member of a whole lot
of roles?

regards, tom lane

Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

[Ulf Lohbrügge]

2017-12-07 17:01 GMT+01:00 Tom Lane :

> =?UTF-8?Q?Ulf_Lohbr=C3=BCgge?=  writes:
> > I could reproduce part of the things I described earlier in this thread.
> A
> > guy named Andriy Senyshyn mailed me after reading this thread here (he
> > could somehow not join the mailing list) and observed a difference when
> > issuing "SET ROLE" as user postgres and as a non-superuser.
>
> This isn't particularly surprising in itself.  When we know that the
> session user is a superuser, SET ROLE just succeeds immediately.
> Otherwise we have to determine whether the SET is allowed, ie, is
> the session user a member of the specified role.
>
> It looks like the first time such a question is asked within a session,
> we build and cache a list of all the roles the session user is a member
> of (directly or indirectly).  That's what's taking the time here ---
> apparently in your test case, the "admin" role is a member of a whole lot
> of roles?
>

Yes, the user "admin" is member of more than 1k roles.

So this cache will not invalidate during the lifetime of the session unless
a new role is added, I guess?

Is there any locking involved when this cache gets invalidated? Could this
be a source for my earlier observed slow executions?

Regards,
Ulf

Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

[Tom Lane]

=?UTF-8?Q?Ulf_Lohbr=C3=BCgge?=  writes:
> 2017-12-07 17:01 GMT+01:00 Tom Lane :
>> It looks like the first time such a question is asked within a session,
>> we build and cache a list of all the roles the session user is a member
>> of (directly or indirectly).  That's what's taking the time here ---
>> apparently in your test case, the "admin" role is a member of a whole lot
>> of roles?

> Yes, the user "admin" is member of more than 1k roles.

> So this cache will not invalidate during the lifetime of the session unless
> a new role is added, I guess?

It looks like any update to the role membership catalog (pg_auth_members)
invalidates that cache.  So basically a "GRANT role" or "REVOKE role"
would do it.

> Is there any locking involved when this cache gets invalidated? Could this
> be a source for my earlier observed slow executions?

This particular aspect of things doesn't seem like such a problem to me,
but it's certainly possible that there are other aspects that get
unreasonably slow when there are that many role memberships involved.
Don't see what it'd have to do with SET SEARCH_PATH, though.  Or RESET
ROLE; that doesn't require any permission checks, either.

regards, tom lane

Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade

[Laurenz Albe]

Gunther wrote:
> Something is wrong with the dump thing. And no, it's not SSL or whatever,
> I am doing it on a local system with local connections. Version 9.5 something.

That's a lot of useful information.

Try to profile where the time is spent, using "perf" or similar.

Do you connect via the network, TCP localhost or UNIX sockets?
The last option should be the fastest.

Yours,
Laurenz Albe

Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade

[Claudio Freire]

On Thu, Dec 7, 2017 at 2:31 PM, Laurenz Albe  wrote:
> Gunther wrote:
>> Something is wrong with the dump thing. And no, it's not SSL or whatever,
>> I am doing it on a local system with local connections. Version 9.5 
>> something.
>
> That's a lot of useful information.
>
> Try to profile where the time is spent, using "perf" or similar.
>
> Do you connect via the network, TCP localhost or UNIX sockets?
> The last option should be the fastest.

You can use SSL over a local TCP connection. Whether it's the case is the thing.

In my experience, SSL isn't a problem, but compression *is*. With a
modern-enough openssl, enabling compression is tough, it's forcefully
disabled by default due to the vulnerabilities that were discovered
related to its use lately.

So chances are, no matter what you configured, compression isn't being used.

I never measured it compared to earlier versions, but pg_dump is
indeed quite slow, and the biggest offender is formatting the COPY
data to be transmitted over the wire. That's why parallel dump is so
useful, you can use all your cores and achieve almost perfect
multicore acceleration.

Compression of the archive is also a big overhead, if you want
compression but want to keep the overhead to the minimum, set the
minimum compression level (1).

Something like:

pg_dump -Fd -j 8 -Z 1 -f target_dir yourdb

Learning EXPLAIN

[Flávio Henrique]

Hi experts!

I read this nice article about Understanding EXPLAIN [1] weeks ago that
opened my mind about the tool, but it seems no enough to explain a lot of
plans that I see in this list.

I often read responses to a plan that are not covered by the article.

I need/want to know EXPLAIN better.

Can you kindly advise me a good reading about advanced EXPLAIN?

Thank you!


[1] http://www.dalibo.org/_media/understanding_explain.pdf

Flávio Henrique

Table with large number of int columns, very slow COPY FROM

[Alex Tokarev]

Hi,

I have a set of tables with fairly large number of columns, mostly int with
a few bigints and short char/varchar columns. I¹ve noticed that Postgres is
pretty slow at inserting data in such a table. I tried to tune every
possible setting: using unlogged tables, increased shared_buffers, etc; even
placed the db cluster on ramfs and turned fsync off. The results are pretty
much the same with the exception of using unlogged tables that improves
performance just a little bit.

I have made a minimally reproducible test case consisting of a table with
848 columns, inserting partial dataset of 100,000 rows with 240 columns. On
my dev VM the COPY FROM operation takes just shy of 3 seconds to complete,
which is entirely unexpected for such a small dataset.

Here¹s a tarball with test schema and data:
http://nohuhu.org/copy_perf.tar.bz2; it¹s 338k compressed but expands to
~50mb. Here¹s the result of profiling session with perf:
https://pastebin.com/pjv7JqxD


-- 
Regards,
Alex.

Re: Setting effective_io_concurrency in VM?

[Mark Kirkwood]

On 28/11/17 07:40, Scott Marlowe wrote:


On Mon, Nov 27, 2017 at 11:23 AM, Don Seiler  wrote:

Good afternoon.

We run Postgres (currently 9.2, upgrading to 9.6 shortly) in VMWare ESX
machines. We currently have effective_io_concurrency set to the default of
1. I'm told that the data volume is a RAID 6 with 14 data drives and 2
parity drives. I know that RAID10 is recommended, just working with what
I've inherited for now (storage is high-end HP 3Par and HP recommended RAID
6 for best performance).

Anyway, I'm wondering if, in a virtualized environment with a VM datastore,
it makes sense to set effective_io_concurrency closer to the number of data
drives?

I'd also be interested in hearing how others have configured their
PostgreSQL instances for VMs (if there's anything special to think about).

Generally VMs are never going to be as fast as running on bare metal
etc. You can adjust it and test it with something simple like pgbench
with various settings for -c (concurrency) and see where it peaks etc
with the setting. This will at least get you into the ball park.

A while back we needed fast machines with LOTS of storage (7TB data
drives with 5TB of data on them) and the only way to stuff that many
800GB SSDs into a single machine was to use RAID-5 with a spare (I
lobbied for RAID6 but was overidden eh...) We were able to achieve
over 15k TPS in pgbench with a 400GB data store on those boxes. The
secret was to turn off the cache in the RAID controller and cranl up
effective io concurrency to something around 10 (not sure, it's been a
while).

tl;dr: Only way to know is to benchmark it. I'd guess that somewhere
between 10 and 20 is going to get the best throughput but that's just
a guess. Benchmark it and let us know!


Reasonably modern Linux hosts with Linux guests using Libvirt/KVM should 
be able to get bare metal performance for moderate numbers of cpus (<=8 
last time we benchmarked). It certainly *used* to be the case that 
virtualization sucked for databases, but not so much now.


The advice to benhmark, however - is golden :-)

Cheers

Mark

Re: Table with large number of int columns, very slow COPY FROM

[Andreas Kretschmer]

On 08.12.2017 05:21, Alex Tokarev wrote:
I have made a minimally reproducible test case consisting of a table 
with 848 columns


Such a high number of columns is maybe a sign of a wrong table / 
database design, why do you have such a lot of columns? How many indexes 
do you have?


Regards, Andreas