Hi,
Background
We've been refactoring our database to reduce the size of it.
Through
some simple logic we've managed to pull out 99% of the data to
reduce
the size from 51GB down to approx 600MB. This logic has been to
remove
rows that are almost the same but not quite identical. As with all
things, the thinking was the difficult bit, the execution somewhat
easier.
As part of the testing we've been doing, we've now hit on an odd and
weird problem to do with the COMMIT statement. A commit of a few
hundred (circa 600-800) rows takes approx 7 seconds whereas before
we
never even noticed it, though we now know it was two seconds before.
Each row is probably 1-2K of data, so its not very much at all.
Details of what we have tried:
1. We've turned synchronous on and off
PRAGMA synchronous=ON
and thats not made any difference.
2. We are using and have been using WAL mode for years.
PRAGMA journal_mode;
journal_mode
wal
3. We've tested that the server copies OK, we get a consistent
69MB/sec. This is not as fast we would like, but it's the same
across
all our virtual servers.
4. We've tested the commit on our existing 60GB database and it
takes
2 seconds, which is longer than we thought it would be. The server
for
the 60GB database is a large VPS with 8GB/8 cores and runs Ubuntu
14.04. The server we are testing on is a 2GB/2 core test server
running Ubuntu 16.04. Whilst the test server is smaller, we wouldn't
expect it to take 3 times longer to do a commit.
5. The code is identical across the servers. We are running Perl and
the DBI module. The code for doing a commit in Perl::DBI is
$dbh->do("COMMIT");
We are getting the expected performance elsewhere on the system
and
in the code. It's just the commit that is taking a long time.
6. The code we are committing is adding 600-800 lines to a table
that
used to be 200,000,000 rows in size. It's now 400,000 lines in size.
We are wondering if the deletion of the lines has had an impact we
didn't expect. We have vacuumed and analysed the database.
The schema for the table we insert into is
CREATE TABLE IF NOT EXISTS "Disruptions" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"version" integer NOT NULL,
"Disruption_id" INTEGER NOT NULL,
"status" integer NOT NULL,
"severity" integer NOT NULL,
"levelOfInterest" integer NOT NULL,
"category" integer NOT NULL,
"subCategory" integer NOT NULL,
"startTime" TEXT NOT NULL,
"endTime" text NOT NULL,
"location" integer NOT NULL,
"corridor" integer NOT NULL,
"comments" integer NOT NULL,
"currentUpdate" integer NOT NULL,
"remarkTime" TEXT NOT NULL,
"lastModTime" TEXT NOT NULL,
"CauseAreaPointX" real NOT NULL,
"CauseAreaPointY" real NOT NULL,
"Direction" TEXT
);
CREATE INDEX "Disruptions_Idx1" ON Disruptions ("location" COLLATE
NOCASE ASC, "corridor" COLLATE NOCASE ASC, "status" COLLATE NOCASE
ASC, "category" COLLATE NOCASE ASC, "severity" COLLATE NOCASE ASC,
"levelOfInterest" COLLATE NOCASE ASC, "version" COLLATE NOCASE ASC,
"subCategory" COLLATE NOCASE ASC, "startTime" COLLATE NOCASE ASC);
CREATE UNIQUE INDEX "Disruptions_Idx2" ON Disruptions
("Disruption_id"
COLLATE NOCASE ASC, "version" COLLATE NOCASE ASC, "category" COLLATE
NOCASE ASC, "subCategory" COLLATE NOCASE ASC);
CREATE UNIQUE INDEX "Disruptions_Idx3" ON Disruptions ("version"
COLLATE NOCASE ASC, "Disruption_id" COLLATE NOCASE ASC, "location"
COLLATE NOCASE ASC);
CREATE INDEX Disruptions_Idx5 ON Disruptions ("status",
"Disruption_id", "Severity", "levelOfInterest", "category",
"subCategory", "version");
We have checked that this schema is consistent across the databases.
We're about to recreate the table to see if that makes a difference.
Any help or advice welcomed.
Thanks
Rob
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
