未加星标

MySQL 5.7 Performance Tuning Immediatedly After Installation

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二04 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏

This blog updates Stephane Combaudon’s blog on mysql performance tuning , and covers MySQL 5.7 performance tuning immediately after installation.

A few years ago, Stephane Combaudon wrote a blog post on Ten MySQL performance tuning settings after installation that covers the (now) older versions of MySQL: 5.1, 5.5 and 5.6. In this post, I will look into what to tune in MySQL 5.7 (with afocus on InnoDB).

The good news is that MySQL 5.7 has significantly better default values.Morgan Tocker created a page with a complete list of features in MySQL 5.7 , and is a great reference point. For example, the following variables are set:

innodb_file_per_table =ON innodb_stats_on_metadata = OFF innodb_buffer_pool_instances = 8 (or 1 if innodb_buffer_pool_size < 1GB) query_cache_type = 0; query_cache_size = 0; (disabling mutex)

In MySQL 5.7, there are only four really importantvariables that need to be changed. However, there are other InnoDBand global MySQL variables that might need to be tuned for a specific workload and hardware.

To start, add the following settings to my.cnf under the [mysqld] section. You will need to restart MySQL: [mysqld] # other variables here innodb_buffer_pool_size = 1G # (adjust value here, 50%-70% of total RAM) innodb_log_file_size = 256M innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0 innodb_flush_method = O_DIRECT

Description:

Variable Value innodb_buffer_pool_size Start with 50%70% of total RAM.Does not need to be larger than the database size innodb_flush_log_at_trx_commit 1 (Default) 0/2 (more performance,less reliability) innodb_log_file_size 128M 2G (does not need to be larger than buffer pool) innodb_flush_method O_DIRECT (avoid double buffering)

What is next?

Those are a good starting point for any new installation.There are a number of other variables that can increase MySQL performance for some workloads. Usually, I would setup a MySQL monitoring/graphing tool (for example, the Percona Monitoring and Management platform ) and then check the MySQL dashboard to perform further tuning.

What canwe tune further based on the graphs?

InnoDB buffer pool size .Look at the graphs:


MySQL 5.7 Performance Tuning Immediatedly After Installation
MySQL 5.7 Performance Tuning Immediatedly After Installation

As we can see, we can probably benefit from increasing the InnoDB buffer pool size a bit to ~10G, as we have RAM available and the number of free pages is small compared to the total buffer pool.

InnoDB log file size. Look at the graph:


MySQL 5.7 Performance Tuning Immediatedly After Installation

As we can see here, InnoDB usually writes 2.26 GB of data per hour, which exceeds the total size of the log files (2G). We can now increase the innodb_log_file_size variable and restart MySQL. Alternatively, use “show engine InnoDB status” to calculate a good InnoDB log file size .

Other variables

There are a number of other InnoDB variables that can be further tuned:

innodb_autoinc_lock_mode

Setting innodb_autoinc_lock_mode =2 (interleaved mode) can remove the need fortable-level AUTO-INC lock (and can increase performance when multi-row insert statements are used to insert values into tables with auto_increment primary key). This requires binlog_format = ROW or MIXED (and ROW is the default in MySQL 5.7).

innodb_io_capacity and innodb_io_capacity_max

This is a more advanced tuning, and only make sense when you are performing a lot of writes all the time (it does not apply to reads, i.e. SELECTs). If you really need to tune it, the best method is knowing how many IOPS the system can do. For example, if the server has one SSD drive, we can set innodb_io_capacity_max = 6000 and innodb_io_capacity = 3000 (50% of the max). It is a good idea to run the sysbench or any other benchmark tool to benchmark the disk throughput.

But do we need to worry about this setting? Look at the graph of buffer pool’s “ dirty pages “:


MySQL 5.7 Performance Tuning Immediatedly After Installation

In this case, the total amount of dirty pages is high, and it looks like InnoDB can’t keep up with flushing them. If we have a fast disk subsystem (i.e., SSD), we might benefit from increasing innodb_io_capacity and innodb_io_capacity_max.

Conclusion orTL;DR version

The new MySQL 5.7 defaults are much better for general purpose workloads. At the same time, we still need to configure InnoDB variables to take advantages of the amount of RAM on the box. After installation,follow these steps:

Add InnoDB variables to my.cnf (as described above) and restart MySQL Install a monitoring system, (e.g., Percona Monitoring and Management platform) Look at the graphs and determine if MySQL needs to be tuned further

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: MySQLSQLInnoDBAUTUTAU2G
分页:12
转载请注明
本文标题:MySQL 5.7 Performance Tuning Immediatedly After Installation
本站链接:http://www.codesec.net/view/483714.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(33)