Thanks for the suggestion and link. It seems that noatime has already been set for both partition types on my test setup. I turned atime back on and re-ran the tests.
The results are within error margins and therefore arguably the same as previous. I'd be interested in the results if others would run the script below. Thanks in advance Nick On 9 May 2011, at 13:31, Black, Michael (IS) wrote: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
