Re: [PERFORM] Hot Standby performance issue

[sparikh]

 I will try to request both perf and strace to be installed. But I am 
> not quite sure as the VMs are managed by third party. Will keep you 
> posted... 

What do you mean by VM? Is this a virtualized environment or bare hardware? 

Yes, they are virtualized environments. 

Sorry about the confusion. But I was just telling from based on the explain
plan report. e.g at the bottom of explain plan report it says "Total
runtime: 1698.453 ms" (This is with analyze option).

But from the client perspective (either run from pgadmin or directly from
the server command line) it takes more that 20 min to display the output.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Hot-Standby-performance-issue-tp5774673p5775550.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Hot Standby performance issue

[Tomas Vondra]

On 22.10.2013 23:50, sparikh wrote:
> Sorry, it was typo from my side. I meant strace only.

OK.

> 
> I will try to request both perf and strace to be installed. But I am
> not quite sure as the VMs are managed by third party. Will keep you
> posted...

What do you mean by VM? Is this a virtualized environment or bare hardware?

> 
> The main thing puzzling to me is Explain Plan with Analyze takes
> couple of secs to execute the operation but in reality it runs for
> more than 20 mins.

So, now I'm getting confused. Can you please provide timings for each
case. I.e. how long it takes to execute

1) plain query
2) explain query
3) explain analyze query

Execute each of those a couple of times, and let us know about
significant variations.

It should always be

   EXPLAIN ANALYZE >= query >= EXPLAIN

If you're reporting you see "EXPLAIN ANALYZE < query" then I find that
really strange.

Tomas


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Hot Standby performance issue

[Tomas Vondra]

On 22.10.2013 23:41, sparikh wrote:
>>From Primary:
> 
> relname   relpages
> pg_toast_176731812819
> pg_toast_17594161660
> pg_toast_17972121902
> pg_toast_1758777190
> pg_toast_1853729108
> pg_toast_1757826638
> pg_toast_17673_index  19984
> pg_toast_1786814911
> pg_toast_17594_index  2208
> pg_toast_1072246  1922
> pg_toast_17587_index  1510
> pg_toast_17972_index  1399
> pg_statistic  911
> pg_toast_18694883
> pg_toast_17578_index  375
> pg_attribute  336
> pg_toast_16475332
> pg_toast_18537_index  321
> pg_proc   233
> pg_depend_depender_index  176
> 
>>From Secondary :
> 
> relname   relpages
> pg_toast_176731812819
> pg_toast_17594161660
> pg_toast_17972121902
> pg_toast_1758777190
> pg_toast_1853729108
> pg_toast_1757826638
> pg_toast_17673_index  19984
> pg_toast_1786814911
> pg_toast_17594_index  2208
> pg_toast_1072246  1922
> pg_toast_17587_index  1510
> pg_toast_17972_index  1399
> pg_statistic  911
> pg_toast_18694883
> pg_toast_17578_index  375
> pg_attribute  336
> pg_toast_16475332
> pg_toast_18537_index  321
> pg_proc   233
> pg_depend_depender_index  176
> 
> Yes, result looks same both on primary and standby.

Yes. And it also shows that the really interesting tables (e.g.
pg_class) are not bloated.

Tomas


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Hot Standby performance issue

[sparikh]

Sorry, it was typo from my side. I meant strace only.

I will try to request both perf and strace to be installed. But I am not
quite sure as the VMs are managed by third party. Will keep you posted...

The main thing puzzling to me is Explain Plan with Analyze takes couple of
secs to execute the operation but in reality it runs for more than 20 mins.

Thanks.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Hot-Standby-performance-issue-tp5774673p5775529.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Hot Standby performance issue

[sparikh]

>From Primary:

relname relpages
pg_toast_17673  1812819
pg_toast_17594  161660
pg_toast_17972  121902
pg_toast_17587  77190
pg_toast_18537  29108
pg_toast_17578  26638
pg_toast_17673_index19984
pg_toast_17868  14911
pg_toast_17594_index2208
pg_toast_10722461922
pg_toast_17587_index1510
pg_toast_17972_index1399
pg_statistic911
pg_toast_18694  883
pg_toast_17578_index375
pg_attribute336
pg_toast_16475  332
pg_toast_18537_index321
pg_proc 233
pg_depend_depender_index176

>From Secondary :

relname relpages
pg_toast_17673  1812819
pg_toast_17594  161660
pg_toast_17972  121902
pg_toast_17587  77190
pg_toast_18537  29108
pg_toast_17578  26638
pg_toast_17673_index19984
pg_toast_17868  14911
pg_toast_17594_index2208
pg_toast_10722461922
pg_toast_17587_index1510
pg_toast_17972_index1399
pg_statistic911
pg_toast_18694  883
pg_toast_17578_index375
pg_attribute336
pg_toast_16475  332
pg_toast_18537_index321
pg_proc 233
pg_depend_depender_index176

Yes, result looks same both on primary and standby.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Hot-Standby-performance-issue-tp5774673p5775526.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Logic of lowering seq_page_cost for SSD?

[Josh Berkus]

All,

I've often seen people lower seq_page_cost for SSD access.  This has the
effect of raising CPU costs relative to the costs of disk access.
However, CPUs have also gotten lots faster, so I'm not sure that results
in a better cost balance.

Has anyone done extensive testing on this?  What did you find?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Hot Standby performance issue

[Tomas Vondra]

On 22.10.2013 02:00, sparikh wrote:
>> Do you suggest if I remove all the data files from /data/base folder 
>> of standby and again rebuild using rsync from primary ? do you see 
>> any issues there.? This is just to rule out any fragmentation on 
>> standby side. 
> 
> The EXPLAIN really should not do much I/O. I doubt it has anything to do 
> with fragmentation, so I doubt this is going to help. 
> 
> Actually I was referring to this in the context of addressing main
> underlying performance issue, not EXPLAIN. Sorry, I may not have
> communicated it correctly.
> 
> Even strance does not seem to be installed.

It's 'strace' (aka syscall trace), not 'strance'. Please install both
perf and strace and try to collect some information about the backend
executing the slow query. We're mostly speculating and we need the data.

Try perf first - it's basically a profiler and the results are usually
understandable. Even a simple "perf top" can give us a hint.

Strace is much more low-level and much more difficult to analyze.

> The filesytem type it shows to me ext3.

OK. Not the best filesystem IMHO, but I doubt it's related to the issue
we're discussing here.

Tomas


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Hot Standby performance issue

[Tomas Vondra]

On 22.10.2013 06:49, Magnus Hagander wrote:
> 
> On Oct 22, 2013 1:14 AM, "Tomas Vondra"  > wrote:
>>
>> On 22.10.2013 00:59, sparikh wrote:
>> > Yes, Expalin without Analyze is taking long. It is weird. In the
>> > pg_stat_activity Explain was the only query running. So server was
>> > almost idle. Using New relic interface I checked CPU was almost idle
>> > - around 10-20%. There were some IO activity - around 40-50%.
>> >
>> > I forgot to mention before I could run perf on command line even with
>> > root permission. It says command not found. May be utility is not
>> > installed or not enabled.
>>
>> Obviously you need to install it ... maybe ask your sysadmin to do that.
>>
>> > I have attached the snapshot of vmstat while explain was running in
>> > background. vmstat.txt
>> > 
>>
>> The vmstat clearly shows that ~1 CPU is waiting on I/O. Hmm, I'm really
>> wondering what's going on here - I can't think of a case where this
>> would happen with a plain EXPLAIN ...
> 
> Catalog bloat could make that happen. Though that should show up on
> the master as well, it could be that it's cached there and therefor
> only shows us to as cpu and not io and is therefore not noticed.

Maybe, but sparikh reported the query to be running for ~20 minutes.
That'd be hell of a bloat.

Sparikh, can you show us the size of system catalogs? Something like

   SELECT relname, relpages FROM pg_class
WHERE relname LIKE 'pg%'
ORDER BY relpages DESC LIMIT 20;

Should give the same results both on primary and standby.


Tomas


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance