A quick update on handling the the select query. We have some speed results which are quite illuminating and positive.
We?ve been playing around with building a few web services, one of which was the postcode lookup that people have kindly helped with. We have one service that is a simple insert into a database. One piece of data comes in (some GPS information), and we need to get it into a database table as quickly as possible. Its basically a data logger. The other is the postcode lookup, where a user requests a GPS location based on a postcode. The postcode lookup uses a single table in SQLite with a unique postcode as a key. We had written some rubbish shell scripts to test both services, which was lazy and pretty useless. So we decided to do the job properly and do a load test using Siege as the weapon of choice (pun intended). We like Siege as its dead simple and works very well. We can argue over the merits of Siege vs roadrunner vs whatever later. Both tests done on a Mac Pro quad core with 32GB of RAM. Test 1 - Simulating 100 concurrent users doing 100 requests one after the other. This uses morbo as a wrapper for the Perl script Transactions: 10000 hits Availability: 100.00 % Elapsed time: 11.25 secs Data transferred: 0.53 MB Response time: 0.11 secs Transaction rate: 888.89 trans/sec Throughput: 0.05 MB/sec Concurrency: 99.32 Successful transactions: 10000 Failed transactions: 0 Longest transaction: 0.17 Shortest transaction: 0.00 Mmmm? Just under 900 requests per second. Test 2 - This is simulating writing a single (small) line of data to a table. No special set up has been done regarding pragmas or anything like that. Its a pretty simple ?standard? setup. our macs have SSD rather than HDD though. This uses morbo as a wrapper script around the perl. We simulate 100 concurrent users writing 100 requests again. Transactions: 10000 hits Availability: 100.00 % Elapsed time: 33.15 secs Data transferred: 0.02 MB Response time: 0.33 secs Transaction rate: 301.66 trans/sec Throughput: 0.00 MB/sec Concurrency: 99.51 Successful transactions: 10000 Failed transactions: 0 Longest transaction: 0.52 Shortest transaction: 0.05 Thats 10,000 inserts into the database. We cleared the table out beforehand and then checked that the table had 10,000 records in afterwards. It did. Test 3: As per test1 but using hypnotoad (don?t you just love the names). This is a more performant wrapper for Perl. Transactions: 10000 hits Availability: 100.00 % Elapsed time: 6.53 secs Data transferred: 0.53 MB Response time: 0.06 secs Transaction rate: 1531.39 trans/sec Throughput: 0.08 MB/sec Concurrency: 91.40 Successful transactions: 10000 Failed transactions: 0 Longest transaction: 0.92 Shortest transaction: 0.00 Performance is almost (ish) doubled to just over 1500 transactions per second. No changes to the Perl scripts. Test 4: as per test2 but using hypnotoad. Database tables cleared out before starting Transactions: 10000 hits Availability: 100.00 % Elapsed time: 13.16 secs Data transferred: 0.02 MB Response time: 0.13 secs Transaction rate: 759.88 trans/sec Throughput: 0.00 MB/sec Concurrency: 95.96 Successful transactions: 10000 Failed transactions: 0 Longest transaction: 0.50 Shortest transaction: 0.00 Double the performance to just over 750 transactions per second. No changes to Perl scripts. Summary We?re rather pleased with the performance of Mojolicious as a web server (or rather web service) with SQLite. The Perl script for the postcode lookup is 96 lines long. The Perl script for the GPS capture is 190 lines and uses a slightly different framework for a number of reasons which are too embarrassing to go into. I have no idea how these compare to other systems, but I think 700 web based insertions per second is pretty good. I have no doubt we could improve the speed with some careful optimisations but to be brutally honest, its not worth it for us. Hope this helps somebody, it validates our choice of software so we?re pretty pleased. Rob
