The max_connections MySQL server configuration variables sets the maximum concurrent connections allow to the database server. If there is not enough connections for all the clients to connect, an error message, “too many connects”, is sent back the client and connection is refused. While the max_connections variable dictiated how many connections are allowed to connect to the server, all the variables list bellow should be configured and tuned to maximize the server connection performance.
- Max_Connections
- Max_user_connections
- Table_open_caches (table_cache)
- Open_files_limit
- thread_cache_size
The max_user_connections variable sets the maximum number of simultaneous connections allowed to any given MySQL user account. The default is set to 0 which means “no limit.” This variable has a global value that can be set at server startup or runtime. It also has a read-only session value that indicates the effective simultaneous-connection limit that applies to the account associated with the current session. The session value is initialized as follows:
· If the user account has a nonzero MAX_USER_CONNECTIONS resource limit, the session max_user_connections value is set to that limit.
· Otherwise, the session max_user_connections value is set to the global value.
The table_open_cache (table_cache for MySql version before 5.1.3) sets the vaules for the number of open table for all threads that the server allows. The default values is 64. Increase this values will increase the number of file descriptors that MySql requires. It is important to make sure that the OS can handle the open file desriter set by the table_opne_cache variable. If the values of table_open_cache is set to too high a value, the MySql server may run out of file descriptots and refuse connections, fail to excute queries, and has unreliable behavior.
The open_file_limit variable sets the number of open file descriptors the operating system allows to open.
The thread_cache_size variable sets the amount of threads will be cached after a connection disconeects for the purpose of maintaining memory for new connection. When a client disconnects, the client's threads are put in the cache if there are fewer than thread_cache_size threads there. Requests for threads are satisfied by reusing threads taken from the cache if possible, and only when the cache is empty is a new thread created.
1.Setting Variables
To determind what value to be set for each variable, we should start by getting some statistics from the current system. If it is going to be a newly deploy system, we should do some scientifit extimation for each values and mornitoring the system for a few days and correct the statistics from the running system, then, change the configuration values as needed.
To check server status from mysql prompt do the following:
mysql>show status \G;
The about commond will display all the status variables. You can also choose to display a sub set of the status variable by using the ‘Like’ clause.
mysql> show global status like '%connection%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Connections | 76 |
| Max_used_connections | 5 |
+----------------------+-------+
2 rows in set (0.00 sec)
mysql> show global status like 'open%table%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Open_table_definitions | 77 |
| Open_tables | 4 |
| Opened_table_definitions | 73 |
| Opened_tables | 11531 |
+--------------------------+-------+
4 rows in set (0.02 sec)
To set server variables, use the SET GLOBAL variable_name=value command in the mysql prompt:
mysql> set global max_connections=300;
Query OK, 0 rows affected (0.00 sec)
Please remember to update the my.cnf file with the updated variable values so the server will start with the new variables in the next restart.
1.1 Getting Server Status
First, check the following server status variables are related to server connections.
· Connections
· Max_used_connections
· Open_tables
· Opened_tables
· Opened_table_definitions
· Open_files
· Threads_cached
· Threads_connected
· Threads_created
· Threads_running
Connections status show the number of connections attemts (successful or fail) to the MySql server since the server started.
Max_used_connections status show the maximum number of connections that have been used simultaneously since the server started. This maximum number this status variable will show is connections + 1. Therefore the connections system variable should be set to above this status number and increased when this status number increased. It is a good idead to set up an alert to monitor the Max_used_connections status variable and proactively change the connections system variable value when the status variable value is close to the connections value.
Open_tables status show the number of currently open tables. A single table open 10 times will add 10 to open_table status. Since mysql is a multi-threaded system, each running quering will open one or more tables, same or different. Truce, open_tables status values will much larger that the total physical number of table a database has. If the open_tables status return a large number, it is a sign that table_cache is not big enough.
Opened_tables status show the number of tables that have been opened. If opend_tables is big, it means that not all the tables are being cached; therefore, table_cache value need to be increased.
Opened_table_definitions status (MySql v5.1.24 or later) show the number of .frm files (table definitions) that have been cached.
Open_files status shows the number of open files descriptors that are currently active.
Threads_cache status shows the number of threads in the thread_cashe
Thread_connected status shows the number of currently opend connections.
Thread_created status shows the number of threads created to handle connections. If the Thread_created status values is big or close to the number of the thread_cache_size server variable, then we should consider to increase the thread_cache_size server variable value. It is good to evaluate the cache miss rate from time to time using the following formular:
Theads_created / Connections.
One way to check if the thread_cache_size is large enough is to gradutely increase it value until the thread_created status value increases increase at a low rate. Thread connection overhead is quite low on linux system.
Thread_running status shows the number if threads that are currently active and not sleeping.
Show Processlist command can also be used (with PROCESS privilege) to obtain the total running processes.
It is also worth to take a look the difference between a process and a thread and how MySQL utilizes threads. A processe has their own heap (memory space), whereas threads share this space. When a new process is created, the operating system has to create a new address space for it to execute a set of instructions with full control over various resources to complete the tasks. On the other hand, threads work under the address space given to its parent process and can change state without having to create a new process. MySQL is a single process multi-threads architecture (a thread-based server architecture) which allows the various executing threads (or lightweight processes) to access key shared resources.
If MySQL is running on Linux, we should also check the open file descriptors (sockets). To check current open file descriptors limits, use the following command:
#>cat /proc/sys/fs/file-max
8192
The above example show that the max open file discriptors is 8192.
We should also check open file limit per shell:
#> ulimit –aH
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
pending signals (-i) 1024
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 77824
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
In the above example, the open file limit is 1024.
1.2 Setting the values
The following server variables are related to connections which should be mornitored and tuned reqularly.
· Max_connections
· Max_user_connections
· Table_open_cache (table_cache for MySQL version prior to 5.1.3)
· Table_definition_cache
· Open_files_limit
· thread_cache_size
The max_connections variable values is default to 100. We should check the max_used_connections status often to reset the connections values when the max_connections status values is closed to the connections variable values.
The Max_user_connections variable values is default to 0 which means unlimit. Nomally, this variable should set to a few connections less than the max_connections. This way, you will always have a few connections reserve for the supper user, root. For example, if your max_connections was set to 500, and you have three application users for three applications to connect to the database, the total connections (max_user_conections) should set to less than 500, say 480.
The table_open_cache variable values is default to 64. We should monitoring the opend_tables status variable frequenry to see if the number are going up constanly. If the opened_tables status going up more than 1 per every 10 minutes on average, then we need to increase the table_open_cache variable values.
The table_definition_cache set the number of table definitions that can be stored in the definition cache. If you use a large number of tables, you can create a large table definition cache to speed up opening of tables. The table definition cache takes less space and does not use file descriptors, unlike the normal table cache. This variable was added in MySQL 5.1.3. The minimum and default values are 1 and 128 before MySQL 5.1.25. The minimum and default are both 256 as of MySQL 5.1.25.
The open_files_limit variable changes the number of file descriptors available to mysqld. You should try increasing the value of this option if mysqld gives you the error Too many open files and preven connections in the error log, it means that the requested number of file descriptors cannot be allocated; therefore, it is time to increase the open_files_limit variable.
The Thread_cache_size variable is default to 8. We should check the threads_created status variable to ensure that this status values is not increased in a high value constantly. Please note that thread_created will show a large numbers if the the thread_cache_size number was not set or set to a small value when the server started because very few threads were cahed. It is not nesccessary to set the thread_cache_size value to the same value as thread_created. We should start by setting the value of thread_cache_size to the values of threads_coonected or the total number of prcesses from show proccesslist command. And, continue monitor the system and increase the value only if the thread_created status value increase in a hight rate more.
In addition to setting the MySQL server variables, we should also set the linux open file descriptor limits to make sure the there is enough file descriptors for MySQL to use. To change the file-max values to 65535, do the following:
# echo "65535" > /proc/sys/fs/file-max
We should also add or change this new value in /etc/sysctl.conf so the server will boot up with this value if it ever need to reboot.
# Maximum number of open files permited
fs.file-max = 65535
To load new values from the sysctl.conf file, do the following:
% sysctl -p /etc/sysctl.conf
Also, we should incress this to the mysql user if you use mysql user to run your server (do it as root):
# vi /etc/security/limits.conf
Modify or add "nofile" (number of file) entries (if the server is run by root, replace mysql with a *.
mysql soft nofile 65535
mysql hard nofile 65535
And add the following to the /etc/pam.d/login file:
# vi /etc/pam.d/login
[Add the line]
session required /lib/security/pam_limits.so
2. Monitoring
We should continue monitoring the status variables, which were mentioned in section 7.2.2 above, to make sure all the server variables were set accurately. Again, performance changes over time. One set of the serser variables work well now will not guarantee will work well three months later. Past performance dose not guarantee future results, it is true for the stock market; it is true for server performance as well.
References:
- MySQL Manual: MySQL Server System Variables
- Understanding MySQL Internals: Chapter 5, Configuration Variables
- High Performance MySQL: Chapter 6, Optomizing Server Settings
- Other Reference On the Web
----------------------------------
Post Update History:
2010-03-28: Initial post. Draft from initial tuning note.
2010-05-06: Added Reference and Related Posts
Posts
