Ini mungkin bukan artikel, tetapi catatan singkat tentang beberapa fitur bekerja dengan tabel besar di MySQL.
Alasan untuk menulis adalah penambahan kolom baru ke meja setiap hari. Tapi semuanya ternyata tidak sesederhana yang diharapkan.
Jadi, suatu malam, agar tidak mengganggu pelanggan kami, kami perlu menambahkan kolom ke meja.
Untuk membuatnya lebih jelas, karakteristik tabel dan alas:
- ukuran meja 110Gb
- jumlah baris: 7,5 juta
- mesin penyimpanan: InnoDB
- ada dua server sql yang terhubung sesuai dengan skema master-slave, sementara master ada di SSD, dan slave ada di HDD
Tampaknya menjadi solusi yang jelas untuk menambahkan kolom - Alter Table.
alter table table_name add source varchar(32)
Kami menggunakannya (ya, kami mengerti bahwa itu buruk, tetapi dalam kasus khusus ini risikonya minimal).
Hasilnya sangat tidak menyenangkan:
- pada panduan, proses penambahan kolom memakan waktu sekitar satu jam (!)
- pada budak, itu dimulai setelah akhir proses pada master dan berlangsung sekitar 8 jam (!!)
- selama tabel alter, replikasi data (!!!) sepenuhnya berhenti pada slave
Tapi ada hikmahnya: bonus kecil adalah bahwa setelah menambahkan kolom, ukuran meja berkurang 10%.
Dalam grafik di bawah ini terlihat jelas.
Grafik muat CPU pada wizard.
Grafik beban CPU pada slave.
Tertunda replikasi.Masalah apa yang menanti mereka yang melakukan ini di atas meja pertempuran?
Pertama, selama durasi Tabel Alter, Anda tidak bisa menulis data ke tabel (tetapi Anda bisa membacanya). Sebenarnya, itu tergantung pada versi MySQL, pada yang terakhir itu tidak, tapi tetap saja Anda perlu memahami apa sebenarnya yang dapat dilakukan versi Anda untuk menghindari masalah.
Dengan demikian,
jika meja besar, maka waktu tidak tersedianya akan signifikan (seperti halnya kami, ketika menggunakan SSD butuh satu jam, dan pada disk biasa - 8 jam), yang tidak mungkin diharapkan oleh pelanggan Anda.
Kedua, seperti dalam kasus kami, selama eksekusi Tabel Alter,
sinkronisasi semua tabel , bukan hanya yang kami ubah,
benar-benar berhenti pada slave. Oleh karena itu, jika data Anda di server kedua sangat penting dan harus segar - Anda berisiko dibiarkan tanpa pembaruan dengan semua konsekuensi berikutnya.
Poin tidak jelas lain yang kami temui ketika menambahkan kolom (tapi ini lain kali) -
ruang disk tambahan diperlukan .
Faktanya adalah bahwa beberapa perubahan pada tabel membuat ulang tabel dari awal, sehingga Anda membutuhkan ruang yang tidak kurang dari tabel yang ada. Untuk meja besar, masing-masing, banyak ruang dibutuhkan, untuk membuatnya lebih sederhana. Menurut dokumentasi, tabel sementara dibuat di direktori yang sama dengan aslinya.
Selain itu, selama eksekusi semua jenis Tabel Alter, semua perubahan ditulis ke file log, sehingga setelah perubahan, data dapat digulirkan selama waktu di mana operasi dilakukan. Dan di sini, juga, kejutan yang tidak menyenangkan mungkin menunggu: jika tabel berubah untuk waktu yang lama, dan volume operasi besar, maka tidak hanya ruang disk yang dapat berakhir, tetapi juga batas ukuran file yang ditentukan dalam pengaturan SQL mungkin terlampaui. Dalam kasus apa pun, "operasi DDL online gagal, dan operasi DML bersamaan tanpa komitmen dibatalkan" menunggu Anda.
Kami dihadapkan dengan fakta bahwa direktori untuk file sementara kecil, akibatnya kami harus mendefinisikan ulang
innodb_tmpdir .
Untuk melihat di mana variabel saat ini menunjuk, Anda dapat melakukan ini:
select @@GLOBAL.innodb_tmpdir;
Perlu diingat bahwa ukuran direktori sementara mungkin juga dibutuhkan ukuran tabel + indeks. Secara umum, persediaan di luar angkasa.
Agar tidak mengulangi dokumentasi, baca lebih detail di
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-space-requirements.htmlTetapi bagaimana cara melakukannya? Bahkan, tidak ada resep tunggal untuk semua kesempatan.
Salah satu opsi yang mungkin, seperti yang kami lakukan untuk tabel yang tidak penting untuk memperbarui:
- Buat tabel baru dengan struktur yang diinginkan
- Isi kolom dari tabel lama
- Hapus atau ganti nama tabel lama
- Ganti nama yang baru
Saya ulangi bahwa ini berfungsi untuk tabel pembaruan yang tidak penting. Dan pada saat yang sama menghindari pemblokiran replikasi. Harus diingat bahwa mengisi tabel baru harus dilakukan sedemikian rupa agar replikasi dapat terus berlanjut, dan karena itu berjalan berurutan, Anda tidak dapat melakukan dengan ekspresi sql tunggal, Anda harus memecahnya menjadi beberapa pertanyaan kecil di mana replikasi data lain akan terjadi. Dalam kasus lain, opsi lain dimungkinkan, mungkin seseorang akan berbagi dalam komentar.
UPD
Syavadee menyarankan menggunakan perubahan skema percona online. Bahkan, itu mengimplementasikan algoritma yang dijelaskan di atas dengan barang tambahan.
UPD
Arheops merekomendasikan untuk mengaktifkan replikasi paralel / gtid untuk menyelesaikan masalah replikasi.
Nah, kebetulan, kadang-kadang, untuk memahami seberapa besar meja itu dan berapa banyak baris di dalamnya, Anda perlu melakukan cara mengajar
select count(*) from table_name
Tetapi pada tabel besar dan dimuat, ini juga bukan operasi tercepat, terutama ketika Anda memiliki setengah juta baris atau lebih.
Oleh karena itu, untuk perkiraan perkiraan volume, Anda dapat menggunakan metode berikut:
SHOW TABLE STATUS FROM express where name='table_name'
Sayangnya, pada mesin InnoDB, ukuran yang dihasilkan mungkin berbeda hingga 50 persen (dalam kasus kami, dengan tabel di atas, jumlah rekaman aktual adalah sekitar 7,5 juta, dan metode ini hanya menunjukkan 5 juta), tetapi ini cukup cocok untuk perkiraan indikatif.
Itu saja, saya harap catatan ini membantu seseorang untuk menghindari masalah besar dengan perintah SQL yang seharusnya tidak berbahaya.