Re: very slow queries and ineffective vacuum

On Thu, Jul 2, 2015 at 9:57 PM Lukasz Wrobel <
lukasz(dot)wrobel(at)motorolasolutions(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>