記一次大庫大表的治理過程

一、背景

部門中一核心應(yīng)用，因?yàn)楦鞣N原因其依賴的MySQL數(shù)據(jù)庫一直處于高水位運(yùn)行，無論是硬件資源，還是磁盤使用率或者QPS等都處于較高水位，急需在大促前完成對應(yīng)的治理，降低各項(xiàng)指標(biāo)，以保障在大促期間平穩(wěn)運(yùn)行，以期更好的支撐前端業(yè)務(wù)。

二、基本情況

2.1、數(shù)據(jù)庫

目前該數(shù)據(jù)庫是一主兩從，且都是零售的物理機(jī)，運(yùn)行多年已都是過保機(jī)器。同時(shí)因?yàn)?a href="http://wenjunhu.com/v/tag/132/" target="_blank">CPU和磁盤較大，已無同規(guī)格的物理機(jī)可以增加一個(gè)從庫。同時(shí)其中一個(gè)從庫的內(nèi)存減半且磁盤還是機(jī)械盤，出故障風(fēng)險(xiǎn)極高且IO性能低導(dǎo)致查詢偏慢，出現(xiàn)過多次因性能問題切到另一個(gè)從庫的情況。

以下是其3臺機(jī)器的硬件資源信息，MySQL版本、部署機(jī)房和硬件配置情況。其中135機(jī)器硬盤容量128T是統(tǒng)計(jì)顯示有誤，可以認(rèn)為也是16T。因?yàn)榇疟P做了RAID0，因此實(shí)際容量在7T左右。

2.2、磁盤空間

截止到2月底，各數(shù)據(jù)庫磁盤空間占用情況如下：

從上表咱們可以看出，各數(shù)據(jù)庫的磁盤空間占用已處于較高水位，急需需要治理，通過結(jié)轉(zhuǎn)或刪除數(shù)據(jù)來降低磁盤占用比例。

2.3、表空間

數(shù)據(jù)庫存在大表其中一個(gè)原因是多條業(yè)務(wù)線共用一個(gè)應(yīng)用，同時(shí)代碼層面抽象的部分不夠抽象，擴(kuò)展部分又不容易擴(kuò)展，導(dǎo)致數(shù)據(jù)都糅合和一起。

以下是所有的表空間占用情況，可以明顯看到大部分的表數(shù)據(jù)量都在千萬行以上，特別是前7張表的表空間占用都在100個(gè)G以上，數(shù)據(jù)行數(shù)也都在億級以上，最多的是status表，30億行數(shù)據(jù)，典型的大庫大表。

2.4、QPS情況

黃色的為主庫的QPS，可以看出主庫的查詢量遠(yuǎn)大于從庫，由于各種原因，應(yīng)用代碼里只有少部分的查詢是走的從庫，急需將部分流量大的查詢接口從主庫切到從庫去查詢；

2.5、慢SQL

不論是主庫還是從庫，都有偶發(fā)的慢SQL查詢，引發(fā)磁盤繁忙，影響系統(tǒng)穩(wěn)定性。

三、治理目標(biāo)

1.數(shù)據(jù)結(jié)轉(zhuǎn)，降低磁盤使用率，處較低水位運(yùn)行。

治理目標(biāo)：將表空間占用大于100G的7張表（xxx_status、xxx_main、xxx_exception、xxx_product_code、xxx_item、freights_info、xxx_extend）先進(jìn)行集中結(jié)轉(zhuǎn)，保留一年數(shù)據(jù)后進(jìn)行常態(tài)化結(jié)轉(zhuǎn)，按天結(jié)轉(zhuǎn)，將數(shù)據(jù)量保持在365天；

1.降低主庫QPS，保障主庫安全。

治理目標(biāo)：將主庫的高頻查詢切換到從庫查詢，使主庫白天QPS降低30%，近一個(gè)月上午峰值平均在20k，下午峰值平均在25k；治理的目標(biāo)為：上午峰值15k，下午峰值18k；

1.慢SQL治理，避免導(dǎo)致磁盤繁忙而影響整體業(yè)務(wù)。

治理目標(biāo)：10s以上的徹底消除；5s以上的，消除80%；1s以上的消除60%；底數(shù)是過去一個(gè)月（1s以上慢sql）；

四、治理方案

4.1、大表數(shù)據(jù)結(jié)轉(zhuǎn)

根據(jù)這7張表的業(yè)務(wù)屬性不同，結(jié)轉(zhuǎn)的類型也不相同；比如對于歷史數(shù)據(jù)無意義的，可以將歷史數(shù)據(jù)直接刪除，比如xxx_exception；另外一類是純歷史數(shù)據(jù)，比如流水?dāng)?shù)據(jù)xxx_status表，結(jié)轉(zhuǎn)方式是同步大數(shù)據(jù)平臺后就可以刪除；最后是業(yè)務(wù)主數(shù)據(jù)，是需要同步大數(shù)據(jù)平臺和需要結(jié)轉(zhuǎn)至歷史庫的，比如main、item和extend表等；

以下的統(tǒng)計(jì)表格是在同步大數(shù)據(jù)平臺后集中刪除和結(jié)轉(zhuǎn)的空間釋放情況，在1個(gè)月內(nèi)對數(shù)據(jù)量在1億以上并且占用空間在100G以上的7張大表進(jìn)行了刪除和結(jié)轉(zhuǎn)后刪除，使數(shù)據(jù)在保留365天的業(yè)務(wù)承諾時(shí)間范圍內(nèi)，降低了470G（10%）的磁盤空間占用；

PS：紅色數(shù)字部分為負(fù)值，也就是磁盤的釋放空間。

4.2、攔截?zé)o參數(shù)查詢

運(yùn)單主檔查詢偶發(fā)會有無任何參數(shù)的查詢，引發(fā)嚴(yán)重慢SQL，造成數(shù)據(jù)庫磁盤繁忙度嚴(yán)重飚高，極大地影響了其他業(yè)務(wù)操作，而由于入口眾多和交叉調(diào)用，如果在入口做參數(shù)校驗(yàn)工作量及風(fēng)險(xiǎn)都比較大，所以采用MyBatis的插件機(jī)制在dao層做攔截，直接拒絕掉無參數(shù)的查詢，上線后就再沒有出現(xiàn)過因無參查詢而出現(xiàn)慢SQL而導(dǎo)致的磁盤繁忙情況；

mybatis-config.xml里的plugin配置：

ParameterInterceptor關(guān)鍵代碼如下：

源代碼如下：

import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

import java.lang.reflect.InvocationTargetException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * MyBatis攔截器，用于攔截SQL查詢無入?yún)⒌膱鼍?，避免全表查? *
 */
@Intercepts({
        @Signature(type = Executor.class, method = "query",
                args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})})
public class ParameterInterceptor implements Interceptor {

    private final Map mappedStatementIdMap = new HashMap();

    @Override
    public Object intercept(Invocation invocation) throws InvocationTargetException, IllegalAccessException {
        Object[] queryArgs = invocation.getArgs();

        MappedStatement mappedStatement = null;
        if (queryArgs[0] instanceof MappedStatement) {
            mappedStatement = (MappedStatement) queryArgs[0];
        }

        if (null != mappedStatement && mappedStatementIdMap.containsKey(mappedStatement.getId())) {
            // 獲取SQL
            BoundSql boundSql = mappedStatement.getBoundSql(queryArgs[1]);

            List parameterMappingList = boundSql.getParameterMappings();

            if (parameterMappingList.isEmpty()) {
                Profiler.businessAlarm(mappedStatement.getId(), "查詢參數(shù)為空");
                throw new BadArgumentException("查詢參數(shù)為空，請確認(rèn)入?yún)⑹欠裼兄?);
            }

        }

        return invocation.proceed();
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {
        for (Object key : properties.keySet()) {
            mappedStatementIdMap.put(key, 1);
        }
    }
}

4.3、查詢切從庫

主庫QPS高峰期達(dá)30k/s，長期處于高位運(yùn)行，需要梳理出TOP10的查接口來切從庫查詢，而應(yīng)用中接口眾多，無法逐個(gè)接口查各接口的調(diào)用量，可以利用JSF的filter功能結(jié)合UMP業(yè)務(wù)監(jiān)控來統(tǒng)計(jì)provider的調(diào)用次數(shù)，再通過Python程序獲取統(tǒng)計(jì)數(shù)據(jù)生產(chǎn)統(tǒng)計(jì)報(bào)表。

JSF的配置文件新增filter

JsfInvokeFilter的代碼：

import com.jd.jsf.gd.filter.AbstractFilter;
import com.jd.jsf.gd.msg.RequestMessage;
import com.jd.jsf.gd.msg.ResponseMessage;
import com.jd.jsf.gd.util.RpcContext;
import com.jd.ump.profiler.proxy.Profiler;
import org.springframework.stereotype.Component;
import java.util.HashMap;
import java.util.Map;
/**
 * JSF filter
 * JSF服務(wù)的調(diào)用次數(shù)統(tǒng)計(jì)
 */
@Component
public class JsfInvokeFilter extends AbstractFilter {
    /**
     * 按API接口統(tǒng)計(jì)方法調(diào)用量 - 業(yè)務(wù)監(jiān)控KEY
     */
    private static final String API_PROVIDER_METHOD_COUNT_KEY = "api.jsf.provider.method.count.key";
    private static final String API_CONSUMER_METHOD_COUNT_KEY = "api.jsf.consumer.method.count.key";
    @Override
    public ResponseMessage invoke(RequestMessage requestMessage) {
        String key;
        if (RpcContext.getContext().isProviderSide()) {
            key = API_PROVIDER_METHOD_COUNT_KEY;
        } else {
            key = API_CONSUMER_METHOD_COUNT_KEY;
        }
        String method = requestMessage.getClassName() + "." + requestMessage.getMethodName();
        Map tags = new HashMap(2);
        tags.put("bMark", method);
        tags.put("bCount", "1");
        Profiler.sourceDataByStr(key, tags);
        return getNext().invoke(requestMessage);
    }
}

業(yè)務(wù)監(jiān)控點(diǎn)列表

明細(xì)項(xiàng)

Python腳本

import os
import openpyxl
import json
import requests
from cookies import Cookie
import time

headers = {
    'Cookie': Cookie,
    'Content-Type': 'application/json',
    'token': '******',
    'erp': '******'
}


def get_jsf(start_time, end_time):
    url = 'http://xxx.taishan.jd.com/api/xxx/xxx/xxx/'
    body = {}
    params = {'startTime': start_time,
              'endTime': end_time,
              'endPointKey': 'api.jsf.provider.method.count.key',
              'quickTime': int((end_time - start_time) / 1000),
              'markFlag': 'true',
              'markLimit': 500}

    res = requests.post(url=url, data=json.dumps(body), params=params, headers=headers)

    print('url: ', res.request.url)  # 查看發(fā)送的url
    # print('response: ', res.text)  # 返回請求結(jié)果

    res_json = json.loads(res.text)

    title = ['序號', 'jsf key', '次數(shù)', '占比%', '峰值', '次/秒', '峰值時(shí)間']
    i = 0

    keys = {}
    marks = res_json['response_data']['marks']
    for mark in marks:
        keys.setdefault(mark, [0, 0, 0, ''])

    data = []
    records = res_json['response_data']['monitorData']
    print(len(records))
    for key, value in records.items():
        count = 0
        max_val = 0
        max_time = ''
        for val in value:
            v = val['value']
            count += v
            if v > max_val:
                max_val = v
                max_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(int(val['dateTime'] / 1000)))
        keys[key] = [count, max_val, int(max_val / 1200), max_time]

    key_list = sorted(keys.items(), key=lambda x: x[1], reverse=True)
    # print(key_list)

    all_count = key_list[0][1][0]

    for key in key_list:
        values = [i, key[0], key[1][0], str(round(key[1][0] / all_count * 100, 2)) + '%', key[1][1], key[1][2], key[1][3]]
        data.append(values)
        i += 1
    #
    # # print(data)
    #
    path = r"/Users/xxx/Documents/治理/QPS治理/"
    os.chdir(path)  # 修改工作路徑
    workbook = openpyxl.Workbook()
    sheet = workbook.active
    sheet.title = 'JSF接口調(diào)用次數(shù)統(tǒng)計(jì)'
    sheet.append(title)
    for record in data:
        sheet.append(record)
    workbook.save('JSF接口調(diào)用次數(shù)統(tǒng)計(jì)-' + str(start_time / 1000) + '-' + str(end_time / 1000) + '.xlsx')


def change_time(dt):
    # 轉(zhuǎn)換成時(shí)間數(shù)組
    time_array = time.strptime(dt, "%Y-%m-%d %H:%M:%S")
    # 轉(zhuǎn)換成時(shí)間戳
    timestamp = time.mktime(time_array)
    return int(timestamp * 1000)


if __name__ == '__main__':
    start_time = '2024-03-06 12:20:00'
    end_time = '2024-03-07 12:20:00'

    get_jsf(change_time(start_time), change_time(end_time))

Cookie的代碼如下：

Cookie = '*****'

分析Top10接口的切從庫方案：

通過優(yōu)化讀操作切換至從庫查詢，降低了主庫30%的QPS流量，白天峰值從25k降低到17.5k；

治理前QPS（峰值25k）

治理后QPS（峰值17.5k）

4.4、慢SQL治理

通過對慢SQL設(shè)定有針對性的治理，成功地徹底消除10s以上的慢SQL；5s以上的，消除80%；1s以上的消除60%。

關(guān)于慢SQL的治理不過多介紹，采用的都是通用分析和治理方法，有很多的文章都有介紹。需要注意的是在治理過程中要做好灰度，完全驗(yàn)證后再全量上線運(yùn)行。

五、寫在最后

可能有同學(xué)會想到分庫分表，一個(gè)是在規(guī)劃中提前部署分庫分表，一個(gè)是現(xiàn)在使用分庫分表技術(shù)進(jìn)行治理；關(guān)于前一個(gè)問題由于時(shí)間久遠(yuǎn)咱們不做過多討論，關(guān)于未使用分庫分表進(jìn)行治理的原因是業(yè)務(wù)規(guī)劃的問題，目前此應(yīng)用業(yè)務(wù)較為穩(wěn)定，如采用分庫分表治理動作比較大風(fēng)險(xiǎn)較高，ROI不高，故以上治理方案以穩(wěn)定為主降低風(fēng)險(xiǎn)為輔。

還有一個(gè)治理方案是遷云，利用云計(jì)算的彈性及快速恢復(fù)等特性降低來運(yùn)行風(fēng)險(xiǎn)，因?yàn)闃I(yè)務(wù)的不可中斷性，此方案必須是在線遷移，涉及雙數(shù)據(jù)庫從雙寫到雙讀，再到單讀，最后單寫，還有數(shù)據(jù)一致性檢查和同步等，成本較高。同時(shí)云數(shù)據(jù)庫未能有如此大的磁盤容量和CPU核數(shù)，所以此方案需要結(jié)合分庫分表方案同時(shí)進(jìn)行，更增加了成本和風(fēng)險(xiǎn)，但此方案目前是在計(jì)劃中的，如業(yè)務(wù)有較大幅度增長，以上治理也已無法滿足時(shí)，將采用遷云加分庫分表，且分庫和分表是分期進(jìn)行推進(jìn)。

六、探討

大家在日常及大促中有其他好的治理方案的話，歡迎發(fā)在評論區(qū)一起探討。

審核編輯 黃宇