A netizen's experience in MySQL database Optimization

We know thatMySQL database OptimizationThere are many methods on the Internet. This article mainly introduces the optimization experience of a MySQL database user, hoping to help you.

Optimization experience:

Run the show variables command to view the variable value of max_connections for the maximum number of connections in the database.

Run the command: show status; to view the currently active connection thread variable value: threads_connected.

Set the new maximum number of connections to 2000: mysql> set GLOBAL max_connections = 2000;

The maximum number of connections allowed by the actual MySQL server is 16384; the value of tmp_table_size is the default 32 M, and the value of tmp_table_size is changed to 200 M:

Tmp_table_size = 33554432; // The default unit is byte.

Mysql> set tmp_table_size = 209715200; these seem to work temporarily! I am using xampp so to D: \ xampp \ mysql \ bin

Although I have a my. ini file in C: \ windows, I found that the parameter was changed. It seems that I didn't work. Finally, I found that using editplus to open my here is easy to use!

 
 

  
  
[Mysqld]
  
  
Port = 3306
  
  
Socket = "D:/xampp/mysql. sock"
  
  
Basedir = "D:/xampp/mysql"
  
  
Tmpdir = "D:/xampp/tmp"
  
  
Datadir = "D:/xampp/mysql/data"
  
  
Skip-locking
  
  
Key_buffer = 16 M
  
  
Max_allowed_packet = 1 M
  
  
Table_cache = 64
  
  
Sort_buffer_size = 512 K
  
  
Net_buffer_length = 8 K
  
  
Read_buffer_size = 256 K
  
  
Read_rnd_buffer_size = 512 K
  
  
Myisam_sort_buffer_size = 8 M
  
  
Max_connections = 2000
  
  
Tmp_table_size = 209715200
  
  
Long_query_time = 2
  
  
# Enter a name for the query log file. Otherwise a default name will be used.
  
  
# Note: writing the txt file editplus can be reloaded in time, but it can be reloaded only when it is stored on drive C)
  
  
Log = c:/mysql_query.log.txt
  
  
# Enter a name for the slow query log file. Otherwise a default name will be used.
  
  
Log-slow-queries = c:/mysql_solw_query.log.txt
  
  
# Enter a name for the update log file. Otherwise a default name will be used.
  
  
Log-update = # Enter a name for the binary log. Otherwise a default name will be used.
 
 

Log-bin = MYSQL installed in windows does not contain mysqldumpslow, so the generated log files cannot be analyzed in windows. However, you can store the generated log files in MYSQL in LINUX for analysis. You can run the following command on the command line to enter the mysql/bin directory, test slowquery, the log file generated in windows. rename log to slowquery. log to the mysql/bin directory): mysqldumpslow slowquery. log to view the analysis content.

Here is an introduction to MySQL database optimization. I hope this introduction will be helpful to you!