Dropped User Session

[Vishal Kohli]

Hello,

Is there a view in database to find sessions PID of dropped user in
database?

Thanks,
Vishal

Re: Dropped User Session

[Vishal Kohli]

I dropped a connected user in database, cannot find pid in
pg_stat_activity. I can query from dropped user session but how to find
session in database without going into OS.

On Fri, 30 Nov 2018 at 5:08 PM, Vishal Kohli 
wrote:

> Hello,
>
> Is there a view in database to find sessions PID of dropped user in
> database?
>
> Thanks,
> Vishal
>
>
>

Re: surprising query optimisation

[Chris Withers]

On 28/11/2018 22:49, Stephen Frost wrote:

* Chris Withers (ch...@withers.org) wrote:

We have an app that deals with a lot of queries, and we've been slowly
seeing performance issues emerge. We take a lot of free form queries from
users and stumbled upon a very surprising optimisation.

So, we have a 'state' column which is a 3 character string column with an
index on it. Despite being a string, this column is only used to store one
of three values: 'NEW', 'ACK', or 'RSV'.


Sounds like a horrible field to have an index on.


That's counter-intuitive for me. What leads you to say this and what 
would you do/recommend instead?



Really though, if you want something more than wild speculation, posting
the 'explain analyze' of each query along with the actual table
definitions and sizes and such would be the best way to get it.


table definition:

# \d alerts_alert
  Table "public.alerts_alert"
 Column  |   Type   | Modifiers
-+--+---
 tags| jsonb| not null
 id  | character varying(86)| not null
 earliest_seen   | timestamp with time zone | not null
 latest_seen | timestamp with time zone | not null
 created | timestamp with time zone | not null
 modified| timestamp with time zone | not null
 type| character varying(300)   | not null
 state   | character varying(3) | not null
 until   | timestamp with time zone |
 latest_note | text | not null
 created_by_id   | integer  | not null
 modified_by_id  | integer  | not null
 owner_id| integer  |
 owning_group_id | integer  | not null
 latest_new  | timestamp with time zone | not null
Indexes:
"alerts_alert_pkey" PRIMARY KEY, btree (id)
"alert_tags_index" gin (tags)
"alerts_alert_1efacf1d" btree (latest_seen)
"alerts_alert_3103a7d8" btree (until)
"alerts_alert_599dcce2" btree (type)
"alerts_alert_5e7b1936" btree (owner_id)
"alerts_alert_9ae73c65" btree (modified)
"alerts_alert_9ed39e2e" btree (state)
"alerts_alert_b3da0983" btree (modified_by_id)
"alerts_alert_c5151f5a" btree (earliest_seen)
"alerts_alert_e2fa5388" btree (created)
"alerts_alert_e93cb7eb" btree (created_by_id)
"alerts_alert_efea2d76" btree (owning_group_id)
"alerts_alert_id_13155e16_like" btree (id varchar_pattern_ops)
"alerts_alert_latest_new_e8d1fbde_uniq" btree (latest_new)
"alerts_alert_state_90ab480b_like" btree (state varchar_pattern_ops)
"alerts_alert_type_3021f46f_like" btree (type varchar_pattern_ops)
Foreign-key constraints:
"alerts_alert_created_by_id_520608c0_fk_alerts_user_id" FOREIGN KEY 
(created_by_id) REFERENCES alerts_user(id) DEFERRABLE INITIALLY DEFERRED
"alerts_alert_modified_by_id_6db4b04b_fk_alerts_user_id" FOREIGN 
KEY (modified_by_id) REFERENCES alerts_user(id) DEFERRABLE INITIALLY 
DEFERRED
"alerts_alert_owner_id_0c00548a_fk_alerts_user_id" FOREIGN KEY 
(owner_id) REFERENCES alerts_user(id) DEFERRABLE INITIALLY DEFERRED
"alerts_alert_owning_group_id_a4869b66_fk_auth_group_id" FOREIGN 
KEY (owning_group_id) REFERENCES auth_group(id) DEFERRABLE INITIALLY 
DEFERRED

Referenced by:
TABLE "alerts_alertevent" CONSTRAINT 
"alerts_alertevent_alert_id_edd734b8_fk_alerts_alert_id" FOREIGN KEY 
(alert_id) REFERENCES alerts_alert(id) DEFERRABLE INITIALLY DEFERRED


Row counts by state:

# select state, count(*) from alerts_alert group by 1 order by 1;
 state |  count
---+-
 ACK   |1053
 NEW   |1958
 RSV   | 1528623
(3 rows)

here's an example of the "bad" query plan:
https://explain.depesz.com/s/cDkp

here's an example with all the "state!='RSV'" clauses rewritten as I 
described:

https://explain.depesz.com/s/B9Xi


I'd suggest you check out the wiki article written about this kind of
question:

https://wiki.postgresql.org/wiki/Slow_Query_Questions


Thanks!

Chris

Re: surprising query optimisation

[Stephen Frost]

Greetings,

On Fri, Nov 30, 2018 at 07:52 Chris Withers  wrote:

> On 28/11/2018 22:49, Stephen Frost wrote:
> > * Chris Withers (ch...@withers.org) wrote:
> >> We have an app that deals with a lot of queries, and we've been slowly
> >> seeing performance issues emerge. We take a lot of free form queries
> from
> >> users and stumbled upon a very surprising optimisation.
> >>
> >> So, we have a 'state' column which is a 3 character string column with
> an
> >> index on it. Despite being a string, this column is only used to store
> one
> >> of three values: 'NEW', 'ACK', or 'RSV'.
> >
> > Sounds like a horrible field to have an index on.
>
> That's counter-intuitive for me. What leads you to say this and what
> would you do/recommend instead?
>
> > Really though, if you want something more than wild speculation, posting
> > the 'explain analyze' of each query along with the actual table
> > definitions and sizes and such would be the best way to get it.
>
> table definition:
>
> # \d alerts_alert
>Table "public.alerts_alert"
>   Column  |   Type   | Modifiers
> -+--+---
>   tags| jsonb| not null
>   id  | character varying(86)| not null
>   earliest_seen   | timestamp with time zone | not null
>   latest_seen | timestamp with time zone | not null
>   created | timestamp with time zone | not null
>   modified| timestamp with time zone | not null
>   type| character varying(300)   | not null
>   state   | character varying(3) | not null
>   until   | timestamp with time zone |
>   latest_note | text | not null
>   created_by_id   | integer  | not null
>   modified_by_id  | integer  | not null
>   owner_id| integer  |
>   owning_group_id | integer  | not null
>   latest_new  | timestamp with time zone | not null
> Indexes:
>  "alerts_alert_pkey" PRIMARY KEY, btree (id)
>  "alert_tags_index" gin (tags)
>  "alerts_alert_1efacf1d" btree (latest_seen)
>  "alerts_alert_3103a7d8" btree (until)
>  "alerts_alert_599dcce2" btree (type)
>  "alerts_alert_5e7b1936" btree (owner_id)
>  "alerts_alert_9ae73c65" btree (modified)
>  "alerts_alert_9ed39e2e" btree (state)
>  "alerts_alert_b3da0983" btree (modified_by_id)
>  "alerts_alert_c5151f5a" btree (earliest_seen)
>  "alerts_alert_e2fa5388" btree (created)
>  "alerts_alert_e93cb7eb" btree (created_by_id)
>  "alerts_alert_efea2d76" btree (owning_group_id)
>  "alerts_alert_id_13155e16_like" btree (id varchar_pattern_ops)
>  "alerts_alert_latest_new_e8d1fbde_uniq" btree (latest_new)
>  "alerts_alert_state_90ab480b_like" btree (state varchar_pattern_ops)
>  "alerts_alert_type_3021f46f_like" btree (type varchar_pattern_ops)
> Foreign-key constraints:
>  "alerts_alert_created_by_id_520608c0_fk_alerts_user_id" FOREIGN KEY
> (created_by_id) REFERENCES alerts_user(id) DEFERRABLE INITIALLY DEFERRED
>  "alerts_alert_modified_by_id_6db4b04b_fk_alerts_user_id" FOREIGN
> KEY (modified_by_id) REFERENCES alerts_user(id) DEFERRABLE INITIALLY
> DEFERRED
>  "alerts_alert_owner_id_0c00548a_fk_alerts_user_id" FOREIGN KEY
> (owner_id) REFERENCES alerts_user(id) DEFERRABLE INITIALLY DEFERRED
>  "alerts_alert_owning_group_id_a4869b66_fk_auth_group_id" FOREIGN
> KEY (owning_group_id) REFERENCES auth_group(id) DEFERRABLE INITIALLY
> DEFERRED
> Referenced by:
>  TABLE "alerts_alertevent" CONSTRAINT
> "alerts_alertevent_alert_id_edd734b8_fk_alerts_alert_id" FOREIGN KEY
> (alert_id) REFERENCES alerts_alert(id) DEFERRABLE INITIALLY DEFERRED
>
> Row counts by state:
>
> # select state, count(*) from alerts_alert group by 1 order by 1;
>   state |  count
> ---+-
>   ACK   |1053
>   NEW   |1958
>   RSV   | 1528623
> (3 rows)
>
> here's an example of the "bad" query plan:
> https://explain.depesz.com/s/cDkp
>
> here's an example with all the "state!='RSV'" clauses rewritten as I
> described:
> https://explain.depesz.com/s/B9Xi
>
> > I'd suggest you check out the wiki article written about this kind of
> > question:
> >
> > https://wiki.postgresql.org/wiki/Slow_Query_Questions


Have you tried a partial index on state!=‘RSV’?

Thanks,

Stephen

> 
>

Re: surprising query optimisation

[Chris Withers]

On 30/11/2018 12:55, Stephen Frost wrote:

 > I'd suggest you check out the wiki article written about this kind of
 > question:
 >
 > https://wiki.postgresql.org/wiki/Slow_Query_Questions


Have you tried a partial index on state!=‘RSV’?


The solution I originally posted, that we do easily enough at our query 
generation layer, is working perfectly, but this is good to know for 
next time.


My post here is mainly to try and understand what's going on so I can 
improve my general feel for how to use postgres at it's best.


So, why was the query ending up being a big scan rather than some quick 
lookups based on the index?


cheers,

Chris

Re: surprising query optimisation

[Stephen Frost]

Greetings,

On Fri, Nov 30, 2018 at 08:00 Chris Withers  wrote:

> On 30/11/2018 12:55, Stephen Frost wrote:
> >  > I'd suggest you check out the wiki article written about this
> kind of
> >  > question:
> >  >
> >  > https://wiki.postgresql.org/wiki/Slow_Query_Questions
> >
> >
> > Have you tried a partial index on state!=‘RSV’?
>
> The solution I originally posted, that we do easily enough at our query
> generation layer, is working perfectly, but this is good to know for
> next time.
>
> My post here is mainly to try and understand what's going on so I can
> improve my general feel for how to use postgres at it's best.
>
> So, why was the query ending up being a big scan rather than some quick
> lookups based on the index?


Thought that was mentioned already but at least part of the issue is that
PG can’t just search for the other values when it’s a != in the index
because it wouldn’t know what values to search for...  PG doesn’t know,
with complete certainty, that there’s only 3 values.

The partial index is something you should want anyway- that index won’t
ever be used to search for RSV because it’s cheaper to just scan the table
if we are looking for those, and the index will be much, much smaller
without that common value being included.

Thanks!

Stephen

>

Log level of logical decoding

[Francesco Nidito]

Hi All,

With reference to an old thread 
(https://www.postgresql.org/message-id/8307.1419439826%40sss.pgh.pa.us), our 
application polls the database instance periodically to check if there are new 
transaction in the WAL waiting to be processed.  As a consequence of this, our 
log file is full of messages like

2018-11-30 10:37:29 UTC LOG:  starting logical decoding for slot "test_slot"
2018-11-30 10:37:29 UTC DETAIL:  streaming transactions committing after 
4AC/41D5F950, reading WAL from 4AC/41C16868
2018-11-30 10:37:29 UTC STATEMENT:
SELECT * FROM pg_logical_slot_peek_changes('test_slot', NULL, NULL,
  'include-timestamp', '1', 'skip-empty-xacts', '1')

As those logs are of dubious utility, and following the comment of Tom in the 
last email of the old thread, I would like to ask why this message is a LOG and 
not a DEBUG and if there is any plan to make them DEBUG?

Thanks a lot in advance!

Francesco

Re: surprising query optimisation

[Stephen Frost]

Greetings,

* Chris Withers (ch...@withers.org) wrote:
> On 28/11/2018 22:49, Stephen Frost wrote:
> >* Chris Withers (ch...@withers.org) wrote:
> >>We have an app that deals with a lot of queries, and we've been slowly
> >>seeing performance issues emerge. We take a lot of free form queries from
> >>users and stumbled upon a very surprising optimisation.
> >>
> >>So, we have a 'state' column which is a 3 character string column with an
> >>index on it. Despite being a string, this column is only used to store one
> >>of three values: 'NEW', 'ACK', or 'RSV'.
> >
> >Sounds like a horrible field to have an index on.
> 
> That's counter-intuitive for me. What leads you to say this and what would
> you do/recommend instead?

For this, specifically, it's because you end up with exactly what you
have: a large index with tons of duplicate values.  Indexes are
particularly good when you have high-cardinality fields.  Now, if you
have a skewed index, where there's one popular value and a few much less
popular ones, then that's where you really want a partial index (as I
suggest earlier) so that queries against the non-popular value(s) is
able to use the index and the index is much smaller.

Of course, for this to work you need to set up the partial index
correctly and make sure that your queries end up using it.

Thanks!

Stephen


signature.asc
Description: PGP signature

psql is hanging

[John Smith]

We have a long script of sql that we run, several thousand lines of sql. If
I execute the script from start to finish, somewhere in the middle of it,
one sql command will hang and take 2 to 3 hours. During this time, "htop"
shows 100% cpu usage with a postgresql process, so it appears to be doing
something.

If I stop that command, then immediately rerun the rest of the script
starting from the command that "hung", the "hung" command then completes in
5 seconds and the rest of the script just continues on perfectly. I can
interrupt the command within 30 mins of hanging, or 2 hours of hanging;
regardless of when I interrrupt the hung command, it always immediately
completes successully in < 5 seconds when I rerun the script immediately
after that.

I have noticed this in multiple scripts, multiple commands; one was an
"insert into", a different script hung on a "update" and a third one would
hang on a "create table x as (select ... )". These are all short running
commands normally, and we have indexes on all the columns for the
appropriate "where" clauses.

I can't send the sql (forbidden), but the actual command does not appear to
be the issue. Rerunning the same little section of sql manually which
includes the hung command, always takes less than a minute, but the entire
sql which should only take 30 minutes in total will always hang at the same
spot; and then interrupting it (regardless of how long it was running) and
rerunning immediately completes.

The "hung" command does eventually finish at times after 2 to 3 hours, most
often we can't afford to wait for it to run since this batch of sql has to
run daily.

Some other notes:
- Environment is Ubuntu 16.04; postgresql version was originaly 9.5(?) but
we have upgraded to 11.1 using postgresql packages for ubuntu and the
problem has persisted across postgresql versions.
- There are no other queries running on the postgresql server during this
time, and server load is idling (other than whatever psql is doing).
- It is an amazon server, with ZFS as the files system, it has 50% disk
free on this storage partition that postgresql database is on.
- We have 60 gigs of ram total, 8 cores.
- We have run the entire script using "create unlogged table" everywhere as
well, no difference.
- We have made tunings to the postgresql configuration to make it use more
RAM.
- All sql commands are sent through a custom Java application; so we use
java + JDBC to connect to postgresql for this. The script has custom
commands which are interpreted by the Java app, so we can't test running
the exact script start to finish in psql native tools since the native
tools won't understand our custom sql. However the java app is very simple.
All individual sql commands are committed immediately after they run so
there is no long transaction in play here. The psql jdbc driver is
"postgresql-42.2.4".
- One may suspect the custom java app; but again, htop shows postgresql
using 100% CPU usage on one core, so postgresql is chewing on the command,
doing something.

I have attached our current postgresql.conf file.

We're kind of pulling out our hair here, any ideas?

Thanks!
<>

Re: psql is hanging

[Adrian Klaver]

On 11/30/18 7:53 AM, John Smith wrote:
We have a long script of sql that we run, several thousand lines of sql. 
If I execute the script from start to finish, somewhere in the middle of 
it, one sql command will hang and take 2 to 3 hours. During this time, 
"htop" shows 100% cpu usage with a postgresql process, so it appears to 
be doing something.


First, a naming note. psql is the name of the Postgres command line 
client. What you are actually referring to is the Postgres server.


Second, without the actual commands solving this is going to be 
difficult to impossible.


That being said I would suspect some sort of locking issue. I would 
start with pg_stat_activity:


https://www.postgresql.org/docs/10/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

See what it says when you are in the 'hung' phase.



If I stop that command, then immediately rerun the rest of the script 
starting from the command that "hung", the "hung" command then completes 
in 5 seconds and the rest of the script just continues on perfectly. I 
can interrupt the command within 30 mins of hanging, or 2 hours of 
hanging; regardless of when I interrrupt the hung command, it always 
immediately completes successully in < 5 seconds when I rerun the script 
immediately after that.


I have noticed this in multiple scripts, multiple commands; one was an 
"insert into", a different script hung on a "update" and a third one 
would hang on a "create table x as (select ... )". These are all short 
running commands normally, and we have indexes on all the columns for 
the appropriate "where" clauses.


I can't send the sql (forbidden), but the actual command does not appear 
to be the issue. Rerunning the same little section of sql manually which 
includes the hung command, always takes less than a minute, but the 
entire sql which should only take 30 minutes in total will always hang 
at the same spot; and then interrupting it (regardless of how long it 
was running) and rerunning immediately completes.


The "hung" command does eventually finish at times after 2 to 3 hours, 
most often we can't afford to wait for it to run since this batch of sql 
has to run daily.


Some other notes:
- Environment is Ubuntu 16.04; postgresql version was originaly 9.5(?) 
but we have upgraded to 11.1 using postgresql packages for ubuntu and 
the problem has persisted across postgresql versions.
- There are no other queries running on the postgresql server during 
this time, and server load is idling (other than whatever psql is doing).
- It is an amazon server, with ZFS as the files system, it has 50% disk 
free on this storage partition that postgresql database is on.

- We have 60 gigs of ram total, 8 cores.
- We have run the entire script using "create unlogged table" everywhere 
as well, no difference.
- We have made tunings to the postgresql configuration to make it use 
more RAM.
- All sql commands are sent through a custom Java application; so we use 
java + JDBC to connect to postgresql for this. The script has custom 
commands which are interpreted by the Java app, so we can't test running 
the exact script start to finish in psql native tools since the native 
tools won't understand our custom sql. However the java app is very 
simple. All individual sql commands are committed immediately after they 
run so there is no long transaction in play here. The psql jdbc driver 
is "postgresql-42.2.4".
- One may suspect the custom java app; but again, htop shows postgresql 
using 100% CPU usage on one core, so postgresql is chewing on the 
command, doing something.


I have attached our current postgresql.conf file.

We're kind of pulling out our hair here, any ideas?

Thanks!



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: psql is hanging

[Chris Mair]

We're kind of pulling out our hair here, any ideas?


You might try issuing the command

  analyze;

right *before* the command that hangs.

The rationale behind this idea is that your script changed data
and the "hung" command uses a wrong plan based on outdated statistics.
By the time you run it manually it would be fast again, because in the
mean time the statistics have been updated automatically.

Analyze forces an immediate update of the statistics on the whole
database.

This can by itself take some time according to size of the
database. If my idea works, you can then further optimize by
doing analyze only on the changed tables.

Bye,
Chris.

Re: psql is hanging

[Chris Mair]

We're kind of pulling out our hair here, any ideas?


You might try issuing the command

   analyze;

right *before* the command that hangs.


Clarification: I meant to say: "put it into the script at the location right before 
the command, that hangs".

Bye,
Chris.

Re: psql is hanging

[Ron]

On 11/30/2018 09:53 AM, John Smith wrote:
We have a long script of sql that we run, several thousand lines of sql. 
If I execute the script from start to finish, somewhere in the middle of 
it, one sql command will hang and take 2 to 3 hours. During this time, 
"htop" shows 100% cpu usage with a postgresql process, so it appears to be 
doing something.


If I stop that command, then immediately rerun the rest of the script 
starting from the command that "hung", the "hung" command then completes 
in 5 seconds and the rest of the script just continues on perfectly. I can 
interrupt the command within 30 mins of hanging, or 2 hours of hanging; 
regardless of when I interrrupt the hung command, it always immediately 
completes successully in < 5 seconds when I rerun the script immediately 
after that.


I have noticed this in multiple scripts, multiple commands; one was an 
"insert into", a different script hung on a "update" and a third one would 
hang on a "create table x as (select ... )". These are all short running 
commands normally, and we have indexes on all the columns for the 
appropriate "where" clauses.


I can't send the sql (forbidden), but the actual command does not appear 
to be the issue. Rerunning the same little section of sql manually which 
includes the hung command, always takes less than a minute, but the entire 
sql which should only take 30 minutes in total will always hang at the 
same spot; and then interrupting it (regardless of how long it was 
running) and rerunning immediately completes.


The "hung" command does eventually finish at times after 2 to 3 hours, 
most often we can't afford to wait for it to run since this batch of sql 
has to run daily.


Some other notes:
- Environment is Ubuntu 16.04; postgresql version was originaly 9.5(?) but 
we have upgraded to 11.1 using postgresql packages for ubuntu and the 
problem has persisted across postgresql versions.
- There are no other queries running on the postgresql server during this 
time, and server load is idling (other than whatever psql is doing).
- It is an amazon server, with ZFS as the files system, it has 50% disk 
free on this storage partition that postgresql database is on.

- We have 60 gigs of ram total, 8 cores.
- We have run the entire script using "create unlogged table" everywhere 
as well, no difference.
- We have made tunings to the postgresql configuration to make it use more 
RAM.
- All sql commands are sent through a custom Java application; so *we use 
java + JDBC to connect to postgresql* for this. The script has custom 
commands which are interpreted by the Java app, so we can't test running 
the exact script start to finish in psql native tools since the native 
tools won't understand our custom sql. However the java app is very 
simple. All individual sql commands are committed immediately after they 
run so there is no long transaction in play here. The psql jdbc driver is 
"postgresql-42.2.4".
- One may suspect the custom java app; but again, htop shows postgresql 
using 100% CPU usage on one core, so postgresql is chewing on the command, 
doing something.


I have attached our current postgresql.conf file.

We're kind of pulling out our hair here, any ideas?


We have a seemingly identical problem in v9.6 on our big prod databases.  It 
worked perfectly in 8.4 *and works perfectly in 9.6 our small staging servers*.


A giant sql script that drops the oldest child tables from a large set of 
partitioned tables (by dropping all relevant triggers, indexes, functions 
etc, dropping the oldest children, adding the new children then recreating 
all the relevant triggers, indexes, functions etc).


In 9.6 It works perfectly when I run the script directly from psql.

--
Angular momentum makes the world go 'round.

Re: psql is hanging

[Steve Crawford]

On Fri, Nov 30, 2018 at 8:05 AM Chris Mair  wrote:

>
> > We're kind of pulling out our hair here, any ideas?
>
> You might try issuing the command
>
>analyze;
>
> right *before* the command that hangs.
>
>
You might consider trying the "auto_explain" module (
https://www.postgresql.org/docs/current/auto-explain.html ). This will let
you "trap" the query plan used for the long-running query. Then compare
that query plan with a manually run explain when it runs quickly to see if
they differ. If they do, it suggests that bad statistics are a likely
culprit and Chris' suggestion of running analyze will help.

You only need to analyze those tables used in the query and, most likely,
only tables that were substantially changed within a moderately short
period prior to the start of the query.

Autovacuum, which will handles analyze as well, typically defaults to
checking for tables that need attention every minute so for processes that
have a lot of steps it becomes "luck of the draw" whether or not a needed
analyze is run after a substantial table change and before that table is
used.

We frequently put specific "analyze" statements in such scripts immediately
following bulk-update statements.

Cheers,
Steve

Re: psql is hanging

[Tom Lane]

Ron  writes:
> We have a seemingly identical problem in v9.6 on our big prod databases.  It 
> worked perfectly in 8.4 *and works perfectly in 9.6 our small staging 
> servers*.
> A giant sql script that drops the oldest child tables from a large set of 
> partitioned tables (by dropping all relevant triggers, indexes, functions 
> etc, dropping the oldest children, adding the new children then recreating 
> all the relevant triggers, indexes, functions etc).

A script like that is going to be taking exclusive locks on a whole lot
of tables.  My guess is that you are running up against lock conflicts
against other activity in the DB, and that the "hang" is just waiting
for other transactions to release their table locks.  Looking into
the pg_locks view could be informative.

regards, tom lane

Re: psql is hanging

[Ron]

On 11/30/2018 10:38 AM, Tom Lane wrote:

Ron  writes:

We have a seemingly identical problem in v9.6 on our big prod databases.  It
worked perfectly in 8.4 *and works perfectly in 9.6 our small staging servers*.
A giant sql script that drops the oldest child tables from a large set of
partitioned tables (by dropping all relevant triggers, indexes, functions
etc, dropping the oldest children, adding the new children then recreating
all the relevant triggers, indexes, functions etc).

A script like that is going to be taking exclusive locks on a whole lot
of tables.  My guess is that you are running up against lock conflicts
against other activity in the DB, and that the "hang" is just waiting
for other transactions to release their table locks.  Looking into
the pg_locks view could be informative.


We thought of that, but...
1. Even when that process is the only one touching the database, it hangs.
2. It worked perfectly on 8.4.
3. It works perfectly on 9.6 using the small staging database.
4. It works perfectly when running the script from psql.

Before that weekly job runs again, the application team is going to upgrade 
to JDBC driver v42.2.5 (don't know what they're using now), and add 
protocolVersion=2 to the connection string.  I'll post an update next Wednesday.



--
Angular momentum makes the world go 'round.

Re: psql is hanging

[Geoff Winkless]

On Fri, 30 Nov 2018 at 15:53, John Smith  wrote:
> We have a long script of sql that we run, several thousand lines of sql. If I 
> execute the script
> from start to finish, somewhere in the middle of it, one sql command will 
> hang and take 2 to
> 3 hours. During this time, "htop" shows 100% cpu usage with a postgresql 
> process, so it
> appears to be doing something.
>
> If I stop that command, then immediately rerun the rest of the script 
> starting from the command that "hung",
> the "hung" command then completes in 5 seconds and the rest of the script 
> just continues on perfectly.

I assume that JDBC doesn't put a transaction around your whole script
if you send it in one hit?

Geoff

postgis after pg_upgrade

[Slavcho Trnkovski]

Hi,

I have postgresql 9.4 with postgis extension installed (latest version,
2.4.5). I upgraded postgresql from 9.4 to 9.6.
After upgrading to 9.6 I get the following result
from PostGIS_full_version()
 select PostGIS_full_version();

   postgis_full_version
---
 POSTGIS="2.4.5 r16765" *PGSQL="94" (procs need upgrade for use with
"96") *GEOS="3.6.2-CAPI-1.10.2
4d2925d6" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 1.11.4, released
2016/01/25" LIBXML="2.9.1" LIBJSON="0.11" RASTER
(1 row)

Is there any way to resolve this besides recreating the extension?

Regards,
Slavcho

Re: postgis after pg_upgrade

[Paul Ramsey]

If you install the new version, and then use ‘ALTER EXTENSION UPDATE’ to update 
the SQL-side bindings, everything should improve.

> On Nov 30, 2018, at 5:11 AM, Slavcho Trnkovski  wrote:
> 
> Hi,
> 
> I have postgresql 9.4 with postgis extension installed (latest version, 
> 2.4.5). I upgraded postgresql from 9.4 to 9.6. 
> After upgrading to 9.6 I get the following result from PostGIS_full_version()
>  select PostGIS_full_version();
>   
>  postgis_full_version
> ---
>  POSTGIS="2.4.5 r16765" PGSQL="94" (procs need upgrade for use with "96") 
> GEOS="3.6.2-CAPI-1.10.2 4d2925d6" PROJ="Rel. 4.9.3, 15 August 2016" 
> GDAL="GDAL 1.11.4, released 2016/01/25" LIBXML="2.9.1" LIBJSON="0.11" RASTER
> (1 row)
> 
> Is there any way to resolve this besides recreating the extension?
> 
> Regards,
> Slavcho

Re: psql is hanging

[John Smith]

Thanks Chris and Steve for the analyze suggestion; That will be my next
test!

On Fri, Nov 30, 2018 at 11:27 AM Steve Crawford <
scrawf...@pinpointresearch.com> wrote:

>
>
> On Fri, Nov 30, 2018 at 8:05 AM Chris Mair  wrote:
>
>>
>> > We're kind of pulling out our hair here, any ideas?
>>
>> You might try issuing the command
>>
>>analyze;
>>
>> right *before* the command that hangs.
>>
>>
> You might consider trying the "auto_explain" module (
> https://www.postgresql.org/docs/current/auto-explain.html ). This will
> let you "trap" the query plan used for the long-running query. Then compare
> that query plan with a manually run explain when it runs quickly to see if
> they differ. If they do, it suggests that bad statistics are a likely
> culprit and Chris' suggestion of running analyze will help.
>
> You only need to analyze those tables used in the query and, most likely,
> only tables that were substantially changed within a moderately short
> period prior to the start of the query.
>
> Autovacuum, which will handles analyze as well, typically defaults to
> checking for tables that need attention every minute so for processes that
> have a lot of steps it becomes "luck of the draw" whether or not a needed
> analyze is run after a substantial table change and before that table is
> used.
>
> We frequently put specific "analyze" statements in such scripts
> immediately following bulk-update statements.
>
> Cheers,
> Steve
>
>
>
>

Vacuum and Materialized view refresh slow

[Vikas Sharma]

Hello There,

We have postgres 9.6 with 3 slaves replicated in synchronous commit.
 we recently migrated to to another cloud provider.  Though we have the
same configuration for VMs and postgres, we are experiencing systemwide
slowness with writes to database.

I am more concerned about a scheduled script to vacuum and refresh two
Materialized views after every 5 minutes.
Time to refresh MV and vacuum has increased considerably and during this
time a simple insert takes 10-15 seconds which normally should take
milliseconds whereas with old provider that simple insert used to take not
more than couple of seconds during refresh time.

I have seen the vacuum and refresh MV process showing as waiting for a
{change location} sometimes for a fraction of seconds.

Could I be enlighted about what I can do to improve the performance to make
write faster.

Best regards
Vikas

Re: Vacuum and Materialized view refresh slow

[Rene Romero Benavides]

Have you benchmarked the new infrastructure's IO and network performance?
why did you switch providers? was it because of cost?

Re: Vacuum and Materialized view refresh slow

[Rob Sargent]

On 11/30/18 1:53 PM, Rene Romero Benavides wrote:
Have you benchmarked the new infrastructure's IO and network 
performance? why did you switch providers? was it because of cost?
And have you proven to yourself that all the indices are in place, stats 
are up-to-date?  You didn't by any chance migrate away from SSD?

Re: Dump table using pg_dump vs pg_restore -f

[Adrian Klaver]

On 11/29/18 7:31 AM, Adrian Klaver wrote:

Postgres 10.6

I am getting different output using:

pg_dump -d production -U postgres  -s -t container -f cont.sql

vs

pg_restore -s -t container -f container.sql production_112818.out





In the docs:

https://www.postgresql.org/docs/10/app-pgrestore.html

I see:

"
Note

This flag does not behave identically to the -t flag of pg_dump. There 
is not currently any provision for wild-card matching in pg_restore, nor 
can you include a schema name within its -t.

"

That to me reads that the difference is for the search method for table 
names only.


The description for:
-t table

Includes:

"Restore definition and/or data of only the named table. ..."

That in my mind would include the PK and the table triggers.

Am I misunderstanding?



Just wondering,  am I the only one seeing this issue?

Am I doing something wrong?


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: surprising query optimisation

[Gavin Flower]

On 01/12/2018 04:33, Stephen Frost wrote:

Greetings,

* Chris Withers (ch...@withers.org) wrote:

On 28/11/2018 22:49, Stephen Frost wrote:

* Chris Withers (ch...@withers.org) wrote:

We have an app that deals with a lot of queries, and we've been slowly
seeing performance issues emerge. We take a lot of free form queries from
users and stumbled upon a very surprising optimisation.

So, we have a 'state' column which is a 3 character string column with an
index on it. Despite being a string, this column is only used to store one
of three values: 'NEW', 'ACK', or 'RSV'.

Sounds like a horrible field to have an index on.

That's counter-intuitive for me. What leads you to say this and what would
you do/recommend instead?

For this, specifically, it's because you end up with exactly what you
have: a large index with tons of duplicate values.  Indexes are
particularly good when you have high-cardinality fields.  Now, if you
have a skewed index, where there's one popular value and a few much less
popular ones, then that's where you really want a partial index (as I
suggest earlier) so that queries against the non-popular value(s) is
able to use the index and the index is much smaller.

Of course, for this to work you need to set up the partial index
correctly and make sure that your queries end up using it.

Thanks!

Stephen


An index simply tells pg which block to look at (assuming that the index 
itself is not sufficient to satisfy the query), so if using an index 
would still require that pg look at most blocks, then it cheaper to just 
scan the whole table - rather than load the index and still look at all 
blocks that contain the table data.  I've oversimplified slightly.


An index is best used when using it results in fewer blocks being read 
from disk.


Also the use of RAM is better when the size of the index is kept small.  
For example having an index on sex for nurses is a waste of time because 
most nurses are female.  However, a partial index (as suggested by 
Stephen, for your query) that includes only males could be useful if you 
have queries looking for male nurses (assumes male nurses are a very 
small fraction of nurses such that most data blocks don't have rows for 
males nurses, and the planner knows this).


I once optimised a very complex set queries that made extensive use of 
indexes.  However, with the knowledge I have today, I would have most 
likely had fewer and smaller indexes.  As I now realize, that some of my 
indexes were probably counter productive, especially as I'd given no 
thought to how much RAM would be required to host the data plus 
indexes!  Fortunately, while the objective was to run all those queries 
within 24 hours, they actually only took a couple of hours.


Chris, I would strongly suggest, you read up on the excellent 
documentation pg has about indexes, but don't expect to understand it 
all at one sitting...



Cheers,
Gavin