Caching ing MySQL

7.1 Caching ing sisih DB

MySQL nggunakake algoritma sing bisa diukur nalika nggarap tabel, saengga MySQL bisa mlaku sanajan nganggo memori sing sithik. Mesthi, kanggo kinerja sing luwih apik, sampeyan kudu RAM luwih.

Kanggo ndeleng setelan saiki, sambung menyang 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)

Pisanan, priksa manawa caching diaktifake. Variabel:

• query_cache_typekudu ON (1) utawa DEMAND (2)
• query_cache_limit- nemtokake ukuran maksimum asil sing bakal njaluk menyang cache
• query_cache_sizebeda karo nul. Nalika nggunakake DEMAND, mung panjaluk sing ngemot arahan sing bakal di-cacheSQL_CACHE;
• query_cache_min_res_unitukuran minimal saka pamblokiran diparengake memori kanggo nyimpen asil query cached. MySQL ora nyimpen cache ing siji cuwilan gedhe saka memori, nanging allocates pamblokiran karo ukuran minimal query_cache_min_res_unit(= 4KB minangka standar) ing dikarepake. Pamblokiran pungkasan kasebut dipotong dadi ukuran data, lan memori sing isih ana dibebasake.

Efek caching yaiku nalika server nampa panjaluk, katon yen hash panjaluk kasebut ana ing cache. Yen hash cocog - server langsung ngasilake asil - tanpa parsing panjalukan, ngoptimalake, lsp. overhead - diiringi mekanisme caching - browsing cache, nulis asil query menyang cache, etc.

Lan yen sampeyan duwe akeh panjalukan cilik ing cache, mula iki bisa nyebabake fragmentasi memori amarga akeh blok gratis. Lan iki, ing siji, nimbulaké cached entri kanggo dibusak amarga lack saka memori. Ing kasus iki, iku ndadekake pangertèn kanggo ngurangi nilai saka query_cache_min_res_unit. Yen umume pitakon sampeyan ngasilake asil gedhe, banjur nambah setelan iki bisa nambah kinerja.

Ayo nyoba ngevaluasi efek kasebut. We katon ing carane cache hit counters ngganti (Qcahe_hits), nomer panjalukan ngumumaké ora bener amarga lack of memori (Qcache_lowmem_prunes), jumlah total panjalukan saka jinis PILIH (lan mung padha cached):

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

Kanggo ngawasi cache query digunakake 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_blocksnuduhake pirang-pirang blok gratis sing ana ing cache (bakal nyuda nalika panyuwunan cache mundhak);
• Qcache_total_blocks- jumlah blok sing dikuwasani;
• Qcache_free_memory- nuduhake free "kasedhiya" memori kanggo caching;
• Qcache_hits- jumlah panjalukan, asil sing dijupuk saka cache, tanpa bener ngakses database;
• Qcache_inserts- jumlah panjalukan sing ditambahake menyang cache;
• Qcache_lowmem_prunes- jumlah panjalukan sing dibusak saka cache amarga kurang memori;
• Qcache_not_cached- jumlah panjalukan sing ora ditulis ing cache amarga nggunakake fungsi manajemen wektu, etc.;
• Qcache_queries_in_cache- jumlah panjalukan sing ana ing cache.

Sampeyan bisa ndeleng jumlah total pitakon PILIH:

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

7.3 Efisiensi cache

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

Nilai saiki bisa dideleng kaya iki:

SHOW VARIABLES LIKE 'query_cache_size';

Maneh, pitakonan muncul: carane milih nilai sing nyukupiquery_cache_size?

Iki bakal mbantu Qcache_lowmem_prunes. Variabel iki nyimpen jumlah panjalukan sing wis dibusak saka cache amarga perlu kanggo cache panjalukan anyar. Sampeyan kudu ngupayakake ukuran cache sing Qcache_lowmem_prunesbakal nambah mung rada. Kanggo nindakake iki, dianjurake kanggo mbandhingake bedane nilai Qcache_lowmem_prunessaben jam lan jumlah panjalukan sing ditampa dening mysql ing jam sing padha.

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 ing 10 menit) * 1.2"

Iki bakal cache panjalukan kanggo 10 menit + menehi tambahan 20% memori kanggo fragmentasi cache lan cadangan caching tambahan

Sampeyan bisa ngetung jumlah lan volume rata-rata respon kanggo panjalukan nggunakake variabel Bytes_sent , mungguh

Dadi query_cache_sizekita nambah nilai, sawise sampeyan kudu menehi perhatian marang nilai Qcache_total_blocks, Qcache_free_blockslan Qcache_queries_in_cache. MySQL nyimpen cache ing blok. Kanggo 1 panjalukan, 2 blok dibutuhake: siji kanggo teks panyuwunan dhewe, sing kapindho kanggo asil.

Yen kita nimbang tabel saka nilaiQcache%

Jumlah total pamblokiran cacheQcache_total_blocks – 28

Panjaluk 6 saiki di-cache, tegese 6 * 2 = 12 blok lagi sibuk

Blok gratis Qcache_free_blocks – 10. Sing luwih nganggur Qcache_free_blocks, luwih gedhe tingkat "fragmentasi" saka cache.

Yen umume pitakon duwe jumlah data sing sithik, mula kudu dikurangi ukuran blok cache minimal query_cache_min_res_unit, yaiku 4 KB minangka standar.

Yen panjaluk paling akeh ngasilake data, mula kudu nambah ukuran blok cache.

Sing utama yaiku entuk nilai minimal Qcache_free_blocks.

Yen counter Qcache_not_cachedgedhe, sampeyan bisa nyoba nambah variabel query_cache_limit- bakal ngidini sampeyan nambah watesan lan cache asil pitakon sing "ora pas".

Variabel konfigurasi ing ngisor iki tanggung jawab kanggo nggunakake cache query:

• query_cache_size- ukuran cache query. query_cache_size = 0mateni panggunaan cache;
• query_cache_limit- ukuran sampel maksimum sing disimpen ing cache;
• query_cache_wlock_invalidate- nemtokake manawa data bakal dijupuk saka cache yen tabel sing padha duweke dikunci kanggo maca.
=

Kanggo ngaktifake cache query mysql, tambahake baris ing ngisor iki menyang my.cnf (Bagian [mysqld]):

query_cache_size = 268435456
query_cache_type =1 
query_cache_limit =1 048576

Lan miwiti maneh layanan.

7.4 Where cache ora digunakake

Ora cached:

• Panyuwunan sakaSQL_NO_CACHE
• Pitakonan sing disiapake(Prepared statements);
• Pitakon sing minangka subkueri saka pitakon njaba;
• Pitakon ing prosedur lan fungsi sing disimpen;
• Pitakonan sing nggunakake fungsi:

  BENCHMARK (),,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, CONNECTION_ID ()_ CONVERT_TZ ()_ CURDATE ()_ CURRENT_DATE ()_ CURRENT_TIME ()_ CURRENT_TIMESTAMP ()_ CURTIME ()_ DATABASE ()_ ENCRYPT ()_ FOUND_ROWS ()_ GET_LOCK ()_ LAST_INSERT_ID ()_ LOAD_FILE ()_ MASTER_POS_WAIT ()_ NOW ()_ RAND ()_ RELEASE_LOCK ()_ SLEEP ()_ SYSDATE ()_ UNIX_TIMESTAMP ()_ USER ()_UUID ();

• Pitakonan nggunakake fungsi sing disimpen, variabel pangguna utawa tabel referensi ing basis data sistem mysql utawa INFORMATION_SCHEMA;
• • Panjaluk ing formulir ing ngisor iki:
• SELECT ... IN SHARE MODE
• SELECT ... FOR UPDATE
• SELECT ... INTO OUTFILE ...
• SELECT ... INTO DUMPFILE ...
• SELECT * FROM ... WHERE autoincrement_col IS NULL
• Pitakonan karo tabel sementara utawa ora nggunakake tabel ing kabeh;
• Panyuwunan ngasilake bebaya (warning);

Sampeyan bisa defragment cache kanthi printah:

mysql>flush query cache;

Clear - printah:

mysql>flush query cache;

Sing paling penting

Aja kerja ing produksi kanthi setelan gawan. Iki bakal nyebabake sebagian besar sumber daya server ora bisa digunakake. Penyetelan MySQL sing tepat bisa nambah kinerja database kaping pirang-pirang. Iki ora mung bakal nyepetake aplikasi, nanging uga ngatasi beban sing abot.