wiki:SSDtests2
Last modified 8 years ago Last modified on 06/30/2011 10:36:31 AM

Tests with Solid State Disks on ir2srv03 (SLAC)

LSST Database

About test bed

  • sequential read speed 227MB/sec, sequential write speed 220 MB/sec (observed during copying 70 GB file from /ssd1 to ssd2)

Test01 (concurrency)

Test involved running concurrent full table scans, each reading from a different table. The query was: select count(*) from o01 where uFlux_PS>0.

  • 1 query running: ~18 sec to complete
  • 2 queries running concurrently: ~35 sec average time per query, both finished at almost the same time.
  • 4 queries: ~72 sec, all 4 finished almost at the same time
  • 8 queries: ~126 sec average (shortest: 1m41s, longest 2m15s)

In each case:

  • cpu usage was ~ the same: ~18-19%
  • number of reads/sec was around ~1750
  • MB/sec was ~225 for the first 3 tests, ~213 for the last test
  • "await" time report by iowait for 4 for 2, 8 for 4-query test and 13 for 8-query test.

Memory was cleared and mysqld server was restarted between each test, no data caching was observed.

Test02 (selection through index)

Test involved running on a Source table which had 164,989,583 rows (~75 GB). RAM was flushed prior to each SSD and spinning disk test.

select sum(taiRange) from Source where decl > <value>

and

select sum(taiRange) from Source IGNORE INDEX (IDX_Source_decl) where decl > <value>

Results:

<value> rows selected % of time [sec] time [sec] time [sec] time [sec] time [sec] time [sec]
table spinning, w/idxspinning, no idxssd, with idx ssd, no idxram, with idxram, no idx
-6 161929060 98 726 64.17 64.24
-5 153929256 93 723 63.86 63.89
-4 144643294 88 841 317 (scan) 302 63.53 63.56
-3 127062022 77 734 343 (scan) 317 62.84 62.88
-2 109614642 66 734 341 (scan) 342 62.20 62.24
-1 91930480 56 751 342 (scan) 318 61.60 61.59
0 80276433 49 728 342 (scan) 342 61.72 61.22
1 71420698 43 841 339 (scan) 344 60.06 60.86
2 53853350 33 842 330 (scan) 330 58.63 60.24
3 37529654 23 728 853 (idx used) 340 59.67 59.64
3.5 29466029 18 37613 727 836 (idx used) 330 58.37 59.44
4 16769906 10 7590 841 422 (idx used) 341 39.93 57.79
4.4 14967669 9 1550 841 163 (idx used) 339 32.29 58.89
4.5 14001147 8 2277 755 271 (idx used) 311 26.61 58.86
4.75 12122228 7 2260 777 300 (idx used) 337 22.72 58.74
5 10287875 6 7791 841 182 (idx used) 317 19.54 58.52
6 3909643 2 4948 821 115 (idx used) 341 7.44 58.24
6.5 1181124 1 1571 841 37 (idx used) 365 2.30 58.18
7 15895 0 19.82 840 0.62(idx used) 334 0.04 57.00

Information about IO - table scan:

RAM based tests SSD based tests spinning-disk tests
reads/sec 0 1,500 ~730
MB/sec 0 ~185 ~93

(that is ~128 KB per read)

Information about IO - index used:

RAM based tests SSD based tests spinning-disk tests
reads/sec 0 4,150 ~95
MB/sec 0 0.4

(that is ~4 KB per read)

Conclusion: if 10% or more rows are selected from ssd-based data, it is more efficient to bypass index and use full table scan. Index is always better for ram-based data. If data is on spinning disk, even for selectivity as small as 1% full table scan is faster than index scan.