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 …

Two talks in the Dallas area this week. On Tuesday, the North Texas MySQL Users Group, a special interest group of the Dallas Oracle Users Group, is meeting and the subject will be MySQL 101. So please load MySQL and Workbench on a laptop (or try to) and we will go over the basics. RVP so we can get the right amount of pizza ordered!

Then on Thursday, I will present The Proper Care and Feeding of a MySQL database for Linux Administrators at the Dallas/Fort Worth Unix User Group meeting.

MySQL is a great database server. It has lots of flaws, but if you work with its strong points and try to minimize its weaknesses, it works fantastically well for a lot of use cases. Unfortunately, if you bang on its weak points, sometimes you get hit by falling bricks. A couple of its riskiest weak points relate to unavailability of an expected resource, particularly disk space and memory. For example, Stewart Smith has blogged about the interesting results you can get if you intentionally make malloc() fail.

I was thinking recently about what a DBA does, and decided to blog about what I think a DBA could/should do. Most DBAs I know are mired in day-to-day firefighting and time-consuming tedium. This forces them to operate in reactive mode (because they don’t have enough time to “get caught up”), and keeps them from more valuable things they could be doing. Here’s my short and incomplete list:

1. Working with the developers (programmers) to help architect upcoming projects. If the DBA leaves design to the developers, then suboptimal designs might be found after the fact. This often happens after deploying to production, where the design impacts the business. Without early input, the DBA also has no chance to assess and prepare for future needs.
2. Teaching developers how to work with the database. Many developers struggle to understand databases and SQL, and are unable to fully optimize the queries they write. They …

My three previous blog posts I already wrote from Froscon. In this post I still want to go back and mention some people I met and discussions I had.

The MySQL side

There were of course many MySQL people, with both SkySQL and Oracle sponsoring. It was great to meet Carsten from Oracle, who has joined the MySQL Sales Engineer team in Europe (he moved from an OpenOffice position). That's my former team, so it was great to see a new face!

Going there the person I was most looking forward to meet was Hana Hütter, formerly a MySQL account manager for Central Europe, and now doing the same at SkySQL. My first ever MySQL sales gig was with Hana, and Ralf Gebhart who is also now with SkySQL but was not at Froscon. While Ralf was there only that first time to teach me how to be a Sales Engineer, with Hana we then continued to sell MySQL into telecom companies in many European cities. I had not met Hana since I left Sun. It was …

In late 2011 I attended a lecture by John Wilkes on Google compute clusters, which link thousands of commodity computers into huge task processing systems.  At this scale hardware faults are common.  Google puts a lot of effort into making failures harmless by managing hardware efficiently and using fault-tolerant application programming models.  This is not just good for application up-time.  It also allows Google to operate on cheaper hardware with higher failure rates, hence offers a competitive advantage in data center operation.

It's becoming apparent we all have to think like Google to run applications successfully in the cloud.  At Continuent we run our IT and an increasing amount of QA and development on Amazon Web Services …

MySQL server introduced support for the IPv6 protocol in version 5.5 (WL#798). However after some time it turned out that the support is not as functional as it could be. In MySQL 5.6 we are trying to extend the functionality and improve the easiness of use.
Bind address in 5.5 Let's recall for a moment how the MySQL server handles client connections. There is only one server socket and it can be bound to any IP address. The IP address to bind the server socket to can be specified via the --bind-address command line option.

There are two main statements in the phrase above:

• any IP address can be assigned meaning both regular and special IP addresses:
  • addresses like 192.168.1.1 or 127.0.0.1, or ::1, or 2607:f0d0:1002:51::4 are regular -- every address …