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 …

What a flashback this week. Staring at a text terminal trying to establish a connection with a remote server, I began to fret whether I would get my homework assignment done on time. My mind raced back to college nights years ago in the Fishbowl, hunched over an Athena workstation. Would this be another late night fueled by Jolt cola in order to get my problem set done?

Thankfully, no!

Embarking on my first software class in quite a while was relatively painless, and I have Sheeri Cabral and her detailed guidance to thank. This week I started the …

Read the original article at Why your cloud is speeding for a scalability cliff

Also find Sean Hull’s ramblings on twitter @hullsean. Don’t believe me that you’re headed for the cliff? A startup scales up to no avail Towards the end of 2012 I worked with an internet startup in the online education space. Their web application was not unusual, built in PHP and using Linux, Apache & Mysql [...]

For more articles like these go to Sean Hull's Scalable Startups

Related posts:

1. 3 Ways to Boost Cloud Scalability
2. Cloud Operations …

The main goal of global transaction ID is to make it easy to promote a new master and switch all slaves over to continue replication from the new master. This is currently harder than it could be, since the current replication position for a slave is specified in coordinates that are specific to the current master, and it is not trivial to translate them into the corresponding coordinates on the new master. Global transaction ID solves this by annotating each event with the global transaction id which is unique and universal across the whole replication hierarchy.

In addition, there are at least two other main goals for MariaDB global transaction ID:

1. Make it easy to setup global transaction ID, and easy to provision a new slave into an existing replication hierarchy.
2. Fully support …

In On learning InnoDB: A journey to the core I introduced a new library and command-line tool in the innodb_ruby project. Now I’ll show off a few of the things it can do. I won’t try to explain all of the InnoDB structures exposed, since that will get the demos here way off track. We’ll come back to those structures later on!

Installing innodb_ruby

If you’re familiar with Ruby and gems (or you just happen to have a well-configured Ruby installation), I regularly push innodb_ruby gems to RubyGems, so you should only need to:

gem install innodb_ruby

If that doesn’t work, you might want to check out The RubyGems manual to try and get your installation working. …

Here at Percona we’ve spent a lot of time improving our development and testing practices. Why? Because constant innovation keeps us ahead and more productive. We want to work smarter, not harder. One of the tools we use is the Jenkins Continuous Integration server.

We use Jenkins pretty heavily to help with out development processes and workflow. The flexibility, modularity and plugin availability are great advantages to Jenkins, and it being free and open source software that we can freely modify and bug fix is immensely valuable (indeed, I now maintain the Jenkins Bazaar plugin).

We currently have:

• approximately 32 slaves performing builds
• 151 top level jobs (many of these are matrix jobs)
• 2,578 …