This is the fourth day of my participation in Gwen Challenge

preface

After the primary and secondary replication of MySQL in the early stage, it was found that the database read and write was very slow, and the MySQL connection number was too small, resulting in the crash of the whole database. By modifying the parameters of my.cnf configuration file in MySQL, the maximum performance of MySQL relational database can be rationally utilized. This paper is divided into three aspects:

Docker install MySQL

Mysql sudo docker search mysql sudo docker pull mysql sudo docker pull mysql sudo docker pull mysql Sudo docker run --name mysql -e MYSQL_ROOT_PASSWORD=123456 -p 3306:3306 -d mysqlCopy the code

MySQL > connect to MySQL

1) sudo docker exec -it bash 2) mysql -uroot -p 3) use mysql 4) select host, user from user; Delete from user where host="%" and user="root"; Update user set host = '%' where user = 'root'; Refresh PRIVILEGES FLUSH PRIVILEGES; Alter user 'root'@'%' identified with mysql_native_password by '123456';Copy the code

3. Modify parameters in the configuration file

1. After downloading, enter Docker ps to view the image

Docker run -p 3307:3306 –name mysql -e MYSQL_ROOT_PASSWORD=root -d mysql, docker ps can check the status of mysql

Docker exec-it /bin/bash docker exec-it /bin/bash docker exec-it /bin/bash Replace the local file with the container file

4. I want to modify the mysql.cnf file. You can first use ls to view directory files (PS: ls command to display the contents of the current directory)

5. Tail my.cnf (PS: tail command can be used to view the contents of the file)

6. Run viM code

vim /etc/mysql/my.cnf
Copy the code

7. After running the above vim code, you may find that the execution is not successful, because there is no Vim editor in the Docker container, so you need to install vim editor by yourself (see appendix at the end of the article for details of viM installation method).

8. Access the my.cnf file

9. Add or modify the configurations according to your own requirements. The configurations I modified are as follows (for your reference)

10. Attach some key parameters of MySQL database performance optimization

Key parameter 1: back_log

The number of connections required for MySQL. This works when the main MySQL thread gets a very large number of connection requests in a very short period of time, and then the main thread takes some time (albeit a short one) to check the connection and start a new thread.

The back_log value indicates how many requests can be placed on the stack in a short time before MySQL temporarily stops answering new requests. Only if you expect a lot of connections in a short time, you need to increase it, in other words, the size of the listening queue for incoming TCP/IP connections. Your operating system has its own limits on this queue size. Trying to set back_log higher than your operating system’s limit will not work.

When you look at your host process list, found that a large number of 264084 | unauthenticated user | XXX, XXX, XXX, XXX | NULL | NULL | | Connect login | NULL for the connection process, I’m going to increase the back_log. The default value is 50, so I changed it to 500.

Key parameter 2: Interactive_timeout

The number of seconds the server waits for action on an interactive connection before closing it. An interactive client is defined as the client using the CLIENT_INTERACTIVE option for mysQL_real_connect (). The default value is 28800, I changed it to 7200.

Key parameter 3: key_BUFFer_SIZE

The index block is buffered and shared by all threads. Key_buffer_size is the buffer size used to index blocks, increasing it to get more manageable indexes (for all reads and multiplies) as much as you can afford. If you make it too big, the system will start paging and really slow down. The default value is 8388600(8M), my MySQL host has 2GB of memory, so I changed it to 402649088(400MB).

Key parameter 4: max_connections

The number of simultaneous customers allowed. Increasing this value increases the number of file descriptors required by mysqld. This number should be increased; otherwise, you will often see Too many connections errors. The default value is 100, but I changed it to 1024.

Key parameter 5: record_buffer

Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do a lot of sequential scans, you may want to increase this value. The default value is 131072(128K), I changed it to 16773120 (16M)

Key parameter 6: SORt_buffer

Each thread that needs to sort is allocated a buffer of that size. Increasing this value speeds up ORDER BY or GROUP BY operations. The default value is 2097144(2M), and I changed it to 16777208 (16M).

Key parameter 7: table_cache

The number of open tables for all threads. Increasing this value increases the number of file descriptors required by mysqld. MySQL requires 2 file descriptors for each unique open table. The default value is 64, but I changed it to 512.

Key parameter 8: thread_cache_size

The number of saved threads that can be reused. If so, the new thread is fetched from the cache, and the client’s line is placed in the cache if there is room when the connection is disconnected. If there are many new threads, use this variable to improve performance. You can see what this variable does by comparing the Connections and Threads_created state variables. I’m going to set it to 80.

Key parameter 9: wait_timeout

The number of seconds the server waits for action on a connection before closing it. The default value is 28800, I changed it to 7200.

Note: Parameters can be adjusted by modifying the /etc/my.cnf file and restarting MySQL. This is a bit of a cautious exercise, and the results above are just my thoughts, and you can make further modifications based on your own host hardware (especially memory size).

11. Run the :wq command to save the configuration

12. Use tail to check whether the file is correctly modified

tail /etc/mysql/my.cnf
Copy the code

After modifying the configuration file, you can use MySQL as much as you like. (PS: Due to the limitation of the original design, relational database will encounter I/O performance bottleneck in mass data processing and multi-table function query. Therefore, non-relational database (NoSQL) with distributed, high performance, high reliability and other characteristics is gradually understood and used by people, which makes up for the congenital deficiency of relational database performance and has great advantages in storing unstructured data. Mainstream NoSQL databases include Redis, MongoDB and HBase, etc.)

The appendix

Vim editor download and installation

When using the docker container, sometimes there is no vim installed inside the docker container. When running the vim command, the message “vim: command not found” is displayed. In this case, you need to install vim.

apt-get install vim
Copy the code

Error Indicates an Error message:

Reading package lists... Done Building Dependency tree Reading State Information... Done E: Unable to locate package vimCopy the code

According to the usual practice of downloading and installing software with APT command, we need to update apt!!

apt-get update
Copy the code

List and /etc/apt/sources.list.d to obtain the latest package. Wait until the update is complete before running the command:

apt-get install vim

Copy the code

vim /etc/mysql/mysql.conf.d/mysqld.cnf
Copy the code

1.apt-get install vim

2.apt-get update

3. Install the Vim editor