Caching di MySQL

7.1 Caching di sisi DB

MySQL menggunakan algoritme yang sangat skalabel saat bekerja dengan tabel, sehingga MySQL dapat berjalan bahkan dengan jumlah memori yang kecil. Secara alami, untuk kinerja yang lebih baik, Anda membutuhkan lebih banyak RAM.

Untuk melihat pengaturan saat ini, sambungkan ke database

#mysq -u root -p

mysql> show variables like 'query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 134217728 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
5 rows in set (0.00 sec)

Pertama, pastikan caching diaktifkan. Variabel:

• query_cache_typeharus ON (1) atau DEMAND (2)
• query_cache_limit- menentukan ukuran maksimum dari hasil yang akan masuk ke cache
• query_cache_sizeberbeda dari nol. Saat menggunakan DEMAND, hanya permintaan yang berisi direktif yang akan di-cacheSQL_CACHE;
• query_cache_min_res_unitukuran minimum blok memori yang dialokasikan untuk menyimpan hasil kueri yang di-cache. MySQL tidak menyimpan cache dalam satu potongan besar memori, melainkan mengalokasikan blok dengan ukuran minimum query_cache_min_res_unit(=4KB secara default) sesuai permintaan. Blok terakhir tersebut dipotong ke ukuran data, dan memori yang tersisa dibebaskan.

Efek dari caching adalah ketika server menerima permintaan, server akan melihat apakah hash dari permintaan tersebut ada di dalam cache. Jika hash cocok - server segera mengembalikan hasilnya - tanpa menguraikan permintaan, mengoptimalkan, dll. overhead - disertai dengan mekanisme caching - menjelajahi cache, menulis hasil kueri ke cache, dll.

Dan jika Anda memiliki banyak permintaan kecil di cache, ini dapat menyebabkan fragmentasi memori karena banyaknya blok gratis. Dan ini, pada gilirannya, menyebabkan entri yang di-cache dihapus karena kekurangan memori. Dalam hal ini, masuk akal untuk mengurangi nilai query_cache_min_res_unit. Jika sebagian besar kueri Anda memberikan hasil yang besar, meningkatkan setelan ini dapat meningkatkan kinerja.

Mari kita coba mengevaluasi efeknya. Kami melihat bagaimana penghitung hit cache berubah (Qcahe_hits), jumlah permintaan yang dinyatakan tidak valid karena kurangnya memori (Qcache_lowmem_prunes), jumlah total permintaan jenis SELECT (dan hanya mereka yang di-cache):

#mysq -u root -p
mysql> show status like 'Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 715       |
| Qcache_free_memory      | 130369640 |
| Qcache_hits             | 24209     |
| Qcache_inserts          | 16215     |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 444       |
| Qcache_queries_in_cache | 1740      |
| Qcache_total_blocks     | 4225      |
+-------------------------+-----------+
8 rows in set (0.00 sec)

7.2 Status cache saat ini

Untuk memantau cache kueri digunakan SHOW STATUS:

mysql> SHOW STATUS LIKE 'Qcache_%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 10       |
| Qcache_free_memory      | 16755496 |
| Qcache_hits             | 49812    |
| Qcache_inserts          | 103999   |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 180      |
| Qcache_queries_in_cache | 6        |
| Qcache_total_blocks     | 28       |
+-------------------------+----------+
8 rows in set (0.00 sec)

• Qcache_free_blocksmenunjukkan berapa banyak blok kosong yang ada di cache (akan berkurang seiring meningkatnya permintaan yang di-cache);
• Qcache_total_blocks— jumlah blok yang ditempati;
• Qcache_free_memory- menunjukkan memori "tersedia" gratis untuk caching;
• Qcache_hits- jumlah permintaan, yang hasilnya diambil dari cache, tanpa benar-benar mengakses database;
• Qcache_inserts- jumlah permintaan yang ditambahkan ke cache;
• Qcache_lowmem_prunes- jumlah permintaan yang dihapus dari cache karena kekurangan memori;
• Qcache_not_cached- jumlah permintaan yang tidak ditulis ke cache karena penggunaan fungsi manajemen waktu, dll.;
• Qcache_queries_in_cache- jumlah permintaan yang ada di cache.

Anda dapat melihat jumlah total kueri SELECT:

mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 16719 |
+---------------+-------+
1 row in set (0.00 sec)

7.3 Efisiensi caching

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

Nilai saat ini dapat dilihat seperti ini:

SHOW VARIABLES LIKE 'query_cache_size';

Sekali lagi, muncul pertanyaan: bagaimana memilih nilai yang memadaiquery_cache_size?

Ini akan membantu Qcache_lowmem_prunes. Variabel ini menyimpan jumlah permintaan yang telah dihapus dari cache karena kebutuhan untuk menyimpan permintaan baru. Penting untuk mengupayakan ukuran cache yang Qcache_lowmem_pruneshanya akan meningkat sedikit. Untuk melakukan ini, disarankan untuk membandingkan perbedaan nilai Qcache_lowmem_prunesper jam dan jumlah permintaan yang diterima oleh mysql pada jam yang sama.

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2
query_cache_size = (volume lalu lintas dalam 10 menit) * 1,2 "

Ini akan men-cache permintaan selama 10 menit + memberikan tambahan 20% memori untuk fragmentasi cache dan cadangan caching tambahan

Anda dapat menghitung jumlah dan volume rata-rata respons terhadap permintaan menggunakan variabel Bytes_sent masing-masing

Jadi query_cache_sizekami meningkatkan nilainya, setelah itu Anda harus memperhatikan nilainya Qcache_total_blocks, Qcache_free_blocksdan Qcache_queries_in_cache. MySQL menyimpan cache dalam blok. Untuk 1 permintaan, diperlukan 2 blok: satu untuk teks permintaan itu sendiri, yang kedua untuk hasilnya.

Jika kita mempertimbangkan tabel dari nilainyaQcache%

Jumlah total blok cacheQcache_total_blocks – 28

Permintaan 6 di-cache sekarang, yang berarti 6 * 2 = 12 blok sedang sibuk

Blok gratis Qcache_free_blocks – 10. Semakin banyak menganggur Qcache_free_blocks, semakin besar tingkat "fragmentasi" cache.

Jika sebagian besar kueri memiliki sedikit data yang dihasilkan, maka ada baiknya mengurangi ukuran blok cache minimum query_cache_min_res_unit, yaitu 4 KB secara default.

Jika sebagian besar permintaan mengembalikan banyak data, maka ada baiknya menambah ukuran blok cache.

Hal utama adalah mencapai nilai minimum Qcache_free_blocks.

Jika penghitungnya Qcache_not_cachedbesar, Anda dapat mencoba menambah variabel query_cache_limit- ini akan memungkinkan Anda untuk meningkatkan batas dan menyimpan hasil kueri yang "tidak sesuai".

Variabel konfigurasi berikut bertanggung jawab untuk menggunakan cache kueri:

• query_cache_size— ukuran cache kueri. query_cache_size = 0menonaktifkan penggunaan cache;
• query_cache_limit- ukuran sampel maksimum yang disimpan dalam cache;
• query_cache_wlock_invalidate- menentukan apakah data akan diambil dari cache jika tabel tempatnya dikunci untuk dibaca.
=

Untuk mengaktifkan caching kueri mysql, cukup tambahkan baris berikut ke my.cnf (Bagian [mysqld]):

query_cache_size = 268435456
query_cache_type =1 
query_cache_limit =1 048576

Dan restart layanan.

7.4 Di mana cache tidak digunakan

Tidak di-cache:

• Permintaan dariSQL_NO_CACHE
• Kueri yang telah disiapkan(Prepared statements);
• Kueri yang merupakan subkueri dari kueri luar;
• Kueri di dalam prosedur dan fungsi tersimpan;
• Kueri yang menggunakan fungsi:

  BENCHMARK (), CONNECTION_ID (), CONVERT_TZ (), CURDATE (), CURRENT_DATE (), CURRENT_TIME (), CURRENT_TIMESTAMP (), CURTIME (), DATABASE (), ENCRYPT ()dengan satu argumen, FOUND_ROWS (), GET_LOCK (), LAST_INSERT_ID (), LOAD_FILE (), MASTER_POS_WAIT (), NOW (), , RAND (), RELEASE_LOCK (), SLEEP (), SYSDATE (), UNIX_TIMESTAMP ()tanpa argumen, USER (),UUID ();

• Kueri menggunakan fungsi tersimpan, variabel pengguna, atau tabel referensi di database sistem mysql atau INFORMATION_SCHEMA;
• • Permintaan dalam bentuk berikut:
• SELECT ... IN SHARE MODE
• SELECT ... FOR UPDATE
• SELECT ... INTO OUTFILE ...
• SELECT ... INTO DUMPFILE ...
• SELECT * FROM ... WHERE autoincrement_col IS NULL
• Kueri dengan tabel sementara atau tidak menggunakan tabel sama sekali;
• Permintaan menghasilkan peringatan (warning);

Anda dapat mendefrag cache dengan perintah:

mysql>flush query cache;

Hapus - perintah:

mysql>flush query cache;

Yang paling penting

Jangan pernah bekerja dalam produksi dengan pengaturan default. Ini akan mengakibatkan sebagian besar sumber daya server tidak digunakan. Penyesuaian MySQL yang tepat dapat meningkatkan kinerja database beberapa kali. Ini tidak hanya akan mempercepat aplikasi, tetapi juga mengatasi beban yang berat.