How to tune a MySQL server?

1. Hardware tuning, such as updating hardware, such as updating server memory, and replacing hard disks to achieve the purpose of adjusting MySQL performance.

2. Operating system tuning, such as modifying operating system parameters, such as modifying Linux kernel parameters, disabling unnecessary background services, or using a high-performance file system.

3. MySQL service tuning, such as adjusting the number of MySQL references, such as query cache, innodb buffer pool size, join buffer size or sort buffer size, etc., these parameters affect the performance of MySQL.

4. Query optimization, such as finding out the time-consuming query in MySQL and optimizing SQL statements to improve the query performance of MySQL, such as using indexes, rewriting SQL, etc.

5. Adjust the database structure, such as adjusting the database table building method to lose the scattered type, such as sharding the database and tables, such as splitting large tables, etc., to improve the performance of MySQL.

In addition, it also includes the implementation of hot backup technology, that is, regular backup of database backup files to prevent accidental data corruption, and the use of master-slave mode or cluster mode to achieve high availability of MySQL to ensure that the MySQL database will not be seriously affected in the event of sudden failure.

The first method is when replacing the hardware in question. Tune the settings of the MySQL process. Optimize your query.

Replacing problematic hardware is often our first consideration, mainly because the database is resource-intensive. But that's not all there is to it. In fact, you can usually double the speed of your processor (CPU) or disk, or you can increase your memory by 4 to 8 times.

The second method is to tune the MySQL server (also known as MysqlD).

Tuning this process means allocating memory appropriately and letting Mysqld know what type of load it will be subjected to. Speeding up your disk is not as good as reducing the number of disk visits you need. Similarly, ensuring that a MySQL process is operating correctly means that it spends more time on service queries than it does on dealing with background tasks such as processing temporary disk tables or opening and closing files.

Tuning mysqld is the focus of this article.

The best way to do this is to make sure that the query has been optimized. This means that the table is properly indexed and the query is written in a way that can take full advantage of Mysql's capabilities. Although this article doesn't cover query tuning (which has been shed on this topic in many books**), it does configure Mysqld to report queries that may need to be tuned.

Although these tasks have been prioritized, it is still important to pay attention to the hardware and mysqld settings to facilitate proper tuning of queries. I've seen fast machines fail when running well-designed queries due to heavy load, because mysqld is too busy to serve queries.

The third way is to log slow queries.

In an SQL server, data tables are stored on disk. Indexes provide a way for the server to find specific rows of data in a table instead of searching the entire table. When you have to search the entire table, it's called a table scan.

In general, you may only want to get a subset of the data in the table, so a full table scan wastes a lot of disk and therefore a lot of time. This problem is compounded when data must be joined, as multiple rows of data must be compared across the connection.

Of course, table scanning doesn't always pose problems; Sometimes it's more efficient to read the entire table than to pick out a portion of the data (the query planner in the server process uses to make these decisions). If indexes are used inefficiently, or at all, they can slow down queries and become more pronounced as the load on the server and the size of the tables increases. A query that takes longer than a given time frame to execute is called a slow query.

Shenzhen Bitjet