Memecahkan masalah memori MySQL

Kondisi kehabisan memori (OOM) terjadi saat proses mencoba mengalokasikan memori dan gagal karena server tidak memiliki memori yang tersedia. Hal ini sering kali mengakibatkan Linux OOM Killer, yakni proses yang digunakan kernel Linux ketika sistem hampir kehabisan memori, sehingga menghentikan proses database. 

Error database karena kondisi OOM tidak ideal karena proses database dihentikan menggunakan perintah SIGKILL yang membuat aplikasi tidak memiliki peluang untuk melakukan penonaktifan MySQL secara normal. Hal ini menyebabkan masalah berikut:

• Server MySQL mengalami gangguan (saat menyelesaikan pemulihan error yang dapat memakan waktu beberapa menit) sehingga memengaruhi pengguna dan bisnis

• Server MySQL memerlukan waktu tambahan untuk sepenuhnya memanaskan buffer dan cache agar mencapai performa puncak
• Kemungkinan kerusakan data karena error yang tidak terduga

MySQL menambahkan instrumentasi memori di performance_schema mulai dari versi 5.7 yang membantu memahami alokasi memori MySQL. Artikel ini akan membantu Anda memecahkan masalah memori MySQL menggunakan performance_schema.

Setiap instance Cloud SQL didukung oleh mesin virtual (VM) yang berjalan di server Google Cloud host. Setiap VM mengoperasikan program database, seperti Server MySQL dan agen layanan yang menyediakan layanan pendukung, seperti logging dan pemantauan. Cloud SQL mencadangkan sebagian memori untuk sistem operasi (OS), agen layanan, dan mengalokasikan memori yang tersisa untuk program database seperti mysqld. 

Tabel berikut menunjukkan alokasi memori untuk proses mysqld dan konfigurasi kumpulan buffer InnoDB default untuk setiap jenis mesin.

Untuk instance yang memiliki RAM 10 GB ke atas, proses mysqld dibatasi untuk menggunakan 92% RAM instance dan kumpulan buffer InnoDB dikonfigurasi dalam kisaran 75-80% dari memori yang tersedia untuk proses mysqld. Selain kumpulan buffer InnoDB, MySQL mengalokasikan beberapa buffer dan cache untuk meningkatkan performa. Lihat artikel ini untuk memahami alokasi memori di MySQL.

Ketika penggunaan memori mysqld tumbuh melampaui batas proses mysqld yang dikonfigurasi, killer OOM Linux akan menghentikan proses mysqld karena merupakan konsumen memori terbesar pada instance.

performance_schema adalah fitur MySQL untuk memantau eksekusi server di tingkat bawah. Cloud SQL untuk MySQL memungkinkan pengaktifan performance_schema pada instance yang memiliki ukuran RAM 3 GB atau lebih. Fitur ini diaktifkan secara default pada instance dengan RAM minimal 15 GB, dimulai pada versi MySQL 8.0. 

Performance_schema dinonaktifkan secara default untuk versi MySQL 5.6 dan 5.7. Fitur ini dapat diaktifkan menggunakan flag database. 

Mengaktifkan performance_schema melibatkan beberapa overhead performa dan juga memiliki implikasi memori. Lihat Model Alokasi Memori Skema Performa untuk memahami alokasi memorinya. 

Instrumentasi memori performance_schema membantu memantau penggunaan memori di MySQL. Instrumen memori memiliki nama dalam bentuk memory/code_area/instrument_name, dengan code_area adalah nilai seperti SQL atau Innodb, daninstrument_name adalah detail instrumen.

MySQL 5.7 mengaktifkan beberapa instrumen memori secara default. Anda dapat mengaktifkannya dengan memperbarui kolom ENABLED pada tabel performance_schema.setup_instruments. 

mysql> memilih version();

+-------------------+

| version()         |

+-------------------+

| 5.7.39-google-log |

+-------------------+

1 baris dalam set (0,00 detik)

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES     |       70 |

| NO      |      306 |

+---------+----------+

2 baris dalam set (0,00 detik)

mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';

Kueri OK, 306 baris terdampak (0,00 sec)

Baris yang cocok: 376  Diubah: 306  Peringatan: 0

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES     |      376 |

+---------+----------+

1 baris dalam set (0,00 detik)

Mengaktifkan instrumen memori pada runtime hanya akan menghitung objek memori yang dibuat setelah mengaktifkan instrumentasi. Oleh karena itu, sebaiknya tunggu server mengumpulkan data yang cukup untuk tujuan pemecahan masalah.

MySQL 8.0 mengaktifkan semua instrumen memori secara default.

mysql> memilih version();

+---------------+

| version()     |

+---------------+

| 8.0.28-google |

+---------------+

1 baris dalam set (0,00 detik)

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES     |      490 |

+---------+----------+

1 baris dalam set (0,00 detik)

Skema sys berisi banyak tampilan yang merangkum data performance_schema ke dalam bentuk yang mudah dipahami. Sebagian besar tampilan ini muncul berpasangan. Keduanya memiliki nama yang sama dan salah satunya memiliki awalan x$. Tampilan berikut ini tersedia di MySQL 5.7 dan 8.0 untuk menampilkan alokasi memori pada level yang berbeda.

mysql> menggunakan sys;

Database diubah

mysql> menampilkan tabel seperti '%memory%';

+-------------------------------------+

| Tables_in_sys (%memory%)            |

+-------------------------------------+

| memory_by_host_by_current_bytes     |

| memory_by_thread_by_current_bytes   |

| memory_by_user_by_current_bytes     |

| memory_global_by_current_bytes      |

| memory_global_total                 |

| x$memory_by_host_by_current_bytes   |

| x$memory_by_thread_by_current_bytes |

| x$memory_by_user_by_current_bytes   |

| x$memory_global_by_current_bytes    |

| x$memory_global_total               |

+-------------------------------------+

10 baris dalam set (0,02 detik)

Tampilan sys.memory_global_total merangkum total penggunaan memori dalam format yang dapat dibaca.

mysql> memilih * dari sys.memory_global_total;

+-----------------+

| total_allocated |

+-----------------+

| 12.43 GiB       |

+-----------------+

1 baris dalam set (0,44 detik)

sys.x$memory_global_total menampilkan informasi yang sama tetapi dalam byte.

mysql> memilih * dari sys.x$memory_global_total;

+-----------------+

| total_allocated |

+-----------------+

|     13351482992 |

+-----------------+

1 baris dalam set (1,39 detik)

Tampilan tanpa awalan x$ dimaksudkan untuk memberikan output yang lebih ramah pengguna dan lebih mudah dibaca. Tampilan dengan awalan x$ yang menampilkan nilai yang sama dalam bentuk mentah lebih ditujukan untuk digunakan dengan alat lain yang melakukan pemrosesannya sendiri pada data.

Tampilan sys.memory_global_by_current_bytes menampilkan penggunaan memori saat ini yang dikelompokkan menurut jenis alokasi (event_name) misalnya memory/innodb/buf_buf_pool (kumpulan buffer InnoDB). Secara default, baris diurutkan berdasarkan jumlah memori yang digunakan secara menurun. 

Sebelum kita melihat perincian setiap jenis alokasi, ada baiknya Anda memiliki ringkasan penggunaan memori untuk setiap area kode seperti Innodb, sql, performance_schema, dan banyak lagi. 

Kueri berikut menggabungkan memori yang saat ini dialokasikan berdasarkan area kode. 

SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area,

FORMAT_BYTES(SUM(current_alloc)) AS current_alloc

FROM sys.x$memory_global_by_current_bytes

GROUP BY SUBSTRING_INDEX(event_name,'/',2)

ORDER BY SUM(current_alloc) DESC;

+---------------------------+---------------+

| code_area                 | current_alloc |

+---------------------------+---------------+

| memory/innodb             | 11,28 GiB     |

| memory/performance_schema | 520,02 MiB    |

| memory/temptable          | 514,00 MiB    |

| memory/sql                | 106,12 MiB    |

| memory/mysys              | 43,25 MiB     |

| memory/vio                | 372,15 KiB    |

| memory/myisam             |  696 byte    |

| memory/csv                |   88 byte    |

| memory/blackhole          |   88 byte    |

+---------------------------+---------------+

9 baris dalam set (1,02 detik)

Output ini menunjukkan bahwa innodb adalah konsumen memori terbesar yang diikuti oleh performance_schema dan tabel sementara.

Sekarang, kueri sys.memory_global_by_current_bytes secara langsung menunjukkan penggunaan memori untuk struktur internal MySQL seperti yang dijelaskan di kolom event_name. Jika Anda memecahkan masalah peningkatan memori yang tidak normal, Anda dapat mengidentifikasi sumber dengan melihat output tampilan ini.

mysql> pilih event_name,current_alloc,high_alloc, dari sys.memory_global_by_current_bytes limit 5;

+-------------------------------+---------------+------------+

| event_name                    | current_alloc | high_alloc |

+-------------------------------+---------------+------------+

| memory/innodb/buf_buf_pool    | 10,72 GiB     | 10,72 GiB  |

| memory/temptable/physical_ram | 514,00 MiB    | 515,00 MiB |

| memory/innodb/hash0hash       | 292,69 MiB    | 292,69 MiB |

| memory/innodb/memory          | 191,77 MiB    | 197,94 MiB |

| memory/sql/TABEL              | 60,39 MiB     | 62,35 MiB  |

+-------------------------------+---------------+------------+

5 baris dalam set (0,45 detik)

Berikut detail setiap jenis alokasi.

Tampilan ini merangkum penggunaan memori yang dikelompokkan oleh pengguna. Jika pengguna memiliki beberapa koneksi, tampilan ini akan menjumlahkan penggunaan memori untuk semua instance pengguna.

mysql> memilih user,current_allocations dari memory_by_user_by_current_bytes;

+-----------------+-------------------+

| user            | current_allocated |

+-----------------+-------------------+

| sbtest          | 60.30 MiB         |

| background      | 2.68 MiB          |

| root            | 1.52 MiB          |

| event_scheduler | 16.38 KiB         |

+-----------------+-------------------+

4 baris dalam set (1,16 detik)

Jika Anda menggunakan pengguna yang berbeda untuk setiap aplikasi/tugas (disarankan), tampilan ini dapat membantu mengidentifikasi apakah aplikasi atau tugas tertentu menggunakan memori tinggi.

Tampilan ini merangkum penggunaan memori yang dikelompokkan menurut nama host.

mysql> memilih host,current_allocations dari memory_by_host_by_current_bytes;

+------------+-------------------+

| host       | current_allocated |

+------------+-------------------+

| 10.128.0.8 | 62.10 MiB         |

| background | 2.70 MiB          |

| 127.0.0.1  | 311.01 KiB        |

| localhost  | 16.38 KiB         |

+------------+-------------------+

4 baris dalam set (1,58 detik)

Tampilan ini berguna untuk mengidentifikasi penggunaan memori per host klien. Memiliki beberapa server klien/aplikasi sangat umum saat ini. Tampilan ini akan membantu mengidentifikasi apakah ada klien atau beban kerja tertentu yang menyebabkan penggunaan memori yang tinggi. Anda juga dapat men-deploy perubahan pada satu server klien/aplikasi terlebih dahulu, memantau dampaknya pada memori (dengan membandingkan penggunaan memori dengan host klien lainnya), dan membuat keputusan yang tepat.

Tampilan ini merangkum penggunaan memori, yang dikelompokkan menurut thread, dan membantu menemukan thread yang menggunakan sebagian besar memori.

mysql> memilih thread_id,user,current_allocations dari sys.memory_by_thread_by_current_bytes limit 5;

+-----------+-------------------+-------------------+

| thread_id | user              | current_allocated |

+-----------+-------------------+-------------------+

|     12999 | sbtest@10.128.0.8 | 3.80 MiB          |

|     12888 | sbtest@10.128.0.8 | 3.75 MiB          |

|     12760 | sbtest@10.128.0.8 | 3.68 MiB          |

|     13128 | sbtest@10.128.0.8 | 3.67 MiB          |

|     13221 | sbtest@10.128.0.8 | 3.47 MiB          |

+-----------+-------------------+-------------------+

5 baris dalam set (2,29 detik)

Jika Anda menemukan thread tertentu yang menggunakan memori tinggi, Anda dapat memfilter log kueri MySQL yang lambat atau log umum menggunakan thread_id dan mengidentifikasi sesi/kueri yang menyebabkan pertumbuhan memori.

Memecahkan masalah penggunaan memori yang tinggi adalah tugas yang menantang. Untungnya, instrumentasi memori performance_schema membantu pengguna memahami bagaimana memori digunakan oleh struktur MySQL internal. Tampilan skema sistem memudahkan penafsiran informasi. Jika Anda mengalami masalah terkait memori, fitur ini akan membantu Anda menemukan sumber masalah dan Anda dapat memperbaiki masalah yang sama dengan mengubah konfigurasi/kueri. 

Contoh:

1. Jika tabel internal sementara memerlukan jumlah memori yang tinggi, Anda dapat membatasi ukuran tabel sementara menggunakan tmp_table_size atau mengurangi innodb_buffer_pool_size untuk membuat ruang bagi buffer per-sesi
2. Jika thread tertentu menggunakan memori tinggi, Anda dapat memfilter log kueri yang lambat menggunakan thread_id untuk mengidentifikasi kueri yang bermasalah dan menyesuaikannya untuk mengurangi jejak memori untuk thread/sesi tersebut

Jika Anda mengalami situasi di mana instance MySQL menggunakan memori lebih tinggi dari seharusnya dan penggunaan memori terus meningkat hingga server kehabisan memori, dan performance_schema menunjukkan kesenjangan dalam memori total versus memori yang diinstrumentasi atau tidak menunjukkan ke mana perginya memori, mungkin itu adalah gejala kebocoran memori. Jika Anda mencurigai adanya kebocoran memori, Anda dapat melakukan langkah-langkah berikut:

• Tinjau konfigurasi MySQL dengan cermat dan pastikan tidak ada buffer/cache yang dialokasikan secara berlebihan. Artikel ini akan membantu Anda memahami cara MySQL mengalokasikan memori untuk berbagai buffer/cache.
• Identifikasi fitur/konfigurasi/kueri yang menyebabkan masalah memori, coba rekonstruksi masalah tersebut.
• Tinjau catatan rilis MySQL versi minor berikutnya dalam seri yang sama untuk memeriksa apakah ada bug dengan perilaku serupa yang telah diperbaiki. 
• Upgrade MySQL ke versi minor terbaru untuk melihat apakah langkah tersebut dapat memperbaiki masalah memori.
• Telusuri database bugs MySQL untuk memeriksa apakah masalah serupa dilaporkan oleh pengguna komunitas lain.
• Buat bug baru dengan kasus pengujian yang dapat direproduksi jika tidak ada bug untuk masalah yang sama.