I'm not the only one to have noticed this, but I spent a sufficient amount of time banging my head against a wall finding this out that I thought it important to make more people aware of this.

While trying to validate new database hardware we were seeing some serious performance issues in production.  Most MySQL benchmarks using sysbench or pt-playback couldn't reproduce it, but a simple sysbench 16 threaded filio test on the mysql partition showed about 1/3 the throughput we would expect.   The fact that much of the hardware was new as well as the OS we were using made tracking down the cause difficult (changing from CentOS 5.5 to Scientific Linux 6.)

Finally some of our ops people working on different systems started noticing similar issues, and they uncovered the XFS issue.  Sure enough -- when took existing hardware, upgraded to SL6 and ran the same sysbench filio test we immediately saw a …

Tuesday, February 5th was an important day in the MySQL ecosystem due to the release by Oracle of the long-awaited MySQL 5.6 GA. Many MySQL users have been looking forward to this release in order to benefit from the improved performance and scalability. Features such as online operations/schema changes, NoSQL access from memcached to InnoDB, multi threaded-replication and Global Transaction ID significantly improve the competitiveness of MySQL.

read more

UPDATE: Here’s the recording, enjoy!

Hello everybody!

#DBHangOps coming at you this week, Wednesday 1/30/12 at 12:00pm PST. Be sure to check out this blog post tomorrow or check this twitter search to grab the link to join the Google Hangout.

This week’s topics were:
* bug fixes in recent versions
* Plugins
** authorization plugins (e.g. LDAP)
** audit plugin API — http://dev.mysql.com/doc/refman/5.5/en/writing-audit-plugins.html
*** state transitions of records in MySQL with a plugin?
* InnoDB Status variables from twitter — https://github.com/twitter/mysql/wiki/InnoDB-Status-Variables
* Table alters/schema changes
** Twitter patch for non-blocking alter table (throws a different error than “lock_wait_timeout”) — …

With the MySQL 5.6 production-ready GA set for release in the coming days, it’s good to re-cap the key features that make 5.6 the best release of the database ever.  At a glance, MySQL 5.6 is simply a better MySQL with improvements that enhance every functional area of the database kernel, including:

• Improved Security for worry-free application deployments
• Better Performance and Scalability
  • Improved InnoDB storage engine for better transactional throughput
  • Improved Optimizer for better query execution times and diagnostics
• Better Application Availability with Online DDL/Schema changes
• Better Developer Agility with NoSQL Access with Memcached API to InnoDB
• Improved Replication for high performance, self-healing distributed deployments
• Improved Performance Schema for better instrumentation …

Update: Recording!

Finally, the end of year madness is over and we’re in a fresh new year. That must mean it’s time to get back into #DBHangops! Tune in on Wednesday, 1/16/13 at 5:00pm PST to get in on the fun and share your experiences.

Check back on this blog post tomorrow or check this twitter search to grab the link to join the Google Hangout.

Topics for this weeks hangout include:
* triggers: maintenance, problems, etc.
* Oracle MySQL utilities (similar to Percona toolkit)
** Slideshow of MySQL utilities
* Database caching and cache expiration
* InnoDB log file size
** …

The SkySQL and MariaDB Roadshow comes to Sweden:

Stockholm 7 February 2013, 16:30-19:30, Dramaten Restaurang Pauli

SkySQL and Monty Program are on the road with our joint - free - roadshow in Stockholm where Monty Widenius will unveil his vision of the future of the MySQL database via MariaDB.

In addition, we will have speakers from Codership/Galera, as well as SkySQL experts.

read more

451 Research’s 2013 Database survey is now live at http://bit.ly/451db13 investigating the current use of database technologies, including MySQL, NoSQL and NewSQL, as well as traditional relation and non-relational databases.

The aim of this survey is to identify trends in database usage, as well as changing attitudes to MySQL following its acquisition by Oracle, and the competitive dynamic between MySQL and other databases, including NoSQL and NewSQL technologies.

There are just 15 questions to answer, spread over five pages, and the entire survey should take less than ten minutes to complete.

All individual responses are of course confidential. The results will be published as part of a major research report due during Q2.

The full …

I discovered a feature (or bug) of MySQL timestamp fields. Maybe is documented in some place what I not read yet:

When I add a new timestamp field to a table, MySQL magically adds some features to new timestamp field like a “trigger” and a default value to CURRENT_TIMESTAMP.

There is test-case script:

-- CREATING TABLE AND INSERT SOME DUMMY DATA
mysql> CREATE TABLE t(
    -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> val VARCHAR(50)
    -> );
Query OK, 0 ROWS affected (0.15 sec)
 
mysql> INSERT INTO t (val) VALUES ("foo") ,("var");
Query OK, 2 ROWS affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM t;
+----+------+
| id | val  |
+----+------+
|  1 | foo  |
|  2 | var  |
+----+------+
2 ROWS IN SET (0.00 sec)
 
 
-- ADDING NEW TIMESTAMP FIELD AND ADD MORE DUMMY DATA
mysql> ALTER TABLE t ADD ts_field TIMESTAMP;
Query OK, 2 ROWS affected (0.35 sec)
Records: 2  Duplicates: 0 …

Global Transaction Identifiers (GTIDs) are one of the key replication enhancements in MySQL 5.6. GTIDs make it simple to track and compare replication across a master - slave topology. This enables:

- Much simpler recovery from failures of the master,

- Introduces great flexibility in the provisioning and on-going management of multi-tier or ring (circular) replication topologies.

A new on-demand MySQL 5.6 GTID webinar delivered by the replication engineering team is now available, providing deep insight into the design and implementation of GTIDs, and how they enable users to simplify MySQL scaling and HA. The webinar covers:

- Concepts: What is a GTID? How does the …

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