Berapa besar data yang dapat ditangani oleh mysql?

Terkadang MySQL (atau MariaDB) juga bagus untuk data besar. Pada artikel ini, kami akan mengeksplorasi hal-hal yang harus Anda pertimbangkan saat menjawab pertanyaan. “Apakah MySQL pilihan yang bagus untuk proyek data besar saya?”

2 tahun lalu   •   8 mnt dibaca

Daftar Isi

Kata pengantar

Jika Anda seorang DBA MySQL atau pengembang yang berurusan dengan instans MySQL setiap hari, mungkin tidak mengejutkan jika Anda mendengar bahwa Anda tidak boleh menjalankan kumpulan data besar di MySQL. Tanyakan hampir semua DBA MySQL, dan Anda akan mendengar sesuatu seperti

● “MySQL tidak cocok. ”

● “Sudahkah Anda mempelajari NoSQL?”

● “Gunakan MongoDB”

Beberapa dari tanggapan ini memang bermanfaat—misalnya, sistem manajemen basis data berbasis NoSQL seperti MongoDB tentu dapat berguna saat menangani kumpulan data besar. Namun, bertentangan dengan kepercayaan populer, MySQL tidak boleh begitu cepat dikesampingkan—dalam beberapa skenario, MySQL (atau MariaDB) mungkin terbukti menjadi pilihan yang lebih baik untuk data besar daripada mitra NoSQL mereka. Pada artikel ini, kami akan mengeksplorasi hal-hal yang harus Anda pertimbangkan saat menjawab pertanyaan. “Apakah MySQL pilihan yang bagus untuk proyek data besar saya?”

Apakah MySQL merupakan Opsi untuk Big Data?

Beberapa masalah yang biasanya dihadapi DBA—sejauh menyangkut sejumlah data—terkait dengan keandalan dan kinerja. Besarnya masalah ini biasanya sebanding dengan ukuran kumpulan data, jadi apakah MySQL merupakan pilihan dalam kasus ini? . Berikut daftar singkat alasan mengapa hal itu mungkin terjadi

● Salah satu mesin penyimpanan MySQL, InnoDB, adalah mesin penyimpanan performa tinggi dan keandalan tinggi

● InnoDB memiliki parameter khusus yang memungkinkan pengembang berurusan dengan MySQL atau DBA MySQL untuk mendorong mesin hingga batasnya—misalnya, mengalokasikan 60-80% RAM yang tersedia di server hanya untuk InnoDB

● InnoDB juga dapat dibuat sesuai dengan ACID. Kepatuhan ACID dapat terbukti bermanfaat untuk mengurangi korupsi basis data yang dapat diakibatkan oleh pemadaman listrik atau gangguan terkait

Jika Anda telah membaca sejauh ini, kemungkinan besar Anda mungkin dapat mengatakan bahwa Anda seharusnya dapat bereksperimen dengan data besar di MySQL, Server Percona, atau MariaDB—tetapi bagaimana caranya?

Mesin Penyimpanan MySQL. Mana yang Cocok?

MySQL, Percona Server, dan MariaDB semuanya memiliki sejumlah mesin penyimpanan yang dapat Anda pilih. Kami tidak akan membahas setiap detail tentang mesin penyimpanan MySQL di postingan blog ini, tetapi Anda harus mengetahuinya

● MyISAM adalah mesin penyimpanan MySQL default hingga MySQL 5. 5

● Ketika MySQL 5. 5 dirilis pada tahun 2010, InnoDB menggantikan MyISAM, dan sekarang menjadi mesin penyimpanan default yang ditawarkan oleh MySQL. Ini juga merupakan mesin penyimpanan utama untuk sebagian besar insinyur atau pengembang MySQL yang berurusan dengan MySQL, Percona Server, atau MariaDB

● MySQL juga menawarkan mesin lain, beberapa di antaranya mungkin berguna saat mengarsipkan data atau melakukan operasi pengujian. Namun, karena InnoDB dan MyISAM adalah mesin penyimpanan yang paling sering digunakan di dunia MySQL, kami akan fokus pada keduanya.

Seperti di atas, dua mesin database utama MySQL adalah InnoDB dan MyISAM, yang secara kebetulan (atau tidak) juga merupakan mesin yang paling relevan dengan ruang lingkup artikel ini

InnoDB vs. MyISAM untuk Menyimpan Data [Besar].

Setelah menetapkan bahwa InnoDB dan MyISAM keduanya berpotensi cocok untuk penyimpanan data besar, sekarang kami dapat mulai menentukan mesin mana yang paling cocok untuk proyek Anda

Jadi, apakah Anda memilih MyISAM atau InnoDB? . Mari kita gali dan cari tahu

InnoDB untuk Data Besar

Seperti yang kami sebutkan sebelumnya, InnoDB saat ini adalah mesin MySQL default dan mendukung model ACID, bersama dengan penguncian level baris dan kunci asing juga. Sejak MySQL 5. 6, InnoDB mendukung indeks teks lengkap dan tablespace portabel, dan sejak versi 5. 7, juga indeks spasial dan waktu pembaruan terakhir untuk tabel

Kepatuhan ASAM InnoDB

Jika Anda berurusan dengan data besar (atau sejumlah data apa pun), kriteria utama Anda untuk produk basis data mungkin terkait dengan keandalan dan kecepatan.

InnoDB dapat menawarkan keduanya—buat saja mengikuti model ACID, optimalkan beberapa pengaturan di dalam my.cnf, mulai ulang server, dan Anda sebaiknya melakukannya. Jelas, semuanya tidak sesederhana itu, tetapi jangan khawatir – kami akan menjelaskan semua yang perlu Anda ketahui sebentar lagi

Model ACID adalah singkatan dari Atomicity, Consistency, Isolation, dan Durability—pada dasarnya, satu set properti database yang dimaksudkan untuk menjamin validitas data meskipun ada kesalahan, pemadaman listrik, atau kegagalan semacam itu. Begini cara kerjanya di InnoDB

● Atomicity memastikan bahwa semua pernyataan SQL beroperasi sebagai unit yang tidak dapat dibagi

● Konsistensi memastikan bahwa data konsisten dengan menggunakan mekanisme logging yang tersedia di InnoDB

● Isolasi mengacu pada penguncian tingkat baris InnoDB (kita akan membahasnya nanti)

● Ketahanan mengacu pada kemampuan InnoDB untuk memelihara file log

Model ACID dapat diaktifkan (atau dinonaktifkan) dengan mengubah nilai variabel innodb_flush_log_at_trx_commit di dalam my.cnf. Jika Anda ingin menangani data besar di MySQL, penting untuk diingat bahwa variabel ini memiliki tiga opsi yang tersedia

Opsi default, 1, membuat InnoDB ACID sesuai, sedangkan 0 dan 2 membuat InnoDB tidak lagi sesuai ACID, tetapi juga secara substansial meningkatkan kecepatan tulis. Jika Anda ingin menggunakan MySQL untuk proyek data besar Anda, mungkin sebaiknya tetap menggunakan opsi default

Tapi tunggu, bukankah kita membutuhkan keandalan dan kecepatan? . Mengonfigurasi variabel lain seperti innodb_buffer_pool_size,  juga dapat mencapai peningkatan kecepatan yang diinginkan. Teruslah membaca — kami akan memberi tahu Anda caranya sebentar lagi

Gunakan Parameter InnoDB untuk Mengoptimalkan Kecepatan Database MySQL

innodb_buffer_pool_size_ adalah salah satu parameter terpenting dalam arsitektur InnoDB. Tugas penting buffer ini adalah untuk meng-cache data dan indeks tabel InnoDB. Semakin besar buffer ini, semakin banyak data dan indeks yang dapat di-cache, yang sangat penting saat menangani kumpulan data besar

Jelas, RAM pada dasarnya masih terbatas, dan oleh karena itu, perlu menyisakan sejumlah ruang untuk proses sistem lainnya—nilai optimal yang disarankan untuk innodb_buffer_pool_size kira-kira 60-80% dari memori sistem yang tersedia

innodb_buffer_pool_size juga terkait erat dengan parameter innodb_log_file_size. Semakin ekstensif file log Anda, semakin sedikit waktu pemulihan yang Anda perlukan jika terjadi kerusakan, jadi untuk performa optimal dengan data besar, atur parameter ini menjadi sekitar seperempat dari nilai innodb_buffer_pool_size

Sejauh menyangkut kumpulan buffer InnoDB, kumpulan buffer juga dapat dibagi menjadi beberapa instance menggunakan parameter innodb_buffer_pool_instances. Melakukannya dapat meningkatkan I/O disk—nilai defaultnya adalah 8, mulai dari MariaDB 10

Setiap instance kumpulan buffer InnoDB mengelola struktur datanya sendiri dan mengambil sebagian dari total ukuran kumpulan buffer, yang berarti bahwa jika Anda memiliki, katakanlah, 8GB RAM yang tersedia di sistem Anda, Anda dapat mengatur innodb_buffer_pool_size hingga 6GB. Bagi kumpulan buffer InnoDB menjadi enam instans dan setiap instans harus berukuran 1GB

Perlu diingat bahwa parameter ini, bersama dengan beberapa parameter lainnya, tidak digunakan lagi pada MariaDB 10. 5. 1—menurut tim MariaDB, alasan awal pemisahan buffer pool tidak lagi relevan

Menulis ke file log juga sangat penting. innodb_flush_log_at_trx_commit1 menentukan ukuran dalam byte dari buffer yang digunakan InnoDB untuk menulis ke file tersebut. Nilai default dari parameter ini adalah 8MB

Dalam hal ini, buffer log yang lebih besar memungkinkan transaksi besar berjalan tanpa menulis log ke disk sebelum transaksi dilakukan

Fitur InnoDB Lainnya yang Meningkatkan Kinerja

InnoDB juga memungkinkan Anda memilih metode flush, beberapa di antaranya digunakan untuk pengujian kinerja internal, dan karenanya tidak didukung. Namun yang paling penting, metode flush menentukan apa yang digunakan InnoDB untuk membuka file data dan bagaimana metode flush data dan file log ke disk.  

Setiap metode memiliki keuntungan dan kerugian yang berbeda, tetapi untuk kinerja yang optimal—terutama dengan data besar — ​​innodb_flush_log_at_trx_commit2 dan innodb_flush_log_at_trx_commit3 adalah opsi terbaik

Saat innodb_flush_log_at_trx_commit_2 sedang digunakan, data mungkin konsisten atau mungkin tidak konsisten, tetapi keuntungannya adalah innodb_flush_log_at_trx_commit2 lebih cepat daripada innodb_flush_log_at_trx_commit3. innodb_flush_log_at_trx_commit3, di sisi lain, membuat InnoDB lebih stabil dan data-konsisten— pada dasarnya petunjuk bahwa Anda ingin I/O Anda mem-bypass cache kernel Linux. Perlu dicatat bahwa beberapa parameter ini (misalnya, innodb_flush_log_at_trx_commit3) hanya tersedia di sistem Linux dan tidak kompatibel dengan Windows

InnoDB juga mendukung penguncian tingkat baris, yang berarti hanya baris yang diakses oleh transaksi yang akan dikunci. Sebagai perbandingan, MyISAM hanya mendukung penguncian tingkat tabel, artinya hanya satu sesi yang dapat memperbarui tabel pada saat tertentu. Ini berpotensi berguna untuk, antara lain, mengimpor data dalam jumlah besar ke server database Anda

Dengan demikian, Anda juga dapat menggunakan ClusterControl yang dikembangkan oleh pakar basis data di Somenines untuk mencapai tujuan kinerja Anda

InnoDB dan Data Besar. Keanehan

Setelah menggunakan tip di atas untuk mengoptimalkan pengaturan InnoDB di my. cnf, kita harus baik-baik saja, kan?

InnoDB memiliki beberapa file penting untuk kinerjanya. Misalnya, /var/lib/mysql/data berisi folder yang dinamai berdasarkan database dan juga tiga file. ib_logfile0, ib_logfile1, dan ibdata1

ibdata1 adalah tempat semua data yang berkaitan dengan InnoDB disimpan. Ketika berhadapan dengan big data, file ini bisa menimbulkan masalah karena, secara default, ukurannya tidak bisa menyusut—hanya bisa membesar. Mau tidak mau, mematikan MySQL dan menghapus file ini akan menghilangkan semua data yang disimpan di InnoDB—tidak terlalu ideal. ibdata1 menyimpan banyak kelas informasi, termasuk data dan indeks tabel InnoDB, metadata tabel InnoDB, data MVCC, dan juga buffer insert dan doublewrite—artinya file ini bisa menjadi sangat besar. Cara terbaik untuk menghindari sakit kepala saat menangani file ini di MySQL adalah dengan melakukan pembersihan

1. Ambil salinan data yang disimpan di InnoDB
2. Drop semua database kecuali mysql, information_schema, dan performance_schema
3. Hentikan MySQL
4. Aktifkan innodb_file_per_table, setel innodb_flush_method ke O_DIRECT dan setel innodb_buffer_pool_size dan innodb_log_file_size ke nilai yang sesuai sesuai saran di atas
5. Hapus file ibdata1 dan file log dan restart MySQL

Setelah Anda melakukan langkah-langkah di atas, ibdata1 dan file log akan dibuat ulang. Hasil tangkapan? . Ini membuat penyimpanan dan pengelolaan kumpulan data yang sangat besar di dalam InnoDB jauh lebih mudah

Setelah melakukan langkah-langkah di atas, awasi indeks dan normalkan data sedapat mungkin—pada titik ini, instance MySQL harus siap menangani kumpulan data besar

Bagaimana dengan MyISAM?

Meskipun menjadi salah satu mesin penyimpanan MySQL paling populer—dan default hingga MySQL 5. 5—kami belum menyentuh MyISAM, jadi kami mungkin harus membahasnya juga, bukan?

Inilah tangkapannya dengan MyISAM. sementara itu pasti digunakan untuk menawarkan fungsionalitas yang tidak dimiliki oleh mitranya yang lebih baru, karena MySQL terus berkembang, sebagian besar fitur ini telah tersedia di InnoDB. Misalnya, InnoDB telah mendukung indeks teks lengkap dan tablespace portabel sejak MySQL 5. 6, indeks spasial sejak MySQL 5. 7, dan seterusnya. Akibatnya, hari ini, MyISAM secara efektif hanya merupakan alternatif yang valid untuk InnoDB ketika kueri utama—jika bukan satu-satunya—yang dijalankan adalah COUNT(*) sederhana. Karena MyISAM menyimpan nomor dalam metadata tabel (dan InnoDB tidak), kueri COUNT(*) biasanya akan selesai lebih cepat di MyISAM

Jadi haruskah kita menggunakan MyISAM untuk menangani data (atau data besar) di MySQL?

Nah, jawaban singkat? . MyISAM memiliki reputasi sebagai mesin penyimpanan yang tidak dapat diandalkan karena memiliki integritas data yang buruk, pemulihan kerusakan yang buruk, dan penguncian tingkat tabel. Itu juga tidak mendukung ACID (eksklusif untuk InnoDB di ruang ini), dan tabel MyISAM sering rusak setelah crash

Ringkasan

Untuk meringkas

● MySQL sangat mampu menjalankan kumpulan data besar—semuanya bergantung pada bagaimana server (dan instans MySQL) dikonfigurasi

● InnoDB adalah mesin penyimpanan data yang optimal untuk mendorong MySQL ke level berikutnya saat menangani kumpulan data besar, karena dapat dikonfigurasi untuk memberikan kepatuhan ACID dan kinerja tinggi pada saat yang bersamaan

● Menggunakan InnoDB untuk menangani data besar (atau jenis data apa pun, dalam hal ini) di MySQL dapat menyebabkan masalah karena mesin menyimpan semua data terkait dalam file yang tidak dapat menyusut yang disebut ibdata1, tetapi InnoDB dapat dikonfigurasi untuk hanya menyimpan metadata di dalamnya

● MyISAM memiliki integritas data yang buruk dan pemulihan kerusakan yang buruk. Tidak seperti InnoDB, ia juga memiliki penguncian tingkat tabel, artinya MySQL hanya mengizinkan satu sesi untuk memperbarui tabel tertentu pada satu waktu—ini mungkin tidak cocok untuk aplikasi yang memerlukan integritas data dan kinerja tinggi pada saat yang bersamaan. Tetap dengan InnoDB

Selain itu, saat menangani data (atau data besar) di MySQL, kueri Anda mungkin menjadi kompleks. Jika Anda mencari klien SQL yang mudah digunakan, cobalah Arctype

Bisakah MySQL menangani 100 juta catatan?

Bisakah MySQL menangani satu miliar baris?

Bisakah MySQL mendukung database besar?

Bisakah MySQL menangani jutaan pengguna?