How to perform VPS host MySQL optimization

Many times, in some cases, due to the increasing number of Web site data, resulting in sometimes, our website access speed has become very slow, a lot of people usually think that the site is a problem, or poisoning ah, is caused by the attack, in fact, a lot of time, Just because we have not been able to optimize our VPS host on the MySQL database caused by. This article explains in detail how to configure the MySQL configuration file for your VPS host
　　
Optimizing the MySQL database on VPS hosts is designed to make it more suitable for environments such as VPS hosting
　　
query_cache_size=268435456
　　
Query_cache_type=1
　　
query_cache_limit=1048576
　　
View mysql-server static parameter value command
　　
mysql>showvariables;
　　
or Mysqladmin-urootvariables.
　　
MySQL server has many parameters, if you need to know a detailed definition of a parameter, you can use the Mysqld–verbose–help|more
　　
View mysql-server Dynamic Status information command
　　
mysql>showstatus;
　　
or Mysqladmin-urootstatus.
　　
Where Showstatus is divided into show[session|global]status; You can add the parameter "session" or "global" as needed To show the statistical results of the session level (current statistics) and the statistics of the global level (since the database was last started), the default is "session" if not written
　　
1. View and modify the default storage engine to close the unwanted storage engine
　　
In our use of VPS host process, many users only use MyISAM one engine, close so no use of innodbmemory and other engines
　　
See which engines are currently in the system
　　
Mysql>showengines\g
　　
or the second method
　　
Mysql>showvariableslike ' have% ';
　　
View the default engine
　　
Mysql>showvariableslike ' Table_type ';
　　
+---------------+--------+
　　
| variable_name| value|
　　
+---------------+--------+
　　
|table_type| myisam|
　　
+---------------+--------+
　　
1rowinset (0.00SEC)
　　
Or
　　
Mysql>showvariableslike ' Storage_engine ';
　　
+----------------+--------+
　　
| variable_name| value|
　　
+----------------+--------+
　　
|storage_engine| myisam|
　　
+----------------+--------+
　　
1rowinset (0.00SEC)
　　
MySQL default storage engine is MyISAM, modify the default storage engine can modify the/ETC/MY.CNF parameter file, add Default-storage-engine=innodb in the [mysqld] field, here assume that the default setting is InnoDB, According to your own needs to set up, close the unwanted storage engine, you can modify the/etc/my.cnf parameter file, in the [Mysqld] field to add –skip-innodb.
　　
2, adjust the key_buffer_size size
　　
Mysql>showvariableslike ' key_buffer_size ';
　　
+-----------------+-----------+
　　
| variable_name| value|
　　
+-----------------+-----------+
　　
|key_buffer_size|402653184|
　　
+-----------------+-----------+
　　
1rowinset (0.00SEC)
　　
Mysql>
　　
As can be seen from the above, Key_buffer_size is: 384M, you can modify the/ETC/MY.CNF parameter file, add key_buffer_size=384m in the [mysqld] field, of course, you can also use mysql> SETGROBALKEY_BUFFER_SIZE=512M modified.
　　
This parameter is used to <strong> set the size of the index block </strong> (indexblocks) cache, which is shared by all threads and is only suitable for MyISAM storage engines.
　　
3. Adjust Table_cache settings
　　
This parameter indicates the number of caches that the database user has opened the table, and each connection will open at least one table cache, so that Table_cache is related to Max_connections,
　　
View the current Table_cache value
　　
Mysql>showvariableslike ' Table_cache ';
　　
+---------------+-------+
　　
| variable_name| value|
　　
+---------------+-------+
　　
|table_cache|4096|
　　
+---------------+-------+
　　
1rowinset (0.00SEC)
　　
As can be seen from the above, Table_cache is: 4096, you can modify the/etc/my.cnf parameter file, in the [Mysqld] field to add table_cachee=4096
　　
4. Adjust Query_cache settings
　　
Querycache plays a very important role in improving database performance, and its settings are simple enough to write only two lines in the configuration file: Query_cache_type and Query_cache_size, and the querycache of MySQL is very fast! And once hit, it is sent directly to the client, saving a lot of CPU time.
　　
Of course, non-SELECT statements have an impact on buffering, which may cause the data in the buffer to expire. A partial table modification caused by an UPDATE statement will invalidate all buffered data for the table, which is a measure that MySQL has not taken to balance performance. http://vps.zzidc.com/because, if each update needs to examine the modified data, then withdrawing the partial buffer will result in increased complexity of the code.
　　
The QUERY_CACHE_TYPE:0 representative does not use buffering, 1 represents the use of buffering, and 2 is used as needed.
　　
Setting 1 for buffering is always valid, and if buffering is not required, use the following statement:
　　
Selectsql_no_cache*frommy_tablewhere ...
　　
If set to 2, you need to turn on buffering, you can use the following statement:
　　
Selectsql_cache*frommy_tablewhere ...
　　
Use Showstatus to view the buffering situation:
　　
Mysql>showstatuslike ' 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|
　　
+-------------------------+----------+
　　
8rowsinset (0.00SEC)
　　
If you need to calculate the hit ratio, you need to know how many SELECT statements the server executes:
　　
Mysql>showstatuslike ' com_sel% ';
　　
+---------------+---------+
　　
| variable_name| value|
　　
+---------------+---------+
　　
| com_select|2889628|
　　
+---------------+---------+
　　
1rowinset (0.01SEC)
　　
In this example, MySQL hits 83,951 of the 2,889,628 queries, and the INSERT statement has only 545,875. Therefore, there is a big gap between them and the total of 2.8 million queries, so we know that this example uses a buffer type of 2.
　　
In the case of type 1, the value of qcache_hits is much larger than Com_select. Qcache_not_cached is the number of DML statements that can be recorded.

Want to know more detailed VPS host, how to more accurate MySQL how to configure and other related methods, you can read the relevant article: VPS Host Configuration Iis+php+mysql+zend Tutorial Full solution

How to perform VPS host MySQL optimization