innodb_flush_logs_on_trx_commit and Galera Cluster

We deploy Galera Cluster (in MariaDB) for some clients, and innodb_flush_logs_on_trx_commit is one of the settings we’ve been playing with. The options according to the manual: =0 don’t write or flush at commit, write and flush once per second =1 write and flush at trx commit =2 write log, but only flush once per second The flush […]

Galera pre-deployment check

One of the first things we do when preparing a client’s infrastructure for Galera deployment is see whether their schema is suitable. Avoiding quirks and edge cases, we can say that Galera simply requires all tables to be InnoDB and also have a PRIMARY KEY (obviously having a PK in InnoDB is important anyway, for […]

Understanding SHOW VARIABLES: DISABLED and NO values

When you use SHOW VARIABLES LIKE “have_%” to see whether a particular feature is enabled, you will note the value of NO for some, and DISABLED for others. These values are not intrinsically clear for the casual onlooker, and often cause confusion. Typically, this happens with SSL and InnoDB. So, here is a quick clarification! NO means […]

What a Hosting Provider did Today

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 […]

HDlatency – now with quick option

I’ve done a minor update to the hdlatency tool (get it from Launchpad), it now has a –quick option to have it only do its tests with 16KB blocks rather than a whole range of sizes. This is much quicker, and 16KB is the InnoDB page size so it’s the most relevant for MySQL/MariaDB deployments. […]

Fast paging in the real world

This blag was originally posted at http://cafuego.net/2010/05/26/fast-paging-real-world Some time ago I attended the “Optimisation by Design” course from Open Query¹. In it, Arjen teaches how writing better queries and schemas can make your database access much faster (and more reliable). One such way of optimising things is by adding appropriate query hints or flags. These hints […]

Unqualified COUNT(*) speed PBXT vs InnoDB

So this is about a SELECT COUNT(*) FROM tblname without a WHERE clause. MyISAM has an optimisation for that since it maintains a rowcount for each table. InnoDB and PBXT can’t do that (at least not easily) because of their multi-versioned nature… different transactions may see a different number of rows for the table table! […]

PBXT early impressions in production use

With Paul McCullagh’s PBXT storage engine getting integrated into MariaDB 5.1, it’s never been easier to it out. So we have, on a slave off one of our own production systems which gets lots of inserts from our Zabbix monitoring system. That’s possibly an ideal usage profile, since PBXT is a log based engine (simplistically […]

RAM flakier than expected

Ref: Google: Computer memory flakier than expected (CNET DeepTech, Stephen Shankland) Summary: According to tests at Google, it appears that today’s RAM modules have several thousand errors a year, which would be correctable if it weren’t for the fact that most of us aren’t using ECC RAM. Previous research, such as some data from a […]

Tool of the Day: rsnapshot

rsnapshot is a filesystem snapshot utility for making backups of local and remote systems, based on rsync. Rather than just doing a complete copy every time, it uses hardlinks to create incrementals (which are from a local perspective a full backup also). You can specify how long to keep old backups, and all the other […]