On Wed, Jul 1, 2015 at 4:51 AM Pavel Stehule <pavel.steh...@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)? > > 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 >> > >
