MySQL is a popular choice of database for use in web applications, and is a central component of the widely used LAMP web application software stack—LAMP is an acronym for “Linux, Apache, MySQL, Perl/PHP/Python”. MySQL is used in some of the most frequently visited web sites on the Internet, including Flickr, Nokia.com, YouTubeand as previously mentioned, Wikipedia, Google and Facebook.This article is meant to be an easy and relatively safe way to enhance MySQL performance. It is not meant to be a complete guide to tuning MySQL. Fully optimizing MySQL takes both time and effort since every application has different requirements.
To remove the sleeping connections use this
wait_timeout = 60
Setting the max_connections too high will result in the MySQL server crashing with an “Out of memory” error. General value is 400 -500 depending on your ram and other process that are occupying the ram.
max_connections = 400
The key buffer holds the indexes of tables in memory and a bigger key buffer results in faster row lookups. Adjust according to your own needs. Bigger is better, but prevent swapping at all costs. A good rule of thumb seems to be to use 1/4 of system memory.
key_buffer = 128M
Query Cache
This is where the magic happens. Well, not magic really, just plain old caching. Keeping the result of queries in memory until they are invalidated by additional writes enhances performance by magnitudes. The query_cache_size, as the name suggests, is the total size of memory available to query caching. The value query_cache_limit is the maximum number of kilobytes one query may be in order to be cached. Setting this value too high might prevent a lot of smaller queries to be cached. Setting it too low will result in bigger queries to never be cached, and the smaller queries not being able to completely fill the cache size, which would be a waste of resources. Adjust according to your own needs and memory available:
query_cache_size = 128MB
query_cache_limit = 4MB
If you are not using InnoDB table type Add skip-innodb to my.cnf to disable the engine.
Temporary tables are used for sorting and grouping. The buffer is created on demand so watch out for setting this too high here as well. If the buffer cannot accommodate the data, a temp file is used on disk instead.
tmp_table_size = 64MB
An important variable if your application accesses many tables. It is the number of tables a thread can keep open at the same time. A value of 512 should do no harm.
table_cache = 512
Use only log_slow_queries other wise logging will be performance hunter.
MySQL server error Too many connections
If you get a too many connections error when you try to connect to the MySQL server, this means that all available connections are in use by other clients. Server thinks it is under attack when it sees many connect is trying to use its service that’s why MySQL server stops working and shows this error.
There are many reasons for this error to occur. If you code does not close the MySQL connection, then this connections can be live in MySQL server as “sleeping connections”.
For example
// Connecting, selecting database
$link = MySQL_connect('MySQL_host', 'MySQL_user', 'MySQL_password')
or die('Could not connect: ' . MySQL_error());
echo 'Connected successfully';
MySQL_select_db('my_database') or die('Could not select database');
// Performing SQL query
$query = 'SELECT * FROM my_table';
$result = MySQL_query($query) or die('Query failed: ' . MySQL_error());
If you don’t use these two lines then this connection will live in MySQL server – causing the too many connection error.
// Free resultset
MySQL_free_result($result);
// Closing connection
MySQL_close($link);
Simple way to check what we need to tweak
Open up your phpMyAdmin installation in your browser. Right on the front page is the most valuable link (but most people never even realize it’s there), “Show MySQL runtime information”. Click on that link. Now, the page it takes you to is loaded with all sorts of information. Before I go further into determining what we need to do, let me quickly run down this page (depending on your version of phpMyAdmin). The first “block” is the server traffic. It details how many queries have been sent to the server (and connections). The next block is query statistics. It basically lists the number of each type of query. After that block, comes the really stuff we are interested in. From here out, there are 3 columns (Variable, Value, and Description). Notice that all bad variable values will have the value in red.
Another way to check the status of MySQL server is using shell script. Here us a great shell script http://www.howtogeek.com/howto/linux/using-a-MySQL-performance-tuning-analyzer-script/
Check it again
Now, restart the server. Let it run for a few days (checking with free –m to make sure we’re not using too much memory), and go through the cycle again. Once you have everything listed here sitting nice (where you don’t need to adjust any of the variables), you’re done! You’ll want to do this from time to time as your server load changes and DB changes. You should notice a VAST difference (from a stock install) in MySQL performance.
This configuration is running in my dedicated server with 1.7 GB memory
key_buffer = 300M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 384
max_connections = 400
table_cache = 1800
tmp_table_size = 64M
max_heap_table_size = 64M
max_connect_errors = 1000
wait_timeout = 7200
connect_timeout = 20