[yunqa.de] Re: Regular cleanup needed for SQLite databases?
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Thu, 27 Jan 2011 12:58:30 +0100
On 27.01.2011 09:25, Tobias Rapp wrote:
> I recently had the case that the MS Access database file of one program
> became "fragmented": It contained less than 5 rows but still was over 10
> Megabytes in file size and insert/delete of rows took more than 4 seconds.
> Finally it was necessary to replace the database file with a new, unused
> instance to get rid of the performance problems.
>
> I plan to change the database engine from MS Access to SQLite now. Is this
> something that can happen to (DI)SQLite database files, too? Do I need to
> regulary perform some clean-up maintenance on it? My application is running
> 24h/7days a week and using the database file for persistence of running jobs
> (that is amount of rows is low but amount of insert/update/deletes is high).
In principal, all disk files may suffer from fragmentation, more
precisely file system fragmentation (see
http://en.wikipedia.org/wiki/File_system_fragmentation). Database files
are no exception.
To work against this, most database engines do not shrink their data
files after they DELETE records. Instead they reuse empty space when
required by an INSERT or UPDATE.
Generally, this counters file fragmentation, but it can also increase
the size of the data file in worst case scenarios.
In my experience, DISQLite3 does not tend to grow its database file size
excessively for a balanced mixture of INSERTs, UPDATEs and INSERTs. I
have attached a simple project which demonstrates this scenario and
shows that the database file does not grow beyond a reasonable size
after a certain number of operations.
For explicit control of the database file size, DISQLite3 offers two SQL
commands:
* VACUUM rebuilds the entire database, reclaims free database pages and
reduces the size of the database file.
* PRAGMA auto_vacuum = full at every transaction commit moves free
b-tree pages to the end of the database file and truncates the file.
For your scenario, PRAGMA auto_vacuum = full will probably be the best
option. Decide freely after reading the detailed documentation and
discussion in the DISQLite3 help.
Ralf
{ DISQLite3 demo project to simulate continuous INSERT, UPDATE and DELETE
of random data. This demo shows that deleted database pages are in fact
reused and the database file size does not grow excessively, even if
VACUUM or PRAGMA auto_vacuum is not used. }
program DISQLite3_Continuous_Insert_Update_Delete;
{$APPTYPE CONSOLE}
{$I DI.inc}
uses
{$IFDEF FastMM}FastMM4, {$ENDIF}Windows, SysUtils, DISQLite3Api;
const
Changes_Count = $10000;
Changes_per_Transaction = $200;
FileName = 'test.db3';
//----------------------------------------------------------------------------
function GetSizeOfFile(const FileName: string): Int64;
var
lpFileInformation: WIN32_FILE_ATTRIBUTE_DATA;
Size: record
case Integer of
0: (LowPart: LongWord; HighPart: LongWord);
1: (QuadPart: Int64);
end;
begin
Result := 0;
if GetFileAttributesEx(PChar(FileName), GetFileExInfoStandard,
@lpFileInformation) then
begin
Size.LowPart := lpFileInformation.nFileSizeLow;
Size.HighPart := lpFileInformation.nFileSizeHigh;
Result := Size.QuadPart;
end
else
RaiseLastOSError;
end;
var
c: Cardinal;
Data: array[0..$FFF] of AnsiChar;
DB: sqlite3_ptr;
insert_stmt, update_stmt, delete_stmt, begin_stmt, commit_stmt:
sqlite3_stmt_ptr;
inserts, updates, deletes: Cardinal;
r, t: Cardinal;
begin
try
try
Randomize;
sqlite3_check(sqlite3_initialize);
try
sqlite3_check(sqlite3_open(FileName, @DB));
try
{ Uncomment next line to enable full auto-vacuum. }
// sqlite3_check(sqlite3_exec(DB, 'PRAGMA auto_vacuum = full', nil,
nil, nil), DB);
sqlite3_check(sqlite3_exec(DB,
'CREATE TABLE IF NOT EXISTS t (a TEXT)', nil, nil, nil), DB);
sqlite3_check(sqlite3_prepare_v2(DB,
'BEGIN', -1, @begin_stmt, nil), DB);
sqlite3_check(sqlite3_prepare_v2(DB,
'COMMIT', -1, @commit_stmt, nil), DB);
sqlite3_check(sqlite3_prepare_v2(DB,
'INSERT INTO t VALUES (?)', -1, @insert_stmt, nil), DB);
sqlite3_check(sqlite3_prepare_v2(DB,
'UPDATE t SET a = ? WHERE' +
' RowID > abs(random()) % (SELECT max(RowID) FROM t) LIMIT 1', -1,
@update_stmt, nil), DB);
sqlite3_check(sqlite3_prepare_v2(DB,
'DELETE FROM t WHERE' +
' RowID > abs(random()) % (SELECT max(RowID) FROM t) LIMIT 1', -1,
@delete_stmt, nil), DB);
WriteLn('Each . represents ', Changes_per_Transaction, ' changes
wrapped inside a transaction.');
inserts := 0; updates := 0; deletes := 0;
c := Changes_Count;
t := 0;
repeat
if t >= Changes_per_Transaction then
begin
sqlite3_check(sqlite3_step(commit_stmt), DB);
sqlite3_check(sqlite3_reset(commit_stmt), DB);
t := 0;
Write('.');
end;
if t = 0 then
begin
sqlite3_check(sqlite3_step(begin_stmt), DB);
sqlite3_check(sqlite3_reset(begin_stmt), DB);
end;
r := Random(3);
case r of
0:
begin
sqlite3_check(sqlite3_bind_text(insert_stmt,
1, Data, Random(SizeOf(Data)), SQLITE_STATIC), DB);
sqlite3_check(sqlite3_step(insert_stmt), DB);
sqlite3_check(sqlite3_reset(insert_stmt), DB);
Inc(inserts);
end;
1:
begin
sqlite3_check(sqlite3_bind_text(update_stmt,
1, Data, Random(SizeOf(Data)), SQLITE_STATIC), DB);
sqlite3_check(sqlite3_step(update_stmt), DB);
sqlite3_check(sqlite3_reset(update_stmt), DB);
Inc(updates);
end;
2:
begin
sqlite3_check(sqlite3_step(delete_stmt), DB);
sqlite3_check(sqlite3_reset(delete_stmt), DB);
Inc(deletes);
end;
end;
Inc(t);
Dec(c);
until c = 0;
if t > 0 then
begin
sqlite3_check(sqlite3_step(commit_stmt), DB);
sqlite3_check(sqlite3_reset(commit_stmt), DB);
end;
sqlite3_check(sqlite3_finalize(begin_stmt), DB);
sqlite3_check(sqlite3_finalize(commit_stmt), DB);
sqlite3_check(sqlite3_finalize(insert_stmt), DB);
sqlite3_check(sqlite3_finalize(delete_stmt), DB);
sqlite3_check(sqlite3_finalize(update_stmt), DB);
{ Uncomment next line to run VACUUM after database operations. }
// sqlite3_check(sqlite3_exec(DB, 'VACUUM', nil, nil, nil), DB);
WriteLn; WriteLn;
WriteLn('Inserts: ', inserts);
WriteLn('Updates: ', updates);
WriteLn('Deletes: ', deletes);
WriteLn('Total DB Changes: ', sqlite3_total_changes(DB));
WriteLn('File Size (bytes): ', GetSizeOfFile(FileName));
finally
sqlite3_check(sqlite3_close(DB));
end;
finally
sqlite3_check(sqlite3_shutdown);
end;
except
on e: Exception do
WriteLn(e.Message);
end;
finally
WriteLn;
WriteLn('Done - Press ENTER to exit');
ReadLn;
end;
end.
Other related posts:
