Commit() takes 50 seconds after inserting about 100000 rows

optimizationsqlitetransaction

I have a sqlite database that is, (I think), fairly small.

I has about 1 million rows in a couple of table and the total space on disk is about 150 Mb.

I then open a transaction and insert about 100000 rows, the time it takes is about 10 seconds and I think this is acceptable for that number of rows, (and given the fact that my app does some 'other' processing.

My concern is when I do the 'Commit()' itself.

It takes around 50 seconds to write the data to disk… (around 10Mb is actually written).

This is what I do:

-- pseudo code for brevity...
Data Source=database.db;Version=3;Pooling=True;Max Pool Size=100;

PRAGMA journal_mode=WAL;
PRAGMA wal_autocheckpoint = 16;
PRAGMA journal_size_limit = 1536;
PRAGMA cache_size = 10000;
PRAGMA temp_store = MEMORY;

-- BEGIN TRANS

-- 100K Inserts/ 10K Deletes

-- COMMIT

Can anybody think why the commit would take so much longer than the entire transaction?

Any suggestions on how I could speed the commit up?

Best Answer

In WAL mode, the database must do regular checkpoints to prevent the log from growing infinitely large.

So after a large amount of changes, it decides to do an automatic checkpoint (especially since you have set wal_autocheckpoint to a rather small value).

You can move the checkpoint delay to another time (but not avoid it altogether) by doing checkpoints manually.

Related Solutions

Postgresql – Postgres requires commit or rollback after exception

The problem I have with your example is that you're talking about JDBC behaviour, but also using explicit "start transaction" etc commands, which seems a bit of a clash, since I'd expect you'd use JDBC's auto-commit mode to manage transactions.

If you are in auto-commit mode, then the two inserts will each be in their own transaction, and the throw of a SQLException for the first one will not affect the second.

If you are not in auto-commit mode, then an implicit "start transaction" is generated before the first insert, and the second insert cannot be processed until the transaction is rolled back. This behaviour is quite different from if you were executing the script with psql.

(JDBC does not specify whether drivers/connections should default to auto-commit on or off, you should always explicitly set it)

Postgresql treats any error processing a statement as immediately aborting the transaction-- essentially like the XACT_ABORT mode in SQL Server. The intent being that if you submit a sequence of commands as a transaction, each one is dependent on the previous ones, so the failure of any one invalidates all the subsequent ones.

If this isn't the behaviour you want inside a transaction, you need to surround the potentially-aborting updates with creating a savepoint, and rolling back to that savepoint in case of an error.

Beware of looking at very old discussions of behaviour (bugs over ten years old definitely count), as at some point in Postgresql's history, there was a session variable called autocommit, and the behaviour could have been quite different. That variable is gone now, replaced (as I understand it) with the concepts of the database or the JDBC driver automatically wrapping commands inside transactions (so in fact there is not really any such thing as non-transactional interaction with postgresql).

Here is what happens when you execute the script you suggest with psql:

steve@steve@[local] =# start transaction;
START TRANSACTION
steve@steve@[local] *=# create table test(id int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
steve@steve@[local] *=# insert into test values (1);
INSERT 0 1
steve@steve@[local] *=# commit;
COMMIT
steve@steve@[local] =# 
steve@steve@[local] =# -- Following statement throws a SQLException(duplicate key) in
steve@steve@[local] =# -- PG, SS and ORacle
steve@steve@[local] =# insert into test values (1);
ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(1) already exists.
steve@steve@[local] =# 
steve@steve@[local] =# -- Following statement behaves differently for different DBMS:
steve@steve@[local] =# -- SS and OR: No error...statement runs fine
steve@steve@[local] =# -- PG: Another SQLException thrown...must rollback or commit
steve@steve@[local] =# insert into test values (99);
INSERT 0 1

In order to get the same behaviour as you wrote in the script, you'd have to turn off auto-commit before doing the insert- that stops the JDBC driver from issuing an implicit "start transaction" before it executes the next statement. If you put that implicitly-generated transaction into the psql script, it produces the error you describe:

steve@steve@[local] =# start transaction; -- generated by JDBC driver
START TRANSACTION
steve@steve@[local] *=# -- Following statement throws a SQLException(duplicate key) in
steve@steve@[local] *=# -- PG, SS and ORacle
steve@steve@[local] *=# insert into test values (1);
ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(1) already exists.
steve@steve@[local] !=# 
steve@steve@[local] !=# -- Following statement behaves differently for different DBMS:
steve@steve@[local] !=# -- SS and OR: No error...statement runs fine
steve@steve@[local] !=# -- PG: Another SQLException thrown...must rollback or commit
steve@steve@[local] !=# insert into test values (99);
ERROR:  current transaction is aborted, commands ignored until end of transaction block

As an illustration of why this behaviour exists, consider what happens if I run the first transaction again. The intent is "create the table and populate it with a single row":

steve@steve@[local] =# start transaction;
START TRANSACTION
steve@steve@[local] *=# create table test(id int primary key);
ERROR:  relation "test" already exists
steve@steve@[local] !=# insert into test values (1);
ERROR:  current transaction is aborted, commands ignored until end of transaction block
steve@steve@[local] !=# commit;
ROLLBACK

So as soon as a problem is detected ("test" already exists), the remaining data manipulation isn't appropriate (the row already existed too, anyway)

Mysql – the best storing device for DB transaction log

You really need a fast HDD, but you need a properly sized innodb_log_file_size.

Why not SSD for MySQL ? I learned something from this layout from a FaceBook Engineer's blog

I wrote old posts about this

Aug 14, 2013 : How do I determine how much data is being written per day through insert, update and delete operations?
Feb 06, 2014 : MySQL on SSD - what are the disadvantages?
Aug 12, 2014 : Wordpress blog where pages are almost-always served from cache: SSD or more RAM?

I even mentioned doing this for PostgreSQL : Postgres Write Performance on Intel S3700 SSD

BTW See my post MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined? for determining the right size for innodb_log_file_size. You don't want a small HDD for ib_logfile0 and ib_log_file1.

Give it a Try !!!

Best Answer

Related Solutions

Postgresql – Postgres requires commit or rollback after exception

Mysql – the best storing device for DB transaction log

Related Question