Peter ZaitsevHigh availability using MySQL in the cloud (4.6.2015, 07:00 UTC)

Percona MySQL webinarsNext Wednesday (June 10) I’ll be co-presenting a webinar on using MySQL in the cloud for High Availability (HA). Joining me will be 451 Research analyst Jason Stamper and together we’ll talk about the realities of HA using MySQL in the cloud and how vendors are responding to changing application requirements with new developments that can enhance your deployment.

We’ll also present a comparison of available solutions along with key best practices you can follow for successfully attaining HA in the cloud with MySQL. The webinar is scheduled for June 10 at 10 a.m. Pacific. Register here.

Together we’ll cover:

  • What do HA MySQL deployments in the cloud look like today?
  • What are the developing requirements of applications based on future growth and scalability needs?
  • How are key vendors responding to these needs with new features and solution offerings, including those from OpenStack, Amazon, and others?
  • A high level, more technical comparison of the solutions
  • Keys to a successful HA MySQL deployment, including scaling from a single-node application to a cluster of MySQL instances

At the end of this webinar, you will have a good understanding of the options available for High Availability using MySQL in the cloud and how your current HA MySQL deployment in the cloud compares. You’ll also learn the tradeoffs you face depending on your HA solution and be able to identify which vendors and technologies are best suited for your needs.

This webinar, as usual, is free. Register now to reserve your spot and I hope to see you next Wednesday!

The post High availability using MySQL in the cloud appeared first on MySQL Performance Blog.

Jean-Jerome SchmidtBecome a DBA blog series - Monitoring and Trending (4.6.2015, 04:46 UTC)

So, you’ve been working with MySQL for a while and now are being asked to manage it. Perhaps your primary job description is not about support and maintenance of the company’s databases (and data!), but now you’re expected to properly maintain one or more MySQL instances. It is not uncommon that developers, or network/system administrators, or DevOps folks with general backgrounds, find themselves in this role at some point in their career. 

So, what does a DBA do? We know that a DBA manages the company’s databases, what does that mean? In this series of posts, we’ll walk you through the daily database operations that a DBA does (or at least ought to!).

We plan on covering the following topics, but do let us know if we’ve missed something:

  • Monitoring tools
  • Trending
  • Periodical healthchecks
  • Backup handling
  • High Availability
  • Common operations (online schema change, rolling upgrades, query review, database migration, performance tuning)
  • Troubleshooting
  • Recovery and repair
  • anything else?

In today’s post, we’ll cover monitoring and trending.

Monitoring and Trending

To manage your databases, you would need good visibility into what is going on. Remember that if a database is not available or not performing, you will be the one under pressure so you want to know what is going on. If there is no monitoring and trending system available, this should be the highest priority. Why? Let’s start by defining ‘trending’ and ‘monitoring’. 

A monitoring system is a tool that keeps an eye on the database servers and alerts you if something is not right, e.g., a database is offline or the number of connections crossed some defined threshold. In such case, the monitoring system will send a notification in some defined way. Such systems are crucial because, obviously, you want to be the first to be informed if something’s not right with the database.

On the other hand, a trending system will be your window to the database internals. It will provide you with graphs that show you how those cogwheels are working in the system - the number of queries per second, how many read/write operations the database does on different levels, are table locks immediate or do queries have to wait for them, how often a temporary table is created, how often it is created on disk, and so on. If you are familiar with MySQL internals, you’ll be better equipped to analyze the graphs and derive useful information. Else, you may need some time to understand these graphs. Some metrics are pretty self-explanatory, others perhaps not so obvious. But in general, it’s probably better to have more data than not to have any when it’s needed.

Data is presented as graphs for better visibility - from graphs, the human mind can easily derive trends and locate anomalies. The trending system also gives you an idea of how things change over time - you need this visibility in both real time and for historical data, as things happen also when people sleep. If you have been on-call in an ops team, it is not unusual for an issue to have disappeared by the time you get paged at 3am, wake up, and log into the system.

Monitoring - best practices

There are many many monitoring solutions out there, chances are you probably have one of the following options already in your infrastructure: 




Truncated by Planet PHP, read more at the original (another 15700 bytes)

Peter ZaitsevPercona XtraDB Cluster 5.6.24-25.11 is now available (3.6.2015, 20:19 UTC)

Percona XtraDB Cluster 5.6.24-25.11Percona is glad to announce the new release of Percona XtraDB Cluster 5.6 on June 3rd 2015. Binaries are available from downloads area or from our software repositories.

Based on Percona Server 5.6.24-72.2 including all the bug fixes in it, Galera Replicator 3.11, and on Codership wsrep API 25.11, Percona XtraDB Cluster 5.6.24-25.11 is now the current General Availability release. All of Percona’s software is open-source and free, and all the details of the release can be found in the 5.6.24-25.11 milestone at Launchpad.

New Features:

  • Percona XtraDB Cluster now allows reads in non-primary state by introducing a new session variable wsrep_dirty_reads. This variable is boolean and is OFF by default. When set to ON, a Percona XtraDB Cluster node accepts queries that only read, but not modify data even if the node is in the non-PRIM state (#1407770).
  • Percona XtraDB Cluster now allows queries against INFORMATION_SCHEMA and PERFORMANCE_SCHEMA even with variables wsrep_ready and wsrep_dirty_reads set to OFF. This allows monitoring applications to monitor the node when it is even in non-PRIM state (#1409618).
  • wsrep_replicate_myisam variable is now both global and session variable (#1280280).
  • Percona XtraDB Cluster now uses getifaddrs for node address detection (#1252700).
  • Percona XtraDB Cluster has implemented two new status variables: wsrep_cert_bucket_count and wsrep_gcache_pool_size for better instrumentation of galera memory usage. Variable wsrep_cert_bucket_count shows the number of cells in the certification index hash-table and variable wsrep_gcache_pool_size shows the size of the page pool and/or dynamic memory allocated for gcache (in bytes).

Bugs Fixed:

  • Using concurrent REPLACE, LOAD DATA REPLACE or INSERT ON DUPLICATE KEY UPDATE statements in the READ COMMITTED isolation level or with the innodb_locks_unsafe_for_binlog option enabled could lead to a unique-key constraint violation. Bug fixed #1308016.
  • Using the Rolling Schema Upgrade as a schema upgrade method due to conflict with

Truncated by Planet PHP, read more at the original (another 4880 bytes)

Jean-Jerome SchmidtNew Features Webinar: ClusterControl 1.2.10 - Fully Programmable DevOps Platform - Live Demo (3.6.2015, 14:11 UTC)

Following the release of ClusterControl 1.2.10 a week ago, we are excited to demonstrate this latest version of the product on Tuesday next week, June 9th.

Join our CTO, Johan Andersson, who will be discussing and demonstrating the new ClusterControl DSL, Integrated Developer Studio and Database Advisors, which are some of the cool new features we’ve introduced with ClusterControl 1.2.10.

New Features Webinar: ClusterControl 1.2.10


Tuesday, June 9th at 09:00 (UK) / 10:00 CEST (Germany, France, Sweden)
Register Now

North America/LatAm
Tuesday, June 9th at 09:00 Pacific Time (US) / 12:00 Eastern Time (US)
Register Now



Johan Andersson, CTO, Severalnines

Highlights of ClusterControl 1.2.10 include:

  • ClusterControl DSL (Domain Specific Language)
  • Integrated Developer Studio (Developer IDE)
  • Database Advisors/JS bundle
  • On-premise Deployment of MySQL / MariaDB Galera Cluster (New implementation)
  • Detection of long running and deadlocked transactions (Galera)
  • Detection of most advanced (last committed) node in case of cluster failure (Galera)
  • Registration of manually added nodes with ClusterControl
  • Failover and Slave Promotion in MySQL 5.6 Replication setups
  • General front-end optimizations

For additional details about the release:

Join us for this live webinar, where we’ll be discussing and demonstrating the latest features of ClusterControl!

We look forward to “seeing” you there and to insightful discussions!

If you have any questions or would like a personalised live demo, please do contact us.



Setting up, maintaining and operating a database cluster can be tricky. ClusterControl gives you the power to deploy, manage, monitor and scale entire clusters efficiently and reliably. ClusterControl supports a variety of MySQL-based clusters (Galera, NDB, 5.6 Replication), MariaDB as well as MongoDB/TokuMX-based clusters and Postgres. With over 7,000 users to date, ClusterControl is the leading, platform independent automation and management solution for the MySQ

Truncated by Planet PHP, read more at the original (another 3034 bytes)

Peter ZaitsevOptimizing Percona XtraDB Cluster for write hotspots (3.6.2015, 13:50 UTC)

Some applications have a heavy write workload on a few records – for instance when incrementing a global counter: this is called a write hotspot. Because you cannot update the same row simultaneously from multiple threads, this can lead to performance degradation. When using Percona XtraDB Cluster (PXC), some users try to solve this specific issue by writing on multiple nodes at the same time. Good idea or bad idea? Read on!

Simultaneous writes on a standalone InnoDB server

Say you have these 3 transactions being run simultaneously (id is the primary key of the table):

# T1
UPDATE t SET ... WHERE id = 100
# T2
UPDATE t SET ... WHERE id = 100
# T3
UPDATE t SET ... WHERE id = 101

All transactions will require a row lock on the record they want to modify. So T3 can commit at the same time than T1 and/or T2, because it will not lock the same record as T1 and T2.

But T1 and T2 cannot execute simultaneously because they need to set a lock on the same record. Let’s assume T2 is executed by InnoDB first, how long does T1 need to wait? It is essentially the time needed for T2 to execute.

Simultaneous writes on multiple nodes (PXC)

Now is it any different if you have a 3-node PXC cluster and if you want to run T1 on node1 on T2 on node2? Let’s review step by step how the cluster will execute these queries:

1. T1 starts executing on node1: a row lock is set on the record where id=100. T2 also starts executing on node2 and also sets a row lock on the record where id=100. How is it possible that 2 transactions set the same lock on the same record? Remember that locking in Galera is optimistic, meaning that a transaction can only set locks on the node where it is executing, but never on the remote nodes: here, T1 sets a lock on node1 and T2 sets a lock on node2.


2. Let’s assume T2 reaches commit before T1. T2 is then synchronously replicated on all nodes and it gets a global sequence number. At this point, a certification test is run on node2 to determine whether there is any write conflicts between T2 and other “in-flight” transactions in the cluster. Go to the next section if you want some clarification about “in-flight” transactions and the certification test.

Back to our transactions: T2 is the first transaction to try to commit, so no other transaction is “in-flight”: the certification test will succeed and InnoDB will be able to apply the transaction on node1. On node2, the same certification test will be run and T2 will be put in the apply queue (and it will be applied at some point in the near future).


Ok, wait a minute! No other transaction is “in-flight”, really? What about T1 on node1? Actually T1 is simply a local transaction on node1 and it is not known by the cluster yet. Therefore it is not what I called an “in-flight” transaction and it does not play any role in the certification test.

3. Now T1 reaches commit on node1. It is then synchronously replicated to all nodes and a certification test will run on node1. If T1 and T2 did commit simultaneously, there is a good chance that when T1 starts committing, T2 is still in the apply queue of node1. In this case, the certification test for T1 will fail. Why? To make sure that T2 will commit on all nodes no matter what, any other transaction that wants to set a lock on the record where id=100 will be rejected.

Then if the certification test for T1 fails, T1 is rolled back. The only option to commit T1 is to retry executing it.


Let’s assume that this second try is successful, how long did T1 have to wait before being executing? Essentially we had to execute T1 twice so we had to replicate it twice, each replication taking 1 network RTT, we had to roll T1 back on node1 (rollback is expensive in InnoDB) and the application had to decide that T1 had to be executed a second time. That is a lot more work and wait compared to the scenario on a single server.

So where is the fundamental problem when we tried to write on several nodes? Galera

Truncated by Planet PHP, read more at the original (another 2471 bytes)

Peter ZaitsevIs 80% of RAM how you should tune your innodb_buffer_pool_size? (2.6.2015, 10:00 UTC)

It seems these days if anyone knows anything about tuning InnoDB, it’s that you MUST tune your innodb_buffer_pool_size to 80% of your physical memory. This is such prolific tuning advice, it seems engrained in many a DBA’s mind.  The MySQL manual to this day refers to this rule, so who can blame the DBA?  The question is: does it makes sense?

What uses the memory on your server?

Before we question such advice, let’s consider what can take up RAM in a typical MySQL server in their broad categories.  This list isn’t necessarily complete, but I think it outlines the large areas a MySQL server could consume memory.

  • OS Usage: Kernel, running processes, filesystem cache, etc.
  • MySQL fixed usage: query cache, InnoDB buffer pool size, mysqld rss, etc.
  • MySQL workload based usage: connections, per-query buffers (join buffer, sort buffer, etc.)
  • MySQL replication usage:  binary log cache, replication connections, Galera gcache and cert index, etc.
  • Any other services on the same server: Web server, caching server, cronjobs, etc.

There’s no question that for tuning InnoDB, the innodb_buffer_pool_size is the most important variable.  It’s expected to occupy most of the RAM on a dedicated MySQL/Innodb server, but of course other local services may affect how it is tuned.  If it (and other memory consumption on the server) is too large, swapping can kick in and degrade your performance rapidly.

Further, the workload of the MySQL server itself may cause a lot of variation.  Does the server have a lot of open connections and active query workload consuming memory?  The memory consumption caused by this can be dramatically different server to server.

Finally, replication mechanisms like Galera have their own memory usage pattern and can require some adjustments to your buffer pool.

We can see clearly that the 80% rule isn’t as nuanced as reality.

A rule of thumb

However, for the sake of argument, let’s say the 80% rule is a starting point.  A rule of thumb to help us get a quick tuning number to get the server running.  Assuming we don’t know anything really about the workload on the system yet, but we know that the system is dedicated to InnoDB, how might our 80% rule play out?

Total Server RAM Buffer pool with 80% rule Remaining RAM
1G 800MB 200MB
16G 13G 3G
32G 26G 6G
64G 51G 13G
128G 102G 26G
256G 205G 51G
512G 409G 103G
1024G 819G 205G

At lower numbers, our 80% rule looks pretty reasonable.  However, as we get into large servers, it starts to seem less sane.  For the rule to hold true, it must mean that workload memory consumption increases in proportion to needed size of the buffer pool, but that usually isn’t the case.  Our server that has 1TB of RAM likely doesn’t need 205G of that to handle things like connections and queries (likely MySQL couldn’t handle that many active connections and queries anyway).

So, if you really just spent all that money on a beefy server do you really want to pay a 20% tax on that resource because of this rule of thumb?

The origins of the rule

At one of my first MySQL conferences, probably around 2006-2007 when I worked at Yahoo, I attended an InnoDB tuning talk hosted by Heikki Tuuri (the original author of InnoDB) and Peter Zaitsev.  I distinctly remember asking about the 80% rule because at the time Yahoo had some beefy 64G servers and the rule wasn’t sitting right with me.

Heikki’s answer stuck with me.  He said something to the effect of (not a direct quote): “Well, the server I was testing on had 1GB of RAM and 80% seemed about right”.  He then, if memory serves, clarified it and said it would not apply similarly to larger servers.

How should you tune?

80% is maybe a great start and rule of thumb.  You do want to be sure the server has plenty of free RAM for the OS and the usually unknown workload.  However, as we can see above, the larger the server, the more likely the rule will wind up wasting RAM.   I think for most people it starts and ends at the rule of thumb, mostly because changing the InnoDB buffer pool requires a restart in current releases.

So what’s a better rule of thumb?  My rule is that you tune the innodb_buffer_pool_size as large as possible without using

Truncated by Planet PHP, read more at the original (another 1028 bytes)

Chris CalenderMySQL 5.5.44 Overview and Highlights (1.6.2015, 23:06 UTC)

MySQL 5.5.44 was recently released (it is the latest MySQL 5.5, is GA), and is available for download here:

This release, similar to the last 5.5 release, is mostly uneventful.

There were 0 “Functionality Added or Changed” items this time, and just 15 overall bugs fixed.

Out of the 15 bugs, there were 5 InnoDB bugs (1 of which also spans partitioning), 1 security-related bug, 1 performance-related, and 3 additional potential crashing bugs. Here are the ones worth noting:

  • InnoDB: An assertion was raised on shutdown due to XA PREPARE transactions holding explicit locks.
  • InnoDB: Removal of a foreign key object from the data dictionary cache during error handling caused the server to exit.
  • InnoDB: SHOW ENGINE INNODB STATUS output showed negative reservation and signal count values due to a counter overflow error.
  • InnoDB: Estimates that were too low for the size of merge chunks in the result sorting algorithm caused a server exit.
  • InnoDB; Partitioning: The CREATE_TIME column of the INFORMATION_SCHEMA.TABLES table now shows the correct table creation time for partitioned InnoDB tables. The CREATE_TIME column of the INFORMATION_SCHEMA.PARTITIONS table now shows the correct partition creation time for a partition of partitioned InnoDB tables. The UPDATE_TIME column of the INFORMATION_SCHEMA.TABLES table now shows when a partitioned InnoDB table was last updated by an INSERT, DELETE, or UPDATE. The UPDATE_TIME column of the INFORMATION_SCHEMA.PARTITIONS table now shows when a partition of a partitioned InnoDB table was last updated. (Bug #69990)
  • Security-related: A user with a name of event_scheduler could view the Event Scheduler process list without the PROCESS privilege.
  • Performance-related: Certain queries for the INFORMATION_SCHEMA TABLES and COLUMNS tables could lead to excessive memory use when there were large numbers of empty InnoDB tables. (Bug #72322)
  • Crashing Bug: SHOW VARIABLES mutexes were being locked twice, resulting in a server exit.
  • Crashing Bug: Under certain conditions, the libedit command-line library could write outside an array boundary and cause a client program crash.
  • Crashing Bug: For a prepared statement with an ORDER BY that refers by column number to a GROUP_CONCAT() expression that has an outer reference, repeated statement execution could cause a server exit.

I don’t think I’d call any of these urgent for all, but if running 5.5, especially if not a very recent 5.5, you should consider upgrading.

For reference, the full 5.5.44 changelog can be viewed here:

Hope this helps.

Peter ZaitsevPercona TokuMXse 3.0.3-1.0-rc6 is now available (1.6.2015, 17:54 UTC)

Percona TokuMXse Percona is glad to announce the release of Percona TokuMXse 3.0.3-1.0rc6 on June 1st 2015. TokuMXse is a TokuMX storage engine for MongoDB 3.0.3. Downloads are available from our download site here. Packages for this Release Candidate release, will be available in our Ubuntu Trusty, Utopic, Vivid, and Debian Jessie apt testing and CentOS 7 yum testing repositories.

Based on MongoDB 3.0.3, including all the bug fixes in it, Percona TokuMXse 3.0.3-1.0 is the current release candidate.

This release contains minor changes to the Fractal Tree, including:

  • Improved tokuftdump information.
  • Fixed sporadic recovery issue due to rare race between transaction rollback and logging.
  • Report capped boolean for uncapped collections when using tokuft storage engine.
Getting started

After installation, you can start mongod with tokuft as the storage engine, with:

$ mongod --storageEngine tokuft

Note: Transparent huge pages must be turned off for the fractal tree engine to work properly. If you attempt to run mongod with that option enabled, an error informing you of this will be printed to the output of the mongod process and it will fail to start.

You can check if the Transparent huge pages are enabled with:

$ cat /sys/kernel/mm/transparent_hugepage/enabled

[always] madvise never

You can disable the transparent huge pages by running the following command as root (NOTE: Setting this will last only until the server is rebooted):

echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

Percona TokuMXse currently supports various tuning parameters to pass to the mongod instance. Full list can be read by running mongod --help (this will print options for all available storage engines, including tokuft).

The post Percona TokuMXse 3.0.3-1.0-rc6 is now available appeared first on MySQL Performance Blog.

Chris CalenderMariaDB 10.1.4 Overview and Highlights (1.6.2015, 17:48 UTC)

MariaDB 10.1.4 was recently released, and is available for download here:

This is the 2nd beta, and 5th overall, release of MariaDB 10.1. Now that it is beta, there were not as many major changes in this release (compared to 10.1.3), but there were a few notable items as well as many overall bugs fixed (I counted 367).

Since it’s beta, I’ll only cover the major changes and additions, and omit covering general bug fixes (feel free to browse them all here).

To me, these are the highlights:

Of course it goes without saying that do not use this for production systems as it is only the 2nd beta release of 10.1. However, I definitely recommend installing it on a test server and testing it out. And if you happen to be running a previous version of 10.1, then you should definitely upgrade to this latest release.

You can read more about the 10.1.4 release here:

And if interested, you can review the full list of changes in 10.1.4 (changelogs) here:

Hope this helps.

Peter ZaitsevNew PERFORMANCE_SCHEMA defaults in MySQL 5.7.7 (1.6.2015, 14:27 UTC)

I thought it was worth a moment to reiterate on the new Performance Schema related defaults that MySQL 5.7.7 brings to the table, for various reasons.

For one, most of you might have noticed that profiling was marked as deprecated in MySQL 5.6.7. So it is expected that you invest into learning more about Performance Schema (and Mark’s sys schema!).

Second, there are lots of virtual environments and appliances out there running Community Edition MySQL where Performance Schema can be a useful tool for analyzing performance. Thus, expect to see more articles about using PERFORMANCE_SCHEMA and SYS_SCHEMA from us!

Third, we have more and more junior readers who might benefit from light reads such as this. :)

The new defaults that I wanted to highlight are mentioned in the MySQL 5.7.7 release notes:
– The MySQL sys schema is now installed by default during data directory installation.
– The events_statements_history and events_transactions_history consumers now are enabled by default.

Note that if you are upgrading from an earlier version of MySQL to 5.7.7 to get these goodies you will need to run mysql_upgrade and restart the database for the above changes to take effect.

So what do these mean?

If you haven’t had a chance to dig into PERFORMANCE_SCHEMA, check out the quick start guide here. PERFORMANCE_SCHEMA is a nify tool (implemented as a union of a storage engine and a schema in MySQL) to monitor MySQL server execution at a low level, with a focus on performance metrics. It monitors for events that have been “instrumented”, such as function calls, OS wait times, synchronization calls, etc. With performance nomenclature “instruments” are essentially “probes”. The events that the instruments generate can be processed by consumers. Note that not all instruments or consumers are enabled by default.

Some would say that the structure of PERFORMANCE_SCHEMA may be complex and may not be very DBA-friendly. This is what led to the birth of SYS_SCHEMA. For those who are not familiar with Mark Leith’s SYS_SCHEMA and prefer TL;DR – it provides human friendly views, functions and procedures that can help you analyze database usage using PERFORMANCE_SCHEMA. If you haven’t had a chance to check it out yet you might want to read Miguel’s article on using the sys schema or Alexander Rubin’s article about using it in multitenant environments and give it a spin!

I welcome the fact that events_statements_history and events_transactions_history consumers are enabled by default in MySQL 5.7.7 as it means that we get some handy performance details available to us out of the box in vanilla MySQL. Note that these are per-thread tables and by default the history length (the length of the number of entries present; more on those variables here) is automatically sized, thus you may need to increase them.

What details do you get off the bat with them?

Consider the following example:

mysql> select * from performance_schema.events_statements_history where event_id=353G
*************************** 1. row ***************************
              THREAD_ID: 20
               EVENT_ID: 353
           END_EVENT_ID: 456
             EVENT_NAME: statement/sql/select
            TIMER_START: 1818042501405000
              TIMER_END: 1818043715449000
             TIMER_WAIT: 1214044000
              LOCK_TIME: 67000000
               SQL_TEXT: select * from imdb.title limit 100
                 DIGEST: ec93c38ab021107c2160259ddee31faa
            DIGEST_TEXT: SELECT * FROM `imdb` . `title` LIMIT ?
         CURRENT_SCHEMA: performance_schema
            OBJECT_TYPE: NULL
            OBJECT_NAME: NULL

Truncated by Planet PHP, read more at the original (another 1743 bytes)

LinksRSS 0.92   RDF 1.
Atom Feed