MySQL Server Tuning Tips and Tricks

Fine-tuning the settings of servers in general requires a strong grasp of the inner workings of the servers and lots of patience. Did we mention patience? Yup, we did. It’s really important to understand that there are no tips that work for all kinds of setups. But thankfully we can talk about some widely known guidelines that can be followed to get the most out of your MySQL servers.

You are reading the fourth and final part of this MySQL performance optimization multi-part article series. Before we begin, don’t forget to check out the earlier parts. Within the first article the most important benchmarking concepts and strategies were pointed out, while the second segment presented benchmarking applications and other useful tools to apply what we have learned and test our own performance. In the previous third article we talked about optimal schema design and query optimizations.

Since this is the last article, we’d actually like to finish the series by covering some of the most powerful server tuning tips and tricks that may affect the performance of your server by far. Consider everything that you’re going to read about as hints, and don’t take anything for granted. By now you should know how to benchmark your server, notice what works or not, apply tweaks, find slow query bottlenecks, and so forth.

Now that you can’t wait to read about the promised server tuning tips, let’s begin!

{mospagebreak title=Hardware Specs, Common Mistakes}

Before we get into the tuning of server settings and discussions of various tips and tricks regarding performance, we should cover briefly the importance of hardware settings-and by this we mean the hardware components of the server on which MySQL service is running. First of all, always try to aim for 64-bit processors along with 64-bit compatible operating systems. x64 is crucial in order to maximize memory efficiency.

As always, having high bandwidth memories can give a powerful boost. The formula of "the more, the better" applies to CPU cache sizes as well. Multiple CPU platforms, SMP, and even multi-core systems are preferred due to their increased scalability (serving multiple queries at the same time on a parallel processing basis).

RAID gives a ton of benefits, but be careful when deciding which kind of level you choose. For example, RAID1 (or RAID10) seems to be an okay choice, while RAID5 tends to become frustrating and too expensive to manage; in that case, the costs exceed the benefits by far. If you pick RAID then set medium-to-high RAID chunk size. Something along the lines of 512KB-1MB should be enough, but as always-test for yourself!

Lastly, when it comes to server performance, some people neglect the quality and throughput of network devices, such as network cards for example. Nowadays gigabit is the trend and they are really worth the effort. It’s important that you check whether they are configured and working properly (full duplex?).

Moreover, keep in mind that the operating system is also responsible for the way your MySQL server performs. The server instance per se is a single process and, thus, the OS should be configured to permit really large process sizes. Additionally, enable the –large-pages option in MySQL if the OS you are running has this kind of capability. Also, since tables are quite large (usually) consider using a larger file system block size.

Finally, do various benchmarks on Disk I/O and memory performance because these two strongly affect the performance of your MySQL server. Also, enable and try different schemes of caching, re-benchmark the performance, and compare results. Fine-tune the read-ahead kernel technique. Check this documentation and read more.

Now let’s talk a little bit about common mistakes that should be avoided. First of all, don’t try to search for an "optimal" or very best MySQL configuration file because, who knows? You may find one that is claimed to be the best, and then you’re going to set yourself up for nothing but frustration. However, check their content and see the values of the variables; try to search for the meaning of each in the official documentation.

As soon as you have "demystified" the content of the configuration file, then you can grab only the meaningful ones-here we refer to those that are useful for your own needs and server specs. Oh, and do not even attempt to use the default MySQL configuration. It won’t be enough; the default config file is really weak and small.

There are a few commands that you should use to monitor the load and performance of the OS. For example, vmstat gives an overview of processes, virtual memory information, paging, block I/O, CPU usage, and all that; mpstat reports CPU-related statistics and also gives you global averages for each processor/core. Finally, you may want to check out iostat. This command gives you lots of input/output statistics…

Even though MySQL is cross-platform during the aforementioned commands, we’ve somewhat focused on Linux as the main operating system. If you are using Windows, then try to find third-party applications and/or utilities that report the same information to you. The bottom line is that you should be able to monitor the disk I/O and CPU performance.

{mospagebreak title=More MySQL Server Tuning}

On the previous page we’ve laid the foundation of what is going to be required here.

Here we’ll do our best to present some of the configuration variables that you should fiddle around with (meaning try different values until you find one that matches your needs and performs accordingly), a few general guidelines that may serve as "rules of thumb," and ultimately some swapping- and caching- related hints.

First of all, the common scenario is where both Apache and MySQL daemons are being run from the same server. This is not necessarily bad, since having dedicated servers for each daemon sometimes isn’t worth the extra investment. However, if you are required to serve lots of simultaneous clients dynamically on a per-connection basis, then you need to configure MySQL accordingly to accommodate the load spikes.

In order to do this we need to grab our access_log Apache log since it contains the ratio of dynamic requests to static requests. The former depend son MySQL queries (such as a web page that works with a MySQL database via PHP commands; actually, it establishes connections, requests the queries, and grabs the data), while the latter has nothing to do with MySQL (no connections need to be opened). They are static.

Let’s assume the following real-world scenario. Your Apache daemon is configured to serve a total number of 128 maximum simultaneous connections, of which the ratio of dynamic versus static pages is, say, 1:9. Now let’s do the math: 128 / 10 = 12,8. Rounding the amount to 13, we end up with about 13 dynamic pages that "work with" MySQL database(s).

Now comes the fun part. If you are really sure that the ratio is usually around that, then to maintain the overall safety of your server configuration, we can truly assume that doubling 13, meaning 2*13=26, would be a "safe" count of simultaneous database connections to be opened, right? If so, then you can set that amount of max_connections and max_user_connections located in the my.cnf file.

Speaking of the previous two variables, there are a few possibilities regarding the my.cnf configuration file. You need to know that those variables should be located within the [mysqld] section in the conf file. Check that section, since you may already have these variables declared. If so, replace their values with your amount. Make sure they aren’t commented; if so-remove comments. If they aren’t present at all, then just add them as independent and new entries (set-variable=var_name=X).

After you’ve become familiar with the my.cnf config file, it’s time to talk about the rest of the variables. In this part we won’t be so detailed anymore, since there may not be any general guidelines whatsoever. You just need to understand their meaning (what each variable refers to) and then you can predict some "all right" values already.

Memory settings are important, since they have the greatest effect on overall performance. Look for the following variables: key_buffer_size (if you run MyISAM only databases, then setting 30% of total RAM is usually recommended; 4GB is the max limit per key), innodb_buffer_pool_size (if you run InnoDB only databases then setting 70%-80% of total memory is advocated), query_cache_size (32M-512M), read_buffer_size, read_rnd_buffer_size (try values around 1-6MB for the latter two buffer sizes).

Now think a little bit. How frequently do you sort incredibly large sets? If quite often then you may want to increase the value of sort_buffer_size globally right from the config file. If you sort really huge sets only rarely, then it is advisable to leave a smaller buffer size in the config file (global definition) and just change the "sort_buffer_size" right before executing those large sorts.

The aforementioned is important because you will be able to re-set the DEFAULT amount specified globally within the configuration file right after the sorting of those huge sets is done. Having an insanely high buffer size without a real reason will hurt performance.

Finally, set table_cache to at least 1024, and thread_cache to something around 16-64. These values are measured in number of units (number of tables, and how many threads). Pay attention when measurement units are in bytes (M, G, etc.) or just count. If InnoDB is your thing, then check out innodb_log_file_size (512M should be enough but hopefully you can get away with less), and innodb_log_buffer_size (a few megabytes).

A few paragraphs above we mentioned query_cache_size. That variable, if it is set, enables query caching. If you’ve enabled it, then you should monitor its usage. The command show status like ‘qcache%’ reports the default values of query-caching-related variables (such as Qcache_<something_goes_here>). You may want to flush status before running the previous command to report the variables.

Now that you know most of the query-caching-related variables, check out list table below to see their usual meaning and some hints that you can find spread over the Web.

Qcache_free_blocks -> number of contiguous memory blocks in the cache. If this value is high, then blocks are fragmented; flush query cache defragments the query cache.

Qcache_free_memory- > free memory in the cache.

Qcache_hits -> incremented each time a query is served from the cache.

Qcache_inserts -> incremented each time a query is inserted. Divide the number of inserts by the hits to get your miss rate; subtract this value from 1 to get your hit rate.

Qcache_lowmem_prunes -> number of times the cache ran out of memory and was purged in order to free it up. Check this value over time; if it’s increasing, then chances are either fragmentation is high(er) or memory is quite low.

Qcache_not_cached -> number of queries that weren’t candidates for caching.

Qcache_queries_in_cache -> number of queries and responses currently cached.

Qcache_total_blocks -> number of blocks in the cache.

As a final piece of advice, check out this official documentation. It covers lots of variables.

{mospagebreak title=Final Thoughts}

Now that you’ve reached this final page, it means that we’re finishing up this series. Hopefully you have found its segments educational and informative. At the least, you should have developed a sense of what high performance MySQL tuning and optimization is about, and know how to imagine optimization "sessions" and such.

We can’t stress enough that optimizing MySQL server instances and databases (and generally any kind of server) is a sensitive task since there are just way too many factors. All of them highly depend on your setup, requirements, and data. Different organizations have different purposes and database needs, thus totally different queries are to be answered as responsively as possible, on a variety of server specifications.

Fortunately for us, since MySQL is the most popular open-source cross-platform database solution, there are literally tons of discussions circulating around tweaks and optimizations. Conferences are done all over the world where hundreds of DBAs and experts in their field gather together to share their findings in order to achieve bleeding edge performance from their MySQL runs.

Moreover, this means that there is a lot of information out there spread over the Internet within various blog entries and forum posts, but let’s not exclude the dozens of books and tutorials that have been written on the topic. Lots of them are also frequently updated (new tweaks, new releases, etc.).

What you need to develop is the skill to get the most out of that information, adapt everything to your own needs, and you’ll reap astonishing rewards. Keep in mind, just because some say that such and such improves performance by a great deal, don’t take that statement for granted. Also, be careful because there are tweaks that can alter the behavior of MySQL (are they safe?). Others give performance boosts only randomly.

Summing these up, we truly think that by reading this multi-part article series we have opened a new world of endless possibilities for you. You should learn to love tweaking!

In closing, I’d like to invite you to join our experienced community of technology professionals on all areas of IT&C starting from software and hardware up to consumer electronics at Dev Hardware Forums. As well, be sure to check out the community of our sister site at Dev Shed Forums. We are friendly and we’ll do our best to help you.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan