Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id B95E312B5666 for ; Thu, 5 Jul 2012 21:46:33 -0300 (ADT) Received: from mail-gh0-f174.google.com ([209.85.160.174]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Smwgp-0007Dd-PW for pgsql-performance@postgresql.org; Fri, 06 Jul 2012 00:46:33 +0000 Received: by ghrr11 with SMTP id r11so8086860ghr.19 for ; Thu, 05 Jul 2012 17:46:18 -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=p4Zi0ikdfnwjAMOdJeHnKI5VF7nSme8TKjOgjryVBuI=; b=VnGWPKUiOj2gulWA5o13Ii0xBsUolmHV+FzY8dfTsawBeI0tirT+VIMgTEgLcxpH9K bxSlfLBeGKmr6CsqAX9nB4ma4DaH7UU2RgfhKawLgylOvHqi++PRWeakq8uVxUUSxwyt jLEIFMbvFj/yPHrtrTACqMrzxUc7IQyxqQGKAIkLGEeF4HG1MWdRsjztZIiPV4SnV+wI EW/kIeigrI4quX4NFWkjoVOi2PrKuQRxVUZi6sUo6mWgPfRyu7unm+cflnhUTDW0ezbl 99ZOQpee/ckycCsILUE60jyff5DmKHGMCq2k7K7Mq8WfACvLRicLsMn7BXoGt4C0nGTM wPWA== Received: by 10.66.83.1 with SMTP id m1mr24224pay.6.1341535577472; Thu, 05 Jul 2012 17:46:17 -0700 (PDT) Received: from ayaki.localdomain (124-169-169-118.dyn.iinet.net.au. [124.169.169.118]) by mx.google.com with ESMTPS id rd7sm20705699pbc.70.2012.07.05.17.46.14 (version=SSLv3 cipher=OTHER); Thu, 05 Jul 2012 17:46:16 -0700 (PDT) Message-ID: <4FF63553.4080300@ringerc.id.au> Date: Fri, 06 Jul 2012 08:46:11 +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: Reza Taheri CC: "pgsql-performance@postgresql.org" Subject: Re: The need for clustered indexes to boost TPC-V performance References: <66CE997FB523C04E9749452273184C6C137CB88A86@exch-mbx-113.vmware.com> <4FF3D80F.9040408@ringerc.id.au> <66CE997FB523C04E9749452273184C6C137CB88CDD@exch-mbx-113.vmware.com> In-Reply-To: <66CE997FB523C04E9749452273184C6C137CB88CDD@exch-mbx-113.vmware.com> Content-Type: multipart/alternative; boundary="------------090205030509000205080006" X-Gm-Message-State: ALoCoQm8s20/SAJOYaTkHIdX22ScQ0TJf4JbCizug14DJlohwPXMYJCAO4Mjvn8jBZ5BNRoHWQzG X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201207/28 X-Sequence-Number: 47209 This is a multi-part message in MIME format. --------------090205030509000205080006 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit First: Please do try 9.2 beta if you're upgrading from 8.4. It'll be out as a final release soon enough, and index only scans may make a /big/ difference for the problem you're currently having. Looking at your configuration I have a few comments, but it's worth noting that I don't work with hardware at that scale, and I'm more used to tuning I/O bottlenecked systems with onboard storage rather than CPU-bottlenecked ones on big SANs. Hopefully now that you've posted your configuration and setup there might be interest from others. If you're able to post an EXPLAIN ANALYZE or two for a query you feel is slow that certainly won't hurt. Using http://explain.depesz.com/ saves you the hassle of dealing with word-wrapping when posting them, btw. As for your config: I notice that your autovacuum settings are at their defaults. With heavy UPDATE / DELETE load this'll tend to lead to table and index bloat, so the DB has to scan more useless data to get what it needs. It also means table stats won't be maintained as well, potentially leading to poor planner decisions. The following fairly scary query can help identify bloat, as the database server doesn't currently have anything much built in to help you spot such issues: http://wiki.postgresql.org/wiki/Show_database_bloat It might be helpful to set effective_cache_size and effective_io_concurrency so Pg has more idea of the scale of your hardware. The defaults are very conservative - it's supposed to be easy for people to use for simple things without melting their systems, and it's expected that anyone doing bigger work will tune the database. http://www.postgresql.org/docs/9.1/static/runtime-config-resource.html It looks like you've already tweaked many of the critical points for big installs - your checkpoint_segments, wal_buffers, shared_buffers, etc. I lack the big hardware experience to know if they're appropriate, but they're not the extremely conservative defaults, which is a start. Your random_page_cost and seq_page_cost are probably dead wrong for a SAN with RAM and SSD cache in front of fast disks. Their defaults are for local uncached spinning HDD media where seeks are expensive. The typical advice on such hardware is to set them to something more like seq_page_cost = 0.1 random_page_cost = 0.15 - ie cheaper relative to the cpu cost, and with random I/O only a little more expensive than sequential I/O. What's right for your situation varies a bit based on DB size vs hardware size, etc; Greg discusses this more in his book. What isolation level do your transactions use? This is significant because of the move to true serializable isolation with predicate locking in 9.0; it made serializable transactions a bit slower in some circumstances in exchange for much stronger correctness guarantees. The READ COMMITTED default was unchanged. It also looks like you might not have seen the second part of my earlier reply: while PostgreSQL doesn't support covering indexes or clustered indexes at this point, 9.2 has added support for index-only scans, which are a half-way point of sorts. See: http://rhaas.blogspot.com.au/2011/10/index-only-scans-weve-got-em.html http://rhaas.blogspot.com.au/2010/11/index-only-scans.html http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a2822fb9337a21f98ac4ce850bb4145acf47ca27 If at all possible please see how your test is affected by this PostgreSQL 9.2 enhancement. It should make a big difference, and if it doesn't it's important to know why. (CC'd Robert Haas) As for the "invest" side - that's really a matter for EnterpriseDB, Command Prompt, Red Hat, and the other backers who're employing people to work on the DB. Consider asking on pgsql-hackers, too; if nothing else you'll get a good explanation of the current state and progress toward clustered indexes. Some links that may be useful to you are: http://wiki.postgresql.org/wiki/Todo Things that it'd be good to support/implement at some point. Surprisingly, covering/clustered indexes aren't on there or at least aren't easily found. It's certainly a much-desired feature despite its apparent absence from the TODO. http://wiki.postgresql.org/wiki/PostgreSQL_9.2_Development_Plan http://wiki.postgresql.org/wiki/PostgreSQL_9.2_Open_Items -- Craig Ringer --------------090205030509000205080006 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
First: Please do try 9.2 beta if you're upgrading from 8.4. It'll be out as a final release soon enough, and index only scans may make a big difference for the problem you're currently having.

Looking at your configuration I have a few comments, but it's worth noting that I don't work with hardware at that scale, and I'm more used to tuning I/O bottlenecked systems with onboard storage rather than CPU-bottlenecked ones on big SANs. Hopefully now that you've posted your configuration and setup there might be interest from others.

If you're able to post an EXPLAIN ANALYZE or two for a query you feel is slow that certainly won't hurt. Using http://explain.depesz.com/ saves you the hassle of dealing with word-wrapping when posting them, btw.

As for your config:

I notice that your autovacuum settings are at their defaults. With heavy UPDATE / DELETE load this'll tend to lead to table and index bloat, so the DB has to scan more useless data to get what it needs. It also means table stats won't be maintained as well, potentially leading to poor planner decisions. The following fairly scary query can help identify bloat, as the database server doesn't currently have anything much built in to help you spot such issues:

   http://wiki.postgresql.org/wiki/Show_database_bloat

It might be helpful to set effective_cache_size and effective_io_concurrency so Pg has more idea of the scale of your hardware. The defaults are very conservative - it's supposed to be easy for people to use for simple things without melting their systems, and it's expected that anyone doing bigger work will tune the database.

http://www.postgresql.org/docs/9.1/static/runtime-config-resource.html

It looks like you've already tweaked many of the critical points for big installs - your checkpoint_segments, wal_buffers, shared_buffers, etc. I lack the big hardware experience to know if they're appropriate, but they're not the extremely conservative defaults, which is a start.

Your random_page_cost and seq_page_cost are probably dead wrong for a SAN with RAM and SSD cache in front of fast disks. Their defaults are for local uncached spinning HDD media where seeks are expensive. The typical advice on such hardware is to set them to something more like seq_page_cost = 0.1  random_page_cost  = 0.15 - ie cheaper relative to the cpu cost, and with random I/O only a little more expensive than sequential I/O. What's right for your situation varies a bit based on DB size vs hardware size, etc; Greg discusses this more in his book.

What isolation level do your transactions use? This is significant because of the move to true serializable isolation with predicate locking in 9.0; it made serializable transactions a bit slower in some circumstances in exchange for much stronger correctness guarantees. The READ COMMITTED default was unchanged.



It also looks like you might not have seen the second part of my earlier reply:


while PostgreSQL doesn't support covering indexes or clustered indexes at this point, 9.2 has added support for index-only scans, which are a half-way point of sorts. See:

  http://rhaas.blogspot.com.au/2011/10/index-only-scans-weve-got-em.html
  http://rhaas.blogspot.com.au/2010/11/index-only-scans.html
  http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a2822fb9337a21f98ac4ce850bb4145acf47ca27

If at all possible please see how your test is affected by this PostgreSQL 9.2 enhancement. It should make a big difference, and if it doesn't it's important to know why.

(CC'd Robert Haas)



As for the "invest" side - that's really a matter for EnterpriseDB, Command Prompt, Red Hat, and the other backers who're employing people to work on the DB. Consider asking on pgsql-hackers, too; if nothing else you'll get a good explanation of the current state and progress toward clustered indexes.

Some links that may be useful to you are:

  http://wiki.postgresql.org/wiki/Todo
  Things that it'd be good to support/implement at some point. Surprisingly, covering/clustered indexes aren't on there or at least aren't easily found. It's certainly a much-desired feature despite its apparent absence from the TODO.

  http://wiki.postgresql.org/wiki/PostgreSQL_9.2_Development_Plan
  http://wiki.postgresql.org/wiki/PostgreSQL_9.2_Open_Items

--
Craig Ringer




--------------090205030509000205080006--