pt-archiver

pt-archiver:把需要归档的行,保存到另外表或文件中(本地或远程)。

1、保存在本地
session 1:

root@lx 05:52:34>select * from t;
+----+----------+---------------------+
| id | tab_name | gmt_create          |
+----+----------+---------------------+
|  2 | a        | 2011-12-04 17:43:05 |
|  3 | a        | 2011-12-04 17:43:06 |
|  4 | a        | 2011-12-04 17:52:34 |
+----+----------+---------------------+

session 2:

$pt-archiver --source h=xx.xx.xx.xx,D=lx,t=t,u=lx,p=lx \
>   --file '/tmp/%Y-%m-%d-%D.%txt'                      \
>   --where "id=2" --limit 1 --commit-each

导出文本内容:

$more 2011-12-04-lx.txt
2       a       2011-12-04 17:43:05

session 1:
id=2的记录已被删除

root@lx 05:52:37>select * from t;
+----+----------+---------------------+
| id | tab_name | gmt_create          |
+----+----------+---------------------+
|  3 | a        | 2011-12-04 17:43:06 |
|  4 | a        | 2011-12-04 17:52:34 |
+----+----------+---------------------+

恢复记录

root@lx 05:57:44>LOAD DATA  INFILE '/tmp/2011-12-04-lx.txt' into table t; 
Query OK, 1 row affected (0.01 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0
 
root@lx 05:59:43>select * from t;
+----+----------+---------------------+
| id | tab_name | gmt_create          |
+----+----------+---------------------+
|  2 | a        | 2011-12-04 17:43:05 |
|  3 | a        | 2011-12-04 17:43:06 |
|  4 | a        | 2011-12-04 17:52:34 |
+----+----------+---------------------+

2、保存在远程

一、远程主机

在远程主机建一张和本地一模一样的表结构
root@lx 08:46:19>CREATE TABLE `t` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `tab_name` varchar(30) NOT NULL,
    ->   `gmt_create` datetime NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.06 sec)

二、本地主机

root@lx 08:43:14>select * from t;
+----+----------+---------------------+
| id | tab_name | gmt_create          |
+----+----------+---------------------+
|  2 | a        | 2011-12-04 17:43:05 |
|  3 | a        | 2011-12-04 17:43:06 |
|  4 | a        | 2011-12-04 17:52:34 |
+----+----------+---------------------+
$pt-archiver --source h=xx.xx.xx.xx,D=lx,t=t,u=lx,p=lx --dest h=xx.xx.xx.xx \
> --where "id=2" --limit 1 --commit-each
 
root@lx 08:48:29>select * from t;
+----+----------+---------------------+
| id | tab_name | gmt_create          |
+----+----------+---------------------+
|  3 | a        | 2011-12-04 17:43:06 |
|  4 | a        | 2011-12-04 17:52:34 |
+----+----------+---------------------+

三、远程主机

root@lx 08:48:11>select * from t;
+----+----------+---------------------+
| id | tab_name | gmt_create          |
+----+----------+---------------------+
|  2 | a        | 2011-12-04 17:43:05 | 
+----+----------+---------------------+

小结:mysql历史迁移可以考虑一下这个小工具,功能也比较丰富。

pt-table-checksum几点测试

1、count(*)和checksum对比

root@xxx03:21:10>select count(*) from t;
+----------+
| count(*) |
+----------+
|  6566404 |
+----------+
1 row in set (3.38 sec)
 
root@xxx03:21:25>checksum table t;
+-------+----------+
| Table | Checksum |
+-------+----------+
| xxx.t |  3579567 |
+-------+----------+
1 row in set (5.39 sec)

小结:经过多次测试,和pt-table-checksum作者测试时间基本相同,checksum近2倍于count操作(big table)

2、pt-table-checksum过程中不锁表(checksum不锁表)
session 1:

/usr/bin/pt-table-checksum h=xxx.xxx.xxx.xxx,u=tb,p=tb h=xxx.xxx.xxx.xxx,u=tb,p=tb -d xxx   -t t
DATABASE TABLE CHUNK HOST           ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
xxx      t         0 xxx.xxx.xxx.xxx InnoDB       NULL          3579567    5    0 NULL NULL
xxx      t         0 xxx.xxx.xxx.xxx InnoDB       NULL          3579567    5    0 NULL NULL

session 2:
读写都不受影响

root@xxx 03:56:15>  insert into t values(99999999,'a',now());
Query OK, 1 row affected (0.00 sec)
root@xxx 03:56:16>select * from t where id=99999999;
8 rows in set (0.00 sec)

3、当slave延迟于master的情况,checksum值不一致

slave:
root@xxx 03:13:47>stop slave;
Query OK, 0 rows affected (0.05 sec)
 
master:
root@xxx 03:14:15> insert into t values(99999999,'a',now());
Query OK, 1 row affected (0.00 sec)
 
/usr/bin/pt-table-checksum h=xxx.xxx.xxx.xxx,u=tb,p=tb h=xxx.xxx.xxx.xxx,u=tb,p=tb -d xxx -t  t
DATABASE TABLE CHUNK HOST           ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
xxx      t         0 xxx.xxx.xxx.xxx  InnoDB       NULL       2703600780    6    0 NULL NULL
xxx      t         0 xxx.xxx.xxx.xxx InnoDB       NULL       3896121768    6    0 NULL NULL

oracle的dbms_output.put_line函数在mysql的实现

在oracle提供相应函数,可以调用dbms_output.put_line函数,打印执行信息到文本,但mysql没有这样函数,其实以前在别网站看到这样写,这里简单记录一下。

DELIMITER ;;
CREATE PROCEDURE `dbms_output.put_line` (output VARCHAR(128))
BEGIN
  SELECT output;
END;;
DELIMITER ;
mysql> CALL `dbms_output.put_line`('ERROR');
+--------+
| output |
+--------+
| ERROR  | 
+--------+

这样可以把dbms_output.put_line函数在业务存储过程中使用,如果出现异常,可以把异常内容重定向到另外文本,用shell脚本去读取异常内容,实现报警。

plugin和builtin版本小差别(一)

mysql版本为5.1.x

builtin:
root@lx 03:15:36>show variables like '%builtin%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| ignore_builtin_innodb | OFF |
+-----------------------+-------+

root@lx 03:19:50>CREATE TABLE `test` (
-> `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
-> `name` varchar(32),
-> primary key(id)
-> ) ENGINE=InnoDB DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.02 sec)

root@lx 03:20:27>insert into test(name) values('luoxuan');
Query OK, 1 row affected (0.00 sec)

root@lx 03:20:40>create index idx_test_n on test(name);
Query OK, 1 row affected (0.53 sec)
Records: 1 Duplicates: 0 Warnings: 0

plugin:
root@xx 03:28:39>show variables like '%builtin%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| ignore_builtin_innodb | ON |
+-----------------------+-------+
root@xx 03:23:12>select @@innodb_version;
+------------------+
| @@innodb_version |
+------------------+
| 1.0.9 |
+------------------+
1 row in set (0.00 sec)

root@xx 03:22:51>CREATE TABLE `test` (
-> `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
-> `name` varchar(32),
-> primary key(id)
-> ) ENGINE=InnoDB DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.17 sec)

root@xx 03:22:54>insert into test(name) values('luoxuan');
Query OK, 1 row affected (0.00 sec)

root@xx 03:23:00>create index idx_test_n on test(name);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0

小结:plugin的快速创建索引特性,省掉了记录多少行数受影响的函数调用。

CPU saturation


当看到r的值很大,但id又是闲置的,可能原因:在较短期间运行的队列很长,其后是闲置时间,在间隔时间内产生非零运行队列和闲置时间。

间隔时间理解:
5%利用率感觉系统很空闲,但这个时间间隔在是60分钟。如果这个发生在3分钟之内呢?这个利用率就会达到100%,所以利用率要和时间相结合来进行评估。