Tool for DBA / Operations. Repeats command against chunks of large MySQL table, delay between runs adapts to minimise load (judged by mean execution time of N last runs).

mysql-loop

#!/usr/bin/env python3
import argparse
import itertools
import subprocess
import sys
import time


pk_alphabet_level1 = '0123456789abcdefghijklmnopqrstuvwxyz'.replace('.', '')
pk_alphabet_level2 = '0.......8.......g.......o.....u.....'.replace('.', '')

cmdline = argparse.ArgumentParser()
cmdline.add_argument('-db')
cmdline.add_argument('-pkbegin', default='')
#cmdline.add_argument('-limit', type=int)


def log(fmt, **kwargs):
  sys.stderr.write(fmt.format(**kwargs) + '\n')


def run_timed(command):
  t1 = time.time()
  log('% ' + command)
  subprocess.check_call(command, shell=True)
  t2 = time.time()
  td = int((t2-t1)*1000)
  log(' - {t} ms', t=td)
  return td


def main():
  flags = cmdline.parse_args()
  #run_timed('''mysql -Bvr -D{flags.db} -e "create table ds2 as select * from django_session where false"'''.format(**locals()))
  pk_begin = ''
  if len(sys.argv) > 1:
    pk_begin = sys.argv[1]

  times = [1000] * 10
  delay = 0.5

#  while True:
  for pk_end in map(''.join, itertools.product(pk_alphabet_level1, pk_alphabet_level2)):
    if pk_end < pk_begin:
      continue
#    cmd = '''mysql -BNr -D{flags.db} -e "select session_key from django_session where session_key>'{pk_begin}' order by session_key limit 1 offset {limit}"'''.format(**locals())
#    log('% ' + cmd)
#    pk_end = subprocess.check_output(cmd, shell=True).decode().strip()

    #step_cmd = '''mysql -Bvr -D{flags.db} -e "insert into ds2 select * from django_session where session_key>='{pk_begin}' and length(session_data)!=100 order by session_key limit {limit}"'''.format(**locals())
    #step_cmd = '''mysql -Br -D{flags.db} -e "delete from django_session where session_key>='{pk_begin}' and session_key<'{pk_end}' and length(session_data)=468 order by session_key"'''.format(**locals())
    step_cmd = '''mysql -Br -D{flags.db} -e "insert into ds2 select * from django_session where session_key>='{pk_begin}' and session_key<'{pk_end}' order by session_key"'''.format(**locals())
    step_time = run_timed(step_cmd)

    # automatic delay, depends on mean time of last commands
    times.insert(0, step_time)
    times.pop()
    times_mean = sum(times) / len(times)
    delay_mod = step_time / times_mean
    delay_mod = min(delay_mod, 2)
    delay_mod = max(delay_mod, 0.5)
    delay *= delay_mod
    delay = max(delay, 1)
    delay = min(delay, 301)
    log(' - times mean: {times_mean:.0f} mod: {delay_mod:.1f} delay: {delay:.1f}', **locals())

    if not pk_end:
      log('finish - pk_end empty\n')
      break

    pk_begin = pk_end
    time.sleep(delay)

if __name__ == '__main__':
  try:
    main()
  except KeyboardInterrupt:
    sys.exit(1)