☆编译安装,配置文件,监控性能参数大总结

实验环境：
rhat 6.5 英文 basic server
mysql-5.6.26源码包和cmake-2.8.3
本地yum源 配置IP
关闭selinux、iptables

编译安装mysql-5.6.26及配置多实例步骤：

1，安装基本的编译工具

yum  install gcc gcc-c++ ncurses-devel

2，解压cmake，编译安装cmake

[root@root ~]# tar xf cmake-2.8.3.tar.gz
[root@root ~]# cd cmake-2.8.3
[root@root cmake-2.8.3]# ./configure ;make;make install

2，解压mysql，编译安装

[root@root ~]# tar xf mysql-5.6.26.tar.gz 
[root@root ~]# cd mysql-5.6.26
[root@root mysql-5.6.26]# cmake .;make ;make install

3，建mysql用户和属组

[root@root cmake-2.8.3]# useradd -r mysql

4，赋予目录权限

[root@root mysql-5.6.26]# chown -R mysql:mysql /usr/local/mysql

5，初始化数据库

[root@root mysql]# pwd
/usr/local/mysql
[root@root mysql]# ls
bin      data  include         lib  mysql-test  scripts  sql-bench
COPYING  docs  INSTALL-BINARY  man  README      share    support-files
[root@root mysql]# ./scripts/mysql_install_db --datadir=/usr/local/mysqldata/ --user=mysql    
【指定安装目录和数据存放目录】
检查是否有2个ok，初始化完成

6，给数据库目录mysql用户和属组权限

[root@root mysql]# chown -R mysql:mysql /usr/local/mysqldata/

7，把mysql服务器基本配置写进配置文件

[root@root mysql]# cp support-files/my-default.cnf /etc/mysqla.cnf 【自己指定的配置文件】 
[root@root mysql]# vim /etc/mysqla.cnf 
[mysqld]
 basedir = /usr/local/mysql
 datadir = /usr/local/mysqldata
 port = 3307
 server_id = 2
 socket = /usr/local/mysqldata/mysql.sock
 pid-file=/usr/local/mysqldata/mysql.pid

8，修改PATH变量

[root@root mysql]# PATH=$PATH:/usr/local/mysql/bin
[root@root mysql]# vim /root/.bash_profile

# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
# User specific environment and startup programs

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
export PATH

9，添加开机自启动：复制启动服务脚本到/etc/init.d下

[root@root mysql]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d

10，将mysql.server设置成可以以服务方式启动的

[root@root mysql]# chkconfig --add mysql.server

11，指定配置文件方式启动mysql服务器

[root@root mysql]# mysqld_safe --defaults-file=/etc/mysqla.cnf &    #&意思是后台执行
[1] 53337
[root@root mysql]# 160823 06:36:48 mysqld_safe Logging to '/usr/local/mysql/data/root.err'.
160823 06:36:48 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysqldata

查看服务有木有正确启动：

[root@root mysql]# netstat -tnulp|grep 330
tcp        0      0 :::3307                     :::*                        LISTEN      53492/mysqld

12，进入mysql数据库，修改密码，两种方式进入数据库
①

[root@root mysql]# mysql -uroot -S /usr/local/mysql/data/mysql.sock 
mysql> set password = password('123123');

②

[root@root mysql]# mysql -uroot -P3307 -h127.0.0.1 -p123123

至此，一个实例搭建完成

PS：mysql5.7的初始化数据库的脚本 在你安装好了之后的/usr/bin/mysql_install_db –datadir=/usr/local/mysqldata/ –user=mysql ；
mysql5.7的需要用新的xtrabackup做备份，以前的2.3.4的版本的不支持5.7的，安装xtrabackup包缺的那三个包，缺的perl-DBD-mysql可以用本地yum安装。

初始化参数调整(写到配置文件中的)

[mysql]
    prompt = [\u@\h][\d]>\_

    [mysqld]
    # basic settings #
    user = mysql  --使用哪个用户启动数据库
    sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
    autocommit = 1
    character_set_server=utf8mb4
    transaction_isolation = READ-COMMITTED    #隔离级别,或许会产生不可重复读,但是没范围锁
    explicit_defaults_for_timestamp = 1    #创建表示,当一个列属性为timestamp时,即使这个列insert时没有数,也会有时间
    max_allowed_packet = 16777216    #允许最大一个列的长度
    event_scheduler = 1    #是否开启event

    # connection #
    interactive_timeout = 1800     
    wait_timeout = 1800    #连到数据库的线程长时间不用会自动踢出去
    lock_wait_timeout = 1800    #影响表锁时间
    skip_name_resolve = 1    #反向解析,一般没啥用,设置成1关掉
    max_connections = 1500
    max_connect_errors = 1000    #最大失败连接次数,连接失败1000次就不让你连接,防止被攻击,反复尝试密码

    # session memory setting     #会话级别，如果内存足够，建议调大
    read_buffer_size = 16777216     #如果没有myisam业务表和频繁的访问需求，这个参数不需要调整这么大
    read_rnd_buffer_size = 33554432    #对于大型排序，采用二次排序的方式，第一次取一部分放在sort中排序，剩下的放在rnd中进行第二次排序。
    sort_buffer_size = 33554432    #会话级别的。
    tmp_table_size = 67108864    #临时表
    join_buffer_size = 134217728    #与临时表有关
    binlog_cache_size=2048000    #binlog的缓存大小
    max_length_for_sort_data=1024    #排序数据大于这个数才会用二次排序，调大，就会强行调用第一种算法。

    # log settings #
    log_error = error.log    #错误日志
    slow_query_log = 1    #慢查询
    slow_query_log_file = slow.log    #慢查询日志的名字
    log_queries_not_using_indexes = 1    #没走索引的sql都记录，因为mysql不走索引基本无路可走，所以记录一下
    log_slow_admin_statements = 1    #管理性sql慢了会记录(alter table,analyze table,create index等),管理性sql风险大
    log_slow_slave_statements = 1    #没啥用,不用管
    log_throttle_queries_not_using_indexes = 10    #因为没走索引的sql会记录,但是可能会很多,这里限制值记录10条
    expire_logs_days = 90    #binlog清空,每90天清空一次,有风险,因为可能删除量比较大
    long_query_time = 2    #设置多久才算是慢查询,可以设置为0.几秒
    min_examined_row_limit = 100    #sql处理如果小于100行,没必要记录到慢查询
    binlog-rows-query-log-events = 1    #binlog中会有最原始的sql语句,而不是只有处理过程
    log-bin-trust-function-creators = 1    #在开启binlog后,不是1建立不了函数
    expire-logs-days = 90
    log-slave-updates = 1    #从库是否写binlog。一主多从时，一级从库常开启
    log_bin = aaa_master    #生成的log bin 文件叫什么

    # innodb settings #
    innodb_doublewrite=off    #关闭double write，能很大的提升写性能
    innodb_page_size = 16384    #page size为16K，一般不改
    innodb_buffer_pool_size = 160G    #池子的大小，默认是128M，建议改为物理内存的50-80%
    innodb_buffer_pool_instances = 16    #池子数量,一般是2的n次方倍,比如说4,8,16,32
    innodb_buffer_pool_load_at_startup = 1    #在开启的时候会把之前的热数据加载到内存。
    innodb_buffer_pool_dump_at_shutdown = 1    #在关闭的时候,把之前热区域地址记录下来,很有用。
    innodb_lru_scan_depth = 4096    #每次查找脏页的深度，增大该参数可增大脏页写入速度
    innodb_lock_wait_timeout = 5    #行锁 等待,最好很低
    innodb_io_capacity = 10000    #控制每次写入的控制量
    innodb_io_capacity_max = 20000    #与上面相对,两倍关系
    innodb_flush_method = O_DIRECT    #innodb写时绕过文件系统缓存,挺重要
    innodb_file_format = Barracuda    #无所谓
    innodb_file_format_max = Barracuda    #无所谓,类似于文件系统
    innodb_undo_logs = 128    #128段,不要改
    innodb_undo_directory=/usr/local/myundo/    #将undo独立出去,必须在初始化库之前做！！
    innodb_undo_tablespaces=3    #（1,2,3    只要大于0即可）将undo空间从ibdata分离成3份
    innodb_flush_neighbors = 0    #要关,现在就是关,刷新邻接页
    innodb_log_file_size = 17179869184    #log-file大小，默认为48M。该值4G以下很安全。
    innodb_log_files_in_group = 2    #2到5个就行
    innodb_log_buffer_size = 16777216     #经常设置为100M
    innodb_purge_threads = 4    #一般不改
    innodb_large_prefix = 1    #可以打开，可以建立更厉害的索引
    innodb_thread_concurrency = 64     #最好调大，允许最大并发数量，等于核数，两倍核数最大，并发性能提升明显
    innodb_print_all_deadlocks = 1    #打开后，产生死锁后会记录到error.log中，否则不记录
    innodb_strict_mode = 1    #把某些warning当error来报，不要产生warning，而是回滚。
    innodb_sort_buffer_size = 67108864    #这里只是对建索引的时候
    innodb_write_io_threads = 16    #写线程的数量。cpu核数的一半
    innodb_read_io_threads = 16     #读线程的数量。cpu核数的一半
    innodb_file_per_table = 1     #代表一个表一个文件，一定不能是0
    innodb_stats_persistent_sample_pages = 64    #用特殊算法（找64个页，算平均行数），算出表中用多少行
    innodb_autoinc_lock_mode = 2    #值为2对性能有提高，提高insert的速度，但是自增长可能不连续了

    # replication setting #                #主从复制
    master_info_repository = TABLE        #主库传了多少了
    relay_log_info_repository = TABLE    #应用了多少了
    sync_binlog = 1       #事务提交时，每次都会把binlog刷到磁盘上(永久保存)（0表示写到文件系统上，而不是磁盘上；2表示2次提交刷一次到磁盘；3...3次）
    gtid_mode = on    #启用gtid类型，否则就是普通的复制架构
    enforce_gtid_consistency = 1      #强调gtid的一致性（GTID能够保证让一个从服务器到其他的从服务器那里实现数据复制而且能够实现数据整合的。GTID在分布式架构中可以保证数据的一致性。从而也实现了mysql的高可用性。）

    binlog_format = ROW
    binlog_rows_query_log_events = 1
    relay_log = relay.log
    relay_log_recovery = 1    #主库坏了时，自动恢复
    binlog_gtid_simple_recovery = 1  #加速mysql的启动！show一下，有这个参数的话设为1开启就行！
    slave_skip_errors = ddl_exist_errors(具体错误号)     #主库传过来的日志，在从库上执行时，想忽略的错误
   slave-skip-errors 为all 时，忽略所有错误。为ddl_exist_errors（具体错误号）时，跳过这一个错误。（自动）
    set global sql_slave_skip_counter=n：n的值为1或2。如果来自主库的更新语句不使用auto_increment 或 last_insert_id()，n值应为1，否则为2。
    slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'    #从库执行主库的sql时，优先走索引。（'TABLE_SCAN'就是优先走全表扫描）

    # semi sync replication settings #
    plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
    rpl_semi_sync_master_enabled = 1
    rpl_semi_sync_master_timeout = 3000    #毫秒。超过3s就会关闭半同步复制
    rpl_semi_sync_slave_enabled = 1

    # password plugin #
    validate_password_policy=STRONG
    validate-password=FORCE_PLUS_PERMANENT    #强制用复杂密码

    [mysqld-5.7]        #5.7的新特性
    # new innodb setting #
    loose_innodb_numa_interleave=1    #mysql5.7里通过这个参数关闭NUMA架构。会大大减少swap。（建议在BIOS界面设置NUMA）
    innodb_buffer_pool_dump_pct = 40    #数据库关的时候，把40%的热区域的数据记录下来。控制mysql启动时加载多少热数据，调的很高启动很慢
    innodb_page_cleaners = 16    #刷脏页时，需要有线程去找lru上面的脏页刷到写队列中，就是此参数，太大会导致写队列很满，默认是1。
    innodb_undo_log_truncate = 1        #undo log可以进行收缩。但至少要有另一个undo log。
    innodb_max_undo_log_size = 2G    #undo log最大能多大。一般设为20G以上
    innodb_purge_rseg_truncate_frequency = 128    #什么状态下可以进行undo收缩，比如128次    
    # new replication setting #    mysql5.7的新特性
    slave-parallel-type = LOGICAL_CLOCK  #基于时间的并行。大大提升同步速度。
    slave-parallel-workers = 16    #在从库上启动16个线程来并发复制，大大提升同步速度。
    slave_preserve_commit_order=1    
#（全局动态变量，默认0，可选值0、1。 表示是否需要严格保持顺序，默认值为0表示并发执行忽略顺序。对于多线程slaves，来保障事务在slave上执行的顺序与relay log中的顺序严格一致，只有当slave_parallel_workers开启时有效，此时log_bin、log_slave_updates必须开启，而且slave_parallel_type值必须为LOGICAL_CLOCK（默认值为DATABASE），如果你的事务经常是跨DB操作，那么可以考虑使用此参数限定顺序。当此参数开启时，要求任何worker线程执行事务时，只有当前事务中此之前的所有事务都执行后（被其他worker线程执行），才能执行和提交。）
    slave_transaction_retries=128    #锁超时时，先不回滚，先再尝试128次。
    # other change setting #
    binlog_gtid_simple_recovery=1
    log_timestamps=system        #错误日志生成的时候，时间是取的系统时间
    show_compatibility_56=on    #关于兼容性的参数。不打开可能会出问题。在information_schema库里有很多关于系统状态的表。关闭后，这些表在information_schema里就找不到了（就去performance_schema库里了）。

mysql性能监控

监控项：
（IO很高，一般是糟糕的SQL！
cpu很高，是因为内存读写占用了cpu资源，也是糟糕的sql！
用户线程空间内排序，io一般很小！）

网络相关

  • Bytes_received
  • Bytes_sent
  • TPS：Transaction Per Second，每秒事务处理量
  • Com_commit
  • Com_rollback

连接相关

  • created_tmp_disk_use
  • connections
  • connection_errors_max_connections
  • threads_created

负载相关

  • innodb_rows_deleted
  • innodb_rows_inserted
  • innodb_rows_read #异常高时，说明系统处理了过多的行。此时要去找SQL。而SQL在的地方：慢查询里或show
    processlist里。
  • innodb_rows_updated
  • sort_rows

QPS: Query Per Second，每秒查询率

  • Queries
  • Com_delete
  • Com_insert
  • Com_insert_select
  • Com_select
  • Com_update

内存相关

  • innodb_buffer_pool_pages_data
  • innodb_buffer_pool_pages_free
  • innodb_buffer_pool_pages_dirty

IO相关

  • innodb_buffer_pool_pages_flushed #flushed
    忽然高起来，说明是写的问题。比如logfile过小之类的
  • innodb_buffer_pool_wait_free
  • innodb_data_reads
  • innodb_log_waits

锁相关

  • innodb_row_lock_waits

排序相关

  • sort_merge_passes

SQL相关

  • select_full_join
  • select_scan

慢查询相关

  • slow_queries

内存相关参数(和读相关)

1.调整innodb buffer pool

调整根据每秒能接受的io和内存总量
主要调整两个参数

  • ①innodb_buffer_pool_size #一般为内存的50-80%
  • ②innodb_buffer_pool_size_instances #设置pool中有多少池,设置的越多latch征用就越小

buffer-pool 设置时需要和开发讨论,估计一个热数据的量
buffer-pool 设置的合不合理需要看命中率,但是命中率有时候不能说明全部问题,比如
当事务量很巨大时,99.99%和99.98%就有很大的差距,所以还是要看
①innodb-buffer-pool-read-request #请求的数量
②innodb-buffer-pool-reads/s #读硬盘的次数,一般硬盘500,物理读就饱和了-这个值很大除了弄buffer-pool更要整理sql.

2.调整用户工作空间

主要调整五个参数
①sort_merge_pass #如果大于零,说明已将开始用tmp排序->需要调大sort_buffer_size
②read_rnd_buffer_size #sort_buffer_size和read_rnd_buffer_size 只对myisam有意义。如果没有myisam业务表和频繁的访问需求，这个参数不需要调整很大。
③tmp_table_size #与临时表有关
④join_buffer_size #与临时表有关。当有复杂join时,create_tmp_disk_tables会增加,如果这个值持续增加,需要调整这两个值
⑤bin_log_cache_size #大于零,说明用tmp空间了,需要加大binlog cache size
一般不会调整用户空间,只有很多复杂sql(有大量的order by,grep by等)时，才调。

并发相关参数

1.max_connections
最大连接数量,一般调为2000,调整完这个数要看connection_error_max_connections这个参数,是代表因为最大连接数错误而登陆失败的数量,这个参数一定要是0
2.table_open_cache
这个参数可以调大,但是影响不大
thread_cache_size #这个参数如果小了,就会导致thread_connect这个值很大
thread_connect #连接数据库线程数量,是累计值,这个数过大说明thread_cache_size过小,需要调整.
3.innodb_lock_wait_timeout #锁等待的时间
这个一般会调整一下,50ms就差不多
4.innodb_thread_cpmcurrency #实际工作的线程数
这个数是限制并发的数量,一般等于核数

读线程相关参数

①innodb_buffer_pool_pages_flush #脏页写的次数
②innodb_buffer_pool_pages_data(总数据量)和innodb_buffer_pool_pages_dirty(脏块的数量),这俩的比值代表脏块的百分比
③innodb_buffer_pool_wait_free #找干净块的wait
读线程主要有四个参数
①innodb_io_capacity #常规写,可以根据硬件设备来更改,固态磁盘一般上千
②innodb_lru_scan_depth
③innodb_max_dirty_pages_pct #可以调整为90,80等左右,可以大点
如果脏数据过少,只有30%,可以适当减低innodb_lru_scan_depth,但是如果这个值太低,会影响innodb_buffer_pool_wait_free的值会加大,这是不好的现象,代表脏数据块与点多,找干净的替换有点难。

写有时候会出现抖动
出现抖动时,要查看脏块比例,是否过多 -> 看row相关参数(innodb_rows_deleted/insert/read/update)–logfile太小触发写；
一般出现抖动,都是大事务或者logfile有关,查看这两个；
关于写线程也可以调整double write。

日志相关参数

日志性能相关的监控

监控com_commit #查看tps
监控innodb_os_log_fsyncs #写日志数量
监控innodb_log_waits #这个参数一般为0,不为0说明工作空间往logbuffer中写不进去,需要增大logbuffer
监控innodb_os_log_pending_fyncs #大于0时就是有因为线程阻塞而产生的挂起了
监控innodb_os_log_pending_writes #写日志被挂起数量

其他相关参数

innodb_flush_neighbords #刷新邻接页,一般关掉
innodb_large_prefix=1 #等于1是代表建立的索引可以很大
innodb_strict_mode=1 #把某些warning当成error
innodb_write_io_thread #写线程的数量,可以增大,16个还是可以的
innodb_read_io_thread #读线程数量,同上

关于计算

(1)QPS(每秒Query量)

QPS = Questions(or Queries) / seconds
mysql > show  global  status like 'Question%';

(2)TPS(每秒事务量)

TPS = (Com_commit + Com_rollback) / seconds
mysql > show global status like 'Com_commit';
mysql > show global status like 'Com_rollback';

(3)key Buffer 命中率
这里写图片描述

mysql>show  global   status  like   'key%';

key_buffer_read_hits = (1-key_reads / key_read_requests) * 100%
key_buffer_write_hits = (1-key_writes / key_write_requests) * 100%

(4)InnoDB Buffer命中率

mysql> show status like 'innodb_buffer_pool_read%';

innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads(从物理磁盘读的次数) / innodb_buffer_pool_read_requests(从缓存读的次数)) * 100%
(5)Query Cache命中率

mysql> show status like 'Qcache%';

Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%;
(6)Table Cache状态量

mysql> show global  status like 'open%';

比较 open_tables 与 opend_tables 值
(7)Thread Cache 命中率

mysql> show global status like 'Thread%';
mysql> show global status like 'Connections';

Thread_cache_hits = (1 - Threads_created / connections ) * 100%
(8)锁定状态

mysql> show global  status like '%lock%';

Table_locks_waited/Table_locks_immediate=0.3% #如果这个比值比较大的话，说明表锁造成的阻塞比较严重
Innodb_row_lock_waits #innodb行锁，太大可能是间隙锁（行锁的一种，会锁多行）造成的。
(9)复制延时量

mysql > show slave status

查看延时时间
(10) Tmp Table 状况(临时表状况)

mysql > show status like 'Create_tmp%';

Created_tmp_disk_tables/Created_tmp_tables比值最好不要超过10%，如果Created_tmp_tables值比较大，
可能是排序句子过多或者是连接句子不够优化
(11) Binlog Cache 使用状况

mysql > show status like 'Binlog_cache%';

如果Binlog_cache_disk_use值不为0 ，可能需要调大 binlog_cache_size大小
(12) Innodb_log_waits 等待情况

mysql > show status like 'innodb_log_waits';

Innodb_log_waits值不等于0的话，表明 innodb log buffer 因为空间不足而等待

mysql服务器硬件选型

厂商：华为、华三、dell（PowerEdge 系统）、IBM（x系列）
3个口是1U
（实际在官网上买的时候，都可以选）
以买Dell服务器为例：
cpu
内存：32G
RAID卡：本地2块固态盘配raid1，6块机械盘。[NA RAM，非易失缓存]、[有CacheCade]
磁盘：15000转/s –> 服务器最低配
网卡

RAID卡的CacheCade的功能：

找数据时：cpu接内存（32G），内存接写缓存（1G），cache key把固态盘模拟100个G的写缓存，找磁盘(3T)

配置一台服务器最基本的要求

12core/24T
96G内存
600*15000转*SAS*8
raid卡：1G以上NV
1G*2端口*2网卡

监控的主要资源消耗

整体分析思路

  • 1.os层面监控一下主要的资源消耗
    • ①io：负载过重；
    • ②cpu：多个cpu都很繁忙；
    • ③内存：内存消耗殆尽，产生swap；
    • ④网络资源
  • 2.监控mysql分析

Create By LPeng

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/lpeng94/p/12546446.html