login  Naam:   Wachtwoord: 
Registreer je!
 Forum

Overbelasting MySQL server

Offline bertenz - 08/09/2008 18:27
Avatar van bertenzHTML beginner Hallo allemaal,

Op mijn website (Scoort.nu) kunnen voetbalteams een eigen website beheren. Voor elk team worden de gegevens van KNVB.nl automatisch geupdate. Ik krijg echter de laatste paar dagen steeds de melding "MySQL server has gone away" bij het updaten van de gegevens. Nou heb ik er wat naar gezocht en nou blijkt mijn MySQL server overbelast te zijn, klopt dat?
Mijn script werkt op de volgende manier:
- Teams worden ingeladen
- Per team wordt eerst de stand en daarna het programma geupdate via aparte functies.
- Aan het eind worden alle waarden verwijderd (mysql_free_result).

Weet iemand hoe ik ervoor kan zorgen dat met één keer draaien alle teams worden afgegaan en dat ik tóch niet de server overbelast? Alvast bedankt.

Bertenz

23 antwoorden

Gesponsorde links
Offline Martijn - 08/09/2008 18:50 (laatste wijziging 08/09/2008 18:52)
Avatar van Martijn Crew PHP gebruik je LIMIT in je query? Zo niet, zou dat al een deel kunnen verhelpen

edit: die free result, is dat persé nodig? volgens mij niet echt

edit 2:
php.net schreef:
mysql_free_result() only needs to be called if you are concerned about how much memory is being used for queries that return large result sets. All associated result memory is automatically freed at the end of the script's execution.
Offline bertenz - 08/09/2008 21:26
Avatar van bertenz HTML beginner DeviourSoul bedankt voor je hulp. Als ik LIMIT gebruik, dan kan ik toch niet met het draaien van het script in één keer de gegevens van alle teams updaten of wel ?
Overigens gebruik ik voor het veilig invoeren in de database mysql_real_escape_string(), misschien dat het daardoor zou kunnen komen. De code die ik gebruik om de stand te updaten is: Plaatscode: 23746
De code van het programma en de resultaten is vergelijkbaar aan die van de stand.
Dus heeft iemand een idee? Of is dat van DeviourSoul toch de oplossing voor mijn probleem?
Offline Joost - 08/09/2008 21:33 (laatste wijziging 08/09/2008 21:33)
Avatar van Joost PHP expert Denk er wel aan dat wanneer je integers in de database zet, je geen ' ' eromheen zet.
De database ziet het dan als string en dat werkt nou eenmaal langzamer.
Let er verder gewoon goed op dat je keys gebruikt en integers voor vergelijken (userID's, geen usernames), alhoewel dat laatste meestal al wel wordt gedaan.
Offline Slipie - 08/09/2008 21:36
Avatar van Slipie PHP beginner Laat je queries en je tabel opmaak eens zien? Dan is daar misschien nog wat aan te tunen.
Offline bertenz - 08/09/2008 21:53 (laatste wijziging 08/09/2008 21:55)
Avatar van bertenz HTML beginner Hey, allebei bedankt voor de reacties.

Mijn tabel ziet er zo uit:
sql code - Bekijk de code zonder highlighting - Klap code in 
  1. CREATE TABLE `tabelnaam` (
  2.   `stand_id` tinyint(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  3.   `team_name` varchar(50) NOT NULL,
  4.   `stand_gespeeld` tinyint(3) UNSIGNED NOT NULL,
  5.   `stand_winst` tinyint(3) UNSIGNED NOT NULL,
  6.   `stand_gelijk` tinyint(3) UNSIGNED NOT NULL,
  7.   `stand_verlies` tinyint(3) UNSIGNED NOT NULL,
  8.   `stand_punten` tinyint(3) UNSIGNED NOT NULL,
  9.   `stand_verliespunten` tinyint(3) UNSIGNED NOT NULL,
  10.   `stand_dpv` int(5) UNSIGNED NOT NULL,
  11.   `stand_dpt` int(5) UNSIGNED NOT NULL,
  12.   `stand_doelsaldo` int(5) NOT NULL,
  13.   `stand_mindering` tinyint(3) UNSIGNED NOT NULL,
  14.   PRIMARY KEY  (`stand_id`)
  15. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;


Mijn query ziet er nu zo uit:
php code - Bekijk de code zonder highlighting - Klap code in 
  1. $sQueryUpdate = "INSERT INTO `tabelnaam` (`team_name`, `stand_gespeeld`,
  2. `stand_winst`, `stand_gelijk`, `stand_verlies`, `stand_punten`, `stand_verliespunten`,
  3. `stand_dpv`, `stand_dpt`, `stand_doelsaldo`, `stand_mindering`) VALUES ('".$elftal."',
  4. ".$gespeeld.", ".$gewonnen.", ".$gelijk.", ".$verloren.", ".$punten.", ".$verliespunten.",
  5. ".$doelvoor.", ".$doeltegen.", ".$doelsaldo.", ".$puntenmindering.")";
  6. mysql_query($sQueryUpdate) or die(mysql_error());

Ik heb zojuist de enkele aanhalingstekens bij de integers verwijderd zoals Medieval zei. Als mijn script het trouwens inleest van knvb.nl, ziet het die getallen dan eigenlijk wel als integers? Of maakt dat niet uit?
Offline Slipie - 08/09/2008 22:05
Avatar van Slipie PHP beginner Hmm alles via INSERT statements doen is ook erg traag.
Hoeveel records insert je met 1 zo'n actie ongeveer?
Misschien is het een idee om de standen op te splitsen.
De oude resultaten verplaats je naar een andere tabel en de nieuwe kan je dan uit een file inladen met sql loader. dat werk vele malen sneller omdat de database je SQL syntax dan niet hoeft te parsen.
Offline bertenz - 08/09/2008 22:12
Avatar van bertenz HTML beginner Bovenstaande query staat in een foreach-loop die meestal 12 keer doorlopen wordt per team. Op het moment zijn het nog maar 12 teams en dus 144 query's per datasoort (stand, uitslagen, programma). Het zijn er uiteindelijk dus heel veel.

@Slipie: Eerlijk gezegd snap ik niet helemaal waarom de manier die jij geeft sneller is. Je moet namelijk de oude resultaten toch ook ergens in een andere tabel invoeren... Of werkt dat op een andere manier?
Bovendien ben ik niet echt bekend met het inladen van bestanden, maar als dat de oplossing is, kan ik het misschien wel gaan leren.
Offline Martijn - 08/09/2008 22:34 (laatste wijziging 08/09/2008 22:34)
Avatar van Martijn Crew PHP dit is ook een werkende syntax:

sql code - Bekijk de code zonder highlighting - Klap code in 
  1. UPDATE tabelnaam ('KOLOM1','KOLOM2','KOLOM3')
  2. VALUES
  3. ('1a','1b','1c'),
  4. ('2a','2b','2c'),
  5. ('3a','3b','3c')



je zou met je foreach die laatste 3 regels in een $variabel kuynnen zetten die je door $variabel.="('".$waarde1a."',...)"; laat bijvullen. Dan word het nog maar 1 insert
Offline Slipie - 08/09/2008 22:36
Avatar van Slipie PHP beginner Zoals je zelf aangeeft gaat het bij het laden van de knvb data vaak fout.
Als je de data iedere keer naar een andere tabel schrijft dan kan je dat gecontroleerd doen en zowiezo in 1 statement wat al helemaal sneller gaat dan 144 losse insert statements.

Stel je maakt een 2e tabel die noem je old en je tabel met de laatste data noem je new.
Dan krijg je de volgende query:
sql code - Bekijk de code zonder highlighting - Klap code in 
  1. INSERT INTO old SELECT * FROM new;
  2. TRUNCATE TABLE new;


Het inladen van bestanden via sql loader is heel simpel, helemaal bij mysql:

sql code - Bekijk de code zonder highlighting - Klap code in 
  1. LOAD DATA INFILE='path naar je csvfile' 
  2. INTO TABLE new
  3. FIELDS TERMINATED BY ','
  4. LINES TERMINATED BY '\n';


Maar laten we eerlijk zijn, dit zijn allemaal omwegen om de performance beter te krijgen. Het beste is om de hardware van de machine, waar je database op draait, gewoon te upgraden.
Offline Joost - 08/09/2008 23:53
Avatar van Joost PHP expert @Slipie: dat kan helpen, maar zorgen dat je SQL goed is is een stuk goedkoper en beter ;) Maar wanneer dat optimaal is, tja, dan moet het wel.

Trouwens, misschien kan je ook even kijken naar 'server side caching', zie:
http://www.site...de_Caching
Dat kan echt behoorlijk schelen.
Offline Slipie - 09/09/2008 08:12
Avatar van Slipie PHP beginner @Mediaval een beetje database kan 10tallen transacties per seconden aan.
En aan een INSERT valt niet echt veel te tunen.
Maargoed je zou ook nog een sleep in je loop kunnen stoppen.
De import duurt dan langer maar je database raakt dan niet overbelast.
Offline bertenz - 09/09/2008 20:51 (laatste wijziging 09/09/2008 22:58)
Avatar van bertenz HTML beginner Met jullie hulp kom ik er denk ik wel, daarom sowieso bedankt!
Ik ga nu proberen om eerst alle gegevens tijdens de foreach-loop in een array op te slaan. Pas als alle gegevens van dat team in de array zitten, mysql_real_escape_string() er overheen gooien en dan meteen in de database doen met een meervoudige INSERT. Zou dat kunnen helpen? Op die manier staat de mysql-connectie toch niet de hele tijd open of wel?

Edit:
Ik ben nu bezig met die meervoudige insert, maar ik kan niet vinden hoe ik een variabel aantal records INSERT... Dus een soort van while-loop in MySQL. Iemand die mij hiermee op weg kan helpen?
Offline Richard - 09/09/2008 23:22
Avatar van Richard Crew algemeen Om MySQL te overbelasten moet je wel meer doen dan een paarhonderd insert's per seconde...

Even een mythe ontkrachten:
Citaat:
Denk er wel aan dat wanneer je integers in de database zet, je geen ' ' eromheen zet.
De database ziet het dan als string en dat werkt nou eenmaal langzamer.
Let er verder gewoon goed op dat je keys gebruikt en integers voor vergelijken (userID's, geen usernames), alhoewel dat laatste meestal al wel wordt gedaan.


Klinkklare onzin, MySQL kijkt intern wel voor je welk type het moet worden, dat hoef je zelf niet aan te geven. Getallen worden nou eenmaal apart gelext, dat is ook de enige reden dat je getallen buiten quotes mag hebben. Niks geen verschil in snelheid, of accuraatheid.
Offline Wim - 10/09/2008 00:55
Avatar van Wim Crew algemeen userID moet echt geen integer zijn hoor, want je rekent er namelijk niet mee...
Offline bertenz - 13/09/2008 18:37
Avatar van bertenz HTML beginner Hey, allebei ook nog bedankt voor jullie opmerkingen. Ondanks mijn poging om de scripts te optimaliseren, krijg ik nog steeds elke keer "Mysql server has gone away". Ik heb zelfs tussen elke handeling (programma, stand en uitslagen) een sleep van 20 seconden gezet. Nu kan ik wel per team het script laten draaien, maar alle teams achter elkaar is helaas nog niet mogelijk ...
Voor elk record zijn ongeveer 6 mysql_real_escape_string()-acties nodig en per soort handeling zijn er 12 records. Zou de server dit normaal gewoon aan moeten kunnen ?
Offline Wim - 13/09/2008 18:59
Avatar van Wim Crew algemeen kijk in PHPMyAdmin eens na of er geen maximum aantal connecties per user zijn ingesteld, of per host oid...

bij services als freemysql en db4free etc zetten ze vaak limitaties op dit!
Offline gothmog - 13/09/2008 21:12 (laatste wijziging 13/09/2008 21:14)
Avatar van gothmog Lid Hoe vaak wordt dat script gedraaid? Bij iedere pagina die wordt geladen? Of maar een keer als iemand wat aanmaakt? Of maar een keer per dag?
Lijkt me niet nodig om het iedere keer te updaten aangezien er meestal maar 1 wedstrijd per week is dus hoeft het maar een keer in de 7 dagen te worden geupdated.

En die knvbripper, wat ript dat allemaal? Ript dat alle data van de knvb of alleen wat je nodig hebt?
Offline bertenz - 14/09/2008 00:01
Avatar van bertenz HTML beginner @Wimmarien: In PHPmyadmin kan ik alleen de volgende limitatie vinden die er misschien iets mee te maken zou kunnen hebben:
Citaat:
max. concurrent connections 24

Alleen wat dat precies inhoud weet ik dus niet ... Ik heb overigens een betaalde host.

@Gothmog: Het script wordt elke dag minimaal 1 keer gedraaid zodat alle gegevens van alle teams in mijn eigen database geschreven worden. Soms wijzigt de KNVB nog wel eens iets tussendoor, vandaar dat het elke dag geupdate moet worden. Maar bij één keer runnen van het script is de server al overbelast. (tenminste, ik heb aangenomen dat "Mysql server has gone away" betekent dat het overbelast is ...). Ik kan het script wel telkens handmatig voor één team laten draaien, maar dat is nog best veel werk om een paar keer per week te doen.

Overigens wordt alleen de nuttige informatie in mijn database gestopt (teams en de getallen die daarbij horen).
Offline Joost - 14/09/2008 00:37 (laatste wijziging 14/09/2008 01:44)
Avatar van Joost PHP expert De error die voor teveel connecties is in de zin van "user *** has more than 'max_user_connections' !".

Hier wat info over mysql server has gone away:
http://dev.mysq...-away.html
Offline gothmog - 14/09/2008 11:58
Avatar van gothmog Lid Wat dacht je van dit: run het script maar een keer in de 10 minuten, en update iedere 10 minuten maar een team. Zodra er meer teams komen wordt het bijv. 5 minuten.
Zo duurt het maar heel even en is het toch up to date.
Offline bertenz - 15/09/2008 18:14
Avatar van bertenz HTML beginner Allemaal bedankt voor jullie hulp. Het werkt zoals ik al eerder zei voor één team tegelijk gewoon goed. Ik laat nu op aanraden van Gothmog het script vaker draaien, telkens voor één team. Het werkt nu goed, dus nogmaals allemaal bedankt !
Offline Martijn - 15/09/2008 18:58
Avatar van Martijn Crew PHP mssn te laat, maar je zou met een sleep kunnen werken. 1 bestand, en dan in je while na elk team een sleep(5) kunnen zetten. Dan houd het script zelf ff een pauze en kan je server bijkomen
Offline bertenz - 15/09/2008 20:37
Avatar van bertenz HTML beginner Hey DeviourSoul, bedankt dat je ook meedenkt. Ik heb tussen de verschillende scripts al een sleep van 15 seconden staan en helaas lukte het nog niet om alle teams up te daten .... maar toch bedankt!
Gesponsorde links
Dit onderwerp is gesloten.
Actieve forumberichten
© 2002-2024 Sitemasters.be - Regels - Laadtijd: 0.235s