Login to the server and check out your my.cnf file.. To check whether your server is using the correct resourses perform the below commands and checks !!
FYI: Examples are based on 2GHz machine with 1GB of memory
1. table_cache (Eg: table_cache=512)
------------------------------------------------------------------------------------------------------
mysql> show status like '%Open%_tables';
mysql> show variables like 'table_cache';
Run the above two commands and check Open_tables and Opened_tables If Opened_tables is big, then your table_cache variable is probably too small. So increase the table_cache variable. Open /etc/my.cnf and change/add table_cache=newvalue.
------------------------------------------------------------------------------------------------------
2. key_buffer (Eg:key_buffer=128M)
------------------------------------------------------------------------------------------------------
Run the following commands to check key_buffer_size, key_read_requests and key_reads
mysql> show variables like '%key_buffer_size%';
mysql> show status like '%key_read%';
If key_reads / key_read_requests is < 0.01, key_buffer_size is enough. Otherwise key_buffer_size should be increased.
------------------------------------------------------------------------------------------------------
mysql> show status like '%key_write%';
command to check key_write_requests and key_writes, If key_writes / key_write_requests is not less than 1 (near 0.5 seems to be fine), Otherwise increase key_buffer_size.
------------------------------------------------------------------------------------------------------
3. Wait_timeout (Eg:wait_timeout=15), 4.max_connection (Eg:max_connections=200), 5.thread_cache(Eg:thread_cache=32)
------------------------------------------------------------------------------------------------------
If you want to allow more connections, reduce wait_timeout to 15 seconds and increase max_connection as you want.
Check the number of idle connections. If it is too high reduce the wait_timeout and use Thread_cache
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.
++++++++++++++++++++++++++++++++++++++++
[mysqld]
; enable the slow query log, default 10 seconds
log-slow-queries
; log queries taking longer than 5 seconds
long_query_time = 5
; log queries that don’t use indexes even if they take less than long_query_time
; MySQL 4.1 and newer only
log-queries-not-using-indexes
These three settings, used together, log any queries that take longer than 5 seconds and any queries that don’t use indexes.
+++++++++++++++++++++++++++++++++
6. query_cache_size
------------------------------------------------------------------------------------------------------
Each time the query is made, the database must do the same work — parse the query, determine how to execute it, load information from disk, and return it to the client. MySQL has a feature called the query cache that stores the result of a query in memory, should it be needed again. In many instances, this increases performance drastically. The catch, though, is that the query cache is disabled by default.
To Monitor the query cache;
mysql> SHOW STATUS LIKE 'qcache%';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| Qcache_free_blocks | 5216 |
| Qcache_free_memory | 14640664 |
| Qcache_hits | 2581646882 |
| Qcache_inserts | 360210964 |
| Qcache_lowmem_prunes | 281680433 |
| Qcache_not_cached | 79740667 |
| Qcache_queries_in_cache | 16927 |
| Qcache_total_blocks | 47042 |
+-------------------------+------------+
8 rows in set (0.00 sec)
Qcache_free_blocks The number of contiguous memory blocks in the cache. Higher numbers are a sign of fragmentation. FLUSH QUERY CACHE defragments the cache so there’s one free block.
Qcache_free_memory The 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. In the previous example, approximately 87% of the queries are getting served from cache.
Qcache_lowmem_prunes How many times the cache ran out of memory and had to be cleaned up to make room for more queries. This number is best looked at over time; if it’s increasing, it’s a sign that either fragmentation is serious or memory is low (free_blocks and free_memory, above, tell you which it is).
Qcache_not_cached The number of queries that weren’t candidates for caching, usually because they weren’t SELECT statements.
Qcache_queries_in_cache The number of queries (and responses) current cached.
Qcache_total_blocks The number of blocks in the cache.
7.max_connections,8.wait_timeout, 9.max_connect_errors
------------------------------------------------------------------------------------------------------
You should enforce a few limits in mysqld to ensure that the system load doesn’t cause resource starvations. Listing 3 shows some important resource-related settings from my.cnf.
set-variable=max_connections=500
set-variable=wait_timeout=10
max_connect_errors = 100
To determine the maximum number of connections your server has seen so far, execute SHOW STATUS LIKE ‘max_used_connections’.
The second line tells mysqld to terminate any connections that have been idle for more than 10 seconds.
If a host has problems connecting to the server and ends up aborting the request too many times, the host is locked until FLUSH HOSTS can be run. By default, 10 failures are enough to cause blocking.Changing this value to 100 gives the server enough time to recover from whatever problems it has. Using a higher value doesn’t help you much because if the server can’t connect once in 100 tries, chances are it’s not going to connect at all.
10. table_cache
------------------------------------------------------------------------------------------------------
Each table is represented as a file on disk and must be opened before it can be read. To speed up the process of reading from the file, mysqld caches these open files up to the limit specified by table_cache in /etc/mysqld.conf.
mysql> SHOW STATUS LIKE 'open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 5000 |
| Opened_tables | 195 |
+---------------+-------+
shows that 5,000 tables are currently open and that 195 tables had to be opened because there was no available file descriptor in the cache
If Opened_tables increases quickly as you rerun the SHOW STATUS command, you aren’t getting enough hits out of your cache. If Open_tables is much lower than your table_cache setting, you have too many (some room to grow is never a bad thing, though). Adjust your table cache with table_cache = 5000,
11. thread_cache
------------------------------------------------------------------------------------------------------
mysql> SHOW STATUS LIKE 'threads%';
+-------------------+--------+
| Variable_name | Value |
+-------------------+--------+
| Threads_cached | 27 |
| Threads_connected | 15 |
| Threads_created | 838610 |
| Threads_running | 3 |
+-------------------+--------+
The important value here is Threads_created, which is incremented each time mysqld has to create a new thread. If this number increases quickly between successive SHOW STATUS commands, you should look at increasing your thread cache. You do this with thread_cache = 40,
12. key_buffer
------------------------------------------------------------------------------------------------------
mysql> show status like '%key_read%';
+-------------------+-----------+
| Variable_name | Value |
+-------------------+-----------+
| Key_read_requests | 163554268 |
| Key_reads | 98247 |
+-------------------+-----------+
Key_reads represents the number of requests that hit disk, and Key_read_requests is the total number. Dividing the reads by the read requests gives the miss rate — in this case, 0.6 misses per 1,000 requests. If you’re missing more than 1 per 1,000 requests, you should consider increasing your key buffer. key_buffer = 384M
Below are notes on some of the important variables,.
query_cache_size:
MySQL 4 provides one feature that can prove very handy - a query cache. In a situation where the database has to repeatedly run the same queries on the same data set, returning the same results each time, MySQL can cache the result set, avoiding the overhead of running through the data over and over and is extremely helpful on busy servers.
key_buffer_size:
The value of key_buffer_size is the size of the buffer used with indexes. The larger the buffer, the faster the SQL command will finish and a result will be returned. The rule-of-thumb is to set the key_buffer_size to at least a quarter, but no more than half, of the total amount of memory on the server. Ideally, it will be large enough to contain all the indexes (the total size of all .MYI files on the server).
A simple way to check the actual performance of the buffer is to examine four additional variables: key_read_requests, key_reads, key_write_requests, and key_writes.
If you divide the value of key_read by the value of key_reads_requests, the result should be less than 0.01. Also, if you divide the value of key_write by the value of key_writes_requests, the result should be less than 1.
table_cache:
The default is 64. Each time MySQL accesses a table, it places it in the cache. If the system accesses many tables, it is faster to have these in the cache. MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table. Examine the value of open_tables at peak times. If you find it stays at the same value as your table_cache value, and then the number of opened_tables starts rapidly increasing, you should increase the table_cache if you have enough memory.
sort_buffer:
The sort_buffer is very useful for speeding up myisamchk operations (which is why it is set much higher for that purpose in the default configuration files), but it can also be useful everyday when performing large numbers of sorts.
read_rnd_buffer_size:
The read_rnd_buffer_size is used after a sort, when reading rows in sorted order. If you use many queries with ORDER BY, upping this can improve performance. Remember that, unlike key_buffer_size and table_cache, this buffer is allocated for each thread. This variable was renamed from record_rnd_buffer in MySQL 4.0.3. It defaults to the same size as the read_buffer_size. A rule-of-thumb is to allocate 1KB for each 1MB of memory on the server, for example 1MB on a machine with 1GB memory.
thread_cache:
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. This should take some of the load off of the CPU.
tmp_table_size:
"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 all the time.