A project that I’m working on (OK, it’s TSDBExplorer) generates a metric shedload of database rows. For a record that says “This train runs between 01-01-2011 and 31-05-2011 on Mondays – Fridays”, the code generates a timetable for each day. It takes an age to import, and I hope it’s going to be fantastically quick at querying data.
There’s a big downside with ActiveRecord out-of-the-box – it takes a long time to INSERT a record in to a MySQL database. I left some INSERTs going at 9.50am, and they’d just about finished when I got back from the gym five hours later. 1.2 million rows in five hours is shockingly poor.
activerecord-import appears to solve the problem in the least impact way. To group up your INSERTs, you create new instances of a model object – say, Association. You push these in to an array, and then use the new ‘import’ method to do a mass INSERT.
I am quite happy at 62 minutes to insert 1.2 million rows, including processing, considering it’s an activity that only needs to be done twice, maybe three times a year.
Category: Technology
Doubled Sided Printing
Having run out of blank A4 paper and needing to print something, I decided “Hey, I’ve got 24 sheets, I’ll print this 40 page document double-sided!”
Why is it so difficult for me to get my head around how to do this? There are many ways I could screw up – pages back to front (printing odd and even pages on the same side), pages upside down (printing odd pages in one direction, and even upside down on the back), printing the odd pages in order, but the even pages in reverse order (the first sheet having page 1 on the front, and page 39 on the back), offsetting the pages by one sheet.
I only misprinted four pages. I call this a success 🙂
Seven days of 3
A little over a week ago, feeling horribly overcharged and dissatisfied with Orange, I terminated my contract and moved over to 3.
The process was incredibly smooth. I ordered a 3 SIM, which arrived the next day along with a free PAYG SIM. Orange sent me my PAC. I called 3, gave them my PAC – but had the usual overjoyed offshore callcentre guy thank me far too much for moving – and two days later, my number moved.
I have had one problem with data in the last week, and I was in a low-signal area and had to restart my phone. I could get a 3G signal when in the middle of nowhere in Kent yesterday, and browsing when in London is really very quick and responsive.
I made a good choice, and it’s £30/month cheaper than Orange.
I am on a train at the moment, updating my blog using my 3 PAYG data connection, which is equally fast and a very usable tool for when I’m out and about.
3, you’ve made me happy.
Goodbye Orange, Hello 3
I can’t remember exactly how long I’ve been with Orange for, but it’s been something in the order of a decade. That’s a long time.
I remember the days of Wildfire, withdrawn because they didn’t develop it and people stopped using it. I remember HSCSD, where I could get a blazingly fast (for the day) 28.8kbps connection out of my mobile. That’s been superseded by GPRS, 3G and UMTS. I remember, and loved, Everyphone, where I could divert all my incoming calls elsewhere if my battery ran out – but that’s just for business users now.
I remember being with a mobile phone network that was different, innovative and fun.
Now they’ve gone downhill in my view. They are slow to offer new phones, and they put special Orange firmware on them which means months of delays getting updates. Their GPRS service was good, but their 3G service is abysmal and I can’t even connect half the time (and I can tell when that is now – Android 2.3 shows the H or 3G icon in green if the connection will give you Internet access, and in grey if it isn’t) despite having good signal coverage. Their proxy and filter the life out of any Internet connection you do get, to the point at which proxying my web traffic over an SSH tunnel through their network is *faster*. They tease me with Orange Wednesdays, but I pay a lot for the privilege of half-price cinema tickets. To top it off, they took away my loyalty bonus when I dared upgrade my phone in-store because “the loyalty bonus only applies when you upgrade online” – why!?
Orange, you’ve had your day, and unless you suddenly manage to fix your 3G service for me and reduce your prices drastically, I’m using my “PAC code” and going to a 1-month rolling contract on 3 for a mere £10/month.
Android, Facebook and the undialable phone numbers
For some length of time, I’ve had problems with my Nexus One and the Facebook for Android application. Phone numbers from Facebook are imported in to the Android Contacts application with a country code but without a leading +, for example – 447700952155 rather than +447700952155. This makes them rather difficult to dial, as your mobile carrier won’t recognise them.
If you have this problem, try http://www.253below.com/prefixer (or search for Prefixer on the Android Market – find the 253 Below application). Set up a rule to match 44[0-9]+ and tell it to add a prefix of + to the number. Hey presto, you can now dial successfully.
A problem isn't always what it seems
I had a call today from somebody who was trying to use an Avaya IP Phone from their office at home. The phone has built-in IPSec VPN capability, and their phone switch is some distance away on – surprisingly – a DSL line. Call quality is not an issue, which is testament to Zen Internet‘s network quality.
In the office, the phone works fine. At home, it fails to establish a VPN, displaying an “Invalid PSK” error. Looking on the VPN router at the site with the softswitch, I see nothing untoward, so I set about asking the caller to tell me his default gateway address – which is the same address as the network range that the softswitch is no. No prizes for guessing why it doesn’t work.
Here I was, expecting a full-on IPSec debugging session, and it turns out to be IP addresses.
Open Transport Data
The Guardian published an article on National Rail Enquiries’ refusal to be sensible about licensing its data. Malcolm Barclay has mused on NRE’s inflexibility, claiming “They are stuck in the command & control mentality of the industrial age and have zero understanding of what open data is or it’s benefits”.
This whole debacle is reminiscent of Eric S Raymond’s “The Cathedral and The Bazaar” for me – where
TfL have had no problems making their data available free of change, and they’re working really hard to bring the Trackernet service back to life. Emer Coleman, Director of Digital Projects at the GLA, posted “But you can be assured that it definitely will be back and hopefully the solution will be so robust there will be no chance of it falling over like it did the last time. That is TfL’s main concern that once it foes back its there for good and in a robust way”. Hats off to them – TfL were caught by surprise with the popularity of the Trackernet API, and they’re tackling it head-on.
If TfL can be this innovative and forward-thinking, why can’t NRE?
NRE’s jealous data-guarding is not just limited to real-time train running information. If you want daily-updates of fares, timetable and routeing guide (the official definition of the routes you can use with ‘Any Permitted’ route tickets – a complicated beast that few people properly understand), you will need to part with £27,430 according to ATOC’s RSP Data Feeds document. That’s a ludicrous price that serves only to lock data on our country’s rail system away from prying eyes. Heck, it’s £600 if you want a CD with test/trial information.
This data is probably of most value to companies who are deeply involved in selling tickets, such as TheTrainLine and those who
Converting OSGB36 (Eastings/Northings) to WGS84 (Longitude/Latitude) in Ruby
The excellent people at the Greater London Assembly have released a list of bus stops and bus routes in London. The coordinates of each bus stop are in eastings and northings, and I wanted to convert these to longitude and latitude for my Ruby on Rails application.
Using the proj4rb gem and some projection definitions from spatialreference.org – with some help from Harry Wood’s blog, I came up with the following code:
#!/usr/bin/ruby
require 'rubygems'
gem 'proj4rb'
require 'proj4'
easting = 529978
northing = 186491
srcPoint = Proj4::Point.new(easting, northing)
srcProj = Proj4::Projection.new('+proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 +y_0=-100000 +ellps=airy +datum=OSGB36 +units=m +no_defs')
dstProj = Proj4::Projection.new('+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs')
dstPoint = srcProj.transform(dstProj, srcPoint)
print "lat=#{dstPoint.lat * (180 / Math::PI)}n"
print "lon=#{dstPoint.lon * (180 / Math::PI)}n"To convert WGS84 to OSGB36:
#!/usr/bin/ruby
require 'rubygems'
gem 'proj4rb'
require 'proj4'
latitude = 51.5623279577278
longitude = -0.126277004538848
srcPoint = Proj4::Point.new(longitude * (Math::PI / 180), latitude * (Math::PI / 180))
srcProj = Proj4::Projection.new('+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs')
dstProj = Proj4::Projection.new('+proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 +y_0=-100000 +ellps=airy +datum=OSGB36 +units=m +no_defs')
dstPoint = srcProj.transform(dstProj, srcPoint)
print "easting=#{dstPoint.x}n"
print "northing=#{dstPoint.y}n"Importing SSL certificates on Cisco IOS
A requirement came up to use Cisco’s AnyConnect VPN on a router. For this, an SSL certificate and corresponding private key is required – I used CAcert.org.
I will deliberately skip the detail of how to generate an RSA private key, create a CSR and get this signed by a CA. Straight to the chase – here is how to import the key and certificate on to an IOS router.
Use openssl rsa -in foo.key -pubout to display the corresponding public key for your private key foo.key. This will begin with BEGIN PUBLIC KEY.
Next, ensure your private key has a password – use openssl rsa -in foo.key. If it doesn’t, encrypt it with 3DES using openssl rsa -in foo.key -3des and specify a password.
On the IOS device in question:
# crypto key import rsa foo pem terminal to import the PEM encoded public key:
cr(config)#crypto key import rsa foo pem terminal strongpassword
% Enter PEM-formatted public General Purpose key or certificate.
% End with a blank line or "quit" on a line by itself.
-----BEGIN PUBLIC KEY-----
...Ensure there is a complete blank line after pasting the public key, and the router will then prompt:
% Enter PEM-formatted encrypted private General Purpose key.
% End with "quit" on a line by itself.Paste in the 3DES-encrypted key – it will begin BEGIN RSA PRIVATE KEY, and type ‘quit’ on a blank line at the end.
That’s it. It’s not straightforward, and I know I’ll forget if I don’t write it down!
Reducing Nagios' CPU load when using SNMP plugins
I have a virtual machine which has 100 hosts and 600 services being monitored through Nagios. 400 of these services are monitored via SNMP plugins.
One of our system administrators mentioned that this VM was quite CPU hungry, so I set about trying to lighten things up. I found that the simple act of adding a -M MIB-NAME switch in to a service check has dropped the load average from around 1.7 to 0.8 over 15 minutes.
Here’s how to do it:
First, look for any check_snmp plugin which uses an textual OID. Convert this to a numeric OID by using snmptranslate IF-MIB::ifOperStatus -Of. Use the numeric OID shown in place of the textual OID – this will save a few CPU cycles.
Unless you need to translate the returned values back to text – for example, if an enum is returned that needs to be translated in to text – don’t specify ‘-m’ on the command line at all. However, if you do need to translate the returned values, specify -m IF-MIB, or whichever MIB name appears before the :: when translating the textual OID to a numeric OID.
I’m happy, our sysadmin team are happy 🙂