The default or “out of the box” experience is simply the experience for most people, so it’s critically important for us to ensure that the default configuration offers a good experience for the vast majority of cases. This is always a challenge, due to the matrix of factors involved–hardware configurations, software stacks, application use cases, etc.–but it’s most certainly a worthy endeavor.…
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 …[Read more]
Landing on an enterprise with ongoing projects mean that servers are often handed to IT staff without complete knowledge of what's inside. I've built a simple script, scraping from here and there, to gather a summary of relevant information. Once you've gained remote access to the MySQL instance, you can execute the queries to identify the following information regarding the target database
The binary log contains “events” that describe database changes. On a basic installation with default options, it's not turned on. This log is essential for accommodating the possible following requirements:
Replication: the binary log on a master replication server provides a record of the data changes to be sent to slave servers. Point in Time recovery: allow to recover a database from a full
MySQL 5.7 introduces a change in the way we query for global variables and status variables: the INFORMATION_SCHEMA.(GLOBAL|SESSION)_(VARIABLES|STATUS) tables are now deprecated and empty. Instead, we are to use the respective performance_schema.(global|session)_(variables|status) tables.
But the change goes farther than that; there is also a security change. Oracle created a pitfall of 2 changes at the same time:
- Variables/status moved to a different table
- Privileges required on said table
As an example, my non-root user gets:
mysql> show session variables like 'tx_isolation'; ERROR 1142 (42000): SELECT command denied to user 'normal_user'@'my_host' for table 'session_variables'
Who gets affected by this? Nearly everyone and everything.
- Your Nagios will not be able to read …
In MySQL Character encoding – part 1 we stated that the myriad of ways in which character encoding can be controlled can lead to many situations where your data may not be available as expected.
Setting MySQL Client and Server Character encoding.
Lets restart MySQL with the correct setting for our purpose, UTF8. Here we can see the setting in the MySQL configuration file, in this case /etc/mysql/my.cnf.
character-set-server = utf8
This change is then reflected in the session and global variables once the instance is restarted with the new configuration parameter.
mysql> SELECT …[Read more]
Following on from my earlier proposal to change Replication + InnoDB settings, in the MySQL team, we are proposing to make the following additional changes to defaults in MySQL 5.7:
|Setting||Old Default||New Default|