very slow queries and ineffective vacuum

Hello Lukasz,

On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel <
lukasz.wrobel@motorolasolutions.com> wrote:

There doesn't seem to be any issues with disk space, memory or CPU, as
neither of those is even 50% used (as per df and top).

Are you using the default PostgreSQL configuration settings, or have you
custom tuned them? The default settings are targeted for wide compatibility
and are not optimized for performance. If PostgreSQL is performing badly
and using a small amount of system resources it is likely some tuning is
needed. See docs:
http://www.postgresql.org/docs/current/static/runtime-config.html

On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel <
lukasz.wrobel@motorolasolutions.com> wrote:

For whatever reason there is also no data in pg_stat* tables.

You can also turn on tracking (for statistics views) by enabling statistics
collection in the config
http://www.postgresql.org/docs/current/static/runtime-config-statistics.html

*Will J. Dunn*
*willjdunn.com <http://willjdunn.com&gt;*

On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel <
lukasz.wrobel@motorolasolutions.com> wrote:

On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel <
lukasz.wrobel@motorolasolutions.com> wrote:

Perhaps I'm missing some indexes on the tables (creating them on the
columns on which the where clause was used in the long queries seemed to
halve their times). Also how can I monitor my transactions and if they are
closed properly?

To track transactions that have not been left idle but not committed or
rolled back you would:

1) Set track_activities true in the config (doc:
http://www.postgresql.org/docs/current/static/runtime-config-statistics.html#GUC-TRACK-ACTIVITIES
)
2) Query the pg_stat_activity view for connections where state = 'idle in
transaction' (doc:
http://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW
)

As you would suspect, transactions that have been left "idle in
transaction" prevent vacuum from removing old tuples (because they are
still in scope for that transaction)

*Will J. Dunn*
*willjdunn.com <http://willjdunn.com&gt;*

On Tue, Jun 30, 2015 at 4:27 PM, William Dunn <dunnwjr@gmail.com> wrote:

Sorry I meant to say, "To track transactions that *have been* left idle but
not committed or rolled back you would..."
Typo

*Will J. Dunn*
*willjdunn.com <http://willjdunn.com&gt;*

On Tue, Jun 30, 2015 at 4:33 PM, William Dunn <dunnwjr@gmail.com> wrote:

Hi

What is an output of VACUUM VERBOSE statement?

VACUUM can be blocked by some forgotten transaction. Check your
pg_stat_activity table for some old process in "idle in transaction" state.
Then connection should not be reused, and you can see a error messages
about missing connections. I found this issue more time in Java application
- when it doesn't handle transactions correctly. Same effect can have
forgotten 2PC transaction.

When VACUUM long time was not executed - the most fast repair process is a
export via pg_dump and load. Another way is dropping all indexes, VACUUM
FULL and creating fresh indexes.

Autovacuum is based on tracking statistics - you have to see your tables in
table pg_stat_user_tables, and you can check there autovacuum timestamp.
Sometimes autovacuum has too low priority and it is often cancelled.

Regards

Pavel Stehule

2015-06-30 14:57 GMT+02:00 Lukasz Wrobel <
lukasz.wrobel@motorolasolutions.com>:

William Dunn <dunnwjr@gmail.com> writes:

Sorry I meant to say, "To track transactions that have been left idle but not committed or rolled back you would..."
Typo

foodb/postgres
=# \d pg_stat_activity|pg_prepared_xacts
View "pg_catalog.pg_prepared_xacts"
Column | Type | Modifiers
-------------+--------------------------+-----------
transaction | xid |
gid | text |
prepared | timestamp with time zone |
owner | name |
database | name |

View "pg_catalog.pg_stat_activity"
Column | Type | Modifiers
------------------+--------------------------+-----------
datid | oid |
datname | name |
pid | integer |
usesysid | oid |
usename | name |
application_name | text |
client_addr | inet |
client_hostname | text |
client_port | integer |
backend_start | timestamp with time zone |
xact_start | timestamp with time zone |
query_start | timestamp with time zone |
state_change | timestamp with time zone |
waiting | boolean |
state | text |
query | text |

foodb/postgres
=#

Will J. Dunn
willjdunn.com

On Tue, Jun 30, 2015 at 4:33 PM, William Dunn <dunnwjr@gmail.com> wrote:

On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel <lukasz.wrobel@motorolasolutions.com> wrote:

Perhaps I'm missing some indexes on the tables (creating them on the columns on which the where clause was used in the long queries seemed to halve their
times). Also how can I monitor my transactions and if they are closed properly?

To track transactions that have not been left idle but not committed or rolled back you would:

1) Set track_activities true in the config (doc: http://www.postgresql.org/docs/current/static/runtime-config-statistics.html#GUC-TRACK-ACTIVITIES)
2) Query the pg_stat_activity view for connections where state = 'idle in transaction' (doc: http://www.postgresql.org/docs/current/static/monitoring-stats.html#
PG-STAT-ACTIVITY-VIEW)

As you would suspect, transactions that have been left "idle in transaction" prevent vacuum from removing old tuples (because they are still in scope for that
transaction)

Will J. Dunn
willjdunn.com

On Tue, Jun 30, 2015 at 4:27 PM, William Dunn <dunnwjr@gmail.com> wrote:

Hello Lukasz,

On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel <lukasz.wrobel@motorolasolutions.com> wrote:

There doesn't seem to be any issues with disk space, memory or CPU, as neither of those is even 50% used (as per df and top).

Are you using the default PostgreSQL configuration settings, or have you custom tuned them? The default settings are targeted for wide compatibility and are not
optimized for performance. If PostgreSQL is performing badly and using a small amount of system resources it is likely some tuning is needed. See docs: http://
www.postgresql.org/docs/current/static/runtime-config.html 

On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel <lukasz.wrobel@motorolasolutions.com> wrote:

For whatever reason there is also no data in pg_stat* tables.

You can also turn on tracking (for statistics views) by enabling statistics collection in the config http://www.postgresql.org/docs/current/static/
runtime-config-statistics.html

Will J. Dunn
willjdunn.com

On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel <lukasz.wrobel@motorolasolutions.com> wrote:

Hello.

I have multiple problems with my database, the biggest of which is how to find out what is actually wrong.

First of all I have a 9.3 postgres database that is running for about a month. Right now the queries on that database are running very slowly (select with a
simple "where" on a non-indexed column on a table with about 5000 records takes 1,5s, a complicated hibernate select with 7 joins on tables of about 5000
records takes about 15s, insert or update on a table with 35000 records takes up to 20 mins).

The tables and indexes on those tables are bloated to the point where this query: https://wiki.postgresql.org/wiki/Show_database_bloat shows wasted bytes in
hundreds of MB.

For whatever reason there is also no data in pg_stat* tables.

So due to the long query times, there are multiple errors in my application logs like "No free connection available" or "Could not synchronize database
state with session", or "Failed to rollback transaction" and the application fails to start in the required time.

The only thing that helps fix the situation seems to be vacuum full of the entire database. Regular vacuum doesn't even lower the dead tuples count (which
appear by the thousands during application launching). Reindex of all the indexes in the database didn't help as well. All autovacuum parameters are
default.

There doesn't seem to be any issues with disk space, memory or CPU, as neither of those is even 50% used (as per df and top).

Is there any good tool that will monitor the queries and generate a report with useful information on what might be the problem? I tried pg_badger, but all
I got were specific queries and their times, but the long query times are just one of the symptoms of what's wrong with the database, not the cause.

Perhaps I'm missing some indexes on the tables (creating them on the columns on which the where clause was used in the long queries seemed to halve their
times). Also how can I monitor my transactions and if they are closed properly?

I will be grateful for any help and if you need more details I can provide them if possible.

Best regards.
Lukasz

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

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

Jerry,

On Tue, Jun 30, 2015 at 5:31 PM, Jerry Sievers <gsievers19@comcast.net>
wrote:

foodb/postgres
=# \d pg_stat_activity|pg_prepared_xacts
View "pg_catalog.pg_prepared_xacts"
Column | Type | Modifiers
-------------+--------------------------+-----------
transaction | xid |
gid | text |
prepared | timestamp with time zone |
owner | name |
database | name |

View "pg_catalog.pg_stat_activity"
Column | Type | Modifiers
------------------+--------------------------+-----------
datid | oid |
datname | name |
pid | integer |
usesysid | oid |
usename | name |
application_name | text |
client_addr | inet |
client_hostname | text |
client_port | integer |
backend_start | timestamp with time zone |
xact_start | timestamp with time zone |
query_start | timestamp with time zone |
state_change | timestamp with time zone |
waiting | boolean |
state | text |
query | text |

foodb/postgres
=#

What exactly are you trying to tell us? If you want to provide someone
details about one of the system views it is probably better to link them to
the official documentation which lists not only the view's fields and their
datatype but also their meaning,what they will be in their specific
Postgres version, and any additional notes the community deemed useful

*Will J. Dunn*
*willjdunn.com <http://willjdunn.com&gt;*

On Tue, Jun 30, 2015 at 5:31 PM, Jerry Sievers <gsievers19@comcast.net>
wrote:

Lukasz Wrobel wrote:

Hello.

I have multiple problems with my database, the biggest of which is how to
find out what is actually wrong.

First of all I have a 9.3 postgres database that is running for about a
month. Right now the queries on that database are running very slowly
(select with a simple "where" on a non-indexed column on a table with about
5000 records takes 1,5s, a complicated hibernate select with 7 joins on
tables of about 5000 records takes about 15s, insert or update on a table
with 35000 records takes up to 20 mins).

What's your operating system?

What does pg_stat_user_tables tell you about the vacuum times for the
bloated tables? Mainly, is autovacuum processing them at all? If not,
are there log entries about autovacuum trouble (those would show up as
ERROR mentioning automatic vacuuming)? If not, is autovacuum running at
all, and is the stats collector working properly?

I'd recommend setting log_autovacuum_min_duration to a value other than
the default -1 and see whether it is doing anything.

Also useful for debugging would be the VACUUM VERBOSE output for
problematic tables.

Maybe your tuple death rate is higher than what autovacuum can cope
with, with default settings. In that case maybe you need a larger
autovacuum_max_workers setting and/or a decrease of
autovacuum_vacuum_cost_delay and/or a change of autovacuum_naptime.
Sometimes, manual vacuuming of individual problematic tables also helps.

--
�lvaro Herrera http://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

How about your start by giving us a little useful information? Show us
your_longest_query and the output from EXPLAIN your_longest_query;
Although you say you have indexes, they may not be the correct indexes that
you really need.
Also, how many physical disks do you have?
Do you have multiple tablespaces, if so, are your tables and indexes
assigned separate tablespaces?

On Tue, Jun 30, 2015 at 6:16 PM, Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:

Lukasz Wrobel wrote:

Hello.

I have multiple problems with my database, the biggest of which is how to
find out what is actually wrong.

First of all I have a 9.3 postgres database that is running for about a
month. Right now the queries on that database are running very slowly
(select with a simple "where" on a non-indexed column on a table with

about

5000 records takes 1,5s, a complicated hibernate select with 7 joins on
tables of about 5000 records takes about 15s, insert or update on a table
with 35000 records takes up to 20 mins).

What's your operating system?

What does pg_stat_user_tables tell you about the vacuum times for the
bloated tables? Mainly, is autovacuum processing them at all? If not,
are there log entries about autovacuum trouble (those would show up as
ERROR mentioning automatic vacuuming)? If not, is autovacuum running at
all, and is the stats collector working properly?

I'd recommend setting log_autovacuum_min_duration to a value other than
the default -1 and see whether it is doing anything.

Also useful for debugging would be the VACUUM VERBOSE output for
problematic tables.

Maybe your tuple death rate is higher than what autovacuum can cope
with, with default settings. In that case maybe you need a larger
autovacuum_max_workers setting and/or a decrease of
autovacuum_vacuum_cost_delay and/or a change of autovacuum_naptime.
Sometimes, manual vacuuming of individual problematic tables also helps.

--
Álvaro Herrera http://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

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

On Wed, Jul 1, 2015 at 3:37 AM Lukasz Wrobel <
lukasz.wrobel@motorolasolutions.com> wrote:

Hello.

I have multiple problems with my database, the biggest of which is how to
find out what is actually wrong.

First of all I have a 9.3 postgres database that is running for about a
month. Right now the queries on that database are running very slowly
(select with a simple "where" on a non-indexed column on a table with about
5000 records takes 1,5s, a complicated hibernate select with 7 joins on
tables of about 5000 records takes about 15s, insert or update on a table
with 35000 records takes up to 20 mins).

The tables and indexes on those tables are bloated to the point where this
query: https://wiki.postgresql.org/wiki/Show_database_bloat shows wasted
bytes in hundreds of MB.

For whatever reason there is also no data in pg_stat* tables.

Make sure that your tracking parameters are on-
track_counts and track_activities

So due to the long query times, there are multiple errors in my
application logs like "No free connection available" or "Could not
synchronize database state with session", or "Failed to rollback
transaction" and the application fails to start in the required time.

One of things you can do is to set statement timeout in PostgreSQL
configuration (but that may actually increase your problems by cancelling
long running queries which seems to be too many in your case).

The only thing that helps fix the situation seems to be vacuum full of the
entire database. Regular vacuum doesn't even lower the dead tuples count
(which appear by the thousands during application launching).

Though I am not very sure but to me it seems this could be because
your track_counts and track_activities is not set to on. Since your are not
tracking them they are not being updated at all.

try this-
vacuum analyze a table

vacuum analyze schema_name.table_name;

reindex one of that table
reindex table schema_name.table_name;

Reindex of all the indexes in the database didn't help as well. All
autovacuum parameters are default.

Did you analyze the database tables? Since your track_count is off (I have
assumed based on your above statements) your database tables might never
have been analyzed which could be leading to wrong/sub-optimal plans.

There doesn't seem to be any issues with disk space, memory or CPU, as
neither of those is even 50% used (as per df and top).

Is there any good tool that will monitor the queries and generate a report
with useful information on what might be the problem? I tried pg_badger,
but all I got were specific queries and their times, but the long query
times are just one of the symptoms of what's wrong with the database, not
the cause.

Perhaps I'm missing some indexes on the tables (creating them on the
columns on which the where clause was used in the long queries seemed to
halve their times).

Yes, if you create indexes then certainly those will be helpful depending
on the volume of data in that table.

Also how can I monitor my transactions and if they are closed properly?

Check pg_stat_activity view. There is a column for state of the connection
check there are too many connections in <IDLE in transaction> state. This
means a connection has initiated a transaction but has not committed it yet.
You can combine the state with status change time (state_change) column-

select * from pg_stat_activity where
now()-state_change>'1 min'::interval and
state='idle in transaction';

This will list all those sessions which have not committed for last one
minute.

You can look at using pgBouncer to effectively manage your sessions and
connections.

On Wed, Jul 1, 2015 at 4:51 AM Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi

What is an output of VACUUM VERBOSE statement?

VACUUM can be blocked by some forgotten transaction. Check your
pg_stat_activity table for some old process in "idle in transaction" state.
Then connection should not be reused, and you can see a error messages
about missing connections. I found this issue more time in Java application
- when it doesn't handle transactions correctly. Same effect can have
forgotten 2PC transaction.

When VACUUM long time was not executed - the most fast repair process is a
export via pg_dump and load. Another way is dropping all indexes, VACUUM
FULL and creating fresh indexes.

Autovacuum is based on tracking statistics - you have to see your tables
in table pg_stat_user_tables, and you can check there autovacuum timestamp.
Sometimes autovacuum has too low priority and it is often cancelled.

As he has mentioned that he can not see anything in pg_stat* table which
means that probably track_count and track_activities is set to off. In that
case won't autovacuum be *unable* to do anything (since count of row
changes etc is not being captured)?

Hello again.

Thank you for all your responses. I will try to clarify more and attempt to
answer the questions you raised.

I'm attaching the postgresql.conf this time. I cannot supply you guys with
a proper database schema, so I will try to supply you with some obfuscated
logs and queries. Sorry for the complication.

First of all I seem to have misdirected you guys about the pg_stat* tables.
I have a virtual machine with the database from our test team, which was
running for a month. When I deploy it, our java application is not running,
so no queries are being executed. The pg_stat* tables contain no data
(which is surprising). When I launch the application and queries start
going, the stats are collected normally and autovacuums are being performed.

I attached the output of vacuum verbose command.

As for the pg_stat_activity, I have no "idle in transaction" records there,
but I do have some in "idle" state, that don't disappear. Perhaps this
means some sessions are not closed? I attached the query result as
activity.txt.

I also have a few "sending cancel to blocking autovacuum" and "canceling
autovacuum task" messages in syslog.

Sample query explain analyze. This was ran after vacuum analyze of the
entire database.

explain analyze SELECT col1, col2, col3, col4, col5 FROM ( table84 table84
LEFT JOIN table57 table57 ON table84.col7 = table57.col7 ) LEFT JOIN
table19 table19 ON table84.col7 = table19.col7;
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Hash Right Join (cost=46435.43..108382.29 rows=189496 width=79) (actual
time=4461.686..13457.233 rows=5749 loops=1)
Hash Cond: (table57.col7 = table84.col7)
-> Seq Scan on table57 table57 (cost=0.00..49196.63 rows=337963
width=57) (actual time=0.040..8981.438 rows=6789 loops=1)
-> Hash (cost=42585.73..42585.73 rows=189496 width=38) (actual
time=4447.731..4447.731 rows=5749 loops=1)
Buckets: 16384 Batches: 2 Memory Usage: 203kB
-> Hash Right Join (cost=18080.66..42585.73 rows=189496
width=38) (actual time=1675.223..4442.046 rows=5749 loops=1)
Hash Cond: (table19.col7 = table84.col7)
-> Seq Scan on table19 table19 (cost=0.00..17788.17
rows=187317 width=26) (actual time=0.007..2756.501 rows=5003 loops=1)
-> Hash (cost=14600.96..14600.96 rows=189496 width=20)
(actual time=1674.940..1674.940 rows=5749 loops=1)
Buckets: 32768 Batches: 2 Memory Usage: 159kB
-> Seq Scan on table84 table84 (cost=0.00..14600.96
rows=189496 width=20) (actual time=0.059..1661.482 rows=5749 loops=1)
Total runtime: 13458.301 ms
(12 rows)

Thank you again for your advice and I hope that with your help I'll be able
to solve this issue.

Best regards.
Lukasz

Well, right off the bat, it looks like you do not have indexes on
table84.col7
table57.col7
table19.col7

At least a quick review of the query plan shows they are not being used if
they do exist.

So perhaps that is one of the chief causes for slow performance.

On Thu, Jul 2, 2015 at 6:58 AM, Lukasz Wrobel <
lukasz.wrobel@motorolasolutions.com> wrote:

Hello again.

Thank you for all your responses. I will try to clarify more and attempt
to answer the questions you raised.

I'm attaching the postgresql.conf this time. I cannot supply you guys with
a proper database schema, so I will try to supply you with some obfuscated
logs and queries. Sorry for the complication.

First of all I seem to have misdirected you guys about the pg_stat*
tables. I have a virtual machine with the database from our test team,
which was running for a month. When I deploy it, our java application is
not running, so no queries are being executed. The pg_stat* tables contain
no data (which is surprising). When I launch the application and queries
start going, the stats are collected normally and autovacuums are being
performed.

I attached the output of vacuum verbose command.

As for the pg_stat_activity, I have no "idle in transaction" records
there, but I do have some in "idle" state, that don't disappear. Perhaps
this means some sessions are not closed? I attached the query result as
activity.txt.

I also have a few "sending cancel to blocking autovacuum" and "canceling
autovacuum task" messages in syslog.

Sample query explain analyze. This was ran after vacuum analyze of the
entire database.

explain analyze SELECT col1, col2, col3, col4, col5 FROM ( table84 table84
LEFT JOIN table57 table57 ON table84.col7 = table57.col7 ) LEFT JOIN
table19 table19 ON table84.col7 = table19.col7;
QUERY
PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------
Hash Right Join (cost=46435.43..108382.29 rows=189496 width=79) (actual
time=4461.686..13457.233 rows=5749 loops=1)
Hash Cond: (table57.col7 = table84.col7)
-> Seq Scan on table57 table57 (cost=0.00..49196.63 rows=337963
width=57) (actual time=0.040..8981.438 rows=6789 loops=1)
-> Hash (cost=42585.73..42585.73 rows=189496 width=38) (actual
time=4447.731..4447.731 rows=5749 loops=1)
Buckets: 16384 Batches: 2 Memory Usage: 203kB
-> Hash Right Join (cost=18080.66..42585.73 rows=189496
width=38) (actual time=1675.223..4442.046 rows=5749 loops=1)
Hash Cond: (table19.col7 = table84.col7)
-> Seq Scan on table19 table19 (cost=0.00..17788.17
rows=187317 width=26) (actual time=0.007..2756.501 rows=5003 loops=1)
-> Hash (cost=14600.96..14600.96 rows=189496 width=20)
(actual time=1674.940..1674.940 rows=5749 loops=1)
Buckets: 32768 Batches: 2 Memory Usage: 159kB
-> Seq Scan on table84 table84 (cost=0.00..14600.96
rows=189496 width=20) (actual time=0.059..1661.482 rows=5749 loops=1)
Total runtime: 13458.301 ms
(12 rows)

Thank you again for your advice and I hope that with your help I'll be
able to solve this issue.

Best regards.
Lukasz

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

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

On Thu, 2 Jul 2015 12:58:18 +0200
Lukasz Wrobel <lukasz.wrobel@motorolasolutions.com> wrote:

Hello again.

Thank you for all your responses. I will try to clarify more and attempt to
answer the questions you raised.

I'm attaching the postgresql.conf this time. I cannot supply you guys with
a proper database schema, so I will try to supply you with some obfuscated
logs and queries. Sorry for the complication.

First of all I seem to have misdirected you guys about the pg_stat* tables.
I have a virtual machine with the database from our test team, which was
running for a month. When I deploy it, our java application is not running,
so no queries are being executed. The pg_stat* tables contain no data
(which is surprising). When I launch the application and queries start
going, the stats are collected normally and autovacuums are being performed.

I attached the output of vacuum verbose command.

As for the pg_stat_activity, I have no "idle in transaction" records there,
but I do have some in "idle" state, that don't disappear. Perhaps this
means some sessions are not closed? I attached the query result as
activity.txt.

I also have a few "sending cancel to blocking autovacuum" and "canceling
autovacuum task" messages in syslog.

Sample query explain analyze. This was ran after vacuum analyze of the
entire database.

The analyze doesn't seem to be working terribly well. Looking at the
explain, it expects 337963 rows in table57, but there are only 6789.
There are similar discrepencies with table19 and table84.

I don't know if indexes are your problem. Those three tables are pretty
small, so the sequential scans should be pretty quick (probably faster
than index scans, since it looks like most of the rows are returned from
all the tables.

I'm somewhat confused by your description of the situation. Is the performance
problem happening on the virtual machine? Because VMs are notorious for
being on oversubscribed hosts and exhibiting performance far below what
is expected. It would be worthwhile to do some disk speed and CPU speed tests
on the VM to see what kind of performance it's actually capable of ... if
the VM is performing poorly, there's not much you can do with PostgreSQL
to improve things.

explain analyze SELECT col1, col2, col3, col4, col5 FROM ( table84 table84
LEFT JOIN table57 table57 ON table84.col7 = table57.col7 ) LEFT JOIN
table19 table19 ON table84.col7 = table19.col7;
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Hash Right Join (cost=46435.43..108382.29 rows=189496 width=79) (actual
time=4461.686..13457.233 rows=5749 loops=1)
Hash Cond: (table57.col7 = table84.col7)
-> Seq Scan on table57 table57 (cost=0.00..49196.63 rows=337963
width=57) (actual time=0.040..8981.438 rows=6789 loops=1)
-> Hash (cost=42585.73..42585.73 rows=189496 width=38) (actual
time=4447.731..4447.731 rows=5749 loops=1)
Buckets: 16384 Batches: 2 Memory Usage: 203kB
-> Hash Right Join (cost=18080.66..42585.73 rows=189496
width=38) (actual time=1675.223..4442.046 rows=5749 loops=1)
Hash Cond: (table19.col7 = table84.col7)
-> Seq Scan on table19 table19 (cost=0.00..17788.17
rows=187317 width=26) (actual time=0.007..2756.501 rows=5003 loops=1)
-> Hash (cost=14600.96..14600.96 rows=189496 width=20)
(actual time=1674.940..1674.940 rows=5749 loops=1)
Buckets: 32768 Batches: 2 Memory Usage: 159kB
-> Seq Scan on table84 table84 (cost=0.00..14600.96
rows=189496 width=20) (actual time=0.059..1661.482 rows=5749 loops=1)
Total runtime: 13458.301 ms
(12 rows)

Thank you again for your advice and I hope that with your help I'll be able
to solve this issue.

Best regards.
Lukasz

--
Bill Moran

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

On Thu, Jul 2, 2015 at 9:57 PM Lukasz Wrobel <
lukasz.wrobel@motorolasolutions.com> wrote:

Hello again.

Thank you for all your responses. I will try to clarify more and attempt
to answer the questions you raised.

I'm attaching the postgresql.conf this time. I cannot supply you guys with
a proper database schema, so I will try to supply you with some obfuscated
logs and queries. Sorry for the complication.

You postgresql.conf seems to have some issues. Can you explain about the
choice of parameter values for below parameters?

maintenance_work_mem = 32MB
bgwriter_lru_maxpages = 0
synchronous_commit = off
effective_cache_size is left to default
random_page_cost is left to default

I don't know anything about your hardware- memory, cpu and disk layout (and
IOPS of disk) so can not really say what would be the right setting but
this certainly does not seem right to me.

First of all I seem to have misdirected you guys about the pg_stat*
tables. I have a virtual machine with the database from our test team,
which was running for a month. When I deploy it, our java application is
not running, so no queries are being executed. The pg_stat* tables contain
no data (which is surprising). When I launch the application and queries
start going, the stats are collected normally and autovacuums are being
performed.

It is still confusing to me. To help us understand can you specifically
tell if you see anything in pg_stat_user_tables and pg_stat_user_indexes?

I attached the output of vacuum verbose command.

Seems like a lot of your tables have bloats

As for the pg_stat_activity, I have no "idle in transaction" records
there, but I do have some in "idle" state, that don't disappear. Perhaps
this means some sessions are not closed? I attached the query result as
activity.txt.

I also have a few "sending cancel to blocking autovacuum" and "canceling
autovacuum task" messages in syslog.

Can you share some of these log files?

Sample query explain analyze. This was ran after vacuum analyze of the
entire database.

explain analyze SELECT col1, col2, col3, col4, col5 FROM ( table84 table84
LEFT JOIN table57 table57 ON table84.col7 = table57.col7 ) LEFT JOIN
table19 table19 ON table84.col7 = table19.col7;
QUERY
PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------
Hash Right Join (cost=46435.43..108382.29 rows=189496 width=79) (actual
time=4461.686..13457.233 rows=5749 loops=1)
Hash Cond: (table57.col7 = table84.col7)
-> Seq Scan on table57 table57 (cost=0.00..49196.63 rows=337963
width=57) (actual time=0.040..8981.438 rows=6789 loops=1)
-> Hash (cost=42585.73..42585.73 rows=189496 width=38) (actual
time=4447.731..4447.731 rows=5749 loops=1)
Buckets: 16384 Batches: 2 Memory Usage: 203kB
-> Hash Right Join (cost=18080.66..42585.73 rows=189496
width=38) (actual time=1675.223..4442.046 rows=5749 loops=1)
Hash Cond: (table19.col7 = table84.col7)
-> Seq Scan on table19 table19 (cost=0.00..17788.17
rows=187317 width=26) (actual time=0.007..2756.501 rows=5003 loops=1)
-> Hash (cost=14600.96..14600.96 rows=189496 width=20)
(actual time=1674.940..1674.940 rows=5749 loops=1)
Buckets: 32768 Batches: 2 Memory Usage: 159kB
-> Seq Scan on table84 table84 (cost=0.00..14600.96
rows=189496 width=20) (actual time=0.059..1661.482 rows=5749 loops=1)
Total runtime: 13458.301 ms
(12 rows)

You have a lot of issues with this plan-
- The statistics is not updated
- There is a lot of hash join, sequential scan implying you don't have
proper indexes or those are not useful (meaning your indexes are bloated
too, consider reindexing them)

2015-07-03 7:18 GMT+02:00 Sameer Kumar <sameer.kumar@ashnik.com>:

On Thu, Jul 2, 2015 at 9:57 PM Lukasz Wrobel <
lukasz.wrobel@motorolasolutions.com> wrote:

Hello again.

Thank you for all your responses. I will try to clarify more and attempt
to answer the questions you raised.

I'm attaching the postgresql.conf this time. I cannot supply you guys
with a proper database schema, so I will try to supply you with some
obfuscated logs and queries. Sorry for the complication.

You postgresql.conf seems to have some issues. Can you explain about the
choice of parameter values for below parameters?

maintenance_work_mem = 32MB
bgwriter_lru_maxpages = 0
synchronous_commit = off
effective_cache_size is left to default
random_page_cost is left to default

I don't know anything about your hardware- memory, cpu and disk layout
(and IOPS of disk) so can not really say what would be the right setting
but this certainly does not seem right to me.

First of all I seem to have misdirected you guys about the pg_stat*
tables. I have a virtual machine with the database from our test team,
which was running for a month. When I deploy it, our java application is
not running, so no queries are being executed. The pg_stat* tables contain
no data (which is surprising). When I launch the application and queries
start going, the stats are collected normally and autovacuums are being
performed.

It is still confusing to me. To help us understand can you specifically
tell if you see anything in pg_stat_user_tables and pg_stat_user_indexes?

I attached the output of vacuum verbose command.

Seems like a lot of your tables have bloats

As for the pg_stat_activity, I have no "idle in transaction" records
there, but I do have some in "idle" state, that don't disappear. Perhaps
this means some sessions are not closed? I attached the query result as
activity.txt.

I also have a few "sending cancel to blocking autovacuum" and "canceling
autovacuum task" messages in syslog.

Can you share some of these log files?

Sample query explain analyze. This was ran after vacuum analyze of the
entire database.

explain analyze SELECT col1, col2, col3, col4, col5 FROM ( table84
table84 LEFT JOIN table57 table57 ON table84.col7 = table57.col7 ) LEFT
JOIN table19 table19 ON table84.col7 = table19.col7;

QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------
Hash Right Join (cost=46435.43..108382.29 rows=189496 width=79) (actual
time=4461.686..13457.233 rows=5749 loops=1)
Hash Cond: (table57.col7 = table84.col7)
-> Seq Scan on table57 table57 (cost=0.00..49196.63 rows=337963
width=57) (actual time=0.040..8981.438 rows=6789 loops=1)
-> Hash (cost=42585.73..42585.73 rows=189496 width=38) (actual
time=4447.731..4447.731 rows=5749 loops=1)
Buckets: 16384 Batches: 2 Memory Usage: 203kB
-> Hash Right Join (cost=18080.66..42585.73 rows=189496
width=38) (actual time=1675.223..4442.046 rows=5749 loops=1)
Hash Cond: (table19.col7 = table84.col7)
-> Seq Scan on table19 table19 (cost=0.00..17788.17
rows=187317 width=26) (actual time=0.007..2756.501 rows=5003 loops=1)
-> Hash (cost=14600.96..14600.96 rows=189496 width=20)
(actual time=1674.940..1674.940 rows=5749 loops=1)
Buckets: 32768 Batches: 2 Memory Usage: 159kB
-> Seq Scan on table84 table84
(cost=0.00..14600.96 rows=189496 width=20) (actual time=0.059..1661.482
rows=5749 loops=1)
Total runtime: 13458.301 ms
(12 rows)

You have a lot of issues with this plan-
- The statistics is not updated
- There is a lot of hash join, sequential scan implying you don't have
proper indexes or those are not useful (meaning your indexes are bloated
too, consider reindexing them)

Thank you again for your advice and I hope that with your help I'll be
able to solve this issue.

I checked a VACUUM log, and it looks well - so maybe you run VACUUM with
too small frequency and now some tables needs VACUUM FULL, and some indexes
needs REINDEX.

When your read 5000 rows 2sec, then some some is strange - probably too
less data density in data file.

If you do some massive cleaning, more than 30%, it is good idea to run
VACUUM FULL, if it is possible manually. Or if you can - use partitioning -
then you drop a partition without negative effect on other data.

Regards

Pavel