Skip to content

sqlite N-API port - resolve mac issue #288

Closed
Closed
sqlite N-API port - resolve mac issue#288
Milestone
Milestone 11

Description

@mhdawson
mhdawson
opened on Jan 4, 2018

TryGhost/node-sqlite3#830

Activity

  1. mhdawson
    added this to the Milestone 9 milestone on Jan 4, 2018

  2. aruneshchandra commented on Jan 11, 2018

    @aruneshchandra
    aruneshchandra
    on Jan 11, 2018
    Contributor

    @anisha-rohra investigated and fixed the original problem reported, now working on additional reported problems and also to investigate if we can remove the C++ 11 requirement.

  3. mhdawson
    modified the milestones: Milestone 9, Milestone 10 on Apr 12, 2018

  4. mhdawson commented on May 3, 2018

    @mhdawson
    mhdawson
    on May 3, 2018
    MemberAuthor

    mapbox, found issue, we fixed in our repo.

    Anisha is working on PR.

  5. mhdawson
    modified the milestones: Milestone 10, Milestone 11 on Sep 27, 2018

  6. mhdawson commented on Oct 7, 2019

    @mhdawson
    mhdawson
    on Oct 7, 2019
    MemberAuthor

    Jim has been working on this for a while, see TryGhost/node-sqlite3#830 for current state.

  7. jschlight commented on Apr 18, 2020

    @jschlight
    jschlight
    on Apr 18, 2020
    Collaborator

    There is now a PR under active work. TryGhost/node-sqlite3#1304

  8. jschlight commented on Apr 20, 2020

    @jschlight
    jschlight
    on Apr 20, 2020
    Collaborator

    @NickNaso requested some benchmarks. Here are some rough numbers from the Mocha tests.

    Mocha Test NAN N-API
    should return the correct lastID (insert 5000 rows) 102 111
    fuss-free incremental backups work 193 183
    retrieve 100,000 rows with Statement#each 270 350
    should insert rows with lots of null values 97 81
    should insert in parallel 635 805
    should prepare a statement and run it 1005 times 47
    should have inserted all the rows after synchronizing with serialize() 85
    serialize(fn) - should call the callback 129
    unicode - should insert all values 107

    The NAN and N-API columns show those tests for which Mocha is reporting a "slow" time. The Mocha default "slow" threshold is 75ms. A timer is displayed for those tests exceeding ~37ms.

    Both Mocha tests run in about two seconds of wall-clock time.

  9. jschlight commented on Apr 27, 2020

    @jschlight
    jschlight
    on Apr 27, 2020
    Collaborator

    The PR has landed.

  10. jschlight commented on Apr 30, 2020

    @jschlight
    jschlight
    on Apr 30, 2020
    Collaborator

    Below is the results of the benchmarks I've run as we discussed at the most recent weekly meeting.

    Two tests were run, one for NAN and one for N-API, both using Node.js v12.16.2. The NAN test was run using a pre-built, downloadable binary for the currently shipping node-sqlite3 v4.2.0. The N-API test was run using a binary locally built from the current node-sqlite3 master branch that will eventually ship as node-sqlite3 v5.0.0. Both tests were run on a MacBook Pro (Late 2016).

    node-sqlite3 has a Mocha suite that runs 159 tests. For this benchmark, the complete suite was run sequentially 20 times. All 20 iterations were run in the same process. The wall-clock time for the NAN tests was 33 seconds, and the wall-clock time for the N-API tests was 38 seconds. This means the wall-clock time for the N-API version is about 15% greater than the NAN version.

    Here's what the table headings mean:

    Mocha Test The name of the Mocha test.
    NAN Mean The mean run time, in ms, of the 20 NAN tests.
    NAPI Mean The mean run time, in ms, of the 20 N-API tests.
    Diff The difference between the N-API mean run time and NAN mean run time.
    % The N-API mean run time expressed as a percentage of the NAN mean run time.
    NAN s The standard deviation of the NAN mean run time.
    NAPI s The standard deviation of the N-API mean run time.

    Mocha Test NAN
    Mean    		 NAPI
    Mean    		 Diff % NAN
    s    		 NAPI
    s
    query properties should return the correct lastID 93 136 43 146 14 8
    query properties should return the correct changes count 1 1 1 153 0 0
    backup output db created once step is called 4 6 1 134 3 2
    backup copies source fully with step(-1) 4 4 0 102 1 1
    backup backup db not created if finished immediately 1 0 0 73 0 0
    backup error closing db if backup not finished 0 0 0 63 0 0
    backup using the backup after finished is an error 1 1 0 110 1 0
    backup remaining/pageCount are available after call to step 1 1 0 100 0 0
    backup backup works if database is modified half-way through 6 7 1 112 1 1
    backup can backup from temp to main 2 2 0 112 1 0
    backup can backup from main to temp 1 1 0 133 0 0
    backup cannot backup to a locked db 1 2 0 123 0 1
    backup fuss-free incremental backups work 203 204 1 100 6 5
    backup setting retryErrors to empty disables automatic finishing 3 3 0 105 1 1
    backup setting retryErrors enables automatic finishing 3 6 2 174 1 11
    backup default retryErrors will retry on a locked/busy db 4 4 0 105 0 1
    blob should insert blobs 3 4 0 111 1 1
    blob should retrieve the blobs 16 16 -1 96 1 2
    cache should cache Database objects while opening 0 1 0 183 0 0
    cache should cache Database objects after they are open 0 0 0 133 0 0
    constants should have the right OPEN_* flags 0 0 0 50 0 0
    constants should have the right error flags 0 0 0 400 0 0
    error handling throw when calling Database() without new† NaN 0 NaN NaN NaN 0
    error handling should error when calling Database#get on a missing table 0 0 0 50 0 0
    error handling Database#all prepare fail 0 0 0 50 0 0
    error handling Database#run prepare fail 0 1 0 500 0 1
    error handling Database#each prepare fail 0 0 0 133 0 1
    error handling Database#each prepare fail without completion handler 0 0 0 33 0 0
    error handling Database#get prepare fail with param binding 0 0 0 150 0 0
    error handling Database#all prepare fail with param binding 0 0 0 167 0 0
    error handling Database#run prepare fail with param binding 0 0 0 200 0 0
    error handling Database#each prepare fail with param binding 0 0 0 300 0 0
    error handling Database#each prepare fail with param binding without completion handler 0 0 0 17 0 0
    each retrieve 100,000 rows with Statement#each 253 355 103 141 12 17
    each Statement#each with complete callback 28 39 12 142 3 3
    exec Database#exec 1 1 0 145 1 0
    exec retrieve database structure 0 1 0 250 0 1
    fts should create a new fts4 table 1 1 0 150 1 0
    interrupt should interrupt queries 2 2 0 79 4 1
    interrupt should throw if interrupt is called before open 0 0 0 50 0 0
    interrupt should throw if interrupt is called after close 1 0 0 70 1 0
    interrupt should throw if interrupt is called during close 0 0 0 100 0 0
    buffer should insert blobs 1 1 0 125 1 1
    json should select JSON 0 0 0 500 0 0
    map test Database#map() with two columns 1 1 0 177 0 0
    map test Database#map() with three columns 1 1 0 167 0 0
    named columns should create the table 0 0 0 57 0 0
    named columns should insert a value 0 0 0 75 0 0
    named columns should retrieve the values 0 0 0 500 0 0
    named columns should be able to retrieve rowid of last inserted value 0 0 0 300 0 0
    named parameters should create the table 0 0 0 167 0 0
    named parameters should insert a value with $ placeholders 0 0 0 100 0 0
    named parameters should insert a value with : placeholders 0 0 0 300 0 0
    named parameters should insert a value with @ placeholders 0 0 0 Infinity 0 0
    named parameters should insert a value with @ placeholders using an array 0 0 0 100 0 0
    named parameters should insert a value with indexed placeholders 0 0 0 50 0 0
    named parameters should insert a value with autoindexed placeholders 0 0 0 29 1 0
    named parameters should retrieve all inserted values 0 0 0 Infinity 0 0
    null error should create a table 4 6 2 142 2 2
    null error should insert rows with lots of null values 84 89 5 106 35 13
    null error should have created the database 0 0 0 NaN 0 0
    open/close should not be unable to open an inaccessible database 0 0 0 71 0 0
    open/close open and close non-existant database should open the database 0 0 0 225 0 0
    open/close open and close non-existant database should close the database 0 0 0 50 0 0
    open/close open and close non-existant database should have created the file 0 0 0 0 0 0
    open/close open and close non-existant shared database should open the database 0 0 0 233 0 0
    open/close open and close non-existant shared database should close the database 0 0 0 200 0 0
    open/close open and close non-existant shared database should have created the file 0 0 0 0 0 0
    open/close open and close shared memory database should open the first database 0 0 0 75 0 0
    open/close open and close shared memory database should open the second database 0 0 0 120 0 0
    open/close open and close shared memory database first database should set the user_version 0 0 0 150 0 0
    open/close open and close shared memory database second database should get the user_version 0 0 0 200 0 0
    open/close open and close shared memory database should close the first database 0 0 0 100 0 0
    open/close open and close shared memory database should close the second database 0 0 0 Infinity 0 0
    open/close creating database without create flag should fail to open the database 0 0 0 100 0 0
    open/close creating database without create flag should not have created the file 0 0 0 100 0 0
    open/close open and close memory database queuing should open the database 0 0 0 100 0 0
    open/close open and close memory database queuing should close the database 0 0 0 50 0 0
    open/close open and close memory database queuing shouldn't close the database again 0 0 0 33 0 0
    open/close closing with unfinalized statements should create a table 0 0 0 60 0 0
    open/close closing with unfinalized statements should prepare/run a statement 0 0 0 200 0 0
    open/close closing with unfinalized statements should fail to close the database 0 0 0 100 0 0
    open/close closing with unfinalized statements should succeed to close the database after finalizing 0 0 0 167 0 0
    data types should serialize Date() 0 0 0 200 0 0
    data types should serialize RegExp() 0 0 0 67 0 0
    data types should serialize float 4294967296.249 0 0 0 160 0 0
    data types should serialize float 3.141592653589793 0 0 0 200 0 0
    data types should serialize float 3924729304762836.5 0 0 0 150 0 0
    data types should serialize float 1588183026935 0 0 0 150 0 0
    data types should serialize float 912667.394828365 0 0 0 400 0 0
    data types should serialize float 2.3948728634826374e+83 0 0 0 33 0 0
    data types should serialize float 9.293476892934982e+300 0 0 0 60 0 0
    data types should serialize float Infinity 0 0 0 300 0 0
    data types should serialize float -9.293476892934982e+300 0 0 0 100 0 0
    data types should serialize float -2.3948728634826374e+83 0 0 0 500 0 0
    data types should serialize float -Infinity 0 0 0 100 0 0
    data types should serialize integer 4294967299 0 0 0 150 0 0
    data types should serialize integer 3924729304762836 0 0 0 100 0 0
    data types should serialize integer 1588183026935 0 0 0 Infinity 0 0
    data types should serialize integer 2.3948728634826374e+83 0 0 0 100 0 0
    data types should serialize integer 9.293476892934982e+300 0 0 0 NaN 0 0
    data types should serialize integer Infinity 0 0 0 133 0 0
    data types should serialize integer -9.293476892934982e+300 0 0 0 50 0 0
    data types should serialize integer -2.3948728634826374e+83 0 0 0 100 0 0
    data types should serialize integer -Infinity 0 0 0 67 0 0
    parallel should create the table 12 2 -11 13 48 1
    parallel should insert in parallel 651 686 36 106 40 37
    parallel should close the database 0 0 0 125 0 0
    parallel should verify that the database exists 0 0 0 50 0 0
    prepare invalid SQL should fail preparing a statement with invalid SQL 0 0 0 40 0 0
    prepare simple prepared statement should prepare, run and finalize the statement 0 0 0 129 0 0
    prepare inserting and retrieving rows should create the table 0 0 0 150 0 0
    prepare inserting and retrieving rows should insert 1000 rows 20 36 16 182 2 3
    prepare inserting and retrieving rows should prepare a statement and run it 1005 times 22 30 8 139 3 3
    prepare inserting and retrieving rows should have retrieved 1005 rows 0 0 0 Infinity 0 0
    prepare inserting with accidental undefined should create the table 0 0 0 125 0 0
    prepare inserting with accidental undefined should insert two rows 0 0 0 67 0 0
    prepare inserting with accidental undefined should retrieve the data 0 0 0 63 0 0
    prepare inserting with accidental undefined should have retrieved two rows 0 0 0 Infinity 0 0
    prepare retrieving reset() function should retrieve the same row over and over again 10 23 13 224 1 2
    prepare retrieving reset() function should have retrieved 10 rows 0 0 0 Infinity 0 0
    prepare multiple get() parameter binding should retrieve particular rows 1 1 0 153 0 0
    prepare multiple get() parameter binding should have retrieved 10 rows 0 0 0 NaN 0 0
    prepare prepare() parameter binding should retrieve particular rows 0 1 0 167 0 1
    prepare prepare() parameter binding should have retrieved 1 row 0 0 0 0 0 0
    prepare all() should retrieve particular rows 4 8 4 183 1 1
    prepare all() should have retrieved all rows 0 0 0 100 0 0
    prepare all() should retrieve particular rows 1 1 0 173 0 0
    prepare high concurrency should create the table 0 0 0 150 0 0
    prepare high concurrency should insert all values 16 24 8 146 7 12
    prepare high concurrency should retrieve all values 6 6 0 104 2 3
    prepare test Database#get() should get a row 0 0 0 180 0 1
    prepare test Database#get() should have retrieved all rows 0 0 0 NaN 0 0
    prepare Database#run() and Database#all() should create the table 0 0 0 700 0 0
    prepare Database#run() and Database#all() should insert 1000 rows 19 34 15 177 2 3
    prepare Database#run() and Database#all() should retrieve all rows 4 8 3 176 0 1
    profiling should profile a create table 0 1 0 200 0 1
    profiling should profile a select: profile select‡ NaN NaN NaN NaN NaN NaN
    profiling should profile a select 0 0 0 225 0 0
    rerunning statements should create the table 0 0 0 600 0 0
    rerunning statements should insert repeatedly, reusing the same statement 0 0 0 100 0 0
    rerunning statements should retrieve repeatedly, resuing the same statement 1 1 0 100 0 0
    rerunning statements should have inserted and retrieved the right amount 0 0 0 NaN 0 0
    scheduling scheduling after the database was closed 0 0 0 140 0 0
    scheduling scheduling a query with callback after the database was closed 0 0 0 100 0 0
    scheduling running a query after the database was closed 0 0 0 100 0 0
    serialize() and parallelize() should toggle 0 0 0 0 0 0
    serialize() and parallelize() should insert rows 1 5 4 375 0 1
    serialize() and parallelize() should have inserted all the rows after synchronizing with serialize() 24 33 9 135 4 4
    serialize(fn) should call the callback 24 39 15 165 2 5
    serialize(fn) should have inserted and retrieved all rows 0 0 0 0 0 0
    tracing Database tracing 1 1 0 136 0 0
    tracing test disabling tracing #1 1 0 0 73 1 0
    tracing test disabling tracing #2 0 0 0 150 0 0
    unicode should create the table 0 0 0 150 0 0
    unicode should insert all values 19 20 1 103 10 10
    unicode should retrieve all values 4 4 0 99 2 2
    unicode should have inserted and retrieved the correct amount 0 0 0 NaN 0 0
    query properties should upsert 0 1 0 122 0 0

    † This test succeeds on N-API but fails on NAN.
    ‡ This is an artifact from the Mocha tests.

  11. jschlight commented on Apr 30, 2020

    @jschlight
    jschlight
    on Apr 30, 2020
    Collaborator

    This is the raw data, in JSON format, used to create the table above .

    rawdata.txt

  12. mhdawson commented on May 4, 2020

    @mhdawson
    mhdawson
    on May 4, 2020
    MemberAuthor

    PR has landed in sqlite repo so closing this.

  13. mhdawson
    closed this as completedon May 4, 2020

  14. jschlight commented on Jul 1, 2020

    @jschlight
    jschlight
    on Jul 1, 2020
    Collaborator

    node-sqlite3 v5.0.0 with N-API support has been published to npm.

    https://github.com/mapbox/node-sqlite3/releases/tag/v5.0.0

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions