Category: Software

MySQL 5.6 – Memcached / NoSQL Support and More

MySQL 5.6 has been released with some interesting new features and performance increases:

  • What’s New in MySQL 5.6
  • DBA and Developer Guide to MySQL 5.6
  • InnoDB Integration with memcached:MySQL 5.6 includes a NoSQL interface, using an integrated memcached daemon that can automatically store data and retrieve it from InnoDB tables, turning the MySQL server into a fast “key-value store” for single-row insert, update, or delete operations. You can still also access the same tables through SQL for convenience, complex queries, bulk operations, application compatibility, and other strengths of traditional database software.

    With this NoSQL interface, you use the familiar memcached API to speed up database operations, letting InnoDB handle memory caching using its buffer pool mechanism. Data modified through memcached operations such as ADD, SET, INCR are stored to disk, using the familiar InnoDB mechanisms such as change buffering, the doublewrite buffer, and crash recovery. The combination of memcached simplicity and InnoDB durability provides users with the best of both worlds.

  • Multi-threaded Slaves
  • Improved IPv6 Support – both in the bind to address option and the INET_ATON() function.
  • Replication improvements.

All in all, some nice new features. Especially the memcached integration.

That said, MariaDB seems to be making inroads on MySQL with some distributions considering a switch. Some interesting reading from that project includes:

Git Web Applications (aka GitHub Alternatives)

I’ve been through the mill with a few of these and it’s a growing list of options. I should start by saying that I love GitHub and use it regularly for a large number of projects (including some of my own, some for my company and some for my customers). The only problem I have with GitHub is that I just haven’t made the jump to trust it with our proprietary code – the primary asset of the company. Particularly after a high profile security breech.

Now, nothing I’ve come across yet comes close to GitHub. But two we use daily have good matching features:

  • Gitorious –  whether they intended it or not, it’s a good clone of GitHub but always a little behind on features. It’s also a Ruby on Rails application and the documentation is getting far better. When we started, it was pretty… shite, to be honest. Installing and upgrading was a pain. They have addressed this with http://getgitorious.com/ which includes a virtual appliance for VirtualBox so you can be up and running in minutes.
  • Atlassian Stash – if you’re a small team (<= 10 users) then this could be for you. Installation is pretty easy, it looks great, is very fast (over HTTP/S, but it’s inbuilt SSH client is extremely slow) and is very easy to use. Pull requests are also done really really well. It’s a full Java webapp so it does require some CPU and memory. For <= 10 users, my definite favorite. Thereafter though it gets expensive – e.g. for up to 25 users you’ll fork out $1,800 / annum. That’s not entirely unaffordable but if you’re using Stash, then you may also be using their other products such as Jira, Confluence, Bamboo, Fisheye and Crucible. Now that adds up to a hefty bill!

One’s I have not tested and so cannot speak authoritively on include:

  • GitLab – Looks like a very interesting alternative.
  • RhodeCode – they say it will “change the way you manage your code”.
  • Gitolite – a quick look at the available information for this definitely puts it in the also ran category. There’s no polish or anything nice to entice one to even try it.
  • Gitosis – in their own words: “Manage git repositories, provide access to them over SSH, with tight access control and not needing shell accounts”. The project appears dead.

 

Residential Property Price Register

Ireland finally has a residential property price register thanks to the newly formed Property Services Regulatory Authority.

Unfortunately their interface to this data is extremely cumbersome with a constant CAPTCHA challenge.

We knocked out our own version here: http://www.opensolutions.ie/ppr.

Kudos to the PSRA for opening up the information as they explain here.

All our code is licensed under BSD and is available here on GitHub.

Nagios Plugin for Checking Backups via rsnapshot

We’ve just added a check_rsnapshot.php script to our nagios-plugins bundle on Github. This script will verify rsnapshot backups via Nagios using a number of checks / tests:

  • minfiles – checks the number of files in a snapshot against a minimum expected number;
  • minsize – checks the size of a snapshot against a minimum expected size;
  • log – parses the rsnapshot log to ensure the most recent runs for each retention period completed successfully;
  • timestamp – checks for files created server side containing a timestamp and thus ensuring snapshots are succeeding;
  • rotation – checks that retention directories are being rotated; and
  • dir-creation – checks that retention directories are being created.

Please see this Github wiki page for more information including instructions.

Analysing MySQL Slow Query Logs

MySQL has a really useful feature that allows it to log slow queries where slow is a minimum time defined by you in micro seconds. It helps a lot is diagnosing website outages or slow responsiveness issues after the fact.

Unfortunately I couldn’t find any nice graphical tools for analysing these but there are a few command line tools:

mysqldumpslow

MySQL’s own tool, mysqldumpslow, which aggregates queries and allows you to sort them by: query time or average query time; lock time or average lock time; rows sent or average rows sent; or the number of queries.

Percona’s MySQL Slow Query Log Analyser

Dating from 2006, Percona’s Peter Zaitsev wrote about their own version of a slow query log analyser (local copy) which has given me good results. Note that their micro time patch has since been incorporated into MySQL mainstream.

One of the main differences over MySQL’s own version is that as well as printing the aggregated query (with number and string literals wildcarded), it also prints a real example of the query allowing a copy and paste to MySQL for execution with EXPLAIN.

Example output with query details redacted:

### 230 Queries 
### Total time: 4708.948293, Average time: 20.4736882304348
### Taking 0.093420 to 203.693466 seconds to complete
### Rows analyzed 0 - 141008
SET timestamp=XXX;
SELECT ... FROM ... AS A 
        INNER JOIN ... AS C ON C.item_id = A.item_id 
    WHERE XXX AND C.item_lang = 'XXX' AND ... 
    ORDER BY CATALOG.item_sort LIMIT XXX;

SET timestamp=1348032761;
SELECT ... FROM ... AS A 
        INNER JOIN ... AS C ON C.item_id = A.item_id 
    WHERE 1 AND C.item_lang = '1' AND ... 
    ORDER BY C.item_sort LIMIT 1;

 

Thunderbird 15 Released with Chat – But Shite Error Handling

I’ve just moved a couple of my chat and IRC accounts over to Thunderbird 15 to see how they look. After all, one communications application is better than three. Mozilla have a good instruction set here and they work well for standard chat accounts.

However, most corporations using in house XMPP servers with self signed certificates and these fail with no error messages whatsoever. It was by fluke I found this post: How to Make Thunderbird Chat Work with Most XMPP/Jabber Accounts. Thanks for doing the legwork Rod, this helped a lot.

 

Two Git Branching Models

In current projects, we tend to float between two branching models depending on the requirements of the customer / project and the planned deployment process.

git-flow

This was previously known as A successful Git branching model with an extremely detailed overview and instructions here. It has since spawned a project to help make using this model easier – git-flow.

This is a great branching model for a team of people who work towards creating planned versioned deployments (whether it be time based such as every second Tuesday, or milestone based).

In essence, this model uses two main branches:

  • origin/master – the main branch where the source code of HEAD always reflects aproduction-ready state.
  • origin/develop - where the source code of HEAD always reflects a state with the latest delivered development changes for the next release. Some would call this the “integration branch”. This is where any automatic nightly builds are built from.

Then developers create supporting branches as part of their development process:

  • feature branches – branched off origin/develop for active development of a new feature / enhancement;
  • hotfix branches – a branch off origin/master to apply a critical fix to production code;
  • release branches – short lived branches off origin/develop which are used for final testing and patched before being merged to origin/master.

The model works very well in practice and the above linked document is a excellent read on Git branching practices in general as well as git-flow in particular.

Github Flow

This is the model used at Github and discussed by Github developer Scott Chacon here.

This is suited for projects that deploy continuously rather than around the concept of releases and so it’s less constrained than git-flow. These are two very different models and Scott puts forth his arguments in favour of the Github model in his post linked above.

In essence, Github flow works as follows:

  • origin/master is always deployable. Always.
  • Similarly to git-flow, new features are done in their own branch – but off of origin/master in this case.
  • Now, when you believe your new feature is complete, a merge request is opened so someone else can review and check your work. This is the QA process.
  • Once someone else signs off, you merge back into origin/master.
  • This is now deployable and can and will be deployed at anytime.

 

Centralised Logging

I’m currently looking at some centralised logging tools and the following stand out:

  • Octopussy – one I cam across a long time ago but looking at some of the others below it may be past its sell by date?
  • Graylog2 – GSOH (in dating parlance) – “Manage your logs in the dark and have lasers going and make it look like you’re from space.“
  • logstash – “a tool for managing events and logs. You can use it to collect logs, parse them, and store them for later use (like, for searching). Speaking of searching, logstash comes with a web interface for searching and drilling into all of your logs.“
  • Kibana - You have logs. Billions of lines of data. You shipped, dated it, parsed it and stored it. Now what do you do with it? Now you make sense of it… Kibana is an alternative browser based interface for Logstash… that allows you to efficiently search, graph, analyze and otherwise make sense of a mountain of logs.

Kibana has a Bootstrap UI and is written in PHP which immediately bumps it up my list 😉

NOCtools – A Mixed Bag of Tools and Utilities for NOC Engineers

NOCtools is a mixed bag of tools and utilities that are useful for NOC engineers. This project originally started out as a way to highlight and utilise our OSS_SNMP library (a PHP SNMP library for people who hate SNMP).

It since grew into a way to graphically present information on network topology that is normally difficult and cumbersome to do by logging into individual devices. Such information includes a discovered L2 topology by CDP, using this to present rapid-PVST port roles and so forth.

From the company blog:

Today, we are introducing NOCtools which uses this library to provide a number of useful tools including:

  • CDP Neighbours: for a given CDP enabled device, display its CDP neighbours with information and also a graph showing connected ports.
  • CDP L2 Topology: graph the layer 2 network topology based on a recursive crawl of CDP neighbours.
  • RSTP Topology & Port Roles: similar to CDP L2 Topology above but this takes a specific VLAN and identifies and graphs Per-VLAN Spanning Tree port roles.
  • Per-VLAN RSTP Port Roles: a tool that will display the per-VLAN Rapid STP port roles for a given VLAN (or for all VLANs) on a device.
  • Inter-Device VLAN Comparison: a tool that will compare VLANs available (and their respective names) across selected devices allowing you to ensure consistency as well as perform simple security audits.

Follow the links about for screen shots and more details. We are releasing this under a GNU GPL license in the hope that the wider networking community will benefit from them.

A PHP SNMP Library for People Who HATE SNMP, MIBs and OIDs!

I hate SNMP! But I have to use it on a daily basis with my company, Open Solutions.

Don’t get me wrong, it’s an essential tool in the trade of network engineering but it’s also a serious PITA. Finding MIBs, OIBs, making them work, translating them, cross-vendor translations, etc, blah, blah. And then, when you do find what you need, you’ll have forgotten it months later when you need it again.

Anyway, while trying to create some automatic L2 topology graphing tools (via Cisco/Foundry Discovery Protocol for example) and also some per VLAN RSTP tools to show port states, I started writing this library. As I wrote, I realised it was actually very useful and present it here now in the hopes that the wider network engineering community will find it useful and also contribute back MIBs.

An example may best illustrate the library:

First, we need to instantiate an SNMP object with a hostname / IP address and a community string:

$ciscosw = new \OSS\SNMP( $ip, $community );

Assuming the above is a standard Cisco switch, let’s say we want to get an associate array of VLAN names indexed by the VLAN ids:

print_r( $ciscosw->useCisco_VTP()->vlanNames() );

This yields something like the following:

Array (
    [1] => default
    [2] => mgmt
    [100] => cust-widgets
    [1002] => fddi-default
    ...
)

It really is that easy.

See the GitHub project page: https://github.com/opensolutions/OSS_SNMP