Slechte MySQL performance

[Craetive]

Op dit moment ben ik bezig om een verouderde dedicated server om te zetten naar een VPS. De schaalbaarheid en de back-up mogelijkheden maken op dit moment een VPS prijstechnisch een stuk interessanter dan de dedicated server. Op de oude server draaien op dit moment twee verouderde webapplicaties.

De dedicated bak draait op dit moment bij Hetzner (EQ4) en de test VPS bij TransIP (X4).

Specificaties oude server:
Debian 5
Intel(R) Core(TM) i7 CPU 920 @ 2.67GHz, 8 cores
8 GB DDR3 RAM
2x 750 GB SATA II HDD
Ver 5.1.73-1-log for debian-linux-gnu on x86_64 ((Debian))
SHOW VARIABLES;

Specificaties nieuwe server:
CentOS 7 + Plesk Obsidian 18.0.27
2x vCPU (Intel Xeon. Model, type en kloksnelheid onbekend)
4 GB RAM
120 GB SSD
Ver 5.5.65-MariaDB for Linux on x86_64 (MariaDB Server)
SHOW VARIABLES;

Ik merk op dit moment een slechte performance op de nieuwe MySQL server. Het inladen van wat kleinere tabellen gaat prima, maar er zitten ook een aantal tabellen tussen met 4025523 rows. Ik merk dat het ophalen van deze queries via de nieuwe server binnen 2.8 seconde gebeurd, terwijl de oude server hier 0.5 seconden over doet. De queries zijn uitgevoerd via de MySQL console.

Uiteraard heb ik geprobeerd om hier en daar wat tabellen te optimaliseren door gebruik te maken van de juiste INDEX, queries aangepast maar helaas is het nog ruimschoots ondermaats in vergelijking met de oude server.

De database is geëxporteerd vanuit de oude server via mysqldump en geïmporteerd via mysql op de nieuwe server. Belangrijk om te vermelden is dat het gaat om een MyISAM database. Het omzetten naar InnoDB heeft helaas ook niet tot een verbetering van performance geleidt.

Om uit te sluiten dat het mogelijk aan de gebruikte MySQL versies gaat heb ik op de nieuwe server een docker container opgezet met MySQL 5.1.73 met exact dezelfde my.cnf-configuratie als de oude server (SHOW VARIABLES;). Helaas laden deze queries ook binnen 2.8 seconden..

Dingen die ik al geprobeerd heb:
- Nieuwe VPS aangeschaft met 4vCPU cores / 8GB RAM.
- Het zetten van key_buffer_size op 4096M (via deze url).

Heeft iemand misschien een idee waar ik het nog verder kan zoeken? Ik begin steeds meer het vermoeden te krijgen dat de dedicated server toch meer resources krijgt en daardoor over een betere performance beschikt.

[GlowMouse]

Je zou eens moeten meten wat precies de bottleneck is. Ik vermoed dat de single core performance van de vCPU minder hoog is dan van de i7 920.

Hoewel het geen antwoord is op je vraag, zou je ook eens beter naar de query kunnen kijken. Wellicht dat hij met wat aanpassingen op de nieuwe server ook in minder dan 0,5 seconden kan draaien.

[GarBaGe]

Database performance is altijd een heel moeilijk onderwerp.
Voor hetzelfde geldt, passen die 4M rijen toevallig in je 8GB geheugen van je oude server en kon ie daarmee bijna alles vanuit geheugen serveren.
Gezien deze nieuwe maar 4GB heeft, moet ie mogelijk voor iedere query terug naar de disk.

[emnich]

Doe eens een explain van je query en laat de query eens zien.

[synoniem]

Van mysql en performance weet ik niet zoveel maar ik ben bij Hezner op een cloud VPS overgestapt gebaseerd op AMD EPYC 2nd Gen met 3 VCPU en kan vertellen dat die behoorlijk snel is voor een niet dedicated server. (En betaalbaarder dan TransIP).

[jbdeiman]

GarBaGe schreef op dinsdag 2 juni 2020 @ 14:23:
Database performance is altijd een heel moeilijk onderwerp.
Voor hetzelfde geldt, passen die 4M rijen toevallig in je 8GB geheugen van je oude server en kon ie daarmee bijna alles vanuit geheugen serveren.
Gezien deze nieuwe maar 4GB heeft, moet ie mogelijk voor iedere query terug naar de disk.

Dit was ook mijn eerste gedachte.

@Anoniem: 37297
Wat gebeurt er als je de my.cnf van de oude server qua settings overneemt naar de nieuwe VPS, met natuurlijk wel de verwijzing naar de eigenlijke database correct? Als die dan ook trager blijft, en SQL versie en alle settings gelijk zijn, dan zou je het nog eens op de 8GB versie kunnen proberen op exact dezelfde manier.

Je kan natuurlijk ook een vergelijk maken in de query uitvoer door een EXPLAIN statement te gebruiken. Zo hebben wij in onze applicatie een query die op de ene SQL versie vrij snel is en een andere SQL versie is daar heel traag mee, we hebben deze dus aan moeten passen voordat we over konden naar een nieuwe versie.

[pennywiser]

www.mysqltuner.pl ook even draaien en aanbevelingen zoveel mogelijk opvolgen.

verder snap ik de overweging van verlaging van ram niet helemaal, waarom zou je dit doen. bij voorkeur verdubbelen, nooit verlagen.

[ Voor 47% gewijzigd door pennywiser op 02-06-2020 14:41 ]

[easy-cloud]

Ik weet dat de storage van TransIP niet bij dezelfde server staat als de VPS op draait, hierdoor zit er ook meer latency op.

[Freeaqingme]

Is de database al live op de nieuwe server? Zo niet, zou ik deze opnieuw importeren maar het nu even bij myisam houden (en verder nog niets wijzigen). Op die manier kan je eerst vergelijken wat dan de performance is. Nu blijft dat gissen.

[Craetive]

GlowMouse schreef op dinsdag 2 juni 2020 @ 14:20:
Je zou eens moeten meten wat precies de bottleneck is. Ik vermoed dat de single core performance van de vCPU minder hoog is dan van de i7 920.
[..]

Hoe kan ik dit het beste testen? Zelf heb ik sysbench laten draaien op zowel de oude als de nieuwe server, hierbij de resultaten:

Oude server:

code:

root@webbq:~$ sysbench --test=cpu run

sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Doing CPU performance benchmark

Threads started!
Done.

Maximum prime number checked in CPU test: 10000


Test execution summary:
    total time:                          8.9408s
    total number of events:              10000
    total time taken by event execution: 8.9398
    per-request statistics:
         min:                                  0.87ms
         avg:                                  0.89ms
         max:                                  1.63ms
         approx.  95 percentile:               0.92ms

Threads fairness:
    events (avg/stddev):           10000.0000/0.00
    execution time (avg/stddev):   8.9398/0.00

Nieuwe server:

code:

[root@webbq]# sysbench --test=cpu run
WARNING: the --test option is deprecated. You can pass a script name or path on                                                                                                                                                              the command line without any options.
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Prime numbers limit: 10000

Initializing worker threads...

Threads started!

CPU speed:
    events per second:   681.13

General statistics:
    total time:                          10.0012s
    total number of events:              6814

Latency (ms):
         min:                                    1.31
         avg:                                    1.47
         max:                                   39.74
         95th percentile:                        1.70
         sum:                                 9985.89

Threads fairness:
    events (avg/stddev):           6814.0000/0.00
    execution time (avg/stddev):   9.9859/0.00

Op basis van bovenstaande metingen lijkt het er dus inderdaad op dat de oude server een betere kloksnelheid heeft en dat zou de performance issues kunnen verklaren. De resultaten lijken mij trouwens niet helemaal betrouwbaar aangezien beide servers niet dezelfde versie van sysbench kunnen draaien, maar het geeft wellicht wel een goed beeld.

GarBaGe schreef op dinsdag 2 juni 2020 @ 14:23:
Database performance is altijd een heel moeilijk onderwerp.
Voor hetzelfde geldt, passen die 4M rijen toevallig in je 8GB geheugen van je oude server en kon ie daarmee bijna alles vanuit geheugen serveren.
Gezien deze nieuwe maar 4GB heeft, moet ie mogelijk voor iedere query terug naar de disk.

Dat is ook exact de reden dat ik een extra test-VPS heb aangeschaft met 4 vCPU en 8GB aan ram. Zowel de performance op 2vCPU/4GB RAM als 4vCPU/8GB aan ram is gelijk.

emnich schreef op dinsdag 2 juni 2020 @ 14:24:
Doe eens een explain van je query en laat de query eens zien.

Een voorbeeld van een query die langzaam draait op de nieuwe bak:

Nieuwe server:

code:

MariaDB []> EXPLAIN SELECT * FROM toplist, lyrics LEFT JOIN djtunes ON djtunes.lyric=lyrics.id WHERE toplist.toplist_genre='Overall' AND toplist.lyric_id=lyrics.id  ORDER BY toplist.dateCreated DESC, toplist.rankToday ASC LIMIT 10;
+------+-------------+---------+--------+------------------------+---------------+---------+------------------------------------+--------+----------------------------------------------------+
| id   | select_type | table   | type   | possible_keys          | key           | key_len | ref                                | rows   | Extra                                              |
+------+-------------+---------+--------+------------------------+---------------+---------+------------------------------------+--------+----------------------------------------------------+
|    1 | SIMPLE      | toplist | ref    | lyric_id,toplist_genre | toplist_genre | 99      | const                              | 236178 | Using index condition; Using where; Using filesort |
|    1 | SIMPLE      | lyrics  | eq_ref | PRIMARY,id             | PRIMARY       | 4       | .toplist.lyric_id |      1 |                                                    |
|    1 | SIMPLE      | djtunes | ref    | lyric                  | lyric         | 4       | .toplist.lyric_id |      1 |                                                    |
+------+-------------+---------+--------+------------------------+---------------+---------+------------------------------------+--------+----------------------------------------------------+

Oude server:

code:

EXPLAIN SELECT * FROM toplist, lyrics LEFT JOIN djtunes ON djtunes.lyric=lyrics.id WHERE toplist.toplist_genre='Overall' AND toplist.lyric_id=lyrics.id ORDER BY toplist.dateCreated DESC, toplist.rankToday ASC LIMIT 10;
+----+-------------+---------+--------+------------------------+---------------+---------+-----------------------------+--------+-----------------------------+
| id | select_type | table   | type   | possible_keys          | key           | key_len | ref                         | rows   | Extra                       |
+----+-------------+---------+--------+------------------------+---------------+---------+-----------------------------+--------+-----------------------------+
|  1 | SIMPLE      | toplist | ref    | lyric_id,toplist_genre | toplist_genre | 99      | const                       | 168895 | Using where; Using filesort |
|  1 | SIMPLE      | lyrics  | eq_ref | PRIMARY,id             | PRIMARY       | 4       | .toplist.lyric_id |      1 |                             |
|  1 | SIMPLE      | djtunes | ref    | lyric                  | lyric         | 4       | .toplist.lyric_id |      1 |                             |
+----+-------------+---------+--------+------------------------+---------------+---------+-----------------------------+--------+-----------------------------+
3 rows in set (0.00 sec)

Het valt mij trouwens nu op dat de nieuwe server naar een stuk meer rows kijkt dan de oude server Dat zou het uiteraard kunnen verklaren waarom het uitvoeren van de queries langer duurt, maar waarom dat gebeurd? Geen verklaring voor..

Dit is trouwens maar een enkel voorbeeld van een query die langzaam loopt. De algehele performance van de webapplicatie is niet te vergelijken met de oude server. Maar dat kan natuurlijk aan meer factoren liggen dan alleen MySQL

jbdeiman schreef op dinsdag 2 juni 2020 @ 14:34:
[...]
@Anoniem: 37297
Wat gebeurt er als je de my.cnf van de oude server qua settings overneemt naar de nieuwe VPS, met natuurlijk wel de verwijzing naar de eigenlijke database correct?[..]

Dat heb ik dus exact gedaan, zonder resultaat helaas (zie daarvoor de pastebin's in de topicstart)

pennywiser schreef op dinsdag 2 juni 2020 @ 14:36:
www.mysqltuner.pl ook even draaien en aanbevelingen zoveel mogelijk opvolgen.

Deze had ik al uitgevoerd maar de resultaten hadden helaas geen invloed op de performance. Bij deze heb ik hem even opnieuw uitgevoerd. Wellicht dat jij opties zit die direct de performance zouden moeten verbeteren.

Ik ben trouwens op de hoogte van de memory waarschuwingen, dat komt uiteraard vanwege de hoge key_buffer_size.

pennywiser schreef op dinsdag 2 juni 2020 @ 14:36:
verder snap ik de overweging van verlaging van ram niet helemaal, waarom zou je dit doen. bij voorkeur verdubbelen, nooit verlagen.

Heel simpel: de oude server verbruikt 2GB aan ram, dus dan zou 4GB prima moeten volstaan.

Freeaqingme schreef op dinsdag 2 juni 2020 @ 15:47:
Is de database al live op de nieuwe server? Zo niet, zou ik deze opnieuw importeren maar het nu even bij myisam houden (en verder nog niets wijzigen). Op die manier kan je eerst vergelijken wat dan de performance is. Nu blijft dat gissen.

Nee, de server is nog niet live. Het gaat nog steeds om een MyISAM database. Ik heb alleen een test database aangemaakt en deze omgezet naar InnoDB om te kijken of dat verschil in performance biedt, maar helaas dus niet.

[pennywiser]

innodb_buffer_pool_size (>= 12.1G) if possible.

Die is heel fors, ik weet dat je zei dat je MyIsam gebruikt, maar kennelijk zit er een Innodb component in en is de aanbeveling toch dit 12 GB aan ram te laten gebruiken. Echter was en is dat onmogelijk.

Verder is 5.5 stokoud. Nieuwere Mysql versies hebben tal van performance verbeteringen. Pak Mysql 8 er eens bij op een testbak. Mariadb kan ook, iedereen roept dat die beter is, maar bij Mysql zitten ze ook niet stil.

Dat er nu 2GB wordt gebruikt is een harde setting, het is niet zo dat Mysql zelfstandig extra gaat gebruiken op het moment dat dat nodig mocht blijken.

[emnich]

Doe ook eens een test van de diskspeed bijvoorbeeld

Je query lijkt niet CPU intensief dus ik kan me haast niet voorstellen dat de CPU zo'n groot verschil maakt.

[emnich]

Je kan overigens ook als test op beide, die beide tabellen met engine MEMORY maken. Dan staan ze in je RAM. Als ze dan vrijwel even snel zijn op beide bakken dan is het vrij zeker de disk snelheid.

Ook is ede executie van beide queries niet gelijk en ik kan me haast niet voorstellen dat dat ligt aan de versie van MySQL/MariaDB. Heb je die explain ook in die docker met dezelfde versie gedraaid?

[GlowMouse]

Het verschil wordt verklaard door de cpu-snelheid en het andere execution plan. Die laatste kan anders zijn enkel omdat je op andere hardware draait.

De query die je geeft is er eentje die je met wat optimaliseren ook in 0.001 seconden kunt laten draaien.

code:

SELECT * FROM toplist, lyrics
LEFT JOIN djtunes ON djtunes.lyric=lyrics.id
WHERE toplist.toplist_genre='Overall' AND toplist.lyric_id=lyrics.id
ORDER BY toplist.dateCreated DESC, toplist.rankToday ASC
LIMIT 10

Je moet daarvoor twee wijzigingen maken in je datamodel:
1. Zorg dat het veld toplist_genre een redelijke maximale lengte heeft. Niet nodig maar wel verstandig.
2. Maak in toplist een extra veld dateCreatedNegative zodat je daarop ASC kunt sorteren (in MySQL 8 hoeft dit niet meer als je de index anders aanmaakt)

Vervolgens zet je één index (niet drie afzonderlijke indexen) op de drie velden (dateCreated,dateCreatedNegative,rankToday).

Het lijkt erop dat je bij de tabel djtunes een index hebt op id. Is dat niet de primary key?

[ Voor 4% gewijzigd door GlowMouse op 02-06-2020 17:55 ]

[Craetive]

pennywiser schreef op dinsdag 2 juni 2020 @ 17:04:
[...]
Die is heel fors, ik weet dat je zei dat je MyIsam gebruikt, maar kennelijk zit er een Innodb component in en is de aanbeveling toch dit 12 GB aan ram te laten gebruiken. Echter was en is dat onmogelijk.

Verder is 5.5 stokoud. Nieuwere Mysql versies hebben tal van performance verbeteringen. Pak Mysql 8 er eens bij op een testbak. Mariadb kan ook, iedereen roept dat die beter is, maar bij Mysql zitten ze ook niet stil.

Dat er nu 2GB wordt gebruikt is een harde setting, het is niet zo dat Mysql zelfstandig extra gaat gebruiken op het moment dat dat nodig mocht blijken.

Dat innodb_buffer_pool_size vrij vors is kan wel kloppen, zoals eerder aangegeven draait er namelijk een test InnoDB-database samen met nog wat andere InnoDB-databases. Die waardes zijn dus niet super reëel.

MariaDB had ik voorheen al een upgrade gegeven naar 10.3 maar daar ging Plesk niet helemaal lekker op. Voor de rest heb je wel gelijk hoor, 5.5 is stokoud maar wordt vanuit CentOS 7 als standaard geleverd.

emnich schreef op dinsdag 2 juni 2020 @ 17:05:
Doe ook eens een test van de diskspeed bijvoorbeeld

Je query lijkt niet CPU intensief dus ik kan me haast niet voorstellen dat de CPU zo'n groot verschil maakt.

Zojuist gedaan, dit zijn de resultaten:
Oude server:

code:

root@webbq:~$ sync; dd if=/dev/zero of=tempfile bs=1M count=1024; sync
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 1.81851 s, 590 MB/s

code:

root@webbq:~$ dd if=tempfile of=/dev/null bs=1M count=1024
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 0.288673 s, 3.7 GB/s

Nieuwe server:

code:

[root@webbq]# sync; dd if=/dev/zero of=tempfile bs=1M count=1024; sync
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 0.6915 s, 1.6 GB/s

code:

[root@webbq]# dd if=tempfile of=/dev/null bs=1M count=1024
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 0.262226 s, 4.1 GB/s

Zou het dus ook niet aan mogen liggen.

emnich schreef op dinsdag 2 juni 2020 @ 17:08:
Je kan overigens ook als test op beide, die beide tabellen met engine MEMORY maken. Dan staan ze in je RAM. Als ze dan vrijwel even snel zijn op beide bakken dan is het vrij zeker de disk snelheid.

Ook is ede executie van beide queries niet gelijk en ik kan me haast niet voorstellen dat dat ligt aan de versie van MySQL/MariaDB. Heb je die explain ook in die docker met dezelfde versie gedraaid?

Ik heb net de toplist-tabel omgezet naar MEMORY en alleen al met deze tabel merk ik direct een enorme boost in performance (0.3824 seconden ingeladen). Wanneer ik nu ook een EXPLAIN op de query gooi blijkt hij een stuk minder rows langs te moeten gaan:

MEMORY:

code:

EXPLAIN SELECT * FROM toplist, lyrics LEFT JOIN djtunes ON djtunes.lyric=lyrics.id WHERE toplist.toplist_genre='Overall' AND toplist.lyric_id=lyrics.id  ORDER BY toplist.dateCreated DESC, toplist.rankToday ASC LIMIT 10;
+------+-------------+---------+--------+------------------------+---------------+---------+------------------------------------+-------+-----------------------------+
| id   | select_type | table   | type   | possible_keys          | key           | key_len | ref                                | rows  | Extra                       |
+------+-------------+---------+--------+------------------------+---------------+---------+------------------------------------+-------+-----------------------------+
|    1 | SIMPLE      | toplist | ref    | lyric_id,toplist_genre | toplist_genre | 99      | const                              | 89911 | Using where; Using filesort |
|    1 | SIMPLE      | lyrics  | eq_ref | PRIMARY,id             | PRIMARY       | 4       | toplist.lyric_id                   |     1 |                             |
|    1 | SIMPLE      | djtunes | ref    | lyric                  | lyric         | 4       | toplist.lyric_id                   |     1 |                             |
+------+-------------+---------+--------+------------------------+---------------+---------+------------------------------------+-------+-----------------------------+

Helaas heb ik niet de mogelijkheid om lyrics/djtunes om te zetten naar MEMORY aangezien er een paar datatypes tussen zitten die MEMORY niet ondersteund. Ik heb net even de laatste versie van de live-versie gedumpd en de waardes teruggezet naar MyISAM met volgende resultaten:

MyISAM:

code:

EXPLAIN SELECT * FROM toplist, lyrics LEFT JOIN djtunes ON djtunes.lyric=lyrics.id WHERE toplist.toplist_genre='Overall' AND toplist.lyric_id=lyrics.id  ORDER BY toplist.dateCreated DESC, toplist.rankToday ASC LIMIT 10;
+------+-------------+---------+--------+------------------------+---------------+---------+------------------------------------+--------+----------------------------------------------------+
| id   | select_type | table   | type   | possible_keys          | key           | key_len | ref                                | rows   | Extra                                              |
+------+-------------+---------+--------+------------------------+---------------+---------+------------------------------------+--------+----------------------------------------------------+
|    1 | SIMPLE      | toplist | ref    | lyric_id,toplist_genre | toplist_genre | 99      | const                              | 336047 | Using index condition; Using where; Using filesort |
|    1 | SIMPLE      | lyrics  | eq_ref | PRIMARY,id             | PRIMARY       | 4       | toplist.lyric_id                   |      1 |                                                    |
|    1 | SIMPLE      | djtunes | ref    | lyric                  | lyric         | 4       | toplist.lyric_id                   |      1 |                                                    |
+------+-------------+---------+--------+------------------------+---------------+---------+------------------------------------+--------+----------------------------------------------------+

Via MySQL 5.1.73 in de Docker container krijg je trouwens dit terug:
MySQL 5.1.73 Docker Container

code:

EXPLAIN SELECT * FROM toplist, lyrics LEFT JOIN djtunes ON djtunes.lyric=lyrics.id WHERE toplist.toplist_genre='Overall' AND toplist.lyric_id=lyrics.id  ORDER BY toplist.dateCreated DESC, toplist.rankToday ASC LIMIT 10;
+----+-------------+---------+--------+------------------------+---------------+---------+------------------------------------+--------+-----------------------------+
| id | select_type | table   | type   | possible_keys          | key           | key_len | ref                                | rows   | Extra                       |
+----+-------------+---------+--------+------------------------+---------------+---------+------------------------------------+--------+-----------------------------+
|  1 | SIMPLE      | toplist | ref    | lyric_id,toplist_genre | toplist_genre | 99      | const                              | 336047 | Using where; Using filesort |
|  1 | SIMPLE      | lyrics  | eq_ref | PRIMARY,id             | PRIMARY       | 4       | toplist.lyric_id                   |      1 |                             |
|  1 | SIMPLE      | djtunes | ref    | lyric                  | lyric         | 4       | toplist.lyric_id                   |      1 |                             |
+----+-------------+---------+--------+------------------------+---------------+---------+------------------------------------+--------+-----------------------------+

Nagenoeg hetzelfde dus, alleen Using index condition wordt daar genegeerd.

GlowMouse schreef op dinsdag 2 juni 2020 @ 17:53:
Het verschil wordt verklaard door de cpu-snelheid en het andere execution plan. Die laatste kan anders zijn enkel omdat je op andere hardware draait.

De query die je geeft is er eentje die je met wat optimaliseren ook in 0.001 seconden kunt laten draaien.

code:

SELECT * FROM toplist, lyrics
LEFT JOIN djtunes ON djtunes.lyric=lyrics.id
WHERE toplist.toplist_genre='Overall' AND toplist.lyric_id=lyrics.id
ORDER BY toplist.dateCreated DESC, toplist.rankToday ASC
LIMIT 10

Je moet daarvoor twee wijzigingen maken in je datamodel:
1. Zorg dat het veld toplist_genre een redelijke maximale lengte heeft. Niet nodig maar wel verstandig.
2. Maak in toplist een extra veld dateCreatedNegative zodat je daarop ASC kunt sorteren (in MySQL 8 hoeft dit niet meer als je de index anders aanmaakt)

Vervolgens zet je één index (niet drie afzonderlijke indexen) op de drie velden (dateCreated,dateCreatedNegative,rankToday).

Het lijkt erop dat je bij de tabel djtunes een index hebt op id. Is dat niet de primary key?

Thanks voor je tips! Het gaat echter om een verouderde applicatie die ik verder niet beheer en ik ben daarom ook niet van plan om aanpassingen in het ontwerp te doen.

Tabel djtunes heeft op dit moment geen primary key ( ) maar alleen een index op lyric. Die tabel is verder flinterdun (3536 records) dus die heeft verder geen enorme invloed op de loadtime (JOIN is eigenlijk ook verouderd)

[gekkie]

Is het niet gewoon (deels) een gevolg van virtualisatie (dure context switches en dan de laatste tijd helemaal met alle side channel mitigaties die vooral virtualisatie heel hard kunnen treffen) ?

[GlowMouse]

FooBarz schreef op dinsdag 2 juni 2020 @ 21:42:
[...]

Ik heb net de toplist-tabel omgezet naar MEMORY en alleen al met deze tabel merk ik direct een enorme boost in performance (0.3824 seconden ingeladen). Wanneer ik nu ook een EXPLAIN op de query gooi blijkt hij een stuk minder rows langs te moeten gaan

Niet blijkt, maar lijkt. Explain geeft slechts een schatting.

Thanks voor je tips! Het gaat echter om een verouderde applicatie die ik verder niet beheer en ik ben daarom ook niet van plan om aanpassingen in het ontwerp te doen.

Verwijderen dan maar die hap. Iets wat 1000x meer resources verbruikt dan nodig is verspilling. Als het zo slecht in elkaar zit, zal het ook wel niet veilig zijn.

[Freeaqingme]

pennywiser schreef op dinsdag 2 juni 2020 @ 17:04:
[...]

Verder is 5.5 stokoud. Nieuwere Mysql versies hebben tal van performance verbeteringen. Pak Mysql 8 er eens bij op een testbak. Mariadb kan ook, iedereen roept dat die beter is, maar bij Mysql zitten ze ook niet stil.

Toch zou ik als oud ook 5.5 gebruikt dat ook gebruiken op nieuw. Totdat je weet waar de performanceverschillen vandaan komen. Doorgaans pak ik ook standaard de laatste versie, maar heb ook wel eens gehad dat de optimizer een belangrijke query net iets minder optimizede dan op 5.5 gebeurde. Toegegeven, de querywas mega-inefficient, maar toevallig dat de oude versie van mysql dat wel op een 'goede' manier optimizede, terwijl de optimizer van - naar ik meen - mysql 8 dat niet deed.

De kans dat dat gebeurt is onwaarschijnlijk, maar het lijkt me dat je in eerste instantie vooral factoren wil uitsluiten.

[pennywiser]

Freeaqingme schreef op woensdag 3 juni 2020 @ 07:29:
[...]


Toch zou ik als oud ook 5.5 gebruikt dat ook gebruiken op nieuw. Totdat je weet waar de performanceverschillen vandaan komen. Doorgaans pak ik ook standaard de laatste versie, maar heb ook wel eens gehad dat de optimizer een belangrijke query net iets minder optimizede dan op 5.5 gebeurde. Toegegeven, de querywas mega-inefficient, maar toevallig dat de oude versie van mysql dat wel op een 'goede' manier optimizede, terwijl de optimizer van - naar ik meen - mysql 8 dat niet deed.

De kans dat dat gebeurt is onwaarschijnlijk, maar het lijkt me dat je in eerste instantie vooral factoren wil uitsluiten.

Ik bedoelde ook ga er vast mee testen, en kijk of het snel wat kan opleveren.

[Craetive]

synoniem schreef op dinsdag 2 juni 2020 @ 14:33:
Van mysql en performance weet ik niet zoveel maar ik ben bij Hezner op een cloud VPS overgestapt gebaseerd op AMD EPYC 2nd Gen met 3 VCPU en kan vertellen dat die behoorlijk snel is voor een niet dedicated server. (En betaalbaarder dan TransIP).

Begrijpelijk! Het probleem is een beetje dan een van de webapplicaties flink wat file storage in beslag neemt. Bij TransIP heb je Big Storage stacks waarmee je voor een tientje extra ruim 2TB aan schijfruimte kan toevoegen. Zo'n betaalbare optie ben ik nog nergens anders tegen gekomen, vandaar dus de keuze voor provider.

Maar dan moet de performance uiteraard ook wel meewerken.

gekkie schreef op dinsdag 2 juni 2020 @ 21:49:
Is het niet gewoon (deels) een gevolg van virtualisatie (dure context switches en dan de laatste tijd helemaal met alle side channel mitigaties die vooral virtualisatie heel hard kunnen treffen) ?

Zou prima kunnen hoor! Ik was echter in de veronderstelling dat de performance hiervan hedendaags een stuk dichter bij elkaar zouden liggen. Vind het ook wel jammer dat je het niet duidelijk terugziet in de metingen, dus ik hoop stiekem dat het gewoon een instelling is die ik over het hoofd zie.

Helaas heb ik tot op heden nog niet de oplossing kunnen vinden.

[Freeaqingme]

FooBarz schreef op donderdag 4 juni 2020 @ 22:15:
[...]
Bij TransIP heb je Big Storage stacks waarmee je voor een tientje extra ruim 2TB aan schijfruimte kan toevoegen. Zo'n betaalbare optie ben ik nog nergens anders tegen gekomen, vandaar dus de keuze voor provider.

Maar dan moet de performance uiteraard ook wel meewerken.


[...]

Draai je die database op BigStorage dan? Of draait die wel gewoon op de SSD's van TransIP?

FooBarz schreef op donderdag 4 juni 2020 @ 22:15:
[...]
Zou prima kunnen hoor! Ik was echter in de veronderstelling dat de performance hiervan hedendaags een stuk dichter bij elkaar zouden liggen. Vind het ook wel jammer dat je het niet duidelijk terugziet in de metingen, dus ik hoop stiekem dat het gewoon een instelling is die ik over het hoofd zie.

Helaas heb ik tot op heden nog niet de oplossing kunnen vinden.

Ik denk niet dat 't 'm in de contextswitches zit van je database. Als je het wel gewoon op SSD's hebt staan zal het feit dat er een paar meter extra kabel tussen zit ook niet direct een probleem zijn (de hypervisor cachet toch van alles en nog wat).
Als het probleem 'm niet zit in de keuze voor BigStorage of andere configuratie van je VPS, dan denk ik dat het eerder zo is dat je VM op een wat oudere server staat, en daarmee dan ook een oudere CPU heeft.

[emnich]

Voor alle zekerheid, je draait de queries, als je de performance test doet, wel met SQL_NO_CACHE toch?

Laat ook nog even je indexen zien (en hoe groot die zijn).

[Craetive]

Freeaqingme schreef op donderdag 4 juni 2020 @ 22:24:
[...]
Draai je die database op BigStorage dan? Of draait die wel gewoon op de SSD's van TransIP?
[...]

Nee, de database draait uiteraard op de SSD.

emnich schreef op vrijdag 5 juni 2020 @ 09:14:
Voor alle zekerheid, je draait de queries, als je de performance test doet, wel met SQL_NO_CACHE toch?

Niet specifiek, maar caching staat uit op de VPS. Op de dedicated staat het wel aan, maar ik herken aan de query tijden of hij een dan een cache waarde ophaalt.

Desalniettemin:
Dedicated server:

code:

mysql> SELECT SQL_NO_CACHE * FROM toplist, lyrics LEFT JOIN djtunes ON djtunes.lyric=lyrics.id WHERE toplist.toplist_genre='Overall' AND toplist.lyric_id=lyrics.id  ORDER BY toplist.dateCreated DESC, toplist.rankToday ASC LIMIT 10;
10 rows in set (0.57 sec)

4vCPU/8GB RAM:

code:

MariaDB []> SELECT SQL_NO_CACHE * FROM toplist, lyrics LEFT JOIN djtunes ON djtunes.lyric=lyrics.id WHERE toplist.toplist_genre='Overall' AND toplist.lyric_id=lyrics.id  ORDER BY toplist.dateCreated DESC, toplist.rankToday ASC LIMIT 10;
10 rows in set (6.63 sec)

emnich schreef op vrijdag 5 juni 2020 @ 09:14:
Laat ook nog even je indexen zien (en hoe groot die zijn).

code:

mysql> SHOW INDEX FROM toplist;
+---------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name      | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| toplist |          1 | dateCreated   |            1 | dateCreated   | A         |        3939 |     NULL | NULL   |      | BTREE      |         |
| toplist |          1 | lyric_id      |            1 | lyric_id      | A         |       33716 |     NULL | NULL   |      | BTREE      |         |
| toplist |          1 | toplist_genre |            1 | toplist_genre | A         |          44 |     NULL | NULL   | YES  | BTREE      |         |
| toplist |          1 | rankToday     |            1 | rankToday     | A         |         100 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.00 sec)

code:

mysql> SHOW INDEX FROM lyrics;
+--------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name         | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| lyrics |          0 | PRIMARY          |            1 | id               | A         |       40124 |     NULL | NULL   |      | BTREE      |         |
| lyrics |          0 | id               |            1 | id               | A         |       40124 |     NULL | NULL   |      | BTREE      |         |
| lyrics |          1 | artist           |            1 | artist           | A         |       20062 |     NULL | NULL   |      | BTREE      |         |
| lyrics |          1 | song             |            1 | song             | A         |       40124 |     NULL | NULL   |      | BTREE      |         |
| lyrics |          1 | genre            |            1 | genre            | A         |          38 |     NULL | NULL   |      | BTREE      |         |
| lyrics |          1 | source           |            1 | source           | A         |       10031 |      333 | NULL   | YES  | BTREE      |         |
| lyrics |          1 | approved         |            1 | approved         | A         |           2 |     NULL | NULL   |      | BTREE      |         |
| lyrics |          1 | user             |            1 | user             | A         |        1604 |     NULL | NULL   |      | BTREE      |         |
| lyrics |          1 | req              |            1 | req              | A         |        2674 |     NULL | NULL   |      | BTREE      |         |
| lyrics |          1 | DateApproved     |            1 | dateApproved     | A         |       40124 |     NULL | NULL   |      | BTREE      |         |
| lyrics |          1 | datedateApproved |            1 | datedateApproved | A         |        4458 |     NULL | NULL   | YES  | BTREE      |         |
| lyrics |          1 | views            |            1 | views            | A         |        6687 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
12 rows in set (0.00 sec)

code:

mysql> SHOW INDEX FROM djtunes;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| djtunes |          1 | lyric    |            1 | lyric       | A         |        3536 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

[ Voor 45% gewijzigd door Craetive op 05-06-2020 13:06 ]

[emnich]

Ik zou als laatste nog even gaan spelen met het weghalen van de ORDER BY columns, eerst allemaal en dan stuk voor stuk toevoegen. Kijk eens of je bij geen ORDER BY een redelijke vergelijkbare performance hebt. Doe dan wel een bijvoorbeeld een LIMIT 10, 100000

Ook kan je even proberen om alle indexen uit te zetten (met `USE INDEX()` ) en kijken hoe de performance zich dan vergelijkt.

Op die manier kan je beter kijken waar de bottleneck is maar het is een raar verhaal. Wij zijn zelf van dedicated servers naar TransIp gegaan en de performance daarvan was echt wel vergelijkbaar. Hoewel je bij TransIp wel dipjes kan is het nooit echt structureel en kan het volgens mij nooit een factor 10 schelen.

[draadloos]

easy-cloud schreef op dinsdag 2 juni 2020 @ 14:54:
Ik weet dat de storage van TransIP niet bij dezelfde server staat als de VPS op draait, hierdoor zit er ook meer latency op.

Klopt, storage bij TransIP is soms belachelijk traag...

[xares]

Doe eens op die vps bij TransIP.

cat /proc/cpuinfo

Dan kan je de kloksnelheid zien.

Mijn vermoeden is dat dit aan de kloksnelheid ligt icm gelimiteerde cpu units/usage op je VPS opgelegd door TransIP.

Kloksnelheid is voor MySQL super belangrijk over het algemeen. Kan je een cpu hebben met 24 cores maar als die maar 2.2 Ghz zijn is dat voor MySQL te langzaam.

[The Eagle]

Als ik naar die sysbench kijk dan zijn dat twee verschillende versies die draaien. Ben je dan geen appels met peren aan het vergelijken?
Verder: heb je na het importeren en/of omzetten van je DB naar de nieuwe resp innodb of isam, je statistieken opnieuw gedraaid? Zo nee zou ik daar eens beginnen, want das de basis van je query optimizer, explain plans en hoe je db rzijn data van disk ophaalt enzo.

Wat ik zelf ook zeker zou willen weten is de performance qua iops van beide servers. Leuk dat er SSD onder zit, maar ik gok zomaar dat de storage multitenant is met een cap op de IOPS. Testen dus.
En beginnen bij het begin. Eerst je platform meten qua cpu, mem en disk, en dan de DB.