As you may know, flushing in MySQL is an area of my interest, I wrote about it several times, i.e.
http://www.mysqlperformanceblog.com/2011/09/18/disaster-mysql-5-5-flushing/
http://www.mysqlperformanceblog.com/2011/03/31/innodb-flushing-a-lot-of-memory-and-slow-disk/
http://www.mysqlperformanceblog.com/2011/01/03/mysql-5-5-8-in-search-of-stability/

In MySQL 5.6 there was implemented a new flushing logic, so I decided to check what do we have now.

For experiment I took tpcc-mysql workload (3 hours runs), 2500W ( ~250GB of data), on Dell PowerEdge 720 box with …

I have a fairly lightly loaded MySQL server with a few tables that are updated every five minutes. Other than these updates, there are very few queries run against the database. The data is queried just a few times per month. Ever so often, one of the more complicated queries will result in the process getting hung in the "copying to tmp table" state. To be honest, the queries that get hung aren't even that complicated. Usually there's one or two joins, a GROUP BY, and an ORDER BY.

Percona is glad to announce the release of Percona Server 5.1.65-14.0 on September 4th, 2012 (Downloads are available here and from the Percona Software Repositories).

Based on MySQL 5.1.65, including all the bug fixes in it, Percona Server 5.1.65-14.0 is now the current stable release in the 5.1 series. All of Percona‘s software is open-source and free, all the details of the release can be found in the 5.1.65-14.0 milestone at Launchpad.

New Features:

• Percona …

We’re delighted to share the news that our friends at MariaDB today announced the availability of MariaDB Galera Cluster!

We’ve been talking a good bit about MariaDB in the past few months and it’s great to see the MariaDB & Codership partnership result in today’s announcement.

read more

Today we’re pleased to announce the availability of MariaDB Galera Cluster!

With this release, we’re addressing the numerous requests we’ve received over the past few months for a MariaDB-based Galera Cluster. MariaDB, the more reliable, performant, feature-complete & backwards compatible MySQL database becomes even more attractive by making it available for Galera Cluster.

What is it?

• A straight merge of MariaDB 5.5.25 with Galera Cluster by Codership
• An alpha release which should not be used in production environments
• Using the Galera replication methodology, users get:
  • Synchronous, multi-master replication with guaranteed data consistency
  • This solution provides both read & write scalability

…

Over this summer I've observed several very curious MySQL performance issues, but time was short to blog about (and vacation period is not motivating to it either ;-))

But here is a one of them I'd like to share.

The following graphs are representing a MySQL test case running on 12cores Linux box from the same workload: simple Sysbench OLTP_RO benchmark with a growing number of concurrent users (150, 200, 250, 300, 350):
the only difference here that in the fist case (the left part of the graph) MySQL performance is dropping down since 300 concurrent users, while in the second case it's fixed and remaining stable as expected (well, in reality the issue came up since 1024 concurrent users, but I've forced the config params to bring it earlier and to simplify explanation ;-)

Once again, MySQL Performance Schema (PFS) is helping here to fire the hottest contention in this workload:

In …

MySQL performance can be increased in two ways, software optimization and hardware upgrades. While the previous articles have covered much of the software side of performance optimization, we are now going to focus on the hardware aspect. Does hardware help boost performance? Like software optimization, hardware upgrades for MySQL systems are based upon set goals for an organisation. The question is not what hardware would work best; rather a question of what hardware will help the organisation achieve an X goal. The answer is yes, hardware does boost performance, but there are a few caveats to this. There are cases such as organizations’ demands are fulfilled even with crappy hardware, and all they require is a performance boost on the software side. However, an organisation might have a goal plan for a major performance increase down the road, and …

Inspiration for this post is courtesy of a friend and former colleague of mine, Greg Youngblood, who pinged me last week with an interesting MySQL puzzle. He was running Percona Server 5.5.21 with a table structure that looks something like this:

CREATE TABLE foo (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 uid INT UNSIGNED NOT NULL,
 update_time DATETIME NOT NULL,
 ....
 INDEX `uid` (uid, update_time),
 INDEX `bar` (some_other_columns)
 ....
) ENGINE=InnoDB;

When he ran this query:

SELECT MIN(update_time) FROM foo WHERE update_time IS NOT NULL AND update_time <> '0000-00-00 00:00:00';

The result came back as 2012-06-22 10:28:16. However, when he ran a slightly different query:

SELECT MIN(t.update_time) FROM (SELECT uid, MIN(update_time) AS "update_time" FROM foo WHERE update_time IS NOT NULL AND update_time <> '0000-00-00 00:00:00' GROUP BY 1) t;

The answer which came …

Been a while since I wrote about replication work we did. Fake changes based approach was huge success, and now our prefetching has lots of coverage, where standard SELECTs cannot reach. We’re running our systems at replication pressure, where not running faker immediately results in replication lag. On busier machines Python implementation started using quite some CPU and ended up occasionally hitting GIL issues.

So, here’s the straightforward rewrite of fake changes replication prefetcher, faker. It can run 100k statements a sec, if needed. To get it, you can run:

bzr co lp:mysqlatfacebook/tools; cd faker

…

This post aims to provide solution for the common issues which I faced and found other people (on internet posts) also facing. I will keep on updating issues and their resolutions as and when I find them.

MySQL 5.1

Issue 1:
After compilation from source and installation. If you try to start MySQL Server and see following error :

mayank@mayank-pc:~/mysql-bin$ ./libexec/mysqld --vardir=.
120904 14:27:05 [Warning] Can't create test file /home/mayank/mysql-bin/var/mayank-pc.lower-test
120904 14:27:05 [Warning] Can't create test file /home/mayank/mysql-bin/var/mayank-pc.lower-test
./libexec/mysqld: Can't change dir to '$INSTALL_DIR/var/' (Errcode: 2)
120904 14:27:05 [ERROR] Aborting

120904 14:27:05 [Note] ./libexec/mysqld: Shutdown complete

Resolution :
MySQL is not able to find the data directory as …