• DEMO
  • TechTalk: Why does Hornbill use MySQL?
    • By Gerry Sweeney
    • In TechTalk
    • Posted 2016-10-13 22:25:16

    TechTalk: Why does Hornbill use MySQL?

    A customer posed the question - why is Hornbill running on top of a MySQL database and not Microsoft SQL? and I thought this topic would make a good TechTalk blog post - so here goes. 

    Firstly I should clarify that we actually use MariaDB and not MySQL, but for the purpose of this post I will refer generically to MySQL as (almost) everything I talk about relates equally to both MySQL and MariaDB. For anyone that does not know the history of MySQL and MariaDB you can read here: https://mariadb.org/about/

    Being open source and mature MySQL is very very reliable.  It is portable unlike Microsoft SQL Server which is deeply embedded into the Windows operating system which makes it less friendly to work with that something that is designed to run on *nx type platformss. If I need to re-instate a MySQL server in an emergency I can simply copy some files to a folder, and start mysql with some command-line options and I am up and running literally in seconds.  Now I know this is not something we would ordinarily do so whats the big deal? Well the fact that it can be done demonstrates the openness and flexibility of the technology.  Contrast that with Microsoft SQL Server which is propriatary and difficult to do anything with outside of the tools that Microsoft provide (installers etc).  MySQL being open source means we have lots and lots of options at our disposal.  Here is a perfect example of that flexibility in action.

    In our technology stack I wanted "stateless" database connections so we could run all of our SQL traffic over HTTP in order to allow request distribution and load balancing, but I obviously needed this to be efficient and have a very high throughput capability. To achieve that we developed our own HTTP -> SQL gateway, where we are in essence sending a composite MySQL wire protocol data stream over HTTP, recovering the data quickly and without serialisation/deserialisation.  This solution is not available off the shelf but because MySQL is open source we were able to implement this from ground up having it running in production in less than 12 weeks.  Having this sort of flexibility is key when you are providing a cloud service. MySQL gives us the possibility to do these things with ease and without restriction, with Microsoft SQL Server we would not have that option because its closed source and propriatary in nature.  This was an interesting project that gave us some great capabilities both in terms of stateless database I/O and real-time SQL activity monitoring and tracing. This is a whole other topic and can be the subject of another TechTalk blog post in the future - if this is of interest please leave a comment below and let me know.

    Microsoft SQL Server Is a great enterprise class database with superb tooling and lots and lots of enterprise-class functionality.  However, its not a great technology for building a cloud application with an open standards/open source technology stack, its achilles heel is the Windows Operating System, although to be fair to Microsoft they announced earlier this year that SQL Server is now available to run on Linux which is a great step forwards, but then there is the price!.  SQL Server tooling and its price make it perfect for enterprise use where you have no desire to get down and dirty under the hood.

    MySQL on the other hand is simple, it has less productivity tools that an enterprise organisation might need, but its open source, its very very fast and its internals are well documented and understood, and its code base has, and continues to be peer reviewed by an army of very bright, clever and capable people.  It runs on non-windows platforms which is good for companies like us who need the flexibility of easy provisioning, maintaining and supporting, and the performance we can get from an optimised *nx platform (Linux Cent OS in our case) as unbeatable. There is a reason why many major mainstream global scale applications are build on MySQL (think Facebook, Twitter, Google, Amazon RDS oh and Hornbill of course :), it is just better for building cloud applications for a whole host of reasons.

    The one final question to answer is why MariaDB instead of MySQL.  When Sun Microsystems bought the rights to MySQL that was very quickly followed by aggressive sales people trying to monetise MySQL and were pitching the idea that if your software depended on MySQL to run (ours did) then you have to pay a commercial licence, I think they were making the pricing up and getting what they could but I recall $2000/server/year being the sort of figure they were talking about and that presented us with a problem because we already had customers, quite a few of them running on MySQL, this meant that we could not realistically provide any upgrade to MySQL for our customers unless we either told every customer they would *have* to pay an extra $2k a year, or buy a commercial licence themselves, or we would have to foot the bill for many hundreds of customers. Since then Oracle bought Sun and now appear to be stagnating the evolution of MySQL, thankfully the original MySQL creator Monty Widenius decided to start MariaDB as a direct plugin replacement for MySQL and has since attracted a strong developer community who have mostly switched over to that project away from the Oracle/MySQL path.

    Now before I get hosed by the NoSQL, NewSQL or xxxSQL "It is not Webscale" :) fraternities, MySQL does have its limits and I know that, Facebook for example has documented its trials and tribulations with scaling MySQL and using memory cacheing (using a tool called memcache) to help elleviate database load. We too are having to shift workload and function form the database using fairly aggressive memory caching to give the database servers space to breath. For our application stack today though, the MySQL technology is working very well and I believe for us will continue to do so at least for the foreseeable future. 

    Please leave your comments below if you want to discuss any aspect of this article. 

    Categories

    Latest Posts

    • Hornbill iBridge for Linode

      2017-09-22 18:47:33
      Feature Friday

      Hornbill's iBridge is a great way to integrate with Linode, a cloud based solution for creating and managing virtual Linux servers...

    • Exactly Why do ITSM Vendors Lead with ITIL?

      2017-09-17 00:41:41
      Blog Posts

      The problem with niche markets like the ITSM space is there are different parties with different agendas and for the most part they are in direct conflict with each other...

    • Using WebHooks for Integration

      2017-09-16 18:23:16
      Feature Friday

      Web Hooks are a great way to integrate Hornbill with other applications. Web Hooks can send information to a HTTP Endpoint as soon as a record is created or updated, rather than relying on scheduled imports or the continually polling for data...