Mark's stream of verbiage

Expecting the unexpected

2011-05-23T20:19:00.004+01:00

Few developers consider, when trying to build robust platforms, all the possible modes of failure. Indeed, it is difficult to consider them all, let alone plan for them, or design tests which exercise particular symptoms.

In this post, I discuss some of the types of failure we can see in real systems.

Complete server failure

Most developers DO consider this. In a "Complete server failure", what generally happens is:

* The server stops processing new requests, completely.
* The server's OS no longer responds to any network request at all (e.g. "ping")
* Processing does not continue within the server
* The contents of memory are immediately and irretrievably lost.

Typically, the server recovers, and when it does so, it is rebooted and restored to full health. All writes which were acknowledged before its failure have been persisted.

This is very easy to simulate (just hit the "power off" button in your VM hypervisor) and fairly easy to plan for; most robust systems consider this kind of scenario.

Network failure

There are many different kinds of network failure, but consider the simplest, most severe network failure:

* One or more machines in the infrastructure lose network connectivity
* None of them can talk to anything at all, including each other
* Local processing on these servers continues as normal
* No machines need to be rebooted to fix the fault, when it is repaired everything is back to normal.

This is a symptom of, perhaps a switch failure, where a "complete" failure occurs.

I won't discuss network failures at all, but there are many different kinds. My experience suggests that the most common is partial or complete loss of internet connectivity from one location (datacentre).

IO subsystem failures

* One or more discs / volumes suddenly become unavailable
* The OS does not reboot; processes do not stop

These are the kinds of failures which developers typically don't consider and are a lot more difficult to simulate. What might happen is, the power fails for a disc enclosure unit, but not its host server, in this case the OS and its boot discs remain available, but data discs are not. In these cases, failover might not be triggered or might behave incorrectly.

Heavy load or unexpected poor performance

* A single server unexpectedly starts performing very badly
* In the extreme, this means without sufficient capacity to do useful work
* But it's not failed; no subsystem is individually totally unavailable
* Sometimes the effect is severe enough to prevent operations engineers logging in to diagnose / fix the fault

These kinds of faults usually cause a larger problem, because failover systems aren't triggered, or cannot take over in a timely fashion. Common causes can be

* Rogue process consuming lots of resources
* Denial-of-service attack
* Bad application design causing legitimate requests to suddenly spike in resource usage.
* Operational error (well, anything can be caused by operational error :) )

"Zombie" systems or, back from the dead

* A system fails in a catastrophic way and can't be remotely recovered
* Operations engineers assume that it's going to be completely dead until physically replaced (They are some distance away and don't raise a "remote hands" request, or are unable to recover it by doing so)
* Another system is provisioned in its place, and takes over its IP address, role etc
* Then one day... the "Zombie" system unexpectedly comes back from the dead to haunt its successsor ... Brraaainss....

Of course this could be months later, after many software updates (possibly security updates). The "zombie" system is running an old build and will not carry out correct processing if it is given work to do.

Conclusion

These are just a few of the annoying types of failures which happen to real systems in production. Expect the unexpected (as if that's not a contradiction!).

Happy hacking!

HTML 2d Canvas upscaling - really inefficient

2011-02-20T08:33:00.003Z

I started writing some test programs with the HTML canvas element. This is great, as you can actually write games in Javascript - efficiently - in principle.

My previous attempts have all used the DOM API which is not very convenient, and not very efficient.

I had assumed the canvas 2d drawing context was basically a software-renderer - it's not extremely efficient, but provided the canvas doesn't have too many pixels, you can still do a lot of work per frame on a modern machine.

Which is fine.

Suppose you have a canvas which is 640x320 pixels, you can then get it upscaled into whatever resolution is in the browser window, making the game appear the same size for everyone. Great.

Except, the upscaling in web browsers sucks performance. I tried Firefox 3.6 and Chrome 9. Both of them use loads of CPU scaling the canvas on to the screen.

If we use a canvas element without any scaling (no CSS width etc) then all is fine.

Scale it up to a large window, Boom! now it's slow as a pregnant snail. Bummer.

See Example here

MySQL, what are you smoking?

2010-05-26T17:59:00.005+01:00

There are a lot of weird things which MySQL does to handle its mix of transactional and non-transactional behaviour, but this one was new to me :)

create table t1 (ID INT NOT NULL PRIMARY KEY, V INT NOT NULL);

Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 (ID,V) VALUES (2,NULL);
ERROR 1048 (23000): Column 'V' cannot be null

mysql> insert into t1 (ID,V) VALUES (3,1),(4,1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

insert into t1 (ID,V) VALUES (5,1),(6,NULL);
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+---------------------------+
| Level   | Code | Message                   |
+---------+------+---------------------------+
| Warning | 1048 | Column 'V' cannot be null |
+---------+------+---------------------------+
1 row in set (0.00 sec)

select * from t1;

+----+---+
| ID | V |
+----+---+
|  3 | 1 |
|  4 | 1 |
|  5 | 1 |
|  6 | 0 |
+----+---+
4 rows in set (0.00 sec)

What's going on?
MySQL does not consider (some) errors to be errors, if they happen on the second or subsequent row of a multi-row insert.

On the other hand, if it happens on the first row, it's an error.

Why?

Because non-transactional engines can't rollback to a savepoint. This means that if it's inserted one or more rows already, to generate an error would leave some stuff in the database.

No, really why?

I don't know. This is not consistent with, for example, a unique index violation, which makes it stop half way through a multi-row insert anyway, and non-transactional engines can't rollback.

So if you insert a duplicate, THAT still generates an error on the second a subsequent row. It's not even consistent!

mysql> insert into t1 (ID,V) VALUES (10,1),(10,2);
ERROR 1062 (23000): Duplicate entry '10' for key 'PRIMARY'
mysql> select * from t1;
+----+---+
| ID | V |
+----+---+
|  3 | 1 |
|  4 | 1 |
|  5 | 1 |
|  6 | 0 |
| 10 | 1 |
+----+---+
5 rows in set (0.00 sec)

Of course if we use a transactional engine, it looks better:


mysql> ALTER TABLE t1 ENGINE=InnoDB;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into t1 (ID,V) VALUES (20,1),(20,2);
ERROR 1062 (23000): Duplicate entry '20' for key 'PRIMARY'
mysql> select * from t1 WHERE ID=20;
Empty set (0.00 sec)

But that won't change the behaviour of a multi-row insert with NULLs in invalid places.

This kind of stuff is nonsense and we need it to GO AWAY NOW.

How?

SET SQL_MODE='STRICT_ALL_TABLES'

And now every error is really an error. Yay! Why can't this be the default? (I know the answer; this is a rhetorical question)

Streaming data from MySQL

2010-04-25T11:49:00.002+01:00

How do we stream data from MySQL? Traditionally, developers have thought that they can simply use their API's cursor-equivalent object (e.g. resultset) and move through the results.

Unfortunately, this does not do streaming in most cases. Normally most client libraries (which call mysql_store_result) will read the entire result into memory, and you're just going through an already-in-memory data set. This will fail if it doesn't fit in memory.

Enter mysql_use_result - if your library can use this instead of mysql_store_result, you can then skip through the records without needing to keep them all in ram at once.

However, there is some bad news - while going through a result set in mysql_use_result, you can't do any OTHER queries on the connection. An attempt to do so generally violates MySQL's protocol and fails. So if you need to do other queries while you're processing the large data set, you should open another connection.

We've found that in Perl DBI, mysql_use_result performs significantly worse on small data sets (i.e. ones which fit in ram, say 100M - 1G) than mysql_store_result (which is used by default). It seems that a lot more requests need to be done into the libraries from Perl.

Enter HANDLER. This is basically an SQL-level object which behaves a bit like a server-side cursor would in some other databases. You can have multiple handlers open on the same connection, and can do other operations while a handler is open. The disadvantage: it cannot be used for arbitrary queries. Only table and index scans of a single table can be done - no joins or sorting. And the client needs to know a bit about the table to choose the best method - the optimiser doesn't get involved.

However, in the cases where a lot of data are likely to be returned, we can either not require joins, or do joins on the client-side - if there are small "dimension tables" that can be held in-memory on the client.

None of this is great of course, it would be better if mysql actually supported server-side cursors. Unfortunately it doesn't (except in stored procedures).

What is Cassandra good for

2009-11-29T13:31:00.004Z

The Cassandra database has been getting quite a lot of publicity recently. I think this is a good thing in general, but it seems that some people are considering using it for unsuitable purposes.

Cassandra is a cluster database which uses multiple nodes to provide

Read-scaling
Write-scaling
High availability

Unless you need at least TWO of those things, you should probably not bother.

Good reasons to use Cassandra:
High availability

Cassandra tolerates the failure of some nodes and will continue to read data and take writes despite some nodes being offline or unreachable - the exact behaviour depends on its settings and what consistency level of read/write is requested.

Write scaling

Cassandra allows you to scale writes by just adding more nodes; writes are split between nodes, hence you can generally get better and better write performance by JUST adding more nodes (NB: it doesn't necessarily do load balancing, so you might not in all cases, but this is what it aspires to)

Less good reasons to use Cassandra
Read scaling
Cassandra gives you read-scaling in the same way as write-scaling. This is a good thing, but can also be achieved relatively easily* with a conventional database by adding more and more read-only slaves / replicas, or using a cache (if you tend to get a lot of similar requests). Many big MySQL users do both.

Also Cassandra does NOT create more than the configured number of replicas of any given piece of data, regardless of the amount of traffic on that part, so you could end up having a small number of servers hammered and the rest idle.

Bad reasons to use Cassandra

Schema flexibility

aka "I cannot figure out how to use ALTER TABLE", or at least make a flexible conventional schema ...

Some people have cited schema flexibility as a good reason to use Cassandra (same argument applies for Voldemort, Couchdb etc).

However, in practice this is NOT a benefit, because it comes at the cost of EVERYTHING ELSE YOU HAVE IN A TRADITIONAL DATABASE.

Let's see what Cassandra does NOT do:

Secondary indexes - I'd be really surprised if your app doesn't need any of those!
Sorting, grouping or other advanced queries
Filtering (mostly)
Synchronous behaviour of updates
Bulk updates (UPDATE 10,000 rows in one operation)
Efficient table creation / drop

That's quite a big list (and very incomplete) so you'd better have a better reason for using it than "I cannot figure out how to use ALTER TABLE"

Because X or Y uses it

Just because Digg, Facebook et al use Cassandra, doesn't mean you have to. Your data are probably more important than theirs. Your workload is probably different from theirs. In particular, your write/read scale requirements are probably less than theirs.

I have a lot of respect for Facebook, Digg developers etc, but I also have a lot of envy:

They lose data, nobody cares
They lose data, nobody rings up and complains
They lose data, and NOBODY DEMANDS THEIR MONEY BACK

They could get a bit of bad press, their users might desert them in numbers, but they wouldn't lose money directly and immediately.

Most companies who have big data provide a service, which comes with an SLA. The SLA often says that if we lose their data, they get their money back.

* May or may not be easy, depending on the calibre of your developers, ops staff, change control requirements, data structure etc.

When commit appears to fail

2009-10-27T21:15:00.000Z

So you're using explicit transactions. Everything appears to work (every query gives the expected result) until you get to COMMIT.

Then you get an exception thrown from COMMIT. What happened?

Usually this would be because the server has been shut down, or you've lost the connection.

The problem is, that you can't assume that the commit failed, but you also can't assume it succeeded.

A robust application must make NO ASSUMPTION about whether a failed commit did, indeed, commit the transaction or not. It can safely assume that either all or none of it was committed, but can't easily tell which.

So the only way to really know is to have your application somehow remember that the transaction MIGHT have failed, and check later.

Possible solutions:

Ignore it and deal with any inconsistencies manually, or decide that you don't care :)
Write your entire transaction such that if it is repeated having been committed previously, it is a no-op or has no harmful side effects (e.g. change INSERT to INSERT IGNORE to avoid unique index violations). This is rather difficult for complex transactions, but works for some.
Check, in your code, even if the commit apparently succeeded that it really did. If you discover that it didn't, then retry or report failure to the caller / user.
Perform another transaction to "undo" or "cancel" whatever the original transaction did if commit failed (Problem 1: this may itself fail to commit; problem 2: for a short time, an inconsistency exists)

None of these is ideal, and I'd like to think that this never happens. But if you do enough transactions, it's going to happen sooner or later (networks and servers do fail, unfortunately)

In testing, we've found that this happens so infrequently that even deliberate malice (kill -9 of mysql while loads of transactions are processing etc) fails to reproduce it reliably. In the end, a set of iptables rules which blocks the response from a commit was constructed.

On mysql's myisam_block_size setting

2009-09-02T13:00:00.004+01:00

There is a little-known setting, myisam_block_size in MySQL. This affects the block size used in the indexes of MyISAM tables stored in .MYI files on disc and in the key buffer.

The default value is 1k, this is possibly too small for best performance on modern systems; in particular, many filesystems used a bigger block size, so writing a single index block requires a read followed by a write. Random reads are really slow on hard discs (writes are mostly fast as they go into your battery-backed raid controller which has lots of RAM).

I am currently in the process of experimenting with myisam_block_size, and so far have determined the following:

myisam_block_size is settable only at server start time, either in my.cnf or on the command-line
myisam_block_size only affects newly created tables or tables rebuilt using ALTER TABLE; existing MyISAM tables keep their old index block size and work no differently.

There is a second setting, key_buffer_block_size, which is different. This controls the size of blocks in the key buffer, which is independent.

Advantages of higher myisam_block_size

Fewer IO requests required to satisfy some requests (e.g. range scans) as fewer blocks are needed
Better index prefix compression, as the blocks are larger - particularly with PACK_KEYS enabled (index compression works on a block-by-block basis)
No read-then-write required by the OS to write to the underlying disc, if the block size is >= the filesystem block size

Disadvantages

More wasted space in partially filled blocks
Possibly less efficient use of key cache, as more of the "wrong" blocks are cached
Larger IOs are required to fulfil the same request often - as it requires the same number of bigger blocks

So it could work either way. It may depend on your workload.

Testing continues.

Cassandra database and range scans

2009-08-06T18:19:00.004+01:00

I've been doing a little more playing with Cassandra, an open source distributed database. It has several features which make it very compelling for storing large data which has a lot of writes:

Write-scaling - adding more nodes increases write capacity
No single point of failure
configurable redundancy

And the most important:

Key range scans

Key range scans are really important because they allow applications to do what users normally want to do:

What emails did I receive this week
Give me all the transactions for customer X in time range Y

Answering these questions without range scans is extremely difficult; with efficient range scans they become fairly easy (provided you pick your keys right).

Other distributed-hash-table database (e.g. Voldemort) don't do this. This makes it difficult to do such queries.

Conventional RDBMSs do range scans all the time, in fact many queries which return more than one row will be implemented as a range scan.

Cassandra is extremely promising, but still a little bit rough around the edges; I've only done a small amount of research so far, but already found several bugs.

I can't complain about the service though; the main developer(s) have always looked into any problems I've reported immediately.

I hope it continues and becomes something really good.

Distributed data storage systems

2009-03-29T18:21:00.003+01:00

I'm looking for a distributed data storage system. Ideally such a system would have some (or ideally all) of the following characteristics:

Software only, using shared-nothing (must)
Stores arbitrarily large (actually 2G would be enough) items of binary data accessed by a key (a short-ish string would do) specified at store-time. Items would be stored across multiple storage nodes.
No single point of failure (preferable, a single point of failure which does not immediately impact the service would be acceptable)
Keeps N copies of each item in different nodes, specifyable either in config or at store-time
Automatic repairer to re-duplicate items following a storage node's demise (or administrative removal)
Automatic expiry of old data after a time specified at store-time
Managability: all nodes would share the same config; nodes can be administratively added and removed without any explicit config changes to other nodes.
Storage management: nodes should be able to be configured to use a given amount of maximum space; nodes should be able to be put into "readonly" mode where new data are not accepted
Automatic balancing of load for storage of new items
Monitoring: some cluster-aware monitoring tools which could report on the number of available nodes, total space available, which nodes were almost full, how much data is broken and being repaired, etc.

This is a bit of a tall order. There are some systems which almost do it, or do many of the above.

Tahoe seems to be the closest so far.

Of course things like Amazon S3 must do at least most of the above internally, but they aren't open source, indeed you can't even buy it except as a service.

REPAIR TABLE does not always upgrade it

2009-03-17T13:16:00.002Z

MySQL 5.0 uses a new MyISAM table format than 4.1. So when upgrading, the tables need to be upgraded.

This can be done using REPAIR TABLE. Or can it?

On 5.0.67 (I've been testing with), REPAIR TABLE does indeed upgrade it
On 5.0.22 (Earlier version, used on a backup slave), REPAIR TABLE does NOT upgrade it

Oh dear. That breaks my plan for how to upgrade the slave. Not a disaster, but annoying nonetheless

NB: this is a non-production system, and this is the kind of thing that we discover by testing it.

MySQL running out of disc space

2008-12-21T11:42:00.002Z

Running out of disc space is not a good situation. However, if it does happen, it would be nice to have some control over what happens.

We use MyISAM. When you run out of disc space, MyISAM just sits there and waits. And waits, and waits, apparently forever, for some space to become available.

This is not good, because an auditing/logging application (which ours is) may have lots of available servers which it could send its data to - getting an error from one would simply mean that the data could be audited elsewhere.

But if the server just hangs, and waits, the application isn't (currently) smart enough to give up and try another server, so it hangs the audit process too. Which means that audit data starts to back up, and customers wonder why they can't see recent data in their reports etc.

There has to be a better way. I propose

A background thread monitors the disc space level every few seconds
When it falls below a critical level (still more than can reasonably be filled up in a few seconds), force the server to become read-only
When in this mode, modifications to the data fail, quickly, with an error code which tells the process (or develope) exactly what the problem is (Out of disc space)
When the disc space falls below some threshold, the read-only mode is turned back off.

That way, clients get what they expect - either quick service for inserts, or a fast error telling them what's wrong (Go away, I'm full, audit your data somewhere else)

Drizzle etc, should do this.

Or perhaps, it's a job for the storage engine?

Happy Christmas.

Migrating from MySQL 4.1 to 5.x

2008-09-16T10:25:00.003+01:00

Suppose I have around 30 servers with MySQL 4.1 each with up to 1Tb of MyISAM tables. We need to upgrade their OS to support new hardware. We want to upgrade to 64-bit architecture to use the hardware efficiently.

Upgrading to 5.x seems like a good idea, as the additional validation work will be the same as would have to be done anyway - so we could effectively upgrade our MySQL version "for free", i.e. no additional QA work would be required than would be needed anyway.

Upgrading at any other time would involve a repetition of the validation exercise, which is not trivial as we must test at least:

All features of the complex application which use these databases still work without errors
The behaviour of the new version is consistent with the old one (e.g. results returned)
Performance needs to be measured to ensure that performance regressions haven't happened (or are acceptable)
Soak / stress testing needs to be done to ensure that the new version can perform under sustained high load.

Of course some changes to our software are inevitable, but these are likely to be minor. Testing is the main work.

Migration and rollback

My initial plan was to leave the MyISAM table files exactly as they were - after all, 5.0.x can read them fine (apparently) - so that if we need to rollback, we simply move back to the old version and everything works as before (including data created while we were on the new version).

However, this looks tricky. Load testing confirms that 5.0.x has frequent crashes when dealing with "old" tables (i.e. those which have not been upgraded). The MySQL manual insists that mysql_upgrade is run to upgrade all existing MyISAM tables.

This is fine if your database is small and noncritical, but ours are large and important. So what to do?

It is possible to upgrade tables using CHECK TABLE FOR UPGRADE and REPAIR TABLE. However, NO ROLLBACK METHOD exists. This is a problem.

I've estimated (using performance data from a production-spec test system) that in production, migration can be performed in 2-3 hours. This is an acceptable level of planned downtime. The only way of rolling BACK the table versions is to mysqldump the database and reload it. I estimate that this could take 16 hours, which is not acceptable.

It is not acceptable to apply a change without a working (i.e. tested) rollback procedure. Neither is it particularly acceptable to have 16 hours of downtime to perform one.

This makes the upgrade tricky. We're working on it.

Versioning your schema

2008-07-13T10:10:00.005+01:00

How do you version your database schemas?

There are quite a lot of different options. On some very small / trivial applications developed by just one person, it might be acceptable to use ad-hoc queries (maybe with a GUI) to manage the schema - but this runs the risk of development and production schemas becoming out of sync (or rather, out of sync with their respective versions of code).

Requirements are typically

Schema changes are held with the rest of the software in a SCM system
Schema changes can be made in one development environment and will be correctly propogated with code to other development and production environments as required
Schema changes can be rolled back (if for example, a deployment contains a serious bug)

There doesn't seem to be an easy answer for this one.

Our teams do something like:

Have a base create.sql which generates the original schema
In each revision where a schema change is required, write an apply.sql and remove.sql script to apply / roll back the change
These scripts need to be hand written as there are often special cases - sometimes several ALTER statements are required to make a single change, sometimes in combination with UPDATEs etc. Rollback scripts are even more tricky and sometimes need to restore from a backup table.
Have a system of scripts which works out which apply.sql / remove.sql is/are required (when deploying a new version of the code) and runs them, in order to update the schema

The problem with this system is that it relies on developers correctly writing apply.sql and remove.sql - it is fairly error prone. Although our validation process is fairly rigorous, it still lets errors slip through occasionally.

The problem is compounded by having multiple teams working on different code branches, not knowing necessarily which order their releases will go out in (one team would ultimately be responsible for merging the other's changes into their branch prior to release).

Ideas on a postcard please :)

Mark

Linux IP load balancing without a load balancer

2008-05-10T13:23:00.004+01:00

I've been investigating load balancing without a load balancer. I'm building my own implementation of a high availability IP load balancer / failover system.

This will essentially work like the netfilter CLUSTERIP target, except that it will also be self-configuring and self-monitoring / repairing - thus not requiring other tools (such as the complicated LinuxHA tools) to work. Some other efforts to do this have been:

Saru http://www.ultramonkey.org/papers/active_active/ - seems abandoned
Microsoft's "network load balancing" does something similar

An author known as "flavio" wrote an article about load balancer-less clusters here but it seems to have disappeared although it's still on the wayback machine

How IP load balancing works without a dedicated load balancer host is:

ARP requests for the cluster IP address are responded to by a multicast ethernet address
All the hosts join the ethernet multicast group
Hosts selectively accept / ignore traffic based on whether they want to handle it or not, by some hashing algorithm.

I've started work on the implementation on google code. Most parts of it can be done in user-space (A kernel implementation might be necessary for performance later):

I use arptables to block the kernel's own ARP responses on the load balanced IP, otherwise it would give out its own unicast link address.
A small userspace daemon responds to ARP requests, giving out a multicast address.
The IP address is configured normally with "ip addr add ..."
Iptables is used to filter out the traffic we don't want and accept traffic we do want. It uses connection tracking to ensure that established connections are always kept, invalid ones ignored, and new connections passed to a userspace daemon using NFQUEUE
A userspace daemon reads the packets from NFQUEUE and uses a hashing algorithm to determine whther to accept them or not. Each host in the cluster receives the same packets and does the same hash - so reaches the same conclusion about who should receive the packet - thus EXACTLY ONE host will accept each new connection.

Load balancing can be done fairly (all nodes equal weight) or unfairly (different weights). Also, when administratively shutting down a node, we can set its weight to zero and existing connections will be allowed to finish (new ones will then be given to other nodes).

I've created a very sketchy design, it's all basically completely do-able. The userspace daemon uses UDP multicast packets to talk to the other nodes, will organise a "leader" which will then tell the other nodes which hash values to accept/reject, ensuring that there is no overlap and no gaps.

There are a lot of possibilities for race conditions during a reconfiguration due to a node weight change / failure / recovery. I haven't thought about these yet.

This principle works well for TCP-based services such as web and email, but may not be good for some UDP-based services because conntrack cannot ensure that the packets continue going to the same node for the lifetime of the connection (as it does for TCP).

---
Problems / disadvantages:

Apparently, an ARP reply indicating a link-layer multicast address is forbidden by RFC1812
The Linux kernel ignores TCP packets which have a link-layer multicast destination. I've worked around this with a really small kernel module (the same as what CLUSTERIP does)
Interoperability with other network OSs might not be good as this isn't a very official technique. Apparently some routers ignore these ARP packets.

MySQL engines space usage comparison - continued

2008-04-20T18:04:00.006+01:00

Since Oracle announced the InnoDB plugin which provides compressed row formats, I thought I'd do this comparison again.

Here are some more data that I've cobbled together.

I've created a 2M row table with a schema that we use for audit data. It has 47 columns and 17 indexes.

I copied the table into new empty tables using INSERT ... SELECT with each of these engines:

InnoDB row_format=compressed (from the InnoDB plugin)
InnoDB row_format=Compact
InnoDB row_format=Redundant
MyISAM pack_keys=1
MyISAM packed with myisampack (NOTE: these tables are readonly)
PBXT
Falcon

Here are the data:

Engine	Space (MB)
InnoDB compressed	636
InnoDB compact	1946
InnoDB redundant	2253
MyISAM pack_keys=1	738
MyISAM packed	476
PBXT	3379
Falcon	999

I am very impressed with how good Falcon is. I am very impressed with InnoDB's compressed row format's space usage.

I don't think it's very reasonable to compare the loading times for the different engines as they all work in different ways, but some were a LOT faster than others. Take this with a pinch of salt:

Fast: MyISAM, Falcon, InnoDB except compressed
Slow: InnoDB compressed, PBXT

InnoDB compressed and PBXT both took well over an hour to complete what the others did in a few minutes. I can't explain this - maybe it's a tuning issue.

In each case, the copy was done in an INSERT ... SELECT which uses a single transaction. I tried to tune the buffers as best as I could (the server has enough ram) but I'm no expert in PBXT and Falcon.

Improve reliability and maintainability by using stateless connections

2008-04-13T12:29:00.006+01:00

MySQL application developers have some problems with connection state. There is a lot of state which can be used associated with a single connection

Transactions (autocommit, uncommitted work, when a transaction was started, isolation level)
Temporary tables
Session variables which affect behaviour
Other session variables

If you can avoid all of these, then you can benefit from transparent auto-reconnection and transparent connection reuse / pooling etc.

If you can't, then you will have to deal with reconnection, retry at a higher level. This is complicated and difficult to test.

I'll discuss ways of avoiding these things one at a time:

Transactions

I'm not advocating shunning transactions completely, just avoiding keeping any transaction state in the connection:

Keep autocommit on. Execute everything you want in a transaction in a single statement (in MySQL it is possible to delete from several tables in one statement, insert several rows etc).
Transactional engines will still use transactions if autocommit is on - a single statement becomes a single transaction - you will see a consistent view within that statement - no other thread will see parts of a batch insert, update or delete.
Non-transactional engines behave the same anyway.
Alternative: do everything that needs to be in a transaction inside a stored procedure - if these are retried, they will be retried as a whole.

Temporary tables

These are best avoided. They also cause problems for statement-level replication.

Use short-lived permanent tables with unique names instead (perhaps in a separate database which is automatically cleaned up when tables get old)
Wrap things which need temporary tables inside stored procedures
Use derived tables instead - in some cases this is sufficient.

Session variables which affect behaviour

Sometimes you can't get away from having to set these variables. Common examples are:

SET NAMES utf8
SET SQL_MODE = 'TRADITIONAL'

If an autoreconnect happens while these are in effect, you could accidentally get rubbish data inserted into the database if a connection reverts to latin1 unexpectedly or SQL_MODE reverts to its default "anything goes".

As these are unavoidable, they always have to be set - but there is a solution - the MYSQL_INIT_COMMAND option. As these commands won't actually have any effect on their own, we can have them automatically re-done when the library reconnects to the database.

The only problem with this approach is that not all client library interfaces allow the setting of this option - but in some cases it can be set in my.cnf (for client libraries which read this)

Other session variables

Quite simply - don't use them. There is no reason I know of for having another session variable.

Don't select @@LAST_INSERT_ID or last_insert_id() - use your API method to get it instead. An auto-reconnect happening between the insert and the select @@LAST_INSERT_ID will cause it to return zero, which is probably not what you wanted or expected.
Don't define your own variables - keep them on the client instead (or if you absolutely must store state server-side, put it in a table!)

Conclusions

It is possible to enable automatic reconnect for greater resilience at the MySQL API level
To do so safely you must avoid keeping session state in the connection
Variables which need to be reset on connect can be reset with mysql_init_command

MySQL engines and space usage

2008-03-22T09:21:00.000Z

A lot of people seem to spend a lot of effort comparing storage engine in MySQL - chiefly focusing on the difference between MyISAM and InnoDB.

People normally compare:

Feature sets:InnoDB: better durability, transactions, MVCC, foreign key constraints, row-level locking. MyISAM: fulltext, spatial indexes, table-level locking
Run-time performance: (see your favourite benchmark)

But few compare actual storage space usage. As this is very important to our application, I decided to run some tests.

I'm testing here with a realistic-sized table for our application (we partition data into daily partitions and spread them across many servers anyway, so this is just a small piece). We currently use MyISAM, and this is a typical table with approximately 4 million rows.

I can't dump the schema or content of this table here for confidentiality reasons, but it has:

47 columns, many of which are VARCHARs
17 indexes
Typical row size of 300 bytes (as reported by "SHOW TABLE STATUS")

Method

First I generated 4 Million rows of dummy data using a test tool (this is extremely useful as it avoids having to take real, potentially sensitive and very large data from production). Then I measured the space usage using SHOW TABLE STATUS, and successively ALTERed the table to different engines.

Note that ALTERing a table causes its indexes to be rebuilt - they will be a good deal smaller than if the rows had been inserted normally. Therefore, just an ALTER which doesn't change anything could make a big space saving.

Results

Engine	Data size	Index size	Notes
MyISAM, PACK_KEYS=1 (initial)	1152854640	682459136	Initial data load
MyISAM, PACK_KEYS=0	1152854640	2396920832	ALTER
MyISAM, PACK_KEYS=DEFAULT	1152854640	677806080	ALTER
MyISAM, PACK_KEYS=1 (alter)	1152854640	433162240	ALTER
InnoDB	1973420032	2442166272	ALTER

sizes are in bytes.

In case that's not clear from the numbers above, here is a summary of the results

In MyISAM, the data are always the same size regardless of options
The initial (PACK_KEYS=1) index file was 650M
PACK_KEYS=0 makes the index 251% bigger
PACK_KEYS=DEFAULT makes the index marginally smaller (but it's been ALTERed which improves things)
Altering the table with PACK_KEYS=1 makes the index around 36% smaller than it originally was
InnoDB leaves the data 71% bigger and the index 257% bigger

Moving from MyISAM to InnoDB in this case would see an overall increase in space usage of 140% - that's making the disc requirement nearly 2.5 x more.

Conclusions

Before making a switch from MyISAM to InnoDB, plan your space usage carefully.
Measure your actual data, not mine. Yours may be different. We have a lot of indexes compared to some people.

Future work

I hope to be able to repeat this test with Falcon, Maria and PBXT some time soon.

mysql address space

2008-01-02T11:24:00.000Z

Address space can be a significant problem on 32-bit mysql installations. After several IRC discussions attempting to explain this to some people, I decided to write this post about it.

What address space is

Each process running on a multitasking virtual memory OS has its own private address space. This is a range of addresses of a fixed size. The exact size depends on the CPU architecture, but on most 32-bit processors it is 32-bits. Intel/AMD chips address memory in bytes individually, so the maximum capacity of the address space is 2^32 bytes.

Address space has nothing whatsoever to do with the physical amount of RAM installed on a machine; you may have more address space than ram, but critically, you may also have less.

It is not possible to modify or upgrade the address space of a machine or software program, except by recompiling it for a different architecture.

How much space is available

Unfortunately, not all of the approximately 4Gb of address space is usable by mysql for buffers; some is used by:

The operating system kernel reserving address space for its own use
Code from the mysql executable file and its libraries etc
Fixed data allocated by mysql and its libraries
Thread stacks
etc

The amount that these use up is extremely system dependent, but it is normally safe to assume that on a 32-bit Linux OS, roughly 1.5Gb is available for mysql's buffers. On some systems there may be a bit more than this, but it's not likely to exceed 3Gb on any platform.

What impact running out has on mysql

MySQL is a multithreaded server process - each instance of mysql running on a machine (there is typically only one; several applications or databases can use the same server). This means that all its threads share the same, limited address space.

The database server can be tuned to use a lot of memory. There are two kinds of buffer - per thread and global buffers. The MyISAM key buffer is an example of a global buffer, the packet read buffer is an example of a per thread buffer. This is explained in more detail in the manual here: How MYSQL uses memory

All this memory comes from the same, limited address space. Running out of address space is very bad. When you run out of address space, the following happens:

A memory allocation attempt will fail. How gracefully this happens depends on where the allocation attempt was made. It could be: in the mysql core, in a storage engine, in an underlying library (e.g. the C or C++ runtime libraries) or in some other extension.
MySQL will probably crash. If a crash happens:
The server will restart, kicking all clients off.
InnoDB will start its recovery process. This may take a long time during which the server will be unusable.
Eventually all transactions will be rolled back and normal service will resume.
MyISAM tables which were in use at the time will be marked as crashed and needing a repair - this can take a very long time on large tables, during which time those tables are unusable.

This is very inconvenient for everyone concerned (the app developer, the sysadmin/DBA and especially the end user).

Mitigation: How to prevent running out

The most obvious and convenient solution is to run a 64-bit mysql server on a 64-bit OS. These have a lot more address space and are very unlikely to run out.

If, for some reason, you cannot upgrade to 64-bit, it is a very good idea to ensure that you do not run out of address space. This is mostly done by tuning the server parameters.

Do not set max_connections higher than necessary.
Keep per-thread buffers to a minimum
Tune other buffers reasonably

Monitoring the address space in use by mysql is a good idea. This is normally possible under Linux with "top" or "ps", as the "virt" or "VSZ" columns show the amount of address space used.

Final notes

Run a 64-bit OS if possible and for all new development. Run a 64-bit mysql if you run a 64-bit OS.
Running out of address space is not the same as running out of memory- it will probably happen a lot sooner (your server does have more than 2Gb of RAM, right?)
You can still run out of address space if you have less than 2Gb of RAM, because virtual memory is included
None of this is Linux-specific, it will happen on other OSs too (but the amount of space reserved by the kernel may be less)
Avoiding address space exhaustion is important in production systems

On web application configuration

2007-12-29T15:07:00.001Z

There are two common approaches for configuring web applications:

Configuration file(s) in the web application's directory
Storing configuration data in the database

What sort of data am I talking about:

Email setup (where to send response form, error emails etc, whether to send mail to customers etc)
Setup of things which need to vary between development and production - payment service provider configuration, setup of other third party integration things
Error handler configuration (whether to display errors, where to log them, email them etc)
Enabling / disabling features (for example, debug things)
Visual stuff or branding (labels etc)

Clearly the database connection string needs to be stored somewhere too, but that can't be in the database for obvious reasons.

Database configuration

pros: does not need to be configured on each web server of a farm; not accidentally overwritten by new deployments
cons: bad for hierarchical or structured data; performance overhead; difficult to maintain configuration data stored in a database in SCM. Modifications require apply/ rollback scripts

Configuration files

pros: Easy to manage in SCM (like all your other files, even if not in the same place); less runtime performance overhead; better for hierarchical data; database connection string can be held with other items.
cons: Needs to be synchronised across a web farm; care should be taken that the right versions are always deployed everywhere.

Ideas

For a larger application with a web farm, using a database for some configuration is clearly beneficial. If you have many servers and/or servers of other types (non-web servers), they can store all their configuration in a central database. If performance is a problem, copies can be cached locally.

For a small simple application, configuration files are better- they're more manageable generally. I prefer to put most configuration in a file if possible.

mysql dumps are not text files

2007-12-03T14:36:00.000Z

Today's pearl of wisdom:

MySQL dump files, as produced by mysqldump, are not text files.

A common mistake is to think that mysqldump files are text files. While they superficially look like text files, they in fact are not. This leads to confusion and problems if people try to edit them in a text editor application, which will in many cases cause corruption.

MySQL dump files contain the SQL commands required to recreate the contents of a database (or subset thereof). However, these are SQL commands in arbitrary (binary) encoding and can consist of a mixture of different encodings (e.g. if you have BLOBs). This mixture is not safe to edit with a text editor, which expects a text file to contain character data in exactly one encoding (and will typically guess, ask the user or just use its default to determine which).

Suppose you use utf8 data in your database - then your mysqldump file will probably contains all of its strings in utf8. This is fine, until you get to a BLOB. This is a binary lump of data and will be full of sequences of bytes which are NOT LEGAL in utf8, therefore any editor attempting to make sense of them in utf8 won't succeed. It will therefore be forced to change them into something else (i.e. different from the original BLOB data) in order to load them into its buffers.

Saving this modified (or even unmodified) file back out will result in loss of data.

Summary

MySQL dump files aren't text files even though they look like them

Editing one in a text editor will probably lose data

They are often also too big for a text editor

And have lines too long

Some MySQL hacking projects

2007-11-26T20:50:00.000Z

I have two ideas for hacking MySQL in a useful manner, in response to issues I've had using it:

InnoDB tablespace usage monitor

A really simple tool that would report the amount of innodb tablespace used by specific tables and, ideally, indexes too.

This is required as I'm fairly sure that there isn't a tool to do this at the moment.

MyISAM mmap() its index files

I'm having trouble tuning MyISAM's key_buffer for production use. We want to use delay_key_write to reduce the IO of a lot of inserts, but at the same time, I'd prefer it not to get too out of hand, as large flushes create a lot of I/O in one go.

Anyway perhaps that was a poor explanation of my motivation, however, the idea would be:

mmap() the whole of each index file when the table is opened.

Leave the memory mapping in place as long as the table is open. resize the file and the memory mapping if necessary, e.g. when the index file gets bigger.

Have all reads/writes go via the memory mapping.

msync() as necessary (e.g. following a write)

I'm confident that this will have a performance benefit, but mostly, it will remove the onus on the DBA to tune key_buffer correctly, and allow the OS to use its memory how it wants to.

The downside? It won't work very well on 32-bit systems as they'll rapidly run out of address space. But do we care any more?

mysql - ENUMs and sql_mode

2007-10-14T16:25:00.001+01:00

I've been doing a few experiments with ENUMs and SQL_MODE.

Not a lot of people realise that in MySQL, an ENUM can actually contain one other value as well as the specified ones - no, not NULL, but ANOTHER value, specifically, the empty string ''.

This can lead to trouble. The empty string is entered by MySQL when it tries to insert an invalid value into the column. It does also give a warning, but nobody takes any notice of those right?

Demo schema:


CREATE TABLE enumtest (
 id int not null auto_increment,
 name varchar(100) NOT NULL,
 status ENUM('ok','broken','decommissioned','narnia'),
 PRIMARY KEY(id),
 KEY(name),
 KEY(status)
);

Now let's try some inserts...


INSERT INTO enumtest (name,status) VALUES
 ('Mark','ok'),
 ('Fred','broken'),
 ('Bob','decommissioned');

So far so good. Now how about:


mysql> insert into enumtest (name,status) VALUES ('bust','wibble');
Query OK, 1 row affected, 1 warning (0.00 sec)

Ok, so it inserted something, and we got a warning. What's there now?


 SELECT * FROM enumtest;
+----+------+----------------+
| id | name | status         |
+----+------+----------------+
|  1 | Mark | ok             | 
|  2 | Fred | broken         | 
|  3 | Bob  | decommissioned | 
|  4 | bust |                | 
+----+------+----------------+
4 rows in set (0.00 sec)

So what's happened is, we've attempted to insert an invalid value (wibble) and it's put an empty string in instead (empty string is not a null, as we defined the column NOT NULL).

So what's the fix? SET sql_mode to traditional:


mysql> SET SQL_MODE='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO enumtest (name,status) VALUES ('breakme','wibble');
ERROR 1265 (01000): Data truncated for column 'status' at row 1

Much better. We can now actually get ENUMs that only contain values they're supposed to. Good.

Conclusion: use sql_mode TRADITIONAL whenever possible.

MySQL: delay_key_write is good

2007-10-06T09:08:00.000+01:00

If you're using MySQL with the MyISAM engine for a high-writes application, delay_key_write is usually very good.

Let me explain further: delay_key_write is a table option which causes the database NOT to flush the MyISAM key file after every write. This is a really good thing, as if you're doing another write very soon anyway, this is likely to just waste I/O time.

This doesn't sound like a good idea right, because it means that if the power fails (or mysql crashes, or something), then you'll be left with a broken index file? No, it's still a very good idea:

delay_key_write does NOT appear to affect the MyISAM data file - that will still be flushed according to the normal policy.
If you had delay_key_write off, then a power failure or crash during the index write would cause the same level of corruption.
Broken MyISAM index files need to be rebuilt, regardless of how little or how much "brokenness" they actually have.

How do you turn delay_key_write on?

It's a table option, so you have to do it at create time. It's possible to do ALTER TABLE, but that behaves how it normally does under MySQL - i.e. it rebuilds the entire table, just to change one flag. This is of course necessary if you're changing the schema, but delay_key_write shouldn't - sadly, it does.

This means that on a large existing table, the ALTER TABLE needs to be done with care (i.e. out-of-hours or during scheduled down time, if at all).

Another option is to set the variable delay_key_write to "ALL" - however, this is not necessarily a good idea as it applies to all tables (in the entire server) whether you want it to or not.

On infrequently written tables, delay_key_write could increase the chance of index corruption.

Our performance testing cycle for delay_key_write is now over - quite frankly we're amazed by how much performance it gives us for so little effort - it will definitely be going into production.

Our servers are being hurt with heavy write-load which is keeping (at least one thread of) mysql almost permanently in I/O-wait - delay_key_write makes almost all of this go away making inserts much quicker and leaving the server more time for other things. It's likely to become essential once our server workload grows even higher.

Critical Mass

2007-06-30T10:14:00.000+01:00

Last night I went to Critical Mass - it was fairly quiet but regulars were there. Didn't get rained on, which was good.

No bike polo however (Which is moderately dangerous and I'm rubbish at) - but we did see an interesting, erm, thing for Cowley Road Carnival. Some chaps in East Oxford have built a spider-bike - giant spider on top of a frame with three bikes underneath. It animates as it goes along. The middle rider operates the spider and steering, the two back riders provide power.

Upcoming events: Apparently there is a Perl programmers meeting in Reading on 10th July somewhere - I'll post more details when I have them.

Introduction

2007-06-24T08:51:00.000+01:00

Well, here we are. My first blog post on Blogger. Shows that I'm really with the times, starting several years after blogging became passé.

Some topics which I may post on:

Computing generally
IT Security
Linux and/or other operating systems
Music
Beer
The weather (unlikely)

I'll put some links in soon.