How to perform VPS host MySQL optimization

Source: Internet
Author: User
Tags one table vps vps hosting

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.