>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