Percona XtraDB Cluster 5.6 GA release now available

Percona XtraDB Cluster 5.6Percona is pleased to announce the first General Availability release of the leading open source High Availability solution for MySQL, Percona XtraDB Cluster 5.6 on January 30, 2014. Binaries are available from downloads area or from our software repositories.

Percona XtraDB Cluster 5.6
Percona XtraDB Cluster 5.6 is an active/active cluster solution for High Availability (HA) MySQL that delivers performance and MySQL-based cluster management features available nowhere else. This free, open source solution combines Percona Server (a drop-in MySQL replacement), Galera, wsrep API, and Percona XtraBackup, in a single package that’s easier to deploy and manage than the various approaches to MySQL replication. Applications that require high availability will benefit from this combination of the latest versions of these software solutions to deliver cost-effective, high performance based on the latest features and fixes available in Percona Server 5.6, Galera 3, wsrep API 25, and Percona XtraBackup 2.1.

Percona XtraDB Cluster expert Jay Janssen will present a webinar titled “What’s New in Percona XtraDB Cluster 5.6” on February 5 at 1PM EST/10AM PST to talk about some of the new features and upgrade strategies and to answer your questions. Alexey Yurchenko, solutions architect from Codership, will join Jay to contribute additional insights on Galera and the Percona XtraDB Cluster solution.

Percona Server 5.6
As our previous performance analysis demonstrates, MySQL 5.6 was a big step forward from MySQL 5.5. However, Percona Server 5.6 yields significantly better results than MySQL 5.6 in IO-bound cases. The ThreadPool feature in Percona Server 5.6 delivers performance advantages over MySQL 5.6 with as few as 150 concurrent threads and up to 4x better performance as concurrent threads grow into the 1,000s. ThreadPool efficiency improvements in Percona Server 5.6 have even shown to deliver similar gains over MariaDB 10.0.7 in transactional workload scenarios.

Other Percona Server 5.6 features that benefit Percona XtraDB Cluster 5.6 include:

  • Expanded diagnostics capability which reveals new insights into user activity with Performance Schema and User Stats features as well as query activity with the Slow Query Log
  • Support for greater control of the end-user experience with the integration of the Statement Timeout feature from Twitter’s MySQL contributions

Galera 3
Galera from Codership provides synchronous replication for greater data consistency. The latest version, Galera 3, includes new features and fixes to minimize replication traffic between WAN segments, improved memory performance, greater data integrity, and improved data throughput.

Percona XtraBackup 2.1
Adding new nodes and recovering failed nodes is an important part of managing a high availability MySQL cluster. Percona XtraDB Cluster 5.6 now defaults to use Percona XtraBackup 2.1 to transfer node state for these processes. Though Galera supports other choices, Percona XtraBackup 2.1 is the best option because it:

  • Has the fastest State Snapshot Transfers (SST) with parallel, compressed, and compacted options
  • Is highly secure with industry standard AES multi-threaded data encryption available

Drop-in Compatibility
Percona XtraDB Cluster has Percona Server at its core so it is drop-in compatible with MySQL. As a result, upgrades to Percona XtraDB Cluster 5.6 are straightforward from a variety of platforms including:

  • Percona XtraDB Cluster 5.5
  • Percona Server 5.6
  • Percona Server 5.5
  • MySQL 5.6
  • MySQL 5.5
  • MariaDB 5.5

Follow our upgrade steps for moving to Percona XtraDB Cluster 5.6 with no downtime.

Additional Help
You can download the operations manual now at the Percona XtraDB Cluster page on percona.com. Community-based help is available for Percona XtraDB Cluster in the Percona Community Forums.

Commercial help with Percona XtraDB Cluster 5.5 and 5.6 is available from the Percona Consulting team. The Percona Support team offers optional coverage for Percona XtraDB Cluster. The Percona Remote DBA team can provide outsourced management of Percona XtraDB Cluster deployments.

All of Percona‘s software is open-source and free. Details of the release can be found in the 5.6.15-25.3 milestone at Launchpad and in the release notes.

New Features

  • New meta packages are now available in order to make the Percona XtraDB Cluster installation easier.
  • Debian/Ubuntu debug packages are now available for Galera and garbd.
  • xtrabackup-v2 SST now supports the GTID replication.

Bugs Fixed

  • Node would get stuck and required restart if DDL was performed after FLUSH TABLES WITH READ LOCK. Bug fixed #1265656.
  • Galera provider pause has been fixed to avoid potential deadlock with replicating threads.
  • Default value for variable binlog_format is now ROW. This is done so that Percona XtraDB Cluster is not started with wrong defaults leading to non-deterministic outcomes like crash. Bug fixed #1243228.
  • During the installation of percona-xtradb-cluster-garbd-3.x package, Debian tries to start it, but as the configuration is not set, it would fail to start and leave the installation in iF state. Bug fixed #1262171.
  • Runtime checks have been added for dynamic variables which are Galera incompatible. Bug fixed #1262188.
  • During the upgrade process, parallel applying could hit an unresolvable conflict when events were replicated from Percona XtraDB Cluster 5.5 to Percona XtraDB Cluster 5.6. Bug fixed #1267494.
  • xtrabackup-v2 is now used as default SST method. Bug fixed #1268837.
  • FLUSH TABLES WITH READ LOCK behavior on the same connection was changed to conform to MySQL behavior. Bug fixed #1269085.
  • Read-only detection has been added in clustercheck, which can be helpful during major upgrades (this is used by xinetd for HAProxy etc.) Bug fixed #1269469.
  • Binary log directory is now being cleanup as part of the XtraBackup SST. Bug fixed #1273368.
  • First connection would hang after changing the wsrep_cluster_address variable. Bug fixed #1022250.
  • When gmcast.listen_addr variable was set manually it did not allow nodes own address in gcomm address list. Bug fixed #1099478.
  • GCache file allocation could fail if file size was a multiple of page size. Bug fixed #1259952.
  • Group remerge after partitioning event has been fixed. Bug fixed #1232747.
  • Fixed multiple build bugs: #1262716, #1269063, #1269351, #1272723, #1272732, and #1253055.

Other bugs fixed: #1273101, #1272961, #1271264, and #1253055.

We did our best to eliminate bugs and problems during the testing release, but this is a software, so bugs are expected. If you encounter them, please report them to our bug tracking system.

The post Percona XtraDB Cluster 5.6 GA release now available appeared first on MySQL Performance Blog.


Percona Server: Thread Pool Improvements for Transactional Workloads

In a previous thread pool post, I mentioned that in Percona Server we used an open source implementation of MariaDB’s thread pool, and enhanced/improved it further. Below I would like to describe some of these improvements for transactional workloads.

When we were evaluating MariaDB’s thread pool implementation, we observed that it improves scalability for AUTOCOMMIT statements. However, it does not scale well with multi-statement transactions. The UPDATE_NO_KEY test which was run as an AUTOCOMMIT statement and inside a transaction gave the following results:


After analysis, we identified the major cause of that inefficiency: High latency between individual statements inside transactions. This looked pretty similar to the case when transactions are executed in parallel without thread pool. Latency there is high as well, though the cause of high latency in these two cases is different.

  • In the “one-thread-per-connection” case, with 1000 connections, higher latency is caused by increased contention on accessing MySQL server shared resources like structures/locks/etc.
  • In the case of “pool-of-threads”, 1000 client connections will be organized into thread_pool_size groups (or to be more specific into thread_pool_size queues), and latency here comes not from contention as we have much smaller number of parallel threads. It comes from the execution order of individual statements in transactions. Suppose that you have 100 identical transactions (each with 4 statements in it) in the thread group queue. As transactions are processed and executed sequentially, statements of transaction T1 will be placed at the following positions in the thread pool queue: 1…101…201…301…401. So in case of a uniform workload distances between statements in the transaction will be 100. This way transaction T1 may hold server resources during execution of all statements in the thread pool queue between 1 and 401, and that has a negative impact on performance.

In an ideal world, the number of concurrent transactions does not matter, as long as we keep the number of concurrent statements sufficiently low. Reality is different though. An open transaction which is not currently executing a statement may still block other connections by holding metadata or row-level locks. On top of that, any MVCC implementation should examine states of all open transactions and thus may perform less efficiently with large numbers of transactions (we blogged about InnoDB-specific problems here and here).

In order to help execute transactions as fast as possible we introduced high and low priority queues for thread pool. Now with default thread pool settings, we check every incoming statement, and if it is from an already started transaction we put it into the the high priority queue, otherwise it will go in the low priority queue.


Such reordering allowed to notably reduce latency and resulted in very good scalability up to a very high number of threads. You can find details about this optimization in our documentation.

Now let’s check how these introduced changes will affect the workload we mentioned earlier in this article.

The next graphs show the results for the UPDATE_NO_KEY test that was run as an AUTOCOMMIT statement, and inside a transaction for MariaDB with thread_pool and Percona Server with the thread_pool_high_priority mode=statements – which is very similar to behavior of thread_pool in MariaDB and Percona Server with thread_pool_high_priority mode=transactions – optimization that performs statements reordering of the transactions in the thread pool queue.

This works even more efficiently on larger transactions like OLTP_RW from sysbench. See the graphs below for the same set of servers:

IO bound: sysbench dataset 32 tables/12M rows each (~100GB), InnoDB buffer pool=25GB

As seen, we get nearly flat throughput with thread_pool_high_prio_mode=transactions even with very high numbers of users connections.

The post Percona Server: Thread Pool Improvements for Transactional Workloads appeared first on MySQL Performance Blog.


Talking Drupal #034 – Feed

Show Topics

  • Feeds exmplained
  • Feed Features
  • Uses of Feeds
  • Other modules


  • Feeds – https://drupal.org/project/feeds
  • Feeds ImageGrabber  – https://drupal.org/project/feeds_imagegrabber
  • Feeds Xpath Parser – https://drupal.org/project/feeds_xpathparser
  • Location Feeds – https://drupal.org/project/location_feeds
  • Field Collections – https://drupal.org/project/field_collection_feeds

Module of Week

  • ImageMagik – https://drupal.org/project/imagemagick


  • Stephen Cross – www.ParallaxInfoTech.com @stephencross
  • Jason Pamental – www.hwdesignco.com @jpamental
  • John Picozzi – www.RubicDesign.com @johnpicozzi 
  • Nic Laflin – www.nLightened.net @nicxvan
  • Kathy Beck –  www.RubicDesign.com @kbeck303



Talking Drupal #034 – Feed

Show Topics

  • Feeds exmplained
  • Feed Features
  • Uses of Feeds
  • Other modules


  • Feeds – https://drupal.org/project/feeds
  • Feeds ImageGrabber  – https://drupal.org/project/feeds_imagegrabber
  • Feeds Xpath Parser – https://drupal.org/project/feeds_xpathparser
  • Location Feeds – https://drupal.org/project/location_feeds
  • Field Collections – https://drupal.org/project/field_collection_feeds

Module of Week

  • ImageMagik – https://drupal.org/project/imagemagick


  • Stephen Cross – www.ParallaxInfoTech.com @stephencross
  • Jason Pamental – www.hwdesignco.com @jpamental
  • John Picozzi – www.RubicDesign.com @johnpicozzi 
  • Nic Laflin – www.nLightened.net @nicxvan
  • Kathy Beck –  www.RubicDesign.com @kbeck303



MySQL performance optimization: Don’t guess! Measure with Percona Cloud Tools

In our practice we often see that MySQL performance optimization is done in a sort of “black magic” way. A common opinion is: “If there is a performance problem – then it must be a database issue, because what else could it be? And if this is a database issue, then it must involve IO problems because the reason for a slow database is always a slow IO…”  Following this logic might actually give a result, but achieving a fully successful resolution would require magic.

At Percona we use a different approach. Performance optimization should not be based on guesses, but exact measurements. In application to databases, I described previously, we use queries as units of work. And a proper measurement of these units is the first important task in performance optimization.

Let me list the metrics of what our Percona Cloud Tools provides:

  • Query count – How many times query was executed
  • Query_time – Total time that MySQL spent on query execution
  • Lock_time – Time spent in waiting on Table level locks
  • Rows_sent – How many rows query returned to application
  • Rows_examined – How many rows MySQL actually had to read

In my previous post I indicated that Rows_sent/Rows_examined ratio is one of interest for OLTP workloads.

And the following metrics are available only for Percona Server, and not because we crippled our tools, but because MySQL simply does not provide them. It is worth reminding that one of main goals in making Percona Server was to provide diagnostics and transparency.

Percona Server metrics:

  • Rows_affected
  • Merge_passes
  • InnoDB_IO_r_bytes
  • InnoDB_IO_r_ops
  • InnoDB_IO_r_wait
  • InnoDB_pages_distinct
  • InnoDB_queue_wait
  • InnoDB_rec_lock_wait
  • Query_length
  • Bytes_sent
  • Tmp_tables
  • Tmp_disk_tables
  • Tmp_table_sizes

Meaning of all these metrics are available from our docs page

Now, often it is not enough to provide a single value for a given metric, so the following stats are available:
Total, Average, Minimum, Median, 95%, Maximum and Stddev.
Add for each of these trending graphs and you will see a matrix on your query:


Having all of these comprehensive metrics about your queries, you can now make an intelligent decision on how to approach performance optimization of your database.

You can do it all by yourself today with the free Beta of Percona Cloud Tools.

I understand that all of this information might be too much too figure out at first, but as with any tool – it takes skills and some experience to analyze and apply metrics properly. To help to bootstrap with Percona Cloud Tools, I will be running a webinar “Analyze MySQL Query Performance with Percona Cloud Tools” on Feb-12, 2014; if you register and install the Percona Cloud Tools agent you may win a free consulting hour from me during which I will examined your queries and provide an advice for optimization. See all conditions there.

The post MySQL performance optimization: Don’t guess! Measure with Percona Cloud Tools appeared first on MySQL Performance Blog.


10 MySQL settings to tune after installation

When we are hired for a MySQL performance audit, we are expected to review the MySQL configuration and to suggest improvements. Many people are surprised because in most cases, we only suggest to change a few settings even though hundreds of options are available. The goal of this post is to give you a list of some of the most critical settings.

We already made such suggestions in the past here on this blog a few years ago, but things have changed a lot in the MySQL world since then!

Before we start…

Even experienced people can make mistakes that can cause a lot of trouble. So before blindly applying the recommendations of this post, please keep in mind the following items:

  • Change one setting at a time! This is the only way to estimate if a change is beneficial.
  • Most settings can be changed at runtime with SET GLOBAL. It is very handy and it allows you to quickly revert the change if it creates any problem. But in the end, you want the setting to be adjusted permanently in the configuration file.
  • A change in the configuration is not visible even after a MySQL restart? Did you use the correct configuration file? Did you put the setting in the right section? (all settings in this post belong to the [mysqld] section)
  • The server refuses to start after a change: did you use the correct unit? For instance, innodb_buffer_pool_size should be set in bytes while max_connection is dimensionless.
  • Do not allow duplicate settings in the configuration file. If you want to keep track of the changes, use version control.
  • Don’t do naive math, like “my new server has 2x RAM, I’ll just make all the values 2x the previous ones”.

Basic settings

Here are 3 settings that you should always look at. If you do not, you are very likely to run into problems very quickly.

innodb_buffer_pool_size: this is the #1 setting to look at for any installation using InnoDB. The buffer pool is where data and indexes are cached: having it as large as possible will ensure you use memory and not disks for most read operations. Typical values are 5-6GB (8GB RAM), 20-25GB (32GB RAM), 100-120GB (128GB RAM).

innodb_log_file_size: this is the size of the redo logs. The redo logs are used to make sure writes are fast and durable and also during crash recovery. Up to MySQL 5.1, it was hard to adjust, as you wanted both large redo logs for good performance and small redo logs for fast crash recovery. Fortunately crash recovery performance has improved a lot since MySQL 5.5 so you can now have good write performance and fast crash recovery. Until MySQL 5.5 the total redo log size was limited to 4GB (the default is to have 2 log files). This has been lifted in MySQL 5.6.

Starting with innodb_log_file_size = 512M (giving 1GB of redo logs) should give you plenty of room for writes. If you know your application is write-intensive and you are using MySQL 5.6, you can start with innodb_log_file_size = 4G.

max_connections: if you are often facing the ‘Too many connections’ error, max_connections is too low. It is very frequent that because the application does not close connections to the database correctly, you need much more than the default 151 connections. The main drawback of high values for max_connections (like 1000 or more) is that the server will become unresponsive if for any reason it has to run 1000 or more active transactions. Using a connection pool at the application level or a thread pool at the MySQL level can help here.

InnoDB settings

InnoDB has been the default storage engine since MySQL 5.5 and it is much more frequently used than any other storage engine. That’s why it should be configured carefully.

innodb_file_per_table: this setting will tell InnoDB if it should store data and indexes in the shared tablespace (innodb_file_per_table = OFF) or in a separate .ibd file for each table (innodb_file_per_table= ON). Having a file per table allows you to reclaim space when dropping, truncating or rebuilding a table. It is also needed for some advanced features such as compression. However it does not provide any performance benefit. The main scenario when you do NOT want file per table is when you have a very high number of tables (say 10k+).

With MySQL 5.6, the default value is ON so you have nothing to do in most cases. For previous versions, you should set it to ON prior to loading data as it has an effect on newly created tables only.

innodb_flush_log_at_trx_commit: the default setting of 1 means that InnoDB is fully ACID compliant. It is the best value when your primary concern is data safety, for instance on a master. However it can have a significant overhead on systems with slow disks because of the extra fsyncs that are needed to flush each change to the redo logs. Setting it to 2 is a bit less reliable because committed transactions will be flushed to the redo logs only once a second, but that can be acceptable on some situations for a master and that is definitely a good value for a replica. 0 is even faster but you are more likely to lose some data in case of a crash: it is only a good value for a replica.

innodb_flush_method: this setting controls how data and logs are flushed to disk. Popular values are O_DIRECT when you have a hardware RAID controller with a battery-protected write-back cache and fdatasync (default value) for most other scenarios. sysbench is a good tool to help you choose between the 2 values.

innodb_log_buffer_size: this is the size of the buffer for transactions that have not been committed yet. The default value (1MB) is usually fine but as soon as you have transactions with large blob/text fields, the buffer can fill up very quickly and trigger extra I/O load. Look at the Innodb_log_waits status variable and if it is not 0, increase innodb_log_buffer_size.

Other settings

query_cache_size: the query cache is a well known bottleneck that can be seen even when concurrency is moderate. The best option is to disable it from day 1 by setting query_cache_size = 0 (now the default on MySQL 5.6) and to use other ways to speed up read queries: good indexing, adding replicas to spread the read load or using an external cache (memcache or redis for instance). If you have already built your application with the query cache enabled and if you have never noticed any problem, the query cache may be beneficial for you. So you should be cautious if you decide to disable it.

log_bin: enabling binary logging is mandatory if you want the server to act as a replication master. If so, don’t forget to also set server_id to a unique value. It is also useful for a single server when you want to be able to do point-in-time recovery: restore your latest backup and apply the binary logs. Once created, binary log files are kept forever. So if you do not want to run out of disk space, you should either purge old files with PURGE BINARY LOGS or set expire_logs_days to specify after how many days the logs will be automatically purged.

Binary logging however is not free, so if you do not need for instance on a replica that is not a master, it is recommended to keep it disabled.

skip_name_resolve: when a client connects, the server will perform hostname resolution, and when DNS is slow, establishing the connection will become slow as well. It is therefore recommended to start the server with skip-name-resolve to disable all DNS lookups. The only limitation is that the GRANT statements must then use IP addresses only, so be careful when adding this setting to an existing system.


There are of course other settings that can make a difference depending on your workload or your hardware: low memory and fast disks, high concurrency, write-intensive workloads for instance are cases when you will need specific tuning. However the goal here is to allow you to quickly get a sane MySQL configuration without spending too much time on changing non-essential MySQL settings or on reading documentation to understand which settings do matter to you.

The post 10 MySQL settings to tune after installation appeared first on MySQL Performance Blog.


Does a book need a villain?

Does a plot have to have an antagonist – a villain? Do you enjoy books or movies without a villain?

One of the classic plot lines for a story is a protagonist – the hero – defeating the antagonist. It’s the classic good vs. evil tale, and in most cases the hero is an underdog, someone thrust into the limelight against his or her will, and comes of age or defeats his/her fears or weaknesses during the process. Sometimes this is an iconic clash mano-a-mano, e.g. Superman fighting Zod, or Harry Potter vs. Valdemort, or Ahab vs. Moby Dick. Sometimes it is a series of encounters with minor villains, or henchmen, culminating in the big fight at the end, e.g. Luke Skywalker taking on the Empire, fighting his father, and finally defeating the Emperor, or almost every Bond movie where 007 battles and tricks his way through countless minions to confront the evil mastermind.

So do we need an antagonist? No. That said, without one, we need some other dramatic force for the hero to foil against, but this could be nature, the environment, his own fears, anything that provides tension and interest. Let’s look at some books/movies without an antagonist: 2010Wool (pre the shift trilogy), Gravity, Europa Report, Apollo 13, Deep Impact, Castaway, almost every disaster movie ever made, Flood, Ark (Both by Stephen Baxter), Love in the Time of Cholera, Contagion, Rain Man, Close Encounters. Here’s a post by David Brin.

Then there are those plots that on the face of it have an antagonist, but that isn’t the point of the movie/book. The clue here is that if you removed the villain(s) the plot would be almost entirely intact. 2001 for example: It’s a mission to find an alien artifact, the fact that HAL operates against the cast is incidental. Titanic: The husband is a villain, but the movie is about love found and lost on a sinking ship. Up by Pixar: Yes there’s a madman with an airship, but the movie is about discovering and exploring a lost world. If inclined, you could put many murder mysteries into this category. Certainly the murderer is the villain but is often only the inciting incident, and the plot is about the detective solving the clues.

My 3rd novel, that I am working on right now, falls into this category. Certainly some folks aim to stop my heroes, but the book doesn’t have a central villain. Actually, it sort of does, but you’ll have to read it to realize who it is.

What about you? Do you need a villain to hate?

[A previous post about antagonists]



Looking to upgrade to MySQL 5.6? Check out my webinar on Jan 29!

Upgrading to MySQL 5.6: Best PracticesWe are nearing the one-year anniversary since MySQL 5.6 went GA – which is typically a good time even for the most conservative users to start thinking about upgrading. At this point there is a fair amount of practical use and experience; many bugs have also been fixed (1991 to be exact according to Morgan Tocker).

We also know that MySQL 5.6 has been used in some very demanding environments on a very large scale, such as at Facebook. We also know from the Facebook team, after kindly sharing their upgrade experiences, that it takes a lot of work to upgrade to MySQL 5.6. That’s where Percona can help!

In my webinar this Wednesday, “Upgrading to MySQL 5.6: Best Practices,” I will share some of the issues and best practices we have discovered here at Percona in helping our customers upgrade to MySQL 5.6 – and also Percona Server 5.6, which is an enhanced, drop-in MySQL replacement. My webinar starts at 10 a.m. Pacific time. You can register here, and that URL will be available afterwards for the recording.

While we’re on the upgrade topic: MySQL 5.1 has reached EOL when it comes to MySQL support from Oracle. According to mysql.com, “Users are encouraged to upgrade to MySQL 5.6.,” and, “Per Oracle’s Lifetime Support policy, as of December 31, 2013, MySQL 5.1 is covered under Oracle Sustaining Support.” That means there will be no new releases, no new fixes (no error correction for new issues), no new updates.

Only existing updates, fixes and alerts are available – which means no more releases for MySQL 5.1 even if you run into crash-inducing bugs or the need for security fixes. The former can be especially worrying as MySQL 5.1 just got the some significant vulnerabilities fixed (more details) and chances are it will not take long for the next significant security issue to be discovered.

One solution to this problem is to upgrade to MySQL 5.6 or MySQL 5.5, which will be supported for awhile. That’s why Wednesday’s webinar is important to attend if you haven’t yet upgraded. You should also consider calling Percona to help in upgrading to MySQL 5.6 – especially if you do not have experience doing MySQL upgrades.

Another solution is to use Percona’s MySQL Support, which continues to cover MySQL 5.1 and Percona Server 5.1 and will provide fixes for crash-inducing bugs and security issues. I hope to see you on Wednesday!

The post Looking to upgrade to MySQL 5.6? Check out my webinar on Jan 29! appeared first on MySQL Performance Blog.


FOSDEM 2014 MySQL Devroom, Community Dinner in Brussels this Saturday!

This weekend, on 1-2 February, FOSDEM 2014 will take place in Brussels, Belgium.
There will be MySQL talks on Saturday in the MySQL Devroom and a MySQL Community dinner afterward.

30 people have already signed up for the community dinner, we’re almost at the amount of people from last year.

Last orders for the MySQL Community dinner will be accepted on Wednesday 29 January, so if you plan to attend, make sure to buy your tickets now. After that day, we won’t be able to add more people to the list.

I also wanted to thank the sponsors. Without them, this community dinner would not have been made possible:

The post FOSDEM 2014 MySQL Devroom, Community Dinner in Brussels this Saturday! appeared first on MySQL Performance Blog.


MySQL server memory usage troubleshooting tips

There are many blog posts already written on topics related to “MySQL server memory usage,” but nevertheless there are some who still get confused when troubleshooting issues associated with memory usage for MySQL. As a Percona support engineer, I’m seeing many issues regularly related to heavy server loads – OR OOM killer got invoked and killed MySQL server due to high Memory usage… OR with a question like: “I don’t know why mysql is taking so much memory. How do I find where exactly memory is allocated? please help!”

There are many ways to check memory consumption of MySQL. So, I’m just trying here to explain it by combining all details that I know of in this post.

  • Check memory related Global/Session variables.

If you are using MyISAM then you need to check for Key_buffer_size, while using InnoDB, you can check innodb_buffer_pool_size,  innodb_additional_memory_pool_size, innodb_log_buffer_size,  innodb_sort_buffer_size (used only for sorting data while creating index in innodb, introduced from 5.6). max_connections, query_cache_size and table_cache are also important variables to check

We know that whenever a thread is connected to MySQL, it will need it’s own buffers when they are doing some complex operations like FTS,  sorting, creating temp tables etc. So we also need to check the size of read_buffer_size, sort_buffer_size, read_rnd_buffer_size and tmp_table_size.

There is a very good quote from High Performance MySQL, 3rd Edition: “ You can think of MySQL’s memory consumption as falling into two categories: the memory you can control, and the memory you can’t. You can’t control how much memory MySQL uses merely to run the server, parse queries, and manage its internals, but you have a lot of control over how much memory it uses for specific purposes.” So it seems we have to understand the purpose for configuring any variable… either it is Global or Session level. I would like to explain more about that here.

For the Global variables like key_buffer_size, query_cache_size etc,  MySQL always allocates and initializes the specified amount of memory all at once when the server starts. But it’s not happened for those who are global default but can be set as per-session variables, i.e  For read_buffer_size, sort_buffer_size, join_buffer_size, MySQL doesn’t allocate any memory for these buffers until query needs. But when a query needs, it immediately allocates the entire chunk of memory specified. So if there are even small sorts, full buffer size will be allocated which is just waste of memory. Even some buffers can be used multiple times. For example on queries that join several tables join_buffer can be allocated once per joined table. also some complicated queries including sub-queries can use multiple sort_buffers at the same time which can lead to high memory consumption. In some scenario, query didn’t even use sort_buffer whatever size is, as it select by primary key which will not allocate it. So it depends on the nature of your environment but I would say it’s always better to start with a safe variable value that can be larger than default if needed but not as large as it can consume all of the server’s memory.

One more thing,  not all per thread memory allocation is configured by variables.  Some of memory allocation per thread is done by MySQL itself for running complex processes/queries like “stored procedures” and it can take unlimited amount of memory while running. And sometimes, optimizer  can also take a lot of memory working with highly complex queries which generally we can’t control by any configuration parameter.

Even innodb_buffer_pool_size is not a hard limit, usually innodb uses 10% more memory than the one specified. Many people do not recommend using both storage engine MyISAM and InnoDB at the same time on production server. Because both have individual buffers which can eat all server memory.

For detailed information related to this topic, I would suggest reading this post from Peter Zaitsev titled “MySQL Server Memory Usage.”

Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 76056
Buffer pool size 8191
Free buffers 7804
Database pages 387
Old database pages 0
Modified db pages 0

Above one is from Native MySQL but if you’ll check the same with Percona Server you’ll get some more information.

Total memory allocated 137756672; in additional pool allocated 0
Total memory allocated by read views 88
Internal hash tables (constant factor + variable factor)
Adaptive hash index 2217584 (2213368 + 4216)
Page hash 139112 (buffer pool 0 only)
Dictionary cache 597885 (554768 + 43117)
File system 83536 (82672 + 864)
Lock system 333248 (332872 + 376)
Recovery system 0 (0 + 0)
Dictionary memory allocated 43117
Buffer pool size 8191
Buffer pool size, bytes 134201344
Free buffers 7760
Database pages 431
Old database pages 0
Modified db pages 0

This will give you information regarding how much memory is allocated by InnoDB. You can see here “Total Memory Allocated”, “Internal Hash Tables”, “Dictionary Memory Allocated”, “Buffer Pool Size” etc.

  • Profiling MySQL Memory usage with Valgrind Massif

Recently, I used this tool and surprisingly I got very good statistics about memory usage. Here the only problem is you have to shutdown the mysql, start it with valgrind massif and after collecting statistics, you again have to shutdown and normal start.

$ /etc/init.d/mysql stop
$ valgrind --tool=massif --massif-out-file=/tmp/massif.out /usr/sbin/mysqld
$ /etc/init.d/mysql restart

After getting massif.out file, you have to read it with ms_print command. You will see pretty nice graph and then statistics. i.e

[root@percona1 ~]# ms_print /tmp/massif.out
Command:            /usr/sbin/mysqld
Massif arguments:   --massif-out-file=/tmp/massif.out
ms_print arguments: /tmp/massif.out
50.22^         ##
     |         #
     |         #
     |         #
     |         #
     |         #
     |    :    #
     |    ::   #                      ::::::@:::::::::::::@:::@::::@:::@::::
     |    : @::# :::::@@::::::::::::::::::: @::: ::: :::::@:::@::: @:::@::::
     |    : @::# :  ::@ :::::::::: :: ::::: @::: ::: :::::@:::@::: @:::@::::
     |    : @::# :  ::@ :::::::::: :: ::::: @::: ::: :::::@:::@::: @:::@::::
     |    : @::# :  ::@ :::::::::: :: ::::: @::: ::: :::::@:::@::: @:::@::::
     |    : @::# :  ::@ :::::::::: :: ::::: @::: ::: :::::@:::@::: @:::@::::@
     |    : @::# :  ::@ :::::::::: :: ::::: @::: ::: :::::@:::@::: @:::@::::@
     |   :: @::# :  ::@ :::::::::: :: ::::: @::: ::: :::::@:::@::: @:::@::::@
     |  ::: @::# :  ::@ :::::::::: :: ::::: @::: ::: :::::@:::@::: @:::@::::@
     | :::: @::# :  ::@ :::::::::: :: ::::: @::: ::: :::::@:::@::: @:::@::::@:
     | :::: @::# :  ::@ :::::::::: :: ::::: @::: ::: :::::@:::@::: @:::@::::@:
     | :::: @::# :  ::@ :::::::::: :: ::::: @::: ::: :::::@:::@::: @:::@::::@:
     | :::: @::# :  ::@ :::::::::: :: ::::: @::: ::: :::::@:::@::: @:::@::::@:
   0 +----------------------------------------------------------------------->Mi
     0                                                                   575.9
Number of snapshots: 96
 Detailed snapshots: [1, 7, 11 (peak), 16, 35, 48, 58, 68, 78, 88]
  n        time(i)         total(B)   useful-heap(B) extra-heap(B)    stacks(B)
  0              0                0                0             0            0
  1      6,090,089          195,648          194,590         1,058            0
99.46% (194,590B) (heap allocation functions) malloc/new/new[], --alloc-fns, etc.
->48.79% (95,458B) 0x7A1D20: my_malloc (my_malloc.c:38)
| ->25.08% (49,060B) 0x6594F1: read_texts(char const*, char const*, char const***, unsigned int) (derror.cc:160)
| | ->25.08% (49,060B) 0x6597C2: init_errmessage() (derror.cc:69)
| |   ->25.08% (49,060B) 0x506232: init_common_variables() (mysqld.cc:3414)
| |     ->25.08% (49,060B) 0x508CBB: mysqld_main(int, char**) (mysqld.cc:4461)
| |       ->25.08% (49,060B) 0x5B2CD1B: (below main) (in /lib64/libc-2.12.so)
| |
| ->09.36% (18,317B) 0x789571: my_read_charset_file (charset.c:364)
| | ->09.36% (18,317B) 0x789DEC: init_available_charsets (charset.c:458)
| |   ->09.36% (18,317B) 0x4E35D31: pthread_once (in /lib64/libpthread-2.12.so)
| |     ->09.36% (18,317B) 0x789C80: get_charset_by_csname (charset.c:644)
| |       ->09.36% (18,317B) 0x5062E9: init_common_variables() (mysqld.cc:3439)
| |         ->09.36% (18,317B) 0x508CBB: mysqld_main(int, char**) (mysqld.cc:4461)
| |           ->09.36% (18,317B) 0x5B2CD1B: (below main) (in /lib64/libc-2.12.so)
| |
| ->08.37% (16,384B) 0x79DEEF: my_set_max_open_files (my_file.c:105)
| | ->08.37% (16,384B) 0x506169: init_common_variables() (mysqld.cc:3373)
| |   ->08.37% (16,384B) 0x508CBB: mysqld_main(int, char**) (mysqld.cc:4461)
| |     ->08.37% (16,384B) 0x5B2CD1B: (below main) (in /lib64/libc-2.12.so)
| |
| ->04.36% (8,536B) 0x788DB4: init_dynamic_array2 (array.c:70)
| | ->02.45% (4,800B) 0x5CD51A: add_status_vars(st_mysql_show_var*) (sql_show.cc:2062)
| | | ->02.45% (4,800B) 0x505E68: init_common_variables() (mysqld.cc:3245)
| | |   ->02.45% (4,800B) 0x508CBB: mysqld_main(int, char**) (mysqld.cc:4461)
| | |     ->02.45% (4,800B) 0x5B2CD1B: (below main) (in /lib64/libc-2.12.so)
| | |

You can see from the output that where memory is allocated, to which function etc. You can use this tool to find memory leaks. You can get more information here for how to install and use it. Here’s another related post by Roel Van de Paar titled: “Profiling MySQL Memory Usage With Valgrind Massif.”

If possible Valgrind massif should not be used on busy production server as it can degrade the performance. Generally it’s used to find memory leak by creating mirror environment on test/stage server and run on it. It needs debug binary to run so it decreases performance a lot. So it can be used for investigating some cases but not for regular use.

  • Check Plot memory usage by monitoring ps output. 

This also useful when you want to check how much virtual(VSZ) and real memory (RSS) is used by mysqld. You can either simply run some bash script for monitoring it like

while true
  date >> ps.log
  ps aux | grep mysqld >> ps.log
  sleep 60

Or you can also check when needed from shell prompt with “ps aux | grep mysqld” command. 

  • Memory tables in MySQL 5.7

With MySQL 5.7, some very interesting memory statistics tables are introduced to check memory usage in performance_schema.  There is no any detailed documentation available yet but you can check some details here.  http://dev.mysql.com/doc/refman/5.7/en/memory-summary-tables.html

In P_S, there are five memory summary tables.

mysql> show tables like '%memory%';
| Tables_in_performance_schema (%memory%) |
| memory_summary_by_account_by_event_name |
| memory_summary_by_host_by_event_name |
| memory_summary_by_thread_by_event_name |
| memory_summary_by_user_by_event_name |
| memory_summary_global_by_event_name |
5 rows in set (0.00 sec)

So with every event you can get summarized memory consumption for a particular account, host, thread and user. While checking more, I found that there are around 209 different events to check. I have just tried to check one event related to join buffer size.

mysql> select * from memory_summary_by_account_by_event_name where SUM_NUMBER_OF_BYTES_ALLOC <> 0 and user = 'msandbox' and event_name = 'memory/sql/JOIN_CACHE' \G
*************************** 1. row ***************************
USER: msandbox
HOST: localhost
1 row in set (0.00 sec)
mysql> show global variables like 'join%';
| Variable_name | Value |
| join_buffer_size | 262144 |
1 row in set (0.00 sec)

Here, COUNT_ALLOC, COUNT_FREE are aggregate the number of calls to malloc-like and free-like functions. SUM_NUMBER_OF_BYTES_ALLOC and SUM_NUMBER_OF_BYTES_FREE are indicate the aggregate size of allocated and freed memory blocks. CURRENT_COUNT_USED is the aggregate number of currently allocated blocks that have not been freed yet. CURRENT_NUMBER_OF_BYTES_USED is the aggregate size of currently allocated memory blocks that have not been freed yet. LOW_ and HIGH_ are low and high water marks corresponding to the columns. 

If you are aware about these scripts then, these are giving very good summary about overall server memory consumption as well as related to MySQL.

Like in output of pt-summary,

# Memory #####################################################
Total | 11.8G
Free | 143.7M
Used | physical = 11.6G, swap allocated = 4.0G, swap used = 0.0, virtual = 11.6G
Buffers | 224.9M
Caches | 6.2G
Dirty | 164 kB
UsedRSS | 4.8G

In output of pt-mysql-summary.

# Query cache ################################################
query_cache_type | OFF
Size | 0.0
Usage | 0%
HitToInsertRatio | 0%
# InnoDB #####################################################
Version | 5.5.30-rel30.2
Buffer Pool Size | 4.0G
Buffer Pool Fill | 35%
Buffer Pool Dirty | 1%
# MyISAM #####################################################
Key Cache | 32.0M
Pct Used | 20%
Unflushed | 0%


It is really important for us to know where MySQL allocates memory and how it affects the overall load on the MySQL server and performance. I have just tried here to describe a few ways but I still think that we should have some sort of script or something that can combine all of these results and gives us some truthful output of memory usage in MySQL.

The post MySQL server memory usage troubleshooting tips appeared first on MySQL Performance Blog.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com