[MySQL] Is dit optimaal?

[RSD]

Ik had een vrij hoge load op de server van wel 20 ~30. Op de server draaien weblogs met films. Er komen ongeveer 50.000 unieke bezoekers per dag op alles samen en deze zorgen ongeveer voor 500.000 pageviews. Database en data staan op dezelfde server. Dit is om aan te geven onder welke druk de server staat. Nu gebruik ik alleen MyISAM tabellen. Om de hoge load de baas te zijn, ben ik wat gaan tweaken samen met tuning-primer.sh.

Nu heb ik alles redelijk onder controle weten te krijgen, alleen zo nu en dan zitten er nog pieken in van 8.

Ik heb de volgende my.cnf nu:

[mysqld]
thread_cache_size=60
max_connections=1000
query_cache_size=256M
query_cache_type=1
query_cache_limit=1M
table_cache=6000
key_buffer_size=3072M
#log-slow-queries=slow.log
#long_query_time=10
#log-queries-not-using-indexes
low_priority_updates=1
concurrent_insert=2

Nu vroeg ik me af of dit redelijk goed is voor bovenstaande. Het draait op een:

Dual Xeon Quad 2.5Ghz
8GB RAM
2x 1000GB Hardeschijf raid1

[Voutloos]

Dit klein stukje config zou zomaar een micro-optimalisatie kunnen zijn, aangezien je niet hebt uitgezocht wat voor de meeste load zorgt. Komt het door de data? Door de DB? Hoe groot is de DB? Heb je al naar trage queries / queries zonder goede index gezocht? Etc. etc.

offtopic:
En tenzij je fulltext search gebruikt of enkel logt is innoDB in 99% vd gevallen een betere keuze dan MyISAM.

[ Voor 18% gewijzigd door Voutloos op 24-02-2011 19:28 ]

[GlowMouse]

Als er één config was die optimaal was, zat die wel bij MySQL.

[Verwijderd]

Begin bij je applicatie. Zijn alle queries geoptimaliseerd en maken ze allemaal gebruik van indexes?
Zet dus slow query logging aan en gebruik ook de optie om queries de geen indexes gebruiken te loggen.

Die slow query log hoort niet te groeien bij juist gebruik van databases.

Doet je applicatie ook geen zinloze queries, onnodig veel queries, maakt je applicatie geen gebruik van inefficiënte algoritmes?

Je kunt zelden extreme resutaten behalen met alleen wat performance tweaking van de serversoftware. Binnen het domein van de applicatie zelf kun je meestal tientallen zo niet honderden keren meer optimaliseren.

[NMe]

Je begint zomaar in het wilde weg te optimaliseren zonder precies te weten waar de load vandaan komt. Zoek dat eerst eens uit.

[RobIII]

^ Wat hij zegt. Meten == weten.
Wat jij doet is met een honkbalknuppel in 't donker in een porceleinwinkel rondzwaaien in de hoop die vervelende mug te raken...

[ Voor 19% gewijzigd door RobIII op 24-02-2011 20:37 ]

[RSD]

Met het commando top staat mysql bovenaan, dus ik mag aannemen dat dit voor de hoge load zorgt. Hoeft niet natuurlijk. Ik heb uiteraard ook al de slow log queries bekeken en deze stond in eerste instantie vol met normale queries. Door het verhogen van wat waardes, zoals de max_connection en de key_buffer heb ik deze hier uit weg kunnen halen.

Dmw primer kwam ik aan deze variabelen om een hogere waarde te geven. Echter in huge-my.cnf staan nog meer variabelen die anders gezet zijn. Zou het verstandig zijn om deze ook nog aan te passen?

[RobIII]

RSD schreef op donderdag 24 februari 2011 @ 20:48:
Zou het verstandig zijn om deze ook nog aan te passen?

RobIII schreef op donderdag 24 februari 2011 @ 20:37:
Wat jij doet is met een honkbalknuppel in 't donker in een porceleinwinkel rondzwaaien in de hoop die vervelende mug te raken...

M.a.w. Pak de documentatie er bij en zorg dat je weet waar je mee bezig bent i.p.v. random allerlei waardes hoger te gaan zetten En blijft nog steeds staan: meten == weten; zoals al vaker gezegd.

[Rob]

Als je phpmyadmin draait, kijk dan even naar de status pagina. Daar staat ook wat zinvolle informatie

[RSD]

De weblog bestaat niet uit allemaal ingewikkelde queries. Het is allemaal vrij recht toe recht aan. Daarnaast maak ik volgens mij wel redelijk goed gebruik van indexen. Alleen ik zie wel in de slow.log queries staan die geen gebruik maken van indexen.

Ik heb bij elke post bijvoorbeeld een veld online en deze kan de waarde yes of no bevatten (enum). In de where doe ik dan WHERE online='YES'. Op online heb ik geen index. Dat is ook niet gebruikelijk toch? Als ik nu bijvoorbeeld de laatste 15 posts wil ophalen, doe ik WHERE online='YES' ORDER BY id DESC LIMIT 15. In de slowlog query stata nu dat hij 30 rijen heeft gescand en er 15 heetf gedownload. De query is zelf erg snel. Maar door log-queries-not-using-indexes=1 komt deze query in de slow.log te staan. Dit zou dus betekenen dat hij geen index gebruikt. Als ik bijvooreeld LIMIT 90,15 doe, dan scant hij 210 velden.

Ik vind dit een beetje merkwaardig, maar ook weer niet vreemd, omdat hij moet kijken naar 105 velden en dan x2 in verband met 2 waardes voor online.

Kan iemand mij misschien vertellen of:

low_priority_updates=1
concurrent_insert=2

heel erg schadelijk is. Ik heb al wel gezocht wat het inhoud en wat het doet, maar echt nadelige effecten kan ik niet bedenken. Het gaat erom dat hij de tabellen niet afsluit als er een grote select wordt gedaan, met andere woorden, als er op het moment een select bezig is met veel resultaten, hoeven andere queries niet te wachten. In watvoor sitiaties kan dat nadelig zijn? Ik maak soms gebruik van mysql_insert_id() wordt de inhoud hiervan bijvoorbeeld beinvloed door deze instellingen?

[ Voor 21% gewijzigd door RSD op 25-02-2011 10:46 ]

[GlowMouse]

Als ik nu bijvoorbeeld de laatste 15 posts wil ophalen, doe ik WHERE online='YES' ORDER BY id DESC LIMIT 15. In de slowlog query stata nu dat hij 30 rijen heeft gescand en er 15 heetf gedownload. De query is zelf erg snel. Maar door log-queries-not-using-indexes=1 komt deze query in de slow.log te staan. Dit zou dus betekenen dat hij geen index gebruikt.

Dat is raar, want hij moet sorteren op id. Zonder index moet je daarvoor alle rijen ophalen.

Ik vind dit een beetje merkwaardig, maar ook weer niet vreemd, omdat hij moet kijken naar 105 velden en dan x2 in verband met 2 waardes voor online.

Telt hij velden of rijen?

De dingen die jij noemt, zijn niet van zodanige invloed op de performance dat je er veel winst mee boekt.

[RSD]

Ik bedoel rijen.

[ACM]

RSD schreef op donderdag 24 februari 2011 @ 20:48:
Met het commando top staat mysql bovenaan, dus ik mag aannemen dat dit voor de hoge load zorgt. Hoeft niet natuurlijk.

Dat is overigens doorgaans een iets te simpele aanname. MySQL is een multi-threaded applicatie, waar bijvoorbeeld Apache met PHP meestal een multi-processed omgeving is. In de standaardweergave van top worden de losse MySQL-threads dan allemaal samengevoegd en de Apache-processen los getoond. De gezamelijke belasting van alle apache-processen zou dus alsnog hoger kunnen zijn dan die van MySQL.

Het is natuurlijk wel een sterke indicator als je MySQL continu hoge percentages bovenaan met ziet staan en er zelden of nooit apache-processen bovenaan staan

Wat in deze context ook belangrijk is, is om te checken hoeveel disk-io je doet en of er veel queries zijn die schrijven in tabellen waar je ook uit leest. Het kunnen plaatsen van reacties op berichten kan bijvoorbeeld funest zijn voor de performance van het opvragen van reacties bij een bericht. Myisam staat namelijk het tegelijk lezen en schrijven op tabellen alleen in een zeer beperkt aantal scenario's toe.

Als je heel veel data leest (wat meestal zo is bij dit soort websites) kan het ook nuttig zijn om te voorkomen dat je queries uberhaupt bij MySQL terechtkomen. Een veelgebruikte oplossing daarvoor is memcached, waarbij je dan in je php-code besluit om data (bijvoorbeeld query-resultaten of zelfs complete stukjes html waar meerdere queries voor nodig waren) in memcached op te slaan en daarvandaan te halen.
Voor pure key-value-lookups is memcached weer een stuk sneller en schaalbaarder dan MySQL.

Een ander punt van aandacht is wellicht dat je niet zozeer langzame queries hebt, maar domweg meer dan nodig. 2x dezelfde data ophalen kan best heel snel zijn, maar die 2e keer was alsnog waarschijnlijk overbodig en droeg bij aan een hogere load van de mysql-server.

[Verwijderd]

Zoals hierboven al gezegd word, ga eerst eens na waar je hoge load vandaan komt. En je logt wel slow_queries maar je kunt ook een waarde setten die bepaalt bij hoeveel sec een query gelogt word of niet.

In mijn ogen tenzij je database tabellen miljoenen records bevat hoort je database helemaal niet traag te zijn ook al doe je nutteloze queries op je site. Zolang je een goed geoptimaliseerde database hebt zou je door het verhelpen van dit probleem slechts honderste moeten winnen.

Je zegt dat je queries geoptimaliseert zijn, maar wat is geoptimaliseert? Als je namelijk op 1 pagina 20 queries doet die er allemaal 0.1 sec erover doen ben je nog 2 seconde verder en zal de load inderdaad hoog zijn.

Ik raad je aan om toch beter te kijken naar je queries en deze zover mogelijk uit te optimaliseren. Ga eens na hoeveel queries er gedraait worden per page load? Probeer ook erachter te komen bij welke queries geen indexes gebruikt worden, ook al zijn ze snel want table scans zorgen altijd voor veel meer load als je geen indexes gebruikt.

Toevallig heb ik gisteren over dit onderwerp nog een artikel geblogt, niet dat hier alles in staat maar je zou er eens naar kunnen kijken: http://www.keesschepers.n...ql-performance-verbeteren

[GlowMouse]

Verwijderd schreef op donderdag 03 maart 2011 @ 10:50:
Je zegt dat je queries geoptimaliseert zijn, maar wat is geoptimaliseert? Als je namelijk op 1 pagina 20 queries doet die er allemaal 0.1 sec erover doen ben je nog 2 seconde verder en zal de load inderdaad hoog zijn.

Een query van 0.1s is niet geoptimaliseerd. Op je site wil je er daarvan geen een hebben draaien aan de user-kant, ook al is hij cachebaar. Het aantal records zegt heel erg weinig; bij 100 rijen kun je weinig foutdoen maar je hoeft er geen miljoenen te hebben om in de problemen te komen.
Maar we kunnen hier veel zeggen, TS kan beter een goed boek lezen.

Verwijderd schreef op donderdag 03 maart 2011 @ 10:50:
Zoals hierboven al gezegd word, ga eerst eens na waar je hoge load vandaan komt. En je logt wel
Toevallig heb ik gisteren over dit onderwerp nog een artikel geblogt, niet dat hier alles in staat maar je zou er eens naar kunnen kijken: http://www.keesschepers.n...ql-performance-verbeteren

De belangrijkste speltip die ik je kan meegeven is het in een zo mogelijk stadium uitsluiten van onnodige records voor je resultaat. Een voorbeeld hiervan is bijvoorbeeld bepaalde criteria van je WHERE conditie verplaatsen naar je JOIN conditie waardoor er geen records gejoint worden die sowieso hier al niet aan voldoen.

Bij een inner join maakt het niets uit; bij een left join krijg je andere resultaten.

Het is overigens niet verstandig om aanpassingen in mijn artikel op je live database uit te gaan voeren. In sommige gevallen (ja, helaas) kan je database corrupt raken omdat MySQL de weg is kwijt geraakt.

De memory engine is een zeer goede storage engine voor relatief kleine tabellen waarvan de beschikbaarheid van de gegevens niet cruciaal zijn. Bijvoorbeeld een tabel waarin je bijhoudt wie welk document geopend heeft, of een tabel waarin je gebruikers sessies op slaat. De definitie van je tabel word opgeslagen maar de data word direct geplaatst in het geheugen. Hierdoor zul je gegevens extreem snel kunnen opvragen en muteren.

Bij memory heb je garantie dat hij in het geheugen staat, maar als je db-server voldoende geheugen hebt dan biedt een memory engine geen voordelen.

Je artikel mist heel erg veel info die cruciaal is om goede indices te kiezen. Multi-column indices worden niet eens genoemd.

[ Voor 56% gewijzigd door GlowMouse op 03-03-2011 17:26 ]

[Verwijderd]

GlowMouse schreef op donderdag 03 maart 2011 @ 17:22:
Een query van 0.1s is niet geoptimaliseerd. Op je site wil je er daarvan geen een hebben draaien aan de user-kant, ook al is hij cachebaar. Het aantal records zegt heel erg weinig; bij 100 rijen kun je weinig foutdoen maar je hoeft er geen miljoenen te hebben om in de problemen te komen.
Maar we kunnen hier veel zeggen, TS kan beter een goed boek lezen.

Hier zeggen wij toch hetzelfde? Ik schrijf namelijk niet dat een query van 0.1 load een geoptimaliseerde is.

GlowMouse schreef op donderdag 03 maart 2011 @ 17:22:
Bij een inner join maakt het niets uit; bij een left join krijg je andere resultaten.

Ik kan je een test-case laten zien waarbij de situatie die ik schets het zeker een sneller resultaat opleverde. Dit heeft er hoogstwaarschijnlijk mee te maken dat MySQL er intern anders mee omgaat, en kan het zijn dat het bij een andere query of in jouw geval het niet hielp.

GlowMouse schreef op donderdag 03 maart 2011 @ 17:22:

Wat is daar vaag aan?

GlowMouse schreef op donderdag 03 maart 2011 @ 17:22:
Bij memory heb je garantie dat hij in het geheugen staat, maar als je db-server voldoende geheugen hebt dan biedt een memory engine geen voordelen.

Want? Als je MyIsam kiest dan schrijft MySQL ook data weg in het geheugen? Dat zou nieuw zijn voor mij dan..

GlowMouse schreef op donderdag 03 maart 2011 @ 17:22:
Je artikel mist heel erg veel info die cruciaal is om goede indices te kiezen. Multi-column indices worden niet eens genoemd.

Ik heb met name de meest gebruikte indices behandeld, maar het is een goede tip om hier wat meer onderzoek naar te doen, tegen die tijd zal ik mijn artikel ook eens updaten.

[ Voor 17% gewijzigd door Verwijderd op 07-03-2011 12:21 ]

[Raymond P]

Want? Als je MyIsam kiest dan schrijft MySQL ook data weg in het geheugen? Dat zou nieuw zijn voor mij dan..

Ja hoor. Als je je queries erop schrijft worden ze in de query cache gezet, en die bevindt zich in het geheugen.

Nu kan ik er gruwelijk naast zitten, maar een voordeel van een MEMORY table zou kunnen zijn dat je cache niet gebroken wordt zodra er een row aangepast wordt.

[borft]

overigens zou je ook eens het systeem gebruik van mysql kunnen profilen. Zijn je queries cpu bound, of i/o bound? Je hebt 8 cores met maar 2 disken, dat ljikt me een enigzins kromme verhouding, tenzij je dataset zodanig klein is dat je volledige database in memory past.

Wat betreft het gebruik van indeces, Ik zou zeggen dat je op alle kolommen waarop je queries doet een index maakt, dat scheelt aanzienlijk in de snelheid en de load.

Top is een leuke tool, maar kijk bv ook eens naar iostat om je diskload te bekijken. En zoals eerder al voorgesteld, kijk eens naar de server statistieken van de mysqld, hoe zit het met queries die geen indeces gebruiken, query-cache-hit-ratio ed.

[GlowMouse]

Raymond P schreef op maandag 07 maart 2011 @ 14:44:
[...]


Ja hoor. Als je je queries erop schrijft worden ze in de query cache gezet, en die bevindt zich in het geheugen.

Nu kan ik er gruwelijk naast zitten, maar een voordeel van een MEMORY table zou kunnen zijn dat je cache niet gebroken wordt zodra er een row aangepast wordt.

Query cache is een onding en schakel je uit in de meeste situaties. Data wordt weggeschreven op disk bij MyISAM.
Bij InnoDB ook, maar niet direct. Sequentieel worden wel dingen weggeschreven om ingeval van een crash dingen te bewaren, maar het meeste wordt sterk vertraagd weggeschreven. Als je veel schrijft, kom je bij MEMORY weer in de problemen met locking. InnoDB is dus ook voor dat soort situaties ideaal.

borft schreef op maandag 07 maart 2011 @ 14:53:
Wat betreft het gebruik van indeces, Ik zou zeggen dat je op alle kolommen waarop je queries doet een index maakt, dat scheelt aanzienlijk in de snelheid en de load.

Kan het nog vager?

Ik ben nog in overleg met kees26 over zijn testset. Verder is het heel raar dat een db corrupt raakt als je een tabeldefinitie aanpast. Dat hoort nooit te gebeuren, en als dat een bekende bug was, was het allang opgepakt.

[Raymond P]

GlowMouse schreef op maandag 07 maart 2011 @ 18:51:
[...]

Query cache is een onding en schakel je uit in de meeste situaties. Data wordt weggeschreven op disk bij MyISAM.
Bij InnoDB ook, maar niet direct. Sequentieel worden wel dingen weggeschreven om ingeval van een crash dingen te bewaren, maar het meeste wordt sterk vertraagd weggeschreven. Als je veel schrijft, kom je bij MEMORY weer in de problemen met locking. InnoDB is dus ook voor dat soort situaties ideaal.

Standaard staat query_cache_type bij mij ook op DEMAND.
Uit m'n hoofd doet InnoDB op default settings elke seconde een flush plus na elke transaction commit.
Ik was in de veronderstelling dat Buffer Pool er voornamelijk was voor de reads, maar na je post ben ik gaan twijfelen...

[GlowMouse]

Raymond P schreef op maandag 07 maart 2011 @ 20:42:
[...]


Standaard staat query_cache_type bij mij ook op DEMAND.
Uit m'n hoofd doet InnoDB op default settings elke seconde een flush plus na elke transaction commit.
Ik was in de veronderstelling dat Buffer Pool er voornamelijk was voor de reads, maar na je post ben ik gaan twijfelen...

Ik lees altijd compleet uitschakelen als advies, waarschijnlijk zit ook op het checken of iets in de cache zit een global mutex.
Een flush flusht niet direct de dirty pages. Het voordeel van de buffer pool zit hem er ook in dat dirty pages pas na een tijd worden weggeschreven, bij voorkeur sequentieel. Bij SSD's is er geen voordeel van sequentieel wegschrijven, maar bij hdd's nog wel.

[Verwijderd]

GlowMouse schreef op maandag 07 maart 2011 @ 18:51:
Ik ben nog in overleg met kees26 over zijn testset. Verder is het heel raar dat een db corrupt raakt als je een tabeldefinitie aanpast. Dat hoort nooit te gebeuren, en als dat een bekende bug was, was het allang opgepakt.

Ik zal vandaag de shots vandaag hier op me stick zetten, maar ik voel er zelf ook wel wat voor om een echte testset te maken om mezelf en jou te overtuigen.

Maar wat betreft het corrupt raken van een tabel; neem het niet te letterlijk op mijn blog is het meer een advies om eerst een backup te maken. Bij MyIsam heb ik weleens meegemaakt dat bij het aanpassen van indexen de tabel corrupt raakte, gelukkig was dit destijds makkelijk op te lossen door een REPAIR TABLE te doen.

Waar ik ook benieuwd naar ben heeft de TS (RSD) al wat gehad aan de informatie die in dit onderwerp is vermeld en wat de status is van de isseu?

[Wolfboy]

GlowMouse schreef op maandag 07 maart 2011 @ 18:51:
Ik ben nog in overleg met kees26 over zijn testset. Verder is het heel raar dat een db corrupt raakt als je een tabeldefinitie aanpast. Dat hoort nooit te gebeuren, en als dat een bekende bug was, was het allang opgepakt.

Ach... doe een optimize table met te weinig ruimte in /tmp/ (default locatie, standaard 512MB bij RedHat machines) en MySQL blijft halverwege voor eeuwig wachten tot er diskspace is.

Restart MySQL daarna omdat je het commando niet meer kan aborten en opeens heb je een corrupte tabel