Hi, On 19.10.2013 02:53, sparikh wrote: > 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. I see no attachment, so it got lost somehow. Can you post the query on explain.depesz.com and the query inline? Assuming it's not a horror-like story produced by some ORM, in that case an attachment is probably appropriate, not to cause hear-attack to casual readers. > Please note that query has multiple unions only the first query on top is > causing the performance issue. So, if you run only the problematic part, is it faster / slower. If it's equally slow then we can deal with a simpler query, right? > Transaction search is one of the feature in our Admin user interface(web > portal) where user can search for the transactions against our OLTP > database. The attached query is generated dynamically by the application. I.e. it's a form in the UI, and the application generates query matching some fields in a form. >> (3) The load on standby does not seem to be issue, because with >> absolutely no load the query takes long and most of the time >> returned with the conflict error. > > Not suse I understand this. Are you saying that the standby is mostly > idle, i.e. the query seems to be stuck, and then fails with conflict > error most of the time? > > The standby is not idle all the time. What I meant was even with no user > activity or no active user sessions, if I issue the query directly from > pgadmin tool it takes for ever. IIRC I was unable to parse your description of what's happening on the standby (might be my fault, as I'm not a native speaker). I'm still not sure whether the query is stuck or only processing the data very slowly. Anyway, instead of describing what's happening, could you collect some data using vmstat/iostat and post it here? Something like vmstat 1 and iostat -x -k 1 collected while executing the query. Give us ~15-30 seconds of data for each, depending of how much it fluctuates. Another option is to use 'perf' to monitor the backend executing your query. Something like perf record -g -a -p PID for a reasonable time, and then 'perf report' (there's like a zillion of options available for perf, but this should give you some idea where most of the time is spent). If you have strong stomach, you might even use strace ... > Hardware settings both primary and secondary : > =================================== > > Red Hat Enterprise Linux Server release 5.5 (Tikanga) > Linux 2.6.18-194.26.1.el5 x86_64 > 4 CPUs > 16 GB RAM > Intel Xeon OK. But that's only the HW+OS. What about the basic database parameters that you have mentioned checking? Say, shared buffers, work mem and such things? > Postgresql Version: > ================= > "PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 > 20080704 (Red Hat 4.1.2-51), 64-bit" Any particular reason why you're running a minor version that's 2 years old and there were numerous bugs fixed since then? Including a serious security issue in 9.1.9. Please, plan an update to 9.1.10 ASAP. It might easily be the case that you're hitting a bug that was already fixed a long time ago. > 6) After we recovered standby it was fine for few weeks and then >> again started slowing down. > > Was it slowing down gradually, or did it start failing suddenly? > > Honestly speaking I do not exactly, when users started reporting the issue I > started looking into it. But the performance was good in September and > somewhere in October it started slowing down. I guess it was gradual. There > were no code change in the application or major change in the data volume. IMHO that's pretty important fact. If your monitoring or (at least) logs can't give you an answer, then you should improve that. At least start logging slow queries and deploy at least some very basic monitoring (e.g. Munin is quite simple and the PostgreSQL plugin, among other things, collects data about replication conflicts, which might be the culprit here). > Hope this helps. Please let me know if you need any other details. regards Tomas -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
