An Ang Mo Geek in Singapore

MySQL: deleting a huge amount of rows

One of our MySQL tables has started to grow out of control with more than 1 billion rows (that’s 109).

The table is a typical “Rails Active-Record table” with id as primary key (auto increment), created_at, updated_at and a few columns for the business data.

The table has multiple indexes on various columns, some of them having a cardinality in the millions. created_at and status don’t have an index.

The majority of results from Google, involved peoples with millions of rows and deleting them in batches of 1000. We had the problem amplified by 103.

The setup

MySQL 5.6 running on AWS-RDS on a db.r3.2xlarge (8 vCPU, 61GiB RAM).

The problem

Inserting new rows was becoming an issue, specially at peak hours when request concurrency was high. Some inserts would take several seconds and indexes creation would just put the CPU to it’s knees.

Some rows in the past are still needed and can’t be deleted. But most of the rows are obsolete and can be removed.

The table is heavily used 24/7 and the services using it can’t therefore easily be stopped, even for a short amount of time.

Running on RDS, SELECT INTO OUTFILE is not an option.

Failed attempts

Single delete

The easiest would be to run a single delete query, but with several millions of rows concerned and the lack of index on created_at, this would put the server on fire.

delete from table where created_at<(NOW() - INTERVAL 1 MONTH) and status='obsolete';

Add index

Adding an index on created_at and status is too time consuming and would lock the table for too long.

Create a new table

Since the services can’t be stopped, solutions like TRUNCATE TABLE or INSERT INTO SELECT ...; RENAME; aren’t a solution.

Delete in batches

The first attempt to batch-delete failed too.

delete from table where created_at<(NOW() - INTERVAL 1 MONTH) and status='obsolete' order by id limit 1000;

The lack of indexes on the concerned fields made selecting 1000 rows too slow.

The solution

The solution was to export the data to Athena and get a list of id’s to delete.

The advantage of Athena, it allows to execute queries on big amount of data in a timely manner. It also gives a backup to the data that will be deleted from MySQL. The caveat is that the data needs to be prepared and structured the right way. Queries need to make heavy use of partitions to be efficient and cost effective.

Export the data to S3

Define some variables.

DBUSER=my_username
DBPASS=my_secret_password
DBHOST=my_hostname
DBNAME=my_database_name
TABLE=my_table
BUCKET=my_bucket

Export the whole content to a tsv file. This operation can take a certain amount of time, think of running it in an EC2 with screen and provision a big enough EBS volume.

query="select id, status, created_at from $TABLE order by id"

mysql -u $DBUSER -p$DBPASS -h $DBHOST -B -N --quick -e "$query" $DBNAME > output.tsv

Split the file by created_at:

mkdir -p daily
rm -rf daily/*

awk '{ print $0 > "daily/" $3 ".tsv" }' output.tsv
rm -f daily/.tsv

Move each file to folders suited for Athena’s partitioning. And Split each daily file into chunks of 1GB. Athena works best with files around 100MB. 1GB text files will result in gzipped files of 50-100 MB.

cd daily

for file in *.tsv; do
  filename=`basename $file '.tsv'`
  year=`echo $filename | cut -b1-4`
  month=`echo $filename | cut -b6-7`
  day=`echo $filename | cut -b9-10`
  path="year=$year/month=$month/day=$day"
  mkdir -p $path
  mv $file $path/
  prev=$(pwd)
  cd $path
  split -a 2 -d --additional-suffix=.tsv -C 1024m $file data_
  gzip data_*
  rm -f $file
  cd $prev
done
cd ..

Sync the content to your S3 Bucket:

aws s3 sync daily/ s3://$BUCKET/mysql-data/$TABLE/

Define the table in Athena

CREATE EXTERNAL TABLE `my_table`(
  `id` int,
  `status` string,
  `created_at` timestamp)
PARTITIONED BY (
  `year` string,
  `month` string,
  `day` string)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '\t'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://my_bucket/mysql-data/my_table'
TBLPROPERTIES (
  'has_encrypted_data'='false');

Load partitions

MSCK REPAIR TABLE my_table;

List of ID

Depending on how much data you want to work on at a time, you can limit the list by day, month or year. Make good use of the partitions.

select id from `my_table` where year='2018' and month='01' and status='obsolete' order by id;

Download the result

Still on your EC2 instance:

aws s3 cp s3://aws-athena-query-results-<accountid>-<region>/Unsaved/2018/03/15/<uuid>.csv temp.csv

Remove the quotes and the header

tail -n +2 temp.csv > 2018_01.csv
sed -i 's/"//g' 2018_01.csv

Split into batches

The idea is to delete a “few” rows at a time only. This amounts depends on your data size and server capacity. For us 100’000 was the sweat spot.

mkdir delete
cd delete
split -a 3 -l 100000 ../2018_01.csv

This will create files named xaaa, xaab and so on, each with a list of 100000 ids.

Convert the list to an sql delete command. This results in a single delete command with a list of 100000 rows.

for file in *;do
  sed -i 's/$/,/' $file;
  tr -d '\n' < $file > output.lst;
  mv output.lst $file;
  sed -i 's/^/set autocommit=0;\ndelete from my_table where id in (/' $file;sed -i 's/,$/);\ncommit;/' $file;
done

Run all the scripts

Run each file through MySQL and rename the file. This allows to stop the script and continue where we left of.

To let the server breath and allow other queries to perform without too much interference, we pause 30s after each delete, and pause 15 minutes every 20 command. The time needed to rest may vary depending on server load, table size and server performances.

n=0;
for file in x*; do
  echo $file;
  time mysql -u $DBUSER -p$DBPASS -h $DBHOST $DBNAME < $file;
  mv $file y$file;
  if [ $(($n % 20)) -eq 0 ];then
    sleep 900;
  else
    sleep 30;
  fi;
  let n=$n+1;
done;

Next step: daily maintenance

The above script should be run automatically at a defined interval. The backup part can be found in the following Gist. The missing part is exporting the ids to be deleted.