[mysql] performance behouden op koppeltabel

[ieperlingetje]

Het probleem waar ik mee zit is het volgende. Ik hou op mijn website (die ik vooral voor hobbyprojecten gebruik) een hoop top 100 lijsten bij, en dat gaat op dit moment allemaal vlot met over de 5 miljoen records in de database, dus ik vermoed dat ik qua indexen en tabelstructuur en caching goed zit. Alleen wil ik vermijden dat de performantie gaat inzakken (om de 3 maanden komt er miljoen records bij). Hoe is de situatie momenteel:

3 verschillende tabellen (laten we ze voor gemak A,B, en C noemen) die de eigenlijke data bevatten, er zijn dan nog wat gerelateerde tabellen maar die zijn kleiner en bevatten redundante data die voornamelijk 's nachts berekent worden (als een nummer 20000 keer voorkomt wil je niet bij elke hit gaan berekenen hoeveel keer dat nummer in elk land in de top 100, top10 en nummer 1 stond), dus alle zware berekeningen worden 's nachts uitgevoerd wanneer het rustigs is op de server (ik zit op shared hosting, kost me niks en geen onderhoud).

Maar van die 3 tabellen is de koppeltabel 'B' het grootst, die bevat de links tussen de lijsten en de nummers, in vereenvoudigde vorm:

Tabel A	Tabel B	Tabel C
ID	A.ID	ID
waardeA1	C.ID	WaardeC1
WaardeA2	WaardeB1	WaardeC2

Bij grote tabellen wordt aangeraden om te gaan partitioneren, alleen weet ik niet goed hoe ik mijn situatie de koppeltabel ( B ) kan gaan splitsen, aangezien de voorwaarden die gebruikt worden om te selecteren meestal gebeurt op basis van filters op kolommen van tabel A of tabel C, dus hoe ik de koppeltabel ook partitioneer, alle delen van die tabel zullen steeds overlopen moeten worden.

Alleen kan ik mij niet inbeelden dat ik de enigste die met zo een probleem zit. Zijn er nog andere manieren om te zorgen dat bij queries niet de volledige koppeltabel geladen/overlopen moet worden (kort door de bocht natuurlijk, aangezien via indexen gelukkig al een deel van de tabel overgeslagen wordt).

[ Voor 0% gewijzigd door ieperlingetje op 17-01-2014 18:34 . Reden: typo ]

[NMe]

Wat is nu precies je probleem dan? Je geeft aan dat je site snel werkt. Een paar miljoen records is nou ook niet echt een boel. Ben je niet gewoon héél prematuur dingen aan het optimaliseren?

Daarbij: twee tabellen die gelinkt worden met een koppeltabel zijn niet bepaald te versimpelen tenzij je alles denormaliseert. En daardoor wordt het normaal gesproken niet eens merkbaar sneller tenzij je dataset uitzonderlijk groot is of je query zo exotisch dat niet al je indexen goed geraakt kunnen worden door MySQL. Ik geloof niet dat een van beiden hier het geval is. De enige manier waarop je hier dus winst zou kunnen halen is door periodiek data op te schonen en eventueel in week/maandoverzichten te zetten in een andere tabel. Maar ook dat kan prima wachten tot je daadwerkelijk merkt dat je snelheid verliest.

[ieperlingetje]

Wel momenteel merk ik niet echt problemen, maar soms zie ik dat queries er een paar seconden over doen ipv dat ze in een fractie van een seconde uitgevoerd worden, vandaar mijn vrees dat die tabel misschien uit zijn voegen aan het barsten is (het gaat tenslotte wel om shared hosting, ik heb niet een eigen db server tot mijn beschikking). Maar misschien ben ik gewoon te bezorgd.

[RobIII]

ieperlingetje schreef op vrijdag 17 januari 2014 @ 21:32:
maar soms zie ik dat queries er een paar seconden over doen ipv dat ze in een fractie van een seconde uitgevoerd worden,
...
het gaat tenslotte wel om shared hosting

Heb je uitgesloten dat 't aan de query/MySQL ligt of loopt er iemand anders op die bak flink te klieren?

[NMe]

ieperlingetje schreef op vrijdag 17 januari 2014 @ 21:32:
Wel momenteel merk ik niet echt problemen, maar soms zie ik dat queries er een paar seconden over doen ipv dat ze in een fractie van een seconde uitgevoerd worden, vandaar mijn vrees dat die tabel misschien uit zijn voegen aan het barsten is (het gaat tenslotte wel om shared hosting, ik heb niet een eigen db server tot mijn beschikking). Maar misschien ben ik gewoon te bezorgd.

Als dezelfde query het de ene keer razendsnel doet en de andere keer secondenlang blijft hangen zou ik eerst eens op zoek gaan naar externe factoren. Nouja, eerst zou ik EXPLAIN voor die query zetten en kijken wat daaruit komt, maar als dat execution plan niks geks laat zien ligt het probleem waarschijnlijk gewoon elders.

[Gomez12]

ieperlingetje schreef op vrijdag 17 januari 2014 @ 21:32:
Wel momenteel merk ik niet echt problemen, maar soms zie ik dat queries er een paar seconden over doen ipv dat ze in een fractie van een seconde uitgevoerd worden, vandaar mijn vrees dat die tabel misschien uit zijn voegen aan het barsten is (het gaat tenslotte wel om shared hosting, ik heb niet een eigen db server tot mijn beschikking). Maar misschien ben ik gewoon te bezorgd.

Wat bij cheap-ass shared hosting (maar ook wel eens bij de duurdere) voorkomt is simpelweg dat iemand anders op zijn hosting iets heel groot heeft draaien en dat jij daar last van ondervind.

Sharding op een koppeltabel ipv een logische indeling kan wel, maar over het algemeen moet je dan een heleboel shards neerzetten zodat de load een beetje evenwichtig verdeeld wordt.
Sharding met kleine aantallen shards heeft alleen nut als je logisch kan sharden.