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

On Thu, 2 Jul 2015 12:58:18 +0200
Lukasz Wrobel <lukasz.wrobel@motorolasolutions.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.
>
> 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.
>
> I attached the output of vacuum verbose command.
>
> 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.
>
> Sample query explain analyze. This was ran after vacuum analyze of the
> entire database.

The analyze doesn't seem to be working terribly well. Looking at the
explain, it expects 337963 rows in table57, but there are only 6789.
There are similar discrepencies with table19 and table84.

I don't know if indexes are your problem. Those three tables are pretty
small, so the sequential scans should be pretty quick (probably faster
than index scans, since it looks like most of the rows are returned from
all the tables.

I'm somewhat confused by your description of the situation. Is the performance
problem happening on the virtual machine? Because VMs are notorious for
being on oversubscribed hosts and exhibiting performance far below what
is expected. It would be worthwhile to do some disk speed and CPU speed tests
on the VM to see what kind of performance it's actually capable of ... if
the VM is performing poorly, there's not much you can do with PostgreSQL
to improve things.

> 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)
>
> Thank you again for your advice and I hope that with your help I'll be able
> to solve this issue.
>
> Best regards.
> Lukasz


--
Bill Moran