phpプロダクトのDBを軽量化のためデータをたくさん消した話

はじめに

最近大学4年目になったShuhei-ppです。
今回でqiita2回目の投稿になります。
前回の投稿では2,3日に1回投稿するみたいなことを書きました。しかし精度の低い情報も書いてしまうかもな、と思いなかなか投稿できませんでした。

そんな私ですが、今勤めさせていただいているところ(バイト)のプロダクトで本番DBの容量がいっぱいになってきて軽量化のためデータをたくさん消したのでそのプロセス等を共有できたらと思います。
初心者のため間違い等あると思いますがよろしくお願いします🙇‍♂️

環境

php:5.3.3
mysql:5.7.17
使用FW ethna

調査、実行順序

1. 容量の大きいテーブルからデータを消すテーブル候補を出す
2. PHP(phpstorm)でテーブル名で検索し使用されている箇所と使用用途の洗い出し
3. 使用しないデータはダンプし、削除
   の順でやっていきました。

1. データ削除のためのテーブル調査

この調査ですが、テーブル容量の方面からの調査を行いました。
まず容量が上位のテーブルを検索しました。

クエリはこんな感じです。

SELECT  
    table_name, engine, table_rows AS tbl_rows,
    avg_row_length AS rlen,  
    floor((data_length+index_length)/1024/1024) AS allmb,  #総容量
    floor((data_length)/1024/1024) AS data,  #データ容量
    floor((index_length)/1024/1024) AS index   #インデックス容量
FROM 
    information_schema.tables  
WHERE
    table_schema=database()  
ORDER BY
    (data_length+index_length) DESC
LIMIT 20;

クエリ参考記事

このSQLで得られた結果

tablename	engin	rows	rlen	allmb	data	index
*******	****	535434664	64	47217	32816	14401
*******	****	4317916	4521	19174	18619	555
*******	****	3717899	3011	10677	10677	0
....	....	...	...	...	...	...

こんな感じで出てくると思います。

次のステップでは上位10テーブルの解析、呼び出し元の調査を行いました。

2. テーブルが使用されている箇所と使用用途の洗い出し

ここでは実際にコードを読んで使用箇所、使用方法を探していきました。
実際にはPHPStormの文字列検索機能でテーブル名で検索しました。基本的にDBのクラスのSQLの中にいる感じだったので探しやすかったと感じています。

その調査の結果あまり呼び出しされていなく、呼び出されたとしても期間で絞り込みされていたりするテーブルを探しテーブル容量上位1,3つ目のテーブルが影響範囲が少なく消せるのではないかという判断に至りました。

一番上のテーブルではバッチ処理でINSERTした後にパーティショニングを行っているようなのでdeleteではなく
ALTER TABLE t_name DROP PARTITION partition_name
で消す必要があるみたいでした。

今回はパーティションを初めて扱ったので勉強しました。
MYSQLパーティション参考記事

3. 使わないデータのダンプ、削除

ここからは実際にバックアップのためにダンプ、削除を行っていきます。

バックアップのダンプ

このシステムのDBは書き込み用のmasterが一台、readonlyのslaveが２台あります。なのでダンプの際はアクセスが少ない時間帯にslaveをバランシングから外して作業を行いました。

まず容量3番目(10GB)のテーブルをダンプしました。約10GBありましたが1時間半くらいで終わりました。結構早かったかも？

次に容量が一番大きい47GBのテーブルのダンプを行いました。10GBで1時間なので47GBで7時間くらいかかるかなーとは予想していました。しかし2時間15分程で強制終了してしまいました。エラーかな？？と思ったらエラーもでていないのでなぜ？と思っていました。実際にtailコマンドで見てみたらdump completeとなってました。1つの行のデータ量が少ないと総GBが大きくても速くなるみたい？

バックアップ用DBに流し込み

次はダンプしたSQLをバックアップ用のRDSにinsertしました。

INSERTコマンド

$ mysql -h db_host -P 3306 -u user -p password db_name < dump_file

1回目に10GB tableのinsertを行い、成功しました。その流れで47GBのほうも実行しようと思ったら...
the table is full mysql.......
みたいなエラーが出て分割ダンプで行く方法に
五回のダンプに分けてinsertしました。
できてよかった。

データの削除

・まず容量3番目(10GB)のテーブルを消しました。
ちなみにこのテーブルはバックアップ用の凍結データが入っていたみたいなのでtruncateで一瞬でした。
→約10GB開放

・次に47GBのテーブルでした。
先ほど言った通りこのテーブルはパーティショニングを行っているのでパーティションを消さなければなりませんでした。
まずパーティション一覧を検索するためにこのSQLを打ちました。

SELECT
TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,TABLE_ROWS
FROM
	INFORMATION_SCHEMA.PARTITIONS
WHERE
	TABLE_NAME= 'table_name'

TABLE_SCHEMA	PARTITION_NAME	PARTITION_ORDINAL_POSITION	TABLE_ROWS
*****	*****	1	385907
*****	*****	2	327180
*****	*****	3	400826
*****	*****	4	381780
*****	*****	5	396234
.....	.....	.....	.....

みたいな感じで出てくると思います。
ちなみに今回のテーブルはパーティションが1992個ありました。

ALTER TABLE Batch_blog_rank DROP PARTITION partition1,partition2,.....

このコマンドをいちいち打つためにパーティション名を打つのはのは面倒でした。なのであとはさっきの表をvscodeにコピーしていろいろ置換して150くらいずつ消しました。（macのvscodeだとcommand+shift+lでできた。)

→約40GB開放

ちなみにパーティションで消すのはDROP TABLEと同じような処理らしくデータの破棄が高速に実行できるらしいです。

最後に

データベースのデータ削除の際にはこの記事を参考にしていただけたりしたら嬉しいです。