[sqlite] NOP INSERT still writes to the DB/journal
Hello,
On Mon, Dec 7, 2015 at 5:05 PM, Patrick Donnelly
wrote:
> Update on this:
>
> On Mon, May 5, 2014 at 4:53 PM, Patrick Donnelly
> wrote:
>> Hi,
>>
>> I have an INSERT that looks like
>>
>> INSERT INTO T
>> SELECT ...
>>
>> which I'm running numerous times a second that generally does nothing
>> because the SELECT returns no rows. Unfortunately, I've found that
>> SQLite still does numerous disk writes anyway in this situation.
>>
>> Is my only option to eliminate the INSERT by using a SELECT first to
>> check if there are no rows? Something like:
>>
>> CREATE TEMPORARY VIEW V AS
>> SELECT ...
>> SELECT COUNT(*) FROM V;
>> /* If > 0 */
>> INSERT INTO T SELECT * FROM V;
>>
>> ?
>
> I've been able to reproduce it with this minimal example:
>
> CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT);
> .print -
> INSERT INTO t1
> SELECT 0
> WHERE 1 = 2;
> select changes();
>
> $ ./sqlite3 -vfstrace foo.db < test.sql
> ...
> -
> trace.xLock(foo.db,SHARED) -> SQLITE_OK
> trace.xAccess("/home/batrick/scm/cctools/chirp/src/sqlite-amalgamation-3090200/foo.db-journal",0)
> -> SQLITE_OK, out=0
> trace.xFileSize(foo.db) -> SQLITE_OK, size=3072
> trace.xRead(foo.db,n=16,ofst=24) -> SQLITE_OK
> trace.xFileSize(foo.db) -> SQLITE_OK, size=3072
> trace.xAccess("/home/batrick/scm/cctools/chirp/src/sqlite-amalgamation-3090200/foo.db-wal",0)
> -> SQLITE_OK, out=0
> trace.xFileSize(foo.db) -> SQLITE_OK, size=3072
> trace.xLock(foo.db,RESERVED) -> SQLITE_OK
> trace.xFileControl(foo.db,20) -> SQLITE_OK
> trace.xOpen(foo.db-journal,flags=0x806) -> SQLITE_OK
> trace.xDeviceCharacteristics(foo.db) -> 0x1000
> trace.xWrite(foo.db-journal,n=512,ofst=0) -> SQLITE_OK
> trace.xWrite(foo.db-journal,n=4,ofst=512) -> SQLITE_OK
> trace.xWrite(foo.db-journal,n=1024,ofst=516) -> SQLITE_OK
> trace.xWrite(foo.db-journal,n=4,ofst=1540) -> SQLITE_OK
> trace.xLock(foo.db,EXCLUSIVE) -> SQLITE_OK
> trace.xWrite(foo.db-journal,n=4,ofst=1544) -> SQLITE_OK
> trace.xWrite(foo.db-journal,n=1024,ofst=1548) -> SQLITE_OK
> trace.xWrite(foo.db-journal,n=4,ofst=2572) -> SQLITE_OK
> trace.xDeviceCharacteristics(foo.db) -> 0x1000
> trace.xRead(foo.db-journal,n=8,ofst=3072) -> SQLITE_IOERR_SHORT_READ
> trace.xSync(foo.db-journal,FULL) -> 0
> trace.xWrite(foo.db-journal,n=12,ofst=0) -> SQLITE_OK
> trace.xSync(foo.db-journal,FULL) -> 0
> trace.xWrite(foo.db,n=1024,ofst=0) -> SQLITE_OK
> trace.xWrite(foo.db,n=1024,ofst=2048) -> SQLITE_OK
> trace.xFileControl(foo.db,21) -> 12
> trace.xSync(foo.db,FULL) -> 0
> trace.xClose(foo.db-journal) -> SQLITE_OK
> trace.xDelete("/home/batrick/scm/cctools/chirp/src/sqlite-amalgamation-3090200/foo.db-journal",0)
> -> SQLITE_OK
> trace.xFileControl(foo.db,22) -> 12
> trace.xUnlock(foo.db,SHARED) -> SQLITE_OK
> trace.xDeviceCharacteristics(foo.db) -> 0x1000
> trace.xUnlock(foo.db,NONE) -> SQLITE_OK
> 0
> trace.xDeviceCharacteristics(foo.db) -> 0x1000
> trace.xUnlock(foo.db,NONE) -> SQLITE_OK
> trace.xClose(foo.db) -> SQLITE_OK
>
> No rows were inserted but there are several writes. This behavior
> seems to be caused by AUTOINCREMENT?
Now that there is a trivial test-case, I was hoping to find this on
the bugs page [1] but nothing yet. Is this not a bug?
[1] http://www.sqlite.org/src/rptview?rn=1
--
Patrick Donnelly
[sqlite] NOP INSERT still writes to the DB/journal
On Mon, Dec 7, 2015 at 11:51 PM, Simon Slavin wrote:
>
> On 8 Dec 2015, at 12:19am, Patrick Donnelly wrote:
>
>> There are still writes:
>
> Because you have not defined any transactions, each of your INSERT commands
> it getting wrapped in its own transaction. A transaction has to involve
> writes to disk.
>
> Try this ...
>
> BEGIN
> INSERT INTO t1 ...
> END
>
> See whether the INSERT command still involves as much writing.
This problem is not related to transactions. Richard Hipp posted an
example [1] where there are no writes for an "INSERT INTO T SELECT..."
on a table (without AUTOINCREMENT), without any explicit
transactions.
My own test example without AUTOINCREMENT has no writes:
$ cat test2.sql
CREATE TABLE t1(a INTEGER PRIMARY KEY, b integer);
INSERT INTO t1(b) VALUES (1);
.print -
begin transaction;
INSERT INTO t1 (b)
SELECT 0
WHERE 1 = 0;
end transaction;
select changes();
$ diff test.sql test2.sql
1c1
< CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b integer);
---
> CREATE TABLE t1(a INTEGER PRIMARY KEY, b integer);
3d2
< select * from sqlite_sequence;
$ rm foo.db*; ./sqlite3 -vfstrace foo.db < test2.sql
...
-
trace.xLock(foo.db,SHARED) -> SQLITE_OK
trace.xAccess("/home/batrick/sqlite-amalgamation-3090200/foo.db-journal",0)
-> SQLITE_OK, out=0
trace.xFileSize(foo.db) -> SQLITE_OK, size=2048
trace.xRead(foo.db,n=16,ofst=24) -> SQLITE_OK
trace.xFileSize(foo.db) -> SQLITE_OK, size=2048
trace.xAccess("/home/batrick/sqlite-amalgamation-3090200/foo.db-wal",0)
-> SQLITE_OK, out=0
trace.xFileSize(foo.db) -> SQLITE_OK, size=2048
trace.xLock(foo.db,RESERVED) -> SQLITE_OK
trace.xLock(foo.db,EXCLUSIVE) -> SQLITE_OK
trace.xFileControl(foo.db,22) -> 12
trace.xUnlock(foo.db,SHARED) -> SQLITE_OK
trace.xDeviceCharacteristics(foo.db) -> 0x1000
trace.xUnlock(foo.db,NONE) -> SQLITE_OK
0
trace.xDeviceCharacteristics(foo.db) -> 0x1000
trace.xUnlock(foo.db,NONE) -> SQLITE_OK
trace.xClose(foo.db) -> SQLITE_OK
[1]
http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2014-May/052855.html
--
Patrick Donnelly
[sqlite] NOP INSERT still writes to the DB/journal
On Mon, Dec 7, 2015 at 5:31 PM, Igor Tandetnik wrote:
> On 12/7/2015 5:05 PM, Patrick Donnelly wrote:
>>
>> No rows were inserted but there are several writes. This behavior
>> seems to be caused by AUTOINCREMENT?
>
>
> Could be creating sqlite_sequence table where there wasn't one before. I
> wonder if there are still writes on the second and subsequent no-op inserts.
There are still writes:
CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b integer);
INSERT INTO t1(b) VALUES (1);
select * from sqlite_sequence;
.print -
INSERT INTO t1 (b)
SELECT 0
WHERE 1 = 0;
select changes();
$ rm foo.db*; ./sqlite3 -vfstrace foo.db < test.sql
...
t1|1
-
trace.xLock(foo.db,SHARED) -> SQLITE_OK
trace.xAccess("/home/batrick/sqlite-amalgamation-3090200/foo.db-journal",0)
-> SQLITE_OK, out=0
trace.xFileSize(foo.db) -> SQLITE_OK, size=3072
trace.xRead(foo.db,n=16,ofst=24) -> SQLITE_OK
trace.xFileSize(foo.db) -> SQLITE_OK, size=3072
trace.xAccess("/home/batrick/sqlite-amalgamation-3090200/foo.db-wal",0)
-> SQLITE_OK, out=0
trace.xFileSize(foo.db) -> SQLITE_OK, size=3072
trace.xLock(foo.db,RESERVED) -> SQLITE_OK
trace.xFileControl(foo.db,20) -> SQLITE_OK
trace.xOpen(foo.db-journal,flags=0x806) -> SQLITE_OK
trace.xDeviceCharacteristics(foo.db) -> 0x1000
trace.xWrite(foo.db-journal,n=512,ofst=0) -> SQLITE_OK
trace.xWrite(foo.db-journal,n=4,ofst=512) -> SQLITE_OK
trace.xWrite(foo.db-journal,n=1024,ofst=516) -> SQLITE_OK
trace.xWrite(foo.db-journal,n=4,ofst=1540) -> SQLITE_OK
trace.xLock(foo.db,EXCLUSIVE) -> SQLITE_OK
trace.xWrite(foo.db-journal,n=4,ofst=1544) -> SQLITE_OK
trace.xWrite(foo.db-journal,n=1024,ofst=1548) -> SQLITE_OK
trace.xWrite(foo.db-journal,n=4,ofst=2572) -> SQLITE_OK
trace.xDeviceCharacteristics(foo.db) -> 0x1000
trace.xRead(foo.db-journal,n=8,ofst=3072) -> SQLITE_IOERR_SHORT_READ
trace.xSync(foo.db-journal,FULL) -> 0
trace.xWrite(foo.db-journal,n=12,ofst=0) -> SQLITE_OK
trace.xSync(foo.db-journal,FULL) -> 0
trace.xWrite(foo.db,n=1024,ofst=0) -> SQLITE_OK
trace.xWrite(foo.db,n=1024,ofst=2048) -> SQLITE_OK
trace.xFileControl(foo.db,21) -> 12
trace.xSync(foo.db,FULL) -> 0
trace.xClose(foo.db-journal) -> SQLITE_OK
trace.xDelete("/home/batrick/sqlite-amalgamation-3090200/foo.db-journal",0)
-> SQLITE_OK
trace.xFileControl(foo.db,22) -> 12
trace.xUnlock(foo.db,SHARED) -> SQLITE_OK
trace.xDeviceCharacteristics(foo.db) -> 0x1000
trace.xUnlock(foo.db,NONE) -> SQLITE_OK
0
trace.xDeviceCharacteristics(foo.db) -> 0x1000
trace.xUnlock(foo.db,NONE) -> SQLITE_OK
trace.xClose(foo.db) -> SQLITE_OK
--
Patrick Donnelly
[sqlite] NOP INSERT still writes to the DB/journal
Update on this:
On Mon, May 5, 2014 at 4:53 PM, Patrick Donnelly
wrote:
> Hi,
>
> I have an INSERT that looks like
>
> INSERT INTO T
> SELECT ...
>
> which I'm running numerous times a second that generally does nothing
> because the SELECT returns no rows. Unfortunately, I've found that
> SQLite still does numerous disk writes anyway in this situation.
>
> Is my only option to eliminate the INSERT by using a SELECT first to
> check if there are no rows? Something like:
>
> CREATE TEMPORARY VIEW V AS
> SELECT ...
> SELECT COUNT(*) FROM V;
> /* If > 0 */
> INSERT INTO T SELECT * FROM V;
>
> ?
I've been able to reproduce it with this minimal example:
CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT);
.print -
INSERT INTO t1
SELECT 0
WHERE 1 = 2;
select changes();
$ ./sqlite3 -vfstrace foo.db < test.sql
...
-
trace.xLock(foo.db,SHARED) -> SQLITE_OK
trace.xAccess("/home/batrick/scm/cctools/chirp/src/sqlite-amalgamation-3090200/foo.db-journal",0)
-> SQLITE_OK, out=0
trace.xFileSize(foo.db) -> SQLITE_OK, size=3072
trace.xRead(foo.db,n=16,ofst=24) -> SQLITE_OK
trace.xFileSize(foo.db) -> SQLITE_OK, size=3072
trace.xAccess("/home/batrick/scm/cctools/chirp/src/sqlite-amalgamation-3090200/foo.db-wal",0)
-> SQLITE_OK, out=0
trace.xFileSize(foo.db) -> SQLITE_OK, size=3072
trace.xLock(foo.db,RESERVED) -> SQLITE_OK
trace.xFileControl(foo.db,20) -> SQLITE_OK
trace.xOpen(foo.db-journal,flags=0x806) -> SQLITE_OK
trace.xDeviceCharacteristics(foo.db) -> 0x1000
trace.xWrite(foo.db-journal,n=512,ofst=0) -> SQLITE_OK
trace.xWrite(foo.db-journal,n=4,ofst=512) -> SQLITE_OK
trace.xWrite(foo.db-journal,n=1024,ofst=516) -> SQLITE_OK
trace.xWrite(foo.db-journal,n=4,ofst=1540) -> SQLITE_OK
trace.xLock(foo.db,EXCLUSIVE) -> SQLITE_OK
trace.xWrite(foo.db-journal,n=4,ofst=1544) -> SQLITE_OK
trace.xWrite(foo.db-journal,n=1024,ofst=1548) -> SQLITE_OK
trace.xWrite(foo.db-journal,n=4,ofst=2572) -> SQLITE_OK
trace.xDeviceCharacteristics(foo.db) -> 0x1000
trace.xRead(foo.db-journal,n=8,ofst=3072) -> SQLITE_IOERR_SHORT_READ
trace.xSync(foo.db-journal,FULL) -> 0
trace.xWrite(foo.db-journal,n=12,ofst=0) -> SQLITE_OK
trace.xSync(foo.db-journal,FULL) -> 0
trace.xWrite(foo.db,n=1024,ofst=0) -> SQLITE_OK
trace.xWrite(foo.db,n=1024,ofst=2048) -> SQLITE_OK
trace.xFileControl(foo.db,21) -> 12
trace.xSync(foo.db,FULL) -> 0
trace.xClose(foo.db-journal) -> SQLITE_OK
trace.xDelete("/home/batrick/scm/cctools/chirp/src/sqlite-amalgamation-3090200/foo.db-journal",0)
-> SQLITE_OK
trace.xFileControl(foo.db,22) -> 12
trace.xUnlock(foo.db,SHARED) -> SQLITE_OK
trace.xDeviceCharacteristics(foo.db) -> 0x1000
trace.xUnlock(foo.db,NONE) -> SQLITE_OK
0
trace.xDeviceCharacteristics(foo.db) -> 0x1000
trace.xUnlock(foo.db,NONE) -> SQLITE_OK
trace.xClose(foo.db) -> SQLITE_OK
No rows were inserted but there are several writes. This behavior
seems to be caused by AUTOINCREMENT?
--
Patrick Donnelly
[sqlite] misleading note in the documentation for WAL
If doing a SQLITE_CHECKPOINT_RESTART, the docs [1] say that the operation *ensures* the next writer will truncate the log: "This mode works the same way as SQLITE_CHECKPOINT_FULL with the addition that after checkpointing the log file it blocks (calls the busy-handler callback) until all readers are reading from the database file only. This ensures that the next writer will restart the log file from the beginning." This conflicts with [2]: "Whenever a write operation occurs, the writer checks how much progress the checkpointer has made, and if the entire WAL has been transferred into the database and synced and if no readers are making use of the WAL, then the writer will rewind the WAL back to the beginning and start putting new transactions at the beginning of the WAL. This mechanism prevents a WAL file from growing without bound." So if a reader begins a transaction before the next writer, then the log file will not be restarted? [I assume this is why SQLITE_CHECKPOINT_TRUNCATE was added?] [1] https://www.sqlite.org/c3ref/wal_checkpoint_v2.html [2] https://www.sqlite.org/wal.html -- Patrick Donnelly
[sqlite] Extremely long running END (EXCLUSIVE) TRANSACTION in WAL mode
Hi, I'm running into a problem where an `END TRANSACTION;` statement takes several seconds to complete (via sqlite3_profile) on a database in WAL mode. The transaction was exclusive (i.e. `BEGIN EXCLUSIVE TRANSACTION;`). The transactions are small with only a ~10 inserts of small data. Perhaps relevant: a single concurrent reader is creating/ending deferred transactions several times a second alongside the writer. My first thought was that an automatic checkpoint was causing the transaction to block for so long. However, based on my reading of the documentation, it appears that all automatic checkpoints are "passive" and so should not block the checkpointer? I don't see how the writer is being blocked for so long. Can anyone provide hints on how to further debug this? -- Patrick Donnelly
[sqlite] random row from group
Hi, I'm trying to find a way to select a random row from a group (rather than "arbitrary"). Something like: SELECT attr1, attr2 FROM foo GROUP BY attr1 ORDER BY attr1, random() but of course ORDER BY is done after GROUP BY has selected an arbitrary row. Looking online, I've seen non-standard solutions which aren't supported in sqlite. For example: select distinct on (id) id, attribute from like_this order by id, random() from http://stackoverflow.com/questions/16044828/select-random-row-for-each-group Any pointers would be appreciated! -- Patrick Donnelly ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] NOP INSERT still writes to the DB/journal
Hi, I have an INSERT that looks like INSERT INTO T SELECT ... which I'm running numerous times a second that generally does nothing because the SELECT returns no rows. Unfortunately, I've found that SQLite still does numerous disk writes anyway in this situation. Is my only option to eliminate the INSERT by using a SELECT first to check if there are no rows? Something like: CREATE TEMPORARY VIEW V AS SELECT ... SELECT COUNT(*) FROM V; /* If > 0 */ INSERT INTO T SELECT * FROM V; ? Thanks, -- Patrick Donnelly ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
