[PERFORM] Cache performance decreases - Mailing list pgsql-performance

From Suren Arustamyan
Subject [PERFORM] Cache performance decreases
Date
Msg-id 1467027813.174771204@f391.i.mail.ru
Whole thread Raw
List 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
hugeperformance 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
queryjoining 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
butwith 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@inbox.ru

pgsql-performance by date:

Previous
From: Imre Samu
Date:
Subject: Re: can't explain commit performance win7 vs linux : 8000/s vs 419/s
Next
From: George Neuner
Date:
Subject: testing - ignore