Upgrade MySQL = hoge cpu load [custom php/sql omgeving)

[M4RTiN]

Ik heb jarenlang prima gedraaid met een Lamp setup (Ubuntu 14.04) met een eigen gemaakt PHP pakket. Om gebruik te maken van een nieuw bibliotheek was ik geforceerd om PHP 5.6 te draaien en daarom ben ik ook gelijk gegaan naar Ubuntu 16.06.

Eenmaal alles weer ingesteld kom ik erachter dat MySQL een hoge load heeft.

Show proceslist gaf aan dat het erg rustig is, er worden zo nu en dan query's uitgevoerd die kloppen. Niets bijzonders daar te zien

Met behulp van

code:

 Top -H -p <pidnummer mysql>

Krijg ik een lijst van alle child-processes van mysql. Daar zie ik bovenaan wel 12+ processen staan met alsnog wel een redelijke hoge load van 16 tot 23% cpu usage.

Een strace daarop geeft eigenlijk continue de melding:

code:

GetTimeofDay(......

Hierbij moest ik gelijk even denken dat tijdens de upgrade alle tabellen werden bijgewerkt naar een nieuw soort datum notatie.

Indirect kom ik er daarna achter wat de reden is, de load is verdwenen zodra ik een TV-scherm met een browser die elke seconde een json script opvraagt waar vervolgens best wel een hoop SQL querys aan vast hangen.

Nu heeft dit systeem op de vorige versie nog nooit load veroorzaakt, maar er zijn nu vast dingen veranderd, bijvoorbeeld zaken wat nu allemaal wat stricter werkt dan daarvoor.

Nu zijn er nog 2 zaken waar ik sowieso mee aan de slag moet, en ik vraag me af of dit ook grote invloed kan hebben op de performance. Ik heb nu de mysql server en verbinding- collatie op UTF-8 ingesteld. Om eerlijk te zijn weet ik niet eens hoe het ervoor stond ingesteld, maar ik moest dit nu wel instellen om "rare" tekens weer goed te krijgen. Tegelijkertijd zie ik dat ik nooit goed de tabellen heb ingesteld naar een juiste collatie. Er staan nog een hoop op de default van jaren terug: Iets met Swedish.

Dan zag ik ook dat er nog een hoop tabellen op MyIsam stonden ingesteld en de rest op InnoDB.

Dus wat ik kan doen:
1: Tabel collatie goed zetten
2: Alles op InnoDB zetten
3: slow query log enablen en kijken of ik daar wat kan vinden
4: ik maak hier en daar nog gebruik van mysql_query i.p.v. mysqli_query, wellicht heeft dat nu sinds de nieuwe versie ook impact op performance

Zie ik nog wat over het hoofd? Wat zouden jullie verder nog doen?

Globaal is het probleem volgens mij dat de SQL server continue zaken zoals de datum moet vertalen naar een client toe, en dit kost nu meer resources. (De melding van gettimeofday die enorm vaak herhaalt wordt in een child proces)

[DJMaze]

Je gebruikt zeker DATETIME velden?

Stap eens over op INT of

code:

SET time_zone = '+0:00';

En los de timezone op in je code (als dat moet).

[ Voor 20% gewijzigd door DJMaze op 17-01-2018 12:29 ]

[ACM]

M4RTiN schreef op woensdag 17 januari 2018 @ 09:26:
Krijg ik een lijst van alle child-processes van mysql. Daar zie ik bovenaan wel 12+ processen staan met alsnog wel een redelijke hoge load van 16 tot 23% cpu usage.

Een strace daarop geeft eigenlijk continue de melding:

code:

GetTimeofDay(......

Dit lijkt me een vrij nutteloze manier om te kijken wat ie doet, die functie wordt vast gebruikt om diverse timers bij te houden

Hierbij moest ik gelijk even denken dat tijdens de upgrade alle tabellen werden bijgewerkt naar een nieuw soort datum notatie.

Hoe oud was je vorige MySQL en hoe nieuwe je nieuwe? En het was wel handig geweest om daadwerkelijk te weten wat er veranderde, maar daar is het vast nu te laat voor

Indirect kom ik er daarna achter wat de reden is, de load is verdwenen zodra ik een TV-scherm met een browser die elke seconde een json script opvraagt waar vervolgens best wel een hoop SQL querys aan vast hangen.

Maar zijn die queries ook trager geworden? Als ie veel relatief snelle queries doet, dan is het inderdaad niet echt zichtbaar in 'processlist', terwijl ze toch allemaal _iets_ trager geworden kunnen zijn. Het kan geen kwaad om die losse queries te pakken en daar een explain op te doen.

Nu heeft dit systeem op de vorige versie nog nooit load veroorzaakt, maar er zijn nu vast dingen veranderd, bijvoorbeeld zaken wat nu allemaal wat stricter werkt dan daarvoor.

Ik zou eerder gokken op iets andere vertaling van een query naar een zgn 'execution plan'. Wellicht los je het al op door een verse 'analyze table' te doen op de tabel waar de meeste vertraging uit voort is gekomen. Of wordt nu ten onrechte een bepaalde index gebruikt die ie voorheen altijd negeerde of juist andersom.

Nu zijn er nog 2 zaken waar ik sowieso mee aan de slag moet, en ik vraag me af of dit ook grote invloed kan hebben op de performance. Ik heb nu de mysql server en verbinding- collatie op UTF-8 ingesteld. Om eerlijk te zijn weet ik niet eens hoe het ervoor stond ingesteld, maar ik moest dit nu wel instellen om "rare" tekens weer goed te krijgen. Tegelijkertijd zie ik dat ik nooit goed de tabellen heb ingesteld naar een juiste collatie. Er staan nog een hoop op de default van jaren terug: Iets met Swedish.

Die collation steeds vertalen kost vast wat CPU, maar als je niet weet wat ie hiervoor deed is er verder weinig over te zeggen. Heb je de client-side aangepast voor die collation of iets in de config? Als je dat 'nu ineens' moest doen, is er blijkbaar nu een discrepantie tussen server en client die er eerder niet was.
Sterker nog, ik gok dat je eerder gewoon alles in een ISO-8859-variant had (die latin...-swedish) en het zou dus zomaar kunnen dat je nu juist alles van/naar utf-8 aan het vertalen bent.

1: Tabel collatie goed zetten

Als je code utf-8 verwacht en je tabellen zijn dat niet, dan zal dat vast helpen.

2: Alles op InnoDB zetten

InnoDB is niet per se sneller dan MyISAM, maar als er relevante tunables met je nieuwe versie meer in het voordeel van InnoDB staan, dan kan dat uiteraard uitmaken.
Heb je uberhaupt je serverconfig doorgekeken?

3: slow query log enablen en kijken of ik daar wat kan vinden

Als je niks in process list ziet, dan zijn ze nog steeds te snel om hier in te vinden... Tenzij je die querylog heel lage waardes geeft natuurlijk.

4: ik maak hier en daar nog gebruik van mysql_query i.p.v. mysqli_query, wellicht heeft dat nu sinds de nieuwe versie ook impact op performance

Dat lijkt me weinig tot niets uit te maken, maar het is sowieso niet handig dat door elkaar te gebruiken. Als je bovendien sommige queries via mysqli doet en sommige via mysql binnen dezelfde applicatie/php-request, dan heb je ook nog kans dat je steeds twee keer verbindt.

Zie ik nog wat over het hoofd? Wat zouden jullie verder nog doen?

Er was vroeger het concept 'mysql query cache', volgens mij is dat met nieuwe versies verdwenen of in ieder geval standaard uitgezet. Als je oude json-code steeds dezelfde queries deed en er zit inderdaad verschil in die query cache instelling, dan worden die queries ineens allemaal steeds uitgevoerd ipv als een soort hash-table lookup opgelost.
Als je steeds dezelfde queries uitvoert, is je code wellicht ook gewoon inefficient en zou je alsnog dat moeten zien te voorkomen

Globaal is het probleem volgens mij dat de SQL server continue zaken zoals de datum moet vertalen naar een client toe, en dit kost nu meer resources. (De melding van gettimeofday die enorm vaak herhaalt wordt in een child proces)

Dat lijkt me niet. Gettimeofday heeft volgens mij meer met interne timers te maken. Het kan wellicht nog wel zijn dat je bepaalde data-verzamel instellingen aan hebt staan, die niet handig zijn. Maar ik zou niet uit mijn hoofd weten welke.

DJMaze schreef op woensdag 17 januari 2018 @ 12:29:
Je gebruikt zeker DATETIME velden?

Stap eens over op INT

Wat mij betreft is 'overstappen op int' een enorme achteruitgang die ons uit het verleden is opgelegddoordat php nog geen eigen date-representatie had en MySQL er vziw ook niet zo efficiënt mee overweg ging.
Wij zien in ieder geval geen significante verschillen tussen tabellen met ouderwetse ints en "nieuwerwetse" datetime's.

[RobIII]

DJMaze schreef op woensdag 17 januari 2018 @ 12:29:
Je gebruikt zeker DATETIME velden?

Stap eens over op INT

Als we nu nog in 2003 zaten ofzo had je een punt en was het het proberen waard. Nu? Onzin.

[ Voor 14% gewijzigd door RobIII op 17-01-2018 14:31 ]

[DJMaze]

@ACM & @RobIII ik weet niet of jullie internationaal opereren?
Zo ja, hoe handelen jullie dan correct de timezones af in MySQL?

Bijvoorbeeld:
WHERE date BETWEEN '2018-01-01 00:00:00 Europe/Amsterdam' AND '2018-02-01 00:00:00 Europe/Amsterdam'
en
WHERE date BETWEEN '2018-01-01 00:00:00 America/Curacao' AND '2018-02-01 00:00:00 America/Curacao'

iCAL heeft niet voor niks TZ en "float"

Nog maar te zwijgen over:
WHERE YEAR(date) = 2018 AND tz='America/Curacao'

Nee, ik blijf lekker bij INT in MySQL

[RobIII]

DJMaze schreef op woensdag 17 januari 2018 @ 13:49:
@ACM & @RobIII ik weet niet of jullie internationaal opereren?
Zo ja, hoe handelen jullie dan correct de timezones af in MySQL?

Hoe doe jij dat met je INT dan

Maar if you must know: In de meeste gevallen hanteren we, in MySQL, gewoon DATETIME = UTC en laten de timezones over aan de presentatielaag Maar, gelukkig, zijn wij inmiddels bijna helemaal van MySQL af en over op MSSQL (En die kent gewoon fatsoenlijke datetime types )

[ Voor 13% gewijzigd door RobIII op 17-01-2018 14:31 ]

[DJMaze]

RobIII schreef op woensdag 17 januari 2018 @ 13:51:
Hoe doe jij dat met je INT dan

PHP:

$tz = new DateTimeZone('Europe/Amsterdam');
$from = new \DateTime('2018-01-01 00:00:00', $tz);
$till = new \DateTime('2018-02-01 00:00:00', $tz);
"WHERE date BETWEEN {$from->getTimestamp()} AND {$till->getTimestamp()}";

Inderdaad vanaf PHP 5.2 en HTML5 <input type="datetime-local"> is het makkelijk.

RobIII schreef op woensdag 17 januari 2018 @ 13:51:
Maar if you must know: In de meeste gevallen hanteren we gewoon DATETIME = UTC

Dat stond ook in mijn eerste post

[ Voor 29% gewijzigd door DJMaze op 17-01-2018 13:58 ]

[RobIII]

DJMaze schreef op woensdag 17 januari 2018 @ 13:54:
[...]

PHP:

$tz = new DateTimeZone('Europe/Amsterdam');
$from = new \DateTime('2018-01-01 00:00:00', $tz);
$till = new \DateTime('2018-02-01 00:00:00', $tz);
"WHERE date BETWEEN {$from->getTimestamp()} AND {$till->getTimestamp()}";

Inderdaad vanaf PHP 5.2 en HTML5 <input type="datetime-local"> is het makkelijk.

En als ik nou 10 records uit je DB haal, hoe zie ik dan aan die INT welke timezone het is?

Jij hebt 't over het correct afhandelen van timezones:

DJMaze schreef op woensdag 17 januari 2018 @ 13:49:
Zo ja, hoe handelen jullie dan correct de timezones af in MySQL?

En een INT daarvoor gebruiken is alles behalve correct

[ Voor 21% gewijzigd door RobIII op 17-01-2018 14:06 ]

[DJMaze]

RobIII schreef op woensdag 17 januari 2018 @ 14:00:
hoe zie ik dan aan die INT welke timezone het is?

Niet, want MySQL ondersteund dat sowieso niet (dus ook niet op DATETIME).
Vandaar heb je een tweede veld in je tabel genaamd 'timezone VARCHAR(128)' ofzo.

INT:

PHP:

"SELECT date, timezone FROM table";
new \DateTime("@{$row['date']}", new \DateTimeZone($row['timezone']));

DATETIME

PHP:

"SELECT date, timezone FROM table";
new \DateTime($row['date'], new \DateTimeZone($row['timezone']));

Maar.... als je netjes date_default_timezone_set() gebruikt dan....
INT:

PHP:

"SELECT date FROM table";
$date = new \DateTime("@{$row['date']}");
echo $date;
// of
echo date('Y-m-d H:i:s', $row['date']);
// of floating
echo gmdate('Y-m-d H:i:s', $row['date']);

DATETIME

PHP:

"SELECT date, timezone FROM table";
$date = new \DateTime($row['date'], new \DateTimeZone($row['timezone']));
$date->setTimezone(new \DateTimeZone(date_default_timezone_get()));
echo $date;

En dan heb ik het nog niet eens over floating time. Bijvoorbeeld een wekker die om 08:00 af moet gaan ongeacht in welke tijdzone je zit

Al met al is de Unix Timestamp geen slecht idee (tot 2038)

[ Voor 53% gewijzigd door DJMaze op 17-01-2018 14:22 ]

[RobIII]

DJMaze schreef op woensdag 17 januari 2018 @ 14:06:

Ik vind 't allemaal helemaal fantastisch en prima en ik heb al lang gezien dat deze discussie met jou aangaan geen nut heeft (waarschijnlijk omdat je je eigen oplossing helemaal de bom vindt, en dat mag hoor !) maar ik wou dus alleen maar even gezegd hebben toen ik 't initieel aankaartte dat "de rest van de wereld" (grotendeels) het met je oneens is / zal zijn Maar we zijn inmiddels dusdanig offtopic dat ik er daar hier niet verder over wil gaan doorzagen of nog dieper in de details duiken.

[ Voor 6% gewijzigd door RobIII op 17-01-2018 14:36 ]

[M4RTiN]

Allereerst iedereen bedankt voor alle tips, ik denk dat dit mij enorm kan helpen.

Dit lijkt me een vrij nutteloze manier om te kijken wat ie doet, die functie wordt vast gebruikt om diverse timers bij te houden

Grote kans op, dan heb ik niets gezegd haha. Ik probeer soms wat dots te connecten, zijn allemaal aannames.

Hoe oud was je vorige MySQL en hoe nieuwe je nieuwe? En het was wel handig geweest om daadwerkelijk te weten wat er veranderde, maar daar is het vast nu te laat voor

Vorige install zal een up to date versie zijn die bij Ubuntu 14.04 LTS hoort

Maar zijn die queries ook trager geworden? Als ie veel relatief snelle queries doet, dan is het inderdaad niet echt zichtbaar in 'processlist', terwijl ze toch allemaal _iets_ trager geworden kunnen zijn. Het kan geen kwaad om die losse queries te pakken en daar een explain op te doen.

Querys niets aan veranderd, zelfde programma, andere sql/php versie

Ik zou eerder gokken op iets andere vertaling van een query naar een zgn 'execution plan'. Wellicht los je het al op door een verse 'analyze table' te doen op de tabel waar de meeste vertraging uit voort is gekomen. Of wordt nu ten onrechte een bepaalde index gebruikt die ie voorheen altijd negeerde of juist andersom.

Goede tip, analuze table ben ik niet bekend mee, hier ga ik wat meer over uitzoeken

Die collation steeds vertalen kost vast wat CPU, maar als je niet weet wat ie hiervoor deed is er verder weinig over te zeggen. Heb je de client-side aangepast voor die collation of iets in de config? Als je dat 'nu ineens' moest doen, is er blijkbaar nu een discrepantie tussen server en client die er eerder niet was.
Sterker nog, ik gok dat je eerder gewoon alles in een ISO-8859-variant had (die latin...-swedish) en het zou dus zomaar kunnen dat je nu juist alles van/naar utf-8 aan het vertalen bent.

Ik meende dat ik alles al eens naar UTF-8 had gezet, ik ben uiteindelijk opnieuw begonnen met een frisse my.cnf dus ik kan het niet achterhalen. Het is best nog wel aannemelijk dat dit nooit goed heeft gestaan. Uiteindelijk wil ik alles netjes op UTF-8 hebben. Dit is dus weer een stap in de juiste richting.

InnoDB is niet per se sneller dan MyISAM, maar als er relevante tunables met je nieuwe versie meer in het voordeel van InnoDB staan, dan kan dat uiteraard uitmaken.
Heb je uberhaupt je serverconfig doorgekeken?

Ik zou niet weten welke tunables ik gebruik van zou kunnen maken, zo ver reikt mijn kennis niet. Ik weet ook niet wat ik dus in de config files moet zoeken/weten.

Dat lijkt me weinig tot niets uit te maken, maar het is sowieso niet handig dat door elkaar te gebruiken. Als je bovendien sommige queries via mysqli doet en sommige via mysql binnen dezelfde applicatie/php-request, dan heb je ook nog kans dat je steeds twee keer verbindt.

Eens, op dit moment gebruik ik dus inderdaad per php script 2 verbindingen, en doe ik in fases de meuk ombouwen naar mysqli. Niet wenselijk, maar ik zie daar nu even geen andere mogelijkheid in

Er was vroeger het concept 'mysql query cache', volgens mij is dat met nieuwe versies verdwenen of in ieder geval standaard uitgezet. Als je oude json-code steeds dezelfde queries deed en er zit inderdaad verschil in die query cache instelling, dan worden die queries ineens allemaal steeds uitgevoerd ipv als een soort hash-table lookup opgelost.
Als je steeds dezelfde queries uitvoert, is je code wellicht ook gewoon inefficient en zou je alsnog dat moeten zien te voorkomen

Hmmmm, lijkt mij erg aannemelijk dat het hier mee te maken heeft. Ik ga hier ook induiken

Dat lijkt me niet. Gettimeofday heeft volgens mij meer met interne timers te maken. Het kan wellicht nog wel zijn dat je bepaalde data-verzamel instellingen aan hebt staan, die niet handig zijn. Maar ik zou niet uit mijn hoofd weten welke.

Oke, ga ik het daar verder niet in zoeken voorlopig

Wat mij betreft is 'overstappen op int' een enorme achteruitgang die ons uit het verleden is opgelegddoordat php nog geen eigen date-representatie had en MySQL er vziw ook niet zo efficiënt mee overweg ging.
Wij zien in ieder geval geen significante verschillen tussen tabellen met ouderwetse ints en "nieuwerwetse" datetime's.

Eens. Verder maak ik inderdaad een hoop gebruik van datetime velden.

[DJMaze]

@M4RTiN Google vertelt genoeg over Gettimeofday https://www.google.nl/search?q=mysql+Gettimeofday+cpu

@RobIII was inderdaad flink offtopic, het oneens zijn is normaal. Anders was er maar 1 programmeertaal en geen frameworks

[M4RTiN]

DJMaze schreef op woensdag 17 januari 2018 @ 15:42:
@M4RTiN Google vertelt genoeg over Gettimeofday https://www.google.nl/search?q=mysql+Gettimeofday+cpu

Grappig op die pagina zijn bijna alle linkjes paars, maar daar kwamen geen concrete zaken uit die mij óf verder hielpen en programmeer fouten lieten inzien. Ik denk dat ACM gelijk heeft dat dit wat anders is en mij op een verkeerd spoor heeft gezet.

[M4RTiN]

Trouwens ik draai nu een MySQL versie 5.7, ik heb uiteindelijke de hele /etc/mysql verwijderd om maar met een frisse installatie te beginnen. De cache staat nog wel aan met een default config. Ik meende te lezen dat het vanaf 5.6 of 5.7 standaard niet meer gebruikt zou gaan worden.

Op dit moment dus:

code:

Variable_name   Value   
have_query_cache    YES

Hier ligt het dus ook niet aan...