MySQL database optimization on VPS host _ MySQL

Overview: configure the mysql database on the linuxvps host. because the InnoDB engine is started using the database installed in yum by default, and its etcmycnf configuration is not very suitable for our vps host, this article explains in detail how to configure the mysql configuration file suitable for your vps host. objective: Overview:Configure the mysql database on the linux vps host. because the InnoDB engine is started using the database installed in yum by default, its/etc/my. cnf configuration is not very suitable for our vps host. This article describes in detail how to configure the mysql configuration file suitable for your vps host.

Purpose:Optimize the mysql database on the vps host to make it more suitable for environments such as the vps host.

query_cache_size = 268435456query_cache_type=1query_cache_limit=1048576

Command for viewing static mysql-server parameter values
Mysql> show variables;
Or mysqladmin-uroot variables
Mysql server has many parameters. if you need to know the detailed definition of a parameter, you can use mysqld-verbose-help | more
Command for viewing mysql-server dynamic status information

Mysql> show status;

Or mysqladmin-uroot status
Show status is divided into show [session | global] status. you can add the parameter "session" or "global" as needed to display the session level (current statistics) and the global-level statistical results (since the last time the database was started). If no data is written, the default value is "session"

1. view and modify the default storage engine and disable unnecessary storage engines.
When we use a vps host, many users only use the MyISAM engine and close other engines such as InnoDB MEMORY that are not in use.
View the engines of the current system

Mysql> show engines \ G or mysql> show variables like 'have % ';

View default engine

Mysql> show variables like 'Table _ type '; + --------------- + -------- + | Variable_name | Value | + --------------- + -------- + | table_type | MyISAM | + --------------- + -------- + 1 row in set (0.00 sec) or mysql> show variables like 'Storage _ engine '; + ---------------- + -------- + | Variable_name | Value | + ------------------ + -------- + | storage_engine | MyISAM | + ---------------- + -------- + 1 row in set (0.00 sec)

The default storage engine of mysql is MyISAM. you can modify the default storage engine to/etc/my. in the cnf parameter file, add default-storage-engine = innodb in the [mysqld] field. assume that the default value is innodb, and set it based on your needs to disable unnecessary storage engines, you can modify/etc/my. in the cnf parameter file, add-skip-innodb in the [mysqld] field.
2. adjust the key_buffer_size.

Mysql> show variables like 'key _ buffer_size '; + rows + ----------- + | Variable_name | Value | + ----------------- + ----------- + | key_buffer_size | 402653184 | + ----------------- + --------- + 1 row in set (0.00 sec) mysql> can be seen from above, key_buffer_size: 384 M, which can be changed to/etc/my. in the cnf parameter file, add key_buffer_size = 384 M in the [mysqld] field. of course, you can also use mysql> set grobal key_buffer_size = M to modify it. This parameter is usedSet index blocks(Index Blocks) the cache size, which is shared by all threads and is only suitable for the MyISAM storage engine.

3. adjust table_cache settings
This parameter indicates the number of table caches opened by database users. each connection will open at least one table cache. in this way, table_cache is related to max_connections,
View the current table_cache value

mysql> show variables like 'table_cache';+---------------+-------+| Variable_name | Value |+---------------+-------+| table_cache   | 4096  |+---------------+-------+1 row in set (0.00 sec)

As shown above, table_cache is 4096. you can modify the/etc/my. cnf parameter file and add table_cachee = 4096 in the [mysqld] field.

4. adjust query_cache settings

Query Cache plays an important role in improving database performance, and its configuration is very simple. you only need to write two rows in the configuration file: query_cache_type and query_cache _ size, and MySQL's query cache is very fast! In addition, once hit, it is directly sent to the client, saving a lot of CPU time.

Of course, non-SELECT statements have an impact on the buffer, and they may cause the data in the buffer to expire. Modifications to some tables caused by an UPDATE statement will invalidate all the buffered data in the table. this is a measure that MySQL has not taken to balance performance. Because if you need to check the modified data for each UPDATE, and then withdraw some of the buffer, the complexity of the code will increase.

Query_cache_type: 0 indicates that no buffer is used. 1 indicates that the buffer is used, and 2 indicates that the buffer is used as needed.

Setting 1 indicates that the buffer is always valid. If no buffer is required, use the following statement:

SELECT SQL _NO_CACHE * FROM my_table WHERE...

If it is set to 2, you can use the following statement to enable Buffering:

SELECT SQL _CACHE * FROM my_table WHERE...

You can use show status to view the buffer STATUS:

mysql> show status like 'Qca%';+-------------------------+----------+| Variable_name | Value |+-------------------------+----------+| Qcache_queries_in_cache | 8 || Qcache_inserts | 545875 || Qcache_hits | 83951 || Qcache_lowmem_prunes | 0 || Qcache_not_cached | 2343256 || Qcache_free_memory | 33508248 || Qcache_free_blocks | 1 || Qcache_total_blocks | 18 |+-------------------------+----------+8 rows in set (0.00 sec)

To calculate the hit rate, you need to know the number of SELECT statements executed by the server:

mysql> show status like 'Com_sel%';+---------------+---------+| Variable_name | Value |+---------------+---------+| Com_select | 2889628 |+---------------+---------+1 row in set (0.01 sec)

In this example, MySQL hits 2,889,628 of the 83,951 queries, and there are only 545,875 INSERT statements. Therefore, there is a big gap between the two and the total query of 2.8 million. Therefore, we know that the buffer type used in this example is 2.

In the example of type 1, the value of Qcache_hits is much greater than that of Com_select. Qcache_not_cached records the number of DML statements.