Caching dalam MySQL

7.1 Caching pada bahagian DB

MySQL menggunakan algoritma yang sangat berskala apabila bekerja dengan jadual, jadi MySQL boleh berjalan walaupun dengan jumlah memori yang kecil. Sememangnya, untuk prestasi yang lebih baik, anda memerlukan lebih banyak RAM.

Untuk melihat tetapan semasa, sambung ke pangkalan data

#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 didayakan. Pembolehubah:

• query_cache_typemestilah ON (1) atau DEMAND (2)
• query_cache_limit- menentukan saiz maksimum hasil yang akan masuk ke dalam cache
• query_cache_sizeberbeza daripada sifar. Apabila menggunakan DEMAND, hanya permintaan yang mengandungi arahan akan dicacheSQL_CACHE;
• query_cache_min_res_unitsaiz minimum blok memori yang diperuntukkan untuk menyimpan hasil pertanyaan cache. MySQL tidak menyimpan cache dalam satu bahagian memori yang besar, sebaliknya ia memperuntukkan blok dengan saiz minimum query_cache_min_res_unit(=4KB secara lalai) atas permintaan. Blok terakhir seperti itu dipotong kepada saiz data, dan memori yang tinggal dibebaskan.

Kesan caching ialah apabila pelayan menerima permintaan, ia kelihatan untuk melihat sama ada hash permintaan itu berada dalam cache. Jika cincang sepadan - pelayan segera mengembalikan hasilnya - tanpa menghuraikan permintaan, mengoptimumkan, dsb. overhead - disertakan dengan mekanisme caching - menyemak imbas cache, menulis hasil pertanyaan ke cache, dsb.

Dan jika anda mempunyai banyak permintaan kecil dalam cache, maka ini boleh menyebabkan pemecahan memori disebabkan oleh sejumlah besar blok percuma. Dan ini, seterusnya, menyebabkan entri cache dipadamkan kerana kekurangan ingatan. Dalam kes ini, adalah wajar untuk mengurangkan nilai query_cache_min_res_unit. Jika kebanyakan pertanyaan anda menghasilkan hasil yang besar, maka meningkatkan tetapan ini boleh meningkatkan prestasi.

Mari cuba menilai kesannya. Kami melihat bagaimana pembilang hit cache berubah (Qcahe_hits), bilangan permintaan yang diisytiharkan tidak sah kerana kekurangan memori (Qcache_lowmem_prunes), jumlah bilangan permintaan jenis SELECT (dan hanya mereka yang dicache):

#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 semasa

Untuk memantau cache pertanyaan 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 percuma yang terdapat dalam cache (akan berkurangan apabila permintaan cache meningkat);
• Qcache_total_blocks- bilangan blok yang diduduki;
• Qcache_free_memory- menunjukkan memori "tersedia" percuma untuk caching;
• Qcache_hits- bilangan permintaan, keputusan yang diambil dari cache, tanpa benar-benar mengakses pangkalan data;
• Qcache_inserts- bilangan permintaan yang telah ditambahkan pada cache;
• Qcache_lowmem_prunes- bilangan permintaan yang dialih keluar daripada cache kerana kekurangan memori;
• Qcache_not_cached- bilangan permintaan yang tidak ditulis ke cache kerana penggunaan fungsi pengurusan masa, dsb.;
• Qcache_queries_in_cache- bilangan permintaan yang ada dalam cache.

Anda boleh melihat jumlah pertanyaan SELECT:

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

7.3 Kecekapan caching

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

Nilai semasa boleh dilihat seperti ini:

SHOW VARIABLES LIKE 'query_cache_size';

Sekali lagi, persoalan timbul: bagaimana untuk memilih nilai yang mencukupiquery_cache_size?

Ini akan membantu Qcache_lowmem_prunes. Pembolehubah ini menyimpan bilangan permintaan yang telah dialih keluar daripada cache kerana keperluan untuk cache permintaan baharu. Ia adalah perlu untuk berusaha untuk saiz cache sedemikian di mana Qcache_lowmem_prunesia akan meningkat sedikit sahaja. Untuk melakukan ini, adalah disyorkan untuk membandingkan perbezaan dalam nilai Qcache_lowmem_prunessejam dan bilangan 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 trafik dalam 10 minit) * 1.2"

Ini akan cache permintaan selama 10 minit + memberikan tambahan 20% memori untuk pemecahan cache dan simpanan caching tambahan

Anda boleh mengira bilangan dan purata volum respons kepada permintaan menggunakan pembolehubah Bytes_sent , masing-masing

Oleh itu, query_cache_sizekami meningkatkan nilai, selepas itu anda harus memberi perhatian kepada nilai Qcache_total_blocks, Qcache_free_blocksdan Qcache_queries_in_cache. MySQL menyimpan cache dalam blok. Untuk 1 permintaan, 2 blok diperlukan: satu untuk teks permintaan itu sendiri, yang kedua untuk hasilnya.

Jika kita mempertimbangkan jadual daripada nilaiQcache%

Jumlah bilangan blok cacheQcache_total_blocks – 28

Permintaan 6 dicache sekarang, yang bermaksud 6 * 2 = 12 blok sedang sibuk

Blok percuma Qcache_free_blocks – 10. Semakin terbiar Qcache_free_blocks, semakin tinggi tahap "pemecahan" cache.

Jika kebanyakan pertanyaan mempunyai sejumlah kecil data yang terhasil, maka adalah wajar mengurangkan saiz blok cache minimum query_cache_min_res_unit, iaitu 4 KB secara lalai.

Jika kebanyakan permintaan mengembalikan banyak data, maka saiz blok cache berbaloi.

Perkara utama ialah mencapai nilai minimum Qcache_free_blocks.

Jika kaunter Qcache_not_cachedbesar, anda boleh cuba meningkatkan pembolehubah query_cache_limit- ia akan membolehkan anda meningkatkan had dan cache hasil pertanyaan yang "tidak sesuai".

Pembolehubah konfigurasi berikut bertanggungjawab untuk menggunakan cache pertanyaan:

• query_cache_size— saiz cache pertanyaan. query_cache_size = 0melumpuhkan penggunaan cache;
• query_cache_limit- saiz sampel maksimum yang disimpan dalam cache;
• query_cache_wlock_invalidate- menentukan sama ada data akan diambil daripada cache jika jadual kepunyaan mereka dikunci untuk dibaca.
=

Untuk mendayakan caching pertanyaan mysql, cuma tambah baris berikut pada my.cnf (Bahagian [mysqld]):

query_cache_size = 268435456
query_cache_type =1 
query_cache_limit =1 048576

Dan mulakan semula perkhidmatan.

7.4 Di mana cache tidak digunakan

Tidak dicache:

• Permintaan daripadaSQL_NO_CACHE
• Pertanyaan yang disediakan(Prepared statements);
• Pertanyaan yang merupakan subkueri bagi pertanyaan luar;
• Pertanyaan di dalam prosedur dan fungsi yang disimpan;
• Pertanyaan yang menggunakan fungsi:

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

• Pertanyaan menggunakan fungsi tersimpan, pembolehubah pengguna atau jadual rujukan dalam pangkalan data 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
• Pertanyaan dengan jadual sementara atau tidak menggunakan jadual langsung;
• Permintaan menjana amaran (amaran);

Anda boleh defragment cache dengan arahan:

mysql>flush query cache;

Kosongkan - arahan:

mysql>flush query cache;

Yang paling penting

Jangan sekali-kali bekerja dalam pengeluaran dengan tetapan lalai. Ini akan menyebabkan kebanyakan sumber pelayan tidak digunakan. Penalaan MySQL yang betul boleh meningkatkan prestasi pangkalan data beberapa kali. Ini bukan sahaja akan mempercepatkan aplikasi, tetapi juga mengatasi beban yang berat.