MySQL Replication has a weak point: the master (primary) is a single point of failure. People ask frequently about client failover. Making a client library failover a client automatically from one server to another is simple. PECL/mysqlnd_ms can do it. Unfortunately, this is only the tip of the iceberg when using a primary copy (master-slave) database cluster. Among others, you may have to reconfigure clients. Below is one hint (no solution) for deploying PECL/mysqlnd_ms configuration files using MHA (Master High Availability Manager and tools for MySQL).

What’s the plural of dilemma?

If a server is unavailable a client may attempt to connect to another server given a list of alternatives. A client library can handle it entirely transparently and automatically if connection state is no …

A user of mysqlimport. Josh Baird, reminded me of a feature which I should have added from teh start, but which was forgotten about. The deal is that when you put a bunch of JSON objects in a file, you have a couple of options on how to do this.

The most obvious is maybe to export as a JSON array of objects, like this:
[
{"id":1, "name": "Geraint Watkins"},
{"id":2, "name": "Kim Wilson"}
]
But this is not what mysqljsoninport supported and this is not how, say, MongoDB exports JSON by default. The reason is that for large amount if data this is cumbersome, as what is in the file is actually one big JSON object containing all the data. This is difficult to parse, requires that a lot of data is read and that the object in whole is kept in memory, unless some clever processing is done. And if we are clever, this is still not effective. Rather, what was supported by mysqljsonimport and how MongoDB …

Download PDF Presentation

This presentation discusses the lessons learned about the issues of managing a large number of MySQL instances, Including experiences from multiple clients including supporting one billion+ requests statements per day (and 50+ billion SQL statements), and smaller users of LAMP stack, Microsoft stack and RDS configurations. Topics include:

• Monitoring and instrumentation are essential to manage cost
• Cost saving techniques
• How to automate installations, upgrades and deployments
• Traffic minimization techniques
• Creating HA with regions and zones
• Real-time traffic stats (aggregated every 5 seconds)
• AWS …

Apache Cassandra and BigTop updates. And more

For 451 Research clients: Kx Systems aims to slipstream the ‘big data’ bandwagon to expanded kdb+ adoption bit.ly/VxkYlC

— Matt Aslett (@maslett) January 3, 2013

For 451 Research clients: IBM sheds light on ‘big data’ integration and governance the Big Blue way bit.ly/ZTAZcw By Krishna Roy

— Matt Aslett (@maslett) January 2, 2013

The Apache Software Foundation Announces Apache Cassandra v1.2 bit.ly/UFGKFN

— Matt Aslett (@maslett) January …

In On learning InnoDB: A journey to the core, I introduced the innodb_diagrams project to document the InnoDB internals, which provides the diagrams used in this post. (Note that each image below is linked to a higher resolution version of the same image.)

The basic structure of the space and each page was described in The basics of InnoDB space file layout, so now we’ll expand on that to describe InnoDB’s structures related to management of pages and extents, and “free space” management, and how it tracks pages allocated to the many different purposes for which pages are used.

Extents and extent descriptors

As described previously, …

I got some very good comments/questions on my previous post on MariaDB global transaction ID, from Giuseppe and Robert (of Tungsten fame). I thought a follow-up post would be appropriate to answer and further elaborate on the comments, as the points they raise are very important and interesting.

(It also gives me the opportunity to explain more deeply a lot of interesting design decisions that I left out in the first post for the sake of brevity and clarity.)

On crash-safe slave

One of the things I really wanted to improve with global transaction ID is to make the replication slaves more crash safe with respect to their current replication state. This state is mostly persistently stored information about which event(s) were last executed on the slave, so that after a server restart the slave will know from which point in the master binlog(s) …

I was surprised to find on a customer MySQL server this new syntax for Master_host in SHOW SLAVE STATUS.

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: testdb1.xxx.com or 10.XXX.XX.XXX
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: db1-354215-bin-log.000005
          Read_Master_Log_Pos: 1624
               Relay_Log_File: db2-354214-relay-log.000001
   

Is this a fancy new Percona Server feature? No. It’s operator error.

We read a little further to find.

mysql> SHOW SLAVE STATUS\G
...
             Slave_IO_Running: Connecting

...
                Last_IO_Errno: 2005
                Last_IO_Error: error connecting to master 'repl@ testdb1.xxx.com or 10.XXX.XX.XXX' - retry-time: 60  retries: 86400

In On learning InnoDB: A journey to the core, I introduced the innodb_diagrams project to document the InnoDB internals, which provides the diagrams used in this post.

InnoDB’s data storage model uses “spaces”, often called “tablespaces” in the context of MySQL, and sometimes called “file spaces” in InnoDB itself. A space may consist of multiple actual files at the operating system level (e.g. ibdata1, ibdata2, etc.) but it is just a single logical file — multiple physical files are just treated as though they were physically concatenated together.

Each space in InnoDB is assigned a 32-bit integer space ID, which is used in many different places to refer to the space. InnoDB always has a “system space”, which is always assigned the …

There is no great joys for the bloggers to mark the holiday season with some blistering and lightweight blog posts. This Log Buffer Edition in Log Buffer #302 shares that mirth and presents you with another medley of blogs.

Oracle:

Eddie Awad asks; Have you installed or recently upgraded to Oracle APEX Listener 2.0? Have you used SQL Developer 3.2 to manage APEX Listener settings? If you answered yes to both questions then you are in for an unwelcome surprise.

It’s the end of the year, and the start of the year, and thus time for the ‘best of’, ‘ten best’ and perhaps even …

Comparison of Protocols
A while ago I wrote a small random function tester to fuzz test native functions such as linestring, polygon, astext, etc.  The queries it sends are generally small (100 bytes or less) and a totally CPU bound workload, since no data/tables are accessed.

As this was pretty much an open-ended test,  simply pumping random data into the functions, I had planned to let it run for a few days and see if any problems arose.

I benchmarked all the ways to connect on windows;  TCP/IP, named pipe, …