Before you start tuning your server for mysql performance, you need to obtain the system information first. To do that, there are some linux command that may be useful.

top command

How to interpret the output from top command? Here you go.

1. The first line is telling you that the system is up 211 days 9 hr and 38 minutes. There are 2 users using the system (from process table, they are mysql and ec users). For the last 1, 5 and 15 minutes, the load of the system shows 0.01, 0.11 and 0.21 respectively.
2. The 2nd line shows you that there are 82 processes running in the system, only 1 of them are active (In the process table, S=R means Running) and 81 of them are in sleeping mode (S = S means sleeping).
3. The 3rd - 5th line are very useful. It gives you the current status of our system. CPU is 99% idle. If it is not, you want to find out who is using it. The process table is sort by %CPU (if you have dual core processor, the % here can reach 200). In term of memory, the total memory in the system is around 4G in total. As you can see, 3.5 G is in used. For our mysql instance, it is using 1.2G of memory that is about 32% of the used memory. Another interesting thing to examine is the swap memory. There is 2G of swap memory allocated in this system and the system barely uses it. It is good because system takes time to swap in and out the data from memory to disk and vice versa.
4. Troubleshooting: When you see mysql process is taking high %CPU - CPU-bound, there may be queries costing intensive cpu cycles. You can look into it via mysql administrative commands. When you see high % of IO wait - ie. IO-bound, you may have inefficient queries that cause mysql reading too many rows to locate the data you are interested in. In system level, you could reduce the # of IO via better use of memory/buffer or reduce the seek time via faster disk and less random data storage.

Other linux commands

1. more /proc/version (check linux os version)
2. more /proc/cpuinfo (for dual processor with speed 2.80MHz: cpu core = 2, processor = 1, cpu MHz = 2.80)

Look under the hood

First you need to understand how mysql uses the memory. Most of the memory mysql allocates is used for various internal buffers and caches. These buffers fall into 2 major groups: global vs per-connection buffers. The 2 most important global buffers are MyISAM key buffer (key_buffer_size) and InnoDB’s buffer pool (innodb_buffer_pool_size). For MyISAM, remember that msyql doesn’t cache row data but the index block. So, the less often mysql needs to hit the disk to scan a table’s index, the faster the queries will be. In practice, OS may somehow help you out via caching some row data in cache. Even this, if possible, consider making the key buffer large enough to hold the indexes for your most actively used tables. By adding up the size of the .MYI files for the tables, you will have a good idea to set the buffer. For InnoDB, both cache index and row data are together in its buffer pool via using the clustered indexes. S0 if the table size is not large, you will achieve the best performance via storing the whole table in cache because you don’t need to do any IO. However, if you modify the data in the table, you need to sync up your disk either per min or per commit.

Allocate enough memory for mysql

min_memory_needed = global_buffers + (thread_buffers * max_connections)

thread_buffers includes:

• sort_buffer
• myisam_sort_buffer
• read_buffer
• join_buffer
• read_rnd_buffer

global_buffers includes:

• key_buffer
• innodb_buffer_pool
• innodb_log_buffer
• innodb_additional_mem_pool
• net_buffer

When there are large number of concurrent users like 300-400, a particular nasty series of events may happen. The reason is large number of threads need to allocate additional memory, it can cause mysql to allocate so much memory that the OS begins swapping, which causes performance to degrade further, which means that each query takes longer to complete. With queries running more slowly, the odds of more threads needing memory increases. It is a vicious spiral. The only solution is to restore balance between the system’s memory and mysql’s memory needs. That means doing one of the following:

1. Add more memory
2. Decrease max_connections
3. Decrease some of the per-thread buffer sizes

Be proactive, monitor memory use on your server.