Mari kita bincangkan sama ada MySQL akan menyebabkan OOM jika terdapat terlalu banyak pertanyaan data

Artikel ini membawa anda pengetahuan yang berkaitan sama ada MySQL akan menyebabkan OOM jika terdapat terlalu banyak pertanyaan data saya harap ia akan membantu anda.

Memori hos hanya 100G Sekarang kita perlu mengimbas keseluruhan jadual jadual besar 200G Adakah memori hos DB akan digunakan?

Semasa sandaran logik, bukankah ia hanya mengimbas keseluruhan pangkalan data? Jika ini akan memakan semua memori, bukankah sandaran logik akan gagal sejak dahulu lagi?

Jadi nampaknya tiada masalah dengan imbasan meja penuh meja besar. kenapa ni?

Kesan imbasan jadual penuh pada lapisan pelayan

Andaikan kita kini mahu melakukan imbasan jadual penuh pada jadual InnoDB 200G db1 .t Imbasan jadual. Sudah tentu, jika anda ingin menyimpan hasil imbasan pada klien, anda akan menggunakan arahan seperti ini:

mysql -h$host -P$port -u$user -p$pwd -e 
 "select * from db1.t" > $target_file

Data InnoDB disimpan pada indeks kunci utama, jadi imbasan jadual penuh sebenarnya mengimbas terus indeks kunci utama jadual t . Memandangkan pernyataan pertanyaan ini tidak mempunyai syarat pertimbangan lain, setiap baris yang ditemui boleh diletakkan terus dalam set hasil dan kemudian dikembalikan kepada klien.

Jadi, di manakah "set keputusan" ini wujud?

Pelayan tidak perlu menyimpan set hasil lengkap. Proses mendapatkan dan menghantar data adalah seperti berikut:

Dapatkan baris dan tulis pada "net_buffer". Saiz memori ini ditakrifkan oleh parameter "net_buffer_length"**, lalai ialah 16k

Dapatkan baris berulang kali sehingga **"net_buffer"** penuh, hubungi antara muka rangkaian dan hantarkannya

Jika sekiranya penghantaran berjaya, kosongkan **"net_buffer", kemudian teruskan mengambil baris seterusnya dan tulis ke dalam "net_buffer"**

Jika fungsi penghantaran kembali **"EAGAIN" atau "WSAEWOULDBLOCK"**, ini bermakna setempat Timbunan rangkaian (penampan hantar soket) penuh dan sedang menunggu. Sehingga timbunan rangkaian boleh ditulis semula, teruskan menghantar

proses penghantaran hasil pertanyaan

kelihatan:

• satu Semasa proses penghantaran pertanyaan, memori dalaman maksimum yang diduduki oleh MySQL ialah **"net_buffer_length"**, yang tidak akan mencapai 200G

• penampan hantaran soket tidak boleh mencapai 200G (default Define / proc/sys/net/core/wmem_default), jika penimbal hantar soket penuh, proses membaca data akan digantung

Jadi MySQL sebenarnya "menghantar semasa membaca" . Ini bermakna jika pelanggan menerima dengan perlahan, pelayan MySQL tidak akan dapat menghantar keputusan, dan masa pelaksanaan transaksi akan menjadi lebih lama.

Sebagai contoh, keadaan berikut ialah hasil yang dilihat pada senarai proses paparan pelayan apabila pelanggan tidak membaca kandungan **"socket receive buffer"**.

Penghantaran sebelah pelayan disekat

Jika anda melihat Negeri sentiasa "Menghantar kepada klien", ini bermakna susunan rangkaian sebelah pelayan sudah penuh.

Jika pelanggan menggunakan parameter –quick, kaedah mysql_use_result akan digunakan: baca satu baris dan proses satu baris. Andaikan bahawa logik perniagaan tertentu agak rumit, dan jika logik yang akan diproses selepas setiap baris data dibaca adalah sangat perlahan, ia akan mengambil masa yang lama untuk pelanggan mengambil baris data seterusnya, dan perkara di atas keputusan mungkin muncul.

Oleh itu, untuk perniagaan dalam talian biasa, jika pertanyaan mengembalikan sedikit hasil, adalah disyorkan untuk menggunakan antara muka **"mysql_store_result"** untuk menyimpan terus hasil pertanyaan ke memori setempat.

Sudah tentu, premisnya ialah pertanyaan itu tidak mengembalikan banyak hasil. Jika terdapat terlalu banyak, pelanggan akan menduduki hampir 20G memori kerana pertanyaan besar dilaksanakan Dalam kes ini, anda perlu menggunakan antara muka "mysql_use_result".

Jika anda melihat banyak urutan dalam "Menghantar kepada pelanggan" dalam MySQL yang anda bertanggungjawab untuk mengekalkan, ini bermakna anda mahu pelajar pembangunan perniagaan anda mengoptimumkan hasil pertanyaan dan menilai sama ada banyak hasil yang dikembalikan adalah munasabah.

Untuk mengurangkan bilangan utas dalam keadaan ini dengan cepat, anda boleh menetapkan **"net_buffer_length"** menjadi lebih besar.

Kadangkala, status banyak pernyataan pertanyaan pada kejadian ialah "Menghantar data", tetapi tiada masalah semasa menyemak rangkaian Mengapakah Penghantaran data mengambil masa yang lama?

Perubahan status pernyataan pertanyaan adalah seperti berikut:

• Selepas pernyataan pertanyaan MySQL memasuki fasa pelaksanaan, mula-mula tetapkan status kepada "Menghantar data"

• Kemudian, hantar maklumat berkaitan lajur (data meta) hasil pelaksanaan kepada klien

• dan kemudian teruskan proses melaksanakan pernyataan

• Selepas pelaksanaan selesai, tetapkan status kepada rentetan kosong.

Iaitu, "Menghantar data" tidak semestinya bermaksud "menghantar data", tetapi mungkin pada mana-mana peringkat dalam proses pelaksana. Sebagai contoh, anda boleh membina senario menunggu kunci dan melihat status Menghantar data.

Membaca keseluruhan jadual dikunci:

Menghantar status data

Ia boleh dilihat bahawa sesi2 sedang menunggu Kunci, status dipaparkan sebagai Menghantar data.

• Hanya apabila benang dalam keadaan "menunggu pelanggan menerima hasil", "Menghantar kepada pelanggan" akan dipaparkan

• Jika ia dipaparkan sebagai "Menghantar data", ia hanya bermaksud "melaksanakan"

Oleh itu, hasil pertanyaan dihantar kepada klien dalam segmen, jadi keseluruhan jadual diimbas dan pertanyaan mengembalikan sejumlah besar data, yang tidak menyebabkan memori menjadi Meletup.

Di atas ialah logik pemprosesan lapisan pelayan. Bagaimanakah ia diproses dalam enjin InnoDB?

Impak imbasan jadual penuh pada InnoDB

Salah satu fungsi memori InnoDB ialah menyimpan hasil yang dikemas kini dan bekerjasama dengan log semula untuk mengelakkan rawak Tulis cakera.

Halaman data memori diuruskan dalam Buffer Pool (dirujuk sebagai BP), dan BP memainkan peranan dalam mempercepatkan kemas kini dalam WAL.

BP juga boleh mempercepatkan pertanyaan.

Disebabkan WAL, apabila urus niaga dilakukan, halaman data pada cakera adalah lama Jika terdapat pertanyaan untuk membaca halaman data dengan serta-merta, perlukah log buat semula digunakan pada halaman data dengan segera?

Tidak perlu. Kerana pada masa ini, hasil halaman data memori adalah yang terkini, baca sahaja halaman memori secara terus. Pada masa ini, pertanyaan tidak perlu membaca cakera, dan hasilnya diambil terus dari memori, yang sangat pantas. Oleh itu, Kolam Penampan boleh mempercepatkan pertanyaan.

Kesan pecutan BP pada pertanyaan bergantung pada penunjuk penting, iaitu: kadar pukulan memori.

Anda boleh menyemak kadar pukulan BP semasa sistem dalam hasil status enjin papar innodb. Secara umumnya, untuk sistem dalam talian dengan perkhidmatan yang stabil untuk memastikan masa tindak balas memenuhi keperluan, kadar pukulan memori mestilah melebihi 99%.

Laksanakan status innodb enjin paparan, anda boleh melihat perkataan "Kadar pukulan kumpulan penimbal", yang memaparkan kadar pukulan semasa. Sebagai contoh, kadar hit dalam gambar di bawah ialah 100%.

Jika semua halaman data yang diperlukan untuk pertanyaan boleh diperolehi terus daripada memori, itu adalah yang terbaik, dan kadar hit yang sepadan ialah 100%.

Saiz Kolam Penampan InnoDB ditentukan oleh parameter **"innodb_buffer_pool_size"** secara amnya disyorkan untuk menetapkannya kepada 60%~80% daripada memori fizikal yang tersedia.

Kira-kira sepuluh tahun yang lalu, volum data satu mesin ialah ratusan gigabait, dan memori fizikal ialah beberapa gigabait Kini, walaupun banyak pelayan mempunyai memori 128G atau lebih tinggi, volum data satu mesin adalah Mencapai tahap T.

Jadi, lazimnya "innodb_buffer_pool_size" adalah lebih kecil daripada saiz data cakera. Jika Kolam Penampan penuh dan halaman data perlu dibaca daripada cakera, halaman data lama mesti dihapuskan.

Pengurusan memori InnoDB

menggunakan algoritma Paling Kurang Digunakan Baru-baru ini (LRU) untuk menghapuskan data terpanjang yang tidak digunakan.

Algoritma LRU asas

Algoritma LRU untuk pengurusan InnoDB BP dilaksanakan menggunakan senarai terpaut:

• nyatakan1, ketua senarai terpaut ialah P1, menunjukkan P1 Ia adalah halaman data yang diakses baru-baru ini

• Pada masa ini, permintaan baca mengakses P3, jadi ia menjadi keadaan 2, dan P3 dialihkan ke hadapan

• Negeri 3 menunjukkan bahawa halaman data yang diakses kali ini tidak wujud dalam senarai terpaut, jadi halaman data baharu Px perlu dipohon dalam BP dan ditambah pada kepala senarai terpaut. Tetapi kerana memori penuh, memori baru tidak boleh diminta. Jadi memori halaman data Pm di penghujung senarai terpaut dikosongkan, kandungan Px disimpan dan diletakkan di kepala senarai terpaut

Akhirnya, halaman data Pm yang tidak diakses untuk masa yang paling lama dihapuskan.

Apakah yang akan berlaku jika kita ingin melakukan imbasan jadual penuh pada masa ini? Jika anda ingin mengimbas jadual 200G, jadual ini ialah jadual data sejarah dan biasanya tiada perniagaan yang mengaksesnya.

Kemudian, pengimbasan mengikut algoritma ini akan menghapuskan semua data dalam BP semasa dan menyimpan kandungan halaman data yang diakses semasa proses pengimbasan. Dalam erti kata lain, BP terutamanya menyimpan data daripada jadual data sejarah ini.

Untuk perpustakaan yang menyediakan perkhidmatan perniagaan, ini tidak boleh dilakukan. Anda akan melihat bahawa kadar pukulan memori BP menurun secara mendadak, tekanan cakera meningkat, dan tindak balas pernyataan SQL menjadi perlahan.

Jadi, InnoDB tidak boleh menggunakan LRU mentah secara langsung. InnoDB mengoptimumkannya.

Algoritma LRU yang dipertingkatkan

InnoDB membahagikan senarai terpaut kepada kawasan Baharu dan kawasan Lama dalam nisbah 5:3. Dalam rajah, LRU_old menunjuk ke kedudukan pertama kawasan lama, iaitu 5/8 daripada keseluruhan senarai terpaut. Iaitu, 5/8 berhampiran kepala senarai terpaut ialah kawasan Baharu, dan 3/8 berhampiran hujung senarai terpaut ialah kawasan lama.

Proses pelaksanaan algoritma LRU yang dipertingkatkan:

Nyatakan 1, anda perlu mengakses P3 Memandangkan P3 berada di kawasan Baharu, ia adalah sama seperti LRU sebelum pengoptimuman, jadi alihkannya ke kepala of the linked list => State 2

Selepas itu, anda perlu mengakses halaman data baharu yang tidak wujud dalam senarai terpaut semasa Pada masa ini, halaman data Pm masih dihapuskan, tetapi yang baru halaman data yang dimasukkan Px diletakkan di **"LRU_old"**

Halaman data di kawasan lama mesti membuat pertimbangan berikut setiap kali ia diakses:

Jika halaman data wujud dalam senarai terpaut LRU selama lebih daripada 1 saat, alihkannya ke kepala senarai terpaut

Jika halaman data wujud dalam senarai terpaut LRU kurang daripada 1s, kedudukan kekal tidak berubah. 1s dikawal oleh parameter **"innodb_old_blocks_time"**, nilai lalai ialah 1000, unit ms.

Strategi ini disesuaikan untuk mengendalikan operasi seperti imbasan jadual penuh. Atau imbas jadual data sejarah 200G:

4 Semasa proses imbasan, halaman data yang perlu dimasukkan baru diletakkan di kawasan lama

5 halaman data, ini Halaman data akan diakses beberapa kali, tetapi disebabkan pengimbasan berurutan, selang masa antara akses pertama dan akses terakhir halaman data ini tidak akan melebihi 1 saat, jadi ia masih akan dikekalkan di kawasan lama

6 Jika kita terus mengimbas data berikutnya, halaman data sebelumnya tidak akan diakses lagi, jadi tidak akan ada peluang untuk berpindah ke kepala senarai terpaut (Kawasan baharu), dan ia akan segera dihapuskan.

Dapat dilihat bahawa manfaat terbesar strategi ini ialah dalam proses mengimbas jadual besar ini, walaupun BP juga digunakan, ia tidak memberi kesan kepada kawasan muda sama sekali, sekali gus memastikan Penampan Pool bertindak balas kepada pertanyaan perniagaan biasa Kadar hit.

Ringkasan

MySQL menggunakan logik pengiraan dan pengeluaran pada masa yang sama, jadi untuk hasil pertanyaan dengan jumlah data yang banyak, hasil lengkap tidak akan disimpan pada bahagian pelayan yang ditetapkan. Oleh itu, jika pelanggan tidak membaca keputusan dalam masa, ia akan menyekat proses pertanyaan MySQL, tetapi ia tidak akan memecahkan memori.

Bagi enjin InnoDB dalaman, disebabkan oleh strategi penyingkiran, pertanyaan besar tidak akan menyebabkan letupan memori. Selain itu, kerana InnoDB telah menambah baik algoritma LRU, kesan imbasan jadual penuh data sejuk pada Kolam Penampan juga boleh dikawal.

Imbasan jadual penuh masih menggunakan sumber IO, jadi masih tidak boleh melakukan imbasan jadual penuh secara langsung dalam talian pada pangkalan data utama semasa tempoh perniagaan puncak.

Pembelajaran yang disyorkan: tutorial video mysql

Atas ialah kandungan terperinci Mari kita bincangkan sama ada MySQL akan menyebabkan OOM jika terdapat terlalu banyak pertanyaan data. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!