Server optimization and hardware optimization _ MySQL

Server optimization and hardware optimization tips: Server optimization principle: the data in the memory is faster than the data on the disk; keeping the site data in the memory for as long as possible can reduce the workload of disk read/write activities; keeping the index information in the memory is more important than keeping the data record content in the memory. To speed up data running, upgrading hardware is the most direct solution.

1. server optimization
Optimization principles:

The data in the memory is faster than the data on the disk;

Keeping the site data in the memory for as long as possible can reduce the workload of disk read/write activities;

It is more important to keep the index information in the memory than to keep the data record content in the memory.

In view of the above principles, we should adjust the server:

Increase the cache capacity of the server so that the data remains in the cache for a longer period of time to reduce disk I/0. The following describes several important buffers:

The data table buffer contains the information of the opened data table. its size can be set by the server parameter "table_cache. The Opened_tables parameter records how many times the server has opened a data table. if this value changes a lot, it may be that the data table buffer is full and some infrequently used tables need to be removed from the buffer zone, to open a new data table. Run the following command to view Opened_tables values:

Show status like 'opened _ tables ';
In MyISAM and ISAM data tables, the index is cached in the "key buffer", and its size is controlled by the server parameter "key_buffer_size. The default size of the system is 8 MB. if the memory is sufficient, you can expand the value to cache more index blocks in the zone to speed up the index.

The InnoDB and BDB data tables also have a buffer zone called innodb_buffer_pool_size and bdb_cache_size. InnoDB also has a log buffer named innodb_log_buffer_size.

MySQL has a buffer zone named query buffer since 4.0.1. it is mainly used to store the text and results of repeated queries. when the same query is encountered again, the server returns results directly from the buffer zone. This function is a built-in function. if you do not want to support this function, you can use the -- without-query-cache option of configure script when compiling the server.

The query buffer is controlled by three server parameters:

1. query_cache_size
Control the buffer size. if this value is 0, the query buffer function is disabled. The setting method is set in the option file:
[Mysqld]
Set-variable = query_cache_size = 16 M
In this way, a 16 m query buffer is set.

2. query_cache_limit
The maximum capacity (in bytes) of the buffered result set. if the queried result set is larger than this value, this value is not buffered.

3. query_cache_type
The operation mode of the buffer.
0 indicates no buffer;
1 indicates that all queries starting with SELECT SQL _NO_CACHE are buffered;
2 indicates that only queries starting with SELECT SQL _ON_CACHE are buffered.
By default, the server is buffered according to the server settings, but the client can also use commands to change the server settings. The client can directly use the SELECT SQL _NO_CACHE and SELECT SQL _CACHE commands to request the server to buffer or not buffer the query results. If you do not want to write parameters for each query, you can also use SET SQL _QUERY_CACHE_TYPE = val; on the client side to change the server's query buffer behavior. Val can be 0, 1, 2, OFF, ON, or DEMAND.

Disable unnecessary data table handlers. If the server is created from the source code, you can completely disable ISAM, InnoDB, and BDB data tables.

The permission relationship in the permission table should be as simple as possible. of course, it is necessary to ensure security.

When creating a server from the source code, try to use a static library instead of a shared library for configuration. Static databases are faster to execute. However, if you want to load user-defined functions (UDFs), you cannot use static databases because UDFs must rely on dynamic libraries.

2. hardware optimization
To speed up data running, upgrading hardware is the most direct solution. In view of the characteristics of database applications, you should consider the following when upgrading the hardware:

For database servers, memory is the most important factor affecting performance. By increasing the memory, the database server can save more data in the buffer, greatly reducing disk I/O, thus improving the overall performance of the database.

Configure a high-speed disk system to reduce the wait time for reading a disk and increase the response speed.

Disk I/O should be reasonably distributed across multiple devices to reduce resource competition and improve parallel operation capability.

Configure a multi-processor. MySQL is a multi-threaded Database. a multi-processor can execute multiple threads at the same time.