DB分类整理篇

如今市面上 DB 可谓 琳琅满目, 今天就简单整理,市面上数据库分布:

RDBMS :

oracle, sql server , DB2,  mysql, mariadb, postgresql, microsoft access 等。

nosql:

1, kv:   Memcached, Redis, leveldb

2, 文档:  MongoDB, CouchDB

3, 列式: Cassandra, HBase, Infobright, mariadb columnstore(InfiniDB)

4, 图形: Neo4J, InforGrid

newsql:

1, MPP: ClickHouse, Greenplum

2, SMP:  teradata, Vertica

3, 分布式:  tidb, google spanner,

4, 分布式内存: gemfire, VoltDB.

5. oltp: CUBRID

6. 一体机 :IBM PureData(Netezza), Oracle Exadata, SAP Hana

 

How to scaled out MySQL DATA

目前大多互联网 公司 数据,随着时间流逝,单实例数据没办法支撑 海量数据的读写。

拆分规则: 垂直和水平。

1. 垂直拆分: 降低业务耦合度,可以将不同业务模块所使用表分拆到不同库中,根据不同类型表来进行拆分,对应用程序影响也更小。

2. 水平拆分: 分为库内分表和分库。水平拆分是根据垂直拆分出来的块进行的再次拆分,而不是针对所有表进行的。另一方面,一些负载较高的系统,即使仅仅只是单个表都无法通过单台数据库主机来支持其负载,意味着需要将垂直和水平拆分联合使用。针对热点数据选着性做水平切分。将整个数据库切分整一个分布式矩阵。

3. 水平拆分所引起的问题:

a1. 事物问题

一. 使用分布式事务

优点: 交由数据库管理,简单。

缺点: 当shard越来越多时,性能代价高。

二   由程序和数据库共同控制

原理:将一个跨多个数据库的分布式事务分拆成多个仅处于单个数据库上面的              小事务,并通过应用程序来总控各个小事务。
优点:性能上有优势
缺点:需要应用程序在事务控制上做灵活设计。如果使用了spring的事务管理,                      改动起来会面临一定的困难。

a2.  跨节点join问题

只要是进行切分,跨节点Join的问题是不可避免的。但是良好的设计和切分却可            以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一              次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数                   据。

a3. 跨节点的count,order by,group by以及聚合函数问题

这些是一类问题,因为它们都需要基于全部数据集合进行计算。多数的代理都              不会自动处理合并工作。解决方案:与解决跨节点join问题的类似,分别在各个             节点上得到结果后在应用程序端进行合并。和join不同的是每个结点的查询可               以并行执行,因此很多时候它的速度要比单一大表快很多。但如果结果集很                 大,对应用程序内存的消耗是一个问题。

 

 

MySQL Index Condition Pushdown

官方原理:

摘抄

http://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html

To see how this optimization works, consider first how an index scan proceeds when Index Condition Pushdown is not used:

  1. Get the next row, first by reading the index tuple, and then by using the index tuple to locate and read the full table row.
  2. Test the part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.

When Index Condition Pushdown is used, the scan proceeds like this instead:

  1. Get the next row’s index tuple (but not the full table row).
  2. Test the part of the WHERE condition that applies to this table and can be checked using only index columns. If the condition is not satisfied, proceed to the index tuple for the next row.
  3. If the condition is satisfied, use the index tuple to locate and read the full table row.
  4. Test the remaining part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.

Index Condition Pushdown (ICP)是MySQL 5.6 以上 版本中的新特性,是一种在存储引擎层使用索引过滤数据的一种优化方式。

a 当关闭ICP时,index 仅仅是data access 的一种访问方式,存储引擎通过索引回表获取的数据会传递到MySQL Server 层进行where条件过滤。

b 当打开ICP时,如果部分where条件能使用索引中的字段,MySQL Server 会把这部分下推到引擎层,可以利用index过滤的where条件在存储引擎层进行数据过滤,而非将所有通过index access的结果传递到MySQL server层进行where过滤.

优化效果:ICP能减少引擎层访问基表的次数和MySQL Server 访问存储引擎的次数,减少io次数,提高查询语句性能

set optimizer_switch=’index_condition_pushdown=off';

mysql瑞士军刀–pt工具

Percona-Toolkits

  1. Percona-toolkit 简介

percona-toolkit是一组高级命令行工具的集合,用来执行各种通过手工执行非常复杂和麻烦的mysql任务和系统任务,这些任务包括:

检查master和slave数据的一致性

有效地对记录进行归档

查找重复的索引

对服务器信息进行汇总

分析来自日志和tcpdump的查询

当系统出问题的时候收集重要的系统信息

根据工具类型划分:开发、性能、配置、监控、复制、系统、实用 这7种类型。

可谓是  mysql 的 瑞士军刀~~

二.percona-toolkit的使用

(一. 开发类工具

  1. pt-duplicate-key-checker

功能:

检查mysql中 冗余的索引。

用法:

pt-duplicate-key-checker –user=root  –password=xxxx  –host=localhost  –socket=/tmp/mysql.sock

 

  1. pt-online-schema-change

功能:

不锁表的情况下,修改表结构.

实现原理:

创建一个和你要执行alter操作的表一样的空表结构,执行表结构修改,然后从原表中copy原始数据到表结构修改后的表,当数据copy完成以后就会将原表移走,用新表代替原表,默认动作是将原表drop掉。在copy数据的过程中,任何在原表的更新操作都会更新到新表,因为这个工具在会在原表上创建触发器,触发器会将在原表上更新的内容更新到新表。

用法:

添加索引

pt-online-schema-change -u root -h 10.250.7.50  -p yang –critical-load=”Threads_running=200″ –alter=’add key indx_vid(vid) ‘ –execute D=houyi,t=ga

删除字段

pt-online-schema-change -u root -h 10.250.7.50  -p yang –alter=’drop  column vid ‘ –execute D=houyi,t=ga

远程加索引

pt-online-schema-change -usjh -P3306 -h192.168.11.70 -psjh –alter=’add key idx_c1_c2(c1,c2)’ –execute D=sjh,t=yw

3.       pt-show-grants

功能:导出mysql.user权限。

工具执行流程:

1、先查找所有用户和Host

2、然后逐个执行show grants

用法:

pt-show-grants -u pt -p 213456 -S /tmp/mysql.sock

打印:

— Grants dumped by pt-show-grants

— Dumped from server Localhost via UNIX socket, MySQL 5.5.36-MariaDB-log at 2015-07-10 13:26:17

— Grants for ‘canal’@’192.168.%’

GRANT REPLICATION CLIENT, REPLICATION SLAVE, SELECT ON *.* TO ‘canal’@’192.168.%’ IDENTIFIED BY PASSWORD ‘*219FB8C1D88632A46DF3F9C8039A14BB75051ECF';

— Grants for ‘hc_newRO’@’192.168.1.28′

GRANT USAGE ON *.* TO ‘hc_newRO’@’192.168.1.28′ IDENTIFIED BY PASSWORD ‘*8A66FB579D98E9605C30D86CEF69B569251E5F08′;

GRANT SELECT ON `voice`.* TO ‘hc_newRO’@’192.168.1.28′;

 

  1. pt-upgrade

功能:

在多台服务器上执行查询,并比较有什么不同!这在升级服务器的时候非常有用,可以先安装并导数据到新的服务器上,然后使用这个工具跑一下sql看看有什么不同,可以找出不同版本之间的差异。

用法:

只查看某个sql在两个服务器的运行结果范例:

pt-upgrade h=’localhost’ h=192.168.8.92 –user=root –password=zhang@123 –query=”select * from user_data.collect_data limit 5″

查看文件中的对应sql在两个服务器的运行结果范例:

pt-upgrade h=’localhost’ h=192.168.8.92 –user=root –password=zhang@123  aaa.sql

查看慢查询中的对应的查询SQL在两个服务器的运行结果范例:

pt-upgrade h=’localhost’ h=192.168.8.92 –user=root –password=zhang@123  slow.log

此外还可以执行compare的类型,主要包含三个query_times,results,warnings,比如下面的例子,只比较sql的执行时间

pt-upgrade h=192.168.3.91 h=192.168.8.92 –user=root –password=zhang@123 –query=”select * from user_data.collect_data” –compare query_times

 

() 性能类工具

 

  1. pt-index-usage

功能:

从log文件中读取插叙语句,并用explain分析他们是如何利用索引。完成分析之后会生成一份关于索引没有被查询使用过的报告。

用法:

从满查询中的sql查看索引使用情况范例:

pt-index-usage /data/dbdata/localhost-slow.log –host=localhost –user=root –password=root

将分析结果保存到数据库范例:

pt-index-usage /data/dbdata/localhost-slow.log –host=localhost –user=root –password=zhang@123 –no-report –create-save-results-database

 

  1. pt-pmp

功能:

为查询程序执行聚合的GDB堆栈跟踪,先进性堆栈跟踪,然后将跟踪信息汇总。

用法:

pt-pmp -p 21933

pt-pmp -b /usr/local/mysql/bin/mysqld_safe

 

  1. pt-visual-explain

功能:

格式化explain出来的执行计划按照tree方式输出,方便阅读.

用法:

mysql -uroot -pzhang@123  -e “explain select email from test.collect_data where id=101992419″ |pt-visual-explain

 

()         配置类工具

 

  1. pt-config-diff

功能:

对比配置文件中不一致的地方。

用法:

pt-config-diff h=192.168.8.85 h=192.168.8.22 -uwwg -pwwg

 

8 config differences

Variable                  dev-bbs-db-8-85.sh.hupu.com soccer-web-8-22-dev.sh.h

========================= ========================= =========================

back_log                  4000                      50

basedir                   /usr/local/webserver/D… /usr/local/mysql

big_tables                ON                        OFF

binlog_format             ROW                       MIXED

bulk_insert_buffer_size   67108864                  33554432

datadir                   /data/db-data/hupu.com… /data/mysql/mysql_3306…

expire_logs_days          60                        10

general_log               ON                        OFF

 

  1. pt-mysql-summary

功能:

精细地对mysql的配置和sataus信息进行汇总。

用法:

pt-mysql-summary — –user=root –password=root –host=localhost

 

3.  pt-variable-advisor

功能:

分析mysql的参数变量,并可能对问题参数提出建议。

用法:

pt-variable-advisor –user=root –password=root  localhost

 

() 监控类工具

 

  1. pt-deadlock-logger

功能:

打印死锁信息。

核心参数 :

–daemonize  : 守护进程

–create-dest-table :创建指定的表。

–dest              :创建存储死锁信息的表。

–database          :-D,指定链接的数据库。

–table             :-t,指定存储的表名。

–log               :指定死锁日志信息写入到文件。

–run-time          :运行次数,默认永久

–interval          :运行间隔时间,默认30s。

u,p,h,P           :链接数据库的信息。

 

用法:

把本地死锁信息永久保存在 远端 test.deadlock222的表里

pt-deadlock-logger –user=wwg –password=wwg –host=192.168.8.22 –interval=3  –create-dest-table –dest u=wwg,p=wwg,h=192.168.8.85,D=test,t=deadlock222

 

(root@localhost)[test]> select * from deadlock222;

+————–+———————+——–+——–+———-+——+———–+—-+—–+——-+—————–+———–+———–+———–+——–+———————————————–+

| server       | ts                  | thread | txn_id | txn_time | user | hostname  | ip | db  | tbl   | idx             | lock_type | lock_mode | wait_hold | victim | query                                         |

+————–+———————+——–+——–+———-+——+———–+—-+—–+——-+—————–+———–+———–+———–+——–+———————————————–+

| 192.168.8.22 | 2015-07-10 15:18:30 |     38 |      0 |       25 | root | localhost |    | wwg | test3 | GEN_CLUST_INDEX | RECORD    | X         | w         |      1 | update test3 set name=’helloworld’ where id>1 |

| 192.168.8.22 | 2015-07-10 15:18:30 |     39 |      0 |       11 | root | localhost |    | wwg | test3 | GEN_CLUST_INDEX | RECORD    | X         | w         |      0 | update test3 set name=’www’ where id>1        |

+————–+———————+——–+——–+———-+——+———–+—-+—–+——-+—————–+———–+———–+———–+——–+———————————————–+

 

直接打印:

[root@soccer-web-8-22-dev ~]# pt-deadlock-logger -uroot -proot -hlocalhost

server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query

localhost 2015-07-10T15:18:30 38 0 25 root localhost  wwg test3 GEN_CLUST_INDEX RECORD X w 1 update test3 set name=’helloworld’ where id>1

localhost 2015-07-10T15:18:30 39 0 11 root localhost  wwg test3 GEN_CLUST_INDEX RECORD X w 0 update test3 set name=’www’ where id>1

 

  1. pt-fk-error-logger

功能:

提取和记录mysql外键错误信息。

用法:

pt-fk-error-logger –user=root –password=’root’ h=192.168.1.248 –dest D=test,t=foreign_key_errors

 

写到远端:

pt-fk-error-logger –user=root –password=’root’ h=192.168.1.248 –dest D=test,t=foreign_key_errors –user=root –password=root –host=192.168.1.249

 

  1. pt-mext

功能:

并行查看SHOW GLOBAL STATUS的多个样本的信息。

用法:

每隔10s执行一次SHOW GLOBAL STATUS,并将结果合并到一起查看

pt-mext -r — mysqladmin -uroot -p’root’ –socket=/tmp/mysql.sock  ext -i10 -c2

-i  sleep

-c  迭代多少次

 

  1. pt-query-digest

功能:

分析查询执行日志,并产生一个查询报告,为MySQL、PostgreSQL、 memcached过滤、重放或者转换语句。

用法:

  • 分析本地的慢查询文件

pt-query-digest –user=root –password=zhang@123 /data/dbdata/localhost-slow.log

  1. 重新回顾满查询日志,并将结果保存到query_review中,注意query_review表的表结构必须先建好,表结构如下:

CREATE TABLE query_review ( checksum BIGINT UNSIGNED NOT NULL PRIMARY KEY, fingerprint TEXT NOT NULL, sample TEXT NOT NULL, first_seen DATETIME, last_seen DATETIME, reviewed_by VARCHAR(20), reviewed_on DATETIME, comments TEXT );

 

从远端抓取slowlog

pt-query-digest –user=root –password=zhang@123 –review h=localhost,D=test,t=query_review /data/dbdata/localhost-slow.log

 

() 复制类工具

 

  1. pt-heartbeat

用法:

pt-heartbeat [OPTION…] [DSN] –update|–monitor|–check|–stop

 

主库上起个守护进程

pt-heartbeat -D test –update –user=root –password=zhang@123 -h192.168.8.22 –create-table –daemonize

 

从库监控延迟(实时监控)

pt-heartbeat -D test –monitor –user=wwg –password=wwg -h192.168.8.22 –master-server-id=203306

0.00s [  0.00s,  0.00s,  0.00s ]

0.00s [  0.00s,  0.00s,  0.00s ]

0.00s [  0.00s,  0.00s,  0.00s ]

0.00s [  0.00s,  0.00s,  0.00s ]

0.00s [  0.00s,  0.00s,  0.00s ]

0.00s [  0.00s,  0.00s,  0.00s ]

 

只监控一次就退出

pt-heartbeat -D test –check –user=root –password=zhang@123 -h192.168.8.22 –master-server-id=203306

 

  1. pt-slave-delay

功能:

设置从服务器落后于主服务器指定时间。

用法:

使从落后主1分钟,并每隔15秒钟检测一次,运行10分钟(不加 run-time 会永久执行)

pt-slave-delay –daemonize –user=root –password=root –delay 1m –interval 15s –run-time 10m –host=192.168.3.92

 

  1. pt-slave-find

功能:

查找和打印mysql所有从服务器复制层级关系

用法:

查找主库为192.168.8.22 所有从库

pt-slave-find –user=wwg –password=wwg –host=192.168.8.22

 

  1. pt-slave-restart

功能:

监视mysql复制错误,并尝试重启mysql复制当复制停止的时候。

用法:

跳过错误数:

pt-slave-restart –user=root –password=zhang@123 –host=192.168.3.92 –skip-count=1

跳过错误类型:

pt-slave-restart –user=root –password=zhang@123 –host=192.168.3.92 –error-numbers=1062

 

  1. pt-table-checksum

功能:

检查mysql复制一致性

核心参数:

–no-check-binlog-format 如果不是row格式 都要带上

–replicate-check-only                 只显示问题的表

用法:

主库上执行:

pt-table-checksum –nocheck-replication-filters –no-check-binlog-format –replicate=sjh.checksums –create-replicate-table -h192.168.1.134 -P 3306 -u wwg -p wwg –recursion-method=”processlist”

TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE

07-26T21:23:38      0      3   700000       6       0   3.320 wubx.t5

 

如果数据是一致的DIFFS是等于0的,不等于0表示不一致的。

执行结果显示参数意义:

TS            :完成检查的时间。

ERRORS     :检查时候发生错误和警告的数量。

DIFFS        :0表示一致,大于0表示不致。当指定–no-replicate-check时,会一直为0,当指定

–replicate-check-only会显示不同的信息。

ROWS       :表的行数。

CHUNKS    :被划分到表中的块的数目。

SKIPPED    :由于错误或警告或过大,则跳过块的数目。

TIME         :执行的时间。

TABLE       :被检查的表名。

 

  1. pt-table-sync

功能:

高效同步mysql表的数据

用法:

1.打印 ip 为主库ip

pt-table-sync –replicate=sjh.checksums –print –databases=hc_webgame –charset=utf8 h=192.168.1.134,u=wwg,p=wwg

2.执行

pt-table-sync –replicate=sjh.checksums –execute –databases=hc_webgame –charset=utf8 h=192.168.1.134,u=wwg,p=wwg

 

() 系统类工具

  1. pt-diskstats

功能:

查看服务器的IO。

用法:

pt-diskstats –interval=5 –show-timestamps  –columns-regex=sdb

  1. pt-fifo-split

功能:

可以模拟切割文件,并通过管道传递给先入先出队列而不用真正的切割文件。

用法:

一个每次读取一百万行,指定fifo文件为/tmp/my-fifo,并使用load data命令导入到mysql中。

pt-fifo-split infile.txt –fifo /tmp/my-fifo –lines 1000000 while [ -e /tmp/my-fifo ]; do mysql -e “set foreign_key_checks=0; set sql_log_bin=0; set unique_checks=0; load data local infile ‘/tmp/my-fifo’ into table load_test fields terminated by ‘\t’ lines terminated by ‘\n’ (col1, col2);” sleep 1; done

 

  1. pt-summary

功能:

友好地收集和显示系统信息概况

用法:

pt-summary

 

4.pt-stalk

功能:

在MySQL服务器出现短暂(5~30秒)的性能波动的时候,一般的性能监控工具都很难抓住故障现场,也就很难收集对应较细粒度的诊断信息。另外,如果这种波动出现的频率很低,例如几天才一次,我们也很难人为的抓住现场,收集数据。这正是pt-stalk所解决的问题。

用法:

pt-stalk –collect-tcpdump –function status –variable Threads_connected –threshold 2 –daemonize — –user=root –password=’root’

 

查看日志(或 –log 指定日志输出):

tail -f /var/log/pt-stalk.log

 

查看生成的信息( –dest 指定目录):

ls /var/lib/pt-stalk/

 

=== pt-stalk的触发条件

三种触发条件,通过参数function设置:

status

–function status –variable Threads_connected –threshold 2500,表示MySQL状态值Threads_connected超过2500时触发数据收集。常用的触发条件还可以使用Threads_running等。

processlist

–function processlist –variable State –match statistics –threshold 10,表示,show processlist中State列的值为statistics的线程数超过10则触发收集。

自定义脚本

包含 trg_plugin函数的shell脚本, trg_plugin 函数必须返回一个int值,比如下面的脚本,因为slow log的status是一个累加值,不能分析差值,因此写小脚本来获取差值,对slow突然出现的尖刺进行分析。

 

====其他有用的参数:

–iterations:该参数指定pt-stalk在收集几次故障现场后就退出。默认pt-stalk会一直运行

–run-time:触发收集后,该参数指定收集多长时间的数据。默认是30秒

–sleep:为防止一直触发收集数据,该参数指定在某次触发后,必须sleep一段时候才继续观察并触发收集。默认是300秒

–interval:默认情况pt-stalk会每隔一秒检查一次状态数据,判断是否需要触发收集。该参数指定间隔时间,默认是1秒。

–cycles:默认情况pt-stalk只有连续观察到五次状态值满足触发条件时,才触发收集。该参数控制,需要连续几次满足条件,收集被触发,默认是5次。

 

pt-sift 打印 pt-stalk输出的文件

 

  1. pt-ioprofile

功能:

对某个pid附加一个strace进程进行io分析。

用法:

pt-ioprofile –profile-pid=22544 –cell=sizes

 

先用 iotop -o 排查 什么进程 占用io 更高。

再用 pt-ioprofile  查看 pid 哪些 所属的文件 读写更 频繁。

 

() 实用类工具

 

  • pt-archiver

功能:

将mysql数据库中表的记录归档到另外一个表或者文件,也可以直接进行记录的删除操作。

用法:

  1. 每5000条 删除1次,每隔2000行输出一次 数据(基本不会照成延迟)

pt-archiver –source D=ibilling_pay_hupu,t=purchase_order –user=root –password=’root’ -S /tmp/mysql.sock –where “create_datetime<=’2015-06-20 00:00:00′” –purge –limit=5000 –progress 2000 –why-quit –no-check-charset > /opt/1.log

 

  1. 用于把数据导出文件,不用删除原表中数据

pt-archiver  –source h=127.0.0.1,P=58886,D=test,t=t_info_refresh  –no-check-charset  –where ‘id>1′  –progress 4000  –no-delete –file “/tmp/pt-archiver.sql”  –limit=10000

 

  1. 把数据归档至 归档库(默认保留最后条)。

pt-archiver  –source P=3306,D=wwg,t=yw –user=root –password=root -S /tmp/mysql.sock –dest h=192.168.8.85,P=3306,u=wwg,p=wwg,D=ibilling,t=his_yw  –no-check-charset  –where ‘id < 5001′   –no-delete  –statistics

 

  1. pt-find

功能:

查找mysql表并执行指定的命令。

用法:

1.查找192.168.8.22中1天以前创建的InnoDB的表 ,并打印

pt-find –ctime +1  –host=localhost –engine InnoDB –user=root –password=root

`test`.`deadlocks`

`wwg`.`test3`

`wwg`.`wwg_t1`

 

2.查找192.168.8.22中1天以前更改过的数据库名字匹配%mysql%的并且引擎为MYISAM的表,并将表的引擎更改为InnoDB引擎。

pt-find –mtime +1 –dblike mysql –engine MyISAM –host=localhost –user=root –password=root –exec “ALTER TABLE %D.%N ENGINE=InnoDB”

 

3.查找192.168.8.22中wwg库和zhang库中的空表,并删除。

pt-find –empty wwg zhang –host=192.168.3.135 –user=root –password=zhang@123  –exec-plus “DROP TABLE %s”;

 

4.查找192.168.8.22中超过10G的表:

pt-find –tablesize +10G –host=192.168.3.135 –user=root –password=zhang@123

 

  1. pt-kill

功能:

找出匹配条件 myql 语句 kill掉。

核心参数:

–victims  all   配置所有连接,默认是一个

–interval     30 默认每30秒循环执行一次,可以根据实际情况调整,如果不加,只执行一次;加上的话,会在后台不停的执行。

默认会过滤掉复制线程,请不必担心杀掉复制线程。

–busy-time 批次查询已运行的时间超过这个时间的线程;

–idle-time 杀掉sleep 了多少时间的连接线程,必须在–match-command sleep时才有效

–match-state 匹配状态

Locked

login

copy to tmp table

Copying to tmp table

Copying to tmp table on disk

Creating tmp table

executing

Reading from net

Sending data

Sorting for order

Sorting result

Table lock

Updating

等待

–kill                 杀掉连接并且退出

–kill-query           只杀掉连接执行的语句,但是线程不会被终止

–print                打印满足条件的语句