MySQL teljesítmény és Query Cache problémák

Mit érdemes tenni, ha a mysql processlistben rengeteg a „Waiting for query cache lock”?

A `Waiting for query cache lock` üzenet a MySQL processlistben azt jelzi, hogy a Query Cache-t használod, és a folyamatok sorban állnak, mert egyszerre csak egy query írhat/érvényesíthet bejegyzést a cache-be.

1. A Query Cache globális lockot használ → sok írás/törlés esetén a cache állandóan érvénytelenítve lesz.
2. Emiatt a párhuzamos lekérdezések blokkolják egymást.

• Kapcsold ki a Query Cache-t:

    query_cache_type = 0
    query_cache_size = 0
    

• Alternatívaként csökkentsd a cache méretét vagy állítsd `DEMAND`-re.
• Modern környezetben érdemes alkalmazás szintű cache-t használni (Redis/Memcached).

Bevált a query_cache_type = DEMAND; alacsony query cache méret mellett.

—

Válasz: Igen, online is kikapcsolható:

  SET GLOBAL query_cache_type = OFF;
  SET GLOBAL query_cache_size = 0;

1. `query_cache_type` dinamikusan állítható.
2. `query_cache_size` csak 0-ra állítható futás közben, visszaállítani új értékre csak 0 után lehet.
3. Tartós kikapcsoláshoz módosítani kell a my.cnf-t és újraindítani.

—

Válasz:

1. Ha `query_cache_size` jelenleg nem 0, először állítsd 0-ra:

    SET GLOBAL query_cache_size = 0;
    

1. Ezután állítsd a kívánt méretre bájtban:

    SET GLOBAL query_cache_size = 134217728; -- 128 MB
    

1. Kapcsolhatod vissza a cache-t:

    SET GLOBAL query_cache_type = ON;
    

—

A terhelés nem csökkent, egy gyakori lekérdezés indexelésével javítottunk a problémán.

—

Lépések:

1. 1 GB → 8 GB
2. Ezzel a gyakran használt adatok és indexek memóriában maradnak.
3. Hit rate ellenőrizhető:

    SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
    Hit rate = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)
    

Példa számítás:

1. read_requests = 1,000,000
2. reads = 5,000
3. Hit rate = 1 - 5000/1,000,000 = 0.995 = 99.5%

—

• Query Cache modern környezetben gyakran hátrányos → kikapcsolás javasolt.
• Kompozit indexek létrehozása jelentősen csökkenti a beolvasott sorok számát.
• Buffer pool növelése javítja a memóriából történő kiszolgálás arányát.
• Ezek kombinációja gyorsabb, stabilabb MySQL működést biztosít.