[MySQL] database kiest "ineens" verkeerde indexen

[P.O. Box]

Ik heb een probleem met onze MySQL 4 database. (We kunnen helaas nog niet over op een nieuwere versie van MySQL, maar dat willen we wel gaan doen (en de prioriteit daarvan wordt steeds sneller naar voren geschoven ).

Sinds ongeveer een maand, kiest de engine niet meer de juiste indexen als ik een selectie op de database doe en ik kan niet verklaren waarom. Het speelt niet op 1 enkele tabel, maar meerdere tabellen hebben er last van. Aangezien we een database hebben die heel veel records bevat, is het kiezen van een verkeerde index funest voor de performance.

Als ik op dit probleem google vind ik alleen oplossingen door "use index" te gebruiken. Dat werkt idd wel prima, maar onze code is behoorlijk uitgebreid, dus het is vrij veel werk om overal een use index neer te zetten. Bovendien wil ik liever geen index forceren, omdat ik graag de database de optimale index laat kiezen. Maar nu kiest hij dus niet meer de optimale index.

Voorbeeld:
ik deed tot een maand geleden altijd:

code:

SELECT [fields] FROM xmltable WHERE device = 'AAA' and xmldate = now()

De database pakte dan automatisch xmldate als te gebruiken index. En dat werkt prima. Tegenwoordig loopt hij bij deze query "vast" en moet ik echt doen:

code:

SELECT [fields] FROM xmltable USE INDEX(xmldate) WHERE device = 'AAA' and xmldate = now()

en dan is het gewoon millisecondenwerk om resultaat te krijgen.

Is er ergens een setting of is er een ander mogelijke manier om dit probleem op te lossen.

We hebben overigens 2 weken geleden nog, omdat we hoopten dat dat de problemen zou verhelpen, alle tables geoptimized.

[Creepy]

En wat geeft een explain? En welke indexen heb je op die tabel zitten?

[ACM]

Je zou nog iets als 'analyze xmltable' kunnen uitvoeren om te zien of dat weer de nieuwste gegevens in je plans oplevert. Soms helpt 'optimize xmltable' ook om de files weer lekker naar disk te schrijven (let op eventuele locks die je niet wilt hebben).

[P.O. Box]

oh sorry, ik wist dat ik iets vergeten was

ik heb een index op "device" en ik heb een index op "xmldate" en verder nog een index op "reference" en een primary key "xmlid".

de explain op device = printer geeft het volgende... deze query is WEL snel:

code:

explain
select * from xmltable where device = 'printer' and xmldate = now()


geeft:
+----------+------+----------------+---------+---------+--------+------+-------------+
| table    | type | possible_keys  | key     | key_len | ref    | rows | Extra       |
+----------+------+----------------+---------+---------+--------+------+-------------+
| xmltable | ref  | Device,XmlDate | XmlDate |       4 | const  | 9050 | Using where |
+----------+------+----------------+---------+---------+--------+------+-------------+

de explain op device = web geeft het volgende... deze query is NIET snel:

code:

explain
select * from xmltable where device = 'web' and xmldate = now()


geeft:
+----------+------+----------------+--------+---------+--------+------+-------------+
| table    | type | possible_keys  | key    | key_len | ref    | rows | Extra       |
+----------+------+----------------+--------+---------+--------+------+-------------+
| xmltable | ref  | Device,XmlDate | Device |      11 | const  | 2729 | Using where |
+----------+------+----------------+--------+---------+--------+------+-------------+

ter informatie:
er staan in deze tabel 7000 records met device = web
er staan in deze tabel 2154160 records met device = printer
er staan totaal in deze tabel 14828183 records

[Voutloos]

De cardinality is anders, en daarom pakt hij bij printer Device index niet.

Waarom hij XmlDate niet oppikt is een raadsel, maar dat is iets waar optimize wel soms bij wilt helpen.

Maar goed, alle tijd die je in dit soort tweaks stop is weggegooid, want je kan beter eerst maar je mysql eens het nieuwe millennium in helpen.

[ Voor 77% gewijzigd door Voutloos op 28-12-2010 15:25 ]

[cariolive23]

MySQL 4

Zowel versie 4.0 als 4.1 worden al jaren niet meer ondersteund. Wanneer jouw data zo onbelangrijk is dat je zelfs geen updates wilt uitvoeren, waarom is het dan nu ineens een probleem dat de boel langzaam is? Sinds versie 4.x is er héél erg veel veranderd en verbeterd. Ga updaten en stel ook een onderhoudskalender op voor toekomstig onderhoud: onderhoud is noodzakelijk.

[justmental]

Je plannen laten zien dat hij anders reageert op een specifieke waarde.
De optimizer heeft dus informatie over de cardinaliteit van veschillende waarden.
Als mysql histogrammen heeft zouden die dus verkeerd bepaald kunnen zijn. Of als de cardinatiteit geschat wordt door te prikken of de index structuur te bekijken dan zit daar iets mis.
Als ik zo rondkijk lijkt het erop dat innodb het laatste doet.

Heb je misschien een grote delete gedaan op deze tabel?

[DigiK-oz]

Kent MySQL iets als "update statistics"? Zo ja, doe dat eens voor die tabel, en dan met een full scan (of een hoog percentage bij estimate).

Dat bij device "printer" de index op device niet wordt gekozen lijkt me logisch, omdat iets van 15% van de records dit device hebben. Dus blijft daar alleen de xmldate index over. Bij device "web" heeft slechts een half procent die waarde. Kennelijk weet/denkt de optimizer dat xmldate een minder selectieve index is dan 0,5% (hoeveel verschillende waarden staan er in die kolom?)

Maar dan nog, de index op device zou de records toch ook nog enigzins acceptabel snel moeten kunnen ophoesten...

[P.O. Box]

Bedankt voor de reacties... We hebben vanacht een aantal tabellen geoptimized (hadden we twee weken geleden ook al gedaan) en nu lijkt het weer smooth te werken. Ook bij device=web kiest hij gewoon weer de xmldate als meest logische index (alhoewel het nog ochtend is, dus de xmldate van vandaag bevat nog niet zoveel records)... blijkbaar moeten we tot onze upgrade iedere twee weken even alle tabellen optimizen...

cariolive23 schreef op dinsdag 28 december 2010 @ 16:21:
[...]

Zowel versie 4.0 als 4.1 worden al jaren niet meer ondersteund. Wanneer jouw data zo onbelangrijk is dat je zelfs geen updates wilt uitvoeren, waarom is het dan nu ineens een probleem dat de boel langzaam is? Sinds versie 4.x is er héél erg veel veranderd en verbeterd. Ga updaten en stel ook een onderhoudskalender op voor toekomstig onderhoud: onderhoud is noodzakelijk.

vertel mij wat dat we nu nog met MySQL4 zitten komt door verkeerde keuzes in het verleden en verkeerde keuzes in het heden... Door het verleden is het heel veel werk om een upgrade uit te voeren, omdat ook andere software moet worden aangepast (ondergetekende mag dat gaan doen)... Door het heden is de prioriteit om deze aanpassing te doen steeds naar achteren geschoven omdat de hoge heren binnen het bedrijf denken dat ITérs alleen maar dingen hoeven te bedenken en dat ze dan als vanzelf in een paar minuten geprogrammeerd zijn (en ja, er is hen al talloze keren duidelijk gemaakt dat het niet zo werkt). Het komt vooral neer op steeds weer nieuwe projecten bedenken die geld opleveren (wat natuurlijk goed is), deze projecten een deadline geven die veel te krap is, waardoor het project vol bugs wordt opgeleverd, om vervolgens de bugs hoger in de prioriteitenlijst te krijgen dan de noodzakelijke aanpassingen aan bijv. de database. (zo, ook weer even mijn hart gelucht )