Arjen Lentz Blog

Would you prefer InnoDB to be the default storage engine?

Fri, 10 Oct 2008 16:13:17 -0700

I've created a new community poll: Would you prefer InnoDB to be the default storage engine?, as I'm just curious how the community currently feels about this.

Of course, anyone can put --default-storage-engine=InnoDB (and --sql_mode=NO_ENGINE_SUBSTITUTION) in their my.cnf to accomplish the same, but... as a friend of mine has been asking me for years, it would make MySQL "ACID compliant out of the box" (e.g., by default) with no silent ignoring of transactions or foreign key constraints.

You can always specify the desired engine explicitly with CREATE TABLE ... ENGINE=...

One could say that it's about usability and ease-of-use for new users who don't yet know much about MySQL or its configuration. Did you know that the config wizard on Windows actually already sets the default storage engine to InnoDB if you select that you might be using transactions? It's not explicitly noted that that's the consequence, but it is what happens.

So, please put in your vote, and possibly a comment here explaining your opinion? Great!

Creating more entropy...

Thu, 09 Oct 2008 18:12:37 -0700

Yeayea, the universe does that all on its own, all the time. But on a headless box, it's rather difficult to make tools like gpg happy (in the case of gpg, for --gen-key). While googling for a possible solution, I came across this little gem: rngd -r /dev/urandom (in the rng-tools package). Instant success, lots of entropy!

Book: The Manga Guida to Databases

Thu, 09 Oct 2008 18:12:37 -0700

Look, this could be hilarous, or dreadful. I haven't got the book, so I don't know which - but couldn't resist writing about it since someone pointed it out to me ;-)

The Manga Guide to Databases [ILLUSTRATED] (Paperback) by Mana Takahashi

Sebastian visits Sydney! Quality Assurance in PHP Projects

Mon, 06 Oct 2008 01:35:12 -0700

Back on popular demand, Sebastian Bergmann will teach his 3-day workshop Quality Assurance in PHP Projects in Australia again! It's scheduled 8-10 December in Sydney.

Many applications using MySQL are written in PHP... this three-day workshop will introduce/update PHP Developers to writing unit tests for the backend and system tests for the frontend of a web application as well as managing the quality from development to deployment and maintainance using tools such as PHPUnit, Selenium RC, phpUnderControl, PHP_CodeSniffer, and PHP_Depend. Sebastian is the author of PHPUnit, and long-time contributor to PHP itself.

Pricing is AUD 1695 + GST. Since this is a workshop, the number of seats will be limited to 10-12. Speedy registrants will receive a free PHP-related book of choice (from a Sebastian shortlist, he knows which are good).

Around the course dates Sebastian will also be available for consulting and/or in-house training in the region, simply contact Open Query to discuss.

Open Query goes Eventum

Sun, 05 Oct 2008 17:05:37 -0700

I didn't really evaluate other issue tracking tools this time. I know Eventum from my time at MySQL when it got acquired from Joao Prado Maia. It's currently maintained by Bryan Alsdorf (lordrashmi on Freenode IRC #eventum) who is very helpful. It works well, it has a pretty decent user interface, I knew it would do the job for OQ, and I got a local company (with Eventum experience) to do the necessary customisation and integration stuff.

So now any incoming general mail or form ends up there, which properly removes me (and my inbox) as a communication bottleneck for Open Query's effective growth. It also allows everybody (including myself) to work more efficiently, since tasks have a clear status and "owner" that is not dependent on email tagging, flagging or location. Threads and issue can't just get lost - not that they were, but it takes time, effort and brainpower to make sure and now all that energy is freed up at least for new issue ;-) and this works both when I'm online as well as over email as I reply to an issue through Eventum and it takes care of the status changes.

Yawn: why does EnterpriseDB keep talking about MySQL

Mon, 29 Sep 2008 20:08:30 -0700

Wasn't EnterpriseDB focusing on replacing Oracle in some cases, with specific Oracle-compatible features tacked onto its PostgreSQL base? This seems like a reasonable thing to do, good for them.

What I'm just amazed about is that EnterpriseDB in public communications and appearances keeps focusing on MySQL. What's the point? This started long ago with a movie on the EnterpriseDB site, featuring sharks - which I believe was aimed at the MySQL dolphin, but my memory may be faulty there.

Anyway, now there's an upcoming broadcast with some high-up person from EnterpriseDB, and the topic is "The great debate: PostgreSQL vs MySQL". No I will not provide links, as I don't think it's a worthwhile topic in this context, particularly if it's initiated by one vendor who has their own (albeit unclear) agenda. The info didn't even tell if anyone else was invited so it could be a very one-sided debate ;-)

It seems like such a waste of time. If Oracle is the focus of its business, why does it keep spending time on MySQL? Is EnterpriseDB just trying to catch a bit more Google-juice for its communications by including the word "MySQL" or something? Or is it really wanting to aim for the MySQL market?

Wisdom of the Day... Incy Wincy

Sun, 28 Sep 2008 20:08:32 -0700

You know, the spider! Many good ideas don't come from a specific field such as databases.... this gem comes from my daughter Phoebe, this morning as I was getting my bike ready to ride us to school. She said "don't give up papa, keep on going! Just like Incy Wincy Spider, he climbed up, got washed out by the rain, but didn't give up either!" Smart chook, that Phoebs.

By the way... the key to not being held hostage to investors' whims, is not to have any investors to begin with! Money doesn't generally solve problems, it actually creates more. If you have to make it all on your own, you get this wonderful focus. In a nutshell, never spend money you don't have.

MySQL 5.x starts even with fatal InnoDB errors

Wed, 24 Sep 2008 20:07:46 -0700

This bug (http://bugs.mysql.com/19027) was reported by Peter Zaitsev over two years ago... it may not be critical, but it's nasty and surely it can be fixed. This is the problem.

If InnoDB incurs a fatal error during initialisation, for instance changing logfile size, or from a typo in another parameter in the config, mysqld still starts but InnoDB will be disabled. According to Heikki, a storage engine cannot make mysqld not start at that stage, so the only option would be to chuck an assertion - frankly, I'd prefer that over the current misbehaviour!

So you can have a running server that kinda looks ok, but not have access to some or all of your tables.
In a word: ridiculous!

A SAN is a single point-of-failure, too

Mon, 22 Sep 2008 06:09:43 -0700

This is a controversial angle, and when put it like this I know many people vehemently disagree with it. Late last week, a "very high end dual site / fully redundant SAN system" at Internode failed, causing serious disruption in this ISPs various services. Internode are one of Australia's big "good guys" in ISP land and, apart from being managed by an insightful individual (Simon Hackett), they really do know their stuff technically.

I've called SANs "very expensive single points of failure". Sure, they have lots of redundancy built in, and in the case of Internode it was even physically distributed across multiple data centres. Still, something went wrong. This is because there's just an abundance of "interesting" ways to fail that are just about impossible to deal with automatically. So, SANs do have a very high uptime rating, but since big chunks of a business depend on it, any failures are quite spectacular.

Is there an alternative? Yes. I'm not sure who invented the new paradigm, but Brad Fitzpatrick at Danga/LiveJournal definitely pioneered some of it. The revolutionary thought is to accept partial failure and build your infrastructure accordingly. Eek! If you have more choices than merely "100% up" or "all down", your architecture might look very different. At LiveJournal, some accounts may not be available for a bit, but the rest is. Think about this, a world of opportunity opens up! Apart from disaster management, this even makes maintenance much easier.

In the online world, 100% uptime for everything is just downright impossible - so: accept it, change focus, build accordingly. And in that new world, SANs simply don't fit. They can certainly be useful in some other situations, but not nearly as often as people reckon. It's just one of those tools that people pick by default, just like people often pick Oracle for a database, even when a flat file would do better for the purpose (see, not plugging MySQL for that, either ;-)

Australian govt PC grants pay for license fees

Fri, 19 Sep 2008 05:08:26 -0700

As noted in this article: PC grants pay for licence fees, it appears that in the current setup, the Rudd government's education revolution ends up less than ideal, with a heap of the money flowing to mainly Microsoft.

Clarifying the story... state education authorities and catholic education generally have an arrangement with Microsoft which gives them very cheap access to for instance Windows and Office licenses. But all is relative... under the agreement, any PC in the school's realm gets counted, even if it doesn't run any of the software. So if a school acquires new PCs, even if they were to all run Linux, OpenOffice.org and the whole shebang, Microsoft still gets a cut. If, as a consequence, the authorities have to acquire lower grade hardware to get to their quota of machines, that's not-so-shiny, eh? And that's exactly what appears to be happening.

I don't actually blame Microsoft for making such a deal, of course it'd take what it can get, and offering a simpler auditing system for its licensing kinda makes sense for the other side as well. It just ends up borked. It's up to the education authorities to renegotiate something. The federal government says it's not their gig. True, strictly speaking, but really....

SQL database injection on the SSN/government level...

Wed, 17 Sep 2008 16:27:55 -0700

In the realm of the daily WTF, this SQL doozy popped up earlier in the year: Oklahoma Leaks Tens of Thousands of Social Security Numbers, Other Sensitive Data

SQL queries were part of the URLs, so anybody could see which tables/cols were present, and modify at will to extract lots of privileged data including social security numbers. I particularly cried around the bit where the developers, after being informed of the problem, merely changed the social security number column name to start with caps, while leaving the whole "SQL in URL" thing in place. They only actually took the thing off-line "for routine maintenance" after it was then proven that the developers' own personal information was also retrievable. Apparently it had to have such a personal connection to "hit home".

Converting all tables in a db to another engine

Wed, 17 Sep 2008 16:27:55 -0700

Happens a lot these days, like when people (finally) discover the virtues of InnoDB. Of course there's more to it than just the conversion, for instance some server tuning will be required also.

Anyway, there are a few methods:

Most MySQL installations will have the mysql_convert_table_format script (Perl) included. The script does not contain an option to tweak server parameters though. For instance, when converting to MyISAM (as an intermediate step for moving to innodb_file_per_table), index building will be much faster if you have a large myisam_sort_buffer_size setting. So you'd have to either tweak the script or make that change globally (SET GLOBAL myisam_sort_buffer_size=1024*1024*1024 for 1G). I prefer to have such changes local to a setting as they can be dangerous, but in this case it should be ok since this particular buffer is only used for alter table and repairs.

You could write a stored procedure to use information_schema and dynamic queries. But unless you're stuck with the way you can access the server, the above method should suffice!

What I actually often do when dealing with such conversions, is extract the table names and prepare a script. Then I look at the table structures and the slow query log, and tweak the indexes. Removing duplicates/useless ones, adding some composite/covering indexes where appropriate, etc. After all, an alter table requires the entire table to be copied, so adding/removing/sanitising indexes at the same time as converting the engine type saves hassle.

Note: contrary to what I wrote earlier, you can't do the bulk change with phpMyAdmin; I thought you could but Marc Delisle from the project corrected me (see comments).

Kristy Bennett's courses on marketing (including yourself)

Fri, 05 Sep 2008 17:05:24 -0700

Kristy Bennett is well known in OSS circles in Australia as a creative soul, as well as a serial (and concurrent) entrepreneur. Among many other things, she runs MIB Business Solutions providing a range of management and marketing services. This is usually done in-house, but every once in a while she offers public training days. The next upcoming ones are in Melbourne, in the evening of September 30th, and October 1st. A rare opportunity!

The evening session is Selling Yourself: Presenting with Confidence.

Whether you are looking for a new client, business partner, interviewing potential staff or seeking a new employer the art of 'selling yourself', either as an individual or as a representation of your business, product or service, is critical to finding what you are seeking. Moving away from typical topics of marketing channels, sales and branding, what to wear and even writing and reviewing applications this session is solely focused on you and how your sell yourself into a role.

With a very practical, hands on approach you will be working through finding the words to say, the manner to say them and they physical aspects of meeting with your potential 'purchaser'. This workshop is geared for people with an technology and like professional background and will look specific at presentation manner.

The next day has two independent parts (that can be booked together also):
Quick Start Marketing: Starting your Marketing from Scratch and Leaving with a Strategic Plan
and
Getting Linked In: How to find your Marketing Match

Many people are weary of business/marketing training, but with the right person teaching it's very valuable stuff. Take a peek. It's pretty cheap already, and there's an earlybird discount for bookings before September 12th, and you also get some additional goodies including a good book and a bottle of organic wine ;-)

Note: Open Query is providing the logistics for Kristy, similar to us organising Sebastian Bergmann teaching his "Quality Assurance in PHP Projects" workshop. Is Open Query going in a different direction with these new things? I don't think so, in fact I reckon it makes perfect sense: OSS has a strong but generally neglected business and marketing component.
For code, there's the great OSS community. For your business, there's OSIA but it's also good to get some clueful professional insights through training. I don't partner with just anybody, in fact I'm very picky. Training should not just be by a competent trainer with reasonable materials, it should be done by an expert in the field. In my opinion, the trainer is what makes training valuable. If you ask a tangential question during a course, you'll want to expect a competent answer rather than "euh", right? That's what I'm talking about.

Arjen in Auckland

Sat, 30 Aug 2008 20:51:42 -0700

Flying to Auckland this arvo (that's afternoon in Aussie ;-) for a week and a bit. If you would like to catch up, have dinner, talk MySQL, Drizzle, Open Source in general, smart business innovation, or any other interesting stuff, drop me a line or SMS (not call please I might be teaching) +61-438 210 269. I'll have transport.

Melbourne course days (15,16,17 Sep) for MySQL DBAs

Fri, 29 Aug 2008 09:51:35 -0700

Are you in Melbourne/VIC, and are you an experienced DBA needing to tune InnoDB? Or do you just maintain some MySQL instances on the side and need to know more about proper installation, security and backup/recovery methods?

Well... the upcoming Open Query course days in Melbourne still have seats available. The topics:

Monday 15 September: MySQL Installation, Security and User Management (AUD 475 + GST)
MySQL Server is easily set up and ready to go within mere minutes, however this does not make it suitable for a production environment in terms of performance, security, and monitoring. Additional configuration of MySQL Server as well as its operating environment is necessary.
Tuesday 16 September: MySQL Backup and Recovery (AUD 475 + GST)
We explore the different methods and tools (some of which you may not be familiar with), when and where to use each, discuss common problems and pitfalls, and provide a good basis for developing processes for keeping your data safe in your own environment.
Wednesday 17 September: MySQL InnoDB Performance Tuning (AUD 575 + GST)
Delving into the vast realm of the InnoDB storage engine, its architecture, diagnostic tools, and performance tuning. InnoDB is the most widely used transactional storage engine in MySQL. In many deployments it is used for many if not all tables. While the default settings make InnoDB functionally usable, it is by no means optimised. The server's actual utilisation by an application, as well as the physical architecture (such as CPUs/cores and disk I/O) come into play. You may want to change some paths, look at the many other tuning options, and see how queries can be adjusted to take advantage of some of InnoDB's features.

Secure your seat today!

You can choose individual days. Naturally, the first two days are a must for anybody responsible for administering a MySQL server, while the last day is definitely just for the more advanced. For an overview of all current MySQL-related course topics, see http://openquery.com.au/training/mysql. Open Query also does customer (in-house) courses, and on-site and remote consulting. If you need a helping hand, let us know!

Towards Open Query's first anniversary

Fri, 29 Aug 2008 06:51:35 -0700

Wow indeed, it's been almost a year already! How time flies... I left MySQL in July 2007, and had two months much needed holiday.

But one needs to live, and opportunity in Australia was clearly there, so I started Open Query in September and went for part familiar, part less familiar territory. The less familiar was consulting, in the sense that there had been no MySQL consulting in Australia by either me or MySQL directly, ever. I had done consulting before, in my old software development company in The Netherlands, as well as for others (I built a training & consulting firm for someone else).

The more familiar (in the MySQL realm) was of course training, as I'd been teaching courses here for years. The way that came about:

In 2001, MySQL AB hired me as a tech writer for the docs, however my boss Kaj had just come in from Polycon which had developed MySQL training materials. Since I also had training experience, I suggested to also try scheduling courses in Australia. That's how it developed, as essentially Australia has never been a priority for MySQL; Marten has always (and I think rightly) maintained that you can't go into a new territory half-heartedly, you need to put enough local resources into it to make it effective and successful. So MySQL did stuff in AU because I happened to be here; as tech writer I could've come from anywhere, and so it's pretty much coincidence ;-)

I tell this story because I've found that I'm actually competing against my former self, when you look up mysql training related stuff (in Australia) on Google, there are many articles, mailing list posts, and even press quotes referencing me @ MySQL, with of course all the appropriate links to the MySQL site. So, I reckon I did a good job back then - I hope I'm even more effective now and thus able to outcompete my former now static self - however the mysql company site does of course have a lot of Google juice, and the net does not forget. It's interesting (at least to me ;-)

Anyway, back to the present... I'm currently undecided on which "arm" of Open Query is more successful. They're both expanding nicely and in ways I couldn't have anticipated when I started.
I run on a simple business plan, try to remain agile, and OQ is self-funding (and profitable) and with low overheads so any adjustment in the direction the company goes in (as is inevitable) is actually fairly painless. Put differently: any mistakes I make aren't too costly ;-)

There's also another reason why I intend to keep both training and consulting around: I think there's an advantage to a) not being a full-time trainer and b) the additional real world exposure you get with consulting. It's simply other other side of the same coin!

Think about professors at universities often lose touch with the big world out there once they've been only lecturing for years. That's just a pity and doesn't serve the students really, does it? Do they learn about the real world?
A teacher hears and sees plenty of stuff in the process of teaching a good and interactive course, but having to solve immediate problems in consulting is something else. It requires you to step directly in the same position your students would be in, and applying the exact techniques that you're teaching. If the approach doesn't work, you find out first hand. I think that's invaluable.

On China in the software market, exploring with a twist!

Fri, 29 Aug 2008 04:51:37 -0700

Please allow me to take your mind on a little adventure! I was just reading A global Chinese software company in the next 20 years? about how China goes about acquiring technical know-how. It's not quite like what Japan did after WWII, it uses unique aspects of China, such as its size as well as its form of government.

Savio then makes a comment about "[...] piracy rates sky-high and IP legislation and enforcement in its infancy, [...]" which I think misses a very important point: China's government form is of course communist. One of the fundamentals of communism is that there's no individual ownership of things, most things are *communal* (hence the name "communism" ;-). Wikipedia writes "Communism is a socio-economic structure that promotes the establishment of an egalitarian, classless, stateless society based on common ownership of the means of production and property in general."

Therefore, IP -> intellectual *property* kinda doesn't fit into China's picture of the world. So what the US and the media generally completely fail to appreciate (or convey) when talking about this topic, is that it's not at all about "not enforcing IP". Very simply, without IP, there's nothing to enforce!

Open Source is sometimes regarded as IP communism, but from the above you can easily see that it is not - OSS has a strong foundation in copyright law: the GPL, for instance, builds directly onto it and would not work without.

Then there's the debate over whether communism actually works or not; most implementations tend not to, but some can be quite successful in certain ways (for different reasons). Cuba for instance has done some very interesting things. But China is a special case. It's been creating its own special mix producing economic wealth as well as power, and I reckon that the "does it work" debate in this case is completely moot. China is such a force that they can pretty much do as they please, and the rest of the world will just put up with it.

Likewise, human rights suck in China, and media freedom is an oxymoron - but they've just held very successful Olympic Games and the world has gone along with all the nasties it knows exist.

Right now, the world needs China more than China needs the rest of the world. The other countries have created a huge dependency by outsourcing manufacturing over many years. Shopping in Australia, most things are manufactured in China, even if the company is Australian. This is just a fact right now, whether you like it or not.

So here's the thing... given the above-sketched picture of China and the world, does China have any reason to change anything in the way it does things? Why would it change its approach to IP?

China uses lots of Open Source software, and has created its own Linux distro and versions of other OSS packages. So it's getting the benefits from that perspective already.
If you're building say a website, the key factor is speed and time to market, not IP. In fact, many in "the West" get very distracted by IP (patents generally) and waste a lot of time and money on it, to the detriment of the possible economic advantage in their marketplace. So Chinese might have an advantage already here, they don't have that "fussing about IP" background and would thus not be hindered by such distracting thoughts ;-)
Building other Open Source software... well, within China, does it really matter? Outside China there's copyright laws, so there the stuff can be made "safe" anyway with authors' rights fully protected.

The world seems to work just fine, from China's perspective? Perhaps this is actually similar to tax(free) havens. They're regarded as a nuisance when viewed from the outside, but the tax havens themselves and people using them are quite happy with the arrangement. See China as an IP(free) haven! Perhaps, rather than lamenting the lack of IP protection, we can look at how we can use this haven in good and useful ways?

Ticket paid, Arjen going to OpenSQL Camp 2008

Thu, 28 Aug 2008 20:51:23 -0700

Alrighty, got flights in sardine-class with nasty airlines organised & paid, so I'm going to OpenSQL Camp 2008! See you all there?

Open Query partners with The Pythian Group

Thu, 28 Aug 2008 17:54:16 -0700

Open Query has partnered with The Pythian Group, founded in Canada by Paul Vallee and active around the world providing fractional database infrastructure teams on a linear cost-to-effort basis.

Open Query focuses on MySQL and related training and consulting work, whereas The Pythian Group has its remote DBA offering with around the clock (and around the globe) coverage. A perfect match!

I'm very pleased with this partnership, so a grand thanks to Paul and Alex, but also Sheeri, Niklas, Gerry, Augusto, Keith, Julien, Singer, Danil, Santinesh, Paul, and everybody else (they're everywhere!) I've met some of you already, and hope to meet more and work with you in the time to come. A great gang with a good offering and an friendly business model.

Pythian's model is unique in that it allows larger customers to outsource some or all of the management of entire environments while still allowing smaller customers to tap the subject matter expertise and 24x7 access to resources normally only available in very large enterprise infrastructure teams.

Like Open Query's consulting, Pythian services are not in any way limited by a scope of work. As a result, they can easily and seamlessly be blended in with a customer's existing team to substantially increase its overall horsepower, flexibility and capabilities, while maintaining all the advantages and continuity of the existing personnel. For more information, contact us for an introduction.

Scribus on OS X anyone? PPC (Tiger) and Intel (Leopard)

Thu, 28 Aug 2008 17:54:16 -0700

Help.... the info I find online online is a tad messy and sometimes outdated. Has anyone created installable Scribus packages for OS X yet?

I'd prefer to not go through the whole Fink saga, and MacPorts doesn't appear to want to fly for building qt4. Each approach seems to have some issues, but perhaps that observation too is based on now outdated info. Please do tell me!

Right now I need a PPC Tiger build for an old PowerBook, and a Intel Leopard for myself would be most useful.
Thanks!

A few seats left in Canberra (22,23,24 Sep) for MySQL DBAs

Thu, 28 Aug 2008 17:54:16 -0700

Are you in Canberra/ACT, and are you an experienced DBA needing to tune InnoDB? Or do you just maintain some MySQL instances on the side and need to know more about proper installation, security and backup/recovery methods?

Well... the upcoming Open Query course days in Canberra still have a few seats left. No, it's not "nearly full" but it's not empty either. And yes, I know that Sun/MySQL recently cancelled a DBA training week in Canberra at the last minute (they also scheduled it close to the last minute to begin with ;-) however these Open Query days are definitely going ahead. The topics:

Monday 22 September: MySQL Installation, Security and User Management (AUD 475 + GST)
MySQL Server is easily set up and ready to go within mere minutes, however this does not make it suitable for a production environment in terms of performance, security, and monitoring. Additional configuration of MySQL Server as well as its operating environment is necessary.
Tuesday 23 September: MySQL Backup and Recovery (AUD 475 + GST)
We explore the different methods and tools (some of which you may not be familiar with), when and where to use each, discuss common problems and pitfalls, and provide a good basis for developing processes for keeping your data safe in your own environment.
Wednesday 24 September: MySQL InnoDB Performance Tuning (AUD 575 + GST)
Delving into the vast realm of the InnoDB storage engine, its architecture, diagnostic tools, and performance tuning. InnoDB is the most widely used transactional storage engine in MySQL. In many deployments it is used for many if not all tables. While the default settings make InnoDB functionally usable, it is by no means optimised. The server's actual utilisation by an application, as well as the physical architecture (such as CPUs/cores and disk I/O) come into play. You may want to change some paths, look at the many other tuning options, and see how queries can be adjusted to take advantage of some of InnoDB's features.