Re: [GENERAL] very slow queries and ineffective vacuum

2015-07-02 Thread Pavel Stehule 
2015-07-03 7:18 GMT+02:00 Sameer Kumar :

>
>
> On Thu, Jul 2, 2015 at 9:57 PM Lukasz Wrobel <
> lukasz.wro...@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


>
>> 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
>>
>

Re: [GENERAL] very slow queries and ineffective vacuum

2015-07-02 Thread Sameer Kumar 
On Thu, Jul 2, 2015 at 9:57 PM Lukasz Wrobel <
lukasz.wro...@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.
>
> 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
>

Re: [GENERAL] very slow queries and ineffective vacuum

2015-07-02 Thread Bill Moran 
On Thu, 2 Jul 2015 12:58:18 +0200
Lukasz Wrobel  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

Re: [GENERAL] very slow queries and ineffective vacuum

2015-07-02 Thread Melvin Davidson 
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.wro...@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.

Re: [GENERAL] very slow queries and ineffective vacuum

2015-07-01 Thread Sameer Kumar 
On Wed, Jul 1, 2015 at 4:51 AM Pavel Stehule 
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)?


>
> Regards
>
> Pavel Stehule
>
> 2015-06-30 14:57 GMT+02:00 Lukasz Wrobel <
> lukasz.wro...@motorolasolutions.com>:
>
>> 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
>>
>
>

Re: [GENERAL] very slow queries and ineffective vacuum

2015-07-01 Thread Sameer Kumar 
On Wed, Jul 1, 2015 at 3:37 AM Lukasz Wrobel <
lukasz.wro...@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  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.


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

Re: [GENERAL] very slow queries and ineffective vacuum

2015-06-30 Thread Melvin Davidson 
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 
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 Herrerahttp://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.

Re: [GENERAL] very slow queries and ineffective vacuum

2015-06-30 Thread Alvaro Herrera 
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 Herrerahttp://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

Re: [GENERAL] very slow queries and ineffective vacuum

2015-06-30 Thread William Dunn 
Jerry,

On Tue, Jun 30, 2015 at 5:31 PM, Jerry Sievers 
 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 *

On Tue, Jun 30, 2015 at 5:31 PM, Jerry Sievers 
wrote:

> William Dunn  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  wrote:
> >
> > On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel <
> lukasz.wro...@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 
> wrote:
> >
> > Hello Lukasz,
> >
> > On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel <
> lukasz.wro...@motorolasolutions.com> wrote:
> >
> > There doesn't seem to be any issues wit

Re: [GENERAL] very slow queries and ineffective vacuum

2015-06-30 Thread Jerry Sievers 
William Dunn  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  wrote:
>
> On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel 
>  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  wrote:
>
> Hello Lukasz,
>
> On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel 
>  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 
>  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 
>  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 connect

Re: [GENERAL] very slow queries and ineffective vacuum

2015-06-30 Thread Pavel Stehule 
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.wro...@motorolasolutions.com>:

> 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
>

Re: [GENERAL] very slow queries and ineffective vacuum

2015-06-30 Thread William Dunn 
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 *

On Tue, Jun 30, 2015 at 4:33 PM, William Dunn  wrote:

> On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel <
> lukasz.wro...@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  wrote:
>
>> Hello Lukasz,
>>
>> On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel <
>> lukasz.wro...@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.wro...@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.wro...@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
>>>
>>
>>
>

Re: [GENERAL] very slow queries and ineffective vacuum

2015-06-30 Thread William Dunn 
On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel <
lukasz.wro...@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  wrote:

> Hello Lukasz,
>
> On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel <
> lukasz.wro...@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.wro...@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.wro...@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
>>
>
>

Re: [GENERAL] very slow queries and ineffective vacuum

2015-06-30 Thread William Dunn 
Hello Lukasz,

On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel <
lukasz.wro...@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.wro...@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.wro...@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
>