Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 03D19ABB2F9 for ; Sat, 14 Jul 2012 00:28: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 1Spt2E-0007Fw-4B for pgsql-performance@postgresql.org; Sat, 14 Jul 2012 03:28:50 +0000 Received: by pbbrp8 with SMTP id rp8so6487955pbb.19 for ; Fri, 13 Jul 2012 20:28:31 -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=ZuQk8wBel4qsi2mk260wBOTvouUPicd7zobagdzzKAA=; b=ckcAfO/2SpAh7FWlsrw4y2n2TPvfnhPuqP9yHhaCnriiqkF7/HlULC4POTJMXVUJEb H4BNdzFn7AAJRBo2+O98QbfcyehpxZN6hGFqECFPQ/K7ikNb+ihxwysRfUajsZIMOGsG iuROBUOwMB7C4AnKFQJponZV4tuplnc4NNky8EpWtwdy9y8mVmLSlDVNG/HessMGr9uR wkUdb9dulBS0VuFXPKrK+fY3n1yXRLzl5ZsNpJ8iPFxTKIkddSwNwGjpck9cTuTOWMxw I7FH0WkkglJ3VFT4cuZwYxdCPTeyC4IkvGP+SBDXhrI0yxL1VW9gWkC9/jTPGd0J2tTX m0Ww== Received: by 10.66.74.3 with SMTP id p3mr6383719pav.49.1342236511555; Fri, 13 Jul 2012 20:28:31 -0700 (PDT) Received: from ayaki.localdomain (58-7-231-93.dyn.iinet.net.au. [58.7.231.93]) by mx.google.com with ESMTPS id oi5sm5918170pbb.39.2012.07.13.20.28.28 (version=SSLv3 cipher=OTHER); Fri, 13 Jul 2012 20:28:30 -0700 (PDT) Message-ID: <5000E75A.6030108@ringerc.id.au> Date: Sat, 14 Jul 2012 11:28:26 +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: Andy Halsall CC: pgsql-performance@postgresql.org Subject: Re: query overhead References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------050503010706020307020309" X-Gm-Message-State: ALoCoQm/WIXCs/VsViPpSgD8bTVLzEEl0Mqt3iZaBXnh0SfxaKprQHyJV+Yxk95T20/E6KOZHl9M X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201207/181 X-Sequence-Number: 47362 This is a multi-part message in MIME format. --------------050503010706020307020309 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 07/11/2012 07:46 PM, Andy Halsall wrote: > > I've written an Immutable stored procedure that takes no parameters > and returns a fixed value to try and determine the round trip overhead > of a query to PostgreSQL. Call to sp is made using libpq. We're all > local and using UNIX domain sockets. > PL/PgSQL or SQL stored proc? There's a definite calling overhead for PL/PgSQL compared to plain SQL functions. SQL functions in turn cost more than a direct statement. These costs aren't big. They're massively outweighed by any kind of disk access or any non-trivial query. They start to add up if you have a lot of procs that wrap a simple "SELECT * FROM x WHERE x.id = $1" though. > Client measures are suggesting ~150-200 microseconds to call sp and > get the answer back 0.0015 to 0.002 milliseconds? That's ... um ... fast. Presumably that's during a loop where your no-op is run repeatedly without connection setup costs, etc. > > ping to loopback returns in ~20 microseconds (I assume domain sockets > are equivalent). UNIX domain sockets are typically at least as fast and somewhat lower overhead. > So it looks like a local no-op overhead of at least 150 microseconds > which would leave us struggling. > Could someone please let me know if this is usual and if so where the > time's spent? > Short of getting a faster server, is there anything I can do to > influence this? I'm not sure how much a faster server would help with single query response time. It'll help with response to many parallel queries, but may not speed up a single query, especially a tiny lightweight one, particularly dramatically. The Xeon 7040: http://ark.intel.com/products/27226/Intel-Xeon-Processor-7040-(4M-Cache-3_00-GHz-667-MHz-FSB) is not the newest beast out there, but it's not exactly slow. Honestly, PostgreSQL's focus is on performance with bigger units of work, not on being able to return a response to a tiny query in microseconds. If you are converting an application that has microsecond response time requirements and hammers its database with millions of tiny queries, PostgreSQL is probably not going to be your best bet. If you're able to adapt the app to use set-oriented access patterns instead of looping, eg instead of (pseudocode): customer_ids = [ ... array from somewhere ... ] for customer_id in ids: c = SELECT c FROM customer c WHERE customer_id = :id if c.some_var: UPDATE customer SET c.some_other_var = 't' you can do: UPDATE customer SET c.some_other_var = [expression] WHERE [expression] then you'll get much better results from Pg. -- Craig Ringer --------------050503010706020307020309 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
On 07/11/2012 07:46 PM, Andy Halsall wrote:

I've written an Immutable stored procedure that takes no parameters and returns a fixed value to try and determine the round trip overhead of a query to PostgreSQL. Call to sp is made using libpq. We're all local and using UNIX domain sockets.

PL/PgSQL or SQL stored proc? There's a definite calling overhead for PL/PgSQL compared to plain SQL functions. SQL functions in turn cost more than a direct statement.

These costs aren't big. They're massively outweighed by any kind of disk access or any non-trivial query. They start to add up if you have a lot of procs that wrap a simple "SELECT * FROM x WHERE x.id = $1" though.

Client measures are suggesting ~150-200 microseconds to call sp and get the answer back
0.0015 to 0.002 milliseconds?

That's ... um ... fast. Presumably that's during a loop where your no-op is run repeatedly without connection setup costs, etc.

 
ping to loopback returns in ~20 microseconds (I assume domain sockets are equivalent).
UNIX domain sockets are typically at least as fast and somewhat lower overhead.

So it looks like a local no-op overhead of at least 150 microseconds which would leave us struggling.
Could someone please let me know if this is usual and if so where the time's spent?
Short of getting a faster server, is there anything I can do to influence this?

I'm not sure how much a faster server would help with single query response time. It'll help with response to many parallel queries, but may not speed up a single query, especially a tiny lightweight one, particularly dramatically.

The Xeon 7040:
     http://ark.intel.com/products/27226/Intel-Xeon-Processor-7040-(4M-Cache-3_00-GHz-667-MHz-FSB)
is not the newest beast out there, but it's not exactly slow.

Honestly, PostgreSQL's focus is on performance with bigger units of work, not on being able to return a response to a tiny query in microseconds. If you are converting an application that has microsecond response time requirements and hammers its database with millions of tiny queries, PostgreSQL is probably not going to be your best bet.

If you're able to adapt the app to use set-oriented access patterns instead of looping, eg instead of (pseudocode):

customer_ids = [ ... array from somewhere ... ]
for customer_id in ids:
    c = SELECT c FROM customer c WHERE customer_id = :id
    if c.some_var:
        UPDATE customer SET c.some_other_var = 't'

you can do:

UPDATE customer SET c.some_other_var = [expression] WHERE [expression]

then you'll get much better results from Pg.

--
Craig Ringer
--------------050503010706020307020309--