[PERFORM] Cache performance decreases

From: Suren Arustamyan <suren-a(at)inbox(dot)ru>
To: pgsql-performance(at)postgresql(dot)org <pgsql-performance(at)postgresql(dot)org>
Subject: [PERFORM] Cache performance decreases
Date: 2016-06-27 11:43:33
Message-ID: 1467027813.174771204@f391.i.mail.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello Guys,

I found very strange behavior on one of the master-slave clusters.

In case i'm running query several times in the same session i'm getting performance increase on the second request and huge performance decrease starting from 6 request. Behavior in 100% situations repeatable but only for this type of query. Issue happens on both master and slave systems. There is no such issue on other clusters.

Query:
SELECT * FROM get_results(1, 1, 1, '1', '1234567890', '1234567890', 2*get_rate(26, 26, now()), ARRAY[1], '{}', 13, ARRAY[1, 2, 3], ARRAY[1, 2, 3]) WHERE a AND b AND c AND d AND NOT e AND f

Query is as simple as running a procedure with additional filters afterwards. Procedure inside calls pretty complex query joining 7-8 tables and several side-procedures. get_result returns ~ 30 columns and ~15-30 rows  as a result.

Explain shows interesting picture.

For the first call:

Rows Removed by Filter: 14
Buffers: shared hit=7599
Planning time: 0.190 ms
Execution time: 86.083 ms

For the second-fifth calls:

Rows Removed by Filter: 14
Buffers: shared hit=4804
Planning time: 0.113 ms
Execution time: 57.835 ms

For the six and afterwards:

Rows Removed by Filter: 14
Buffers: shared hit=24474
Planning time: 0.073 ms
Execution time: 217.545 ms

So we can see consistent pattern between 'shared hit' and query performance.
I tried to change definition of the function from STABLE to VOLATILE and tried to set small and very big costs values but with no luck.

Server:
256Gb RAM
4 x Intel(R) Xeon(R) CPU E7- 8837 @ 2.67GHz
RAID 10 from 8 x TOSHIBA AL13SXB600N based on MegaRAID

postgresql.conf (9.4.7)
shared_buffers = 64578MB
work_mem = 64MB
maintenance_work_mem = 256MB
effective_cache_size = 129156MB

Any idea how I should investigate it further or what it could be?

Thanks in advance!

Suren Arustamyan
suren-a(at)inbox(dot)ru

Browse pgsql-performance by date

  From Date Subject
Next Message George Neuner 2016-06-28 14:10:07 testing - ignore
Previous Message Imre Samu 2016-06-25 22:27:57 Re: can't explain commit performance win7 vs linux : 8000/s vs 419/s