On 2016/01/04 6:11 PM, Bernardo Sulzbach wrote:
> On Mon, Jan 4, 2016 at 1:50 PM, Stephen Chrzanowski<pontiac76 at gmail.com>
> wrote:
>> I did the test to compare SSD vs 5200rpm and 7200rpm drives. I should
>> throw this at my 7200rpm hybrid at this app and see what happens.....
>>
> Could you please share any performance data you still have? Thank you.
>
I decided to cook up a test for this since I happen to have one dev
machine that is rather new with 3 different storage media in.
The following script was used to produce a severely fragmented database
file around 20GB in size (while only about 7GB of actual data remained
inside). It takes about 20 mins to run on a normal drive:
================================
DROP TABLE IF EXISTS vacTest;
DROP TABLE IF EXISTS vacCopy;
CREATE TABLE vacTest(
i INTEGER NOT NULL PRIMARY KEY,
a REAL,
b NUMERIC,
c TEXT
);
CREATE TABLE vacCopy(
i INTEGER NOT NULL PRIMARY KEY,
a REAL,
b NUMERIC,
c TEXT
);
WITH VI(x, r) AS (
SELECT 0, 2450000
UNION ALL
SELECT x+1, ((random()/9223372036854775806.0)*20000)+2450000 FROM
VI WHERE x<100000000
)
INSERT INTO vacTest (i,a,b,c) SELECT x, r, datetime(r), printf('Some
Text aimed at consuming a few bytes S%6d',substr(r,2,6))
FROM VI;
INSERT INTO vacCopy SELECT * FROM vacTest;
DELETE FROM vacTest
WHERE (i / 5) = (i / 5.0);
DELETE FROM vacCopy
WHERE (i / 6) = (i / 6.0);
WITH VI(x) AS (
SELECT 0
UNION ALL
SELECT x+1 FROM VI WHERE x<10000000
)
INSERT INTO vacTest (a,b,c) SELECT 10000.0, '2000-01-01 00:00:00',
printf('Some Text aimed at consuming a few bytes S%6d',x)
FROM VI;
WITH VI(x) AS (
SELECT 0
UNION ALL
SELECT x+1 FROM VI WHERE x<10000000
)
INSERT INTO vacCopy (a,b,c) SELECT 10000.0, '2000-01-01 00:00:00',
printf('Some Text aimed at consuming a few bytes S%6d',x)
FROM VI;
DELETE FROM vacTest
WHERE instr(i,'4') > 0;
DELETE FROM vacCopy
WHERE instr(i,'5') > 0;
=============================
It was run inside sqlite3.exe using 3.9.2 (the standard pre-compiled CLI
as downloadable from the downloads page on sqlite.org) with 4K page size
and WAL mode. OS is Windows 10 64 bit.
The file produced was then copied to 4 different drives as follows:
Test 1 - F: - External Western Digital 3TB 7500 rpm drive via USB3.0,
Max Read - Write rated @ 120MB/s
Test 2 - D: - Internal Western Digital Velociraptor 600MB 10,000 rpm via
SATA 6GB/s rated @ 150 MB/s
Test 3 - E: - Internal Western Digital Black 2TB 7500 rpm (WD2002FAEX)
via SATA 6GB/s rated @ 164 MB/s
Test 4 - C: - Internal Kingston HyperX Predator 480GB SSD via m.2 PCIe
interface rated @ 1400MB/s (read) - 1000 MB/s (write)
Following is the output from the VACUUM command (with timers enabled)
using the sqlite3 CLI and connecting to the same copied file on the 4
drives:
F:\[SCIENCE]>sqlite3 vacTest.sqlitedb
SQLite version 3.9.2 2015-11-02 18:31:45
Enter ".help" for usage hints.
sqlite> .timer ON
sqlite> VACUUM;
Run Time: real 1309.109 user 203.921875 sys 114.984375
sqlite> .q
F:\[SCIENCE]>sqlite3 D:\vacTest.sqlitedb
SQLite version 3.9.2 2015-11-02 18:31:45
Enter ".help" for usage hints.
sqlite> .timer ON
sqlite> VACUUM;
Run Time: real 824.223 user 140.953125 sys 87.921875
sqlite> .q
F:\[SCIENCE]>sqlite3 E:\ADrive\vacTest.sqlitedb
SQLite version 3.9.2 2015-11-02 18:31:45
Enter ".help" for usage hints.
sqlite> .timer ON
sqlite> VACUUM;
Run Time: real 788.750 user 131.921875 sys 81.593750
sqlite> .q
F:\[SCIENCE]>sqlite3 c:\AMD\vacTest.sqlitedb
SQLite version 3.9.2 2015-11-02 18:31:45
Enter ".help" for usage hints.
sqlite> .timer ON
sqlite> VACUUM;
Run Time: real 214.056 user 92.843750 sys 56.640625
sqlite> .q
No surprises really, the VACUUM operation is clearly IO bound.
In all three cases the processor (i7 5930K Hexacore 12-thread @ 3.5GHz)
Never breaks the 4% total usage, and no single thread rose above 40%
until right before the VACUUM operation concludes, at which point the
one thread spikes up to 95% for around 2-6 seconds seemingly in step
with each drive's general performance. (This is my perception, I had no
means of measuring it exactly).
File Size goes down from ~20GB to around ~6.8GB, so the Vacuuming has a
purpose in this case, but it would be hard to find this level of
fragmentation in a normal-use real-World DB.
In all tests, the TEMP folder was set to the D drive which is the second
slowest drive, it showed a lot of usage. I started out using the usual
6GB Ramdrive for TEMP, but it was too small, so redirected it to D. I
tried the last test again with the TEMP set to the fast SSD, figures
were only about 16% better (214 vs. 181), but I imagine using a ramdrive
or at least a second SSD would show a significant improvement.
I initially added tests for "SELECT * FROM vacTest WHERE xxx;" but they
seemed to be near instantaneous before and after the vacuum so I dropped
it - I realized afterward it is probably thanks to the Primary Key I
used in the WHERE clause, and may have had more useful results if I used
something needing a table-scan - but then that would not mimic
real-world conditions either.
Cheers,
Ryan
