MySQL Optimization Guide

12006

1. Database Optimization

The performance optimization of MySQL databases involves the following aspects:

1. Properly Configure MySQL Buffer Pool: MySQL has various caching mechanisms, such as Key Buffer, Query Cache, and InnoDB Buffer Pool (default). Improper configuration can affect database performance, so it should be set reasonably based on actual needs;
2. Properly Configure MySQL Tables: Converting MyISAM tables to InnoDB tables can improve performance. When setting the table type, factors such as table size and concurrent read/write operations need to be considered;
3. Choose the Right Storage Engine: MySQL has multiple storage engines, such as MyISAM, InnoDB (default), and Memory. Different storage engines perform differently in various usage scenarios, so the appropriate storage engine should be selected based on actual conditions;
4. Optimize SQL Statements: SQL is the language used by MySQL. Adding appropriate indexes to SQL statements can improve query efficiency, and attention should be paid to the performance of SQL statements;
5. Regular Maintenance and Optimization of MySQL Database: Regular maintenance and optimization of the MySQL database are necessary, such as cleaning unnecessary logs and cached data, backing up data, and adjusting server parameters.

Here are some recommended optimization configurations:

• Maximum Memory Usage: Based on the server configuration and actual conditions, the maximum memory usage for MySQL can be set to improve query performance.
• Configure Caching: MySQL caching can significantly enhance query performance and reduce disk access frequency. Caching can be set through configuration files or using MySQL's built-in tools.
• Optimize Query Statements: Based on specific business scenarios, query statements can be optimized to reduce the amount of data queried and the number of queries, thereby improving query efficiency.
• Partition Tables: For tables with very large amounts of data, consider using partition tables to enhance query efficiency.
• Set Appropriate interactive_timeout and wait_timeout Parameters: These two parameters represent the number of seconds the server waits before closing interactive and non-interactive active connections, respectively. Generally, these parameters should be set as low as possible. You can add the following settings in the MySQL configuration file:

[mysqld]  
interactive_timeout=240  
wait_timeout=240  

2. Check MySQL Current Status

Through the Jianghu panel's MySQL plugin, you can view the current status of MySQL:

1. On the left side of the Jianghu panel, select "MySQL" to open the management interface of the MySQL plugin.
2. Check the "Current Status" in the MySQL plugin. If any data is too high, you can adjust it according to the relevant suggestions in "Performance Optimization."
3. Exception: If the "Opened Tables" data is too high, you need to increase table_open_cache and set open_files_limit to 16096.

Note: The table_open_cache parameter is ineffective if it exceeds 2048 by default. If increasing table_open_cache does not improve MySQL performance, the following configurations need to be made:

• Execute the command:

vi /usr/lib/systemd/system/mysql-apt.service  

• In the opened file, find [Service] and add the following configuration under [Service]:

LimitNOFILE=infinity  
LimitMEMLOCK=infinity  

• Restart the MySQL service:

systemctl daemon-reload  
systemctl restart mysql-apt.service  

3. JianghuJS Project Configuration

JianghuJS projects need to connect to a database to run. If a JianghuJS project creates too many MySQL connections, it may exhaust MySQL resources, preventing other project processes from using MySQL. Therefore, proper configuration is necessary to ensure the project can reasonably utilize system resources.

Two configurations in JianghuJS projects determine how much MySQL resources the project uses:

• The number of workers set in the startup script in the package.json file

// package.json  
{  
  // Other configurations omitted  
  "scripts": {  
    "start": "egg-scripts start --daemon --workers=2 --port=7001 --title=my_jianghujs_project", ## Startup script, if the number of workers is not specified, it will use all CPU cores  
    // Other scripts omitted  
  },  
}  

• The pool configuration in knex in the ./config/config.prod.js configuration file

// ./config/config.prod.js  

module.exports = appInfo => {  
  return {  
    // Other settings omitted  
    knex: {  
      client: {  
        dialect: 'mysql',  
        connection: {  
          // Connection settings omitted  
        },  
        pool: { min: 0, max: 10 }, # Minimum and maximum MySQL connections  
      },  
      app: true  
    }  
  };  
};  

The number of connections each JianghuJS project can use = number of workers x number of connections in the connection pool. For general projects, the number of workers is usually set to 1-2, and the maximum number of connections in the pool is typically set to 7-10. You need to set reasonable values based on the specific functions and traffic of the project.

Assignment

Based on the content of this article, complete the following assignments:

• Use the panel to create a database named "jianghu"
• Backup the "jianghu" database
• Attempt to change the root password of the database