Some database cleanups

Since the popularity of the site is increasing I need to do some optimizations to keep the site running smoothly.

As announced before, I ran some database restructuring batch jobs during the weekend. The last one was actually started on Monday morning and the execution was completed early Tuesday morning. I hope the slowdowns were not too noticeable, I tried to make them run slowly enough to not interfere with normal use.

I'm currently looking at ways to clean up some unused data from the aprs.fi database. There's quite a lot of automatically or erroneously generated targets stored in the database, most of which are not really looked at by anyone. They just inflate my tables and make the prefix browsing view very cluttered. The performance impact caused by the extra targets isn't really noticeable, but it's just not very nice to have them there.

If you're keeping an eye on the statistics, you might have noticed that today I deleted about 60000 targets. Those were all APRS objects, which had only a single point stored (non-moving), which were last announced over a week ago, and which were announced for less than 12 hours. Almost all of them were automatically generated earthquake and severe weather warning objects, which don't really need to be here any more.

There's also an awful lot of regular APRS targets which have a corrupted source callsign. Most usually one or two characters have been lost from the beginning of the callsign. For example: 1VAJ (J41VAJ), 3GXT-2 (W3GXT-2) and 3TVX-9 (VE3TVX-9). You can find these by looking at the prefix browsing view, picking a strange prefix starting with a number, and looking up the correct original call in the "nearby stations" list of the info page. I wonder how the callsigns get mangled like this, and how could I remove these from the database without accidentally removing some valid data.

Slowdowns caused by maintenance work

I'm doing some changes in the database structures during this Friday evening and Saturday morning, so there might be some slowdowns in the aprs.fi service. In particular, updated data might take a few minutes to be inserted in the database. No data should be lost, though, it'll just be delayed. Thank you for your patience!

APRS in русский язык

Thanks to UZ2HZ, RA1AMW, UA3IRS and UR7IMM, aprs.fi is now available in the Russian language! The site has now been translated to 20 languages. Although there's not so much APRS activity in Russia yet, Russian is the primary language of about 164 million people living in quite a few countries. Even here in Helsinki, it's the third most common language.

There are spots of APRS coverage in (at least) St. Petersburg, Moscow and Novgorod.

Single-target APRS packet paths

The APRS packet path plotting now works while tracking a single target. As a natural side-effect, all of the stations used as digipeaters and igates by the tracked target will also be shown on the map.

On Saturday I also made some significant changes in the backend code, which should make it scale out a bit better. I added some more in-memory caching which should speed things up so that the addition of things like APRS packet paths will not cause a significant performance hit.

"First heard" algorithm fix, and APRS-IS server failover

I've just fixed the "first heard by" algorithm to mark the igate as the first receiving station, when an intact WIDE1-1,WIDE2-1 path is seen. It was incorrectly guessing that the packet might have been digipeated because there is a WIDE2-1 in there, but the untouched WIDE1-1 clearly says that the packet has not been digipeated yet.

I've also added support for failing over to secondary APRS-IS servers automatically, so now I don't have to do anything manually if my own APRS-IS server fails (or is taken down for maintenance). This should prevent today's outage from happening again.

A little outage in APRS data collection

While I was at the hosting site adding memory to the second aprs.fi frontend today, I also did maintenance on the Sun Enterprise 4500 server which hosts finland.aprs2.net (the Tier 2 APRS-IS server which aprs.fi uses as the data source) and the aprs.fi backup database. So I configured the primary aprs.fi server to connect to another APRS-IS server while finland.aprs2.net was down, but made a mistake in the configuration, so the APRS-IS data collection broke. I didn't notice the alarms which it triggered, because I was working hard with the memory upgrade and the Sun reboot.

As a result, aprs.fi didn't collect APRS data between 11:35:43 UTC and 14:21:13 UTC. Sorry about that, I'll try to be more careful next time.

The memory upgrade itself went fine. The server should perform quite well for a while now.

Frontend memory upgrade on Sunday

I'll be upgrading the second aprs.fi frontend web server from 4 GB to 8 GB of RAM tomorrow. I'll do the installation some time during Sunday afternoon Finnish local time (that'll be Sunday morning in the US). I'll move it's IP address to the primary frontend server for the duration of the upgrade, so the service should be available all the time (unless something unexpected happens).

The upgrade should speed up the second frontend a bit (not that it would really have been slow before), and make it do the job for another year or so. It's been a gig or two on the swap before, but there hasn't been significant swap I/O happening normally, so it hasn't really been a problem. This is just a proactive upgrade to keep it performing as well as before, with the increased usage.