[MySQL] Problemen met grote database

[TangLeFuzZ]

Hey lui,

onze site (www.spamvrij.nl) heeft een vrij grote database, en deze begint ook nog eens steeds harder te groeien.
We hebben de laatste tijd steeds meer problemen met de database (momenten dat hij heel erg traag is, of helemaal plat ligt). We hebben geen precies idee waar het aan kan liggen, maar we hadden er geen last van toen hij nog een stuk kleiner was.
Ik wil jullie om advies vragen over grote MySQL databases, omdat we daar nog niet echt ervaring mee hebben en we er nu steeds meer mee te maken krijgen.
Als er iets irritant is, dan is dat wel je site plat hebben liggen door het aantal bezoekers dat je hebt

Misschien een rare vraagstelling zo, maar ik weet niet echt hoe ik het anders zou moeten vragen.... elk advies is in ieder geval welkom

[ Voor 5% gewijzigd door curry684 op 20-08-2003 23:18 ]

[gorgi_19]

TangLeFuzZ schreef op 20 August 2003 @ 20:14:
Hey lui,

onze site http://www.eensite.nl heeft een vrij grote database, en deze begint ook nog eens steeds harder te groeien.

Definieer groot. Een grote database begint bij mij pas vanaf 2 GB.

We hebben de laatste tijd steeds meer problemen met de database (momenten dat hij heel erg traag is, of helemaal plat ligt). We hebben geen precies idee waar het aan kan liggen, maar we hadden er geen last van toen hij nog een stuk kleiner was.

Hoe staan je indexen? Wat wordt er gedaan met caching? Welke queries doen er lang over?

[ Voor 10% gewijzigd door gorgi_19 op 20-08-2003 23:37 ]

[ripexx]

Ten eerste wat is groot, dit namelijk relatief, hebben we het hier over 10, 100 MB's of GB's?

Veel problemen komen voort uit of slechte db ontwerp waardoor er veel (onnodige) query;s verwerk moeten worden, of geen of slecht gebruik van indexen. Verder kan table locking voor problemen zorgen. Zodra je insert, update of delete query doet wordt de betreffende table gelockt. Dit is op te lossen door gebruik te maken Van InnoDB tables welke doen aan rowlocking ipv table locking. Verder kan je ook je db server tunen voor bepaalde proformance.

[curry684]

Allereerst heb ik de URL van je site verwijderd, die voegt niets aan je vraag toe en wordt dus beschouwd als spam. Daarnaast mis ik iets heel belangrijks in je openingspost: een vraag Je vraagt om advies met een grote database, en nu moeten wij met de grote glazen bol als vanouds gaan zitten raden wat je allemaal aan software draait, hoeveel tabellen/velden/rijen in de DB zitten etc. etc. etc. Wat je verder wilt doen en waar je dus ons advies bij nodig hebt blijft me ook onduidelijk.

Graag zsm een specifieke vraag met specifieke informatie opstellen dus of er komt een slotje aangevlogen

[whoami]

Zoals reeds eerder gezegd zijn veel performance-problemen mbt databases te wijten aan een slecht datamodel.
Daarnaast kan het zijn dat je teveel of te weinig indexen hebt, of dat je indexen niet op de goeie plaats (columns) staan, en dus niet gebruikt worden.
Daarnaast kan het zijn dat je DB - server gewoon te licht is, echter, ik denk dat de problemen eerder met de eerste 2 puntjes te maken zullen hebben.

[ACM]

Een additionele tip is dan nog om naar InnoDB datafiles te kijken, ipv MyISAM. De laatste doet nogal aan erg lompe locking, wat de database erg kan vetragen als er veel wijzigingen en selects door elkaar lopen.

[TangLeFuzZ]

ACM schreef op 21 augustus 2003 @ 00:01:
Een additionele tip is dan nog om naar InnoDB datafiles te kijken, ipv MyISAM. De laatste doet nogal aan erg lompe locking, wat de database erg kan vetragen als er veel wijzigingen en selects door elkaar lopen.

In de ochtend duren inserts echt gigaaaaaaaantisch lang. Ik dacht laatst dus ook dat InnoDB wel slim zou zijn om de reden die jij al aan geeft.
Ik heb toen een nieuwe postings tabel gemaakt (innodb), maar telkens als ik die innodb tabel in ging nadat hij gevuld was werd de hele server gigantisch traag
Ook gaf hij als ik in phpmyadmin keek, de ene keer bijv. 402.342 postings aan en de andere keer 534.237... best bizar, weet iemand waar dat door kan komen?

[ripexx]

Kom eens met duidelijk cijfers, behalve roepen dat het traag is heb ik nog geen echte gegevens gezien. Welke versie gebruik je hoe groot is je dataset. Welke indexen gebruikje en hoe gebruikje deze. Is je datamodel wel correct of kan je nog dingen aanpassen. Hoeveel queries krijg je gemiddeld te verwerken en wat is je hardware.

Verder een hele late kick

[cavey]

Heb je eigenlijk al de performance tuning en optimalisatie tips van http://www.mysql.com bekeken en uitgevoerd wat van toepassing is op jouw database?

MySQL Optimisation

[curry684]

Ik weet niet hoe MySQL ermee omgaat, maar SQL Server legt default een clustered index op alle primary keys indien aangemaakt via Enterprise Manager: en dat is een ramp voor de performance als je meer INSERTs dan SELECTs doet zogezegd. Je zal dus zoals ripexx ook al aangeeft echt even moeten uitspitten welke indexen je waarop hebt liggen, performancetrubbels op INSERTs zijn 9 van de 10 keer foute indexes, en die tiende keer een stom datamodel. We gaan dus maar even uit van het eerste

[ACM]

TangLeFuzZ schreef op 17 maart 2004 @ 08:32:
In de ochtend duren inserts echt gigaaaaaaaantisch lang. Ik dacht laatst dus ook dat InnoDB wel slim zou zijn om de reden die jij al aan geeft.

Komt dat niet gewoon omdat de boel niet meer in je cache zit en het daarom opnieuw in de cache moet raken?

Ik heb toen een nieuwe postings tabel gemaakt (innodb), maar telkens als ik die innodb tabel in ging nadat hij gevuld was werd de hele server gigantisch traag

"Tabel in ging", je bedoelt vast dat je met phpmyadmin de boel bekijkt.
Phpmyadmin heeft een van de meest stomme dingen in de code verwerkt die je maar met een beetje fatsoenlijke database niet moet doen!

't Doet een count(*) op je records per tabel! Myisam houdt het aantal records bij in een veldje en is er daardoor zeer snel mee, maar innodb kan dat niet bijhouden omdat door de support van transacties gewoon elke transactie een ander getal kan krijgen. (als transactie A alle records verwijderd en dan gaat tellen komt ie op 0, als tegelijkertijd transactie B gaat tellen ziet ie zo'n beetje alle records nog...) En dus duurt dat met een grote tabel erg lang om te tellen, dat is het eerste wat wij bij een nieuwe release van phpmyadmin weer uit de code slopen voor we het inzetten
't Wordt iig gebruikt voor de muistips in de linkerframe, mocht je willen zoeken waar het o.a. door komt.

Naast die count(*) (kan zijn dat ze dat er ondertussen eindelijk uit gesloopt hebben) duurt het opvragen van de tabelinformatie van innodbtabellen vrij lang, hij probeert een goede schatting van het aantal records te maken en ik vermoed dat het daardoor vrij lang duurt.

Ook gaf hij als ik in phpmyadmin keek, de ene keer bijv. 402.342 postings aan en de andere keer 534.237... best bizar, weet iemand waar dat door kan komen?

Dat heeft dus met die schatting te maken, trek je er niks van aan

[TangLeFuzZ]

De database van nu heeft heel erg veel oude tabellen, sommige 4 jaar oud, toen begon ik net met mysql te werken.
Die tabellen zijn in de loop van tijd aangevuld met allerlei velden en zooi, ook indexen.

Ik ga de komende tijd bezig met een compleet nieuwe database voor m'n site, want er zit teveel zooi in de huidige, veel velden worden ook niet meer gebruikt en ik heb bij veel tabellen in het begin nergens bij nagedacht.
Zodra ik die nieuwe af heb zal ik zoveel mogelijk hier posten in de hoop dat er een paar mensen naar willen kijken

komakeef schreef op 17 maart 2004 @ 09:43:
Heb je eigenlijk al de performance tuning en optimalisatie tips van http://www.mysql.com bekeken en uitgevoerd wat van toepassing is op jouw database?

MySQL Optimisation

Heb vanmorgen toevallig dat hele stuk uitgeprint en doorgenomen toen ik in de trein zat, ben achter erg veel dingen gekomen die ik nog niet wist, ga proberen overal rekening mee te houden met het nieuwe model.
De db config is ook nog totaal niet goed ingesteld, zoals bijv. de table-cache.
Ik kwam in een ander topic een oude config van Femme tegen, denk dat ik daar ook vrij veel aan heb.

Heb ook alle queries uit m'n scripts zojuist in textfiles gezet die ik ook ga printen om te kijken wat er aan de queries zelf beter kan.

@ ACM: dat bedoelde ik inderdaad, en daar zal het dan ook inderdaad wel door komen, bedankt voor de uitleg
Ik wist al wel dat InnoDB daar erg traag mee was, ging er alleen niet vanuit dat phpmyadmin het niet 'even anders' had gedaan voor InnoDB tabeltypen.

Nog even een vraag tussendoor; het zal wel niet, maar is er misschien een mogelijkheid om te checken welke velden van een tabel niet tot erg weinig gebruikt zijn in een bepaalde tijd?
M'n gebruikers tabel bevat namelijk veel teveel velden, na flinke aanpassingen een paar jaar geleden heb ik oude velden niet verwijderd, en nu ik een nieuwe database ga maken heb ik dus waarschijnlijk de helft niet meer nodig.
De enige mogelijkheid zal wel zijn om in m'n queries alle velden te checken en te noteren, maar je weet maar nooit.......

[ACM]

Dat is inderdaad de beste oplossing, er is vast wel wat aan te automatiseren (alle queries loggen en die log analyseren), maar dit lijkt me handiger

[TangLeFuzZ]

Ok, doe ik dat

Trouwens, hoe verander ik een set-variable in mysql?

Ik dacht het zonet gevonden te hebben, dacht dat het als volgt moest:
./mysqladmin --password --set-variable=max_connections=600

Maar dan krijg ik de melding dat max_connections een 'unknown variable' is...

Edit: ben er al achter dat je dit met het opstarten van mysqld moet doen, maar als ik mysqladmin shutdown uitvoer en daarna geen foutmelding krijg, zegt hij bij het starten van mysqld_safe nog steeds 'a mysqld process already exists'

[ Voor 31% gewijzigd door TangLeFuzZ op 17-03-2004 17:22 ]

[cavey]

hmm

kan zijn dat er nog een mysql.pid ergens in een /var directory staat ...

[TangLeFuzZ]

Het moest met killall, het is me al gelukt

ACM schreef op 17 maart 2004 @ 12:35:
[...]

Komt dat niet gewoon omdat de boel niet meer in je cache zit en het daarom opnieuw in de cache moet raken?

Wat bedoel je daar eigenlijk precies mee? Het is toch niet 'gewoon' dat de site in de ochtend een paar uur achter elkaar gigantisch traag is, en een tijdje later weer snel?

[ Voor 82% gewijzigd door TangLeFuzZ op 18-03-2004 08:39 ]

[ACM]

Nee, dat is idd niet wat ik bedoel. En zal dus ook niet door caches komen, dat is hooguit een paar minuten.

Ik zou als ik jou was gewoon kijken wat je zoal aan verkeer hebt 's ochtends dat anders is dan 's avonds, want MySQL kijkt echt niet naar het tijdstip om te zien of ie dan wel of niet traag moet zijn. Dat wordt puur en alleen bepaal door de drukte en eventueel andere statements die 's ochtends wel en 's avonds niet worden uitgevoerd.

Oftewel, kijk wat er 's ochtends anders is dan 's avonds en dat kunnen wij niet voor je lopen raden. Hou evt een log van alle queries op een dag bij en ga die analyseren met de tijden dat het traag en snel is ernaast.

Btw, mysql afsluiten doe je door mysqladmin shutdown aan te roepen, de kill-commando's kun je beter zo min mogelijk gebruiken, sowieso niet als er een alternatieve (door de producent aangeleverde) manier is.

[TangLeFuzZ]

ACM schreef op 18 maart 2004 @ 11:43:
Ik zou als ik jou was gewoon kijken wat je zoal aan verkeer hebt 's ochtends dat anders is dan 's avonds, want MySQL kijkt echt niet naar het tijdstip om te zien of ie dan wel of niet traag moet zijn. Dat wordt puur en alleen bepaal door de drukte en eventueel andere statements die 's ochtends wel en 's avonds niet worden uitgevoerd.

In de ochtend zou het verkeer juist het minst moeten zijn en er lopen dan ook geen andere processen.

Ben echter al achter het probleem gekomen. Na contact met onze host kwamen we erachter dat het backup script van 02:00 tot 06:00 liep, waarna de backup werd geupload naar een andere server in plaats van intern.
Dit wordt binnenkort aangepast (heb dus ook meer dataverkeer moeten neerleggen, maar daar wordt ook rekening mee gehouden).

Heb nog wel een vraag, ik zat er aan te denken om mijn "Who's Online" tabel InnoDB te maken, omdat in die tabel heel veel wordt ge-insert en gedelete.

Probleem is echter dat ik het aantal gasten uit de tabel haal met SELECT count(distinct guestip), wat met InnoDB dus nogal traag zou zijn.
Wat zou de beste manier zijn om dit te ontlopen? Als die er niet echt is laat ik het aantal gasten gewoon weg denk ik.

[Macros]

Who's online is meestal maar een klein tabelletje? Als je gene count wilt doen, dan laat je je php/asp whatever gewoon het aantal records tellen bij je normale select en laat je het verder nooit zien.