Mysql server Optimization Method

You can run the mysqladmin variables command to check the current value of the system variable. You can use the--set-variable var_name = value option to set a variable in the command line (-ovar_name = value is equivalent ). To set several variables, you can use multiple -- set-variable options. You can also use the following syntax to set variables in the [mysqld] group of an option file:
Set-variale = var_name = value
All the server variables are listed under the mysql program terms in appendix E. Variables that are commonly used for performance optimization are listed below. You can also find additional discussions on this topic in the chapter "obtain the highest performance from MySQL" in the MySQL reference manual.
Number of client connection requests introduced by back_log. These requests are queued during processing from the current client. If you have a busy site, you can increase the value of this variable.
Delayed_queue_size this variable controls the number of rows in the queued insert delayed statement. If the queue is full, more insert delayed statements will be blocked until the queue has space. This prevents the clients that publish the statements from continuing the operation. If you have many clients that execute such INSERT statements and find they are congested, you should add this variable to make more clients work faster (insert d e l ayed in 4. 5)
Flush_time if the system is faulty and often locked or rebooted, set this variable to a non-zero value, which will cause the server to refresh the table's cache in flush_time seconds. Writing table modifications in this way reduces the performance, but reduces the chance of table loss or data loss.
In Windows, you can use the -- flush option on the command line to start the server to force the modification of the table to be refreshed after each update.
Key _ buffer_size is used to store the size of the index block buffer. If the variable value is added, the time for creating and modifying indexes is accelerated. The larger the value, the more likely MySQL will find the key value in the memory, which will reduce the number of disk accesses required for index processing.
In versions earlier than MySQL 3.23, the variable name is key _ buffer. Both MySQL3.23 and later versions recognize these two names at the same time.
The maximum buffer size used for max_allowed_packet client communication. If a client needs to send a large number of BLOB or TEXT values, the variable value of this server may need to increase.
The client currently uses a default buffer of 24 MB. If there is an old client that uses a small buffer. The buffer size of the client may be larger. For example, mysql can specify the limit value of a 2 4 MB information package as follows:
Mysql -- set-varibale max_allowed_packet = 24 M
The maximum number of concurrent connections allowed by the max_connections server. This value may be added if the server is busy. For example, if your MySQL server is used by the Web server for queries generated by DBI or PHP scripts and there is a large amount of Web communication, if this variable is set too low, then, visitors to your site will find that the request is rejected.
The cache size of the table_cache table. Adding this value can keep mysqld more tables and reduce the number of file opening and closing operations required.
If the max_connections or table_cache value is added, the server requires a large number of file descriptors. This will cause the operating system to limit the total number of processes in the file descriptor. In this case, you need to increase the limit or gradually solve it. Due to the increase of the limit on the number of file descriptors, the process will change. Therefore, you may use the ulimit command in a running script to start the server, or to reconfigure your system. Some systems can simply configure and reboot by editing the system description file. For other systems, you must edit a kernel description file and recreate the kernel. For more information about how to proceed, see your system documentation.
One solution to the limitation of the Process file descriptor is to separate the Data Directories into multiple data directories and run multiple servers. In this way, the number of available descriptors doubles by running multiple servers. On the other hand, other complex factors may cause problems. Because two servers are named, you cannot access different numbers from a single server
Database in the data directory, and the permission to copy the authorization table between different servers, so that you need to access more than one server.
Two variables are frequently added by administrators to improve performance. They are record _ buffer and sort _ buffer. These buffers are used in Connection and classification operations, but their values belong to each connection. That is to say, each client obtains its own buffer. If the value of these variables is large, the performance may suffer actual losses due to the consumption of expensive system resources. If you want to modify these variables, run mysqladmin variables to check their current values, and then incrementally adjust their values. This operation allows you to estimate the effect of modifications to reduce serious performance degradation.