Optimizing MySQL Server

mysql| Server | Optimizing the MySQL server has several parameters (variables) that affect its operation. If the default parameter value is not appropriate, you can modify it to a value that is more appropriate for the server running environment. For example, if you have a large amount of memory, you can tell the service to use a larger buffer for disk and index operations. This will enable memory to hold more information and reduce the number of disk accesses that must be made
Amount If it is a generic system, you can tell the server to use a smaller buffer to prevent it from disrupting the system's resources and damaging other processes.
The current value of the system variable can be checked by executing the mysqladmin variables command. Variable available--set-variable var_name = value option is set on the command line (-ovar_name = value is equivalent). If you want to set several variables, you can use multiple--set-variable options, and you can use the following syntax to set a variable in the [Mysqld] group of an option file:
Set-variale=var_name=value
A full list of server variables is given under the terms of the MySQL program in Appendix E. Variables that are commonly used for performance tuning are given in the following list. You can also find additional discussion of this topic in the MySQL reference manual, "get the highest performance from MySQL" chapter.
The number of client connection requests introduced by Back_log, which are queued when processing from the current client. If you have a busy site, you can increase the value of the 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 will block until there is room in the queue, which prevents the client from publishing those statements from continuing the operation. If you have many clients that execute this INSERT and you find that they are blocking, increase the variable so that more clients work faster (insert D E L ayed in 4). Section 5, "Scheduling and locking issues" in the discussion)
Flush_time if the system has problems and is often locked or rebooted, set the variable to a value other than 0, which causes the server to flush the table cache by flush_time seconds. Writing changes to a table in this way reduces performance, but reduces the chance of table corruption or data loss.
In Windows, you can start the server with the--flush option on the command line to force changes to the table to be refreshed after each update.
Key _ Buffer_size is used to store the size of the index block buffer. If you increase the value of the variable, the time to create and modify the index operation is accelerated. The greater the value MySQL is more likely to find key values in memory, which will reduce the number of disk accesses required for index processing.
In the previous version of MySQL3.23, the variable was named key-buffer. The MySQL3.23 and later versions recognize the two names at the same time.
The maximum size of the buffer used by the Max_allowed_packet client communication. If a client wants to send a large number of BLOB or text values, the server variable value may need to be increased.
The client currently uses a default buffer size of 24MB. If you have an old client that uses a smaller buffer. You may need to make the client's buffer larger. For example, MySQL can be used to specify a limit of 2 4 MB packets as Cut:
Mysql--set-varibale max_allowed_packet=24m
The maximum number of simultaneous connections allowed by the Max_connections server. If the server is busy, you may need to increase the value. For example, if your MySQL server is used by a Web server to handle queries generated by DBI or PHP scripts, and there is a lot of web traffic, if the variable is set too low, visitors to your site will find the request rejected.
The size of the cache for the Table_cache table. Adding this value allows mysqld to maintain more tables while opening and reducing the number of file opening and closing operations that must take place.
If you increase the size of the max_connections or Table_cache value, the server will require a large number of file descriptors. This will cause problems with the operating system qualifying the total process of the file descriptor, in which case you need to increase the limit or resolve it gradually. Because the procedure changes as you increase the limit on the number of file descriptors, you might do so when you use the Ulimit command in a run script that can be used to start the server or reconfigure your system. Some systems can be simply configured and rebooted by editing the system description file. For some other systems, you must edit a kernel description file and rebuild the kernel. How to proceed, please refer to your system's documentation.
One way to resolve the total process file descriptor restriction is to separate the data directory into multiple data directories and run multiple servers. This makes the number of available descriptors multiply by running multiple servers. But on the other hand, other complex factors may cause problems. Because you have named two servers, you cannot access different numbers from a single server
Depending on the database in the directory, you also need to copy authorization table permissions between different servers so that users need to access more than one server.
There are two variables that administrators often add 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. In other words, each client gets its own buffer. If the values of these variables are large, performance may suffer actual losses due to expensive system resource consumption. If you want to modify these variables, first perform mysqladmin variables look at their current values and then increment their values. This operation allows you to estimate the effect of the modifications that are made to reduce severe performance degradation.