Kisah penghapusan fisik 300 juta catatan di MySQL

pengenalan

Halo. Saya ningenMe, pengembang web.

Seperti judulnya, cerita saya adalah kisah penghapusan fisik 300 juta record di MySQL.

Saya menjadi tertarik dengan hal ini, jadi saya memutuskan untuk membuat pengingat (instruksi).

Beranda - Waspada

Server batch yang saya gunakan dan pelihara memiliki proses reguler yang mengumpulkan data bulan lalu dari MySQL sekali sehari.

Biasanya proses ini selesai dalam waktu sekitar 1 jam, namun kali ini tidak selesai selama 7 atau 8 jam, dan peringatan tidak berhenti bermunculan...

Mencari alasan

Saya mencoba memulai ulang proses dan melihat log, tetapi saya tidak melihat ada yang salah.
Kueri telah diindeks dengan benar. Namun ketika saya memikirkan apa yang salah, saya menyadari bahwa ukuran databasenya cukup besar.

hoge_table | 350'000'000 |

350 juta catatan. Pengindeksan sepertinya berfungsi dengan benar, hanya saja sangat lambat.

Pengumpulan data yang diperlukan per bulan adalah sekitar 12 catatan. Sepertinya perintah pilih memakan waktu lama dan transaksi tidak dijalankan dalam waktu lama.

DB

Ini pada dasarnya adalah tabel yang bertambah sekitar 400 entri setiap hari. Basis data seharusnya hanya mengumpulkan data selama sebulan terakhir, oleh karena itu, diharapkan dapat menampung jumlah data sebanyak ini, namun sayangnya, operasi rotasi tidak disertakan.

Basis data ini tidak saya kembangkan. Saya mengambil alih dari pengembang lain, jadi masih terasa seperti utang teknis.

Ada saatnya volume data yang dimasukkan setiap hari menjadi besar dan akhirnya mencapai batasnya. Diasumsikan bahwa ketika bekerja dengan data dalam jumlah besar, data tersebut perlu dipisahkan, tetapi sayangnya hal ini tidak dilakukan.

Dan kemudian saya mulai bertindak.

Koreksi

Lebih rasional mengurangi ukuran database itu sendiri dan mengurangi waktu pemrosesan daripada mengubah logika itu sendiri.

Situasinya akan berubah secara signifikan jika Anda menghapus 300 juta catatan, jadi saya memutuskan untuk melakukannya... Eh, saya pikir ini pasti akan berhasil.

Tindakan 1

Setelah menyiapkan cadangan yang andal, saya akhirnya mulai mengirimkan permintaan.

「Mengirim permintaan」

DELETE FROM hoge_table WHERE create_time <= 'YYYY-MM-DD HH:MM:SS';

"..."

"..."

“Hmm… Tidak ada jawaban. Mungkin prosesnya lama?” — Saya berpikir, tetapi untuk berjaga-jaga, saya melihat grafana dan melihat bahwa beban disk bertambah sangat cepat.
“Berbahaya,” pikirku lagi dan segera menghentikan permintaan itu.

Tindakan 2

Setelah menganalisis semuanya, saya menyadari bahwa volume data terlalu besar untuk menghapus semuanya sekaligus.

Saya memutuskan untuk menulis skrip yang dapat menghapus sekitar 1 catatan dan meluncurkannya.

「Saya menerapkan skrip」

“Sekarang ini pasti akan berhasil,” pikirku.

Tindakan 3

Metode kedua berhasil, tetapi ternyata sangat memakan waktu.
Untuk melakukan semuanya dengan hati-hati, tanpa rasa gugup yang tidak perlu, akan memakan waktu sekitar dua minggu. Namun tetap saja, skenario ini tidak memenuhi persyaratan layanan, jadi kami harus menjauh darinya.

Jadi, inilah yang saya putuskan untuk lakukan:

Salin tabel dan ganti namanya

Dari langkah sebelumnya, saya menyadari bahwa menghapus data dalam jumlah besar akan menimbulkan beban yang sama besarnya. Jadi saya memutuskan untuk membuat tabel baru dari awal menggunakan insert dan memindahkan data yang akan saya hapus ke dalamnya.

| hoge_table     | 350'000'000|
| tmp_hoge_table |  50'000'000|

Jika Anda membuat tabel baru dengan ukuran yang sama seperti di atas, kecepatan pemrosesan datanya juga akan menjadi 1/7 lebih cepat.

Setelah membuat tabel dan mengganti namanya, saya mulai menggunakannya sebagai tabel master. Sekarang jika saya menghapus tabel dengan 300 juta catatan, semuanya akan baik-baik saja.
Saya menemukan bahwa truncate atau drop menghasilkan lebih sedikit overhead daripada delete dan memutuskan untuk menggunakan metode ini.

Eksekusi

「Mengirim permintaan」

INSERT INTO tmp_hoge_table SELECT FROM hoge_table create_time > 'YYYY-MM-DD HH:MM:SS';

"..."
"..."
"Emm…?"

Tindakan 4

Saya pikir ide sebelumnya akan berhasil, tetapi setelah mengirimkan permintaan penyisipan, banyak kesalahan muncul. MySQL tidak memaafkan.

Saya sudah sangat lelah sehingga saya mulai berpikir bahwa saya tidak ingin melakukan ini lagi.

Saya duduk dan berpikir dan menyadari bahwa mungkin ada terlalu banyak pertanyaan penyisipan untuk satu waktu...
Saya mencoba mengirimkan permintaan penyisipan jumlah data yang harus diproses database dalam 1 hari. Telah terjadi!

Nah, setelah itu kami terus mengirimkan permintaan data dengan jumlah yang sama. Karena kami perlu menghapus data selama satu bulan, kami mengulangi operasi ini sekitar 35 kali.

Mengganti nama tabel

Di sini keberuntungan ada di pihak saya: semuanya berjalan lancar.

Peringatan hilang

Kecepatan pemrosesan batch telah meningkat.

Sebelumnya proses ini memakan waktu sekitar satu jam, kini menjadi sekitar 2 menit.

Setelah saya yakin semua masalah telah terpecahkan, saya membuang 300 juta rekaman. Saya menghapus tabel dan merasa terlahir kembali.

Ringkasan

Saya menyadari bahwa pemrosesan rotasi tidak ada dalam pemrosesan batch, dan itulah masalah utamanya. Kesalahan arsitektur seperti ini hanya membuang-buang waktu.

Apakah Anda memikirkan beban selama replikasi data saat menghapus catatan dari database? Jangan membebani MySQL secara berlebihan.

Mereka yang ahli dalam database pasti tidak akan menemui masalah seperti itu. Bagi kalian semua, semoga artikel ini bermanfaat.

Terima kasih sudah membaca!

Kami akan sangat senang jika Anda memberi tahu kami apakah Anda menyukai artikel ini, apakah terjemahannya jelas, apakah bermanfaat bagi Anda?

Sumber: www.habr.com