MySQL · 调优案例 · WMS

发表于 | 更新于 | 分类于 | 阅读次数：

实例信息

实例 角色 版本 业务库 数据量
10.138.98.20:3306 Master 5.7.23 cosmo_base (5) 82.68G
10.138.98.21:3306 Slave 5.7.23 …. ….
10.138.228.40:3306 Master 5.6.39 cosmo_wms_xx (23) 139.5G
10.138.228.41:3306 Slave 5.6.39 …. ….
10.138.228.43:3306 Master 5.6.39 cosmo_wms_xx (23) ….
10.138.42.94:3306 Master 10.1.21 cosmo_wms_xx (9) ….
10.138.232.165:3306 Master 5.6.39 cosmo_xx_xx (30) 155.9G
10.138.232.164:3306 Slave 5.6.39 …. ….
10.138.232.166:3306 Slave 5.6.39 …. ….
10.133.7.31:3306 Master 5.7.23 epg_xx (19) 66.6G 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
# 10.138.98.20:3306按库大小排序
+--------------------+-----------+------------+
| table_schema       | data_size | index_size |
+--------------------+-----------+------------+
| cosmo_base         | 45.28GB   | 37.38GB    |
| cosmo_wms          | 0.17GB    | 0.03GB     |
| xwsy               | 0.09GB    | 0.03GB     |
| cosmo_epg_5001     | 0.02GB    | 0.00GB     |
| xwsytest           | 0.01GB    | 0.00GB     |
+--------------------+-----------+------------+
9 rows in set, 28 warnings (0.30 sec)

# 10.138.228.40:3306按库大小排序
+--------------------+-----------+------------+
| table_schema       | data_size | index_size |
+--------------------+-----------+------------+
| cosmo_wms_1006     | 17.48GB   | 4.73GB     |
| cosmo_wms_1011     | 17.00GB   | 6.16GB     |
| cosmo_wms_1007     | 11.75GB   | 7.36GB     |
| cosmo_wms_1013     | 9.78GB    | 4.60GB     |
| cosmo_wms_1009     | 7.82GB    | 4.17GB     |
| cosmo_wms_1010     | 6.52GB    | 3.48GB     |
| cosmo_wms_1012     | 5.24GB    | 4.32GB     |
| cosmo_wms_1018     | 3.39GB    | 0.88GB     |
| cosmo_wms_1019     | 3.31GB    | 0.66GB     |
| cosmo_wms_2012     | 2.77GB    | 0.61GB     |
| cosmo_wms_2015     | 2.45GB    | 0.46GB     |
| cosmo_wms_1008     | 1.68GB    | 1.46GB     |
| cosmo_wms_1015     | 1.48GB    | 0.68GB     |
| cosmo_wms_2008     | 1.31GB    | 0.29GB     |
| cosmo_wms_2009     | 1.23GB    | 0.21GB     |
| cosmo_wms_2005     | 1.12GB    | 0.23GB     |
| cosmo_wms_2007     | 1.04GB    | 0.22GB     |
| cosmo_wms_2010     | 0.87GB    | 0.18GB     |
| cosmo_wms_2065     | 0.76GB    | 0.30GB     |
| SPC_COM            | 0.48GB    | 0.29GB     |
| cosmo_wms_2006     | 0.27GB    | 0.05GB     |
| cosmo_wms_2066     | 0.04GB    | 0.05GB     |
| cosmo_wms_3001     | 0.02GB    | 0.00GB     |
+--------------------+-----------+------------+
27 rows in set (3.19 sec)

# 10.138.228.43:3306按库大小排序
+--------------------+-----------+------------+
| table_schema       | data_size | index_size |
+--------------------+-----------+------------+
| cosmo_wms_1031     | 13.06GB   | 4.44GB     |
| cosmo_wms_1026     | 9.78GB    | 4.22GB     |
| cosmo_wms_1022     | 5.39GB    | 3.57GB     |
| cosmo_wms_2019     | 5.34GB    | 1.68GB     |
| cosmo_wms_1028     | 4.92GB    | 2.38GB     |
| cosmo_wms_1029     | 4.20GB    | 2.09GB     |
| cosmo_wms_1025     | 3.83GB    | 2.95GB     |
| cosmo_wms_1032     | 3.67GB    | 1.95GB     |
| cosmo_wms_1030     | 2.96GB    | 2.00GB     |
| cosmo_wms_2025     | 2.79GB    | 0.49GB     |
| cosmo_wms_2026     | 2.52GB    | 0.36GB     |
| cosmo_wms_1027     | 2.47GB    | 1.01GB     |
| cosmo_wms_2018     | 1.96GB    | 0.52GB     |
| cosmo_wms_1024     | 1.57GB    | 1.30GB     |
| cosmo_wms_2023     | 1.02GB    | 0.26GB     |
| cosmo_wms_2020     | 0.78GB    | 0.19GB     |
| cosmo_wms_2022     | 0.77GB    | 0.18GB     |
| cosmo_wms_2021     | 0.44GB    | 0.17GB     |
| cosmo_wms_1023     | 0.27GB    | 0.09GB     |
| cosmo_wms_2024     | 0.26GB    | 0.12GB     |
| cosmo_wms_2027     | 0.14GB    | 0.08GB     |
| cosmo_wms          | 0.12GB    | 0.07GB     |
| cosmo_wms_2028     | 0.02GB    | 0.03GB     |
+--------------------+-----------+------------+
27 rows in set (3.19 sec)

# 10.138.42.94:3306按库大小排序
+--------------------+-----------+------------+
| table_schema       | data_size | index_size |
+--------------------+-----------+------------+
| cosmo_wms_uniall   | 8.21GB    | 1.36GB     |
| cosmo_wms          | 6.10GB    | 4.27GB     |
| cosmo_wms_1002     | 4.12GB    | 3.46GB     |
| cosmo_base         | 3.20GB    | 1.57GB     |
| cosmo_wms_1005     | 1.51GB    | 1.03GB     |
| cosmo_wms_test     | 0.75GB    | 1.00GB     |
| cosmo_wms_testn    | 0.48GB    | 0.31GB     |
| cosmo_wms_2001     | 0.39GB    | 0.10GB     |
| cosmo_wms_2002     | 0.14GB    | 0.00GB     |
+--------------------+-----------+------------+
13 rows in set, 1 warning (0.63 sec)

# 10.138.232.165:3306按库大小排序
+------------------------+-----------+------------+
| table_schema           | data_size | index_size |
+------------------------+-----------+------------+
| cosmo_zzkt_1046        | 30.88GB   | 1.80GB     |
| cosmo_hdgt_1003        | 25.19GB   | 3.87GB     |
| cosmo_fsgt_1045        | 23.05GB   | 0.87GB     |
| cosmo_hdzk             | 20.03GB   | 0.69GB     |
| cosmo_xyyj             | 18.18GB   | 3.63GB     |
| cosmo_jndr_1017        | 5.58GB    | 3.21GB     |
| cosmo_zysj             | 2.39GB    | 1.73GB     |
| cosmo_szjsj_1049       | 1.87GB    | 0.85GB     |
| cosmo_tjbl_1021        | 1.45GB    | 0.81GB     |
| cosmo_szjsj_1048       | 1.35GB    | 0.61GB     |
| cosmo_jzzjc_2064       | 1.01GB    | 0.28GB     |
| cosmo_zddr_1057        | 1.00GB    | 0.12GB     |
| cosmo_lycd_1014        | 0.92GB    | 0.05GB     |
| cosmo_wms              | 0.78GB    | 0.52GB     |
| cosmo_wms_penghai      | 0.52GB    | 0.47GB     |
| cosmo_zzktwk_ruig_2053 | 0.49GB    | 0.17GB     |
| cosmo_zzktwk_2054      | 0.16GB    | 0.07GB     |
| cosmo_fsgtwk_2047      | 0.14GB    | 0.05GB     |
| cosmo_zzwk_2048        | 0.12GB    | 0.05GB     |
| cosmo_plat             | 0.12GB    | 0.06GB     |
| cosmo_jzkg_2056        | 0.11GB    | 0.05GB     |
| cosmo_zysjwk_jad       | 0.09GB    | 0.05GB     |
| cosmo_zysjwk_2085      | 0.04GB    | 0.01GB     |
| cosmo_qddz_1055        | 0.03GB    | 0.02GB     |
| cosmo_zysjwk_bw        | 0.02GB    | 0.00GB     |
| cosmo_xyyjwk           | 0.02GB    | 0.00GB     |
| cosmo_eng_1414         | 0.01GB    | 0.00GB     |
| cosmo_penghai_1215     | 0.01GB    | 0.00GB     |
| cosmo_zdbx_1058        | 0.00GB    | 0.00GB     |
| cosmo_plat_penghai     | 0.00GB    | 0.00GB     |
+------------------------+-----------+------------+
33 rows in set, 20 warnings (6.91 sec)

# 10.133.7.31:3306按库大小排序
+--------------------+-----------+------------+
| table_schema       | data_size | index_size |
+--------------------+-----------+------------+
| cosmo_epg_5001     | 9.37GB    | 4.27GB     |
| cosmo_cqjm_5006    | 7.32GB    | 3.72GB     |
| epg_hdme_5004      | 6.64GB    | 3.31GB     |
| epg_hdzn_5015      | 5.85GB    | 3.56GB     |
| epg_hfsj_5007      | 5.44GB    | 2.64GB     |
| epg_jzbp_5002      | 4.44GB    | 1.65GB     |
| cosmo_dljm_5005    | 3.24GB    | 1.08GB     |
| epg_jnjm_5003      | 2.63GB    | 0.64GB     |
| cosmo_wms          | 0.30GB    | 0.13GB     |
| cosmo_plat         | 0.16GB    | 0.07GB     |
| epg_tjwk_2095      | 0.01GB    | 0.00GB     |
| epg_whzn_5012      | 0.00GB    | 0.00GB     |
| epg_cqzn_5009      | 0.00GB    | 0.00GB     |
| epg_hfzn_5013      | 0.00GB    | 0.00GB     |
| epg_qdzn_5016      | 0.00GB    | 0.00GB     |
| epg_sdbp_5011      | 0.00GB    | 0.00GB     |
| epg_whdk_5014      | 0.00GB    | 0.00GB     |
| epg_zzzn_5010      | 0.00GB    | 0.00GB     |
| epg_sddz_5008      | 0.00GB    | 0.00GB     |
+--------------------+-----------+------------+
23 rows in set (0.90 sec)

架构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
wms:
  datasource:
    initialSize: 50
    maxActive: 400
    maxIdle: 50
    minIdle: 50
    maxWait: 10000
    ds1:
      driver: com.mysql.jdbc.Driver
      url: jdbc:mysql://10.138.228.165:3306?juseUnicode=true&characterEncoding=utf-8
      username: wms
      password: xx
    ds2:
      driver: com.mysql.jdbc.Driver
      url: jdbc:mysql://10.133.7.31:3306?useUnicode=true&characterEncoding=utf-8
      username: cosmowms_epg
      password: xx
    ds3:
      driver: com.mysql.jdbc.Driver
      url: jdbc:mysql://10.138.228.165:3306/cosmo_hdzk?useUnicode=true&characterEncoding=utf-8
      username: wms
      password: xx
    ds4:
      driver: com.mysql.jdbc.Driver
      url: jdbc:mysql://10.138.228.165:3306/cosmo_hdzkwk?useUnicode=true&characterEncoding=utf-8
      username: wms
      password: xx

存在的问题

  • 大量使用视图