You do realize the number they quote is a MAX....not necessarily what you'll get.
With 16 transactions per second you're writing on transaction every 63ms. My understanding is that the hard drive in the netbook is 15ms access time so you're seeing 4 accesses per transaction with the way you're doing this test (write caching off, full sync). When you write a record you have to update last access times for example. Might be interesting to turn off last access updates and see if it changes your results much. http://www.cyberciti.biz/faq/linux-noatime-ext3-ext4-fstab-configuration/ 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
