Hallo,

Ik heb sinds kort een probleem met mijn webapplicatie. Bij mijn huidige host (one.com) werkt het allemaal super snel, maar ik wil die website verplaatsen naar mijn reseller hosting account (versio.nl), zodat ik alles mooi bij elkaar heb. Alleen heb ik een probleem met de MySQL performance van mijn reseller account. Ik ben al 3 dagen bezig het op te lossen, maar ik weet het echt niet meer. Ik heb de oorzaak kunnen vast stellen van het performance verlies, maar een goede oplossing heb ik niet.

Het gaat om de volgende 2 MySQL tabellen:

Code:

CREATE TABLE `artist` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(250) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `name` (`name`),
) ENGINE=MyISAM AUTO_INCREMENT=29119 DEFAULT CHARSET=utf8 AUTO_INCREMENT=29119 ;

CREATE TABLE `song` (
  `id` int(11) NOT NULL auto_increment,
  `artistid` int(11) NOT NULL,
  `title` varchar(300) NOT NULL,
  `text` longtext NOT NULL,
  `youtubeID` varchar(11) NOT NULL,
  `views` int(11) NOT NULL,
  `reports` int(11) NOT NULL default '0',
  `movieChecked` enum('true','false') NOT NULL default 'false',
  `lyricsChecked` enum('true','false') NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `artistid` (`artistid`),
  KEY `title` (`title`),
) ENGINE=MyISAM AUTO_INCREMENT=484694 DEFAULT CHARSET=utf8 AUTO_INCREMENT=484694 ;

Artist bestaat uit ongeveer 28.000 rijen en song uit ongeveer 450.000 rijen.

De volgende SQL query is het probleem:

Code:

SELECT a.name AS artist, 
s.id AS id, 
s.title AS title, 
s.views AS views,
s.movieChecked AS checked
FROM song s, 
artist a
WHERE a.id = s.artistid
ORDER BY a.name ASC, s.title ASC
LIMIT 0 , 50

Nu ben ik erachter gekomen dat het verlies komt door de ORDER BY, als ik hem weg haal, duurt de query ongeveer 0,08 seconden. Met de ORDER BY doet MySQL er ongeveer 2 minuten over.

Nu ben ik ook al met indexen bezig geweest, maar zonder enig resultaat.

Wie o wie kan me helpen?

Heb je er al een EXPLAIN query op losgelaten?

EXPLAIN geeft het volgende:

Code:

id select_type  table  type   possible_keys   key    key_len   ref      rows    Extra                                          
1     SIMPLE     a     ALL     PRIMARY     NULL     NULL     NULL     28774     Using temporary; Using filesort                        
1     SIMPLE     s     ref     artistid     artistid     4     zingmee.a.id     16

Het probleem is simpel te verklaren: Mysql gaat eerst alle rijen ordenen en dat kost erg veel tijd. De oplossing is dat je deze query niet iedere keer gaat uitvoeren maar dat je gebruik gaat maken van caching. Ik kan dit waarschijnlijk wel tegen betaling voor je doen, laat het me maar weten.

Je ordert ook 2 waardes, Als je nou s.title ASC eens weg haalt en gewoon 1 waarde ordert.

Wat je ziet bij EXPLAIN is dat je geen key gebruikt bij table a. Ka je eens een EXPLAIN geven zonder de ORDER BY?

De EXPLAIN zonder de ORDER BY:

Code:

id select_type   table  type  possible_keys key      key_len ref   rows     Extra                                          
1     SIMPLE     a     ALL     PRIMARY     NULL     NULL     NULL     28774                              
1     SIMPLE     s     ref     artistid     artistid     4     zingmee.a.id     16

Probeer anders eens om de tabel te optimaliseren (gewoon in phpmyadmin), misschien maakt het wat uit?

Origineel gepost door Timo Maas

Probeer anders eens om de tabel te optimaliseren (gewoon in phpmyadmin), misschien maakt het wat uit?

Dan moet hij alsnog alles ordenen en dergelijke. Caching is de uitkomst hier om de functionaliteit te kunnen behouden.

Helaas, OPTIMIZE TABLE en ANALYZE table halen niets uit. Naja, niet wat snelheidswinst betreft.

Caching zou inderdaad een oplossing kunnen zijn. Maar wat ik vreemd vind is dat het bij mijn huidige host (One.com) wel allemaal super snel werkt en dat ZONDER enige indexen (op de PRIMARY index na).

Zou dat aan de configuratie van hun servers kunnen liggen?

Origineel gepost door Wouter Carabain

Helaas, OPTIMIZE TABLE en ANALYZE table halen niets uit. Naja, niet wat snelheidswinst betreft.

Caching zou inderdaad een oplossing kunnen zijn. Maar wat ik vreemd vind is dat het bij mijn huidige host (One.com) wel allemaal super snel werkt en dat ZONDER enige indexen (op de PRIMARY index na).

Zou dat aan de configuratie van hun servers kunnen liggen?

Dat kan zeker aan de configuratie liggen, maar ik vermoed dat one.com aparte database servers heeft met 15k schijven en veel geheugen. Ik weet dit niet zeker, want het kan ook zijn dat je host mysql verkeerd ingesteld heeft. Maar dan is het probleem er alsnog: de query is gewoon traag. Er zijn zoveel records die afgewerkt moeten worden waardoor het erg lang duurt.

Ik heb het al bij meer klanten gezien die ook gekozen hebben voor caching en het bleek dat in sommige stukjes script gewoon een slechte query zit en dat deze vervangen moet worden of gecached indien het essentieel is en een andere oplossing niet mogelijk is.

@ Wouter Carabain
Ik heb ook enkele sites bij Versio ondergebracht, en ben erg tevreden over hun service. Ik zou dit probleem ook eens bij hun aankaarten.

Origineel gepost door Peter R

@ Wouter Carabain
Ik heb ook enkele sites bij Versio ondergebracht, en ben erg tevreden over hun service. Ik zou dit probleem ook eens bij hun aankaarten.

Zie net pas dat het versio.nl is en dan zeg ik gelijk dat het niet zo gek is. Ze hebben oa Core 2 Duo servers (E2200) met 500GB schijven er in en ze verkopen elke GB die ze op hun server hebben. Daarnaast is voor de resellers overselling toegestaan dus....
Hun servers zijn echt niet geschikt voor complexe queries als deze...

Aan de explain te zien is 2 minuten voor deze helemaal niet zo complexe query idioot lang, zelfs als de indexen niet worden gebruikt. Is er niet iets anders aan de hand? Kun je tijdens het uitvoeren van die query de MySQL processlist bekijken?

Origineel gepost door Maarten vL

Aan de explain te zien is 2 minuten voor deze helemaal niet zo complexe query idioot lang, zelfs als de indexen niet worden gebruikt. Is er niet iets anders aan de hand? Kun je tijdens het uitvoeren van die query de MySQL processlist bekijken?

Volgens mij is het probleem dat er zoveel regels zijn.