87 lines
4.6 KiB
Python
87 lines
4.6 KiB
Python
import gzip
|
|
import json
|
|
import os
|
|
import re
|
|
import shutil
|
|
import subprocess
|
|
import time
|
|
|
|
BASE = '/www/backup/txiaw-mysql-fix-20260831'
|
|
config = open('/www/wwwroot/www.txiaw.com/Runtime/Conf/config.php', encoding='utf-8').read()
|
|
cfg = {m.group(1).lower(): m.group(3) for m in re.finditer(r'''['"](db_[a-z_]+)['"]\s*=>\s*(['"])(.*?)\2''', config, re.I)}
|
|
assert cfg['db_name'] == 'xiaxia'
|
|
env = dict(os.environ, MYSQL_PWD=cfg.get('db_pwd', cfg.get('db_password')))
|
|
auth = ['--no-defaults', '-u' + cfg['db_user'], '-h' + cfg['db_host'], '--connect-timeout=5']
|
|
mysql = ['/www/server/mysql/bin/mysql'] + auth + ['-B']
|
|
|
|
def sql(query, timeout=20):
|
|
p = subprocess.run(mysql + ['-e', query], env=env, stdout=subprocess.PIPE, stderr=subprocess.PIPE, universal_newlines=True, timeout=timeout)
|
|
if p.returncode:
|
|
raise RuntimeError(p.stderr[:1500])
|
|
return p.stdout
|
|
|
|
schema = sql('SHOW CREATE TABLE xiaxia.gxl_news; SHOW INDEX FROM xiaxia.gxl_news;')
|
|
schema_path = BASE + '/gxl_news-schema-before.txt'
|
|
if not os.path.exists(schema_path):
|
|
with open(schema_path, 'x') as f:
|
|
f.write(schema)
|
|
os.chmod(schema_path, 0o600)
|
|
|
|
indexes = {}
|
|
for line in sql('SHOW INDEX FROM xiaxia.gxl_news;').splitlines()[1:]:
|
|
cols = line.split('\t')
|
|
indexes.setdefault(cols[2], []).append((int(cols[3]), cols[4]))
|
|
compatible = [name for name, values in indexes.items() if [col for _, col in sorted(values)][:2] == ['news_status', 'news_addtime']]
|
|
if compatible:
|
|
print('SUITABLE_INDEX_ALREADY_EXISTS', compatible, flush=True)
|
|
else:
|
|
if 'idx_news_status_addtime' in indexes:
|
|
raise RuntimeError('Index name exists with different columns; refusing replacement')
|
|
backup = BASE + '/gxl_news-before.sql.gz'
|
|
if not os.path.exists(backup):
|
|
partial = backup + '.partial'
|
|
if os.path.exists(partial):
|
|
with gzip.open(partial, 'rb') as previous:
|
|
if previous.read(1):
|
|
raise RuntimeError('Nonempty partial backup exists; inspect before retry')
|
|
os.rename(partial, partial + '.empty-failed-' + str(int(time.time())))
|
|
print('BACKING_UP_GXL_NEWS_WITH_CONSISTENT_SNAPSHOT', flush=True)
|
|
dump_auth = [a for a in auth if not a.startswith('--connect-timeout=')]
|
|
dump_args = ['/www/server/mysql/bin/mysqldump'] + dump_auth + ['--single-transaction', '--quick', '--skip-lock-tables', '--skip-add-locks', '--hex-blob', '--set-gtid-purged=OFF', 'xiaxia', 'gxl_news']
|
|
with open(BASE + '/dump-stderr.txt', 'wb') as err:
|
|
p = subprocess.Popen(dump_args, env=env, stdout=subprocess.PIPE, stderr=err)
|
|
with open(partial, 'xb') as raw:
|
|
os.chmod(partial, 0o600)
|
|
with gzip.GzipFile(fileobj=raw, mode='wb', compresslevel=1) as target:
|
|
shutil.copyfileobj(p.stdout, target, 1024 * 1024)
|
|
p.stdout.close()
|
|
if p.wait(timeout=90) != 0:
|
|
raise RuntimeError('Backup failed; see private dump-stderr.txt. No ALTER performed.')
|
|
with gzip.open(partial, 'rb') as check:
|
|
while check.read(1024 * 1024):
|
|
pass
|
|
os.replace(partial, backup)
|
|
print('BACKUP_VERIFIED', backup, os.path.getsize(backup), flush=True)
|
|
print('ADDING_SECONDARY_INDEX_INPLACE_LOCK_NONE', flush=True)
|
|
started = time.monotonic()
|
|
sql('SET SESSION lock_wait_timeout=5; ALTER TABLE xiaxia.gxl_news ADD INDEX idx_news_status_addtime (news_status, news_addtime), ALGORITHM=INPLACE, LOCK=NONE;', timeout=90)
|
|
print('INDEX_ADDED_SECONDS', round(time.monotonic() - started, 3), flush=True)
|
|
|
|
query = 'SELECT SQL_NO_CACHE news_id,news_cid,news_name FROM xiaxia.gxl_news WHERE news_status=1 ORDER BY news_addtime DESC LIMIT 100'
|
|
plan = sql('EXPLAIN ' + query + ';')
|
|
print('EXPLAIN_AFTER\n' + plan, flush=True)
|
|
if 'Using filesort' in plan:
|
|
raise RuntimeError('The optimizer still sorts; inspect before claiming success')
|
|
samples = []
|
|
for _ in range(3):
|
|
started = time.monotonic()
|
|
result = sql(query + ';')
|
|
samples.append({'wall_seconds_including_client': round(time.monotonic() - started, 4), 'rows': len(result.splitlines()) - 1})
|
|
assert all(s['rows'] == 100 for s in samples)
|
|
metrics = sql(query + "; SHOW SESSION STATUS WHERE Variable_name IN ('Sort_rows','Sort_scan','Sort_range','Handler_read_key','Handler_read_next','Handler_read_prev');")
|
|
metrics = metrics[metrics.rfind('Variable_name\tValue'):]
|
|
print('QUERY_SAMPLES', json.dumps(samples), flush=True)
|
|
print('SESSION_COUNTERS_AFTER_ONE_QUERY\n' + metrics, flush=True)
|
|
with open(BASE + '/index-result.json', 'w') as f:
|
|
json.dump({'explain': plan, 'samples': samples, 'session_counters': metrics}, f, indent=2)
|