MySQL is a powerful open-source database that supports a wide range of database-driven applications. As users push MySQL beyond its limits, learning how to optimize MySQL server performance becomes crucial. Running MySQL with optimal settings based on your server’s resources is key to managing heavy server loads and preventing slowdowns. To ensure peak performance, equipping your server with ample memory and fast, large drives is essential. When we conduct a MySQL performance review, we assess the setup and suggest improvements. MySQL offers many configurable variables that meet specific needs for better performance.
BASIC SETTINGS
Here are some key MySQL performance tuning settings to consider when learning how to optimize MySQL server performance.
- innodb_buffer_pool_size
The larger the buffer pool, the more InnoDB acts like an in-memory database, reading data from disk as soon as after which having access to the records from memory at some point of subsequent reads. Typical values are 5-6GB(8GB Ram),20-25GB(32GB Ram), 100-120GB(128GB Ram).
- innodb_log_file_size
This is the size of the redo logs. The redo logs used to ensure writes are fast and desirable and also during crash recovery. Fortunately, crash recovery performance has stepped forward a lot since Mysql5.5. So you can now have good write overall performance and rapid crash recovery. In MySQL 5.5, the entire redo log size was restricted to 4GB, but this limitation was removed in MySQL 5.6.
- max_connections
If you get a “too many connections” error in MySQL that means the max connection is too low or the number of available connections is in use by other clients on the server. So in this case, you need much more than the default 151 connections. The main drawback of high values for max_connection is that server turns unresponsive if for any motive it has to run 1000 or more active transactions.
when tuning server variables for maximum performance, the MySQL manual recommends that you first look at the key_buffer_size and table_cache variables.
- key_buffer_size
This variable determines the size of the index buffers held in memory, which affects the speed of index reads. Typically, you would want to keep this value near 25 to 30% of the total available memory on the server.
- table_open_cache
This value indicates the maximum number of tables the server can keep open in a single table cache instance. If you set the table_open_cache value too high, MariaDB may refuse connections as the operating system runs out of file descriptors.
InnoDB SETTINGS
- innodb_file_per_table
InnoDB stores all tables and indexes in the system tablespace by default. However, innodb_file_per_table provides an alternative where InnoDB stores each table and its indexes in a separate .ibd data file, creating individual tablespaces. The innodb_file_per_table option controls this behavior, and it is enabled by default. When enabled (innodb_file_per_table = on), InnoDB stores data and indexes in separate .ibd files for each table. If disabled (innodb_file_per_table = off), it stores them in the shared tablespace. One key advantage of this approach is that it can increase the likelihood of a successful recovery and reduce downtime when corruption occurs.
- innodb_flush_log_at_trx_commit
If innodb_flush_log_at_trx_commit=0 then InnoDB writes the modified data to log file and flush the log file every second, but it will not do anything at transaction commit. In case the system crashes, all the unflushed data will not be recoverable, since it is not written to log files or stored disks. If innodb_flush_log_at_trx_commit=1 then InnoDB writes the log buffer to the transaction log and flush to durable storage for every transaction. If innodb_flush_log_at_trx_commit=2 then InnoDB writes the log buffer to a log file at every commit but doesn’t write data to disk. InnoDB flush data once every second. Even if the system crashes, data in the log file will be available and can be recoverable.
OTHER SETTINGS
- log_bin
To set the server as a replication master, enable binary logging and set the server_id to a unique value. Enabling binary logging causes the server to log all data-changing statements to the binary log, which you can then use for backup and replication.
- query_cache_size
To set the query cache size, use the query_cache_size system variable. Setting query_cache_size=0 disables the query cache, and zero is the default value, meaning the cache is disabled by default. To improve MySQL server performance, enable the query cache and specify its size. The query cache requires a minimum size of 40KB to allocate its structure.
- query_cache_type
When the system variable query_cache_type is set to 1, the system stores all queries that meet the size constraints in the cache, unless they include an SQL_NO_CACHE clause. To ensure that no queries are stored in the cache unless explicitly required, set the query_cache_type server variable to 2 or demand. This will cache only queries with the SQL_CACHE attribute. To define the size of the query cache, adjust the query_cache_size system variable. Setting it to 0 disables the query cache, as does setting query_cache_type to 0. By default, the query cache is disabled.
For expert assistance on how to optimize MySQL server performance, contact Skynats. Our team of professionals is ready to review your MySQL setup, suggest performance improvements, and ensure your server runs at peak efficiency. Reach out today for tailored solutions and support!
