randyl/extension-crash-sqlite

extension-crash-sqlite's Issues

Discussion: aw, snap when importing 2M records

Re-posted from an email thread, per request by Thomas Steiner, intended for use as a public discussion ground for the problem...

Good evening, folks,

Downloaded and running on Chrome v117 on Linux...

Doing such a large retrieve of a data set that way (via the worker/promiser API) is always going to be painful, as it has to collect the whole data set before responding and return the whole thing via a single massive postMessage().

My first recommendation would be to change:

    let response = await promiser('exec', {
        sql: 'SELECT * FROM t',
        resultRows: [],
        rowMode: 'object'
    });

to

    let response = await promiser('exec', {
        sql: 'SELECT * FROM t',
        callback: function(...){...}
        rowMode: 'object'
    });

where callback is a function which receives an event for each row as it's fetched. An example callback just tested with this extension:

    const resultRows = [];
    let response = await promiser('exec', {
        sql: 'SELECT * FROM t',
        callback: function(ev){
          if(undefined!==ev.row){
            if( 0 === ev.rowNumber % 1000 ) { console.log(ev.rowNumber); }
            resultRows.push(ev.row);
          }
        },
        rowMode: 'object'
    });
    console.log(`retrieve ${retrieveCount}: got ${resultRows.length} rows`);

The event object has 3 relevant properties:

• rowNumber: 1-based row number, or null at the end of the result set

• row: the result row. Its type depends on the rowMode argument. row==undefined at the end of the result set.

• columnNames: array of column names in the order they appear in the query.

That's not going to help much, if any, with the run time of a 2M-row result set, but it will give the developer some insight into where the run-time is going. Just eyeballing the result row counts in the console, it appears to be fetching 30k-40k rows/second on this modest computer.

The above variant crashed quickly during the 2nd half of the 2nd retrieve in 4 consecutive test runs. Unfortunately, though, there's literally zero information to go on beyond "aw, snap!" :(. The error reported on the Snap page, as in the screenshot of the source repo, is SIGILL (Illegal Instruction), which is not something we can directly trigger from JS or WASM. The implication of that seems to be that it's crashing in native browser-side code. There's no trace of direct JS or WASM involvement in the crash, though certainly at least one of them is playing a role here.

Ah, here we go: on the 5th test run, in the 2nd half of the 2nd retrieve, my Dev Tools unexpectedly paused with:

triggered by an array.splice() call, which suggests a native-side OOM as opposed to a WASM-side one. Tapping "continue" in the dev tools after poking around it for several minutes, it continued on its way (presumably some GC cleaned up while it was paused) and made it to the end of the 2M rows. On the next attempt it predictably died horribly in the 2nd half of the 2M result rows. (It doesn't always die at the same point: sometimes 1.1M, sometimes 1.7M.)

Beyond those meager details, there's unfortunately not much to go on regarding what the exact crash is and where it lies.