Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UYFQs-0002QR-RG for pgsql-performance@arkaria.postgresql.org; Fri, 03 May 2013 12:49:51 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UYFQr-0001vm-Qo for pgsql-performance@arkaria.postgresql.org; Fri, 03 May 2013 12:49:49 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UYFQr-0001vd-0J for pgsql-performance@postgresql.org; Fri, 03 May 2013 12:49:49 +0000 Received: from mxin.ulb.ac.be ([164.15.128.112]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UYFQm-0001gJ-Ol for pgsql-performance@postgresql.org; Fri, 03 May 2013 12:49:48 +0000 X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: AuIBAAaxg1GkD30E/2dsb2JhbAANOQcDgz6JGrYVgRCDEwEBAQMBVSMGCwsYCRYPCQMCAQIBRRMIAQGIAq9XikGHPI1rC4EhFxGDQgOTXIlRjj8 Received: from bebif01.ulb.ac.be (HELO [192.168.0.50]) ([164.15.125.4]) by smtp.ulb.ac.be with ESMTP/TLS/DHE-RSA-CAMELLIA256-SHA; 03 May 2013 14:49:41 +0200 Message-ID: <5183B264.3050106@ulb.ac.be> Date: Fri, 03 May 2013 14:49:40 +0200 From: Julien Cigar User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:17.0) Gecko/20130404 Thunderbird/17.0.5 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Hardware suggestions for maximum read performance References: <3C9B31A5-F8FE-4161-A374-971D5DF69E8A@mbari.org> In-Reply-To: <3C9B31A5-F8FE-4161-A374-971D5DF69E8A@mbari.org> Content-Type: multipart/alternative; boundary="------------000200080209060205010507" X-Pg-Spam-Score: -6.8 (------) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------000200080209060205010507 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 05/03/2013 01:11, Mike McCann wrote: > Hello, > Hello, > We are in the fortunate situation of having more money than time to > help solve our PostgreSQL 9.1 performance problem. > > Our server hosts databases that are about 1 GB in size with the > largest tables having order 10 million 20-byte indexed records. The > data are loaded once and then read from a web app and other client > programs. Some of the queries execute ORDER BY on the results. There > are typically less than a dozen read-only concurrent connections to > any one database. > I would first check the spurious queries .. 10 millions rows isn't that huge. Perhaps you could paste your queries and an explain analyze of them ..? You could also log slow queries and use the auto_explain module > SELECTs for data are taking 10s of seconds. We'd like to reduce this > to web app acceptable response times (less than 1 second). If this is > successful then the size of the database will grow by a factor of ten > - we will still want sub-second response times. We are in the process > of going through the excellent suggestions in the "PostgreSQL 9.0 High > Performance" book to identify the bottleneck (we have reasonable > suspicions that we are I/O bound), but would also like to place an > order soon for the dedicated server which will host the production > databases. Here are the specs of a server that we are considering with > a budget of $13k US: > > HP ProLiant DL360p Gen 8 > Dual Intel Xeon 2.4GHz 4-core E5-2609 CPUs > 64GB RAM > 2x146GB 15K SAS hard drives > 3x200GB SATA SLC SSDs > + the usual accessories (optical drive, rail kit, dual power supplies) > > Opinions? > > Thanks in advance for any suggestions you have. > > -Mike > > -- > Mike McCann > Software Engineer > Monterey Bay Aquarium Research Institute > 7700 Sandholdt Road > Moss Landing, CA 95039-9644 > Voice: 831.775.1769 Fax: 831.775.1736 http://www.mbari.org > -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. --------------000200080209060205010507 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
On 05/03/2013 01:11, Mike McCann wrote:
Hello,


Hello,

We are in the fortunate situation of having more money than time to help solve our PostgreSQL 9.1 performance problem.

Our server hosts databases that are about 1 GB in size with the largest tables having order 10 million 20-byte indexed records. The data are loaded once and then read from a web app and other client programs.  Some of the queries execute ORDER BY on the results. There are typically less than a dozen read-only concurrent connections to any one database.


I would first check the spurious queries .. 10 millions rows isn't that huge. Perhaps you could paste your queries and an explain analyze of them ..? You could also log slow queries and use the auto_explain module

SELECTs for data are taking 10s of seconds. We'd like to reduce this to web app acceptable response times (less than 1 second). If this is successful then the size of the database will grow by a factor of ten - we will still want sub-second response times.  We are in the process of going through the excellent suggestions in the "PostgreSQL 9.0 High Performance" book to identify the bottleneck (we have reasonable suspicions that we are I/O bound), but would also like to place an order soon for the dedicated server which will host the production databases. Here are the specs of a server that we are considering with a budget of $13k US:

HP ProLiant DL360p Gen 8
Dual Intel Xeon 2.4GHz 4-core E5-2609 CPUs
64GB RAM
2x146GB 15K SAS hard drives
3x200GB SATA SLC SSDs
+ the usual accessories (optical drive, rail kit, dual power supplies)

Opinions?

Thanks in advance for any suggestions you have.

-Mike

--
Mike McCann
Software Engineer
Monterey Bay Aquarium Research Institute
7700 Sandholdt Road
Moss Landing, CA 95039-9644
Voice: 831.775.1769  Fax: 831.775.1736 http://www.mbari.org 



-- 
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--------------000200080209060205010507--