Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
________________________________
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Nick [maill...@css-uk.net]
Sent: Sunday, May 08, 2011 3:52 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Transaction speed too slow?
>From http://www.sqlite.org/faq.html#q19 it says "A transaction normally
>requires two complete rotations of the disk platter, which on a 7200RPM disk
>drive limits you to about 60 transactions per second."
Using Linux/Ubuntu 10.04 on an otherwise idle Atom powered Nettop with a
5400RPM disk drive I was expecting circa 45 transactions per second. However
using the basic python script below I seem to be getting a lot slower results,
namely:
ext3 partition: 16 transactions per second
jfs partition: 9 transactions per second
:memory: 15798 transaction per second
Does anyone have an explanation for this?
The script used is below. I'd be grateful if people could confirm whether it is
just my hardware or a common result
FULL RESULTS:
nick@Haribo:~$ sudo hdparm -W 0 /dev/sda
/dev/sda:
setting drive write-caching to 0 (off)
write-caching = 0 (off)
nick@Haribo:~$ python write-transactions-1.py
Run Number: 1, Location: :memory:
0:00:00.000108
0:00:00.000058
0:00:00.000057
0:00:00.000057
0:00:00.000056
0:00:00.000056
0:00:00.000057
0:00:00.000057
0:00:00.000057
0:00:00.000056
Time Avg: 0.000062
Trans/sec Avg: 16155
Run Number: 2, Location: write-transactions-1.db
0:00:00.099678
0:00:00.121630
0:00:00.110672
0:00:00.099599
0:00:00.110782
0:00:00.099542
0:00:00.121776
0:00:00.099599
0:00:00.121794
0:00:00.099624
Time Avg: 0.108470
Trans/sec Avg: 9
---------------------------------------------------
nick@Haribo:~$ sudo hdparm -W 1 /dev/sda
/dev/sda:
setting drive write-caching to 1 (on)
write-caching = 1 (on)
nick@Haribo:~$ python write-transactions-1.py
Run Number: 1, Location: :memory:
0:00:00.000113
0:00:00.000057
0:00:00.000056
0:00:00.000056
0:00:00.000056
0:00:00.000057
0:00:00.000057
0:00:00.000056
0:00:00.000056
0:00:00.000056
Time Avg: 0.000062
Trans/sec Avg: 16129
Run Number: 2, Location: write-transactions-1.db
0:00:00.001438
0:00:00.000898
0:00:00.000989
0:00:00.000984
0:00:00.000982
0:00:00.001945
0:00:00.001059
0:00:00.001169
0:00:00.000914
0:00:00.001063
Time Avg: 0.001144
Trans/sec Avg: 874
--------------------------------------
SCRIPT
# Test Benchmark for Transactions speed per second using built in SQLite
import sqlite3
import datetime
contype = [":memory:", "write-transactions-1.db"]
for runidx, runtype in enumerate(contype):
# Heading
print "Run Number: %d, Location: %s" % (runidx + 1, runtype)
con = sqlite3.connect(runtype)
con.isolation_level = None
con.execute("PRAGMA synchronous = FULL")
times = []
# Create the table
con.execute("drop table if exists person")
con.execute("create table person(firstname, lastname)")
# Measure 10 points
for run in range(10):
# BEGIN transaction
con.execute("begin")
# Fill the table
con.execute("insert into person(firstname, lastname) values (?, ?)",
("alex","bob"))
# Start the clock
begin = datetime.datetime.now()
# COMMIT transaction
con.execute("commit")
# Stop the clock
end = datetime.datetime.now()
# Record the time
times.append(end - begin)
# Measure 10 points
for run in range(10):
# Display the time results
print times[run]
# Print Average
import operator
td = reduce(operator.add, times)
totalresultsec = (float(td.microseconds) + (td.seconds + td.days * 24 *
3600) * 10**6) / 10**6
avgsec = totalresultsec / 10.0
print "Time Avg: %f" % avgsec
print "Trans/sec Avg: %0.0f" % (1.0/avgsec)
con.close()
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
