compaction.py

import mysql.connector

import subprocess

import os

db = 'test'
table = 'lineitem'
version = 'v8.3.0'

tls = ""
# tls = "--ca-path /path/to/ca.crt --cert-path /path/to/client.crt --key-path /path/to/client.pem"

try:
    connection = mysql.connector.connect(
        host="127.0.0.1", 
        port="4000",
        user="root",   
        password="",
        database=f"{db}" 
    )
    if connection.is_connected():
        print("Connected to MySQL")
except mysql.connector.Error as err:
    print(f"Error: {err}")

def get_tikv(region_id):

    cursor = connection.cursor()
    cursor.execute(f'''
                   select address 
                   from information_schema.tikv_region_peers r, information_schema.tikv_store_status s 
                   where r.store_id = s.store_id
                   and r.region_id = {region_id}
                   ''')
    query_result = cursor.fetchall()
    cursor.close()
    return query_result


cursor = connection.cursor()
cursor.execute(f"select region_id from information_schema.tikv_region_status where db_name='{db}' and table_name='{table}'") 
region_ids = cursor.fetchall()
cursor.close()
result_dict = {}
for region_id in region_ids:
    for store_address in get_tikv(region_id[0]):
        cmd = f'tiup ctl:{version} tikv {tls} --host {store_address[0]} region-properties -r ' + str(region_id[0])
        #print(cmd)
        result = subprocess.run(cmd, capture_output=True, text=True, shell=True, env=os.environ.copy())
        #print (result.stdout)
        if result.returncode == 0:
            for line in result.stdout.split('\n'):
                if len(line)>0:
                    key, value = line.split(': ')
                    result_dict[key] = value
            if (float(result_dict['mvcc.num_deletes']) / float(result_dict['mvcc.num_rows']) > .2 or \
            float(result_dict['writecf.num_deletes']) / float(result_dict['writecf.num_entries']) > .2):
                print (f'tiup ctl:{version} tikv {tls} --host {store_address[0]} compact --bottommost force -c write -r ' + str(region_id[0]))
                print (f'tiup ctl:{version} tikv {tls} --host {store_address[0]} compact --bottommost force -c default -r ' + str(region_id[0]))