Re: very slow queries and ineffective vacuum

On Wed, Jul 1, 2015 at 4:51 AM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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)?

>
> Regards
>
> Pavel Stehule
>
> 2015-06-30 14:57 GMT+02:00 Lukasz Wrobel <
> lukasz(dot)wrobel(at)motorolasolutions(dot)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
>>
>
>