Hi,
Yes, you are right. The table is the biggest one . Please find below the
information you requested. I agree the fact that autovacuum ran on this
table would fix the performance issue on standby does not sound very
convincing. But that is the only thing I could correlate when the query on
Table statistics I sent before were from primary. Following are from standby.
Index Tuples Fetched25910277
Tuples Inserted 0
Tuples Updated 0
Tuples Deleted 0
Tuples HOT Updated 0
Live Tuples 0
Dead Tuples 0
Heap Blocks Read
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_index
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
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
Stupid question - when you say that a query is fast on primary but slow
on standby, are you referring to exactly the same query, including
parameter values?
Yes . It is exactly and exactly the same query with the same parameters.
Yes, it sounds stupid but that is what happening. Though plan
Yes, both Explain and Explain Analyse are taking time. As you suggested I set
the lock parameters, but no locks are observed. Also checked
pg_stat_activity and none of the sessions are either waiting are blocked.
I agree we must upgrade to latest version (9.1.10), but unfortunately kind
of
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
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
Thanks so much Tomas and Kevin for your valuable inputs. I am getting very
good response from this forum and learning so many new stuffs. I will try
all those options and will let you update .
standby_performance_issue.rar
Anybody has any idea, or pointer ? This is a high priority issue I have
resolve at work. Any help would be of great help.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Hot-Standby-performance-issue-tp5774673p5775103.html
Sent from the PostgreSQL - performance mailing
Hi Tomas,
Thanks so much for your response and sorry for not providing the enough
details.
I have attached the zip file which has query,explain plan and database
parameter settings for both primary and secondary.
Please note that query has multiple unions only the first query on top is
causing
Hi, This is my first post ever in the Postgres forum. I am relatively new to
Postgres and coming from oracle background. We have hot stand by setup to
serve mainly the read only queries. Past few days we have been facing a
performance issues on one of the transaction search. The search mainly
13 matches
Mail list logo
