7.1 Caching aan de DB-kant
MySQL gebruikt zeer schaalbare algoritmen bij het werken met tabellen, zodat MySQL zelfs met kleine hoeveelheden geheugen kan werken. Voor betere prestaties heb je natuurlijk meer RAM nodig.
Maak verbinding met de database om de huidige instellingen te bekijken
#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)
Zorg er eerst voor dat caching is ingeschakeld. Variabel:
query_cache_typemoet AAN (1) of VRAAG (2) zijnquery_cache_limit- bepaalt de maximale grootte van het resultaat dat in de cache terechtkomtquery_cache_sizeanders zijn dan nul. Bij gebruik van DEMAND worden alleen verzoeken die de richtlijn bevatten in de cache opgeslagenSQL_CACHE;query_cache_min_res_unitde minimale grootte van het toegewezen geheugenblok voor het opslaan van de resultaten van de query in de cache. MySQL slaat de cache niet op in één groot stuk geheugen, maar wijstquery_cache_min_res_unitop verzoek blokken toe met een minimale grootte (standaard = 4KB). Het laatste blok wordt afgekapt tot de gegevensgrootte en het resterende geheugen wordt vrijgemaakt.
Het effect van caching is dat wanneer de server een verzoek ontvangt, deze kijkt of de hash van het verzoek zich in de cache bevindt. Als de hash overeenkomt - retourneert de server onmiddellijk het resultaat - zonder het verzoek te ontleden, te optimaliseren, enz. overhead - vergezeld van een caching-mechanisme - door de cache bladeren, het zoekresultaat naar de cache schrijven, enz.
En als u veel kleine verzoeken in de cache heeft, kan dit leiden tot geheugenfragmentatie vanwege een groot aantal vrije blokken. En dit zorgt er op zijn beurt voor dat vermeldingen in de cache worden verwijderd wegens gebrek aan geheugen. In dit geval is het logisch om de waarde van te verlagen query_cache_min_res_unit. Als de meeste van uw zoekopdrachten grote resultaten opleveren, kan het verhogen van deze instelling de prestaties verbeteren.
Laten we proberen het effect te evalueren. We bekijken hoe de hittellers van de cache veranderen (Qcahe_hits), het aantal verzoeken dat ongeldig is verklaard wegens gebrek aan geheugen (Qcache_lowmem_prunes), het totale aantal verzoeken van het SELECT-type (en alleen deze worden in de cache opgeslagen):
#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 Huidige cachestatus
Om de querycache te bewaken wordt gebruikt 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_blockslaat zien hoeveel vrije blokken er in de cache zijn (zal afnemen naarmate verzoeken in de cache toenemen);Qcache_total_blocks— het aantal bezette blokken;Qcache_free_memory- toont het vrije "beschikbare" geheugen voor caching;Qcache_hits- het aantal verzoeken waarvan de resultaten uit de cache zijn gehaald, zonder daadwerkelijk toegang te krijgen tot de database;Qcache_inserts- het aantal verzoeken dat aan de cache is toegevoegd;Qcache_lowmem_prunes- het aantal verzoeken dat uit de cache is verwijderd wegens gebrek aan geheugen;Qcache_not_cached- het aantal verzoeken dat niet naar de cache is geschreven vanwege het gebruik van tijdbeheerfuncties, enz.;Qcache_queries_in_cache- het aantal verzoeken dat zich in de cache bevindt.
U kunt het totale aantal SELECT-query's bekijken:
mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 16719 |
+---------------+-------+
1 row in set (0.00 sec)
7.3 Caching-efficiëntie
“Het wordt aanbevolen om de effectiviteit van de cache te evalueren door de waarde van de variabele te delen Qcache_hits on Qcache_hits + Com_select, aangezien bij het verwerken van een verzoek de teller Qcache_hits toeneemt (als het verzoek wordt verwerkt vanuit de cache) of Com_select (als het verzoek niet in de cache is opgeslagen). Deze methode wordt voorgesteld in "Mysql Performance Optimization" O'reilly
Er is een andere manier online
qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)
Als deze waarde > 0,8 is, wordt 80% van uw verzoeken in de cache opgeslagen, wat een zeer goede indicator is.
Als %de cachehit laag is, moet u de waarde verhogen query_cache_size.
De huidige waarde kan als volgt worden bekeken:
SHOW VARIABLES LIKE 'query_cache_size';
Opnieuw rijst de vraag: hoe een geschikte waarde te kiezenquery_cache_size?
Dit zal helpen Qcache_lowmem_prunes. Deze variabele slaat het aantal verzoeken op dat uit de cache is verwijderd vanwege de noodzaak om nieuwe verzoeken in de cache op te slaan. Het is noodzakelijk om te streven naar een dergelijke cachegrootte waarbij Qcache_lowmem_prunesdeze slechts licht zal toenemen. Om dit te doen, is het aan te raden om het verschil in waarden per uur te vergelijken Qcache_lowmem_prunesmet het aantal verzoeken dat in hetzelfde uur door mysql is ontvangen.
“In de praktijk kan een van de twee formules worden gebruikt om query_cache_size te berekenen:
query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2
of
query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2
query_cache_size = (verkeersvolume in 10 minuten) * 1.2 "Hierdoor worden verzoeken gedurende 10 minuten in de cache geplaatst + wordt 20% extra geheugen toegevoegd voor cachefragmentatie en extra cachereserve
U kunt het aantal en het gemiddelde volume van een antwoord op een verzoek berekenen met behulp van respectievelijk de Bytes_sent- variabelen
En dus query_cache_sizehebben we de waarden verhoogd, waarna je op de waarden moet letten Qcache_total_blocks, Qcache_free_blocksen Qcache_queries_in_cache. MySQL slaat cache op in blokken. Voor 1 verzoek zijn 2 blokken nodig: één voor de verzoektekst zelf, het tweede voor het resultaat.
Als we de tabel beschouwen vanuit de waardeQcache%
Totaal aantal cacheblokkenQcache_total_blocks – 28
Verzoek 6 is nu in de cache geplaatst, wat betekent dat 6 * 2 = 12 blokken bezet zijn
Gratis blokken Qcache_free_blocks – 10. Hoe meer inactiviteit Qcache_free_blocks, hoe groter de mate van "fragmentatie" van de cache.
Als de meeste query's een kleine hoeveelheid resulterende gegevens bevatten, is het de moeite waard om de minimale cacheblokgrootte te verkleinen query_cache_min_res_unit, die standaard 4 kB is.
Als de meeste verzoeken veel gegevens retourneren, is het de moeite waard om de grootte van het cacheblok te vergroten.
Het belangrijkste is om een minimumwaarde te bereiken Qcache_free_blocks.
Als de teller Qcache_not_cachedgroot is, kunt u proberen de variabele te verhogen query_cache_limit- hiermee kunt u de limiet verhogen en de resultaten van query's die "niet passen" cachen.
De volgende configuratievariabelen zijn verantwoordelijk voor het gebruik van de querycache:
query_cache_size— de grootte van de querycache.query_cache_size = 0schakelt cachegebruik uit;query_cache_limit- de grootte van het maximale monster dat in de cache is opgeslagen;query_cache_wlock_invalidate- bepaalt of de gegevens uit de cache worden gehaald als de tabel waartoe ze behoren is vergrendeld voor lezen. =
Om caching van mysql-query's in te schakelen, voegt u de volgende regels toe aan my.cnf (sectie [mysqld]):
query_cache_size = 268435456
query_cache_type =1
query_cache_limit =1 048576
En herstart de service.
7.4 Waar de cache niet wordt gebruikt
Niet gecached:
- Verzoeken van
SQL_NO_CACHE - Voorbereide vragen
(Prepared statements); - Query's die subquery's zijn van de buitenste query;
- Query's binnen opgeslagen procedures en functies;
- Query's die functies gebruiken:
BENCHMARK (),CONNECTION_ID (),CONVERT_TZ (),CURDATE (),CURRENT_DATE (),CURRENT_TIME (),CURRENT_TIMESTAMP (),CURTIME (),DATABASE (),ENCRYPT ()met één argument,FOUND_ROWS (),GET_LOCK (),LAST_INSERT_ID (),LOAD_FILE (),MASTER_POS_WAIT (),NOW (),RAND (),RELEASE_LOCK (),SLEEP (),SYSDATE (),UNIX_TIMESTAMP ()zonder argumenten,USER (),UUID (); - Query's met behulp van opgeslagen functies, gebruikersvariabelen of verwijzende tabellen in mysql- systeemdatabases of INFORMATION_SCHEMA;
- • Verzoeken in de volgende vormen:
SELECT ... IN SHARE MODESELECT ... FOR UPDATESELECT ... INTO OUTFILE ...SELECT ... INTO DUMPFILE ...SELECT * FROM ... WHERE autoincrement_col IS NULL- Query's met tijdelijke tabellen of helemaal geen tabellen gebruiken;
- Verzoeken om waarschuwingen te genereren (waarschuwingen);
U kunt de cache defragmenteren met het commando:
mysql>flush query cache;
Wissen - commando:
mysql>flush query cache;
Het belangrijkste
Werk nooit in productie met standaardinstellingen. Dit zal ertoe leiden dat de meeste bronnen van de server niet worden gebruikt. Correcte MySQL-afstemming kan de databaseprestaties meerdere keren verbeteren. Dit zal niet alleen de applicatie versnellen, maar ook omgaan met een zware belasting.
GO TO FULL VERSION