table_open_cache 与 table_definition_cache 对MySQL(内存)的影响

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....

October 13, 2017 · admin

MySQL实例阻塞分析一例(线程statistics状态)

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....

September 23, 2017 · admin

一个简单的数据订阅程序(for DBA)

本程序基于大众点评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....

September 5, 2017 · admin

网易云跟帖迁移评论到disqus

早前折腾博客的时候,在众多评论系统中选择了多说,用了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/

August 29, 2017 · admin

MySQL数据库表结构同步之mysqldiff

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....

August 5, 2017 · admin

ProxySQL PPT分享

前些天在公司内部进行了一次 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/

July 19, 2017 · admin

ProxySQL监控方案

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:...

July 16, 2017 · admin

ProxySQL高可用方案

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。...

July 15, 2017 · admin

ProxySQL之改进patch:记录查询sql完整样例与合并digest多个?

近期一直在思考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++ = '?...

April 27, 2017 · admin

ProxySQL之性能测试对比

本文会通过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....

April 20, 2017 · admin