Seberapa besar database mysql yang besar?

Sebagian besar database tumbuh dalam ukuran dari waktu ke waktu. Pertumbuhannya tidak selalu cukup cepat untuk memengaruhi kinerja database, tetapi pasti ada kasus di mana hal itu terjadi. Ketika itu terjadi, kita sering bertanya-tanya apa yang dapat dilakukan untuk mengurangi dampak tersebut dan bagaimana kita dapat memastikan kelancaran operasi database saat menangani data dalam skala besar

Pertama-tama, mari kita coba mendefinisikan apa yang dimaksud dengan "volume data besar"? . InnoDB bekerja sedemikian rupa sehingga sangat diuntungkan dari memori yang tersedia – terutama kumpulan buffer InnoDB. Selama data pas di sana, akses disk diminimalkan untuk menangani penulisan saja – pembacaan dilakukan di luar memori. Apa yang terjadi jika data melebihi memori? . Saat jumlah data bertambah, beban kerja beralih dari terikat CPU ke terikat I/O. Ini berarti bahwa hambatannya bukan lagi CPU (yang merupakan kasus ketika data masuk ke dalam memori – akses data dalam memori cepat, transformasi dan agregasi data lebih lambat) tetapi subsistem I/O (operasi CPU pada data adalah cara . ) Dengan peningkatan adopsi flash, beban kerja terikat I/O tidak terlalu buruk seperti dulu pada saat drive berputar (akses acak jauh lebih cepat dengan SSD) tetapi performa yang dicapai masih ada

Hal lain yang harus kita ingat bahwa kita biasanya hanya peduli dengan kumpulan data aktif. Tentu, Anda mungkin memiliki terabyte data dalam skema Anda, tetapi jika Anda harus mengakses hanya 5GB terakhir, ini sebenarnya situasi yang cukup bagus. Tentu, ini masih menimbulkan tantangan operasional, tetapi dari segi kinerja seharusnya masih baik-baik saja

Mari kita asumsikan untuk tujuan blog ini, dan ini bukan definisi ilmiah, bahwa volume data besar yang kami maksud adalah kasus di mana ukuran data aktif secara signifikan melebihi ukuran memori. Bisa 100GB kalau punya memori 2GB, bisa 20TB kalau punya memori 200GB. Titik kritisnya adalah bahwa beban kerja Anda benar-benar terikat I/O. Tetap bersama kami saat kami membahas beberapa opsi yang tersedia untuk MySQL dan MariaDB

Mempartisi

Pendekatan historis (tetapi sangat valid) untuk menangani volume data yang besar adalah dengan mengimplementasikan partisi. Ide di baliknya adalah membagi tabel menjadi beberapa partisi, semacam sub-tabel. Pemisahan terjadi sesuai dengan aturan yang ditentukan oleh pengguna. Mari kita lihat beberapa contoh (contoh SQL diambil dari MySQL 8. 0 dokumentasi)

MySQL8. 0 hadir dengan jenis partisi berikut

• JANGKAUAN
• DAFTAR
• KOLOM
• HASH
• KUNCI

Itu juga dapat membuat subpartisi. Kami tidak akan menulis ulang dokumentasi di sini, tetapi kami masih ingin memberi Anda wawasan tentang cara kerja partisi. Untuk membuat partisi, Anda harus menentukan kunci partisi. Itu bisa berupa kolom atau dalam kasus RANGE atau LIST beberapa kolom yang akan digunakan untuk menentukan bagaimana data harus dipecah menjadi partisi

Partisi HASH mengharuskan pengguna untuk menentukan kolom, yang akan di-hash. Kemudian, data akan dipecah menjadi sejumlah partisi yang ditentukan pengguna berdasarkan nilai hash tersebut

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;

Dalam hal ini hash akan dibuat berdasarkan hasil yang dihasilkan oleh fungsi YEAR() pada kolom 'disewa'

Pemartisian KEY mirip dengan pengecualian bahwa pengguna menentukan kolom mana yang harus di-hash dan sisanya terserah MySQL untuk menangani

Sementara partisi HASH dan KEY secara acak mendistribusikan data ke seluruh jumlah partisi, RANGE dan LIST membiarkan pengguna memutuskan apa yang harus dilakukan. RANGE umumnya digunakan dengan waktu atau tanggal

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

Itu juga dapat digunakan dengan jenis kolom lainnya

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

Partisi DAFTAR bekerja berdasarkan daftar nilai yang mengurutkan baris di beberapa partisi

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

Apa gunanya menggunakan partisi yang mungkin Anda tanyakan? . Katakanlah Anda ingin mencari baris yang dibuat pada bulan tertentu. Jika Anda memiliki data selama beberapa tahun yang disimpan dalam tabel, ini akan menjadi tantangan – indeks harus digunakan dan, seperti yang kita ketahui, indeks membantu menemukan baris tetapi mengakses baris tersebut akan menghasilkan banyak pembacaan acak dari . Jika Anda memiliki partisi yang dibuat berdasarkan tahun-bulan, MySQL dapat membaca semua baris dari partisi tersebut – tidak perlu mengakses indeks, tidak perlu melakukan pembacaan acak. cukup baca semua data dari partisi, secara berurutan, dan kita siap

Partisi juga sangat berguna dalam menangani perputaran data. Jika MySQL dapat dengan mudah mengidentifikasi baris untuk dihapus dan memetakannya ke partisi tunggal, alih-alih menjalankan DELETE FROM table WHERE…, yang akan menggunakan indeks untuk menemukan baris, Anda dapat memotong partisi. Ini sangat berguna dengan partisi RANGE – mengikuti contoh di atas, jika kita ingin menyimpan data hanya untuk 2 tahun, kita dapat dengan mudah membuat tugas cron, yang akan menghapus partisi lama dan membuat partisi baru yang kosong untuk bulan depan

Kompresi InnoDB

Jika kita memiliki volume data yang besar (belum tentu memikirkan database), hal pertama yang terlintas di benak kita adalah mengompresnya. Ada banyak alat yang menyediakan opsi untuk mengompres file Anda, mengurangi ukurannya secara signifikan. InnoDB juga memiliki opsi untuk itu – MySQL dan MariaDB mendukung kompresi InnoDB. Keuntungan utama menggunakan kompresi adalah pengurangan aktivitas I/O. Data, ketika dikompresi, lebih kecil sehingga lebih cepat untuk membaca dan menulis. Halaman InnoDB tipikal berukuran 16KB, untuk SSD ini adalah 4 operasi I/O untuk membaca atau menulis (SSD biasanya menggunakan halaman 4KB). Jika kami berhasil mengompres 16KB menjadi 4KB, kami hanya mengurangi operasi I/O menjadi empat. Itu tidak banyak membantu mengenai rasio dataset ke memori. Sebenarnya, ini bahkan dapat memperburuknya – MySQL, untuk beroperasi pada data, harus mendekompresi halaman. Namun itu membaca halaman terkompresi dari disk. Ini menghasilkan kumpulan buffer InnoDB yang menyimpan 4KB data terkompresi dan 16KB data tidak terkompresi. Tentu saja, ada algoritme untuk menghapus data yang tidak dibutuhkan (halaman yang tidak terkompresi akan dihapus bila memungkinkan, hanya menyimpan satu terkompresi di memori) tetapi Anda tidak dapat mengharapkan terlalu banyak peningkatan di area ini

Penting juga untuk diingat bagaimana kompresi bekerja terkait penyimpanan. Solid state drive adalah norma untuk server database saat ini dan mereka memiliki beberapa karakteristik khusus. Mereka cepat, mereka tidak terlalu peduli apakah lalu lintas berurutan atau acak (walaupun mereka masih lebih suka akses berurutan daripada acak). Mereka mahal untuk volume besar. Mereka menderita "usang" karena mereka dapat menangani siklus tulis dalam jumlah terbatas. Kompresi sangat membantu di sini – dengan mengurangi ukuran data pada disk, kami mengurangi biaya lapisan penyimpanan untuk basis data. Dengan mengurangi ukuran data yang kami tulis ke disk, kami meningkatkan masa pakai SSD

Sayangnya, meskipun kompresi membantu, untuk volume data yang lebih besar mungkin masih belum cukup. Langkah lain adalah mencari yang lain selain InnoDB

MyRocks

MyRocks adalah mesin penyimpanan yang tersedia untuk MySQL dan MariaDB yang didasarkan pada konsep yang berbeda dari InnoDB. Rekan saya, Sebastian Insausti, memiliki blog yang bagus tentang penggunaan MyRocks dengan MariaDB. Intinya adalah, karena desainnya (menggunakan Log Structured Merge, LSM), MyRocks secara signifikan lebih baik dalam hal kompresi daripada InnoDB (yang didasarkan pada struktur B+Tree). MyRocks dirancang untuk menangani data dalam jumlah besar dan untuk mengurangi jumlah penulisan. Itu berasal dari Facebook, di mana volume datanya besar dan persyaratan untuk mengakses datanya tinggi. Jadi penyimpanan SSD – tetap saja, dalam skala besar setiap peningkatan kompresi sangat besar. MyRocks bahkan dapat memberikan kompresi hingga 2x lebih baik daripada InnoDB (yang berarti Anda memotong jumlah server menjadi dua). Ini juga dirancang untuk mengurangi amplifikasi tulis (jumlah penulisan yang diperlukan untuk menangani perubahan konten baris) – ini membutuhkan penulisan 10x lebih sedikit daripada InnoDB. Ini, jelas, mengurangi beban I/O tetapi, yang lebih penting, ini akan meningkatkan masa pakai SSD sepuluh kali lipat dibandingkan dengan menyerahkan beban yang sama menggunakan InnoDB). Dari sudut pandang kinerja, semakin kecil volume data, semakin cepat akses sehingga mesin penyimpanan seperti itu juga dapat membantu mengeluarkan data dari database lebih cepat (walaupun itu bukan prioritas tertinggi saat mendesain MyRocks)

Datastore Kolom

Sumber daya terkait

 Pengelolaan Performa ClusterControl

 Memahami Efek Latensi Tinggi pada Solusi MySQL dan MariaDB dengan Ketersediaan Tinggi

 Lembar Curang Kinerja MySQL

Pada titik tertentu yang bisa kita lakukan adalah mengakui bahwa kita tidak dapat menangani volume data seperti itu menggunakan MySQL. Tentu, Anda dapat memecahkannya, Anda dapat melakukan hal yang berbeda tetapi pada akhirnya itu tidak masuk akal lagi. Saatnya mencari solusi tambahan. Salah satunya adalah dengan menggunakan penyimpanan data berbentuk kolom – database, yang dirancang dengan mempertimbangkan analitik data besar. Tentu, mereka tidak akan membantu dengan jenis lalu lintas OLTP tetapi analitik cukup standar saat ini karena perusahaan mencoba untuk didorong oleh data dan membuat keputusan berdasarkan angka yang tepat, bukan data acak. Ada banyak penyimpanan data kolom tetapi kami ingin menyebutkan dua di antaranya. MariaDB AX dan ClickHouse. Kami memiliki beberapa blog yang menjelaskan apa itu MariaDB AX dan bagaimana MariaDB AX dapat digunakan. Yang penting, MariaDB AX dapat ditingkatkan dalam bentuk kluster, meningkatkan kinerja. ClickHouse adalah opsi lain untuk menjalankan analitik – ClickHouse dapat dengan mudah dikonfigurasi untuk mereplikasi data dari MySQL, seperti yang telah kita bahas di salah satu postingan blog kami. Ini cepat, gratis dan juga dapat digunakan untuk membentuk cluster dan data shard untuk kinerja yang lebih baik

Kesimpulan

Kami harap posting blog ini memberi Anda wawasan tentang seberapa besar volume data dapat ditangani di MySQL atau MariaDB. Untungnya, ada beberapa opsi yang dapat kita gunakan dan, pada akhirnya, jika kita tidak dapat membuatnya bekerja, ada alternatif yang bagus

Seberapa besar database MySQL yang terlalu besar?

Bisakah MySQL menangani 1 juta catatan?

Seberapa besar database SQL yang besar?

Bisakah MySQL menangani satu miliar baris?