In general, CPUs got much faster than disk IO a long time ago, so it
is expected that a single thread, write through to disk program would
have lots of time where the is CPU waiting for disk IO to complete.
(BTW: A common error of novice db programmers is using a disk based db
to store variables instead of handling them in memory, thus reducing a
L1 cache or register operation to the speed of a disk.)
The technology of caching is an attempt to win performance with
smaller, faster caches closer to the CPU, exploiting temporal or
(memory) spacial locality to not need to go all the way to the disk as
little as possible. The list has more than a few discussions of
people using SSDs to increase performance or even caching, mission
critical, RAID controllers to win speed.
That said, why is the dropping of a table dependent on the size of
the table? Does Sqlite have to mark every block of memory it used as
dropped? (This is obvious for high security mode, but otherwise?)
regards,
Adam DeVita
On Fri, Apr 22, 2016 at 8:23 AM, Cecil Westerhof <cldwesterhof at gmail.com>
wrote:
> 2016-04-22 14:06 GMT+02:00 E.Pasma <pasma10 at concepts.nl>:
>
>>
>> 22 apr 2016, Cecil Westerhof:
>>
>>>
>>> With createBigTable.sh ...
>>>
>> Can you paste the svript in the message? Attachments are not sent.
>>
>
> createBigTable.sh:
> #/usr/bin/env bash
>
> # An error should terminate the script
> # An unset variable is also an error
> set -o errexit
> set -o nounset
>
>
> declare -r INSERT_TEMPLATE="INSERT INTO testUniqueUUIDBig
> SELECT uuid, %d FROM testUniqueUUID
> ;
> "
> declare -r NR_OF_COPIES=10
>
> declare insert=""
>
>
> function getInsertStr {
> printf "${INSERT_TEMPLATE}" "${1}"
> }
>
>
> for i in $(seq "${NR_OF_COPIES}") ; do
> insert+="$(getInsertStr ${i})
> "
> done
>
> sqlite3 checkUUID.sqlite <<EOT
> .echo ON
> .timer ON
> DROP TABLE IF EXISTS testUniqueUUIDBig;
> CREATE TABLE testUniqueUUIDBig (
> UUID blob,
> count int,
>
> PRIMARY KEY(UUID, count)
> CHECK(TYPEOF(UUID) = 'blob' AND
> LENGTH(UUID) = 16 AND
> SUBSTR(HEX(UUID), 13, 1) == '4' AND
> SUBSTR(HEX(UUID), 17, 1) IN ('8', '9', 'A', 'B')
> )
> );
> ${insert}
> EOT
>
>
> The logging:
> .timer ON
> DROP TABLE IF EXISTS testUniqueUUIDBig;
> Run Time: real 293.257 user 6.708000 sys 28.844000
> CREATE TABLE testUniqueUUIDBig (
> UUID blob,
> count int,
>
> PRIMARY KEY(UUID, count)
> CHECK(TYPEOF(UUID) = 'blob' AND
> LENGTH(UUID) = 16 AND
> SUBSTR(HEX(UUID), 13, 1) == '4' AND
> SUBSTR(HEX(UUID), 17, 1) IN ('8', '9', 'A', 'B')
> )
> );
> Run Time: real 0.277 user 0.000000 sys 0.000000
> INSERT INTO testUniqueUUIDBig
> SELECT uuid, 1 FROM testUniqueUUID
> ;
> Run Time: real 89.930 user 48.872000 sys 28.196000
> INSERT INTO testUniqueUUIDBig
> SELECT uuid, 2 FROM testUniqueUUID
> ;
> Run Time: real 133.674 user 56.416000 sys 43.032000
> INSERT INTO testUniqueUUIDBig
> SELECT uuid, 3 FROM testUniqueUUID
> ;
> Run Time: real 269.029 user 59.520000 sys 48.840000
> INSERT INTO testUniqueUUIDBig
> SELECT uuid, 4 FROM testUniqueUUID
> ;
> Run Time: real 356.622 user 61.196000 sys 51.956000
> INSERT INTO testUniqueUUIDBig
> SELECT uuid, 5 FROM testUniqueUUID
> ;
> Run Time: real 398.048 user 61.924000 sys 57.540000
> INSERT INTO testUniqueUUIDBig
> SELECT uuid, 6 FROM testUniqueUUID
> ;
> Run Time: real 413.252 user 61.684000 sys 59.816000
> INSERT INTO testUniqueUUIDBig
> SELECT uuid, 7 FROM testUniqueUUID
> ;
> Run Time: real 464.911 user 61.672000 sys 63.200000
> INSERT INTO testUniqueUUIDBig
> SELECT uuid, 8 FROM testUniqueUUID
> ;
> Run Time: real 545.974 user 61.900000 sys 66.916000
> INSERT INTO testUniqueUUIDBig
> SELECT uuid, 9 FROM testUniqueUUID
> ;
> Run Time: real 695.315 user 64.016000 sys 69.692000
> INSERT INTO testUniqueUUIDBig
> SELECT uuid, 10 FROM testUniqueUUID
> ;
> Run Time: real 1129.854 user 64.428000 sys 76.704000
>
>
> performanceTest.sh:
> #/usr/bin/env bash
>
> # An error should terminate the script
> # An unset variable is also an error
> set -o errexit
> set -o nounset
>
>
> declare -r DB=checkUUIDSmall.sqlite
> declare -r DEINIT=".timer OFF
> .echo OFF"
> declare -r INIT=".echo ON
> .timer ON"
> declare -r TABLE=testUniqueUUID
> declare -r TABLE_BIG=testUniqueUUIDBig
>
> declare -r DELETE_AND_DROP="DELETE FROM ${TABLE};
> DROP TABLE ${TABLE};
> DELETE FROM ${TABLE_BIG};
> DROP TABLE ${TABLE_BIG};"
> declare -r DROP="DROP TABLE ${TABLE};
> DROP TABLE ${TABLE_BIG};"
>
>
> function cpDB {
> giveMessage "Copying database"
> rm --force "${DB}"-journal
> cp "${DB}".bck "${DB}"
> }
>
> function giveMessage {
> printf "%s: %s\n" "$(date +%T)" "${1}"
> }
>
>
> cpDB
> giveMessage "DROP only"
> sqlite3 "${DB}" <<EOT
> ${INIT}
> ${DROP}
> ${DEINIT}
> EOT
>
> cpDB
> giveMessage "DELETE and DROP"
> sqlite3 "${DB}" <<EOT
> ${INIT}
> ${DELETE_AND_DROP}
> ${DEINIT}
> EOT
>
> cpDB
> giveMessage "SECURE_DELETE=0; DROP only"
> sqlite3 "${DB}" <<EOT
> ${INIT}
> PRAGMA SECURE_DELETE=0;
> ${DROP}
> PRAGMA SECURE_DELETE=1;
> ${DEINIT}
> EOT
>
> cpDB
> giveMessage "SECURE_DELETE=0; DELETE and DROP"
> sqlite3 "${DB}" <<EOT
> ${INIT}
> PRAGMA SECURE_DELETE=0;
> ${DELETE_AND_DROP}
> PRAGMA SECURE_DELETE=1;
> ${DEINIT}
> EOT
>
> giveMessage "Done"
>
> The logging:
> 23:31:24: Copying database
> 23:31:50: DROP only
> .timer ON
> DROP TABLE testUniqueUUID;
> Run Time: real 92.543 user 1.216000 sys 6.324000
> DROP TABLE testUniqueUUIDBig;
> Run Time: real 1474.282 user 12.812000 sys 63.832000
> .timer OFF
> .echo OFF
> 23:57:58: Copying database
> 23:58:23: DELETE and DROP
> .timer ON
> DELETE FROM testUniqueUUID;
> Run Time: real 13.811 user 1.064000 sys 4.556000
> DROP TABLE testUniqueUUID;
> Run Time: real 0.258 user 0.000000 sys 0.004000
> DELETE FROM testUniqueUUIDBig;
> Run Time: real 1287.970 user 12.984000 sys 63.412000
> DROP TABLE testUniqueUUIDBig;
> Run Time: real 0.525 user 0.004000 sys 0.000000
> .timer OFF
> .echo OFF
> 00:20:06: Copying database
> 00:20:32: SECURE_DELETE=0; DROP only
> .timer ON
> PRAGMA SECURE_DELETE=0;
> 0
> Run Time: real 0.000 user 0.000000 sys 0.000000
> DROP TABLE testUniqueUUID;
> Run Time: real 75.249 user 0.632000 sys 1.596000
> DROP TABLE testUniqueUUIDBig;
> Run Time: real 872.330 user 6.900000 sys 15.956000
> PRAGMA SECURE_DELETE=1;
> 1
> Run Time: real 0.001 user 0.000000 sys 0.000000
> .timer OFF
> .echo OFF
> 00:36:20: Copying database
> 00:36:45: SECURE_DELETE=0; DELETE and DROP
> .timer ON
> PRAGMA SECURE_DELETE=0;
> 0
> Run Time: real 0.000 user 0.000000 sys 0.000000
> DELETE FROM testUniqueUUID;
> Run Time: real 3.085 user 0.452000 sys 0.324000
> DROP TABLE testUniqueUUID;
> Run Time: real 0.277 user 0.000000 sys 0.000000
> DELETE FROM testUniqueUUIDBig;
> Run Time: real 209.797 user 5.564000 sys 10.860000
> DROP TABLE testUniqueUUIDBig;
> Run Time: real 0.243 user 0.000000 sys 0.000000
> PRAGMA SECURE_DELETE=1;
> 1
> Run Time: real 0.001 user 0.000000 sys 0.000000
> .timer OFF
> .echo OFF
> 00:40:19: Done
>
> --
> Cecil Westerhof
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
--
--------------
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1
