Home arrow MySQL arrow Page 3 - MySQL Server Tuning Tips and Tricks

More MySQL Server Tuning - MySQL

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.

  1. MySQL Server Tuning Tips and Tricks
  2. Hardware Specs, Common Mistakes
  3. More MySQL Server Tuning
  4. Final Thoughts
By: Barzan "Tony" Antal
Rating: starstarstarstarstar / 14
November 25, 2008

print this article



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.

>>> More MySQL Articles          >>> More By Barzan "Tony" Antal

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: