The latest from the MySQL community

Editor’s Note: Because our bloggers have lots of useful tips, every now and then we update and bring forward a popular post from the past. Today’s post was originally published on May 23, 2018.

In recent weeks I’ve been focusing on Docker in order to get a much better understanding of the containerized world that is materializing in front of us. Containers aren’t just for stateless applications anymore and we’re seeing more cases where MySQL and other databases are being launched in a containerized fashion, so it’s important to know how to configure your MySQL container!

In docker hub, you will see an option for this by doing a volume mount from the Docker host to the container on /etc/mysql/conf.d. But the problem is that the container image you’re using may …

When MySQL becomes too slow (or too unstable), temptation usually is to tweak the MySQL configuration file. Indeed, it’s a good place to start. But if you ever looked at the available configuration options, you know things can get messy – MySQL now has over 450 configuration variables for your consideration, that are not classified in any way, and neither of them are included in the stock my.cnf. It’s hard to know where to start!

I’m hoping that this blog post will help you overcome the anxiety of tuning MySQL, whether you’re setting up a new server, or tuning an already running server for better performance.

Don’t do it the way rookies do it

During the last 9 years I’ve spent at Percona working as a MySQL performance and scalability consultant, I found that customers often use the trial and error approach when tuning MySQL configuration: they change a few things and check if it …

I log into a lot of different servers running MySQL and one of the first things I do is create a file in my home directory called ‘.my.cnf’ with my credentials to that local mysql instance:

[client]
user=root
password=secret

This means I don’t have to type my password in every time, nor am I tempted to include it on the command line with -p and get the dreaded (but completely accurate):

Warning: Using a password on the command line interface can be insecure.

MySQL 5.6 introduces a utility to make this easier and more secure. First, let’s start with a new mysqld instance with a blank root password and make it more secure:

[vagrant@localhost ~]$ mysqladmin -u root password
New password:secret
Confirm new password:secret
[vagrant@localhost ~]$ mysql -u root
ERROR 1045 (28000): Access denied for user …

When you're testing out a new version of MySQL in a non-production environment there is a temptation to go wild and turn on all kinds of new features.  Especially if you're reading the changelogs or the manual and scanning through options.  You want to start with the most reasonable set of defaults, right?  Maybe you're even doing benchmarks to optimize performance using all the new bells and whistles.

Resist the temptation!  If your goal is to upgrade your production environment then what you really want is to isolate changes.  You want to preform the upgrade with as little to no impact as possible.  Then you can start turning on features or making changes one-by-one.

Why?  Anytime you're doing a major upgrade to something as fundamental as your core RDBMS, there are many ways things can go wrong.  Performance regressions & incompatible changes, client/server incompatibilities …

While doing some testing (that I published later here) on the still-in-development MySQL 5.7 I wanted to do some analysis on the configuration to see if the changes in performance were due to the code changes or just to the new MySQL defaults (something that is very common in the migration from 5.5 to 5.6 due to the default transaction log size and other InnoDB parameters). This is a quick post aiming to identify the global variables changed between these two versions.

You could tell me that you could just read the release notes, but my experience (and this is not an exception, as you will see) …

Upgrading from MySQL 5.5 to MySQL 5.6 is a fairly straightforward process. However, the move to version 5.6 does bring with it some new varaibles with which to work.

One of the recent innovations (pioneered by Percona in Percona Server 5.5 if I recall correctly) is the ability to "dump" the innodb buffer pool***. This can be done any time MySQL is running and can be configured to be executed during MySQL shutdown. One reason to do this is to have a server "pre-warmed" when you perform a server restart. Why this is important is that during server operation the innodb buffer pool will fill up. Typically the server will operate much better when this buffer pool is full instead of being empty or paritally full because the return of data from the buffer pool is much faster than returning data from disk. 

There are four important variables with each having a value of 'ON' or 'OFF'.

innodb_buffer_pool_dump_at_shutdown …

Read the original article at Thoughts on Upcoming MySQL 5.6 Defaults

During Oracle Open World 2012 and the parallel MySQL Connect conference, the new 5.6 version was announced. It’s only release candidate right now, but that means the GA release is just around the corner. With that James Day has posted changes to various of the new parameter defaults. Many of them you may not run [...]

For more articles like these go to Sean Hull's Scalable Startups

Related posts:

1. 5 Ways to Boost MySQL Scalability
2. 5 Ways to fortify MySQL …

Ah, the sometimes bitter pill of experience.

Recently I got "the call" about a server that was having problems. I began standard troubleshooting procedures but didn't see anything abnormal. After about 15 minutes of getting irritated I opened up the my.cnf and looked through it. Imagine my suprise when I saw the innodb_log_file_size parameter was set to 5M (five megabytes). Even though it was hard-coded in the my.cnf, this is actually the default size (at least through MySQL 5.1). A log file size of five megabytes is pitifully small and was proving to be a severe bottleneck for the system. The two log files were being , filled up and flushed more frequently than once a second.

Once the source of the trouble was discovered it was a simple matter of filing a RFC and resolving the issue. We increased the log file size to 2000 megabytes so flushing of the transaction logs occured when the server had the time/resources and not …

In order to benchmark a Drupal site performance you need to see all the database queries related to your Drupal site. In case you don’t have access to the my.cnf file If you don’t have access to the my.cnf file, you can log the queries from the Drupal code itself: cp includes/database.mysql-common.inc includes/database.mysql-common.inc.backup.orig vim includes/database.mysql-common.inc […]

Various people have complained about Linux swapping unexpectedly on boxes running mysqld, when apparently mysqld was not using all the memory and there was quite a bit of free memory available.

There’s also an article by Jeremy Cole. However, his solution requires a one-line change to mysqld_safe which while it’s small does not work very well if you have to maintain a large number of servers and are using packages asa package upgrade will overwrite the modified file mysqld_safe and then restart mysqld with the unmodified script. This leads to the need to repatch the script and then restart mysqld. Not very helpful.

So I was looking for another solution and came up with this option which basically requires a minor change to /etc/my.cnf and the use of a small shell wrapper script. The change to my.cnf is simply to …