mysql && 优化 && mysql中间件 - 2877206/docs GitHub Wiki

mysql中间件研究（Atlas，cobar，TDDL）

工具与架构

101个MySQL的调优技巧

http://www.ttlsa.com/mysql/101-mysql-tuning-techniques/

# SHOW PROCESSLIST

#监控mysql每个ip的连接数

select SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip;

# /usr/local/mysql/bin/mysql -u root -h127.0.0.1 -e"show processlist\G;"| egrep "Host\:" | awk -F: '{ print }'| sort | uniq -c

# /usr/local/mysql/bin/mysql -u root -h127.0.0.1 --skip-column-names -e"show processlist;"|awk '{print }'|awk -F":" '{print }'|sort|uniq -c

MySQL 慢查询日志分析及可视化结果

http://blog.csdn.net/seteor/article/details/24017913

http://www.ttlsa.com/mysql/mysql-slow-query-log-analysis-and-visualization-of-results/

http://keithlan.github.io/2015/07/15/mysqsla/

# pt-query-digest

找使用CPU多的用户session

  • cpu used by this session

select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value from v$session a,v$process b,v$sesstat c where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;

要统计数据库的连接数，我们通常情况下是统计总数，没有细分到每个IP上。现在要监控每个IP的连接数，实现方式如下：

select SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip;

# mysql -u root -p -e"show processlist\G;"| egrep "Host\:" | awk -F: '{ print }'| sort | uniq -c

# mysql -u root -p  --skip-column-names -e"show processlist;"|awk '{print }'|awk -F":" '{print }'|sort|uniq -

MySQL InnoDB监控

http://www.ttlsa.com/mysql/mysql-innodb-monitoring/

MySQL基线测试

http://www.ttlsa.com/mysql/mysql-test-for-baseline

性能测试

http://www.ttlsa.com/mysql/mysql-mysqlslap-test/

# mysqlslap -a --concurrency=50,100 --number-of-queries 4000 --iterations 5 --debug-info -uroot -p

#将MySQL数据迁移到Redis http://www.ttlsa.com/database/mysql_data_will_be_migrated_to_redis/

案例如下： MySQL数据表结构：

CREATE TABLE events_all_time ( id int(11) unsigned NOT NULL AUTO_INCREMENT, action varchar(255) NOT NULL, count int(11) NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uniq_action (action) ); Redis存储结构： HSET events_all_time [action] [count] 下面是重点，能过下面SQL语句将MySQL输出直接变更成redis-cli可接收的格式：

vim events_to_redis.sql

SELECT CONCAT( "*4\r\n", '$', LENGTH(redis_cmd), '\r\n', redis_cmd, '\r\n', '$', LENGTH(redis_key), '\r\n', redis_key, '\r\n', '$', LENGTH(hkey), '\r\n', hkey, '\r\n', '$', LENGTH(hval), '\r\n', hval, '\r' ) FROM ( SELECT 'HSET' as redis_cmd, 'events_all_time' AS redis_key, action AS hkey, count AS hval FROM events_all_time ) AS t 然后用管道符重定向输出即可：

mysql stats_db --skip-column-names --raw < events_to_redis.sql | redis-cli --pipe

使用redis内部的数据格式然后走pipeline，比遍历mysql一行一行的写redis快多了!

MySQL服务器配置参数详解

http://www.ttlsa.com/mysql/mysql-config-args/

log_query_not_using_indexes

long_query_time

innodb_buffer_pool_instances

innodb_read_io_threads

innodb_write_io_threads={1 .. 64}

innodb_rollback_on_timeout

innodb_thread_concurrency=一般推荐为CPU个数的2倍加上磁盘的个数。默认值为0，表示无上限(不检查并发数量)

max_heap_table_size

log_error=/PATH/TO/ERROR_LOG_FILENAME

have_query_cache

innodb_io_capacity=1500

innodb_buffer_pool_size 42949672960

innodb_log_file_size 1342177280

innodb_io_capacity 2000

innodb_max_dirty_pages_pct 25

innodb_adaptive_flushing ON

innodb_write_io_threads 4

innodb_read_io_threads 4

innodb_max_dirty_pages_pct

MySQL 在大型网站的应用架构演变

http://www.ttlsa.com/mysql/mysql-large-sites-in-the-evolution-of-application-architecture/

V1.0 简单网站架构

1.数据量的总大小 一个机器放不下时

2.数据的索引（B+ Tree）一个机器的内存放不下时

3.访问量(读写混合)一个实例不能承受

V2.0 垂直拆分

1.单实例单业务 依然存在V1.0所述瓶颈

V3.0 主从架构

1.写入量主库不能承受

V4.0 水平拆分

cluster扩容的时候重做数据的成本。

V5.0 云计算

优化

http://www.dlxedu.com/detail/7/478706.html

MySQL优化漏斗

优化慢查有三宝：

# pt-query-digest

# explain

# show profiling

硬件层优化（SSD）

系统层优化（CFQ/NOOP/DEADLINE）

MySQL自身的优化

  • innodb_max_dirty_pages_pct

争议比较大，一般来说都是在75-90之间，主要控制BP中的脏数据刷盘的时机，如果太小会频繁刷盘造成IO上升，如果太大会导致MySQL正常关闭的时候需要很长的时间才能normal shutdown，具体需要看实际场景，个人推荐90

  • innodb_io_capacity

磁盘IO吞吐，具体为缓冲区落地的时候，可以刷脏页的数量，默认200，由于使用了SSD硬盘，所以推荐设置到3000-5000

  • innodb_read_io_threads innodb_write_io_threads

增加后台处理线程的数目，默认为4，推荐改成8

  • sync_binlog innodb_flush_log_at_trx_commit

著名的双1参数，对性能影响非常的大

sync_binlog控制刷binlog的策略，MySQL在每写N次 二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

innodb_flush_log_at_trx_commit控制log buffer刷log file的策略，设置为0的时候每秒刷新一次，设置为1的时候每次commit都会刷新。 从上述描述就可以看出如果追求数据的安全性，那么设置双一是最安全的，如果追求性能最大化，那么双0最合适，这中间可以相差至少2倍的性能。

  • innodb_log_file_size

innodb redo log的size大小，5.5最大4G，5.6最大256G，这个越大可以提升写的性能，大部分时候不需要等待checkpoint覆盖就可以一直write。

  • query_cache_type

看上去很美的东西，但是在实际生产环境中，多次给我们带来了故障，由于每次表的更新都会清空buffer，并且对于sql的匹配是逐个字符效验实际效果很长，大部分时间并没有得到cache的效果，反而得到了很多wait for query cache lock。建议关闭。

以上，仅针对MySQL 5.5，目前我们还在摸索5.6和5.7由于还没有大规模线上使用，所以还谈不上有什么经验。 经验：如果有人力可以投入，可以学习BAT针对数据库进行二次开发，通过path的方式获得更高的性能和稳定性。如果没有人力，只要深入了解MySQL自身参数的影响也可以满足业务的需求，不用一味的追源码级别的开发改造。

业务优化

所谓的业务优化其实说白了很多时候就是index的优化，我们DBA常说一条慢SQL就能将上面所有的优化都付之一炬，CPU直接打满，RT全都都飙升到500ms甚至1s以上。

优化慢查有三宝：

# pt-query-digest # explain # show profiling

首先，使用pt-query-digest可以定位到定位影响最中的慢查是哪条。

然后通过explain具体分析慢查晓的问题所在。

重点查看type，rows和extra这三个字段。

其中type的顺序如下：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

最后，如果问题还是比较严重，可以通过show profiling来定位一下到底是那个环节出现的问题。

架构优化

  • cache为王

热点数据必须使用Redis或者mc之类的cache抗量，让MySQL抗流量是不明智的。

  • 使用队列消峰

众所周知MySQL的异步同步机制是单线程的，所有主库上的并发到从库上都是通过io-thread来慢慢做的，即使主库写入速度再快，从库延迟了，整个集群还是不可用，所以最好采用队列来进行一定的写入消峰，使写入维持在一个较为均衡的水平。

  • 适度的过度设计

很多产品最开始的时候比较小，但是有可能上线之后广受好评一下用活跃度就上来了，这个时候如果数据库出现瓶颈需要拆分需要开发、DBA、架构师等等一起配合来做，而且很有可能没有时间。所以在产品初期进行一定的过度设计会为未来这种情况打好铺垫。最明显的就是拆库拆表，最好在一开始就对业务进行适度的垂直拆分和比较过度的水平拆分，以便应对业务的高速增长。

MySQL翻页优化

http://www.ttlsa.com/mysql/millions_of_millions_data_paging/

禁用mysql query cache

http://www.ttlsa.com/mysql/disable-mysql-query-cache

MySQL运行状态show status详解

http://www.ttlsa.com/mysql/mysql_show_status_descriptsions/

MySQL管理工具MySQL Utilities — 介绍与安装(1)

http://www.ttlsa.com/mysql/mysql-utilities-introduction-and-install/

MySQL读写分离与负载均衡

http://www.ttlsa.com/mysql/read-and-write-separation-and-load-balanced/

http://dev.mysql.com/doc/connector-j/5.1/en/

MySQL水平分区代理Spock Proxy

http://www.ttlsa.com/mysql/mysql-proxy-spock-proxy-1/

Percona Toolkit介绍

http://www.cnblogs.com/zhanjindong/p/3472804.html

https://www.percona.com/doc/percona-toolkit/2.1/index.html#tools

# yum install perl-Digest-MD5.x86_64