[MYSQL] Simpele query verbruikt alle performance

[Erpenator2]

Hoi Tweakers.

Vandaag hebben wij een website gelanceerd welke vele duizenden bezoekers per dag verzorgt. Wij hebben op onze hoofdpagina een overzicht staan van de laatst toegevoegde items. Maar de relatief query die daarvoor gebruikt wordt trekt de gehele performance van de server weg.

Het gaat om deze query

code:

SELECT s.ID, s.Title,   s.URLTitle, s.Description, s.Logo, m.Name, st.URLTitle as SoftwareTypeUrl,
DATE_FORMAT(s.AddedDate, "%d/%m/%y") AS AddedDate, DATE_FORMAT(s.AddedDate, "%h:%i") AS AddedTime  FROM (   software s,  manufacturer m, softwaretype st    )
WHERE s.SoftwareTypeID = st.ID AND s.ManufacturerID = m.ID  ORDER BY s.AddedDate DESC
LIMIT 8

In de software tabel staan ongeveer 100 records net als in de manufacturer tabel. In software type staan er hooguit 10. Ook wanneer ik left joins gebruik bij deze query is er geen verschil merkbaar.

Ik wil dan ook graag weten waaorm deze query zo veel performance verbruikt en hoe ik deze kan versnellen.

[frickY]

Indexen gebruikt?
Wat voor veld-typen hebben we het over?
Wat is de uitslag van explain? (zet "EXPLAIN " voor je select, en voer de query uit in bijv phpmyadmin)

[Siliakus]

Wat voor DBMS gebruik je? Je kan eventueel even experimenteren met een subquery. <- OK vergeet dat, bedankt voor de tip om in de TT te kijken

Op welke kolommen staan je indexen?

Hoe vaak wordt er iets verkocht waardoor dat overzicht wijzigt? Je kan misschien materialized views gaan gebruiken? Dus elk half uur een update van dat overzicht bijvoorbeeld?

[ Voor 11% gewijzigd door Siliakus op 01-11-2006 21:24 ]

[Wolfboy]

Bij deze query moet je in ieder geval een index op s..SoftwareTypeId, st.ID, s.ManufacturerID, m.ID en s.AddedDate hebben om het zo snel te krijgen. Overigens is het niet eens zo'n hele simpele query aangezien je toch al met meerdere tabellen werkt.

edit:
@Siliakus: zie het 'MYSQL' gedeelte in de topictitel

[ Voor 12% gewijzigd door Wolfboy op 01-11-2006 21:18 ]

[whoami]

Overigens is het niet eens zo'n hele simpele query aangezien je toch al met meerdere tabellen werkt.

Nounou, dit is een simpele query dat voor ieder DBMS een 'walk in the park' zou moeten zijn.
Het probleem schuilt 'm hier idd in indexen.
Als je met Databases bezig bent, zijn dat toch één van de dingen waar je toch minstens eens zou moeten over gehoord hebben, en je zou ook moeten weten wat de voor- en nadelen zijn en waar ze je kunnen helpen.
Ik zou zeggen: lees eens iets over indexen, en leg indexen op de nodige kolommen. (Hetgeen wolfboy zegt, is geen slechte keuze).

Silakiuseventueel even experimenteren met een subquery.

Waar zou een subquery hier enig nut hebben ? Zowiezo zal een gewone inner join (wat hier gebruikt wordt) imho altijd efficienter zijn dan een subquery. (Een beetje DBMS zal beide oplossingen wel naar eenzelfde execution plan optimaliseren, maar ik weet niet hoe mysql hier mee omgaat).

Je kan misschien materialized views gaan gebruiken?

Waarom ? Die query is in die mate eenvoudig, dat dit helemaal niet nodig is.

[ Voor 6% gewijzigd door whoami op 01-11-2006 21:24 ]

[alx]

Frontpagina cache je toch gewoon? Of is het essentieel dat iedere update direct de ms erna bij de volgende bezoeker zichtbaar is? Meestal niet.


edit: beetje traag getypt, enkele suggesties verwijderd

[ Voor 39% gewijzigd door alx op 01-11-2006 21:29 ]

[Siliakus]

whoami:

Het ligt geheel aan het execution plan (en dus de DBMS) en de omvang van de tabellen.
Maar aangezien er hier kolommen uit alle geselecteerde tabellen daadwerkelijk getoond worden zal een join idd sneller zijn.

In ieder geval: ik probeer alternatieven aan te dragen waarmee de TS kan spelen/practicen. Op die manier kan hij zelf besluiten wat de beste oplossing voor hemzelf is

Ook bij mijn suggestie van MVs gaat deze vlieger op. Ik denk dat de TS er meer aan heeft dat hem opties worden aangedragen dan oplossingen. Simple MVs kunnen namelijk wel degelijk verschil uit gaan maken indien de omvang van de tabellen gaat toenemen, dus dan heeft hij daar al eens van gehoord

[ Voor 28% gewijzigd door Siliakus op 01-11-2006 21:37 ]

[Wolfboy]

whoami schreef op woensdag 01 november 2006 @ 21:23:
[...]
Nounou, dit is een simpele query dat voor ieder DBMS een 'walk in the park' zou moeten zijn.
Het probleem schuilt 'm hier idd in indexen.

Uiteraard, maar het verschil tussen een standaard 'SELECT s.ID, s.Title FROM software as s' zal toch _heel_ veel sneller zijn
Maar een erg spannende query is het ook weer niet nee

simulacrum schreef op woensdag 01 november 2006 @ 21:28:
Frontpagina cache je toch gewoon? Of is het essentieel dat iedere update direct de ms erna bij de volgende bezoeker zichtbaar is? Meestal niet.

Leuke workaround maar als het probleem eenvoudig op te lossen is met een paar indexen dan lijkt het me toch een beter idee hoor, cachen kan altijd nog (en doet je DBMS als het goed is zelf ook al)

[Erpenator2]

Ten eerste heel erg bedankt voor de reacties!! Ik heb niet eerder kunnen antwoorden aangezien er nog wat puntjes waren die ook opgelost moesten worden voor de nieuwbakken website.

Ik zal even wat extra info geven op de vragen die hierboven gesteld zijn.

Wanneer ik Explain gebruik voor de query is dit de output

code:

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra  
1 SIMPLE s ALL NULL NULL NULL NULL 107 Using filesort 
1 SIMPLE st eq_ref PRIMARY PRIMARY 4     s.SoftwareTypeID 1   
1 SIMPLE m eq_ref PRIMARY PRIMARY 4     s.ManufacturerID 1

Het lijkt dat op software geen index gezet zijn maar dat is wel zo.
Hieronder een overzicht van de indexen
S: id (107 records) = Software
M: id (119 records) = Manufacturer
ST: id (9 records) = SoftwareType

Ik weet behoorlijk wat van indexen maar hoor graag jullie mening / oplossingen want het is nooit verkeerd om bij te leren Waar moeten volgens jullie dan precies de indexen op gezet worden? Ook hebben we geprobeerd om de tabel de optimaliseren (phpmyadmin optimaliseer tabel)en voor sommige zaken heeft dat geholpen maar nog niet voor deze queries

Cachen van de queries is niet het probleem oplossen maar een workaround voor het probleem. Graag willen we de queries eerst goed laten functioneren en dan alsnog cachen!!

Mocht het nodig zijn om ook de tabellen weer te geven dan hoor ik het wel.

[4VAlien]

Erpenator2 schreef op donderdag 02 november 2006 @ 19:43:

Cachen van de queries is niet het probleem oplossen maar een workaround voor het probleem. Graag willen we de queries eerst goed laten functioneren en dan alsnog cachen!!

Waarom zou je iets cachen als het goed loopt? Maar goed, hoe lang duurt deze query precies, ik denk dat de misschien de code die de webpage opbouwt een foutje maakt. De tabellen zijn gewoon te klein om problemen op te leveren normaal gesproken.

[justmental]

Hoeveel resultaten komen er uit de query zonder limit?
Zo te zien is de filesort het probleem.

Hou er rekening mee dat er gesorteerd wordt op alle rijen (voor de limit).

[ACM]

Erpenator2 schreef op donderdag 02 november 2006 @ 19:43:
Ik weet behoorlijk wat van indexen maar hoor graag jullie mening / oplossingen want het is nooit verkeerd om bij te leren Waar moeten volgens jullie dan precies de indexen op gezet worden? Ook hebben we geprobeerd om de tabel de optimaliseren (phpmyadmin optimaliseer tabel)en voor sommige zaken heeft dat geholpen maar nog niet voor deze queries

Had je het al geprobeerd met een index op s.AddedDate ? Maar verder sluit ik me aan bij 4VAlien, dit ziet er iig niet uit als een standaard geval dat heel lang mag duren op MySQL.

[Erpenator2]

Wanneer ik op s.AddedDate een index zet krijg ik de volgende melding

Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys

Het AddedDate veld is van het type "datetime" maar dat zou toch geen probleem moeten zijn?

Wanneer ik de limit weg haal zijn het zo'n 107 records die terug komen. Ook geen schrikbarend aantal dus.

[Kama]

Just a thought: Gaat de query sneller als je de DATE_FORMAT's eruit gooit?

[frickY]

SQL:

SELECT
   s.ID, s.Title, s.URLTitle, s.Description, s.Logo, m.Name, 
   st.URLTitle as SoftwareTypeUrl, DATE_FORMAT(s.AddedDate, "%d/%m/%y") AS AddedDate, 
   DATE_FORMAT(s.AddedDate, "%h:%i") AS AddedTime
FROM
   software s, manufacturer m, softwaretype st
WHERE 
   s.SoftwareTypeID = st.ID AND s.ManufacturerID = m.ID
ORDER BY 
   s.AddedDate DESC
LIMIT 8

Probeer de alias AddedDate eens anders te noemen. Door de table-prefix zou hij het niet moeten doen, maar wellicht datje ORDER BY nu met de convertereerde datum aan de slag gaat. Dat betekend dus dat hij alle datums converteert, en die gaat sorteren, in plaats van gewoon de index te gebruiken en die te sorteren.

[reddevil]

Kama schreef op vrijdag 03 november 2006 @ 10:12:
Just a thought: Gaat de query sneller als je de DATE_FORMAT's eruit gooit?

Ik zou even

code:

ORDER BY  
   s.AddedDate DESC 
LIMIT 8

eruit halen en kijken hoe de snelheid dan is. Als hij idd dan goed is, zal het aan het sorteren liggen, mogelijk moet je de data ook als isostring o.i.d. opslaan

[Erpenator2]

Wanneer ik de order bij en de limit weghaal is er geen verandering behalve dan dat de server weer vrijwel direct traag word.

[DigiK-oz]

Query zou inderdaad geen probleem mogen opleveren lijkt me. Een paar dingen om even te checken :

Duurt de query ook zo lang als je hem in MySQL zelf uitvoert?
Wordt de query niet per ongeluk meerdere keren aangeroepen bij het laden van de pagina?
Zijn de ID-velden in de diverse tabellen van hetzelfde type?

[reddevil]

probeer ook is select count(s.id) ipv je eigen select.

[ Voor 4% gewijzigd door reddevil op 03-11-2006 12:08 ]

[Erpenator2]

Ik denk dat we het probleem hebben gevonden. Ik kom er nog op terug.

Het lijkt te liggen aan een count statement welke ergens anders op de pagina wordt weergegeven.

Ok het ging om een tweetal queries (niet die hierboven) die een left join uitvoerde op de users tabel die 90.000 records groot was, na deze join te hebben verwijderd gaat het al een speer!
Ook een count query die te vaak werd aangeroepen gaf problemen.

De reden dat de load op de server omhoog ging na het gebruiken van de query hierboven was omdat deze query de weg naar de bottleneck verzorgde.

Ik wil jullie heel erg bedanken voor jullie snelle en goede hulp! We zijn er nog niet maar hebben de grootste problemen gevonden (zo het nu lijkt)

[ Voor 62% gewijzigd door Erpenator2 op 03-11-2006 13:13 ]