Re: [GENERAL] BDR, wal sender, high system cpu, mutex_lock_common

2017-10-11 Thread Craig Ringer 
On 12 October 2017 at 11:03, Andres Freund  wrote:
> On 2017-10-12 10:25:43 +0800, Craig Ringer wrote:
>> On 4 October 2017 at 00:21, milist ujang  wrote:
>> > On Tue, Oct 3, 2017 at 8:49 PM, Craig Ringer  wrote:
>> >>
>> >>
>> >> Can you get stacks please?
>> >>
>> >> Use -g
>> >
>> >
>> > # Events: 2K cpu-clock
>> > #
>> > # Overhead   Command  Shared ObjectSymbol
>> > #     .  
>> > #
>> > 86.96%  postgres  [kernel.kallsyms]  [k] __mutex_lock_common.isra.5
>> > |
>> > --- __mutex_lock_common.isra.5
>> > read
>>
>>
>> Unfortunately it looks like you're using a postgres built with
>> -fomit-frame-pointers (the default) on x64, with an older perf not
>> built with libunwind. This produces useless stacks.
>
> Just read this mail, but for libunwind to work you'd have to specify
> "--call-graph dwarf", no?

I think you're right. But only on a version of perf where it's
available and used.

I haven't recently checked if perf has finally grown the ability to
load external debug symbols either. It never used to.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


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

Re: [GENERAL] BDR, wal sender, high system cpu, mutex_lock_common

2017-10-11 Thread Andres Freund 
On 2017-10-12 10:25:43 +0800, Craig Ringer wrote:
> On 4 October 2017 at 00:21, milist ujang  wrote:
> > On Tue, Oct 3, 2017 at 8:49 PM, Craig Ringer  wrote:
> >>
> >>
> >> Can you get stacks please?
> >>
> >> Use -g
> >
> >
> > # Events: 2K cpu-clock
> > #
> > # Overhead   Command  Shared ObjectSymbol
> > #     .  
> > #
> > 86.96%  postgres  [kernel.kallsyms]  [k] __mutex_lock_common.isra.5
> > |
> > --- __mutex_lock_common.isra.5
> > read
> 
> 
> Unfortunately it looks like you're using a postgres built with
> -fomit-frame-pointers (the default) on x64, with an older perf not
> built with libunwind. This produces useless stacks.

Just read this mail, but for libunwind to work you'd have to specify
"--call-graph dwarf", no?

- Andres


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

Re: [GENERAL] BDR, wal sender, high system cpu, mutex_lock_common

2017-10-11 Thread Craig Ringer 
On 4 October 2017 at 00:21, milist ujang  wrote:
> On Tue, Oct 3, 2017 at 8:49 PM, Craig Ringer  wrote:
>>
>>
>> Can you get stacks please?
>>
>> Use -g
>
>
> # Events: 2K cpu-clock
> #
> # Overhead   Command  Shared ObjectSymbol
> #     .  
> #
> 86.96%  postgres  [kernel.kallsyms]  [k] __mutex_lock_common.isra.5
> |
> --- __mutex_lock_common.isra.5
> read


Unfortunately it looks like you're using a postgres built with
-fomit-frame-pointers (the default) on x64, with an older perf not
built with libunwind. This produces useless stacks.

You may need to recompile with -fno-omit-frame-pointer


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

Re: [GENERAL] Equivalence Classes when using IN

2017-10-11 Thread David Rowley 
On 12 October 2017 at 10:15, Kim Rose Carlsen  wrote:
> Why don't I see that predicate (customer_id) pushed into the outer nested 
> loop so we don't have to sort the whole table on each loop.
>
> (See original post and follow up for definitions)
> QUERY PLAN
> -
> Nested Loop Left Join  (cost=139.00..10392.96 rows=668 width=16) (actual 
> time=0.528..35.120 rows=200 loops=1)
>   Join Filter: (c.customer_id = product.customer_id)
>   Rows Removed by Join Filter: 199900
>   ->  Nested Loop  (cost=0.28..199.21 rows=334 width=12) (actual 
> time=0.075..1.146 rows=100 loops=1)
> ->  Seq Scan on customer  (cost=0.00..21.51 rows=334 width=8) (actual 
> time=0.067..0.282 rows=100 loops=1)
>   Filter: (age < 20)
>   Rows Removed by Filter: 901
> ->  Index Only Scan using customer_pkey on customer c  
> (cost=0.28..0.53 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=100)
>   Index Cond: (customer_id = customer.customer_id)
>   Heap Fetches: 100
>   ->  Materialize  (cost=138.73..173.75 rows=2001 width=8) (actual 
> time=0.005..0.130 rows=2001 loops=100)
> ->  Sort  (cost=138.73..143.73 rows=2001 width=8) (actual 
> time=0.448..0.588 rows=2001 loops=1)
>   Sort Key: product.customer_id, product.product_id
>   Sort Method: quicksort  Memory: 142kB
>   ->  Seq Scan on product  (cost=0.00..29.01 rows=2001 width=8) 
> (actual time=0.006..0.215 rows=2001 loops=1)
> Planning time: 0.214 ms
> Execution time: 35.284 ms

I don't really see any blockers that would mean we couldn't support
this, it's just that we don't currently support it. The predicates
that we do pushdown are just ones we deem as safe to pushdown of the
ones that appear in the query, or ones that can be derived through
equivalence. (e.g. ab.a = ab.b and ab.b = 1 --> ab.a = 1)

For example, consider the difference between the following:

create table ab(a int, b int);
insert into ab select x,x from generate_series(1,100)x;
create index on ab(a);
create index on ab(b);

postgres=# explain select * from (select distinct on (a) a,b from ab
order by a,b) ab where ab.b < 10;
QUERY PLAN
---
 Subquery Scan on ab  (cost=127757.34..145257.34 rows=33 width=8)
   Filter: (ab.b < 10)
   ->  Unique  (cost=127757.34..132757.34 rows=100 width=8)
 ->  Sort  (cost=127757.34..130257.34 rows=100 width=8)
   Sort Key: ab_1.a, ab_1.b
   ->  Seq Scan on ab ab_1  (cost=0.00..14425.00
rows=100 width=8)
(6 rows)


postgres=# explain select * from (select distinct on (a) a,b from ab
order by a,b) ab where ab.a < 10;
  QUERY PLAN
---
 Unique  (cost=8.73..8.77 rows=9 width=8)
   ->  Sort  (cost=8.73..8.75 rows=9 width=8)
 Sort Key: ab.a, ab.b
 ->  Index Scan using ab_a_idx on ab  (cost=0.42..8.58 rows=9 width=8)
   Index Cond: (a < 10)
(5 rows)

The "a < 10" was pushed down as we're distinct on (a), but pushing
down "ab.b < 10" would be invalid and could cause wrong results.

The predicate you'd like to see pushed down is actually a parameter in
a parameterized Path and we don't currently generate any parameterized
paths outside of each query level. Likely there's no good reason for
this other than it's not been done yet, but it's really only been
since 9.6 that the query planner has been flexible enough to possibly
allow something like this to be done at all.

The reason the planner may appear to push down the predicate when
there's no DISTINCT ON clause is that the planner was able to pull the
subquery (or view) up a level. When the planner is able to do this
it's much more flexible to the types of plans it can generate. It's
just that we don't ever pull up subqueries with DISTINCT ON, plus a
bunch of other reasons.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


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

Re: [GENERAL] Equivalence Classes when using IN

2017-10-11 Thread Kim Rose Carlsen 

> On 11 Oct 2017, at 21.46, David Rowley  wrote:
> 
>> On 12 October 2017 at 08:37, Kim Rose Carlsen  wrote:
>> 
>>> Yeah.  The ORDER BY creates a partial optimization fence, preventing
>>> any such plan from being considered.
 
>> 
>> I can see in the general case it semanticly means different things If you 
>> allow the WHERE to pass through ORDER BY.
>> 
>> A special case can be allowed for WHERE to pass the ORDER BY if the column 
>> is part of DISTINCT ON.
> 
> Yeah, we do allow predicates to be pushed down in that case.
> 

Let's ignore that it's not a very useful query I have written. 

Why don't I see that predicate (customer_id) pushed into the outer nested loop 
so we don't have to sort the whole table on each loop. 

(See original post and follow up for definitions) 
QUERY PLAN  

-
Nested Loop Left Join  (cost=139.00..10392.96 rows=668 width=16) (actual 
time=0.528..35.120 rows=200 loops=1)
  Join Filter: (c.customer_id = product.customer_id)
  Rows Removed by Join Filter: 199900
  ->  Nested Loop  (cost=0.28..199.21 rows=334 width=12) (actual 
time=0.075..1.146 rows=100 loops=1)
->  Seq Scan on customer  (cost=0.00..21.51 rows=334 width=8) (actual 
time=0.067..0.282 rows=100 loops=1)
  Filter: (age < 20)
  Rows Removed by Filter: 901
->  Index Only Scan using customer_pkey on customer c  (cost=0.28..0.53 
rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=100)
  Index Cond: (customer_id = customer.customer_id)
  Heap Fetches: 100
  ->  Materialize  (cost=138.73..173.75 rows=2001 width=8) (actual 
time=0.005..0.130 rows=2001 loops=100)
->  Sort  (cost=138.73..143.73 rows=2001 width=8) (actual 
time=0.448..0.588 rows=2001 loops=1)
  Sort Key: product.customer_id, product.product_id
  Sort Method: quicksort  Memory: 142kB
  ->  Seq Scan on product  (cost=0.00..29.01 rows=2001 width=8) 
(actual time=0.006..0.215 rows=2001 loops=1)
Planning time: 0.214 ms
Execution time: 35.284 ms

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

Re: [GENERAL] Equivalence Classes when using IN

2017-10-11 Thread David Rowley 
On 12 October 2017 at 08:37, Kim Rose Carlsen  wrote:
>
>> Yeah.  The ORDER BY creates a partial optimization fence, preventing
>> any such plan from being considered.
>>>
>
> I can see in the general case it semanticly means different things If you 
> allow the WHERE to pass through ORDER BY.
>
> A special case can be allowed for WHERE to pass the ORDER BY if the column is 
> part of DISTINCT ON.

Yeah, we do allow predicates to be pushed down in that case.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


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

Re: [GENERAL] Equivalence Classes when using IN

2017-10-11 Thread Kim Rose Carlsen 

> Yeah.  The ORDER BY creates a partial optimization fence, preventing
> any such plan from being considered.
>> 

I can see in the general case it semanticly means different things If you allow 
the WHERE to pass through ORDER BY. 

A special case can be allowed for WHERE to pass the ORDER BY if the column is 
part of DISTINCT ON.




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

Re: [GENERAL] Making subscribers read only in Postgres 10 logical replication

2017-10-11 Thread rverghese 
You mean at the user permissions level? Yes, I could, but would mean doing so
table by table, which is not our current structure. I guess there is nothing
at the database level.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


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

Re: [GENERAL] Making subscribers read only in Postgres 10 logical replication

2017-10-11 Thread Joshua D. Drake 

On 10/11/2017 11:18 AM, rverghese wrote:

Hi
I'm testing out logical replication on PostgreSQL 10. Is there a setting to
make subscribers read-only slaves like with Slony. Currently I can insert
into the Publisher and the Subscriber. If there is a conflict, i.e. same
record exists in both, then all replication gets backed up (even to other
tables) till that one record is resolved.


GRANT?

JD



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL Centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://pgconf.us
* Unless otherwise stated, opinions are my own.   *


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

[GENERAL] Making subscribers read only in Postgres 10 logical replication

2017-10-11 Thread rverghese 
Hi
I'm testing out logical replication on PostgreSQL 10. Is there a setting to
make subscribers read-only slaves like with Slony. Currently I can insert
into the Publisher and the Subscriber. If there is a conflict, i.e. same
record exists in both, then all replication gets backed up (even to other
tables) till that one record is resolved.

Thanks
RV



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


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

Re: [GENERAL] Why does increasing the precision of a numeric column rewrites the table?

2017-10-11 Thread Tom Lane 
Thomas Kellerer  writes:
> I don't understand why going from numeric(12,2) to numeric(15,3) would 
> require a table rewrite. 

The comment for numeric_transform explains this:

 * Flatten calls to numeric's length coercion function that solely represent
 * increases in allowable precision.  Scale changes mutate every datum, so
 * they are unoptimizable.  Some values, e.g. 1E-1001, can only fit into an
 * unconstrained numeric, so a change from an unconstrained numeric to any
 * constrained numeric is also unoptimizable.

The issue is basically that changing '1.00' to '1.000' requires a change
in the actually-stored value.

regards, tom lane


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

Re: [GENERAL] startup process stuck in recovery

2017-10-11 Thread Tom Lane 
Simon Riggs  writes:
> On 11 October 2017 at 08:09, Christophe Pettus  wrote:
>> While it's certainly true that this was an extreme case, it was a real-life 
>> production situation.  The concern here is that in the actual production 
>> situation, the only symptom was that the startup process just stopped.  
>> There were no log messages or any other indication of what was going wrong.

> Which indicates it was making progress, just slowly.

> Tom says "This is pretty easy to diagnose though
> because it spews "out of shared memory" WARNING messages to the
> postmaster log at an astonishing rate"

> These don't seem to match.

Yeah.  I'm still suspicious that Christophe saw some other misbehavior
than the one I found.  We know his server was dealing with < 10K locks,
which doesn't seem like enough to cause any obvious problem from a mere
O(N^2) behavior.

regards, tom lane


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

Re: [GENERAL] Determine size of table before it's committed?

2017-10-11 Thread Melvin Davidson 
On Wed, Oct 11, 2017 at 9:43 AM, Seamus Abshere  wrote:

> hi,
>
> I've had an `INSERT INTO x SELECT FROM [...]` query running for more
> then 2 days.
>
> Is there a way to see how big x has gotten? Even a very rough estimate
> (off by a gigabyte) would be fine.
>
> Best,
> Seamus
>
>
> --
> Seamus Abshere, SCEA
> https://www.faraday.io
> https://github.com/seamusabshere
> https://linkedin.com/in/seamusabshere
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

>Is there a way to see how big x has gotten?...

Try:

SELECT n.nspname as schema,
   c.relname as table,
   a.rolname as owner,
   c.relfilenode as filename,
   c.reltuples::bigint,
   pg_size_pretty(pg_relation_size(n.nspname|| '.' || c.relname)) as
size,
   pg_size_pretty(pg_total_relation_size(n.nspname|| '.' || c.relname))
as total_size,
   pg_relation_size(n.nspname|| '.' || c.relname) as size_bytes,
   pg_total_relation_size(n.nspname|| '.' || c.relname) as
total_size_bytes,
   CASE WHEN c.reltablespace = 0
THEN 'pg_default'
ELSE (SELECT t.spcname
FROM pg_tablespace t WHERE (t.oid = c.reltablespace)
)
END as tablespace
FROM pg_class c
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
  JOIN pg_authid a ON ( a.oid = c.relowner )
  WHERE relname = 'x'
ORDER BY total_size_bytes DESC, 1, 2;

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Determine size of table before it's committed?

2017-10-11 Thread Justin Pryzby 
On Wed, Oct 11, 2017 at 10:43:26AM -0300, Seamus Abshere wrote:
> I've had an `INSERT INTO x SELECT FROM [...]` query running for more
> then 2 days.
> 
> Is there a way to see how big x has gotten? Even a very rough estimate
> (off by a gigabyte) would be fine.

On linux:

Run ps -fu postgres (or SELECT pid, query FROM pg_stat_activity) and
look at: ls -l /proc/PID/fd

writing to XX.22 means it's written ~22GB.

You can also SELECT relfilenode FROM pg_class WHERE oid='x'::regclass (or
relname='x').

Or try using strace (but beware I've seen its interruption to syscalls change
the behavior of the program being straced).

Justin


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

[GENERAL] Determine size of table before it's committed?

2017-10-11 Thread Seamus Abshere 
hi,

I've had an `INSERT INTO x SELECT FROM [...]` query running for more
then 2 days.

Is there a way to see how big x has gotten? Even a very rough estimate
(off by a gigabyte) would be fine.

Best,
Seamus


--
Seamus Abshere, SCEA
https://www.faraday.io
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere


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

Re: [GENERAL] startup process stuck in recovery

2017-10-11 Thread Simon Riggs 
On 11 October 2017 at 08:09, Christophe Pettus  wrote:
>
>> On Oct 10, 2017, at 23:54, Simon Riggs  wrote:
>>
>> The use case described seems incredibly
>> unreal and certainly amenable to being rewritten.
>
> While it's certainly true that this was an extreme case, it was a real-life 
> production situation.  The concern here is that in the actual production 
> situation, the only symptom was that the startup process just stopped.  There 
> were no log messages or any other indication of what was going wrong.

Which indicates it was making progress, just slowly.

Tom says "This is pretty easy to diagnose though
because it spews "out of shared memory" WARNING messages to the
postmaster log at an astonishing rate"

These don't seem to match.

-- 
Simon Riggshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

[GENERAL] Why does increasing the precision of a numeric column rewrites the table?

2017-10-11 Thread Thomas Kellerer 
When increasing the length constraint on a varchar column, Postgres is smart 
enough to not rewrite the table. 

I expected the same thing to be true when increasing the size of a numeric 
column.

However this does not seem to be the case:

Consider the following table:

create table foo 
(
  some_number numeric(12,2)
);


The following statement returns "immediately", regardless of the number of rows 
in the table

alter table foo alter column some_number numeric(15,2);

However, when running (on the original table definition)

alter table foo alter column some_number numeric(15,3);

it takes quite a while (depending on the number of rows) which indicates a 
table rewrite is taking place. 

I don't understand why going from numeric(12,2) to numeric(15,3) would require 
a table rewrite. 

Thomas



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

Re: [GENERAL] startup process stuck in recovery

2017-10-11 Thread Christophe Pettus 

> On Oct 10, 2017, at 23:54, Simon Riggs  wrote:
> 
> The use case described seems incredibly
> unreal and certainly amenable to being rewritten.

While it's certainly true that this was an extreme case, it was a real-life 
production situation.  The concern here is that in the actual production 
situation, the only symptom was that the startup process just stopped.  There 
were no log messages or any other indication of what was going wrong.
--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [GENERAL] startup process stuck in recovery

2017-10-11 Thread Simon Riggs 
On 10 October 2017 at 21:23, Tom Lane  wrote:

> What I see is that, given this particular test case, the backend
> process on the master never holds more than a few locks at a time.
> Each time we abort a subtransaction, the AE lock it was holding
> on the temp table it created gets dropped.  However ... on the
> standby server, pre v10, the replay process attempts to take all
> 12000 of those AE locks at once.  This is not a great plan.

Standby doesn't take locks "at once", they are added just as they
arrive. The locks are held by topxid, so not released at subxid abort,
by design, so they are held concurrently.

> v10 and HEAD avoid the problem because the standby server doesn't
> take locks (any at all, AFAICS).  I suppose this must be a
> consequence of commit 9b013dc238c, though I'm not sure exactly how.

Locks are still taken, but in 9b013dc238c we just avoid trying to
release locks when transactions don't have any.

> Anyway, it's pretty scary that it's so easy to run the replay process
> out of shared memory pre-v10.  I wonder if we should consider
> backpatching that fix.  Any situation where the replay process takes
> more locks concurrently than were ever held on the master is surely
> very bad news.

v10 improves on this specific point because we perform lock release at
subxid abort.

Various cases have been reported over time and this has been improving
steadily in each release.

It isn't "easy" to run the replay process out of memory because
clearly that doesn't happen much, but yes there are some pessimal use
cases that don't work well. The use case described seems incredibly
unreal and certainly amenable to being rewritten.

Backpatching some of those fixes is quite risky, IMHO.

-- 
Simon Riggshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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