MySQL 在线optimize 77 G 大表记录

1，操作原因：

线上表访问比较慢，update尤其慢，查了下，max(id)有146亿了，但是总记录数才2亿，所以怀疑是以前做了太多delete操作导致碎片过多引起的，所以决定做optimize操作。

2，环境

MySQL cdb实例，版本5.6.34，共享实例非物理独占类型。

3，操作前措施：

为了安全起见，停止应用写操作，应用读操作保留

4，操作方案选择：

刚准备用alter table xxxxx ，虽然5.6 在线ddl不锁全表了，但是考虑到万一故障了，回滚起来就慢的要死，说不定有可能坏表，就暂时放弃了。最终选择pt-online-schema-change工具进行操作。

5，操作时间

半夜1点，应用发布后立即执行操作。

6，操作过程：

[huangshan@db1ys] ~/pt$ bash -x muzhi_2.sh + pt-online-schema-change --recursion-method=none --no-version-check --execute --alter 'engine=innodb;' h=172.20.200.251,P=3306,u=root,p=L0xxFy3e4xhmsaml,D=user_db,t=huayuan_devicesNo slaves found.  See --recursion-method if host TENCENT64.site has slaves.Not checking slave lag because no slaves were found and --check-slave-lag was not specified.Operation, tries, wait:  analyze_table, 10, 1  copy_rows, 10, 0.25  create_triggers, 10, 1  drop_triggers, 10, 1  swap_tables, 10, 1  update_foreign_keys, 10, 1Altering `user_db`.`huayuan_devices`...Creating new table...Created new table user_db._huayuan_devices_new OK.Altering new table...Altered `user_db`.`_huayuan_devices_new` OK.2017-08-30T01:20:26 Creating triggers...2017-08-30T01:20:26 Created triggers OK.2017-08-30T01:20:26 Copying approximately 284468721 rows...Copying `user_db`.`huayuan_devices`:   0% 02:04:22 remainCopying `user_db`.`huayuan_devices`:   0% 02:10:22 remainCopying `user_db`.`huayuan_devices`:   1% 02:15:47 remainCopying `user_db`.`huayuan_devices`:   1% 02:16:21 remainCopying `user_db`.`huayuan_devices`:   1% 02:16:39 remainCopying `user_db`.`huayuan_devices`:   2% 02:16:55 remainCopying `user_db`.`huayuan_devices`:   2% 02:16:53 remainCopying `user_db`.`huayuan_devices`:   2% 02:17:11 remainCopying `user_db`.`huayuan_devices`:   3% 02:17:23 remainCopying `user_db`.`huayuan_devices`:   3% 02:18:07 remainCopying `user_db`.`huayuan_devices`:   3% 02:18:08 remainCopying `user_db`.`huayuan_devices`:   4% 02:18:01 remainCopying `user_db`.`huayuan_devices`:   4% 02:17:45 remainCopying `user_db`.`huayuan_devices`:   4% 02:17:47 remainCopying `user_db`.`huayuan_devices`:   5% 02:17:34 remainCopying `user_db`.`huayuan_devices`:   5% 02:17:52 remainCopying `user_db`.`huayuan_devices`:   5% 02:17:47 remainCopying `user_db`.`huayuan_devices`:   6% 02:17:37 remainCopying `user_db`.`huayuan_devices`:   6% 02:17:19 remainCopying `user_db`.`huayuan_devices`:   6% 02:17:11 remainCopying `user_db`.`huayuan_devices`:   7% 02:16:51 remainCopying `user_db`.`huayuan_devices`:   7% 02:16:33 remainCopying `user_db`.`huayuan_devices`:   7% 02:16:28 remainCopying `user_db`.`huayuan_devices`:   8% 02:16:14 remainCopying `user_db`.`huayuan_devices`:   8% 02:15:56 remainCopying `user_db`.`huayuan_devices`:   8% 02:15:45 remainCopying `user_db`.`huayuan_devices`:   9% 02:15:28 remainCopying `user_db`.`huayuan_devices`:   9% 02:15:09 remainCopying `user_db`.`huayuan_devices`:   9% 02:14:55 remainCopying `user_db`.`huayuan_devices`:  10% 02:14:36 remainCopying `user_db`.`huayuan_devices`:  10% 02:14:08 remainCopying `user_db`.`huayuan_devices`:  10% 02:13:46 remainCopying `user_db`.`huayuan_devices`:  11% 02:13:25 remainCopying `user_db`.`huayuan_devices`:  11% 02:13:05 remainCopying `user_db`.`huayuan_devices`:  11% 02:12:44 remainCopying `user_db`.`huayuan_devices`:  11% 02:12:38 remainCopying `user_db`.`huayuan_devices`:  12% 02:12:10 remainCopying `user_db`.`huayuan_devices`:  12% 02:11:45 remainCopying `user_db`.`huayuan_devices`:  12% 02:11:23 remainCopying `user_db`.`huayuan_devices`:  13% 02:10:57 remainCopying `user_db`.`huayuan_devices`:  13% 02:10:34 remainCopying `user_db`.`huayuan_devices`:  13% 02:10:05 remainCopying `user_db`.`huayuan_devices`:  14% 02:09:49 remainCopying `user_db`.`huayuan_devices`:  14% 02:09:19 remainCopying `user_db`.`huayuan_devices`:  14% 02:08:54 remainCopying `user_db`.`huayuan_devices`:  15% 02:08:28 remainCopying `user_db`.`huayuan_devices`:  15% 02:08:00 remainCopying `user_db`.`huayuan_devices`:  15% 02:07:41 remainCopying `user_db`.`huayuan_devices`:  16% 02:07:14 remainCopying `user_db`.`huayuan_devices`:  16% 02:06:54 remainCopying `user_db`.`huayuan_devices`:  16% 02:06:28 remainCopying `user_db`.`huayuan_devices`:  17% 02:06:03 remainCopying `user_db`.`huayuan_devices`:  17% 02:05:32 remainCopying `user_db`.`huayuan_devices`:  17% 02:05:07 remainCopying `user_db`.`huayuan_devices`:  18% 02:04:42 remainCopying `user_db`.`huayuan_devices`:  18% 02:04:15 remainCopying `user_db`.`huayuan_devices`:  18% 02:03:55 remainCopying `user_db`.`huayuan_devices`:  19% 02:03:29 remainCopying `user_db`.`huayuan_devices`:  19% 02:02:56 remainCopying `user_db`.`huayuan_devices`:  19% 02:02:29 remainCopying `user_db`.`huayuan_devices`:  19% 02:02:04 remainCopying `user_db`.`huayuan_devices`:  20% 02:01:34 remainCopying `user_db`.`huayuan_devices`:  20% 02:01:11 remainCopying `user_db`.`huayuan_devices`:  20% 02:00:41 remainCopying `user_db`.`huayuan_devices`:  21% 02:00:14 remainCopying `user_db`.`huayuan_devices`:  21% 01:59:42 remainCopying `user_db`.`huayuan_devices`:  21% 01:59:14 remainCopying `user_db`.`huayuan_devices`:  22% 01:58:43 remainCopying `user_db`.`huayuan_devices`:  22% 01:58:13 remainCopying `user_db`.`huayuan_devices`:  22% 01:57:50 remainCopying `user_db`.`huayuan_devices`:  23% 01:57:18 remainCopying `user_db`.`huayuan_devices`:  23% 01:56:48 remainCopying `user_db`.`huayuan_devices`:  23% 01:56:16 remainCopying `user_db`.`huayuan_devices`:  24% 01:55:49 remainCopying `user_db`.`huayuan_devices`:  24% 01:55:21 remainCopying `user_db`.`huayuan_devices`:  24% 01:54:52 remainCopying `user_db`.`huayuan_devices`:  25% 01:54:28 remainCopying `user_db`.`huayuan_devices`:  25% 01:53:57 remainCopying `user_db`.`huayuan_devices`:  25% 01:53:29 remainCopying `user_db`.`huayuan_devices`:  26% 01:52:57 remainCopying `user_db`.`huayuan_devices`:  26% 01:52:28 remainCopying `user_db`.`huayuan_devices`:  26% 01:52:00 remainCopying `user_db`.`huayuan_devices`:  27% 01:51:33 remainCopying `user_db`.`huayuan_devices`:  27% 01:51:14 remainCopying `user_db`.`huayuan_devices`:  27% 01:50:47 remainCopying `user_db`.`huayuan_devices`:  28% 01:50:20 remainCopying `user_db`.`huayuan_devices`:  28% 01:49:51 remainCopying `user_db`.`huayuan_devices`:  28% 01:49:24 remainCopying `user_db`.`huayuan_devices`:  29% 01:48:55 remainCopying `user_db`.`huayuan_devices`:  29% 01:48:27 remainCopying `user_db`.`huayuan_devices`:  29% 01:48:02 remainCopying `user_db`.`huayuan_devices`:  29% 01:47:33 remainCopying `user_db`.`huayuan_devices`:  30% 01:47:01 remainCopying `user_db`.`huayuan_devices`:  30% 01:46:32 remainCopying `user_db`.`huayuan_devices`:  30% 01:46:05 remainCopying `user_db`.`huayuan_devices`:  31% 01:45:35 remainCopying `user_db`.`huayuan_devices`:  31% 01:45:08 remainCopying `user_db`.`huayuan_devices`:  31% 01:44:42 remainCopying `user_db`.`huayuan_devices`:  32% 01:44:13 remainCopying `user_db`.`huayuan_devices`:  32% 01:43:45 remainCopying `user_db`.`huayuan_devices`:  32% 01:43:16 remainCopying `user_db`.`huayuan_devices`:  33% 01:42:47 remainCopying `user_db`.`huayuan_devices`:  33% 01:42:16 remainCopying `user_db`.`huayuan_devices`:  33% 01:41:51 remainCopying `user_db`.`huayuan_devices`:  34% 01:41:21 remainCopying `user_db`.`huayuan_devices`:  34% 01:40:49 remainCopying `user_db`.`huayuan_devices`:  34% 01:40:18 remainCopying `user_db`.`huayuan_devices`:  35% 01:39:47 remainCopying `user_db`.`huayuan_devices`:  35% 01:39:16 remainCopying `user_db`.`huayuan_devices`:  35% 01:38:45 remainCopying `user_db`.`huayuan_devices`:  36% 01:38:18 remainCopying `user_db`.`huayuan_devices`:  36% 01:37:47 remainCopying `user_db`.`huayuan_devices`:  36% 01:37:15 remainCopying `user_db`.`huayuan_devices`:  37% 01:36:44 remainCopying `user_db`.`huayuan_devices`:  37% 01:36:13 remainCopying `user_db`.`huayuan_devices`:  37% 01:35:41 remainCopying `user_db`.`huayuan_devices`:  38% 01:35:10 remainCopying `user_db`.`huayuan_devices`:  38% 01:34:41 remainCopying `user_db`.`huayuan_devices`:  38% 01:34:09 remainCopying `user_db`.`huayuan_devices`:  39% 01:33:37 remainCopying `user_db`.`huayuan_devices`:  39% 01:33:05 remainCopying `user_db`.`huayuan_devices`:  39% 01:32:33 remainCopying `user_db`.`huayuan_devices`:  40% 01:32:00 remainCopying `user_db`.`huayuan_devices`:  40% 01:31:30 remainCopying `user_db`.`huayuan_devices`:  40% 01:31:01 remainCopying `user_db`.`huayuan_devices`:  41% 01:30:29 remainCopying `user_db`.`huayuan_devices`:  41% 01:29:58 remainCopying `user_db`.`huayuan_devices`:  41% 01:29:26 remainCopying `user_db`.`huayuan_devices`:  42% 01:28:53 remainCopying `user_db`.`huayuan_devices`:  42% 01:28:22 remainCopying `user_db`.`huayuan_devices`:  42% 01:27:52 remainCopying `user_db`.`huayuan_devices`:  43% 01:27:21 remainCopying `user_db`.`huayuan_devices`:  43% 01:26:49 remainCopying `user_db`.`huayuan_devices`:  43% 01:26:18 remainCopying `user_db`.`huayuan_devices`:  44% 01:25:46 remainCopying `user_db`.`huayuan_devices`:  44% 01:25:14 remainCopying `user_db`.`huayuan_devices`:  44% 01:24:42 remainCopying `user_db`.`huayuan_devices`:  45% 01:24:14 remainCopying `user_db`.`huayuan_devices`:  45% 01:23:41 remainCopying `user_db`.`huayuan_devices`:  45% 01:23:08 remainCopying `user_db`.`huayuan_devices`:  46% 01:22:37 remainCopying `user_db`.`huayuan_devices`:  46% 01:22:05 remainCopying `user_db`.`huayuan_devices`:  46% 01:21:34 remainCopying `user_db`.`huayuan_devices`:  47% 01:21:02 remainCopying `user_db`.`huayuan_devices`:  47% 01:20:33 remainCopying `user_db`.`huayuan_devices`:  47% 01:20:01 remainCopying `user_db`.`huayuan_devices`:  48% 01:19:31 remainCopying `user_db`.`huayuan_devices`:  48% 01:18:59 remainCopying `user_db`.`huayuan_devices`:  48% 01:18:28 remainCopying `user_db`.`huayuan_devices`:  49% 01:17:57 remainCopying `user_db`.`huayuan_devices`:  49% 01:17:27 remainCopying `user_db`.`huayuan_devices`:  49% 01:16:59 remainCopying `user_db`.`huayuan_devices`:  50% 01:16:29 remainCopying `user_db`.`huayuan_devices`:  50% 01:15:57 remainCopying `user_db`.`huayuan_devices`:  50% 01:15:25 remainCopying `user_db`.`huayuan_devices`:  51% 01:14:53 remainCopying `user_db`.`huayuan_devices`:  51% 01:14:21 remainCopying `user_db`.`huayuan_devices`:  51% 01:13:52 remainCopying `user_db`.`huayuan_devices`:  52% 01:13:20 remainCopying `user_db`.`huayuan_devices`:  52% 01:12:49 remainCopying `user_db`.`huayuan_devices`:  52% 01:12:17 remainCopying `user_db`.`huayuan_devices`:  53% 01:11:46 remainCopying `user_db`.`huayuan_devices`:  53% 01:11:14 remainCopying `user_db`.`huayuan_devices`:  53% 01:10:43 remainCopying `user_db`.`huayuan_devices`:  54% 01:10:13 remainCopying `user_db`.`huayuan_devices`:  54% 01:09:40 remainCopying `user_db`.`huayuan_devices`:  54% 01:09:08 remainCopying `user_db`.`huayuan_devices`:  55% 01:08:36 remainCopying `user_db`.`huayuan_devices`:  55% 01:08:04 remainCopying `user_db`.`huayuan_devices`:  55% 01:07:31 remainCopying `user_db`.`huayuan_devices`:  56% 01:07:02 remainCopying `user_db`.`huayuan_devices`:  56% 01:06:30 remainCopying `user_db`.`huayuan_devices`:  56% 01:05:59 remainCopying `user_db`.`huayuan_devices`:  57% 01:05:28 remainCopying `user_db`.`huayuan_devices`:  57% 01:04:56 remainCopying `user_db`.`huayuan_devices`:  57% 01:04:24 remainCopying `user_db`.`huayuan_devices`:  58% 01:03:52 remainCopying `user_db`.`huayuan_devices`:  58% 01:03:22 remainCopying `user_db`.`huayuan_devices`:  58% 01:02:51 remainCopying `user_db`.`huayuan_devices`:  59% 01:02:19 remainCopying `user_db`.`huayuan_devices`:  59% 01:01:45 remainCopying `user_db`.`huayuan_devices`:  59% 01:01:14 remainCopying `user_db`.`huayuan_devices`:  60% 01:00:41 remainCopying `user_db`.`huayuan_devices`:  60% 01:00:07 remainCopying `user_db`.`huayuan_devices`:  60% 59:36 remainCopying `user_db`.`huayuan_devices`:  61% 59:05 remainCopying `user_db`.`huayuan_devices`:  61% 58:33 remainCopying `user_db`.`huayuan_devices`:  61% 58:02 remainCopying `user_db`.`huayuan_devices`:  62% 57:29 remainCopying `user_db`.`huayuan_devices`:  62% 56:58 remainCopying `user_db`.`huayuan_devices`:  62% 56:30 remainCopying `user_db`.`huayuan_devices`:  63% 55:57 remainCopying `user_db`.`huayuan_devices`:  63% 55:26 remainCopying `user_db`.`huayuan_devices`:  63% 54:54 remainCopying `user_db`.`huayuan_devices`:  64% 54:22 remainCopying `user_db`.`huayuan_devices`:  64% 53:51 remainCopying `user_db`.`huayuan_devices`:  64% 53:19 remainCopying `user_db`.`huayuan_devices`:  65% 52:50 remainCopying `user_db`.`huayuan_devices`:  65% 52:19 remainCopying `user_db`.`huayuan_devices`:  65% 51:47 remainCopying `user_db`.`huayuan_devices`:  66% 51:16 remainCopying `user_db`.`huayuan_devices`:  66% 50:44 remainCopying `user_db`.`huayuan_devices`:  66% 50:12 remainCopying `user_db`.`huayuan_devices`:  67% 49:43 remainCopying `user_db`.`huayuan_devices`:  67% 49:12 remainCopying `user_db`.`huayuan_devices`:  67% 48:40 remainCopying `user_db`.`huayuan_devices`:  68% 48:08 remainCopying `user_db`.`huayuan_devices`:  68% 47:37 remainCopying `user_db`.`huayuan_devices`:  68% 47:05 remainCopying `user_db`.`huayuan_devices`:  69% 46:34 remainCopying `user_db`.`huayuan_devices`:  69% 46:04 remainCopying `user_db`.`huayuan_devices`:  69% 45:33 remainCopying `user_db`.`huayuan_devices`:  70% 45:01 remainCopying `user_db`.`huayuan_devices`:  70% 44:29 remainCopying `user_db`.`huayuan_devices`:  70% 43:57 remainCopying `user_db`.`huayuan_devices`:  71% 43:25 remainCopying `user_db`.`huayuan_devices`:  71% 42:55 remainCopying `user_db`.`huayuan_devices`:  72% 42:23 remainCopying `user_db`.`huayuan_devices`:  72% 41:51 remainCopying `user_db`.`huayuan_devices`:  72% 41:19 remainCopying `user_db`.`huayuan_devices`:  73% 40:48 remainCopying `user_db`.`huayuan_devices`:  73% 40:16 remainCopying `user_db`.`huayuan_devices`:  73% 39:45 remainCopying `user_db`.`huayuan_devices`:  74% 39:14 remainCopying `user_db`.`huayuan_devices`:  74% 38:41 remainCopying `user_db`.`huayuan_devices`:  74% 38:10 remainCopying `user_db`.`huayuan_devices`:  75% 37:38 remainCopying `user_db`.`huayuan_devices`:  75% 37:07 remainCopying `user_db`.`huayuan_devices`:  75% 36:35 remainCopying `user_db`.`huayuan_devices`:  76% 36:05 remainCopying `user_db`.`huayuan_devices`:  76% 35:34 remainCopying `user_db`.`huayuan_devices`:  76% 35:02 remainCopying `user_db`.`huayuan_devices`:  77% 34:29 remainCopying `user_db`.`huayuan_devices`:  77% 33:58 remainCopying `user_db`.`huayuan_devices`:  77% 33:26 remainCopying `user_db`.`huayuan_devices`:  78% 32:57 remainCopying `user_db`.`huayuan_devices`:  78% 32:25 remainCopying `user_db`.`huayuan_devices`:  78% 31:53 remainCopying `user_db`.`huayuan_devices`:  79% 31:22 remainCopying `user_db`.`huayuan_devices`:  79% 30:51 remainCopying `user_db`.`huayuan_devices`:  79% 30:19 remainCopying `user_db`.`huayuan_devices`:  80% 29:48 remainCopying `user_db`.`huayuan_devices`:  80% 29:18 remainCopying `user_db`.`huayuan_devices`:  80% 28:45 remainCopying `user_db`.`huayuan_devices`:  81% 28:14 remainCopying `user_db`.`huayuan_devices`:  81% 27:42 remainCopying `user_db`.`huayuan_devices`:  81% 27:10 remainCopying `user_db`.`huayuan_devices`:  82% 26:39 remainCopying `user_db`.`huayuan_devices`:  82% 26:09 remainCopying `user_db`.`huayuan_devices`:  82% 25:37 remainCopying `user_db`.`huayuan_devices`:  83% 25:06 remainCopying `user_db`.`huayuan_devices`:  83% 24:35 remainCopying `user_db`.`huayuan_devices`:  84% 24:03 remainCopying `user_db`.`huayuan_devices`:  84% 23:32 remainCopying `user_db`.`huayuan_devices`:  84% 23:02 remainCopying `user_db`.`huayuan_devices`:  85% 22:30 remainCopying `user_db`.`huayuan_devices`:  85% 21:59 remainCopying `user_db`.`huayuan_devices`:  85% 21:27 remainCopying `user_db`.`huayuan_devices`:  86% 20:56 remainCopying `user_db`.`huayuan_devices`:  86% 20:25 remainCopying `user_db`.`huayuan_devices`:  86% 19:54 remainCopying `user_db`.`huayuan_devices`:  87% 19:23 remainCopying `user_db`.`huayuan_devices`:  87% 18:52 remainCopying `user_db`.`huayuan_devices`:  87% 18:20 remainCopying `user_db`.`huayuan_devices`:  88% 17:48 remainCopying `user_db`.`huayuan_devices`:  88% 17:17 remainCopying `user_db`.`huayuan_devices`:  88% 16:46 remainCopying `user_db`.`huayuan_devices`:  89% 16:16 remainCopying `user_db`.`huayuan_devices`:  89% 15:45 remainCopying `user_db`.`huayuan_devices`:  89% 15:14 remainCopying `user_db`.`huayuan_devices`:  90% 14:42 remainCopying `user_db`.`huayuan_devices`:  90% 14:11 remainCopying `user_db`.`huayuan_devices`:  90% 13:40 remainCopying `user_db`.`huayuan_devices`:  91% 13:11 remainCopying `user_db`.`huayuan_devices`:  91% 12:39 remainCopying `user_db`.`huayuan_devices`:  91% 12:08 remainCopying `user_db`.`huayuan_devices`:  92% 11:37 remainCopying `user_db`.`huayuan_devices`:  92% 11:06 remainCopying `user_db`.`huayuan_devices`:  92% 10:36 remainCopying `user_db`.`huayuan_devices`:  93% 10:07 remainCopying `user_db`.`huayuan_devices`:  93% 09:40 remainCopying `user_db`.`huayuan_devices`:  93% 09:11 remainCopying `user_db`.`huayuan_devices`:  94% 08:42 remainCopying `user_db`.`huayuan_devices`:  94% 08:14 remainCopying `user_db`.`huayuan_devices`:  94% 07:45 remainCopying `user_db`.`huayuan_devices`:  95% 07:17 remainCopying `user_db`.`huayuan_devices`:  95% 06:49 remainCopying `user_db`.`huayuan_devices`:  95% 06:22 remainCopying `user_db`.`huayuan_devices`:  96% 05:54 remainCopying `user_db`.`huayuan_devices`:  96% 05:26 remainCopying `user_db`.`huayuan_devices`:  96% 04:57 remainCopying `user_db`.`huayuan_devices`:  97% 04:28 remainCopying `user_db`.`huayuan_devices`:  97% 03:59 remainCopying `user_db`.`huayuan_devices`:  97% 03:30 remainCopying `user_db`.`huayuan_devices`:  97% 03:03 remainCopying `user_db`.`huayuan_devices`:  98% 02:33 remainCopying `user_db`.`huayuan_devices`:  98% 02:04 remainCopying `user_db`.`huayuan_devices`:  98% 01:35 remainCopying `user_db`.`huayuan_devices`:  99% 01:07 remainCopying `user_db`.`huayuan_devices`:  99% 00:37 remainCopying `user_db`.`huayuan_devices`:  99% 00:09 remain2017-08-30T03:54:44 Copied rows OK.2017-08-30T03:54:44 Analyzing new table...2017-08-30T03:54:44 Swapping tables...2017-08-30T03:54:44 Swapped original and new tables OK.2017-08-30T03:54:44 Dropping old table...2017-08-30T03:55:20 Dropped old table `user_db`.`_huayuan_devices_old` OK.2017-08-30T03:55:20 Dropping triggers...2017-08-30T03:55:20 Dropped triggers OK.Successfully altered `user_db`.`huayuan_devices`.[huangshan@db1ys] ~/pt$