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