mysql的DDL操作對業務產生影響測試

to_be_Dba 發表於 2021-11-30
MySQL

今天稽核一個MySQL指令碼時未發現其中的問題,造成了潛在的風險。記錄下來已備反思!


思考以下問題:

在oracle資料庫中,進行大表欄位刪除時可以kill或中斷嗎?

在mysql資料庫中,進行大表欄位長度擴充套件、欄位新增、欄位刪除操作,可以中斷或kill嗎?



下面通過sysbench、dodba工具來測試一些基本DDL操作對業務的影響。


安裝sysbench

# yum install sysbench


首先在5.7版本中進行測試:


create database sbtest;

準備壓測資料:

sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=root123 --oltp-test-mode=read_only --oltp-tables-count=2 --oltp-table-size=3000000 --threads=10 --time=120 --report-interval=1 prepare

#執行壓力測試:

sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=root123 --oltp-test-mode=read_only --oltp-tables-count=2 --oltp-table-size=3000000 --threads=10 --time=120 --report-interval=1 run >> /root/mytest/mysql5.7_add_column_length_when_execute_readonly_transaction_sysbench.log



壓測過程中進行表修改:

mysql> show create table sbtest1;

+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table   | Create Table                                                                                                                                                                                                                                                                                                           |

+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| sbtest1 | CREATE TABLE `sbtest1` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `k` int(10) unsigned NOT NULL DEFAULT '0',

  `c` char(120) NOT NULL DEFAULT '',

  `pad` char(60) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `k_1` (`k`)

) ENGINE=InnoDB AUTO_INCREMENT=3000001 DEFAULT CHARSET=utf8 MAX_ROWS=1000000 |

+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)


mysql> alter table sbtest1 modify c varchar(140) not null default '';

Query OK, 3000000 rows affected (36.78 sec)

Records: 3000000  Duplicates: 0  Warnings: 0


通過sysbench和dodba的日誌可以看到,在37s的時間內,該表出於不可用狀態


 67s ] thds: 10 tps: 464.08 qps: 9304.61 (r/w/o: 6506.12/1870.32/928.16) lat (ms,95%): 41.10 err/s: 0.00 reconn/s: 0.00

[ 68s ] thds: 10 tps: 525.04 qps: 10364.77 (r/w/o: 7253.54/2061.15/1050.08) lat (ms,95%): 38.94 err/s: 0.00 reconn/s: 0.00

[ 69s ] thds: 10 tps: 489.92 qps: 9902.43 (r/w/o: 6951.90/1970.69/979.84) lat (ms,95%): 44.17 err/s: 0.00 reconn/s: 0.00

[ 70s ] thds: 10 tps: 472.97 qps: 9458.46 (r/w/o: 6608.62/1903.89/945.95) lat (ms,95%): 39.65 err/s: 0.00 reconn/s: 0.00

[ 71s ] thds: 10 tps: 471.09 qps: 9420.74 (r/w/o: 6596.22/1882.35/942.17) lat (ms,95%): 40.37 err/s: 0.00 reconn/s: 0.00

[ 72s ] thds: 10 tps: 501.63 qps: 10029.70 (r/w/o: 7025.89/2000.54/1003.27) lat (ms,95%): 38.94 err/s: 0.00 reconn/s: 0.00

[ 73s ] thds: 10 tps: 484.16 qps: 9705.23 (r/w/o: 6786.26/1950.65/968.32) lat (ms,95%): 42.61 err/s: 0.00 reconn/s: 0.00

[ 74s ] thds: 10 tps: 494.18 qps: 9891.57 (r/w/o: 6922.50/1980.71/988.36) lat (ms,95%): 41.85 err/s: 0.00 reconn/s: 0.00

[ 75s ] thds: 10 tps: 472.97 qps: 9413.36 (r/w/o: 6595.55/1871.87/945.94) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00

[ 76s ] thds: 10 tps: 489.07 qps: 9835.40 (r/w/o: 6873.98/1983.28/978.14) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00

[ 77s ] thds: 10 tps: 512.96 qps: 10183.24 (r/w/o: 7131.47/2025.85/1025.92) lat (ms,95%): 37.56 err/s: 0.00 reconn/s: 0.00

[ 78s ] thds: 10 tps: 496.00 qps: 9968.04 (r/w/o: 6993.03/1983.01/992.00) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00

[ 79s ] thds: 10 tps: 179.00 qps: 3568.06 (r/w/o: 2507.04/703.01/358.01) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00

[ 80s ] thds: 10 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00

[ 81s ] thds: 10 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00

[ 82s ] thds: 10 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00

[ 83s ] thds: 10 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00

[ 84s ] thds: 10 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00

[ 85s ] thds: 10 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00

[ 86s ] thds: 10 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00



dodba日誌:

---------+----load--avg----+-----cpu-usage-----+--swap--+----net----+-------mysql-status-------+-slow---th---+---bytes---

time     |   1m    5m   10m| usr  sys  iow  ide|  si  so| recv  send|   QPS  TPS  ins  upd  del| sql run  con| recv  send

---------+-----------------+-------------------+--------+-----------+--------------------------+-------------+-----------

23:47:53 | 4.89  1.69  0.64|61.9 17.2  7.9  9.8|   0   0|   0K    2K|  7213 2072  517 1036  519|   0   7   12| 627K 19.5M

23:47:54 | 5.46  1.86  0.70|64.4 18.6  6.2  7.2|   0   0|   1K    2K|  6905 1956  490  978  488|   0   4   12| 597K 18.6M

23:47:55 | 5.46  1.86  0.70|63.2 17.4  6.8  9.5|   0   0|   1K    1K|  6751 1948  487  974  487|   0   7   12| 588K 18.2M

23:47:57 | 5.46  1.86  0.70|64.5 16.6  7.4  7.8|   0   0|   0K    1K|  6969 1982  495  992  495|   0   7   12| 603K 18.8M

23:47:57 | 5.46  1.86  0.70|55.2 18.6  7.6 15.9|   0   0|   0K    0K|  6306 1790  446  898  446|   0  12   12| 545K 17.1M

23:47:58 | 5.46  1.86  0.70|31.3  7.5 19.4 40.8|   0   0|   0K    0K|     0    0    0    0    0|   0  12   12|   0K    7K

23:47:59 | 5.18  1.86  0.70|33.7  7.3 12.2 46.3|   0   0|   0K    0K|     0    0    0    0    0|   0  12   12|   0K    7K

……

23:48:28 | 4.03  1.86  0.73|31.8  7.5 17.4 42.3|   0   0|   0K    0K|     0    0    0    0    0|   0  12   12|   0K    7K

23:48:29 | 3.87  1.86  0.74|28.1  7.1 15.8 49.0|   0   0|   0K    0K|     0    0    0    0    0|   0  12   12|   0K    7K

23:48:30 | 3.87  1.86  0.74|29.1  7.3 18.4 44.2|   0   0|   0K    0K|     0    0    0    0    0|   0  12   12|   0K    7K

23:48:31 | 3.87  1.86  0.74|32.8  6.9 17.6 42.2|   0   0|   0K    0K|     0    0    0    0    0|   0  12   12|   0K    7K

23:48:32 | 3.87  1.86  0.74|32.1  7.3 17.6 42.0|   0   0|   0K    0K|     0    0    0    0    0|   0  12   12|   0K    7K

---------+----load--avg----+-----cpu-usage-----+--swap--+----net----+-------mysql-status-------+-slow---th---+---bytes---

time     |   1m    5m   10m| usr  sys  iow  ide|  si  so| recv  send|   QPS  TPS  ins  upd  del| sql run  con| recv  send

---------+-----------------+-------------------+--------+-----------+--------------------------+-------------+-----------

23:48:33 | 3.87  1.86  0.74|30.3  7.2 19.0 43.6|   0   0|   0K    0K|     0    0    0    0    0|   0  12   12|   0K    7K

23:48:34 | 3.64  1.85  0.74|28.9 13.4 17.9 37.8|   0   0|   0K    1K|  2151  605  151  303  151|  10  11   12| 186K  5.7M

23:48:35 | 3.64  1.85  0.74|57.3 19.0  9.9 10.8|   0   0|   0K    0K|  5606 1603  400  802  401|   0   8   12| 486K 15.2M

23:48:36 | 3.64  1.85  0.74|58.9 21.7  8.0  8.0|   0   0|   0K    0K|  6620 1888  472  945  471|   0   7   12| 574K 17.9M

23:48:37 | 3.64  1.85  0.74|57.7 19.4  9.7  9.2|   0   0|   0K    0K|  7046 2006  502 1002  502|   0   3   12| 610K 18.9M

23:48:38 | 3.64  1.85  0.74|61.9 17.7  7.4  9.8|   0   0|   0K    0K|  6826 1951  486  978  487|   0   5   12| 592K 18.5M


結論:5.7版本mysql庫中欄位由char改為varchar,由於要重建表,操作過程中該表完全不可用。



試想,這是對百萬級資料量的表做操作,如果資料量更大、表結構更復雜,需要回滾呢?


2.欄位修改過程中中斷操作

開啟壓測:

sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=root123 --oltp-test-mode=read_only --oltp-tables-count=2 --oltp-table-size=3000000 --threads=10 --time=300 --report-interval=1 run >> /root/mytest/mysql5.7_add_column_length_when_execute_readonly_transaction_sysbench_manual_cancel.log 

進行欄位縮短操作,幾秒鐘後ctrl+c中斷:

mysql> alter table sbtest1 modify c varchar(130) not null default '';  

^C^C -- query aborted

ERROR 1317 (70100): Query execution was interrupted


23:53:25 | 4.51  4.51  2.33|68.5 18.9  4.1  5.0|   0   0|   1K    2K|  7969 2276  569 1138  569|   0   7   12| 691K 21.5M

23:53:26 | 4.51  4.51  2.33|69.1 17.3  4.7  5.2|   0   0|   0K    0K|  7552 2147  535 1075  537|   0   5   12| 653K 20.4M

23:53:26 | 4.51  4.51  2.33|64.8 21.8  3.9  5.6|   0   0|   0K    0K|  7680 2202  550 1102  550|   0  11   12| 667K 20.7M

23:53:27 | 4.51  4.51  2.33|65.8 19.3  4.5  6.9|   0   0|   0K    0K|  8076 2294  575 1146  573|   0   4   12| 699K 21.7M

23:53:28 | 4.51  4.51  2.33|35.2 10.1 13.4 40.2|   0   0|   0K    0K|  2185  615  152  311  152|   0  12   12| 188K  6.0M

23:53:29 | 4.23  4.45  2.32|33.2  7.3 17.1 41.5|   0   0|   0K    0K|     0    0    0    0    0|   0  12   12|   0K    7K

23:53:30 | 4.23  4.45  2.32|30.5  7.1 19.3 42.6|   0   0|   0K    0K|     0    0    0    0    0|   0  12   12|   0K    7K

23:53:31 | 4.23  4.45  2.32|32.5  6.9 16.7 42.9|   0   0|   0K    0K|     0    0    0    0    0|   0  12   12|   0K    7K

23:53:32 | 4.23  4.45  2.32|34.5  7.6 17.8 40.1|   0   0|   0K    0K|     0    0    0    0    0|   0  12   12|   0K    7K

---------+----load--avg----+-----cpu-usage-----+--swap--+----net----+-------mysql-status-------+-slow---th---+---bytes---

time     |   1m    5m   10m| usr  sys  iow  ide|  si  so| recv  send|   QPS  TPS  ins  upd  del| sql run  con| recv  send

---------+-----------------+-------------------+--------+-----------+--------------------------+-------------+-----------

23:53:33 | 4.23  4.45  2.32|31.7  5.5 16.1 45.7|   0   0|   0K    0K|     0    0    0    0    0|   0  12   12|   0K    7K

23:53:34 | 4.69  4.54  2.37|50.2 14.7 10.9 21.8|   0   0|   0K    1K|  3698 1076  271  534  271|   0   6   12| 324K  9.9M

23:53:36 | 4.69  4.54  2.37|61.9 17.0  8.3  9.6|   0   0|   0K    0K|  6760 1942  486  970  486|   0   8   12| 588K 18.3M

23:53:37 | 4.69  4.54  2.37|65.9 18.7  5.1  6.1|   0   0|   0K    0K|  7064 2006  500 1004  502|   0   5   12| 611K 19.0M

23:53:37 | 4.69  4.54  2.37|62.9 18.0  6.6  9.0|   0   0|   0K    0K|  7179 2054  515 1026  513|   0   8   12| 623K 19.4M


結論:DDL操作中斷後表的可用性可以立即恢復。



3.進行增加欄位操作:

mysql> alter table sbtest1 add d varchar(60) not null default 'aaaa';

Query OK, 0 rows affected (37.09 sec)

Records: 0  Duplicates: 0  Warnings: 0



---------+----load--avg----+-----cpu-usage-----+--swap--+----net----+-------mysql-status-------+-slow---th---+---bytes---

time     |   1m    5m   10m| usr  sys  iow  ide|  si  so| recv  send|   QPS  TPS  ins  upd  del| sql run  con| recv  send

---------+-----------------+-------------------+--------+-----------+--------------------------+-------------+-----------

23:56:13 | 9.89  6.82  3.56|68.6 17.2  4.4  6.4|   0   0|   0K    0K|  7431 2130  533 1064  533|   0   5   12| 645K 20.1M

23:56:14 | 9.74  6.84  3.59|69.9 17.1  3.7  6.0|   0   0|   4K    2K|  7227 2051  511 1029  511|   0   2   12| 625K 19.5M

23:56:15 | 9.74  6.84  3.59|65.7 18.2  5.0  7.2|   0   0|   0K    0K|  7015 2012  504 1004  504|   0   7   12| 609K 18.9M

23:56:16 | 9.74  6.84  3.59|68.8 16.3  4.8  6.2|   0   0|   0K    0K|  7287 2086  521 1043  522|   0   4   12| 632K 19.6M

23:56:17 | 9.74  6.84  3.59|66.0 17.6  4.8  8.0|   0   0|   0K    0K|  7829 2236  560 1117  559|   0   6   12| 679K 21.1M

23:56:18 | 9.74  6.84  3.59|68.8 16.8  4.3  6.7|   0   0|   0K    3K|  7095 2025  505 1015  505|   0   2   12| 615K 19.1M

23:56:20 | 9.76  6.89  3.62|62.9 17.6  6.8  9.3|   0   0|   0K    0K|  7301 2088  523 1042  523|   0   5   12| 634K 19.7M

23:56:20 | 9.76  6.89  3.62|67.7 18.4  4.0  6.0|   0   0|   0K    0K|  7543 2151  538 1075  538|   0   2   12| 653K 20.3M

23:56:22 | 9.76  6.89  3.62|68.9 17.0  4.9  5.8|   0   0|   0K    0K|  7253 2088  521 1045  522|   0   8   12| 631K 19.7M  《《《此處開始執行增加欄位操作

23:56:22 | 9.76  6.89  3.62|56.0 18.9 11.9 10.1|   0   0|   0K    0K|  6838 1950  488  975  487|   0  12   12| 593K 18.5M

23:56:23 | 9.76  6.89  3.62|46.7 14.6 21.1 15.6|   0   0|   0K    0K|  3190  895  223  449  223|   0   9   12| 275K  8.5M

23:56:24 | 9.30  6.84  3.62|40.5 13.8 25.6 17.9|   0   0|   0K    0K|  2382  697  175  347  175|   0  12   12| 210K  6.5M

23:56:25 | 9.30  6.84  3.62|31.7 12.2 32.3 22.8|   0   0|   0K    0K|  2079  579  144  291  144|   0   7   12| 179K  5.6M

23:56:26 | 9.30  6.84  3.62|32.4 10.3 36.6 18.8|   0   0|   0K    0K|  2205  645  162  321  162|   0  12   12| 194K  6.0M

23:56:27 | 9.30  6.84  3.62|30.5 10.5 32.1 25.3|   0   0|   0K    0K|  1946  544  134  274  136|   0  12   12| 167K  5.2M

23:56:28 | 9.30  6.84  3.62|28.4  9.6 36.5 23.9|   0   0|   0K    0K|  2071  581  145  293  143|   0   7   12| 179K  5.6M

23:56:29 | 9.27  6.88  3.65|26.5 10.7 34.2 27.6|   0   0|   0K    0K|  1639  474  119  234  121|   0  12   12| 143K  4.3M

23:56:30 | 9.27  6.88  3.65|28.1  9.5 35.2 25.6|   0   0|   0K    0K|  1540  429  107  215  107|   0   9   12| 133K  4.1M

23:56:31 | 9.27  6.88  3.65|31.0 11.7 32.5 22.8|   0   0|   0K    0K|  1934  569  144  283  142|   0  12   12| 171K  5.3M

23:56:32 | 9.27  6.88  3.65|27.3 10.8 39.2 21.6|   0   0|   0K    0K|  1914  537  134  269  134|   0  12   12| 165K  5.1M

---------+----load--avg----+-----cpu-usage-----+--swap--+----net----+-------mysql-status-------+-slow---th---+---bytes---

time     |   1m    5m   10m| usr  sys  iow  ide|  si  so| recv  send|   QPS  TPS  ins  upd  del| sql run  con| recv  send

---------+-----------------+-------------------+--------+-----------+--------------------------+-------------+-----------

23:56:33 | 9.27  6.88  3.65|32.8 10.3 37.7 17.6|   0   0|   0K    0K|  1892  562  141  280  141|   0  12   12| 168K  5.2M

23:56:34 | 8.69  6.80  3.64|28.2  9.7 36.9 23.6|   0   0|   0K    0K|  1634  457  114  229  114|   0   9   12| 141K  4.4M

23:56:35 | 8.69  6.80  3.64|28.7  9.4 33.2 27.2|   0   0|   0K    0K|  1964  571  143  285  143|   0  12   12| 172K  5.3M

23:56:36 | 8.69  6.80  3.64|29.8 10.6 38.4 19.7|   0   0|   0K    0K|  1663  461  113  232  116|   0   8   12| 143K  4.4M

23:56:37 | 8.69  6.80  3.64|28.6 10.7 35.2 24.0|   0   0|   0K    0K|  1758  513  129  256  128|   0  11   12| 155K  4.8M

23:56:38 | 8.69  6.80  3.64|31.3  9.1 34.3 23.7|   0   0|   0K    0K|  1955  561  141  280  140|   0  12   12| 171K  5.3M

23:56:39 | 8.23  6.74  3.64|27.1 10.4 39.6 21.9|   0   0|   0K    0K|  1701  473  117  238  118|   0  10   12| 146K  4.6M

23:56:40 | 8.23  6.74  3.64|27.1  9.4 42.2 19.8|   0   0|   0K    0K|  1819  512  128  257  127|   0  12   12| 157K  4.9M

23:56:41 | 8.23  6.74  3.64|29.8 11.1 33.2 24.5|   0   0|   0K    0K|  1744  520  132  257  131|   0  12   12| 155K  4.8M

23:56:42 | 8.23  6.74  3.64|32.5 11.3 31.4 22.7|   0   0|   0K    0K|  2102  584  145  293  146|   0  11   12| 180K  5.6M

23:56:43 | 8.23  6.74  3.64|35.4 11.2 27.7 24.8|   0   0|   0K    0K|  1914  538  135  269  134|   0   6   12| 166K  5.2M

23:56:44 | 7.89  6.69  3.64|33.2 12.1 32.6 20.5|   0   0|   0K    0K|  2046  605  151  303  151|   0  10   12| 181K  5.6M

23:56:46 | 7.89  6.69  3.64|34.8 10.9 39.3 13.5|   0   0|   0K    0K|  1990  573  144  285  144|   0  12   12| 174K  5.4M

23:56:46 | 7.89  6.69  3.64|32.9 10.1 41.1 15.2|   0   0|   0K    0K|   152   43   10   22   11|   0  12   12|  14K  442K

23:56:47 | 7.89  6.69  3.64|51.6 10.9 21.7 14.1|   0   0|   0K    0K|  2170  621  156  310  155|   0   9   12| 189K  5.9M

23:56:48 | 7.89  6.69  3.64|52.3 14.5 15.5 14.5|   0   0|   0K    0K|  4807 1353  338  677  338|   0   5   12| 414K 12.9M

23:56:49 | 8.14  6.76  3.68|56.9 17.0 10.6 12.2|   0   0|   0K    0K|  5904 1695  424  847  424|   0   6   12| 513K 15.9M

23:56:50 | 8.14  6.76  3.68|53.9 17.2 11.7 13.3|   0   0|   0K    0K|  6793 1937  484  969  484|   0   4   12| 589K 18.3M

23:56:51 | 8.14  6.76  3.68|62.2 17.8  7.4  8.7|   0   0|   0K    0K|  6937 1995  499  997  499|   0   9   12| 604K 18.8M

23:56:52 | 8.14  6.76  3.68|63.5 17.7  7.7  7.2|   0   0|   0K    0K|  7161 2020  505 1010  505|   0   4   12| 618K 19.2M

---------+----load--avg----+-----cpu-usage-----+--swap--+----net----+-------mysql-status-------+-slow---th---+---bytes---

time     |   1m    5m   10m| usr  sys  iow  ide|  si  so| recv  send|   QPS  TPS  ins  upd  del| sql run  con| recv  send

---------+-----------------+-------------------+--------+-----------+--------------------------+-------------+-----------

23:56:53 | 8.14  6.76  3.68|42.6 14.8 21.3 19.1|   0   0|   0K    0K|  3588 1052  263  526  263|   0  12   12| 315K  9.8M

23:56:54 | 8.45  6.85  3.73|48.5 14.6 17.5 16.5|   0   0|   0K    2K|  4038 1148  286  576  286|   0  10   12| 350K 10.9M

23:56:55 | 8.45  6.85  3.73|22.1  9.9 18.2 49.2|   0   0|   0K    0K|   992  288   73  142   73|   0  12   12|  87K  2.7M

23:56:56 | 8.45  6.85  3.73| 8.6  6.1 13.6 70.7|   0   0|   0K    0K|     0    0    0    0    0|   0  12   12|   0K   12K

23:56:57 | 8.45  6.85  3.73|10.3  6.7 18.0 64.4|   0   0|   0K    0K|     0    0    0    0    0|   0  12   12|   0K   12K

23:56:58 | 8.45  6.85  3.73|11.1  6.1 19.7 62.1|   0   0|   0K    0K|     0    0    0    0    0|   0  12   12|   0K   12K   《《《《到此執行結束

23:56:59 | 8.82  6.95  3.78|23.2 12.9 20.1 42.4|   0   0|   0K    0K|   690  139   32   75   32|  10   5   12|  52K  1.8M

23:57:00 | 8.82  6.95  3.78|51.4 16.6 15.4 14.3|   0   0|   0K    0K|  4323 1255  314  627  314|   0  11   12| 378K 11.6M

23:57:01 | 8.82  6.95  3.78|48.9 21.9 18.3  8.7|   0   0|   0K    0K|  3754 1074  267  539  268|   0   7   12| 326K 10.2M

23:57:02 | 8.82  6.95  3.78|51.9 15.3 16.9 13.1|   0   0|   0K    0K|  4157 1180  295  590  295|   0   8   12| 360K 11.2M

23:57:03 | 8.82  6.95  3.78|56.6 16.2 13.1 11.1|   0   0|   0K    0K|  6551 1870  468  935  467|   0   5   12| 568K 17.6M

23:57:04 | 8.91  7.00  3.81|60.1 17.2  9.1  9.6|   0   0|   0K    0K|  6609 1902  476  950  476|   0   9   12| 575K 17.9M


結論:增加欄位過程中表的訪問效能收到影響,最後進行表名修改過程中,有3秒左右的不可用。



4.增加欄位過程中中斷操作

[23:58:58]mysql> alter table sbtest1 add e varchar(60) not null default 'aaaa'; 

[23:59:09]^C^C -- query aborted

[23:59:09]ERROR 1317 (70100): Query execution was interrupted

[23:59:10]mysql> 



[23:58:56]23:58:56 | 5.83  6.77  4.11|63.4 17.8  6.4  7.9|   0   0|   0K    0K|  7392 2120  530 1060  530|   0  11   12| 642K 20.0M

[23:58:57]23:58:57 | 5.83  6.77  4.11|66.0 17.7  5.7  7.2|   0   0|   0K    0K|  7322 2092  523 1046  523|   0  11   12| 635K 19.8M

[23:58:58]23:58:58 | 5.83  6.77  4.11|65.2 19.6  6.0  5.4|   0   0|   0K    0K|  7322 2089  522 1045  522|   0  11   12| 635K 19.8M  《《《《此處開始進行增加欄位操作

[23:58:59]23:58:59 | 6.41  6.87  4.15|53.2 15.6 16.6 12.2|   0   0|   0K    0K|  5295 1506  376  754  376|   0   9   12| 458K 14.3M   

[23:59:00]23:59:00 | 6.41  6.87  4.15|43.8 13.3 24.6 16.3|   0   0|   0K    0K|  2655  759  190  379  190|   0  12   12| 231K  7.2M

[23:59:01]23:59:01 | 6.41  6.87  4.15|41.1 13.3 26.7 16.7|   0   0|   0K    0K|  2563  721  178  363  180|   0   6   12| 221K  6.9M

[23:59:02]23:59:02 | 6.41  6.87  4.15|31.1 11.7 36.2 19.9|   0   0|   0K    0K|  1962  572  145  284  143|   0  11   12| 173K  5.3M

[23:59:03]23:59:03 | 6.41  6.87  4.15|30.7 11.1 31.7 24.6|   0   0|   0K    0K|  1783  502  124  252  126|   0   8   12| 154K  4.8M

[23:59:04]23:59:04 | 6.21  6.82  4.15|30.8 10.7 33.9 23.2|   0   0|   0K    0K|  2016  574  145  286  143|   0   7   12| 175K  5.4M

[23:59:05]23:59:05 | 6.21  6.82  4.15|26.1 10.2 37.5 25.0|   0   0|   0K    0K|  1566  455  114  227  114|   0  11   12| 138K  4.2M

[23:59:06]23:59:06 | 6.21  6.82  4.15|33.0 10.8 31.6 23.1|   0   0|   0K    0K|  2026  574  141  290  143|   0   9   12| 175K  5.5M

[23:59:07]23:59:07 | 6.21  6.82  4.15|32.4 11.2 33.5 21.3|   0   0|   0K    0K|  1862  542  138  268  136|   0  12   12| 164K  5.0M

[23:59:08]23:59:08 | 6.21  6.82  4.15|29.6 10.7 35.2 23.0|   0   0|   0K    0K|  2254  637  158  320  159|   0  10   12| 195K  6.1M

[23:59:09]23:59:09 | 5.96  6.76  4.15|29.5 10.0 36.3 22.6|   0   0|   0K    0K|  1946  554  137  278  139|   0  12   12| 169K  5.3M

[23:59:10]23:59:10 | 5.96  6.76  4.15|55.8 17.0 13.8 10.7|   0   0|   0K    0K|  4287 1233  311  614  308|   0   9   12| 374K 11.6M  《《《此處撤銷了增加欄位操作

[23:59:11]23:59:11 | 5.96  6.76  4.15|63.4 18.3  6.9  8.4|   0   0|   0K    0K|  5617 1588  396  795  397|   0   8   12| 485K 15.1M

[23:59:12]23:59:12 | 5.96  6.76  4.15|63.1 19.7  6.9  6.4|   0   0|   0K    0K|  7354 2113  529 1056  528|   0   6   12| 640K 19.9M

[23:59:13]---------+----load--avg----+-----cpu-usage-----+--swap--+----net----+-------mysql-status-------+-slow---th---+---bytes---

[23:59:13]time     |   1m    5m   10m| usr  sys  iow  ide|  si  so| recv  send|   QPS  TPS  ins  upd  del| sql run  con| recv  send

[23:59:13]---------+-----------------+-------------------+--------+-----------+--------------------------+-------------+-----------

[23:59:13]23:59:13 | 5.96  6.76  4.15|66.8 18.1  5.0  6.5|   0   0|   0K    0K|  7215 2043  509 1025  509|   0   6   12| 623K 19.4M

[23:59:14]23:59:14 | 5.72  6.70  4.14|63.5 19.0  6.5  7.5|   0   0|   0K    0K|  7265 2098  526 1046  526|   0  11   12| 634K 19.6M

[23:59:15]23:59:15 | 5.72  6.70  4.14|63.5 18.5  6.5  8.0|   0   0|   0K    0K|  7091 2013  503 1006  504|   0   4   12| 613K 19.1M

[23:59:16]23:59:16 | 5.72  6.70  4.14|63.5 19.0  6.5  7.0|   0   0|   0K    0K|  6797 1954  489  977  488|   0  10   12| 591K 18.4M

[23:59:17]23:59:17 | 5.72  6.70  4.14|63.5 18.2  6.9  7.9|   0   0|   0K    0K|  7585 2167  542 1083  542|   0  10   12| 658K 20.4M

[23:59:18]23:59:18 | 5.72  6.70  4.14|67.0 18.1  5.3  5.9|   0   0|   0K    0K|  7419 2112  527 1057  528|   0   8   12| 642K 20.0M


結論:增加欄位操作過程中中斷操作,表的可用性立即恢復。



#清理資料:

sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=root123 --oltp-tables-count=2 cleanup



二、8.0版本的測試


結論一致。


mysql增加刪除欄位等DDL操作都是採用重建表方式完成的,相對於oracle來說優勢在於發現造成業務影響時可以隨時停止。

需要重點記住:mysql涉及到增加欄位長度的操作時並不是完全沒影響。
utf8 字符集,由小於等於85擴充套件至86或以上字元
utf8mb4 字符集,由小於等於63擴充套件至64或以上字元
即該欄位擴充套件跨越了255位元組,將導致重建表。
可以參見官方文件的說明:
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26451536/viewspace-2844753/,如需轉載,請註明出處,否則將追究法律責任。