From: Luke Lonergan <llonergan@greenplum.com>
To: Luke Lonergan <llonergan@greenplum.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Hardware/OS recommendations for large databases (
Date: Thu, 24 Nov 2005 09:07:40 -0800
Message-ID: <BFAB335C.14684%llonergan@greenplum.com> (raw)
In-Reply-To: <BFAAB702.1460D%llonergan@greenplum.com>
The same 12.9GB distributed across 4 machines using Bizgres MPP fits into
I/O cache. The interesting result is that the query "select count(1)" is
limited in speed to 280 MB/s per CPU when run on the lineitem table. So
when I run it spread over 4 machines, one CPU per machine I get this:
======================================================
Bizgres MPP, 4 data segments, 1 per 2 CPUs
======================================================
llonergan=# explain select count(1) from lineitem;
QUERY PLAN
----------------------------------------------------------------------------
----------
Aggregate (cost=582452.00..582452.00 rows=1 width=0)
-> Gather Motion (cost=582452.00..582452.00 rows=1 width=0)
-> Aggregate (cost=582452.00..582452.00 rows=1 width=0)
-> Seq Scan on lineitem (cost=0.00..544945.00 rows=15002800
width=0)
(4 rows)
llonergan=# \timing
Timing is on.
llonergan=# select count(1) from lineitem;
count
----------
59986052
(1 row)
Time: 12191.435 ms
llonergan=# select count(1) from lineitem;
count
----------
59986052
(1 row)
Time: 11986.109 ms
llonergan=# select count(1) from lineitem;
count
----------
59986052
(1 row)
Time: 11448.941 ms
======================================================
That's 12,937 MB in 11.45 seconds, or 1,130 MB/s. When you divide out the
number of Postgres instances (4), that's 283MB/s per Postgres instance.
To verify that this has nothing to do with MPP, I ran it in a special
internal mode on one instance and got the same result.
So - we should be able to double this rate by running one segment per CPU,
or two per host:
======================================================
Bizgres MPP, 8 data segments, 1 per CPU
======================================================
llonergan=# select count(1) from lineitem;
count
----------
59986052
(1 row)
Time: 6484.594 ms
llonergan=# select count(1) from lineitem;
count
----------
59986052
(1 row)
Time: 6156.729 ms
llonergan=# select count(1) from lineitem;
count
----------
59986052
(1 row)
Time: 6063.416 ms
======================================================
That's 12,937 MB in 11.45 seconds, or 2,134 MB/s. When you divide out the
number of Postgres instances (8), that's 267MB/s per Postgres instance.
So, if you want to "select count(1)", using more CPUs is a good idea! For
most complex queries, having lots of CPUs + MPP is a good combo.
Here is an example of a sorting plan - this should probably be done with a
hash aggregation, but using 8 CPUs makes it go 8x faster:
- Luke
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: llonergan@greenplum.com
Subject: Re: Hardware/OS recommendations for large databases (
In-Reply-To: <BFAB335C.14684%llonergan@greenplum.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox