Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 54D282E0042 for ; Thu, 24 Apr 2008 10:59:33 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 36778-02 for ; Thu, 24 Apr 2008 10:59:23 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.225]) by developer.postgresql.org (Postfix) with ESMTP id 7E50B2E008F for ; Thu, 24 Apr 2008 10:59:23 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id r21so2734663wxc.28 for ; Thu, 24 Apr 2008 06:59:22 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; bh=2/CHthaApUb3RZTyloz7fV06RjSNvxknJprMY11CWrU=; b=TyK++1yIZXWkkCebiaFypgDWqZzbgiA0raEAiWOZARRvJKGBsSkCWYV2tEuJNGf4nWx1GwYThhWk2cOnNdRBW939B99+9COJzAvPpBypnk+ResBXGy1n4UJ793ejVlMAtXmHONcIL9/iN9KaPvYVmNeuStD31exb4OVSCJyt1xQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type; b=FC1UqX8Mhv6XCxEHmOMX+d+v/yAyvHluWQ3zSHyN8fabLOhAOWPr85WwUHgEQDD0MGOP4SIbfJ5K5xkQGTZi09E9OVgkp/QORkHk5Au0n8ymiQtdlGq7E3lSkOIVFi1lKLI87v9ir4HXlP7SN/+lfwwa3rIHTZQh+0vBvx6o6BU= Received: by 10.100.215.14 with SMTP id n14mr5382932ang.69.1209045562153; Thu, 24 Apr 2008 06:59:22 -0700 (PDT) Received: by 10.100.168.11 with HTTP; Thu, 24 Apr 2008 06:59:20 -0700 (PDT) Message-ID: Date: Thu, 24 Apr 2008 09:59:20 -0400 From: "Nikolas Everett" To: pgsql-performance@postgresql.org Subject: Question about disk IO an index use and seeking advice MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_11_9236393.1209045562147" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200804/357 X-Sequence-Number: 29850 ------=_Part_11_9236393.1209045562147 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I have a question about index us and IO and am seeking advice. We are running postgres 8.2. We have two big big tables. Our ~600,000,000 row table is changed very infrequently and is on a 12 disk software raid-6 for historical reasons using an LSI Logic / Symbios Logic SAS1068 PCI-X Fusion-MPT SAS Our ~50,000,000 row staging table is on a 12 disk hardware raid-10 using a Dell PowerEdge Expandable RAID controller 5. All of the rows in the staging table are changed at least once and then deleted and recreated in the bigger table. All of the staging table's indexes are on the raid-10. The postgres data directory itself is on the raid-6. I think all the disks are SATA 10Ks. The setup is kind of a beast. So my disk IO and index question. When I issue a query on the big table like this: SELECT column, count(*) FROM bigtable GROUP BY column ORDER BY count DESC When I run dstat to see my disk IO I see the software raid-6 consistently holding over 70M/sec. This is fine with me, but I generally don't like to do queries that table scan 600,000,000 rows. So I do: SELECT column, count(*) FROM bigtable WHERE date > '4-24-08' GROUP BY column ORDER BY count DESC When I run dstat I see only around 2M/sec and it is not consistent at all. So my question is, why do I see such low IO load on the index scan version? If I could tweak some setting to make more aggressive use of IO, would it actually make the query faster? The field I'm scanning has a .960858 correlation, but I haven't vacuumed since importing any of the data that I'm scanning, though the correlation should remain very high. When I do a similar set of queries on the hardware raid I see similar performance except the numbers are both more than doubled. Here is the explain output for the queries: SELECT column, count(*) FROM bigtable GROUP BY column ORDER BY count DESC "Sort (cost=74404440.58..74404444.53 rows=1581 width=10)" " Sort Key: count(*)" " -> HashAggregate (cost=74404336.81..74404356.58 rows=1581 width=10)" " -> Seq Scan on bigtable (cost=0.00..71422407.21 rows=596385921 width=10)" --------------- SELECT column, count(*) FROM bigtable WHERE date > '4-24-08' GROUP BY column ORDER BY count DESC "Sort (cost=16948.80..16948.81 rows=1 width=10)" " Sort Key: count(*)" " -> HashAggregate (cost=16948.78..16948.79 rows=1 width=10)" " -> Index Scan using date_idx on bigtable (cost=0.00..16652.77 rows=59201 width=10)" " Index Cond: (date > '2008-04-21 00:00:00'::timestamp without time zone)" So now the asking for advice part. I have two questions: What is the fastest way to copy data from the smaller table to the larger table? We plan to rearrange the setup when we move to Postgres 8.3. We'll probably move all the storage over to a SAN and slice the larger table into monthly or weekly tables. Can someone point me to a good page on partitioning? My gut tells me it should be better, but I'd like to learn more about why. Does anyone have experience migrating large databases to a SAN? I understand that it'll give me better fail over capabilities so long as the SAN itself doesn't go out, but are we going to be sacrificing performance for this? That doesn't even mention the cost.... Thanks so much for reading through all this, --Nik ------=_Part_11_9236393.1209045562147 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I have a question about index us and IO and am seeking advice.

We are running postgres 8.2.  We have two big big tables.  Our ~600,000,000 row table is changed very infrequently and is on a 12 disk software raid-6 for historical reasons using an  LSI Logic / Symbios Logic SAS1068 PCI-X Fusion-MPT SAS  Our ~50,000,000 row staging table is on a 12 disk hardware raid-10 using a Dell PowerEdge Expandable RAID controller 5.  All of the rows in the staging table are changed at least once and then deleted and recreated in the bigger table.  All of the staging table's indexes are on the raid-10.  The postgres data directory itself is on the raid-6.  I think all the disks are SATA 10Ks.  The setup is kind of a beast.

So my disk IO and index question.  When I issue a query on the big table like this:
SELECT    column, count(*)
FROM    bigtable
GROUP BY column
ORDER BY count DESC
When I run dstat to see my disk IO I see the software raid-6 consistently holding over 70M/sec.  This is fine with me, but I generally don't like to do queries that table scan 600,000,000 rows.  So I do:
SELECT    column, count(*)
FROM    bigtable
WHERE date > '4-24-08'
GROUP BY column
ORDER BY count DESC
When I run dstat I see only around 2M/sec and it is not consistent at all.

So my question is, why do I see such low IO load on the index scan version?  If I could tweak some setting to make more aggressive use of IO, would it actually make the query faster?  The field I'm scanning has a .960858 correlation, but I haven't vacuumed since importing any of the data that I'm scanning, though the correlation should remain very high.  When I do a similar set of queries on the hardware raid I see similar performance except  the numbers are both more than doubled.

Here is the explain output for the queries:
SELECT    column, count(*)
FROM    bigtable
GROUP BY column
ORDER BY count DESC
"Sort  (cost=74404440.58..74404444.53 rows=1581 width=10)"
"  Sort Key: count(*)"
"  ->  HashAggregate  (cost=74404336.81..74404356.58 rows=1581 width=10)"
"        ->  Seq Scan on bigtable (cost=0.00..71422407.21 rows=596385921 width=10)"
---------------
SELECT    column, count(*)
FROM    bigtable
WHERE date > '4-24-08'
GROUP BY column
ORDER BY count DESC
"Sort  (cost=16948.80..16948.81 rows=1 width=10)"
"  Sort Key: count(*)"
"  ->  HashAggregate  (cost=16948.78..16948.79 rows=1 width=10)"
"        ->  Index Scan using date_idx on bigtable (cost=0.00..16652.77 rows=59201 width=10)"
"              Index Cond: (date > '2008-04-21 00:00:00'::timestamp without time zone)"

So now the asking for advice part.  I have two questions:
What is the fastest way to copy data from the smaller table to the larger table?

We plan to rearrange the setup when we move to Postgres 8.3.  We'll probably move all the storage over to a SAN and slice the larger table into monthly or weekly tables.  Can someone point me to a good page on partitioning?  My gut tells me it should be better, but I'd like to learn more about why.
Does anyone have experience migrating large databases to a SAN?  I understand that it'll give me better fail over capabilities so long as the SAN itself doesn't go out, but are we going to be sacrificing performance for this?  That doesn't even mention the cost....

Thanks so much for reading through all this,

--Nik
------=_Part_11_9236393.1209045562147--