Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 5901679D56 for ; Thu, 12 Jul 2012 03:56:50 -0300 (ADT) Received: from mail-pb0-f46.google.com ([209.85.160.46]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SpDKS-0006DM-Mb for pgsql-performance@postgresql.org; Thu, 12 Jul 2012 06:56:50 +0000 Received: by pbbrp8 with SMTP id rp8so3272568pbb.19 for ; Wed, 11 Jul 2012 23:56:35 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:cc:subject :references:in-reply-to:content-type:x-gm-message-state; bh=ZtXZb5TNsfYOL/sdGNfV26wdzxyo20o01sRrW9hzKGw=; b=j5ZHRQD2Xe4yvw7G10TxNcNXGOTOryHnJrOlrIkxWl+xtLg9BBBD4un18AGcXMVNmb n34BlCTi5Yf8Db7wybmuaekURI/R97kv74u8K0mEJHjwU0AGoCSFU4dzTc+VktdDPOe1 0CkUdJ1CDxFurkP5sv6rtZkKhEfrGkbXZ35u/qQ2G3T3uyXfOGFxFymVXhW7TbzHGMa6 NYvy1bafcmio59jsRyi+5rASKL6sv8lMeO9B/TphRqrvqj7blqTQcohSbuJqS1FrF5ul 2Jc4GTpX9YDlc9Kk71qhwHKs3gP6YBXjKfH2+aIzXqhikCtLL06p1i/K8Mn0qUEt7ofW twNA== Received: by 10.68.189.104 with SMTP id gh8mr2736450pbc.152.1342076195433; Wed, 11 Jul 2012 23:56:35 -0700 (PDT) Received: from ayaki.localdomain (office1.postnewspapers.com.au. [150.101.171.128]) by mx.google.com with ESMTPS id hw6sm3270712pbc.73.2012.07.11.23.56.32 (version=SSLv3 cipher=OTHER); Wed, 11 Jul 2012 23:56:34 -0700 (PDT) Message-ID: <4FFE751D.3050105@ringerc.id.au> Date: Thu, 12 Jul 2012 14:56:29 +0800 From: Craig Ringer User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:13.0) Gecko/20120605 Thunderbird/13.0 MIME-Version: 1.0 To: Yan Chunlu CC: Albe Laurenz , "pgsql-performance@postgresql.org" Subject: Re: how could select id=xx so slow? References: <4FFB848D.7080900@ringerc.id.au> <4FFB9781.60601@ringerc.id.au> <4FFE17CF.1050601@ringerc.id.au> <4FFE346A.7030402@ringerc.id.au> In-Reply-To: Content-Type: multipart/alternative; boundary="------------090805060505000609020800" X-Gm-Message-State: ALoCoQlQqu85pWGpIlRRAjAbE6+/JOt2FSPnfcFiHXLPRBKK3p2VZR+uxRFbulkULk1jBCkH34s2 X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201207/152 X-Sequence-Number: 47333 This is a multi-part message in MIME format. --------------090805060505000609020800 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 07/12/2012 01:10 PM, Yan Chunlu wrote: > after check out the wiki page Maciek mentioned, turns out that heavy > connection also burden the disk hardly. > looks like I am in the vicious circle: > 1, slow query cause connection blocked so the client request more > connection. > 2, more connection cause high disk io and make even the simplest query > slow and block. While true, you can often control this by making sure you don't completely overload your hardware, queuing queries instead of running them all at once. You may still discover that your hardware can't cope with the workload in that your queues may just keep on getting deeper or time out. In that case, you certainly need to optimise your queries, tune your database, and/or get bigger hardware. -- Craig Ringer --------------090805060505000609020800 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
On 07/12/2012 01:10 PM, Yan Chunlu wrote:
after check out the wiki page Maciek mentioned, turns out that heavy connection also burden the disk hardly.
looks like I am in the vicious circle:
1, slow query cause connection blocked so the client request more connection. 
2, more connection cause high disk io and make even the simplest query slow and block.

While true, you can often control this by making sure you don't completely overload your hardware, queuing queries instead of running them all at once.

You may still discover that your hardware can't cope with the workload in that your queues may just keep on getting deeper or time out. In that case, you certainly need to optimise your queries, tune your database, and/or get bigger hardware.

--
Craig Ringer
--------------090805060505000609020800--