On Linux, MySQL installation does not provide the option to select a large memory configuration or MySQL server option during installation, unlike the Windows installation package. Therefore, optimization and adjustments can only be performed post-installation.

Preparation

1. Stop the MySQL service. Use /etc/init.d/mysql stop, or if you are using LNMP, you can directly run lnmp mysql stop.
2. Locate the MySQL configuration file. For LNMP, the file is located at /etc/my.cnf. If installed via another method, you can search for it using find / -name my.cnf.

Optimization Configuration

The goal of optimizing MySQL configuration is to enhance performance without increasing hardware resources. Below are some recommendations for optimizing MySQL on a server with 16GB of memory:

1. Adjust Buffer Pool

MySQL uses the buffer pool to cache query and table data to reduce I/O operations. Depending on the server configuration and application load, the following buffer pool parameters can be adjusted:

• innodb_buffer_pool_size: This parameter is used by the InnoDB storage engine to store table and index data. It should be set to approximately 70% of available memory. On a server with 16GB of memory, it can be set to around 12GB.
• key_buffer_size: If using the MyISAM storage engine, set this parameter to 10% to 25% of available memory.
• query_cache_size: This parameter is used to cache query results for faster returns. However, query caching can negatively impact large databases. For a server with 16GB of memory, an appropriate query cache size would be between 128MB and 256MB.

2. Adjust Connection Limits

In MySQL, each client connection requires a certain amount of memory. Excessive connections may lead to insufficient memory and degraded performance. Adjust the maximum number of connections based on the specific needs of your application to ensure optimal server performance.

• max_connections: This parameter specifies the maximum number of simultaneous client connections. Generally, it should be set to available memory divided by the average memory required per connection. For example, if each connection uses 500KB of memory, you could set max_connections to 32.

3. Adjust Logging Settings

MySQL’s log files are used to record queries, transactions, and other activities. Properly configuring logging can reduce disk write operations and improve performance.

• innodb_log_file_size: This parameter specifies the size of the InnoDB storage engine’s transaction log files. It is recommended to set it to 25% to 50% of the buffer pool size.
• innodb_flush_log_at_trx_commit: This parameter specifies the frequency at which InnoDB flushes the log buffer to disk upon transaction commits. If data consistency is not a primary concern, it can be set to 0 or 2 to improve performance.

4. Other Adjustments

• tmp_table_size and max_heap_table_size: These parameters specify the maximum size of temporary tables used for operations like sorting. If space allocation fails, MySQL will create these tables on disk, leading to performance loss. It is recommended to set them to 5% to 10% of the stable maximum available memory.
• table_open_cache: This parameter specifies the size of the table cache. By default, MySQL allows opening 64 tables. If you have many tables, increasing this value can enhance performance.

5. Start MySQL

Finally, execute /etc/init.d/mysql start or use lnmp mysql start for LNMP setups.

In conclusion, after implementing the new configuration, it is essential to test the performance and stability of MySQL. Typically, key metrics such as request response time, buffer pool usage, and query cache hit rate should be monitored, and parameters should be adjusted accordingly to achieve optimal performance.