BSD Talk #42 has an interview Karl Lehenbauer about FlightAware.com. FlightAware tracks flight information, so far example here is their page on live flights to and from Sacramento Executive Airport. There is a lot of information that they are making available for free. Here is an outline of some of the more interesting bits that were mentioned by Karl during the interview.
All of FlightAware’s systems are 64-bit AMD based computers running FreeBSD 6.x, specifically the FreeBSD/amd64 port. They use PostgreSQL for the database back end. Slony 1 is being used to replicate data. Hard drives are in a RAID 1 (mirroring) configuration using 3ware controllers.
Now for some numbers:
- Receiving the data and processing it puts them about 6 minutes behind real time
- Generating one map can be done in about 160 milliseconds of CPU time
- Capable of generating several million maps a day
- About 1 TB of stored data
- Approximately 40 million position updates on air craft per day
PostgreSQL wasn’t able to keep up with the updates so they wrote a memory resident database service queries. I’m still not exactly clear on what the relationship is between PostgreSQL and their memory resident database, which uses about 1 GB of RAM.
Nice to see a company putting FreeBSD and PosgreSQL to good use. I’m curious about the 40 million inserts per day number. Bring on the math!
- 40,000,000 inserts per day
- 40,000,000 / 24 = 1,666,667 inserts per hour
- 1,666,6667 / 60 = 27,778 inserts per minute
- 27,227 / 60 = 463 inserts per second
So that boils down to about 463 inserts per second on average. I’d expect that their actual peak requirements are much higher than that (perhaps two or three times that number?). That is just data that they are receiving, that doesn’t include the queries being run against their system to power the website. This brings up another question, how much bandwidth do they have dedicated to receiving these updates? It is possible that each individual update is fairly small (lat, long, src, dest, flight id, airline, plane type, etc) so that might not be too bad. Even at 256 bytes per update, doing 40 million of those a day adds up very quickly.
Right now their website provides the following numbers:
Currently Tracking
Tracking 4,986 airborne aircraft (224 VFR) with 21,205,488 total flights in the database.
FlightAware has tracked 48,940 arrivals in the last 24 hours.
Cool stuff.
6 replies on “FlightAware: FreeBSD and PostgreSQL”
The 40M inserts per day is accurate, and you are correct that at peak times it’s much greater than 460 inserts per second. This is what we use the memory resident database for — to provide a rapid way to store it and to rapidly provide query results for aircraft in-flight and generate the track logs to the map servers, etc.
Once a flight completes or certain other conditions are met, the flight track is logged to the PostgreSQL database, but as a single transaction. So for example a three hour flight might generate 200-300 position updates, but when the flight completes, those are stored in the database as a single insert, and we let PostgreSQL’s “toast” system efficiently handle the somewhat oversized record.
As to the six minute delay, five minutes of that are intentionally added upstream by the FAA. Latencies transmitting and receiving the data and then decoding, interpreting, and processing it takes a little less than a minute.
RAID 1 seems surprising. I’d have thought RAID 5. I wonder what their reason behind that choice was. It’s a very impressive site though. One of the most interesting and useful open-source implementations I’ve seen.
[…] First up, I’ll pick on my own site. We’ll take my entry on FlightAware: FreeBSD and PostgreSQL as an example. You’ll see the red circle come up and indicate that there are 4 errors. If you dig into those errors you’ll find that they are from the Google AdSense ads on the site. Unfortunately the code causing the errors is inserted by the Google ads when the page is loaded, so there doesn’t seem to be much that I can do about that. I may try to chance the style of ads to see if I can make it work later on. […]
FlightAware using PostgreSQL…
We’ve recently added FlightAware to the PostgreSQL Featured Users list and added some new quotes to the quotes archive.
FlightAware provides on-line tracking of airline flights for free. The service runs on BSD and PostgreSQL and makes use of Slon…
[…] es halt auf Qualität ankommt […]
[…] with the BSD license, another notable release last month was FreeBSD 7.2, a great platform to complement the reliability of PostgreSQL and another way to gain some performance improvements. Speaking of […]