This post is the second of a series that started here.

The first step to build the HA solution is to create two working instances, configure them to be EBS based and create a security group for them. A third instance, the client, will be discussed in part 7. Since this will be a proof of concept, I'll be using m1.small type instances while normally, the mysql host would be much larger. Using another type is trivial. I will assume you are using the command line api tools, on Ubuntu, install "ec2-api-tools". The use of these tools simplifies the expression of the command compared to the web based console.

Create the security group

The instances involved in the MySQL HA setup will need to be inside the same security group for networking purposes and the help identify them. To …

In addition to the Boston edition, there’s an OpenSQL Camp at the same time and place as FrOSCon mid-August in Germany. The call for papers is open until July 11th. As always, the conference is about all kinds of open-source databases: MySQL and PostgreSQL are only two of the obvious ones; MongoDB and Cassandra featured prominently at the last one I attended, and SQLite was well represented at the first one.

Related posts:

1. OpenSQL Camp Boston 2010
2. OpenSQL Camp events in 2009
3. Recap of Portland OpenSQL Camp 2009 …

Monty’s talk at Velocity 2010 seemed to go down really well (at least from reading the agile admin entry on Drizzle). There are a few great bits from this article that just made me laugh:

“Oracle’s “run Java within the database” is an example of totally retarded functionality whose main job is to ruin your life”

Love it that we’re managing to get the message out.

• Share this on Facebook
• …

Again, another simple test. Same basic tuning as yesterday, and the table schema is the same:
CREATE TABLE `t1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` char(100) DEFAULT NULL,
PRIMARY KEY (`c1`)
);
The table is again filled with 1.3 million rows, but this time I'm doing an UPDATE. The update is again a simple primary key update:
UPDATE t1 SET c2 = CONCAT('xxx', RAND(), 'yyy') WHERE c1 = <random value 1 - 1000000>;
I run this on the Maria, InnoDB and MyISAM engines. The issues with the MyISAM and Maria engines here is that they lack row level locking. In MariaDB 5.1.47, the InnoDB version is 1.0.6, so it is more scalable than what it used to be. The testbench is not an incredibly hot machiine, just a 4 core AMD box.

I run the test in some different configurations, using a single thread, using 10 threads and using 100 threads. Here we can see that InnoDB Row-level locking …

We want talk about all open source database. Not only MySQL.
Open source database fans, wake up, and …

You have a huge mysql table - maybe 100 GB. And you need to run alter on it - to either add an index, drop an index, add a column or drop a column. If you run the simple mysql "alter table" command, you will end up spending ages to bring the table back into production.

Here is a simple hack to get the thing done. The benefit of the hack is that the alter runs quite fast. But since this is a hack

I have just uploaded Q4M (Queue for MySQL) 0.9.4 to q4m.31tools.com.  There has been no bug fixes since 0.9.3, the only change is the newly added function queue_compact(table_name) that can be used to trigger table compaction manually.

If you were looking for a way to control queue compaction timing, it would be worth considering upgrading to 0.9.4.

For more information of what compaction is, please refer to my last entry on Q4M describing concurrent compaction.

Over at the Drizzle blog, the recent 2010-06-07 tarball was announced. This tarball release has my fixes for the ENUM type, so that it now works as it should. I was quite amazed that such a small block of code could have so many bugs! One of the most interesting was the documented limit we inherited from MySQL (see the MySQL Docs on ENUM) of a maximum of 65,535 elements for an ENUM column.

This all started out from a quite innocent comment of Jay‘s in a code review for adding support for the ENUM data type to the embedded_innodb engine. It was all pretty innocent… saying that I should use a constant instead of the magic 0×10000 number as a limit …

Updated: 29-Jun-2010, 30-Jun-2010.

For me, ODTUG Kaleidoscope 2010 started on Friday with the ACE Directors briefing. Best practices topic was touched there slightly and I twitted about it. I decided that the feedback deserves a blog post so I’m simply quoting the conversation here. If you have anything to add, you know where to find the comment box.

alexgorbachev:
best practices should be forbidden or rather renamed to blue-prints #ACED …

With the exhaustion of IPv4 address space looming sometime in 2012; probably earlier rather than later, it makes sense to ease on into IPv6 land.  Without straying into tunnel broking and endpoint shenanigans 6to4 is a method of wrapping up IPv6 inside of IPv4.

(note that MySQL does not currently support IPv6 itself – but what we’re discussing here is about externally facing systems, like your web/application servers)

6to4 performs three functions:

1. Allocates an IPv6 address block to any host/network that has a global IPv4 address.
2. Wraps up IPv6 packets inside IPv4 packets for transmission over IPv4 using 6in4 (traffic is sent over IPv4 inside IPv4 packets whose IP headers have the IP protocol number  set to 41; IPv6-in-IPv4. )  …