Re: [PERFORM] Hardware suggestions for maximum read performance
On May 7, 2013, at 4:21 PM, Jeff Janes wrote: On Thu, May 2, 2013 at 6:35 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Thu, May 2, 2013 at 5:11 PM, Mike McCann mcc...@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. I wouldn't count on this being a problem that can be fixed merely by throwing money at it. How many rows does any one of these queries need to access and then ORDER BY? ... 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. But it can take a surprisingly long time to get it cached in the first place, from a cold start. If that is the problem, pg_prewarm could help. Cheers, Jeff Thank you everyone for your suggestions. It's clear that our current read performance was not limited by hardware. An 'explain analyze' for a sample query is: 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) Increasing work_mem to 355 MB improves the performance by a factor of 2: 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) I tried changing random_page_cost to from 4 to 1 and saw no change. 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? We have an alternate (a bit more conventional) server configuration that we are considering: 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) All suggestions welcomed! -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
Re: [PERFORM] Hardware suggestions for maximum read performance
On May 13, 2013, at 4:24 PM, Jeff Janes wrote:
On Mon, May 13, 2013 at 3:36 PM, Mike McCann mcc...@mbari.org wrote:
Increasing work_mem to 355 MB improves the performance by a factor of 2:
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)
I tried changing random_page_cost to from 4 to 1 and saw no change.
I'm wondering now what changes might get this query to run in less than one
second.
I think you are worrying about the wrong thing here. What is a web app going
to do with 3,381,814 rows, once it obtains them? Your current testing is not
testing the time it takes to stream that data to the client, or for the
client to do something meaningful with that data.
If you only plan to actually fetch a few dozen of those rows, then you
probably need to incorporate that into your test, either by using a LIMIT, or
by using a mock-up of the actual application to do some timings.
Also, what is the type and collation of the column you are sorting on?
non-'C' collations of text columns sort about 3 times slower than 'C'
collation does.
If all the data is in memory, then will faster CPU and memory be the things
that help?
Yes, those would help (it is not clear to me which of the two would help
more), but I think you need to rethink your design of sending the entire
database table to the application server for each page-view.
Cheers,
Jeff
Hi Jeff,
The datavalue column is double precision:
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
Thanks for the suggestion and advice to examine the web app performance. We've
actually taken quite a few steps to optimize how the web app works. The example
query I provided is a simple worst-case one that we can use to help us decide
on the proper hardware. An actual query performed by the web app is:
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
[PERFORM] Hardware suggestions for maximum read performance
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
