When you have to drop a large database, you\u2019ll encounter some problems, mainly replication lag. Now I\u2019ll show you how to avoid this.<\/p>\n
What can cause replication lag when you drop a database? First, it takes some disk I\/O to unlink the files, and secondly, MySQL will scan through the buffer pool to see if there are pages from that database or not. On a huge (or at least big) database this could take seconds or even minutes, what means your slaves will collect lag for seconds or (of course) even minutes.<\/p>\n
<\/p>\n
Every database dropping should be started with a backup of that database. I did it on a replica.<\/p>\n
[root@db-secondary.bfc \/home\/banyek\/chomp]# mysqldump --set-gtid-purged=OFF --single-transaction chomp > chomp.sql\n[root@db-secondary.bfc \/home\/banyek\/chomp]# ls\nchomp.sql\n[root@db-secondary.bfc \/home\/banyek\/chomp]# ls -alh\ntotal 823M\ndrwxr-xr-x 2 root root 4.0K Mar 24 05:31 .\ndrwx------ 4 banyek banyek 4.0K Mar 24 05:31 ..\n-rw-r--r-- 1 root root 823M Mar 24 05:32 chomp.sql\n[root@db-secondary.bfc \/home\/banyek\/chomp]# gzip chomp.sql\n[root@db-secondary.bfc \/home\/banyek\/chomp]# ls -lah\ntotal 202M\ndrwxr-xr-x 2 root root 4.0K Mar 24 05:33 .\ndrwx------ 4 banyek banyek 4.0K Mar 24 05:31 ..\n-rw-r--r-- 1 root root 202M Mar 24 05:32 chomp.sql.gz\n<\/code><\/pre>\nSo, your database is dumped, you can now safely remove it.<\/p>\n
The following steps should happen:<\/p>\n
\n- Clean up data from tables<\/li>\n
- Shrink the datafiles<\/li>\n
- Drop tables<\/li>\n
- Drop database<\/li>\n<\/ul>\n
###Clean up data from tables<\/p>\n
First, check what to deal with. How many tables we have, how big they are, etc.<\/p>\n
[root@db-master.bfc \/home\/banyek]# ls -lh \/var\/lib\/mysql\/chomp\/\ntotal 2.8G\n-rw-r----- 1 mysql mysql 8.5K Jan 21 00:25 apikeys.frm\n-rw-r----- 1 mysql mysql 1.0M Jan 21 00:01 apikeys.ibd\n-rw-r----- 1 mysql mysql 65 Jan 21 00:25 db.opt\n-rw-r----- 1 mysql mysql 13K Jan 21 00:25 images.frm\n-rw-r----- 1 mysql mysql 2.8G Mar 4 14:22 images.ibd\n-rw-r----- 1 mysql mysql 8.4K Jan 21 00:25 keys.frm\n-rw-r----- 1 mysql mysql 1.0M Jan 21 00:01 keys.ibd\n-rw-r----- 1 mysql mysql 8.5K Jan 21 00:25 transform_lock.frm\n-rw-r----- 1 mysql mysql 8.0M Jan 21 00:01 transform_lock.ibd\n-rw-r----- 1 mysql mysql 8.5K Jan 21 00:25 videos.frm\n-rw-r----- 1 mysql mysql 9.0M Mar 4 13:14 videos.ibd\n\nMon Mar 24 05:34:26 2014root@localhost [chomp]> select count(*) from images;\n+----------+\n| count(*) |\n+----------+\n| 8843673 |\n+----------+\n1 row in set (7.57 sec)\n\nMon Mar 24 06:31:58 2014root@localhost [chomp]> select count(*) from apikeys\n -> ;\n+----------+\n| count(*) |\n+----------+\n| 3 |\n+----------+\n1 row in set (0.00 sec)\n\nMon Mar 24 06:32:16 2014root@localhost [chomp]> select count(*) from `keys`;\n+----------+\n| count(*) |\n+----------+\n| 1 |\n+----------+\n1 row in set (0.00 sec)\n\nMon Mar 24 06:32:30 2014root@localhost [chomp]> select count(*) from transform_lock;\n+----------+\n| count(*) |\n+----------+\n| 0 |\n+----------+\n1 row in set (0.00 sec)\n\nMon Mar 24 06:32:42 2014root@localhost [chomp]> select count(*) from videos;\n+----------+\n| count(*) |\n+----------+\n| 8570 |\n+----------+\n1 row in set (0.02 sec)\n<\/code><\/pre>\nYou can see we have only one really big table, and that is \u2018images\u2019 with about 9 million rows. The \u2018videos\u2019 table is the second biggest, it is 1\/1000th of the images.<\/p>\n
So far, so good.<\/p>\n
I use the pt-archiver<\/em> tool to clean up tables because it chunks up the data what needed to delete. That means it will run the delete command with a limit clause where \u2018limit\u2019 equals the chunk size. I have calculated earlier that our database can delete about 2000 row in a chunk with no replication lag, so I\u2019ll use the same chunk size for cleaning up images.<\/p>\nBefore I start the delete, I check how many chunks I have \u2013 just to help myself keep tracking the state of delete.<\/p>\n
Mon Mar 24 05:39:23 2014root@localhost [chomp]> select 8843673 \/ 2000 as chunks;\n+-----------+\n| chunks |\n+-----------+\n| 4421.8365 |\n+-----------+\n1 row in set (0.00 sec)\n<\/code><\/pre>\nHere is the snippet I use to track the state of delete:<\/p>\n
# SELECT CONCAT(100 - ROUND(($CHUNKS - (($MAXITEMS - COUNT(*)) \/ $CHUNKSIZE \/ $CHUNKS * 100),'%' as percent_done FROM $TABLE_TO_CLEAN_UP\n#\n# So, wrapped into a while loop, in the current way it will be like:\n\nwhile true; do clear ; mysql chomp -e \"select concat(100 - round((4421 - ((8843673 - count(*)) \/2000)) \/ 4421 * 100),'%') as percent_done from images;\" ; sleep 30; done\n\n# It will produce a neat table like this - refreshed in every 30 seonds.\n# +--------------+\n# | percent_done |\n# +--------------+\n# | 61% |\n# +--------------+\n<\/code><\/pre>\nAfter the progress bar is set up, we can start the table cleanup job with pt-archiver<\/p>\n
[root@db-master.bfc \/home\/banyek]# pt-archiver --statistics --no-check-charset --limit 2000 --txn-size 2000 --source D=chomp,t=images,u=user,p=password --purge --where \"1=1\"\n\nSource: D=chomp,p=...,t=images,u=user\nSELECT 8420052\nINSERT 0\nDELETE 8420052\nAction Count Time Pct\ndeleting 8420052 1981.1935 72.48\nselect 4212 72.4313 2.65\ncommit 4211 9.3239 0.34\nother 0 670.3669 24.53\n\n# You can see the 'Count' row is a bit less than it was at the select count(*), but this is just because\n# I've ran the archiver two times.\n#\n#\n# After the cleanup is done at the table, repeat this with the others too.\n<\/code><\/pre>\n###Shrink the datafiles<\/p>\n
After you have cleaned up the data from the tables themselves you can see that the data files are still there. (You can read more about this here<\/a>) So the next step is to shrink the datafiles. The easiest way to achieve this to run a simple alter like \u2018ALTER TABLE tablename engine=InnoDB\u2019 but we are still aware of replication lag, so run it via pt-online-schema-change<\/em><\/p>\n