Open Query blog

DrupalDownUnder 2012 will be held in Melbourne Australia 13-15 January. A great event, I’ve been to several of its predecessors. People there don’t care an awful lot for databases, but they do realise that sometimes it’s important to either learn more about it or talk to someone specialised in that field. And when discussing general infrastructure, resilience is quite relevant. Clients want a site to remain up, but keep costs low.

I will teach pre-conference training sessions on the Friday at DDU:

• MySQL Query Design ($220 half-day)
• MySQL Server Tuning ($220 half-day)

The material is made specific to Drupal developers and users. The query design skills, for instance, will help you with module development and designing Drupal Views. The two half-days can also be booked as a MySQL Training Pack for $395.

On Saturday afternoon in the main conference, I have a session Scaling out your Drupal and Database Infrastructure, Affordably covering the topics of resilience, ease of maintenance, and scaling.

I’m honoured to have been selected to do these sessions, I know there were plenty of submissions from excellent speakers. As with all Drupal conferences, attendees also vote on which submissions they would like to see.

After DDU I’m travelling on to Ballarat for LinuxConfAU 2012, where I’m not speaking in the main program this year, but will have sessions in the “High Availability and Storage” and “Business of Open Source” miniconfs. I’ll do another post on the former – the latter is not related to Open Query.

This recent session at OSDC 2011 Canberra is based on part of an Open Query training day, and (due to time constraints) without much of the usual interactivity, exercises and further MySQL specific detail. People liked it anyway, which is nice! The info as presented is not MySQL specific, it provides general insight in how databases implement concurrency and what trade-offs they make.

See http://2011.osdc.com.au/SQLL for the talk abstract.

http://thedailywtf.com/Articles/The-AntiSQL-Coalition-.aspx

“There is a small coalition of developers at the office who are vehemently anti-database,” writes Bob, “naturally, this faction also doesn’t value ‘experience’ — mostly, because they have none. At least, not outside their university studies. They’ll often liken a database server to a file system, and suggest that it’s just a convenient way to store blobs of data — but everything else is inelegant bloat.”

I found Dennis the Menace, he now has a job as system administrator for a hosting company. Scenario: client has a problem with a server becoming unavailable (cause unknown) and has it restarted. MySQL had some page corruption in the InnoDB tablespace. The hosting provider, being really helpful, goes in as root and first deletes ib_logfile* then ib* in /var/lib/mysql. He later says “I am sorry if I deleted it. I thought I deleted the log only. Sorry again.”  Now this may appear nice, but people who know what they’re doing with MySQL will realise that deleting the iblogfiles actually destroys data also. MySQL of course screams loudly that while it has FRM files it can’t find the tables. No kidding! Then, while he’s been told to not touch anything any more, and I’m trying to see if I can recover the deleted files on ext3 filesystem (yes there are tools for that), he goes in again and puts an ibdata1 file back. No, not the logfiles – but he had those somewhere else too. The files get restored and turn out to be two months old (no info on how they were made in the first place but that’s minor detail in this grand scheme). All the extra write activity on the partition would’ve also made potential deleted file recovery more difficult or impossible. This story will still get a “happy” ending, using a recent mysqldump to load a new server at a different hosting provider. Really – some helpfulness is not what you want. Secondary lesson: pick your hosting provider with care. Feel free to ask us for recommendations as we know some excellent providers and have encountered plenty of poor ones.

In 2007, Etsy made a big bet on homegrown middleware to help with the site’s scalability. A half-year after it was taken live, the company decided to abandon it. As a senior software engineer at Etsy put it, “if you’re doing something ‘clever,” you’re probably doing it wrong.” Read the full article at Arstechnica.com I want to focus on the important lessons from this article, about middleware and using stored procedures in this fashion for a public web application, creating unscalable design complexity (smart and “proper” according to the old enterprise design teachings…) – causing infrastructure, development and maintenance hassles. In the process they did replace PostgreSQL with MySQL but that’s not the critical change that made all the difference. PostgreSQL is a fine database system also.

Some so-called “Green” harddisks don’t like being in a RAID array. These are primarily SATA drives, and they gain their green credentials by being able reduce their RPM when not in use, as well as other aggressive power management trickery. That’s all cool and in a way desirable – we want our hardware to use less power whenever possible! – but the time it takes some drives to “wake up” again is longer than a RAID setup is willing to tolerate. First of all, you may wonder why I bother with SATA disks at all for RAID. I’ve written about this before, but they simply deliver plenty for much less money. Higher RPM doesn’t necessarily help you for a db-related (random access) workload, and for tasks like backups which do have a lot of speed may not be a primary concern. SATA disks have a shorter command queue than SAS, so that means they might need to seek more – however a smart RAID controller would already arrange its I/O in such a way as to optimise that. The particular application where I tripped over Green disks was a backup array using software RAID10. Yep, a cheap setup – the objective is to have lots of diskspace with resilience, and access speed is not a requirement. Not all Green HDs are the same. Western Digital ones allow their settings to be changed, although that does need a DOS tool (just a bit of a pest using a USB stick with FreeDOS and the WD tool, but it’s doable), whereas Seagate has decided to restrict their Green models such that they don’t accept any APM commands and can’t change their configuration. I’ve now replaced Seagates with (non-Green) Hitachi drives, and I’m told that Samsung disks are also ok. So this is something to keep in mind when looking at SATA RAID arrays. I also think it might be a topic that the Linux software RAID code could address – if it were “Green HD aware” it could a) make sure that they don’t go to a state that is unacceptable, and b) be tolerant with their response time – this could be configurable. Obviously, some applications of RAID have higher demands than others, not all are the same.

My colleagues and I are looking for extra talent – is that you? What we do:help clients prevent problems (rather than being the fire department), we work on a subscription basis although we also do some ad-hoc consulting, and training. Apart from MySQL/MariaDB query and DBA work, we do quite a bit of system administration. Mainly Red Hat and Debian based distros, and expect to see replication and the MySQL-MMM multi-master system. You’d work from home, whereever it might be, so you will need to be self-motivating (but we do keep in touch online). What we’re not: a full-time employer. With us, you make a life rather than a living. Everybody is contracted part-time. You can make enough to live comfortably, but that has nothing to do with hours. If you’re stressed about not filling all hours in your week with work-work-work, we’re not the company for you… there’s more to life than work, and we feel that’s really important. Haven’t scared you off yet? Groovy. Take a peek at our jobs page for additional detail and contact info. Hope to hear from you!

This week, ten years ago, I was in London for MySQL AB‘s first “train the trainer” course, also meeting (for the first time) my first boss at MySQL Kaj. I’d been hired mid August as employee#25, also doing training but actually primarily as tech-writer for the MySQL documentation (taking over from Jeremy Cole, and essentially I was the documentation team for quite some time . So from this you can deduce that yes, I was hired without meeting either Kaj or anyone in-person! I don’t think we even had a phone call, only email. Oh the days The training week itself was of course disrupted quite a bit by the events in New York. We had Jeremy who had come on a UA flight from the US, and others from all over the place… it also taught some students a lesson about browsing the net while in a training course, it can end up very distracting. The oddest event I remember about that particular trip happened upon departure from Heathrow: someone with a clipboard went round the long queues asking whether anybody was carrying eyebrow tweezers. No other items/questions, just that. I stayed with MySQL for about 6 years, until with a brief break, I started my own company Open Query in 2007 (about half a year before the Sun acquisition). So this September marks the 4-year anniversary of that event already. I spotted an old business card earlier, reminding me that early on we did not just MySQL consulting and training, but also (OSS) business advice – that’s now essentially spun off to Upstarta. The MySQL side of my business has changed quite significantly as well, going from the usual reactive consulting to proactive subscriptions, in part based on Pythian‘s successful model. A key difference has been that we don’t do emergencies. This disruptive shift happened somewhat by luck, after a talk at Linux Users of Victoria in April 2009. Ben Dechrai made a video recording of this interactive “Relax! A Failure is not an Emergency…” try-out. It also mentioned the BlueHackers initiative/stickers. While some people including competitors regard our “no emergencies” approach as nuts , it has worked out very well and apart from making customers happy it’s created the sane lifestyle I was looking for (so I could spend more time with my daughter), and enabled contracting others as well. We’re still growing organically, having adapted our internal tools and processes for the proactive service approach along the way – obviously, it’s now more about project management than handling incident tickets. Like my time at MySQL AB, my journey since then has so far proven interesting, educational, and mostly enjoyable. Later in the year I aim to once again buy a house with a modest garden. And it’s the independence that’ll have made that -and my other explorations- possible. Who knows what lies ahead – most fun when you create your own future!

http://gigaom.com/cloud/lexisnexis-open-sources-code-for-hadoop-alternative/

HPCC Systems has released the open source code of its data-processing software that it’s positioning as a better version of Hadoop. The code is available on Github, and it marks the commencement of HPCC Systems’ quest to build a community of developers underneath Hadoop’s expansive shadow.

WikiLeaks Cable Offers New Insights into Oracle-Sun Deal (PC World)

Nothing too new or shocking in there, but the cable does offer some interesting insights.

http://www.moserware.com/2009/09/stick-figure-guide-to-advanced.html

Jeff Moser on software development