Thread: Hardware suggestions for maximum read performance

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)

On Thu, May 2, 2013 at 5:11 PM, Mike McCann <mccann@mbari.org> wrote:
> 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.
>
> 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)

If your DB is 1G, and will grow to 10G then the IO shouldn't be any
problem, as the whole db should be cached in memory. I'd look at
whether or not you've got good query plans or not, and tuning them.
Things like setting random_cost to 1.something might be a good start,
and cranking up work mem to ~16M or so.

3x200GB suggests you want to use RAID5?

Perhaps you should just pick 2x200GB and set them to RAID1. With roughly
200GB of storage, that should still easily house your "potentially
10GB"-database with ample of room to allow the SSD's to balance the
writes. But you save the investment and its probably a bit faster with
writes (although your raid-card may reduce or remove the differences
with your workload).

You can then either keep the money or invest in faster cpu's. With few
concurrent connections the E5-2643 (also a quad core, but with 3.3GHz
cores rather than 2.4GHz) may be interesting.
Its obviously a bit of speculation to see whether that would help, but
it should speed up sorts and other in-memory/cpu-operations (even if
you're not - and never will be - cpu-bound right now).

Best regards,

Arjen

On 3-5-2013 1:11 Mike McCann wrote:
> 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.
>
> 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
>

Note that with linux (and a few other OSes) you can use RAID-1E
http://en.wikipedia.org/wiki/Non-standard_RAID_levels#RAID_1E
with an odd number of drives.

On Fri, May 3, 2013 at 12:16 AM, Arjen van der Meijden
<acmmailing@tweakers.net> wrote:
> 3x200GB suggests you want to use RAID5?
>
> Perhaps you should just pick 2x200GB and set them to RAID1. With roughly
> 200GB of storage, that should still easily house your "potentially
> 10GB"-database with ample of room to allow the SSD's to balance the writes.
> But you save the investment and its probably a bit faster with writes
> (although your raid-card may reduce or remove the differences with your
> workload).
>
> You can then either keep the money or invest in faster cpu's. With few
> concurrent connections the E5-2643 (also a quad core, but with 3.3GHz cores
> rather than 2.4GHz) may be interesting.
> Its obviously a bit of speculation to see whether that would help, but it
> should speed up sorts and other in-memory/cpu-operations (even if you're not
> - and never will be - cpu-bound right now).
>
> Best regards,
>
> Arjen
>
>
> On 3-5-2013 1:11 Mike McCann wrote:
>>
>> 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.
>>
>> 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
>>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance



--
To understand recursion, one must first understand recursion.

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.

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.

stoqs_march2013_s=# show work_mem;

 work_mem 

----------

 1MB

(1 row)

stoqs_march2013_s=# explain analyze select * from stoqs_measuredparameter order by datavalue;

                                                                QUERY PLAN                                                                

------------------------------------------------------------------------------------------------------------------------------------------

 Sort  (cost=541002.15..549456.68 rows=3381814 width=20) (actual time=6254.780..7244.074 rows=3381814 loops=1)

   Sort Key: datavalue

   Sort Method: external merge  Disk: 112424kB

   ->  Seq Scan on stoqs_measuredparameter  (cost=0.00..55359.14 rows=3381814 width=20) (actual time=0.011..354.385 rows=3381814 loops=1)

 Total runtime: 7425.854 ms

(5 rows)

stoqs_march2013_s=# set work_mem='355MB';

SET

stoqs_march2013_s=# explain analyze select * from stoqs_measuredparameter order by datavalue;

                                                                QUERY PLAN                                                                

------------------------------------------------------------------------------------------------------------------------------------------

 Sort  (cost=422106.15..430560.68 rows=3381814 width=20) (actual time=2503.078..2937.130 rows=3381814 loops=1)

   Sort Key: datavalue

   Sort Method: quicksort  Memory: 362509kB

   ->  Seq Scan on stoqs_measuredparameter  (cost=0.00..55359.14 rows=3381814 width=20) (actual time=0.016..335.745 rows=3381814 loops=1)

 Total runtime: 3094.601 ms

(5 rows)

HP ProLiant DL360p Gen 8

Dual Intel Xeon 3.3GHz 4-core E5-2643 CPUs

128GB PC3-12800 RAM

16x146GB 15K SAS hard drives

HP Smart Array P822/2GB FBWC controller + P420i w/ 2GB FBWC

+ the usual accessories (optical drive, rail kit, dual power supplies)

stoqs_march2013_s=# \d+ stoqs_measuredparameter

                                              Table "public.stoqs_measuredparameter"

     Column     |       Type       |                              Modifiers                               | Storage | Description 

----------------+------------------+----------------------------------------------------------------------+---------+-------------

 id             | integer          | not null default nextval('stoqs_measuredparameter_id_seq'::regclass) | plain   | 

 measurement_id | integer          | not null                                                             | plain   | 

 parameter_id   | integer          | not null                                                             | plain   | 

 datavalue      | double precision | not null                                                             | plain   | 

Indexes:

    "stoqs_measuredparameter_pkey" PRIMARY KEY, btree (id)

    "stoqs_measuredparameter_measurement_id_parameter_id_key" UNIQUE CONSTRAINT, btree (measurement_id, parameter_id)

    "stoqs_measuredparameter_datavalue" btree (datavalue)

    "stoqs_measuredparameter_measurement_id" btree (measurement_id)

    "stoqs_measuredparameter_parameter_id" btree (parameter_id)

Foreign-key constraints:

    "stoqs_measuredparameter_measurement_id_fkey" FOREIGN KEY (measurement_id) REFERENCES stoqs_measurement(id) DEFERRABLE INITIALLY DEFERRED

    "stoqs_measuredparameter_parameter_id_fkey" FOREIGN KEY (parameter_id) REFERENCES stoqs_parameter(id) DEFERRABLE INITIALLY DEFERRED

Has OIDs: no

stoqs_march2013_s=# explain analyze SELECT stoqs_measuredparameter.id,

stoqs_march2013_s-#        stoqs_parameter.name AS parameter__name,

stoqs_march2013_s-#        stoqs_parameter.standard_name AS parameter__standard_name,

stoqs_march2013_s-#        stoqs_measurement.depth AS measurement__depth,

stoqs_march2013_s-#        stoqs_measurement.geom AS measurement__geom,

stoqs_march2013_s-#        stoqs_instantpoint.timevalue AS measurement__instantpoint__timevalue,

stoqs_march2013_s-#        stoqs_platform.name AS measurement__instantpoint__activity__platform__name,

stoqs_march2013_s-#        stoqs_measuredparameter.datavalue AS datavalue,

stoqs_march2013_s-#        stoqs_parameter.units AS parameter__units

stoqs_march2013_s-# FROM stoqs_parameter p1,

stoqs_march2013_s-#      stoqs_measuredparameter

stoqs_march2013_s-#      INNER JOIN stoqs_measurement ON (stoqs_measuredparameter.measurement_id = stoqs_measurement.id)

stoqs_march2013_s-#      INNER JOIN stoqs_instantpoint ON (stoqs_measurement.instantpoint_id = stoqs_instantpoint.id)

stoqs_march2013_s-#      INNER JOIN stoqs_parameter ON (stoqs_measuredparameter.parameter_id = stoqs_parameter.id)

stoqs_march2013_s-#      INNER JOIN stoqs_activity ON (stoqs_instantpoint.activity_id = stoqs_activity.id)

stoqs_march2013_s-#      INNER JOIN stoqs_platform ON (stoqs_activity.platform_id = stoqs_platform.id)

stoqs_march2013_s-#      INNER JOIN stoqs_measuredparameter mp1 ON mp1.measurement_id = stoqs_measuredparameter.measurement_id

stoqs_march2013_s-# WHERE (p1.name = 'sea_water_sigma_t')

stoqs_march2013_s-#       AND (mp1.datavalue > 25.19)

stoqs_march2013_s-#       AND (mp1.datavalue < 26.01)

stoqs_march2013_s-#       AND (mp1.parameter_id = p1.id)

stoqs_march2013_s-#       AND (stoqs_instantpoint.timevalue <= '2013-03-17 19:05:06'

stoqs_march2013_s(#       AND stoqs_instantpoint.timevalue >= '2013-03-17 15:35:13'

stoqs_march2013_s(#       AND stoqs_parameter.name IN ('fl700_uncorr')

stoqs_march2013_s(#       AND stoqs_measurement.depth >= -1.88

stoqs_march2013_s(#       AND stoqs_platform.name IN ('dorado')

stoqs_march2013_s(#       AND stoqs_measurement.depth <= 83.57)

stoqs_march2013_s-# ORDER BY stoqs_activity.name ASC, stoqs_instantpoint.timevalue ASC;

                                                                                                QUERY PLAN                                                                       

                          

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------

 Sort  (cost=10741.41..10741.42 rows=1 width=1282) (actual time=770.211..770.211 rows=0 loops=1)

   Sort Key: stoqs_activity.name, stoqs_instantpoint.timevalue

   Sort Method: quicksort  Memory: 25kB

   ->  Hash Join  (cost=3002.89..10741.40 rows=1 width=1282) (actual time=770.200..770.200 rows=0 loops=1)

         Hash Cond: (stoqs_instantpoint.activity_id = stoqs_activity.id)

         ->  Nested Loop  (cost=2983.69..10722.19 rows=3 width=954) (actual time=770.036..770.036 rows=0 loops=1)

               ->  Nested Loop  (cost=2983.69..9617.36 rows=191 width=946) (actual time=91.369..680.072 rows=20170 loops=1)

                     ->  Hash Join  (cost=2983.69..8499.07 rows=193 width=842) (actual time=91.346..577.633 rows=20170 loops=1)

                           Hash Cond: (stoqs_measuredparameter.parameter_id = stoqs_parameter.id)

                           ->  Nested Loop  (cost=2982.38..8478.47 rows=4628 width=24) (actual time=91.280..531.408 rows=197746 loops=1)

                                 ->  Nested Loop  (cost=2982.38..4862.37 rows=512 width=4) (actual time=91.202..116.140 rows=20170 loops=1)

                                       ->  Seq Scan on stoqs_parameter p1  (cost=0.00..1.30 rows=1 width=4) (actual time=0.002..0.011 rows=1 loops=1)

                                             Filter: ((name)::text = 'sea_water_sigma_t'::text)

                                       ->  Bitmap Heap Scan on stoqs_measuredparameter mp1  (cost=2982.38..4854.40 rows=534 width=8) (actual time=91.194..109.846 rows=20170 loop

s=1)

                                             Recheck Cond: ((datavalue > 25.19::double precision) AND (datavalue < 26.01::double precision) AND (parameter_id = p1.id))

                                             ->  BitmapAnd  (cost=2982.38..2982.38 rows=534 width=0) (actual time=90.794..90.794 rows=0 loops=1)

                                                   ->  Bitmap Index Scan on stoqs_measuredparameter_datavalue  (cost=0.00..259.54 rows=12292 width=0) (actual time=62.769..62.769

 rows=23641 loops=1)

                                                         Index Cond: ((datavalue > 25.19::double precision) AND (datavalue < 26.01::double precision))

                                                   ->  Bitmap Index Scan on stoqs_measuredparameter_parameter_id  (cost=0.00..2719.38 rows=147035 width=0) (actual time=27.412..2

7.412 rows=34750 loops=1)

                                                         Index Cond: (parameter_id = p1.id)

                                 ->  Index Scan using stoqs_measuredparameter_measurement_id on stoqs_measuredparameter  (cost=0.00..6.98 rows=7 width=20) (actual time=0.008..0.

017 rows=10 loops=20170)

                                       Index Cond: (measurement_id = mp1.measurement_id)

                           ->  Hash  (cost=1.30..1.30 rows=1 width=826) (actual time=0.012..0.012 rows=1 loops=1)

                                 Buckets: 1024  Batches: 1  Memory Usage: 1kB

                                 ->  Seq Scan on stoqs_parameter  (cost=0.00..1.30 rows=1 width=826) (actual time=0.007..0.010 rows=1 loops=1)

                                       Filter: ((name)::text = 'fl700_uncorr'::text)

                     ->  Index Scan using stoqs_measurement_pkey on stoqs_measurement  (cost=0.00..5.78 rows=1 width=116) (actual time=0.004..0.004 rows=1 loops=20170)

                           Index Cond: (id = stoqs_measuredparameter.measurement_id)

                           Filter: ((depth >= (-1.88)::double precision) AND (depth <= 83.57::double precision))

               ->  Index Scan using stoqs_instantpoint_pkey on stoqs_instantpoint  (cost=0.00..5.77 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=20170)

                     Index Cond: (id = stoqs_measurement.instantpoint_id)

                     Filter: ((timevalue <= '2013-03-17 19:05:06-07'::timestamp with time zone) AND (timevalue >= '2013-03-17 15:35:13-07'::timestamp with time zone))

         ->  Hash  (cost=18.82..18.82 rows=30 width=336) (actual time=0.151..0.151 rows=7 loops=1)

               Buckets: 1024  Batches: 1  Memory Usage: 1kB

               ->  Hash Join  (cost=1.09..18.82 rows=30 width=336) (actual time=0.035..0.145 rows=7 loops=1)

                     Hash Cond: (stoqs_activity.platform_id = stoqs_platform.id)

                     ->  Seq Scan on stoqs_activity  (cost=0.00..16.77 rows=177 width=66) (actual time=0.005..0.069 rows=177 loops=1)

                     ->  Hash  (cost=1.07..1.07 rows=1 width=278) (actual time=0.014..0.014 rows=1 loops=1)

                           Buckets: 1024  Batches: 1  Memory Usage: 1kB

                           ->  Seq Scan on stoqs_platform  (cost=0.00..1.07 rows=1 width=278) (actual time=0.008..0.012 rows=1 loops=1)

                                 Filter: ((name)::text = 'dorado'::text)

 Total runtime: 770.445 ms

(42 rows)

On Mon, May 13, 2013 at 5:58 PM, Mike McCann <mccann@mbari.org> wrote:

> We assume that steps taken to improve the worst-case query scenario will
> also improve these kind of queries.  If anything above pops out as needing
> better planning please let us know that too!

Bad assumption. If your real workload will be queries like the one
here that takes 700 ms, but you'll be running 10,000 of them a second,
you're tuning / hardware choices are going to be much different then
if your query is going to be the previous 7 second one. Use realistic
queries, not ones that are nothing like what your real ones will be.
then use pgbench and its ability to run custom sql scripts to get a
REAL idea how your hardware performs. Note that if you will run the
slow query you posted like once a minute and roll it up or cache it
then don't get too worried about it. Pay attention to the queries that
will add up, in aggregate, to your greatest load.

On 5/13/13 6:36 PM, Mike McCann wrote:
>     stoqs_march2013_s=# explain analyze select * from
>     stoqs_measuredparameter order by datavalue;
>
>     QUERY PLAN
>
------------------------------------------------------------------------------------------------------------------------------------------
>       Sort  (cost=422106.15..430560.68 rows=3381814 width=20) (actual
>     time=2503.078..2937.130 rows=3381814 loops=1)
>         Sort Key: datavalue
>         Sort Method: quicksort  Memory: 362509kB
>         ->  Seq Scan on stoqs_measuredparameter  (cost=0.00..55359.14
>     rows=3381814 width=20) (actual time=0.016..335.745 rows=3381814 loops=1)
>       Total runtime: 3094.601 ms
>     (5 rows)
>
> I tried changing random_page_cost to from 4 to 1 and saw no change.

Have you tried putting an index by datavalue on this table?  Once you've
done that, then changing random_page_cost will make using that index
look less expensive.  Sorting chews through a good bit of CPU time, and
that's where all of your runtime is being spent at--once you increase
work_mem up very high that is.

> I'm wondering now what changes might get this query to run in less than
> one second.  If all the data is in memory, then will faster CPU and
> memory be the things that help?

You're trying to fix a fundamental design issue with hardware.  That
usually doesn't go well.  Once you get a box big enough to hold the
whole database in RAM, beyond that the differences between server
systems are relatively small.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com

On Sun, May 19, 2013 at 8:44 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> On 5/13/13 6:36 PM, Mike McCann wrote:
>>
>>     stoqs_march2013_s=# explain analyze select * from
>>     stoqs_measuredparameter order by datavalue;
>>
>>     QUERY PLAN
>>
>>
------------------------------------------------------------------------------------------------------------------------------------------
>>       Sort  (cost=422106.15..430560.68 rows=3381814 width=20) (actual
>>     time=2503.078..2937.130 rows=3381814 loops=1)
>>         Sort Key: datavalue
>>         Sort Method: quicksort  Memory: 362509kB
>>         ->  Seq Scan on stoqs_measuredparameter  (cost=0.00..55359.14
>>     rows=3381814 width=20) (actual time=0.016..335.745 rows=3381814
>> loops=1)
>>       Total runtime: 3094.601 ms
>>     (5 rows)
>>
>> I tried changing random_page_cost to from 4 to 1 and saw no change.
>
>
> Have you tried putting an index by datavalue on this table?  Once you've
> done that, then changing random_page_cost will make using that index look
> less expensive.  Sorting chews through a good bit of CPU time, and that's
> where all of your runtime is being spent at--once you increase work_mem up
> very high that is.

This++ plus cluster on that index if you can.