how to delete BLOB object from the data base

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

how to delete BLOB object from the data base

Zhu, Liang [AUTOSOL/ASSY/US]

Sqlite Experts,

I have a table contains the BLOB object,  I am inserting to the BLOB  data into the table at every 250ms,  I delete the oldest row at every 600ms, also I am reading the data from the database at every 10ms.  After almost of 100,000 insert, delete and select operations,   I am getting the  SQLite_locked error on delete,  and my data from the select statement are junk.    To prevent the database fermentation,  I tried PRAGMA incremental_vacuum(1000) and PRAGMA incremental_vacuum;
The performance improved some when I starting using PRAGMA incremental_vacuum but I am still getting the junk data.  Am I using the incremental_vacuum correctly?  And is there any optimal way to delete the BLOB object in the database table

Thank you,

Liang Zhu | Lead Software Engineer | Branson Ultrasonics
Emerson Automation Solutions | 41 Eagle Road | Danbury, CT 06810 | USA
T (203) 796-2235 | F (203) 796-0380
[hidden email]<mailto:[hidden email]>

The information contained in this message is confidential or protected by law. If you are not the intended recipient, please contact the sender and delete this message. Any unauthorized copying of this message or unauthorized distribution of the information contained herein is prohibited.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how to delete BLOB object from the data base

Robert Hairgrove
On 15.07.19 21:38, Zhu, Liang [AUTOSOL/ASSY/US] wrote:
> ... I am inserting to the BLOB data into the table at every 250ms, I
> delete the oldest row at every 600ms, also I am reading the data from
> the database at every 10ms...

How do you determine the "oldest" row? I believe the timestamps
generated by SQLite are only accurate to the nearest second.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how to delete BLOB object from the data base

David Raymond
In reply to this post by Zhu, Liang [AUTOSOL/ASSY/US]
The questions coming to mind at the moment:

What is the schema of the table holding the BLOBs?
What is the normal size for the blobs?
How are you doing the inserts and deletes?
What journal mode are you using?


I would think normal way to delete a record is the simple
delete from blob_table where primary_key_id = ?;


My understanding is that incremental vacuum basically says: "I want to shrink the file size by filling free pages at the front with data from the back to be able to truncate the file." It doesn't do any re-ordering or sorting, it just moves data from the end of the file into any free space closer to the front.
Since you're adding every 250ms and only deleting every 600, then the file size should be progressively increasing, and any free pages should be used up relatively quickly, so I don't think incremental vacuum would do a lot.



-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Zhu, Liang [AUTOSOL/ASSY/US]
Sent: Monday, July 15, 2019 3:39 PM
To: [hidden email]
Subject: [sqlite] how to delete BLOB object from the data base


Sqlite Experts,

I have a table contains the BLOB object,  I am inserting to the BLOB  data into the table at every 250ms,  I delete the oldest row at every 600ms, also I am reading the data from the database at every 10ms.  After almost of 100,000 insert, delete and select operations,   I am getting the  SQLite_locked error on delete,  and my data from the select statement are junk.    To prevent the database fermentation,  I tried PRAGMA incremental_vacuum(1000) and PRAGMA incremental_vacuum;
The performance improved some when I starting using PRAGMA incremental_vacuum but I am still getting the junk data.  Am I using the incremental_vacuum correctly?  And is there any optimal way to delete the BLOB object in the database table

Thank you,

Liang Zhu | Lead Software Engineer | Branson Ultrasonics
Emerson Automation Solutions | 41 Eagle Road | Danbury, CT 06810 | USA
T (203) 796-2235 | F (203) 796-0380
[hidden email]<mailto:[hidden email]>

The information contained in this message is confidential or protected by law. If you are not the intended recipient, please contact the sender and delete this message. Any unauthorized copying of this message or unauthorized distribution of the information contained herein is prohibited.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: how to delete BLOB object from the data base

Zhu, Liang [AUTOSOL/ASSY/US]
In reply to this post by Robert Hairgrove
We do the increment of 1000,  when the record reaches number which dividable by 1000, we delete the record.

-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Robert Hairgrove
Sent: Monday, July 15, 2019 4:00 PM
To: [hidden email]
Subject: [EXTERNAL] Re: [sqlite] how to delete BLOB object from the data base

On 15.07.19 21:38, Zhu, Liang [AUTOSOL/ASSY/US] wrote:
> ... I am inserting to the BLOB data into the table at every 250ms, I
> delete the oldest row at every 600ms, also I am reading the data from
> the database at every 10ms...

How do you determine the "oldest" row? I believe the timestamps generated by SQLite are only accurate to the nearest second.
_______________________________________________
sqlite-users mailing list
[hidden email]
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=4Y1ZhFy9bpH-wvkl_u5WMmphQqcZcyyY0DFEINZ4E6I&m=JgVnpDzYgWWXrCfLyoOP5F3fhNn1Gk3rk2t6Ak5G8Tw&s=9h4tFOoFd335e5M_jT34B0HuiTqMKR2HMqh_E0wcp1Y&e= 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how to delete BLOB object from the data base

Simon Slavin-3
In reply to this post by Zhu, Liang [AUTOSOL/ASSY/US]
On 15 Jul 2019, at 8:38pm, Zhu, Liang [AUTOSOL/ASSY/US] <[hidden email]> wrote:

> I am getting the  SQLite_locked error on delete, and my data from the select statement are junk.

I think you are saying that you get the result SQLITE_LOCKED.  To do this you must be

A) Using one database connection for two or more accesses (e.g. multi-threading) OR
B) Using shared-cache mode.

<https://www.sqlite.org/sharedcache.html>

Under these situations, SQLite has no way to prevent multiple threads from accessing the database at the same time.

To prevent it, make sure each thread uses a separate connection, and do not use shared-cache mode.  If you do this, then SQLite uses its busy/wait system, and will continually retry access until it reaches the time you've set as timeout:

<https://www.sqlite.org/c3ref/busy_timeout.html>

> We do the increment of 1000,  when the record reaches number which dividable by 1000, we delete the record.

This does not seem to be a good way to delete old rows.  Can you not check the rowid instead ?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: how to delete BLOB object from the data base

Keith Medcalf
In reply to this post by Zhu, Liang [AUTOSOL/ASSY/US]

Use one connection.  Use one thread.  As follows:

sqlite *db;
sqlite_stmt *beginstatement;
sqlite_stmt *commitstatement;
sqlite_stmt *insertstatement;
sqlite_stmt *deletestatement;
int i = 0;

sqlite_open_v2('database.db', &db, ...);
sqlite_prepare_v2(db, "begin immediate", &beginstatement, ...);
sqlite_prepare_v2(db, "insert into blah (_rowid_, data) values (?, ?);", &insertstatement, ...);
sqlite_prepare_v2(db, "delete from blah where _rowid_ < ?;", &deletestatement ... );
sqlite_prepare_v2(db, "commit;", &commitstatement, ...);
while 1:
  char *data = WaitForDataToArrive()
  if (!data) break;
  int64 ts = GetTheTimeInMSsinceTheUnixEpochAsInt64()
  if (!i) {
     sqlite_step(beginstatement);
     sqlite_reset(beginstatement);
  }
  sqlite_bind_int64(deletestatement, 0, ts - (numberofmillisecondsofdatatoretainindatabase));
  sqlite_step(deletestatement);
  sqlite_reset(deletestatement);
  sqlite_bind_int64(insertstatement, 0, ts);
  sqlite_bind_blob(insertstatement, 1, data);
  sqlite_step(insertstatement);
  sqlite_reset(insertstatement);
  i++;
  if (i>NumberOfRecordsInEachBatch) {
     sqlite_step(commitstatement);
     sqlite_reset(commitstatement);
  }
}
if (i) {
  sqlite_step(commitstatement);
  sqlite_reset(commitstatement);
}
sqlite_finalize(beginstatement);
sqlite_finalize(commitstatement);
sqlite_finalize(insertstatement);
sqlite_finalize(deletestatement);
sqlite_close_v2(db);

Use a separate connection for the data reading on its own thread/process.  Make sure the database is in WAL mode.  Don't bother with auto vacuum (not needed).  Space freed will be re-used automatically.  Assuming that the data rate is constant you will eventually end up with a stable database size.  Reading will not interfere with writing.  Make sure you do your reads in a deferred transaction if they require a REPEATABLE READ across multiple statements.  Be aware that your rowid (the timestamp) will overflow in a couple of hundred million years, so you will have to devise a different timestamp calculation method before then.  You are probably fine with a NumberOfRecordsInEachBatch being 1, but you might want to set it larger, particularly if your persistent storage is old and slow.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Zhu, Liang
>[AUTOSOL/ASSY/US]
>Sent: Monday, 15 July, 2019 15:11
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] Re: how to delete BLOB object from
>the data base
>
>We do the increment of 1000,  when the record reaches number which
>dividable by 1000, we delete the record.
>
>-----Original Message-----
>From: sqlite-users <[hidden email]> On
>Behalf Of Robert Hairgrove
>Sent: Monday, July 15, 2019 4:00 PM
>To: [hidden email]
>Subject: [EXTERNAL] Re: [sqlite] how to delete BLOB object from the
>data base
>
>On 15.07.19 21:38, Zhu, Liang [AUTOSOL/ASSY/US] wrote:
>> ... I am inserting to the BLOB data into the table at every 250ms,
>I
>> delete the oldest row at every 600ms, also I am reading the data
>from
>> the database at every 10ms...
>
>How do you determine the "oldest" row? I believe the timestamps
>generated by SQLite are only accurate to the nearest second.
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>https://urldefense.proofpoint.com/v2/url?u=http-
>3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-
>2Dusers&d=DwIGaQ&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=4Y1Z
>hFy9bpH-
>wvkl_u5WMmphQqcZcyyY0DFEINZ4E6I&m=JgVnpDzYgWWXrCfLyoOP5F3fhNn1Gk3rk2t
>6Ak5G8Tw&s=9h4tFOoFd335e5M_jT34B0HuiTqMKR2HMqh_E0wcp1Y&e=
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: how to delete BLOB object from the data base

Keith Medcalf
In reply to this post by Zhu, Liang [AUTOSOL/ASSY/US]
>-----Original Message-----
>From: Keith Medcalf [mailto:[hidden email]]
>Sent: Monday, 15 July, 2019 19:13
>To: 'SQLite mailing list'
>Subject: RE: [sqlite] [EXTERNAL] Re: how to delete BLOB object from
>the data base
>
>
>Use one connection.  Use one thread.  As follows:
>
>sqlite *db;
>sqlite_stmt *beginstatement;
>sqlite_stmt *commitstatement;
>sqlite_stmt *insertstatement;
>sqlite_stmt *deletestatement;
>int i = 0;
>
>sqlite_open_v2('database.db', &db, ...);
>sqlite_prepare_v2(db, "begin immediate", &beginstatement, ...);
>sqlite_prepare_v2(db, "insert into blah (_rowid_, data) values (?,
>?);", &insertstatement, ...);
>sqlite_prepare_v2(db, "delete from blah where _rowid_ < ?;",
>&deletestatement ... );
>sqlite_prepare_v2(db, "commit;", &commitstatement, ...);
>while 1:
>  char *data = WaitForDataToArrive()
>  if (!data) break;
>  int64 ts = GetTheTimeInMSsinceTheUnixEpochAsInt64()
>  if (!i) {
>     sqlite_step(beginstatement);
>     sqlite_reset(beginstatement);
>  }
>  sqlite_bind_int64(deletestatement, 0, ts -
>(numberofmillisecondsofdatatoretainindatabase));
>  sqlite_step(deletestatement);
>  sqlite_reset(deletestatement);
>  sqlite_bind_int64(insertstatement, 0, ts);
>  sqlite_bind_blob(insertstatement, 1, data);
>  sqlite_step(insertstatement);
>  sqlite_reset(insertstatement);
>  i++;
>  if (i>NumberOfRecordsInEachBatch) {
>     sqlite_step(commitstatement);
>     sqlite_reset(commitstatement);
      i = 0;  /* Add this cuz you are going to start another batch */

>  }
>}
>if (i) {
>  sqlite_step(commitstatement);
>  sqlite_reset(commitstatement);
>}
>sqlite_finalize(beginstatement);
>sqlite_finalize(commitstatement);
>sqlite_finalize(insertstatement);
>sqlite_finalize(deletestatement);
>sqlite_close_v2(db);
>
>Use a separate connection for the data reading on its own
>thread/process.  Make sure the database is in WAL mode.  Don't bother
>with auto vacuum (not needed).  Space freed will be re-used
>automatically.  Assuming that the data rate is constant you will
>eventually end up with a stable database size.  Reading will not
>interfere with writing.  Make sure you do your reads in a deferred
>transaction if they require a REPEATABLE READ across multiple
>statements.  Be aware that your rowid (the timestamp) will overflow
>in a couple of hundred million years, so you will have to devise a
>different timestamp calculation method before then.  You are probably
>fine with a NumberOfRecordsInEachBatch being 1, but you might want to
>set it larger, particularly if your persistent storage is old and
>slow.
>
>--
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>>-----Original Message-----
>>From: sqlite-users [mailto:sqlite-users-
>>[hidden email]] On Behalf Of Zhu, Liang
>>[AUTOSOL/ASSY/US]
>>Sent: Monday, 15 July, 2019 15:11
>>To: SQLite mailing list
>>Subject: Re: [sqlite] [EXTERNAL] Re: how to delete BLOB object from
>>the data base
>>
>>We do the increment of 1000,  when the record reaches number which
>>dividable by 1000, we delete the record.
>>
>>-----Original Message-----
>>From: sqlite-users <[hidden email]> On
>>Behalf Of Robert Hairgrove
>>Sent: Monday, July 15, 2019 4:00 PM
>>To: [hidden email]
>>Subject: [EXTERNAL] Re: [sqlite] how to delete BLOB object from the
>>data base
>>
>>On 15.07.19 21:38, Zhu, Liang [AUTOSOL/ASSY/US] wrote:
>>> ... I am inserting to the BLOB data into the table at every 250ms,
>>I
>>> delete the oldest row at every 600ms, also I am reading the data
>>from
>>> the database at every 10ms...
>>
>>How do you determine the "oldest" row? I believe the timestamps
>>generated by SQLite are only accurate to the nearest second.
>>_______________________________________________
>>sqlite-users mailing list
>>[hidden email]
>>https://urldefense.proofpoint.com/v2/url?u=http-
>>3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-
>>2Dusers&d=DwIGaQ&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=4Y1
>Z
>>hFy9bpH-
>>wvkl_u5WMmphQqcZcyyY0DFEINZ4E6I&m=JgVnpDzYgWWXrCfLyoOP5F3fhNn1Gk3rk2
>t
>>6Ak5G8Tw&s=9h4tFOoFd335e5M_jT34B0HuiTqMKR2HMqh_E0wcp1Y&e=
>>_______________________________________________
>>sqlite-users mailing list
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users