Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 5015BC32C6E for ; Fri, 23 Mar 2012 07:11:16 -0300 (ADT) Received: from rs133.luxsci.com ([72.32.115.26]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SB1Sk-0008Be-2q for pgsql-performance@postgresql.org; Fri, 23 Mar 2012 10:11:15 +0000 Received: from GNANAM (NSG-Static-233.156.72.182.airtel.in [182.72.156.233] (may be forged)) (authenticated bits=0) by rs133.luxsci.com (8.13.8/8.13.8) with ESMTP id q2NAAPgK024230; Fri, 23 Mar 2012 05:10:57 -0500 Reply-To: From: "Gnanakumar" To: "'Merlin Moncure'" , "'Kevin Grittner'" Cc: References: <006301cd07fd$358ebe40$a0ac3ac0$@com> <4F6AFB5902000025000465C7@gw.wicourts.gov> In-Reply-To: Subject: Re: Write workload is causing severe slowdown in Production Date: Fri, 23 Mar 2012 15:40:05 +0530 Organization: Zoniac Software Private Limited Message-ID: <006c01cd08dd$205af940$6110ebc0$@com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: Ac0IR3TdSgNI7NTlQq6f/nDhKJkY3gAkQD8w Content-Language: en-us X-Lux-Comment: Message q2NAAPgK024230 sent by user #22393 X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201203/159 X-Sequence-Number: 46609 First off, thank you *so much* for that detailed explanation comparing with a real-time application performance benchmark, which was really enlightening for me. > How are you handling concurrency? (Are you using FOR SHARE on your > SELECT statements? Are you explicitly acquiring table locks before > modifying data? Etc.) You might be introducing blocking somehow. No, actually am not explicitly locking any tables -- all are *simple* select, update, insert statements only. > In particular, I recommend that you *never* leave transactions open > or hold locks while waiting for user response or input. Again, we're not leaving any transaction opens until for any user responses, etc. > When you hit that issue, there is not a continual slowdown -- > queries which normally run very fast (a small fraction of a second) > may periodically all take tens of seconds. Is that the pattern > you're seeing? Yes, you're correct. Queries those normally run fast are becoming slow at the time of this slowdown. > Besides the outdated PostgreSQL release and possible blocking, I > would be concerned if you are using any sort of ORM for the update > application. You want to watch that very closely because the > default behavior of many of them does not scale well. There's > usually a way to get better performance through configuration and/or > bypassing automatic query generation for complex data requests. Am not able to understand above statements (...any sort of ORM for the update application ...) clearly. Can you help me in understanding this?