1. 现象，内存使用大 首先说一下最近遇到的一个现象，因为分库的缘故，单实例里面的表的数量增加了20倍，总数将近达到10000个。在开发环境明显感觉到执行简单查询都很慢，在processlist里面看到状态 opening table 达到好几秒但数据库并没有什么负载。本能的想到应该要加大 table_open_cache，可是加大后发现MySQL刚启动 RES 就占用了2.5G内存，之前才500-600M的样子。 只是将 table_open_cache 从默认的2000，增加到10000（先不论这个值合不合理），就独占了2G的内存，这对于生产环境内存浪费是不可接受的。还好，关于这个问题的讨论有不少，感兴趣的话可以阅读 #bug 68287, #bug 68514, 12015-percona-5-6-14-56-very-high-memory-usage。 Oracle官方工程师并不认为这是个bug，导致初始化分配这么多内存的原因是，开启了 Performance_Schema 。P_S测量数据库的性能指标，需要提前一次性分配内存，而不是随着数据库运行逐渐申请内存。 下表是不同参数组合下内存占用的测试结果： （注：可以通过这个来查看PFS里面哪些占内存比较多，mysql -hxxxx -Pxxx -uxx -pxx -e "show engine performance_schema status"|grep memory|sort -nr -k3 |head ） 对于 table_open_cache 设置的非常大的情况下，即使还有许多cache多余，但P_S都需要分配这个数量的内存。解决这个内存大的问题有3个方向： table_open_cache, table_definition_cache, max_connections 设置合理 关闭 performance_schema 保持 PFS 开启，关闭测量 max_table_instances和max_table_handles performance_schema_max_table_instances: 最大测量多少个表对象 对应 (pfs_table_share).memory，我的环境里固定 277600000 bytes performance_schema_max_table_handles: 最大打开表的总数 对应(pfs_table).memory，随着 table_open_cache 的增大而增大 关闭的方法是在my.cnf里面设置以上变量为 0 。默认是 -1 ，表示 autosize，即根据 table_open_cache/table_def_cache/max_connections 的值自动设置，相关代码 pfs_autosize.cc： PFS_sizing_data *estimate_hints(PFS_global_param *param) { if ((param->m_hints....

1. 现象 某日下午下班后低峰期，现网MySQL一个库突然报出大量慢sql，状态是 statistics，但是过后拿这些sql去执行的时候，实际很快。处于 statistics 状态的线程有个特征：查询的都是视图，但看监控那个时间段并没有明显的update/detele/insert。通过我们的快照程序，去分析当时的 innodb status，发现如下信息： SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 17208994 --Thread 139964610234112 has waited at srv0srv.cc line 2132 for 14.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 0x1635a00 created in file dict0dict.cc line 900 a writer (thread id 139964610234112) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file row0purge.cc line 720 Last time write locked in file /home/admin/146_20161018140650857_13830810_code/rpm_workspace/storage/innobase/srv/srv0srv....

本程序基于大众点评github项目 binlog2sql 二次开发而来，可以实现对源库的binlog实时接收，并组装成增量sql。 原项目默认是把sql输出到控制台，二次开发后的版本把sql放入redis队列，根据需要由另一个程序消费到目标库，模拟了一个“从库”。 在测试时--stop-never在qa环境没有作用，添加了在 BinLogStreamReader 实例里面加入 blocking=True 来保证源源不断的接受binlog而不中断。 另外也加入了更改目标库名的功能，比如原库叫d_my1，生成的sql目标库名是 d_my2 。 项目地址：https://github.com/seanlook/binlog2sql 应用场景 目前想到以下应用场景： 实时同步部分表到另外一个数据库实例 比如在数据库迁库时，将当天表的数据同步到新库，模拟阿里云dms数据传输的功能，相当于在测试环境演练，减少失误。 另外还可以从新库反向同步增量数据到老库，解决测试环境多项目测试引起数据库冲突的问题。 正式切库时的回滚措施 比如数据库迁移项目，切换期间数据写向新库，但如果切换失败需要回滚到老库，就需要把这段时间新增的数据同步回老库（启动消费程序），这就不需要程序段再考虑复杂的回滚设计。 数据库闪回 关于数据库误操作的闪回方案，见 文章MySQL根据离线binlog快速闪回 。binlog2sql的 -B 选项可以将sql反向组装，生产回滚sql。如果需要完善的闪回功能，要进一步开发，提高易用性。 binlog搜索功能 目前组内一版的binlog搜索功能，是离线任务处理的方式，好处是不会占用太大空间，缺点是处理时间较长。通过实时binlog解析过滤的方式，入ES可以快速搜索。需要进一步开发完善。 使用方法 安装好python2.7虚拟环境，安装必要模块：pymysql, mysql-replication, redis, rq pip install -r requirements.txt 注意：pymysqlreplication 库在处理 ‘0000-00-00 00:00:00’ 时有些不尽人意，可能会导致生产的sql在目标库执行失败，还有对datetime(6)类型有个bug，也对它进行了修复，地址：https://github.com/seanlook/python-mysql-replication 。 准备一个redis用于存放sql队列，在环境变量里面设置redis地址 export REDIS_URL='redis://localhost:6379' 在主库执行 show master status 得到binlog开始的文件名和postion，然后开始订阅： binlog2sql原版使用时： $ ~/.pyenv/versions/2.7.10/envs/py2_binlog/bin/python binlog2sql.py -h192.168.1.185 -P3306 -uecuser -pecuser \ -d d_ec_contact --tables t_crm_contact_at \ --start-file='mysql-bin.000001' --start-datetime='2017-08-30 12:30:00' --start-position=6529058 \ --stop-never > contact0....

早前折腾博客的时候，在众多评论系统中选择了多说，用了2年结果多说倒闭了，也算是影响了网络上众多的站点。 于是在16年的时候把评论换成了网易云跟帖，以为有网易这个靠山，体验虽然差点但是不会轻易关闭。云跟帖还提供了从多说直接导入的工具，随意旧的评论直接弄过来了。 可谁想不到一年，网易云跟帖也关闭了。 现在不怎么去折腾博客这玩意了，往里面写写东西才是王道，所以就决定直接把评论系统换成国外的 disqus，总不至于国内种种原因关闭了，代价就是要懂得科学上网，考虑博客的受众都是IT同仁，也就只好这样了。 然而被坑了，网上有许多文章和工具可以从多说迁移到disqus，但是几乎没看到从网易云跟帖迁移到disqus，三者导出的评论格式不一样。云跟帖导出的是 json，disqus导入是扩展的Wordpress格式。 在拖了3个月后，找到了从网易云跟帖备份出来的旧评论文件，简单用python转换了一下，现在可以用了。 WXR格式：https://help.disqus.com/customer/portal/articles/472150-custom-xml-import-format 转换代码gist地址：https://gist.coding.net/u/seanlook/c395cda7c5f4421b85efcd898a8fdf21 (comments_convert.py) 云跟帖导出文件命名为 gentie163.py，懒得用python处理，直接修改这个文件的内容为 python 字典定义： sed -i 's/"url":"xgknight.com/"url":"http:\/\/xgknight.com/g' gentie163.py sed -i 's/false/False/g' gentie163.py sed -i 's/:null/:""/g' gentie163.py sed -i 's/^/comments = /' gentie163.py 字典直接转xml比较容易：http://python3-cookbook.readthedocs.io/zh_CN/latest/c06/p05_turning_dictionary_into_xml.html# 转换后的文件为 data_output.xml: # python3 comments_convert.py 在这个页面导入：https://seanlook.disqus.com/admin/discussions/import/platform/generic/ 可在页面 https://import.disqus.com/ 看到import进度，包括失败信息。（不要重复导入） 说明： disqus每篇文章有个thread_idendifier，这里处理直接根据文章的时间戳转换来用，不影响 dsq:remote是设置单点登录，没去深究，直接丢弃这个属性了 头像信息丢失(因为sso) 本文链接地址：http://xgknight.com/2017/08/29/blog_migrate_gentie163_disqus/

mysqldiff mysql官方有个 mysql-utilities 工具集，其中 mysqldiff 可用于比较两个db之间的表结构。 mysqldiff的语法格式是： $ mysqldiff --server1=user:pass@host:port:socket --server2=user:pass@host:port:socket db1.object1:db2.object1 db3:db4 这个语法有两个用法： db1:db2：如果只指定数据库，那么就将两个数据库中互相缺少的对象显示出来，不比较对象里面的差异。这里的对象包括表、存储过程、函数、触发器等。 如果db1与db2名字相同，可以只指定 db1 db1.object1:db2.object1：如果指定了具体表对象，那么就会详细对比两个表的差异，包括表名、字段名、备注、索引、大小写等所有的表相关的对象。 如果两边db和对象名都相同，可以只指定 db1.object1 接下来看一些主要的参数： --server1：配置server1的连接。 --server2：配置server2的连接。 --character-set：配置连接时用的字符集，如果不显示配置默认使用character_set_client。 --width：配置显示的宽度。 --skip-table-options：保持表的选项不变，即对比的差异里面不包括表名、AUTO_INCREMENT、ENGINE、CHARSET等差异。 -d DIFFTYPE,--difftype=DIFFTYPE：差异的信息显示的方式，有 [unified|context|differ|sql]，默认是unified。如果使用sql，那么就直接生成差异的SQL，这样非常方便。 --changes-for=：修改对象。例如 –changes-for=server2，那么对比以sever1为主，生成的差异的修改也是针对server2的对象的修改。 --show-reverse：在生成的差异修改里面，同时会包含server2和server1的修改。 --force：完成所有的比较，不会在遇到一个差异之后退出 -vv：便于调试，输出许多信息 -q：quiet模式，关闭多余的信息输出 问题修复与增强 但是试用下来，发现有以下几大问题 对象在一方不存在时，比对结果是 object does not exist，而我们通常需要的是，生产 CREATE/DROP XXX 语句 要比对一个db下面所有的对象（table, view, event, proc, func, trigger），要手动挨个 db1.t1, db2.v2…，而 db1:db2只是检查对象是否存在，不会自动比较db1与db2下的所有对象 比较时，auto_increment应该忽略，但是 mysqldiff 只提供 --skip-table-options ，忽略全部表选项，包括 auto_increment, engine, charset等等。 严重bug T1: idx1(f1,f2), T2: idx1(f1)，这种索引会生成 ADD INDEX idx(f2) T1: idx2(f1,f2), idx3(f3,f4), T2: idx4(f5)，这种组合索引，有可能生成的会乱序 这两个bug与mysqldiff的设计有关系，个人觉得它把比较和生产差异sql完全分开，复杂化了。它得到差异结果之后，生成sql又从db捞各种元数据来组装，其实从差异diff里面就可以获得组装需要的数据，也不容易出现隐藏的bug。参考实现 https://github....

前些天在公司内部进行了一次 ProxySQL主题的介绍 《ProxySQL数据库中间件使用实践》，因为proxysql是我调研并引入公司的，有必要跟本组开发同学，进行一个正式的介绍和使用说明，以及我们当前的应用情况。 分享比较偷懒，直接拿来proxysql作者renecannao在 Percona Live Europe 2016 上的PPT，是一个非常全面又具有点睛作用的演示稿了。 {% pdf http://github.com/seanlook/sean-notes-comment/raw/main/static/ProxySQL-Tutorials-PerconaLive.pdf 1000 800 %} PPT来源：https://www.percona.com/live/17/sessions/proxysql-tutorial 另外一个觉得也还不错：https://www.slideshare.net/MyDBOPS/proxysql-for-mysql – 我只是ppt的搬运工 原文连接地址：http://xgknight.com/2017/07/19/proxysql-tutorials-ec/

ProxySQL能监控的信息不多，而且大部分是统计信息，不是性能数据。 mysql> show tables from stats; +--------------------------------+ | tables | +--------------------------------+ | global_variables | | stats_mysql_commands_counters | | stats_mysql_connection_pool | | stats_mysql_global | | stats_mysql_processlist | | stats_mysql_query_digest | | stats_mysql_query_digest_reset | | stats_mysql_query_rules | +--------------------------------+ 主要关心的指标都在表 stats_mysql_global 里面，源代码 diamond 目录下有个 proxysqlstat.py 脚本，是通过SHOW MYSQL STATUS命令，由diamond收集进程将指标上报到Graphite。有以下几个Metrics： 并发数 Active_Transactions Questions 连接相关 Client_Connections_connected Server_Connections_connected Server_Connections_aborted 内存相关 Query_Cache_Entries Query_Cache_Memory_bytes SQLite3_memory_bytes ConnPool_memory_bytes 流量相关 mysql_backend_buffers_bytes mysql_frontend_buffers_bytes mysql_session_internal_bytes 其它 MySQL_Monitor_Workers MySQL_Thread_Workers 但是这些远远不够，还有以下更值得关心的指标： 表 stats_mysql_connection_pool: 对后端DB请求的网络延时 Latency 对后端各个DB的请求数 Queries 后端各个DB的当前活跃连接数 ConnUsed 后端DB的状态 status 表 stats_mysql_processlist:...

MySQL的高可用方案现在如 MHA, Galera, InnoDB Cluster，一旦在上游使用中间件之后，中间件本身可能成为单点。所以本文要介绍的是对于ProxySQL自身高可用的方案对比。 首先ProxySQL自身是通过Angel进程的形式运行，即proxysql如果有崩溃，主进程会自动拉起来。但如果是无响应或者网络故障，则需要另外的机制去做到服务的高可用。本文总结了四种方法。 ProxySQL有关介绍，请参考： http://xgknight.com/2017/04/10/mysql-proxysql-install-config/ 1. 与应用一起部署 所有部署应用的地方，都会部署proxysql节点，当这个proxysql挂掉之后，只影响本机的应用。而且不需要多经过一层网络。 但带来的问题是，如果应用节点很多，proxy的数量也会增加： 会导致proxysql的配置不容易管理 proxysql对后端db健康检查的请求成倍增加 限制每个用户或后端db的 max_connections 特性用不了 2. 集中式部署，多ip引用 后端一个db集群，对应中间两个以上的 proxysql 节点，前端应用配置多个ip地址，随机挑选一个使用，完全无状态。仅需要多经过一次网络代理。 这种方式的好处是，不需要再对数据库这种基础服务，多引入一个软件来实现高可用（如下节的keepalive或consul），由应用端获取数据库连接的代码逻辑处理。 但是因为proxysql访问地址是写在配置文件里面的，如果一个节点挂掉，随机挑选还是会落地这个失败的节点。所以优化方案是，ip列表里面默认取某一个，失败之后再选取下一个重试。 示例代码： proxysql_addr_list = ['192.168.1.175', '192.168.1.176', '192.168.1.177'] proxysql_addr_list_len = 3 hostname = 'this_hostname_for_hash_loadbalance' def get_dbconnection(): list_index = hash(hostname) % proxysql_addr_list_len dbconn = None try: dbconn = DBConnect(dbhost=proxysql_addr_list[ list_index ], dbport=3306) # timeout 1000ms except: if (list_index + 1) == proxysql_addr_list_len: list_index = -1 # like Circular Array dbconn = DBConnec(dbhost=proxysql_addr_list[ list_index + 1 ], dbport=3306) # if failed again, through exception return dbconn 上述并不完美，比如可以改用环形数组轮巡，允许重试其它更多的ip。...

近期一直在思考sql上线审核该怎么做，刚好接触到 ProxySQL 这个中间件，内置了一个计算sql指纹的功能，但是没有记录原始的sql语句。当前正有个紧急的拆库项目也希望知道库上所有的查询。于是把ProxySQL的代码下了回来研究了几天，改了把，加入了两个功能： 在 stats_mysql_query_digest 表上增加 query_text 字段，当第一次出现这个digest_text时，把原始sql记录下来。 修改计算指纹的模块，对 IN或者 VALUES 后面的多个 ? 合并。这个是目前 c_tokenizer.c 文件里没有做的，用到底1点上可以避免重复记录。 效果： 多个 ? 被折叠成 ?,，有些意外情况时 ??，因为后面一些多余空格的缘故，没有像 pt-fingerprint 那样完全模糊化，像这里digest就保留了大小写、去除重复空格、保留 ` 分隔符。但仅有的几种意外情况是可以接受的。 后面的 query_text 列也有些未知情况，就是末尾会加上一些奇怪的字符，还在排除，但大体不影响需求。 代码是基于最新 v1.3.6 稳定版修改的，查看变更 https://github.com/sysown/proxysql/compare/v1.3.6...seanlook:v1.3.7-querysample_digest 多个 ? 合并只涉及到 c_tokenizer.c 文件，分别在flag=4（处理 'abc','def' 的情况）和flag=5（处理 1,2, 3 的情况）加入判断： // wrap two more ? to one ?, if (*(p_r_t-2) == '?' && (*(p_r_t-1) ==' ' || *(p_r_t-1) == ',' || *(p_r_t-1) == '?')){ *(p_r-1) = ','; } else *p_r++ = '?...

本文会通过sysbench对ProxySQL进行基准测试，并与直连的性能进行对比。与此同时也对 Maxscale 和 Qihu360 Atlas 放在一起参考。 提示：压测前确保把query cache完全关掉。 1. proxysql vs 直连 1.1 select nontrx ./bin/sysbench --test=/root/sysbench2/sysbench/tests/db/oltp.lua --mysql-host=10.0.100.36 --mysql-port=6033 --mysql-user=myuser --mysql-password=mypass \ --mysql-db=db15 --oltp-tables-count=20 --oltp-table-size=5000000 --report-interval=20 --oltp-dist-type=uniform --rand-init=on --max-requests=0 --oltp-test-mode=nontrx --oltp-nontrx-mode=select \ --oltp-read-only=on --oltp-skip-trx=on --max-time=120 --num-threads=2 run num-threads依次加大 2 5 10 20 50 100 200 400 {% iframe http://www.tubiaoxiu.com/p.html?s=106165b0eeca215a&web_mode 900 700 %} sysbench线程并发数达到10以下，性能损失在30%以上；达到20，性能损失减少到10%左右。看到proxysql承载的并发数越高，性能损失越少；最好的时候在50线程数，相比直连损失5%。 1.2 oltp dml 混合读写测试。proxysql结果图应该与上面相差无几，因为是主要好在计算 query digest 和规则匹配，与select无异，可参考下节的图示。 sysbench 压测命令： ./bin/sysbench --test=/root/sysbench2/sysbench/tests/db/oltp.lua --mysql-host=10.0.100.34 --mysql-port=3306 --mysql-user=myuser --mysql-password=mypass \ --mysql-db=db15 --oltp-tables-count=20 --oltp-table-size=5000000 --report-interval=20 --oltp-dist-type=uniform --rand-init=on --max-requests=0 --oltp-read-only=off --max-time=120 \ --num-threads=2 run num-threads依次加大 2 5 10 16 20 50 100 200 400 分别对PrxoySQL, Maxscale, Atlas, 直连，四种情况做基准测试 2....