Re: very slow queries and ineffective vacuum - Mailing list pgsql-general

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
usedin 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
(asper df and top). 
>
>         Are you using the default PostgreSQL configuration settings, or have you custom tuned them? The default
settingsare targeted for wide compatibility and are not 
>         optimized for performance. If PostgreSQL is performing badly and using a small amount of system resources it
islikely 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
thatdatabase 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
hibernateselect 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_bloatshows 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
requiredtime. 
>
>             The only thing that helps fix the situation seems to be vacuum full of the entire database. Regular
vacuumdoesn'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
helpas 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
(asper df and top). 
>
>             Is there any good tool that will monitor the queries and generate a report with useful information on
whatmight 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'swrong 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
wasused 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