Tunning System Variable - MYSQL

http://www.hashmysql.org/index.php?title=Tuning_System_Variables

Foreword

There are a number of variables available to tune within MySQL, all available for you to see via the SHOW VARIABLES; command. To aid in your diagnostics of how well these variables may be tuned, MySQL provides the SHOW STATUS; command.

Out of these variables, there are some that can help you on your way to the ultimate in "mauve" databases - those that detail the various memory cache's available to you. MySQL breaks up it's total memory structure in to smaller caches that service different functions, such as the Key Cache and Table Cache to hold frequently used index and table data in memory, for example.

Here we'll take a look at which variables to look for, how we can select them, how to interpret them, and ultimately, how to tune them. We will start with the thread cache and show the steps involved in tuning the server variables.

[edit]

The Thread Cache

Before users can even begin to start selecting data, they need to connect to the database. If your application makes a lot of seperate connections to the database over short periods of time (like most PHP based applications) this can cause a lot of overhead with allocating and deallocating the associated "stack" for each specific "Connection" or "Thread".

So wouldn't it make sense to hold a pool of already allocated threads in memory? Enter thread_cache_size - which sets the number of threads to hold open in memory to service new connections.

Using the SHOW STATUS command we can determine how many connections are being serviced by already cached threads, and how many are having to wait for a new thread stack to be allocated. The status variables in question are Threads_created andConnections. We can select very specific status variables by using the LIKE keyword, such as:

 SHOW STATUS LIKE '%thread%'; +------------------------+-------+ | Variable_name          | Value | +------------------------+-------+ | Delayed_insert_threads | 0     | | Slow_launch_threads    | 0     | | Threads_cached         | 30    | | Threads_connected      | 4     | | Threads_created        | 31    | | Threads_running        | 2     | +------------------------+-------+ 

To determine how the thread_cache_size variable should be set, we use what is known as a "Hit Ratio". Hit Ratios are typically stated as a percentage score, giving the percentage of "requests" that were serviced by in memory caches (also known as logical I/O) vs those that cause disk requests (physical I/O) or extra processing - such as in this case, thread allocation to memory (which causes extra CPU activity).

Threads_created details the number of threads that have been created since the MySQL server started, and Connections is the total number of client connections to the MySQL server since startup. To work out the thread cache hit ratio, we use this calculation:

 100 - ((Threads_created / Connections) * 100) 

Given the above value of 31 for Threads_created, and the value of 4567 that Connections showed, I am able to determine that my Thread Cache Hit Ratio is 99.32%.

The ideal situation is to get Threads_created as close as possible to thread_cache_size - no new connections having to wait for new thread allocation - staying as close to around a 99% hit ratio as you can. To see the value of thread_cache_size you can use:

 SHOW VARIABLES LIKE 'thread_cache_size'; +-------------------+-------+ | Variable_name     | Value | +-------------------+-------+ | thread_cache_size | 30    | +-------------------+-------+ 

Of course, these hit ratios can be skewed on a system that has recently been restarted, so should also be evaluated accordingly against Uptime.

[edit]

The MyISAM Key Cache

The MyISAM storage engine has one main area within the MySQL memory structure that helps to service requests to clients, the MyISAM Key Cache.

[edit]

InnoDB Specific Caches

To be written.

[edit]

The Query Cache

To be written.

[edit]

Sort Buffers

To be written.

Optimize MySQL: The Thread Cache

From: http://www.epigroove.com/posts/63/optimize_mysql_the_thread_cache

Optimize MySQL: The Thread Cache

With the recent release of the new Babble site and the resulting increase in mysql activity (MySQL was averaging around 300 queries per second), mysql and server loads rose to a level that was starting to get uncomfortable. So I decided to flip through the MySQL status variables to see if anything looked abnormal. That's when I discovered that my "thread cache hit rate" was abysmal.

The hit rate should be as close to 100% as possible. You can calculate your hit ratio by dividing the 'threads_created' status variable by the 'connections' status variable:

100 - ((Threads_created / Connections) * 100)

My hit rate was less than 1%. Almost every mysql connection was causing a new thread to be created, and a lot of threads were being created, thus creating a lot of unnecessary overhead.

The cause of the problem was that 'thread_cache_size' was set to 0. Thread_cache_size determines how many threads MySQL will hold open in memory to handle new connections. So in my case, MySQL wasn't holding any in cache so it had to create new threads all the time. Not cool.

To determine what you should set 'thread_cache_size' to, pay close attention to the 'threads_created' status variable. If it keeps going up it means your 'thread_cache_size' is set too low. Just keep bumping up 'thread_cache_size' until 'threads_created' no longer increments. My optimal thread_cache_size turned out to be 50.

As soon as I optimized the thread cache, MySQL's server load dropped over 50%!

This MySQL optimization technique worked wonders for me, so I'm sharing it in hopes that it will help you.

BTW- If you don't have it yet, go download the MySQL Administrator. It makes viewing and changing MySQL variables much easier.

Comments

READ this. Regarding INNOdb optimization

http://jeremy.zawodny.com/blog/archives/000173.html

MySQL, Linux, and Thread Caching

Some random bits scribbled by Jeremy Zawodny

Wow, it's been a busy week. I was totally swamped for several days dealing with the remember.yahoo.com MySQL servers and related stuff. And then I used a day or two to recover (sleep, shower, etc).

Anyway, I made some interesting discoveries along the way. The most surprising one had to do with thread caching on Linux when you have a busy MySQL server--busy in a particular way, mind you.

You see, we had a single master server which all the web servers could connect to (using PHP) whenever someone made a change. That includes creating a tile (there were several hundred thousand tiles created), approves a tile, marks one as "cool", and so on. All told, the master was quite busy.

Because there were between 20 and 45 front-end web servers during that time, and each could have had up to 70 apache processes that might have needed to connect, we faced a problem. That meant that the master needed to handle up to 3,150 connections in the worst case (that's 45 x 70). Most of the PHP code used mysql_pconnect() to hold persistent connections.

Rather than worry about how to do that, I made sure that the wait_timeout was set to a very low value: 15 seconds. That means MySQL would close any connection that was idle for more than 15 seconds. But I didn't realize the extent of the problem until I started getting reports from the web servers that the master was refusing connections. Why? Because I had set the maximum number of connections to a reasonable value in the master's my.cnf file:

set-variable = max_connections=180 set-variable = max_user_connections=140 

And at that time, the wait_timeout was set to 600 seconds (10 minutes). Clearly that was a problem. There were a lot of idle clients holding connections and blocking out new clients from connecting and getting real work done.

What to do?

We could have stopped using mysql_pconnect(), but as you'll see, that wouldn't have solved the underlying problem.

I needed to adjust the settings. But I wasn't sure what values to use. And I really didn't want to keep stopping and starting the master. That would just suck. Then I remembered that we were running MySQL 4.0.4. I'd has a new feature that allows you to change most of the server settings on on the fly without restarting! Read abouthere, it in the on-line manual.

Excellent!

All I needed to do was execute a few variations on this command:

SET GLOBAL wait_timeout=60; 

(with different values in the place of "60") to try and strike a balance between letting new clients in and kicking out already connected users too quickly.

Ultimately, I settled on a timeout of 15 seconds.

But that had an interesting and unanticipated side-effect. It meant that the Linux server was having to create new threads (MySQL is a multi-threaded server) at a very high rate. That sucks up a measurable amount of CPU time.

How much CPU time? By the time I got around to looking at the output of SHOW STATUS and seeing this:

| Threads_cached           | 0          | | Threads_created          | 270194     | | Threads_connected        | 46         | | Threads_running          | 28         | 

Things were pretty bad. The machine had very little idle CPU time--probably 5-10% at the most. But it really wasn't doing that much work--maybe 40 queries per second. I was a bit puzzled. But that Threads_created number jumped out at me. It was high and increasing rapidly.

Luckily I remembered the thread_cache setting. So I decided to investigate (using the new syntax for examining server variables):

mysql> SELECT @@global.thread_cache_size; +---------------------+ | @@thread_cache_size | +---------------------+ |                   0 | +---------------------+ 1 row in set (0.00 sec) 

Uh oh. I never set the thread cache in my.cnf, so it has assumed the default. That's bad. It's like removing the pre-forking capabilities of Apache 1.3 and letting it get pounded on a busy web site. The "fork a new process for each new request" gets pretty expensive pretty quickly.

Ugh!

Luckily the thread cache is also tunable on the fly now. So all I had to do was this:

SET GLOBAL thread_cache_size=40; 

I took a guess and figured that by caching 40 threads, we'd be saving a lot of work. And boy was I right!

In the other window, where I was running vmstat 1 I noticed a dramatic change. The idle CPU on the machine immediately went from 5-10% to 35-40%

If only I had thought of that sooner!

So the moral of the story is this: If you have a busy server that's getting a lot of quick connections, set your thread cache high enough that the Threads_created value in SHOW STATUS stops increasing. Your CPU will thank you.

I don't feel bad though. We were all going nuts to try and tune/optimize the code and servers while it was running and had very little sleep. Thread caching really wasn't the worst of our problems. But it became the worst after we had fixed all the bigger ones.

It was quite a learning experience.

[HowTo] Optimising MYSQL

FROM:: 

http://www.interworx.com/forums/showthread.php?t=377

First of all, how to find your variable, and the mysql usage ?

*VARIABLES

Quote:

from mysql : show variables;  or from command line :  mysqladmin variables

*PROCESS / STATUS

Quote:

from Mysql : show status;  or from command line  mysqladmin –i10 processlist extended-status

*SOME USEFUL COMMAND FOR YOU BOX USAGE

Quote:

>Top >ps –axfu  >vmstat 1

* OPTIMISING MYSQL

To obtain the stat of your mysql server since it has been loaded, run mysqladmin processlist extended-status as mentionned above.

1 - The two most important variables : Table_cache and Key_buffer_size

* If Opened_tables is big, then your table_cache variable is probably
too small.
table_cache 64
Open_tables 64
Opened_tables 544468

This is the first serious problem. "The table_cache is the number of open
tables for all threads. MySQL, being multi-threaded, may be running many
queries on the table at one time, and each of these will open a table."
Therefore, even though we only have a few tables, we will need many more
open_tables.

The Opened_tables value is high and shows the number of
cache misses. Getting the table_cache size correct is one of the two best
things you can do to improve performance.



* If Key_reads is big, then your key_buffer_size variable is probably
too small. The cache hit rate can be calculated with
Key_reads/Key_read_requests.
key_buffer_size 16M
Key_read_requests 2973620399
Key_reads 8490571
(cache hit rate = 0.0028)

"The key_buffer_size affects the size of the index buffers and the speed
of index handling, particularly reading." The MySQL manual (and other sources) say that
"Key_reads/Key_read_request ratio should normally be < 0.01." This is the
other most important thing to get correct. Here the value seems to be correct (< 0.01)

Also check key_write_requests and key_writes.
The key_writes/key_writes_request should normally be < 1 (near 0.5 seems to be fine)


Here is a very interesting web pointer : http://www.databasejournal.com/featu...0897_1402311_3



2 - Others important settings are : Wait_timeout, max_connexion, thread_cache

A little explanation : 
Generaly you have a lot of mysql process that are sleeping because wait_timeout are not set low. So I make sure that the wait_timeout is set to a very low value: 15 seconds (for me) . That means MySQL would close any connection that was idle for more than 15 seconds.

The problem is you also have to increment your max_connexion (mine is set to 300) to be sure there is not a lot of idle clients holding connections and blocking out new clients from connecting and getting real work done.
The pbm is that the box has to create new threads (MySQL is a multi-threaded server) at a very high rate. That may sucks up a measurable amount of CPU time.

So the solution is to use the Thread_cache (from mysql doc) :
"How many threads we should keep in a cache for reuse. When a client disconnects, the client's threads are put in the cache if there aren't more than thread_cache_size threads from before. All new threads are first taken from the cache, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. (Normally this doesn't give a notable performance improvement if you have a good thread implementation.) By examing the difference between the Connections and Threads_created you can see how efficient the current thread cache is for you."


* If Threads_created is big, you may want to increase the
thread_cache_size variable. The cache hit rate can be calculated with
Threads_created/Connections.
thread_cache_size 0
Threads_created 150022
Connections 150023

This is the second problem that should be fixed. A cache size of zero is the default for my-medium.cnf but the recommended size in my-large.cnf is 8. 

you may try this formula : table_cache = opened table / max_used_connection


3 - Finally, you may also have a look at : tmp_table_size and Handler_read_rnd / Handler_read_rnd_next 

* If Created_tmp_disk_tables is big, you may want to increase the
tmp_table_size variable to get the temporary tables memory-based instead
of disk based.

tmp_table_size 32M
Created_tmp_disk_tables 3227
Created_tmp_tables 159832
Created_tmp_files 4444

Created_tmp_disk_tables are the "number of implicit temporary tables on
disk created while executing statements" and Created_tmp_tables are
memory-based. Obviously it is bad if you have to go to disk instead of
memory. About 2% of temp tables go to disk, which doesn't seem too bad
but increasing the tmp_table_size probably couldn't hurt either.


* If Handler_read_rnd is big, then you probably have a lot of queries
that require MySQL to scan whole tables or you have joins that don't use
keys properly.
Handler_read_rnd 27712353
Handler_read_rnd_next 283536234

These values are high, that we could probably stand to improve
the indexes and queries.


I hope this will help some of you to more understand how it is possible to optimise MYSQL to fit your needs, hardaware box, or mysql current usage.

Maybe there is others tweaks to perform, but I know well only these ones. I did setup using these ones on differents mysql box, and generally it did help us to increase performance without have to change hardware (our boxes have 2GB ram)

two or three importants things like :

Used MySQL memory = key_buffer + max_connections * (join_buffer + record_buffer + sort_buffer + thread_stack + tmp_table_size) 

Notice the max_connexion and the multiplier.
connexion increase = memory usage increase too.

Notice key_buffer
for a given memory :
more you add mem to key buffer, less connexion is
less is key buffer, more connexion is

If you change one of these settings for a high value, you system may swap.
If you system swap, try lot decrease these values

Also, about table_cache :
Increasing the size of the table cache may really help you. 
But you must be careful not to make the value too large. All operating systems have a limit on the number "open file pointer" (sorry in french it is called pointer, maybe descriptors is the good translation) a single process may have.
If MySQL tries to open a lot of files, the OS may refuse it and MySQL will generate error message in the error log.