Re: very slow queries and ineffective vacuum

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

On Tue, Jun 30, 2015 at 4:33 PM, William Dunn <dunnwjr(at)gmail(dot)com> wrote:

> On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel <
> lukasz(dot)wrobel(at)motorolasolutions(dot)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>*
>
> On Tue, Jun 30, 2015 at 4:27 PM, William Dunn <dunnwjr(at)gmail(dot)com> wrote:
>
>> Hello Lukasz,
>>
>> On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel <
>> lukasz(dot)wrobel(at)motorolasolutions(dot)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(dot)wrobel(at)motorolasolutions(dot)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>*
>>
>> On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel <
>> lukasz(dot)wrobel(at)motorolasolutions(dot)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
>>>
>>
>>
>