|Showing entries 1 to 10 of 17||7 Older Entries|
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[Read more...]
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[Read more...]
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 StartupsRelated posts:
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[Read more...]
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[Read more...]
A customer called with an emergency issue: A server that normally runs many MySQL instances wouldn’t start them up. Not only would it not start all of them, it wouldn’t even start the first one. The multiple instances were started through the mysql_multi init script. Perhaps you already know what was wrong!
It turns out that this server’s /etc/init.d/mysql_multi wouldn’t start unless it found the text “mysqld_multi” in the /etc/my.cnf file. Not a [mysqld_multi] config file section, but the text string “mysqld_multi”. It was using this text as a proxy for “I found a [mysqld_multi] configuration section.” This was a rather brittle test, as you can imagine.
After reading the source, I determined that the my.cnf file was fine and the configuration should not be changed,[Read more...]
As part of my upcoming FREE my.cnf check advice I first need to ask people to provide the current MySQL configuration file commonly found as a file named my.cnf
If only that question was easy to answer!
MySQL will by default use at least one configuration file from the following defaults. MySQL also uses a cascade approach for configuration files. When you have multiple files in the appropriate paths you can see unexpected behavior when you override certain values in different files.
You can however for example specify –no-defaults to use no configuration file, or add options to your command line execution, so even looking at all configuration files is no guarantee of your operating configuration.
Today, while on IRC in #pentaho I came across a discussion and a published my.cnf. In this configuration I found some grossly incorrect values for per session buffers (see below).
It doesn’t take a MySQL expert to spot the issues, however there is plenty of bad information available on the Internet and developers not knowing MySQL well can easily be mislead. This has spurred me to create a program to rid the world of bad MySQL configuration. While my task is potential infinite, it will enable me to give back and hopefully do a small amount of good. You never know, saving those CPU cycles may save energy and help the planet.
Stay tuned for more details of my program.
[mysqld] ... sort_buffer_size = 6144K myisam_sort_buffer_size = 1G join_buffer_size = 1G bulk_insert_buffer_size = 1G read_buffer_size = 6144K read_rnd_buffer_size = 6144K[Read more...]
|Showing entries 1 to 10 of 17||7 Older Entries|