MySQL Performance Optimization: Few Important System Variables For MySQL InnoDB Database Performance Optimization/Improvement
Below are the few important system variables you might consider changing for getting better performance out of MySQL InnoDB Database.
Buffer Pool Size
# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and # row data. The bigger you set this the less disk I/O is needed to # access data in tables. On a dedicated database server you may set this # parameter up to 80% of the machine physical memory size. Do not set it # too large, though, because competition of the physical memory may # cause paging in the operating system. Note that on 32bit systems you # might be limited to 2-3.5G of user level memory per process, so do not # set it too high. innodb_buffer_pool_size=1500M
Refer: innodb_buffer_pool_size
Transaction Isolation Level
# Set the default transaction isolation level. Levels available are: # READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE transaction_isolation=READ-COMMITTED
Refer: transaction_isolation
Log File Size
# Size of each log file in a log group. You should set the combined size # of log files to about 25%-100% of your buffer pool size to avoid # unneeded buffer pool flush activity on log file overwrite. However, # note that a larger logfile size will increase the time needed for the # recovery process. innodb_log_file_size=256M
Refer: innodb_log_file_size
Log Buffer Size
# The size of the buffer InnoDB uses for buffering log data. As soon as # it is full, InnoDB will have to flush it to disk. As it is flushed # once per second anyway, it does not make sense to have it very large # (even with long transactions). innodb_log_buffer_size=4M
Refer: innodb_log_buffer_size
Flush Log At Transaction Commit
# If set to 1, InnoDB will flush (fsync) the transaction logs to the # disk at each commit, which offers full ACID behavior. If you are # willing to compromise this safety, and you are running small # transactions, you may set this to 0 or 2 to reduce disk I/O to the # logs. Value 0 means that the log is only written to the log file and # the log file flushed to disk approximately once per second. Value 2 # means the log is written to the log file at each commit, but the log # file is only flushed to disk approximately once per second. innodb_flush_log_at_trx_commit=2
Refer: innodb_flush_log_at_trx_commit
Auto-Increment Lock Mode
# innodb_autoinc_lock_mode variable defines the lock mode to use for generating auto-increment values. # The permissible values are 0, 1 or 2 (for “traditional”, “consecutive” or “interleaved” lock mode, respectively). # In most cases, this variable is set to the default of 1. # Interleaved (or 2) is the fastest and most scalable lock mode, # but it is not safe if using STATEMENT-based replication or recovery scenarios when SQL statements are replayed from the binary log. # Another consideration – which you shouldn’t rely on anyway – is that IDs might not be consecutive with a lock mode of 2. # With interleaved, INSERT statements don’t use the table-level AUTO-INC lock and multiple statements can execute at the same time. # Setting it to 0 or 1 can cause a huge hit in concurrency for certain workloads. innodb_autoinc_lock_mode=2
Refer: innodb_autoinc_lock_mode
Binary Logging Format
# binlog_format variable sets the binary logging format, and can be any one of STATEMENT, ROW, or MIXED. # Prior to MySQL 5.7.7, the default format was STATEMENT. In MySQL 5.7.7 and later the default is ROW. # Recommended setting it to BINLOG_FORMAT=ROW, when innodb_autoinc_lock_mode=2. binlog_format=row
Refer: binlog_format
That’s It !!!
.NET Professional | Microsoft Certified Professional | DZone’s Most Valuable Blogger | Web Developer | Author | Blogger
Doctorate in Computer Science and Engineering
Microsoft Certified Professional (MCP) with over 12+ years of software industry experience including development, implementation & deployment of applications in the .NET framework
Experienced and skilled Agile Developer with a strong record of excellent teamwork, successful coding & project management. Specialises in problem identification and proposal of alternative solutions. Provided knowledge and individual mentoring to team members as needed
Among top 3% overall in terms of contribution on Stack Overflow (~2.3 million people reached my posts). Part of the top 1% Stack Overflow answerers in ASP.NET technology.
DZone’s Most Valuable Blogger (MVB)
Created and actively maintain the TechCartNow.com tech blog while also editing, writing, and researching topics for publication.
Excellent skills in Application Development using C#/Vb.Net, .NET Framework, ASP.NET, MVC, ADO.NET, WCF, WPF, Web API, SQL Server, jQuery, Angular, React, BackboneJS
Please share the environment details in which these settings improved performance. It would be helpful for sake of comparison.