This is the second part of the MySQL 5.6 Performance Notes started from here .

Now it's about performance results on a full dbSTRESS test workload scenarios..

Full Series of tests @dbSTRESS Test scenario :

• Workload: Read-Only, Read+Update, Read+Write (Writes=Delete, Insert, Update)
• Configurations: 16, 24, 32, 48 cores
• Concurrent users: 1, 2, 4, 8.. 1024 (4min on each step, growing load)
• Think time: 0 sec

• Points of interest:

  • identify current and potential contentions
  • try to reduce contentions by limiting thread concurrency via CPU taskset (binding "mysqld" process to a fixed list of cores)

Read-Only :

…

As Calvin mentioned in “NoSQL to InnoDB with Memcached“, we just released a “technology preview” of the feature that makes memcached a MySQL Daemon Plugin. And this “technology preview” release demonstrates how user can go around SQL Optimizer and Query Processing and directly interact with InnoDB Storage Engine through InnoDB APIs. Here, we would like to walk with you step by step to see how to get the memcached Daemon Plugin set up and get it running.

If you would just like to get a brief introduction on the setup steps, there is a “README-innodb_memcached” in the mysql-5.6.2-labs-innodb-memcached package. This is a more elaborated description on these steps.

1) Prerequisite:

Currently, the Memcached Daemon Plugin prototype is only supported on Linux platform. And as a prerequisite, you must …

In preparation for the talk I’m giving tomorrow, I was assembling a slide that looks back about 10 years ago and remembering what our database infrastructure was like back then. I was at Yahoo! during that time and our leading-edge MySQL deployments were on machines that just under 1GHz processors running a 32bit operating system, with relatively small, slow, and expensive disks (no SSDs!).

Back then MySQL 3.23 was the norm, though brave folks like us were running MySQL 4.0 beta releases in production to take advantage of the new and improved replication. Remember when simply having replication was a big deal?!

And InnoDB was really, really new–not for the faint of heart.

Oh, how times have changed.

Nowadays, I could probably simulate our old Yahoo! Finance “feeds” infrastructure on my little Thinkpad …

Index Condition Pushdown (ICP) is one of the new optimizer features in the MySQL 5.6.2 milestone release. The goal with Index Condition Pushdown is to move as much as possible of the processing of conditions (mainly WHERE clauses) from the server to the storage engine. Instead of fetching entire rows into the server and then evaluate the conditions in the server, the optimizer "pushes" the parts of condition that can be evaluated using the index down to the storage engine. This gives the storage engine the possibility to filter out non-relevant rows using the index and only return relevant rows to the server. The result of using ICP should be less IO accesses to the base table and fewer accesses from the MySQL server to the storage engine.

To explain how Index Condition Pushdown works let us first look at how retrieval of records based on using an index is done without use of Index Condition Pushdown. The main operations that the …

Download PDF Presentation

The most common mistakes are easy to avoid however many startups continue to fall prey, with the impact including large re-design costs, delays in new feature releases, lower staff productivity and less then ideal ROI. All growing and successful sites need to achieve higher Availability, seamless Scalability and proven Resilience. Know the right MySQL environment to provide a suitable architecture and application design to support these essential needs.

• The different types of accessible data (e.g. R/W, R, none)
• What limits MySQL availability (e.g software upgrades, blocking statements, locking etc)
• The three components of scalability – Read Scalability/Write …

I was lucky to discover MySQL 5.6 features ahead and here are my notes about performance improvements and pending issues observed on my benchmark workloads.. The story finally was very passionate and that's why very long for a one blog post :-)) so I've decided to split it into several parts and will publish all of them over a day..

Here is the first part, Discovery of MySQL 5.6.2 as it..

The new MySQL 5.6 version brings many "fundamental" improvement within InnoDB code:

• first of all a split of the "kernel_mutex" was introduced! - it was one of the most hot contentions observed in MySQL 5.5 and limited InnoDB performance in so many cases..
• purge processing became multi threaded! - so we may expect to not see purge lagging anymore or reduce it considerably on heavy writes..
• page cleaning and flushing activity was removed from the Master Thread and assigned to the Page Cleaner (dedicated …

Read the detailed replication tutorial instead.


This tutorial shows how you can use the Severalnines Configurator for MySQL Replication to deploy a Multi-master replication setup, and install ClusterControl.

When the deployment is finished you have a set of tools to manage and monitor replication, as well as to add new slaves, and to perform failover.
You can choose:

• if you wish also setup bi-directional replication between the masters
• the number of …

MySQL is the most popular open source SQL database. The ever-increasing performance demands of web-based services have generated significant interest in providing NoSQL access methods to MySQL. Today, MySQL is announcing the preview of the NoSQL to InnoDB via memcached. This offering provides users with the best of both worlds – maintain all of the advantages of rich SQL query language, while providing better performance for simple queries via direct access to shared data.

In this preview release, memcached is implemented as a MySQL plugin daemon, accessing InnoDB directly via the native InnoDB API:

Features provided in the current release:

• Memcached as a daemon plugin of mysqld: both mysqld and memcached are running in the same process space, with very low latency access to data
…

Here are some interesting replication related talks that my team will deliver:

• "Boosting MySQL Replication Performance Through Multi-Threaded Slave", by Andrei Elkin (April 11th at 11:45AM).

• "MySQL Replication", by Lars Thalmann (April 11th at 2:30 PM).

• "Introduction to MySQL Replication", by Luis Soares (April 12th at 3:15 PM).

• "Sharding Techniques for MySQL", by Mats Kindahl (April 13th at 8:00 AM).

Come and join us... Have a great week at Collaborate 11!

Luís

Fig. 1 User privileges stored in MyISAM

The Developer Release for MySQL Cluster 7.2 includes a new feature that allows the system data required for checking user access to be shared amongst all of your MySQL Servers. By default all of the tables holding these credentials are stored in MyISAM and so are local to that MySQL Server.

This can become painful to manage – every time you want to create a new user or change their permissions you need to repeat it on every server, miss one out and the user won’t be able to access that server (or will still be able to access it after you withdraw their privileges).

This is illustrated in Fig.1 – The user “fred” is created on one MySQL Server but when Fred attempts to connect to one of the MySQL Servers they’re blocked. This maybe what you wanted to …