Re: very slow queries and ineffective vacuum

2015-07-03 7:18 GMT+02:00 Sameer Kumar <sameer(dot)kumar(at)ashnik(dot)com>:

>
>
> 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.
>>
>
I checked a VACUUM log, and it looks well - so maybe you run VACUUM with
too small frequency and now some tables needs VACUUM FULL, and some indexes
needs REINDEX.

When your read 5000 rows 2sec, then some some is strange - probably too
less data density in data file.

If you do some massive cleaning, more than 30%, it is good idea to run
VACUUM FULL, if it is possible manually. Or if you can - use partitioning -
then you drop a partition without negative effect on other data.

Regards

Pavel

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