MYSQL - lage Cache Hit Rate en veel Query Cache Prunes.

[ImNotnoa]

Mijn vraag

Ik draai een webshop met ca 20k producten, omdat veel queries die op de database uitgevoerd worden vaker voorkomen (bladeren door categorieën etc) wil ik de MYSQL performance verbeteren door aanpassingen in my.ini.

Via verschillende fora heb ik al een aantal wijzigingen aangebracht in de my.ini zo heb ik onder andere de innodb cache size verhoogd naar 2gb. Ik weet dat er veel queries regelmatig voorkomen en dus gecached zouden moeten worden maar dit lijkt niet te gebeuren.(De innodb cache is maar 18MB) Zie de mysql tuner resultaten:



Zou iemand mij een beetje op weg kunnen helpen met de parameters om zo te zorgen dat er meer veel voorkomende queries gecached worden?

Relevante software en hardware die ik gebruik

• Server 2012 R2
• IIS 8.5
• MySQL 5.7.12

Wat ik al gevonden of geprobeerd heb

Ik heb al verschillende aanbevelingen voor my.ini gevolgd maar ik blijf een zeer lage hit rate en zeer veel prunes in de query cache houden

My.ini:

code:

datadir=C:/ProgramData/MySQL/MySQL Server 5.7\Data

character-set-server=utf8

default-storage-engine=INNODB

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

max_connections=151

query_cache_size=25M

# AANGEPAST VAN 0 naar 25M 
table_open_cache=2000

tmp_table_size=80M

thread_cache_size=10

myisam_max_sort_file_size=100G

myisam_sort_buffer_size=151M

key_buffer_size=8M

read_buffer_size=64K
read_rnd_buffer_size=256K

#*** INNODB Specific options ***


innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=8M

innodb_buffer_pool_size=2G

# Aangepast van 8M naar 2G

innodb_log_file_size=512M

innodb_thread_concurrency=8

innodb_autoextend_increment=64

innodb_buffer_pool_instances=8

innodb_concurrency_tickets=5000

innodb_old_blocks_time=1000

innodb_open_files=300

innodb_stats_on_metadata=0

innodb_file_per_table=1

innodb_checksum_algorithm=0

back_log=80

flush_time=0

join_buffer_size=256K

max_allowed_packet=4M

max_connect_errors=100

open_files_limit=4161

query_cache_type=1

# aangepast van 0 naar 1

sort_buffer_size=256K

table_definition_cache=1400

binlog_row_event_max_size=8K

sync_master_info=10000

sync_relay_log=10000

sync_relay_log_info=10000

...

[ Voor 87% gewijzigd door ImNotnoa op 16-01-2018 15:07 . Reden: Edit: My.ini wat ingekort ]

[Gropah]

Wat geeft de DB zelf aan als je

code:

SELECT @@innodb_buffer_pool_size/1024/1024/1024

runt?

[ImNotnoa]

Gropah schreef op dinsdag 16 januari 2018 @ 14:51:
Wat geeft de DB zelf aan als je

code:

SELECT @@innodb_buffer_pool_size/1024/1024/1024

runt?

Dat geeft deze uitvoer

[ Voor 3% gewijzigd door ImNotnoa op 16-01-2018 15:00 ]

[Morax]

Let op dat de innodb_buffer_pool_size niets te maken heeft met de query_cache! Het is overigens niet verkeerd om die hoger te zetten mits je tabellen daadwerkelijk innodb tabellen zijn en je genoeg intern geheugen daarvoor hebt.

Je query_cache_size is momenteel echter maar 25M. Kijk hiervoor naar de setting query_cache_size.

[ImNotnoa]

Morax schreef op dinsdag 16 januari 2018 @ 15:21:
Let op dat de innodb_buffer_pool_size niets te maken heeft met de query_cache! Het is overigens niet verkeerd om die hoger te zetten mits je tabellen daadwerkelijk innodb tabellen zijn en je genoeg intern geheugen daarvoor hebt.

Je query_cache_size is momenteel echter maar 25M. Kijk hiervoor naar de setting query_cache_size.

Bedankt voor het meedenken.

Ik begrijp dat je niet op de bit nauwkeurig kunt zeggen wat een geschikte query_cache_size is, maar in welke richting moet ik ongeveer denken , verdubbelen naar 50M of mag het bijvoorbeeld wel 1G zijn ?


De tabellen zijn wel innodb

code:

mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.01 sec)

[ Voor 16% gewijzigd door ImNotnoa op 16-01-2018 15:27 ]

[Morax]

Imnoa schreef op dinsdag 16 januari 2018 @ 15:22:
[...]


Bedankt voor het meedenken.

Ik begrijp dat je niet op de bit nauwkeurig kunt zeggen wat een geschikte query_cache_size is, maar in welke richting moet ik ongeveer denken , verdubbelen naar 50M of mag het bijvoorbeeld wel 1G zijn ?

Dat hangt enorm af van wat je precies doet. Doe je veel leesacties en weinig schrijfacties dan kan een grotere query_cache je helpen. Schrijf je echter veel weg in die tabel zal je veel cache_misses hebben.

Dan is nog de vraag hoe groot de gemiddelde resultset is. Als je gemiddelde resultset 5mb is, dan is 25m cache size natuurlijk niet zo veel

Let er ook op dat de query cache ook tegen je kan werken! De cache is namelijk single threaded, en mysql lockt bij elke query de cache om te kijken of het erin staat. Alle andere queries moeten dan wachten omdat mysql eerst de cache moet checken. Dit gaat overigens pas spelen als je echt grote hoeveelheden SELECT queries doet.

In jouw geval zou ik de cache eens op 128MB zetten en kijken naar de metrics of het er beter van wordt

[ImNotnoa]

Morax schreef op dinsdag 16 januari 2018 @ 15:28:
[...]


Dat hangt enorm af van wat je precies doet. Doe je veel leesacties en weinig schrijfacties dan kan een grotere query_cache je helpen. Schrijf je echter veel weg in die tabel zal je veel cache_misses hebben.

Dan is nog de vraag hoe groot de gemiddelde resultset is. Als je gemiddelde resultset 5mb is, dan is 25m cache size natuurlijk niet zo veel

Let er ook op dat de query cache ook tegen je kan werken! De cache is namelijk single threaded, en mysql lockt bij elke query de cache om te kijken of het erin staat. Alle andere queries moeten dan wachten omdat mysql eerst de cache moet checken. Dit gaat overigens pas spelen als je echt grote hoeveelheden SELECT queries doet.

In jouw geval zou ik de cache eens op 128MB zetten en kijken naar de metrics of het er beter van wordt

In ons geval is het vooral Read, Write komt natuurlijk ook voor maar ik schat dat 95% Read is.

Edit: R 93% /W 7% gemiddeld. Dus dat klopt aardig

Ik ga het eens aanpassen! Bedankt!

[DJMaze]

https://github.com/major/MySQLTuner-perl

[RobIII]

Imnoa schreef op dinsdag 16 januari 2018 @ 15:22:
De tabellen zijn wel innodb

code:

mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.01 sec)

offtopic:
Nee Je default storage engine is InnoDB, je tabellen kunnen nog steeds MyISAM (of iets anders) zijn

[Gomez12]

Wat wil je nu concreet omlaag/omhoog hebben?

In het screenshot wat je geeft zie ik toch vrij goede getallen qua instellingen van my.ini (laagste lijkt 94% hit rate te zijn), je moet voornamelijk je query's aanpassen zodat je sorts niet meer via temp-tabellen lopen, je joins via indexen lopen etc. etc.

[BCC]

Heb je überhaupt indexen? Wat zegt je slow query log?

[Feanathiel]

Gomez12 schreef op dinsdag 16 januari 2018 @ 19:48:
Wat wil je nu concreet omlaag/omhoog hebben?

In het screenshot wat je geeft zie ik toch vrij goede getallen qua instellingen van my.ini (laagste lijkt 94% hit rate te zijn), je moet voornamelijk je query's aanpassen zodat je sorts niet meer via temp-tabellen lopen, je joins via indexen lopen etc. etc.

+1. Kijk ook even of je niet veel dynamische queries hebt. Dus niet strings aan elkaar aan het flexen bent om een query te maken, of (slechte) frameworks die queries voor je aan het maken zijn (ORM). Als je voor elke categorie of product een nieuwe query krijgt (SELECT things FROM producten WHERE id = 4, SELECT things FROM producten WHERE id = 5, of statements met 'IN' erin), die worden eenmalig gebruikt en zijn kandidaat om (direct) getrashed te worden. -> Parametized queries.

Server instellingen wijzigen heeft zijn legitieme redenen, maar kijk eerst in de applicatie wat daar gebeurd. Vaak lost het aanpassen van instellingen het probleem op, maar ligt er een ander probleem ten grondslag. Daarmee stel je het eigenlijk alleen maar uit.

[ Voor 14% gewijzigd door Feanathiel op 16-01-2018 20:05 ]

[Banpei]

De cache prunes in de mysql tuner output zijn het gevolg van inserts, die daardoor de bestaande query cache op die tabel invalidaten. Persoonlijk raad ik meestal af om query cache aan te zetten bij MySQL: alleen als je heel weinig inserts doet heeft het werkelijk nut. En in die gevallen heeft het vaak meer nut om andere caching methodes te gebruiken, zoals bijvoorbeeld memcache of redis.
In het geval hier heeft het dus weldegelijk nut: er is een cache ratio van 94%, dus daar hoef je weinig aan te doen.

Wat je beter kunt doen is je slow query log aan te zetten (file on disk of in performance schema) en deze te analyseren. Daar zijn bestaande tools voor, zoals pt-query-digest. Vaak kun je daar mee uitvogelen welke indexes je (nog) moet zetten.

[Olaf van der Spek]

Imnoa schreef op dinsdag 16 januari 2018 @ 15:22:
Ik begrijp dat je niet op de bit nauwkeurig kunt zeggen wat een geschikte query_cache_size is, maar in welke richting moet ik ongeveer denken , verdubbelen naar 50M of mag het bijvoorbeeld wel 1G zijn ?

Query cache size moet gewoon 0 zijn.

Denk dat je beter een stapje terug kunt zetten en kunt analyseren waarom performance lager is dan gewenst.
Welke queries kosten hoeveel tijd.. gebruik explain om te kijken waarom ze lang duren en los dan gericht dat probleem op.

[Hydra]

Heb je uberhaupt wel gekeken of caching een oplossing is? Volgens mij is dat vooral een gok. Voordat je in dit soort dingen duikt, ga je normaliter eerst een grondige analyse van het gebruik doen. Dus hoevaak worden exact dezelfde zaken tevoorschijn gehaald? Hoe snel is een cache weer invalid? Als je het in MySQL doet, kan het als het goed is ook in een andere laag, bijvoorbeeld je webapp.

[Gomez12]

Olaf van der Spek schreef op donderdag 25 januari 2018 @ 09:57:
[...]

Query cache size moet gewoon 0 zijn.

Ben jij zo iemand die ook alle FileSystems-Caching uit gaat zitten zetten enz.
Die ook cpu's koopt met zo weinig L1 en L2-Caching etc.

Ik bedoel waarom zou je gewoon gebruik maken van tried and tested technology

[Olaf van der Spek]

Gomez12 schreef op donderdag 25 januari 2018 @ 11:47:
[...]

Ben jij zo iemand die ook alle FileSystems-Caching uit gaat zitten zetten enz.
Die ook cpu's koopt met zo weinig L1 en L2-Caching etc.

Ik bedoel waarom zou je gewoon gebruik maken van tried and tested technology

https://mysqlserverteam.c...port-for-the-query-cache/

Although MySQL Query Cache was meant to improve performance, it has serious scalability issues and it can easily become a severe bottleneck.

[ Voor 15% gewijzigd door Olaf van der Spek op 25-01-2018 12:36 ]

[ImNotnoa]

Ik heb de laatste tijd wat lopen pielen adhv jullie aanwijzigen. o.a. de query cache uitgeschakeld en de perfomance lijkt een stuk beter

Dank allen!