Hello all,
First of all, I'm a complete novice with respect to SQLite so apologies if
there is an obvious answer to my question. I've also posted this question in
IRC, and it was suggested that I post the question to this mailing list. We're
running a test-bed of an object store (Openstack Swift) which uses SQLite to
record information on the objects that are stored in a container. The table
itself (called "object") is very simple, it contains the modification time of
the object, the size of object, the md5sum of the object, and the content-type
of the object. We are seeing a performance anomaly on updates to existing
object records in the SQLite DB. If the container DB is sufficiently large
(about 10 million objects, 3.3GB) then the time to update records at the
beginning of the database by order of entry is anomalously high. The time is
particularly bad for the first approx. 100K records, is somewhat better for the
next 900K records, and settles down to a consistent average from
approx 1 million records on. If this consistent average time is around 7
seconds for 10,000 updates, then we see times of about 170 seconds for 10,000
updates on the first 100K records. We don't see this anomalously high update
time if we start the updates after the first 1 million records.
Note that table updates are performed by first DELETEing the entry and then
INSERTing the changed entry.
Does anybody have any idea why we're seeing this behaviour, and what we can do
to fix it?
Note that Chunky_Ks (IRC) has suggested that B-tree rebalancing could be the
fundamental issue.
Thanks.
SQL Schema follows:
1 database: main
Tables:
container_stat
incoming_sync
object
outgoing_sync
container_stat:
CREATE TABLE container_stat (
account TEXT,
container TEXT,
created_at TEXT,
put_timestamp TEXT DEFAULT '0',
delete_timestamp TEXT DEFAULT '0',
object_count INTEGER,
bytes_used INTEGER,
reported_put_timestamp TEXT DEFAULT '0',
reported_delete_timestamp TEXT DEFAULT '0',
reported_object_count INTEGER DEFAULT 0,
reported_bytes_used INTEGER DEFAULT 0,
hash TEXT default '00000000000000000000000000000000',
id TEXT,
status TEXT DEFAULT '',
status_changed_at TEXT DEFAULT '0',
metadata TEXT DEFAULT '',
x_container_sync_point1 INTEGER DEFAULT -1,
x_container_sync_point2 INTEGER DEFAULT -1
incoming_sync:
CREATE TABLE incoming_sync (
remote_id TEXT UNIQUE,
sync_point INTEGER,
updated_at TEXT DEFAULT 0
);
CREATE TRIGGER incoming_sync_insert AFTER INSERT ON incoming_sync
BEGIN
UPDATE incoming_sync
SET updated_at = STRFTIME('%s', 'NOW')
WHERE ROWID = new.ROWID;
END;
CREATE TRIGGER incoming_sync_update AFTER UPDATE ON incoming_sync
BEGIN
UPDATE incoming_sync
SET updated_at = STRFTIME('%s', 'NOW')
WHERE ROWID = new.ROWID;
END;
object:
CREATE TABLE object (
ROWID INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
created_at TEXT,
size INTEGER,
content_type TEXT,
etag TEXT,
deleted INTEGER DEFAULT 0
);
CREATE INDEX ix_object_deleted_name ON object (deleted, name);
CREATE TRIGGER object_delete AFTER DELETE ON object
BEGIN
UPDATE container_stat
SET object_count = object_count - (1 - old.deleted),
bytes_used = bytes_used - old.size,
hash = chexor(hash, old.name, old.created_at);
END;
CREATE TRIGGER object_insert AFTER INSERT ON object
BEGIN
UPDATE container_stat
SET object_count = object_count + (1 - new.deleted),
bytes_used = bytes_used + new.size,
hash = chexor(hash, new.name, new.created_at);
END;
CREATE TRIGGER object_update BEFORE UPDATE ON object
BEGIN
SELECT RAISE(FAIL, 'UPDATE not allowed; DELETE and INSERT');
END;
outgoing_sync:
CREATE TABLE outgoing_sync (
remote_id TEXT UNIQUE,
sync_point INTEGER,
updated_at TEXT DEFAULT 0
);
CREATE TRIGGER outgoing_sync_insert AFTER INSERT ON outgoing_sync
BEGIN
UPDATE outgoing_sync
SET updated_at = STRFTIME('%s', 'NOW')
WHERE ROWID = new.ROWID;
END;
CREATE TRIGGER outgoing_sync_update AFTER UPDATE ON outgoing_sync
BEGIN
UPDATE outgoing_sync
SET updated_at = STRFTIME('%s', 'NOW')
WHERE ROWID = new.ROWID;
END;
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
