I wrote a few days ago about a problem I was having with SQLite, or rather with DBD::SQLite. Turns out that one should never assume that the version you installed on one machine is the same as the version you installed on the other. After making sure the machine I was testing on was up to DBD::SQLite version 1.11, that part of it worked fine.
I’ve been doing some timing tests on the a generator task that generates 26915 waypoints, and doing one at a time it takes about 45-50 seconds and doing two at a time takes twice as long (about 1:30-1:40), as opposed to MySQL which takes 3:40 for one at a time, and 4:45 for two at a time. The fact that the SQLite one takes twice as long when there are two running makes me think it’s probably CPU-bound. The fact that it’s way, way faster than the MySQL alternative makes me think this is definitely worth pursuing.
But there’s a wrinkle. According to a post on the SQLite mailing list, one program can’t commit a write while another one is doing a query, even if the writes don’t involve the same tables. I guess SQLite’s database level locking is pretty stupid. But that’s the problem – there are three different types of things going on:
- Database reloads – these only happen about once a month, only one at a time, and involve reloading FAA and DAFIF data into the waypoint, comm_freqs, runways and other similar tables. The reload scripts can take a hour or more to run.
- Database generations – these run in the background, and just query those same tables that the reloads are loading. Lots of these run can run at once, and lots are run every day. As mentioned above, they only take a few minutes to run.
- Choosing generating options in the web site. These tasks run after clicking one form page on the navaid.com generator and generating the response page. These mostly do some queries, but as well they track what options you’ve chosen so that they will be the defaults next time you come back. It does that by updating some tables which are not involved in the database generations.
Obviously a user doesn’t want to be sitting there waiting for their page to load for as long as it takes somebody else’s generation script to run. I’m going to have to try putting the tables that are used for storing these options in a different database to see if that will enable the pages to update in a reasonable time.
I found out about SQLite a year or so ago, and have been keeping it in mind should I ever have a project that needs it. So while I haven’t used it, I’ve been reading up on it. The FAQ at sqlite.org says that it locks the whole database file whenever anything needs to update it, which is what you’re seeing.
It’s 6:30AM, so I’m a little fuzzy and probably haven’t thought things through.
You could try is to cache the results somewhere. Generate a hash from the parameters, and then use that as the key, or something. I don’t know how many duplicates you generate, so it may not save you anything. You’d obviously have to clear the cache on a database reload.
You could also create a temporary database for each generation, populate it, and then delete it when it’s done.