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, …

Last time I said that the USL has a forgotten third coefficient, the coefficient of performance. This is the same thing as the system’s throughput at concurrency=1, or C(1). How do you determine this coefficient? There are at least three ways.

Neil Gunther’s writings, or at least those that I’ve read and remember, say that you should set it equal to your measurement of C(1). Most of his writing discusses a handful of measurements of the system: one at concurrency 1, and at least 4 to 6 at higher concurrencies. I can’t remember a time when he’s discussed taking more than one measurement of throughput at each level of concurrency, so I think the assumption is that you’re going to take a single measurement at various concurrencies (or, in the case of hardware scalability, units of hardware), and …

I prefer to run MySQL with innodb_flush_method=O_DIRECT in most cases – it makes sure there is no overhead of double buffering and I can save the limited amount of file system cache I would normally have on database server for those things which need to be cached — system files, binary log, FRM files, MySQL MyISAM system tables etc. Starting MySQL 5.5 MySQL uses asynchronous IO which should allow it to load IO subsystem very effectively being able to issue many outstanding requests which when can be merged on OS level or RAID controller so we should expect at least as good performance from O_DIRECT as from buffered mode ? It turns out it is not always the case.

I came to this take by accident so there is not a lot of science in coming up with it but I think it is still pretty well representative. I have an old test server having 4*7200RPM SATA hard drives in RAID10. It has 8GB of RAM and I’m running Percona Server …

Just found that in a Google referer to the blog:

I want SQL to return blank row even if the condition does not match

This may be useful for certain ORMs which always expect a single row as a result of a query.

Say, we have a query like that:

SELECT  *
FROM    mytable
WHERE   id = 42

and want it to return a single row (possibly consisting of NULL values) no matter what.

If we had a join and the condition in the ON clause:

SELECT  m.*
FROM    values v
JOIN    mytable m
ON      m.id = v.value

, we could just rewrite an INNER JOIN to a LEFT JOIN.

SELECT  m.*
FROM    values v
LEFT JOIN
mytable m
ON      m.id = v.value

This way, we would have at least one record returned for each entry in values.

In our original query we don't have a table to join with. But we can easily generate it:

 …

pt-stalk recipes: Gather forensic data about MySQL when a server problem occurs

It happens to us all from time to time: a server issue arises that leaves you scratching your head. That’s when Percona Toolkit’s pt-stalk comes into play, helping you diagnose the problem by capturing diagnostic data that helps you pinpoint what’s causing the havoc hitting your database.

From the documentation (http://www.percona.com/doc/percona-toolkit/pt-stalk.html):

pt-stalk watches for a trigger condition to become true, and then collects data to help in diagnosing problems. It is designed to run as a daemon with root privileges, so that you can diagnose intermittent problems that you cannot observe directly. You can also use it to execute a custom command, or to gather the data on demand without waiting for the trigger to happen. …

Many times, we see aborted connections in mysql error log file, while restoring backup, taking data dump (backup) or executing analytics (report) query on mysql server. This happens due to extremely poor network communication between the device. The mysql configuration variables related to this is below. The default value of these variables is 30 and 60.

net_read_timeout
net_write_timeout

• net_read_timeout: The number of seconds to wait for more data from a connection before aborting the read. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort.

• net_write_timeout: The number of seconds to wait for a block to be written to a connection before aborting the write.
…

MySQL MHA by Oracle ACE Director Yoshinori Matsunobu is an excellent open source tool to help in providing HA with native MySQL replication. The installation however is dependent on some Perl packages and to the untrained eye this may be an issue if you are using Percona Server as your choice of MySQL implementation.

The MHA Node page requires the perl-DBD-MySQL package to be installed. The installation on RedHat/CentOS/Oracle Linux look like this:

$ sudo yum install perl-DBD-MySQL
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror.anl.gov
* extras: mirror.anl.gov
* updates: mirror.anl.gov
Setting up Install Process
Resolving Dependencies
--> Running transaction check …