Author: Paul Tomblin

SQLite again

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.

RedHat, you suck

Ok, I found out why inn wouldn’t upgrade – I’ve been using “timecaf” for the news spool. This is a semi-binary format which is supposed to be faster and more efficient than “tradspool”, which is the old single file per article in a directory structure based on the newsgroup names that we all used to know and love. “timecaf” creates just a few files per day with multiple entries in each file. I forget why I stopped using “tradspool” because this machine is way overpowered, maybe it was to see if we could use it at NCF.

Timecaf has been working pretty well for me, but evidently it has binary file offsets embedded in the file. And RedHat (oh, sorry, the “Fedora Community”) arbitrarily decided to enable “Large File Support” in between Fedora Core 3 and Fedora Core 4. This means that each record in each “timecaf” file has a 32 bit file offset attached to it, but the program is expecting a 64 bit file offset. That makes it impossible for the program to find the records.

I tried a few things, including compiling the source without large file support, and I still couldn’t get it to work. So I threw in the towel and blew away the whole news spool. After all, this is usenet, every idea comes around again in a few weeks anyway.

Upgrade nearly painless

The WordPress SpamKarma plugin was complaining about invalid SQL syntax. A little investigation showed that SpamKarma was expecting MySQL 4.0 or newer, but reluctantly allowed you to use MySQL 3.x as long as you accepted there might be some problems. I’d had other problems with the old versions of software that were part of Fedora Core 3.

I decided it was time to upgrade to Fedora Core 4. I don’t know for sure, but I think I’ve been on FC3 for a couple of years now. I would rather upgrade to Debian Sarge, since this machine is really just a server now, and I hardly ever use X Windows on it. But that would require too much work. It appeared I could upgrade from FC3 to FC4 using yum, with minimum downtime, so I set aside some time today to do it.

The upgrade is now finished. It was surprisingly smoothly, except for one thing. Inn won’t run. I can’t even rebuild the history files with makehistory – it dies with a SEGV. I’ve tried all sorts of things, and I’ve tried writing to Russ, the main developer. I have a bad feeling that I’m going to have to switch to installing from source, and I don’t want to lose the advantages that you get when you let somebody else (in this case, the Fedora team) manage the upgrades and dependencies.

A couple of geeky items

1. I’ve been playing around with SQLite on my linode. The database generation script runs twice as fast as when I use MySQL. But I’m having one major problem with the update script – it’s probably due to the Perl DBD::SQLite, rather than SQLite itself, but if I insert into a VARCHAR column using


my $wptInsertStmt = $conn->prepare(qq{
INSERT INTO wp_test
(id, datasource_key, tpa)
VALUES (?, ?, ?)});

$wptInsertStmt->execute('abc', '111', 555);
$wptInsertStmt->execute('1E2', '222', 123);

the ids that look like numbers get interpreted as numbers, so that ‘1E2’ gets inserted as ‘100’. Both MySQL and PostgreSQL versions of this code get it right. I suspect I’m going to have to convert the code to use bind_params, which will bloat the code and lead to problems if I get it wrong. It also appears I have to close all my prepared statements.

2. I hereby officially declare THE DEATH OF THE FLOPPY. I was using a floppy to transfer files between my development machine and my test machine. But the shutter on the floppy I was using suddenly stopped working. So I got another floppy, and after a transfer or two, I started getting I/O errors on the new floppy. So I grabbed another, and the same thing happened. That’s when I realized that when the spring sprung, it probably fell off inside one of my floppy drives and is still wreaking havoc on every floppy I used since. Unfortunately I couldn’t find my USB drive, so I had to beg one from a cow orker. Even if it is at home, I’m thinking of buying one or more new ones. They’re certainly cheap enough.

3. The LUGOR mailing list today is full of the plaintive crys for help from a guy who seems to be doing web design for a guy who doesn’t seem to have gotten the idea that a corporate web site should be about content, not craptactular toys. The first request was for information on how to put an animated train running around the borders of the web page. Evidently the boss is a dentist who has toy trains running around the office, and he thinks this would transfer well to his web site. The other, equally brain dead request was for putting automatically translated versions (ie Systrans or Babelfish, not human translated) of all of his web site up and automatically directing people from those countries to the “translated’ version. Can you imagine what a great image you’d give to people in non-English speaking countries subjecting them to Babelfish versions? Especially since you’d be automatically redirecting them there, even if they spoke English. I told him to look at Engrish.com and imagine people all over the world submitting his site to the local equivalent of that site.