Mysql is a superior open source database. With immense and intense database-driven applications people are pushing Mysql beyond its limits. Running MySQL at desirable settings for specific resources helps to handle large server loads and prevents server slowdown. In case you want your server to perform optimally, the best solution is to get tons of memory and big, rapid drives. When we are enlisted for a MySQL execution review, we are relied upon to survey the MySQL setup and to propose enhancements. MySQL exposes a large number of variables whose values can be modified to fulfill custom necessities.
BASIC SETTINGS
Given below are some of the MySQL performance tuning settings that you should always look at.
- 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 are 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. Until Mysql5.5 the entire redo log size was restricted to 4GB. This has been lifted in Mysql5.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 indicates the maximum number of tables that the server can keep open in any one table cache instance. If the table_open_cache value is set to high, MariaDB may start to refuse connections as the operating system runs out of file descriptors.
InnoDB SETTINGS
- innodb_file_per_table
All InnoDB tables and indexes are stored in the system tablespace. innodb_file_per_table tablespaces provide an alternative, where InnoDB table and its indexes are stored in a separate .ibd data file. Each .ibd data file represents an individual tablespace. This feature is controlled by the innodb_file_per_table configuration option, which is enabled by default. innodb_file_per_table tells InnoDB if it should store data and indexes in shared tablespace(innodb_file_per_table=off) or in a separate .ibd file for each table(innodb_file_per_table =on). One of the advantages is that it may improve the chances of a successful recovery and save time 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. If 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
If you want the server to act as replication master then enabling binary logging is essential and also don’t forget to set server_id to a unique value. If binary logging is enabled, the server logs all statements that change data to the binary log, which is used for backup and replication.
- query_cache_size
To set the size of query cache, set query cache size system variable. Setting query_cache_size=0 disables the query cache. Zero is the default size, so query cache is disabled by default. To increase the performance of the MySQL server, the query cache may be turned on. It specifies the size of the cache. The query cache needs a size of 40KB to allocate its structure.
- query_cache_type
If the system variable query_cache_type is set to 1, all queries fitting the size constraints will be stored in the cache unless they contain an SQL_NO_CACHE clause. The query_cache_type server variable must be set to 2, or demand in order to specify that no queries must be stored in the cache unless the query requires it. Then, only queries with the SQL_CACHE attribute are cached. To set the size of the query cache, set the query_cache_size system variable. Setting it to 0 disables the query cache, as does setting query_cache_type=0. The query cache is disabled by default.
Do you want our expert team to optimize your MySQL server?
CONTACT US NOW
