Posted on

The Australian Online Census 2016 Example of How-Not-To

error crossOne of the key problems with the 2016 online census was the architecture, but also the how the whole thing was organised and who was contracted for the job.

IBM, for the $9.6mln it got paid for the job, built something very clunky. They used Java, which is not bad per-se but the system also required Java on the client (browser) side which is just daft. The number of systems that either don’t have or can’t run client side Java is huge, and for the rest you get into version conflict mayhem. And it’s clunky, it’s a lot of code and heaviness to shuffle around which is not a great approach to build a scalable site.

If you think of the census form, the total amount of data gathered is not actually that big. It doesn’t require any particularly complicated database or storage setup.
Serving forms to clients is very light on web servers – if you then use Javascript logic to control the flow through the forms you can actually run most of the work on the client side, including intermediate local saving for the “just in case”. Then you produce a single submit with confirmation, and a transaction with a number of inserts into the database. The language used on the server end is not that important as its job is minimal. Most of the content served can be static, and might even be handled through a CDN.

The scale of the online census task is quite small, relative to many websites. Not only Twitter/Facebook/etc but many e-commerce sites have a vastly more complicated situation where they have to serve many different pages of which many are dynamic, lots of writes and shopping carts that get updated in chunks, then the whole checkout process…. and all that can work fine too. So the census is not a big or complicated problem, really. It just needs to be done right.

The fact that IBM, for $9.6mln, completely stuffed it, is a very serious indicator of where the relevant skills and innovation capability lies. For this type of job, not with IBM. Going with a big company does not guarantee good results. If you reckon this is a one-off, ask Queensland Health about their payroll debacle (SAP implemented by… IBM). Similarly, very expensive is not necessarily better. It can be just very costly, in so many respects.

ABS/IBM also declined the NextDC offer for datacenter level firewalling and DoS protection. Another serious mistake. But application architecture too affects security. When I googled for Census 2016 on census night, the first link that came up was a Census staff login. That’s just beyond astonishing. That should not be public at all. It doesn’t need to be on a public domain, and probably should be only accessible via a VPN.

The company that did the online Census 2016 load testing for another half million $ and bragged before census night about how well their team worked together with the ABS and IBM people, should also be seriously embarrassed about the shoddy job they delivered. From their own site:

“Revolution IT worked in a highly collaborative manner, and their subject knowledge, expertise and advice were key to achieve our project goals and objectives. We were impressed with how well they engaged with our e-Census solution provider (another private company). [IBM]”

Success is not defined by how well your team worked, it’s very simply proven by how well the system deals with the real world. In this case, it didn’t. At all. So, total process fail. It would have been very wise to wait with the bragging until after census night. If it holds up well, you can brag. Otherwise, you hush and no public embarrassment at least on that front. PR fail.

Their public statement (after census night) is at where they explain that the Census site was taken offline due to security concerns, and since security was not part of their brief, their performance was all ok and successful.  But come on now, how is security not part of any practical testing?  It is by nature an integral part of how things work online!  Implementation of security may impact performance, and obviously security aspects always impact availability – and without availability you have no performance at all.

All in all, Census 2016 is a brilliant example of “how not to” in modern online architecture.

And to prove all this again, two students at QUT in Brisbane just built the same in a few days and for about $500 which I understand was mostly pizza costs.

Read that story at (that write-up is rather populist simplistic, but the fact that a few students can very well design a site like this, and properly, is absolutely correct).

Posted on

Choosing Whether to Migrate to Another Database: Uber

Posted on

Found data leak of a company while giving a college lecture | Sijmen Ruwhof

Sijmen writes:

A few weeks ago I gave a guest lecture at the Windesheim University of Applied Sciences in The Netherlands. I graduated there and over the years I kept in contact with some of my teachers since then. One of the teachers told me recently that a lot of students wanted to learn more about IT security and hacking and asked me to give a lecture about it. Of course! And to keep it a bit juicy, I built in a hacking demonstration in my lecture.

Read the full story at

For any server that’s connected to the Internet (and these days, that’s most servers), security is very important.

Mind that as a fundamental, you have to regard any web server as compromised. Not that they necessarily are, but it’s a very useful baseline to use as these are the most visible servers and thus potentially the easiest targets. What information is present on the web server itself, and what information is on there that can be used to access other systems (and to what extent). Scary? Perhaps. But that’s no reason to not review and put sensible practices in place.

If you’d like to discuss ways to secure your online environment, or would like to see how your current setup holds up to the various security benchmarks, have a chat with us: Open Query offers a security review (ad-hoc consulting) package, and we also offer regular security check-ups for our subscription clients.

Posted on

Web Security: SHA1 SSL Deprecated

You may not be aware that the mechanism used to fingerprint the SSL certificates that  keep your access to websites encrypted and secure is changing. The old method, known as SHA1 is being deprecated – meaning it will no longer be supported. As per January 2016 various vendors will no longer support creating certificates with SHA1, and browsers show warnings when they encounter an old SHA1 certificate. Per January 2017 browsers will reject old certificates.

The new signing method, known as SHA2, has been available for some time. Users have had a choice of signing methods up until now, but there are still many sites using old certificates out there. You may want to check the security on any SSL websites you own or run!

To ensure your users’ security and privacy, force https across your entire website, not just e-commerce or other sections. You may have noticed this move on major websites over the last few years.

For more information on the change from SHA1 to SHA2 you can read:

To test if your website is using a SHA1 or SHA2 certificate you can use one of the following tools:

Open Query also offers a Security Review package, in which we check on a broad range of issues in your system’s front-end and back-end and provide you with an assessment and recommendations. This is most useful if you are looking at a form of security certification.

Posted on

Motivation to Migrate RDBMS

Companies that use a standard edition of Oracle’s database software should be aware that a rapidly approaching deadline could mean increased licensing costs.

Speaking from experience (at both MySQL AB and Open Query), typically, licensing/pricing changes such as these act as a motivator for migrations.

Migrations are a nuisance (doesn’t matter from/to what platform) and are best avoided as they’re intrinsically painful, costly and time-consuming. Smart companies know this.

When asked in generic terms, we generally recommend against migrations (even to MySQL/MariaDB) for the above-mentioned practical and business reasons. There are also technical reasons. I’ll list a few:

  • application, query and schema design tends to be most tuned to a particular RDBMS, usually the one the main developer(s) are familiar with. Features are used in a certain way, and the original target platform (even if non deliberate) is likely to execute most efficiently;
  • RDBMS choice drives hardware/network architecture. A migration should also include a re-think of this, to make optimal use of the database platform;
  • it’s quite rare (but not unheard of!) for an application to perform better on another platform, without putting a lot of extra work in. If extra work is on the table, then the original DB platform should also be considered as a valid option;
  • related to other points: a desire to migrate might be based on employees’ expertise with a particular platform rather than this particular application’s intrinsic suitability to that platform. While that can be a valid reason, it should be recognised as the actual reason as there are obviously cost/effort implications in terms of migration cost and other options such as training can be considered.
Nevertheless, a company that’s really annoyed by a vendor’s attitude can opt for the migration route, as they may decide it’s the path of less pain (and lower cost) in the long(er) term.

We do occasionally guide and assist with migrations, if after review it looks like a viable and sensible direction to take.

Posted on

Slow Query Log Rotation

Some time ago, Peter Boros at Percona wrote this post: Rotating MySQL slow logs safely. It contains good info, such as that one should use the rename method for rotation (rather than copytruncate), and then connect to mysqld and issue a FLUSH LOGS (rather than send a SIGHUP signal).

So far so good. What I do not agree with is the additional construct to prevent slow queries from being written during log rotation. The author’s rationale is that if too many items get written while the rotation is in process, this can block threads. I understand this, but let’s review what actually happens.

Indeed, if one were to do lots of writes to the slow query log in a short space of time, a write could block while waiting.

Is the risk of this occurring greater during a logrotate operation? I doubt it. A FLUSH LOGS has to close and open the file. While there is no file open, no writes can occur anyhow and they may be stored in the internal buffer of the lowlevel MySQL code for this.

In any case, if there is such a high write rate, that is an issue in itself: it is not useful to have the slow query log write that fast. Instead, you’d up the long_query_time and min_examined_rows variables to reduce the effectively “flow rate”. It’s always best to resolve an underlying issue rather than its symptom(s).

Posted on

Using Persistent Memory in RDBMS

People at Intel started the pmem library project some time ago, it’s open to the broader community at GitHub and  other developers, including Linux kernel devs, are actively involved.

While the library does allow interaction with an SSD using a good-old-filesystem, we know that addressing SSD through SATA or SAS is very inefficient. That said, the type of storage architecture that SSD uses does require significant management for write levelling and verifying so that the device as a whole actually lasts, and your data is kept safe: in theory you could write to an NVRAM chip, and not know when it didn’t actually store your data properly.

But there are other technologies, such as Memristor (RRAM) and Phase Change Memory (PCM, PRAM). Numonyx (founded by Intel and others, since acquired by Micron) was one of the companies developing PCM some years ago, to the point of some commercial applications. Somewhat oddly (in my opinion), Micron ditched their PCM line in 2014 focusing more on 3D NAND technology. In 2015, Intel and Micron announced that they were working on something called 3D XPoint but Micron denies that it’s based on PCM.

I like the concept of PCM because it has a lot of advantages over NAND technology. It’s very stable, doesn’t “bleed” to adjacent memory cells, if it writes correctly it’s stored correctly, and it’s fast. Not as fast as ordinary RAM, but it’s persistent! What I’ve been holding out for is just a small amount of PCM or similar storage in computers, phones, tablets and e-book readers.

In small mobile devices the advantage would be vastly reduced power consumption. ARM processors are able to put entire sections of the processor in standby to save power, but RAM needs to be powered and refreshed regularly. So with persistent memory, a device could maintain state while using hardly any power.

For RDBMS such as MySQL and MariaDB, persistent memory could be used for the InnoDB log files and other relatively small state information that needs to be persistently kept. So this storage would behave likely memory and be addressed as such (pmem uses mmap), but be persistent. So you could commit a transaction, your fsync is very quick, and the transactional information has been stored in a durable fashion. Very shiny, right?

It doesn’t need to be large, something like 512MB would be ample for RDBMS, and possibly much less for mobile devices.

I still reckon persistent memory space has huge potential – and I mention the mobile devices because that’s obviously a larger market. Previously Micron did work with Nokia on using NVM in phones, but as we all know Nokia was acquired and the Micron focus changed. I find the current state of it all quite disappointing, but I do hope the various players in this field will soon focus on this again properly and get the tech out there to be used!

If you happen to know of any current developments and activities, I’d like to hear about it!

Posted on

ARIN Reaches IPv4 Depletion | Team ARIN

ARIN is the American Registry for Internet Numbers, the organisation that hands out the blocks IP addresses.

Each region has its own, for instance Australia/Pacific has APNIC. Naturally, they work very closely together.

Open Query can assist you with planning and deployment of IPv6 capabilities on your front-end servers, as well as at the back-end if you wish. Not all hosting providers support IPv6, but many good ones do. MySQL/MariaDB can operate in a native IPv6 or dual stack environment. Open Query enabled its own front-end servers for IPv6 a number of years ago already.

ARIN’s IPv4 free pool has depleted. This is an important milestone for the Internet as now we now usher in the age of IPv6.


ARIN has reached depletion of the general IPv4 free pool today, 24 September 2015. We’ve been talking about the inevitability of IPv4 depletion for many years and have been educating the community about the need to get IPv6 resources and prepare public facing services for the IPv6 Internet, and now is the time to make sure you are taking steps toward preparing for IPv6 as soon as possible.

Posted on

on ORDER BY optimization | Domas Mituzas

An insightful exploration by Domas (Facebook) on how some of the MySQL optimiser’s decision logic is sometimes naive, in this case regarding ORDER BY optimisation.

Quite often, “simple” logic can work better than complex logic as chasing all the corner cases can just make things worse – but sometimes, logic can be too simple.

Everything must be made as simple as possible, but no simpler.
— Albert Einstein / Roger Sessions

Posted on

Copyright on APIs and header files? | Upstarta