Обсуждение: intel s3500 -- hot stuff
I recently sourced a 300gb intel s3500 ssd to do some performance testing. I didn't see a lot of results on the web so I thought I'd post some numbers. Testing machine is my workstation crapbox with 4 cores and 8GB ram (of which about 4 is usable by the ~ 50gb database). The drive cost 260$ at newegg (sub 1$/gb) and is write durable. Single thread 'select only' results are pretty stable 2200 tps isn't bad. of particular note is the sub millisecond latency of the read. Per iostat I'm getting ~ 55mb/sec read off the device and around 4100 device tps: transaction type: SELECT only scaling factor: 3000 query mode: simple number of clients: 1 number of threads: 1 duration: 10 s number of transactions actually processed: 22061 tps = 2206.019701 (including connections establishing) tps = 2206.534467 (excluding connections establishing) statement latencies in milliseconds: 0.003143 \set naccounts 100000 * :scale 0.000776 \setrandom aid 1 :naccounts 0.447513 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; Multi thread 'select only' results are also pretty stable: I get around 16-17k tps, but of note: *) iowait in the mid 40's *) cpu bound *) consistent 430mb/sec off the device per iostat !! that's incredible!! (some of the latency may in fact be from SATA). transaction type: SELECT only scaling factor: 3000 query mode: simple number of clients: 32 number of threads: 32 duration: 20 s number of transactions actually processed: 321823 tps = 16052.052818 (including connections establishing) tps = 16062.973737 (excluding connections establishing) statement latencies in milliseconds: 0.002469 \set naccounts 100000 * :scale 0.000528 \setrandom aid 1 :naccounts 1.984443 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; For random write tests, I see around 1000tps for single thread and ~ 4700 with 32 threads. These results are more volatile and, importantly, I disable synchronous commit feature. For the price, unless you are doing tons and tons of writing (in which case i'd opt for a more expensive drive like the S3700). This drive is perfectly suited for OLAP work IMO since ssds like the big sequential loads and random access of the data is no problem. merlin
On Wed, Nov 5, 2014 at 11:40 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> I recently sourced a 300gb intel s3500 ssd to do some performance
> testing. I didn't see a lot of results on the web so I thought I'd
> post some numbers. Testing machine is my workstation crapbox with 4
> cores and 8GB ram (of which about 4 is usable by the ~ 50gb database).
> The drive cost 260$ at newegg (sub 1$/gb) and is write durable.
Here's another fascinating data point. I was playing around
effective_io_concurrency for the device with bitmap heap scans on the
scale 3000 database (again, the performance numbers are very stable
across runs):
bench=# explain (analyze, buffers) select * from pgbench_accounts
where aid between 1000 and 50000000 and abalance != 0;
QUERY PLAN
────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Bitmap Heap Scan on pgbench_accounts (cost=1059541.66..6929604.57
rows=1 width=97) (actual time=5040.128..23089.651 rows=1420738
loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 50000000))
Rows Removed by Index Recheck: 3394823
Filter: (abalance <> 0)
Rows Removed by Filter: 48578263
Buffers: shared hit=3 read=1023980
-> Bitmap Index Scan on pgbench_accounts_pkey
(cost=0.00..1059541.66 rows=50532109 width=0) (actual
time=5038.707..5038.707 rows=49999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 50000000))
Buffers: shared hit=3 read=136611
Total runtime: 46251.375 ms
effective_io_concurrency 1: 46.3 sec, ~ 170 mb/sec peak via iostat
effective_io_concurrency 2: 49.3 sec, ~ 158 mb/sec peak via iostat
effective_io_concurrency 4: 29.1 sec, ~ 291 mb/sec peak via iostat
effective_io_concurrency 8: 23.2 sec, ~ 385 mb/sec peak via iostat
effective_io_concurrency 16: 22.1 sec, ~ 409 mb/sec peak via iostat
effective_io_concurrency 32: 20.7 sec, ~ 447 mb/sec peak via iostat
effective_io_concurrency 64: 20.0 sec, ~ 468 mb/sec peak via iostat
effective_io_concurrency 128: 19.3 sec, ~ 488 mb/sec peak via iostat
effective_io_concurrency 256: 19.2 sec, ~ 494 mb/sec peak via iostat
Did not see consistent measurable gains > 256
effective_io_concurrency. Interesting that at setting of '2' (the
lowest possible setting with the feature actually working) is
pessimal.
merlin
On Wed, Nov 5, 2014 at 12:09:16PM -0600, Merlin Moncure wrote:
> effective_io_concurrency 1: 46.3 sec, ~ 170 mb/sec peak via iostat
> effective_io_concurrency 2: 49.3 sec, ~ 158 mb/sec peak via iostat
> effective_io_concurrency 4: 29.1 sec, ~ 291 mb/sec peak via iostat
> effective_io_concurrency 8: 23.2 sec, ~ 385 mb/sec peak via iostat
> effective_io_concurrency 16: 22.1 sec, ~ 409 mb/sec peak via iostat
> effective_io_concurrency 32: 20.7 sec, ~ 447 mb/sec peak via iostat
> effective_io_concurrency 64: 20.0 sec, ~ 468 mb/sec peak via iostat
> effective_io_concurrency 128: 19.3 sec, ~ 488 mb/sec peak via iostat
> effective_io_concurrency 256: 19.2 sec, ~ 494 mb/sec peak via iostat
>
> Did not see consistent measurable gains > 256
> effective_io_concurrency. Interesting that at setting of '2' (the
> lowest possible setting with the feature actually working) is
> pessimal.
Very interesting. When we added a per-tablespace random_page_cost,
there was a suggestion that we might want to add per-tablespace
effective_io_concurrency someday:
commit d86d51a95810caebcea587498068ff32fe28293e
Author: Robert Haas <rhaas@postgresql.org>
Date: Tue Jan 5 21:54:00 2010 +0000
Support ALTER TABLESPACE name SET/RESET ( tablespace_options ).
This patch only supports seq_page_cost and random_page_cost as parameters,
but it provides the infrastructure to scalably support many more.
In particular, we may want to add support for effective_io_concurrency,
but I'm leaving that as future work for now.
Thanks to Tom Lane for design help and Alvaro Herrera for the review.
It seems that time has come.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
On Sat, Dec 6, 2014 at 7:08 AM, Bruce Momjian <bruce@momjian.us> wrote: > On Wed, Nov 5, 2014 at 12:09:16PM -0600, Merlin Moncure wrote: >> effective_io_concurrency 1: 46.3 sec, ~ 170 mb/sec peak via iostat >> effective_io_concurrency 2: 49.3 sec, ~ 158 mb/sec peak via iostat >> effective_io_concurrency 4: 29.1 sec, ~ 291 mb/sec peak via iostat >> effective_io_concurrency 8: 23.2 sec, ~ 385 mb/sec peak via iostat >> effective_io_concurrency 16: 22.1 sec, ~ 409 mb/sec peak via iostat >> effective_io_concurrency 32: 20.7 sec, ~ 447 mb/sec peak via iostat >> effective_io_concurrency 64: 20.0 sec, ~ 468 mb/sec peak via iostat >> effective_io_concurrency 128: 19.3 sec, ~ 488 mb/sec peak via iostat >> effective_io_concurrency 256: 19.2 sec, ~ 494 mb/sec peak via iostat >> >> Did not see consistent measurable gains > 256 >> effective_io_concurrency. Interesting that at setting of '2' (the >> lowest possible setting with the feature actually working) is >> pessimal. > > Very interesting. When we added a per-tablespace random_page_cost, > there was a suggestion that we might want to add per-tablespace > effective_io_concurrency someday: What I'd really like to see is to have effective_io_concurrency work on other types of scans. It's clearly a barn burner on fast storage and perhaps the default should be something other than '1'. Spinning storage is clearly dead and ssd seem to really benefit from the posix readhead api. merlin
On Mon, Dec 8, 2014 at 03:40:43PM -0600, Merlin Moncure wrote: > >> Did not see consistent measurable gains > 256 > >> effective_io_concurrency. Interesting that at setting of '2' (the > >> lowest possible setting with the feature actually working) is > >> pessimal. > > > > Very interesting. When we added a per-tablespace random_page_cost, > > there was a suggestion that we might want to add per-tablespace > > effective_io_concurrency someday: > > What I'd really like to see is to have effective_io_concurrency work > on other types of scans. It's clearly a barn burner on fast storage > and perhaps the default should be something other than '1'. Spinning > storage is clearly dead and ssd seem to really benefit from the posix > readhead api. Well, the real question is knowing which blocks to request before actually needing them. With a bitmap scan, that is easy --- I am unclear how to do it for other scans. We already have kernel read-ahead for sequential scans, and any index scan that hits multiple rows will probably already be using a bitmap heap scan. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
On Tue, Dec 9, 2014 at 12:43 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Mon, Dec 8, 2014 at 03:40:43PM -0600, Merlin Moncure wrote:
> >> Did not see consistent measurable gains > 256
> >> effective_io_concurrency. Interesting that at setting of '2' (the
> >> lowest possible setting with the feature actually working) is
> >> pessimal.
> >
> > Very interesting. When we added a per-tablespace random_page_cost,
> > there was a suggestion that we might want to add per-tablespace
> > effective_io_concurrency someday:
>
> What I'd really like to see is to have effective_io_concurrency work
> on other types of scans. It's clearly a barn burner on fast storage
> and perhaps the default should be something other than '1'. Spinning
> storage is clearly dead and ssd seem to really benefit from the posix
> readhead api.
I haven't played much with SSD, but effective_io_concurrency can be a big win even on spinning disk.
Well, the real question is knowing which blocks to request before
actually needing them. With a bitmap scan, that is easy --- I am
unclear how to do it for other scans. We already have kernel read-ahead
for sequential scans, and any index scan that hits multiple rows will
probably already be using a bitmap heap scan.
If the index scan is used to provide ordering as well as selectivity than it will resist being converted to an bitmap scan. Also it won't convert to a bitmap scan solely to get credit for the use of effective_io_concurrency, as that setting doesn't enter into planning decisions.
For a regular index scan, it should be easy to prefetch table blocks for all the tuples that will need to be retrieved based on the current index leaf page, for example. Looking ahead across leaf page boundaries would be harder.
Cheers,
Jeff
On 10/12/2014 17:52, Jeff Janes wrote: > On Tue, Dec 9, 2014 at 12:43 PM, Bruce Momjian <bruce@momjian.us > <mailto:bruce@momjian.us>> wrote: > > On Mon, Dec 8, 2014 at 03:40:43PM -0600, Merlin Moncure wrote: > > >> Did not see consistent measurable gains > 256 > > >> effective_io_concurrency. Interesting that at setting of '2' (the > > >> lowest possible setting with the feature actually working) is > > >> pessimal. > > > > > > Very interesting. When we added a per-tablespace random_page_cost, > > > there was a suggestion that we might want to add per-tablespace > > > effective_io_concurrency someday: > > > > What I'd really like to see is to have effective_io_concurrency work > > on other types of scans. It's clearly a barn burner on fast storage > > and perhaps the default should be something other than '1'. Spinning > > storage is clearly dead and ssd seem to really benefit from the posix > > readhead api. > > > I haven't played much with SSD, but effective_io_concurrency can be a > big win even on spinning disk. > > > > Well, the real question is knowing which blocks to request before > actually needing them. With a bitmap scan, that is easy --- I am > unclear how to do it for other scans. We already have kernel read-ahead > for sequential scans, and any index scan that hits multiple rows will > probably already be using a bitmap heap scan. > > > If the index scan is used to provide ordering as well as selectivity > than it will resist being converted to an bitmap scan. Also it won't > convert to a bitmap scan solely to get credit for the use of > effective_io_concurrency, as that setting doesn't enter into planning > decisions. > > For a regular index scan, it should be easy to prefetch table blocks for > all the tuples that will need to be retrieved based on the current index > leaf page, for example. Looking ahead across leaf page boundaries would > be harder. > I also think that having effective_io_concurrency for other nodes that bitmap scan would be really great, but for now having a per-tablespace effective_io_concurrency is simpler to implement and will already help, so here's a patch to implement it. I'm also adding it to the next commitfest. -- Julien Rouhaud http://dalibo.com - http://dalibo.org