Hallo Leute!
Wir haben im Büro einen Server mit MariaDB 10.1.38 von Debian Repository (Stretch). In der Instanz sind 5 Datenbanken, eine davon etwas groß (~25GB), die andere sehr klein.
In der "großen" Datenbank haben wir ein paar Stored Procedures, die innerhalb einer Transaktion Daten von einer Tabelle in eine andere verschiebt (eine Art Aggregationsverfahren). Die Prozedur in sich funktioniert einwandfrei, allerdings wenn diese läuft werden andere Anfragen (auch in anderen Datenbanken!!!) gesperrt, also ich sehe sie in der Prozessliste aber sie tun nichts eine ganze Zeit lang. Die Prozedur selbst läuft mal einige Sekunden und mal mehreren Minuten, obwohl die Daten zu aggregieren sind mehr oder wenige die selben (~30.000 Sätzen).
Hat jemand eine Erklärung oder wenigstens eine Idee, was ich prüfen kann?
Danke Luca Bertoncello (lucabert@lucabert.de)
Am 04.03.2020 15:50, schrieb Andreas Kretschmer:
Am 04.03.20 um 15:14 schrieb Luca Bertoncello:
Hat jemand eine Erklärung oder wenigstens eine Idee, was ich prüfen kann?
ihr könntet eine Umstellung auf PostgreSQL prüfen ;-)
Andreas, dieser Satz hilft überhaupt nicht... Es gibt mehrere Gründen aus dem wir hier MySQL nutzen. Eine Umstellung auf einer anderen Datenbank wäre für uns ein _RIESIGER_ Aufwand, ohne jegliche Garantie, dass das Problem gelöst wird.
Grüße Luca Bertoncello (lucabert@lucabert.de)
Am 04.03.20 um 15:57 schrieb Luca Bertoncello:
Am 04.03.2020 15:50, schrieb Andreas Kretschmer:
Am 04.03.20 um 15:14 schrieb Luca Bertoncello:
Hat jemand eine Erklärung oder wenigstens eine Idee, was ich prüfen kann?
ihr könntet eine Umstellung auf PostgreSQL prüfen ;-)
Andreas, dieser Satz hilft überhaupt nicht... Es gibt mehrere Gründen aus dem wir hier MySQL nutzen. Eine Umstellung auf einer anderen Datenbank wäre für uns ein _RIESIGER_ Aufwand, ohne jegliche Garantie, dass das Problem gelöst wird.
Du hast meinen Smily übersehen ;-)
* prüfe die Engine, vielleicht ist ja MyISAM im Einsatz * THP ist ausgeschaltet?
Andreas
Am 05.03.2020 11:37, schrieb Andreas Kretschmer:
Hallo Andreas
Du hast meinen Smily übersehen ;-)
Vermutlich... zu viel Stress...
- prüfe die Engine, vielleicht ist ja MyISAM im Einsatz
Nein, alle Tabellen sind InnoDB...
- THP ist ausgeschaltet?
# cat /sys/kernel/mm/transparent_hugepage/enabled always [madvise] never
Soll ich das einschalten? Welches Wert soll ich geben?
# uname -a Linux 4.9.0-9-amd64 #1 SMP Debian 4.9.168-1+deb9u4 (2019-07-19) x86_64 GNU/Linux
4 vCPUs so:
vendor_id : GenuineIntel cpu family : 6 model : 44 model name : Westmere E56xx/L56xx/X56xx (Nehalem-C) stepping : 1 microcode : 0x1 cpu MHz : 2397.222 cache size : 4096 KB physical id : 3 siblings : 1 core id : 0 cpu cores : 1 apicid : 3 initial apicid : 3 fpu : yes fpu_exception : yes cpuid level : 11 wp : yes flags : fpu de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 syscall nx lm constant_tsc rep_good nopl pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 x2apic popcnt aes hypervisor lahf_lm kaiser bugs : cpu_meltdown spectre_v1 spectre_v2 spec_store_bypass l1tf mds bogomips : 4794.44 clflush size : 64 cache_alignment : 64 address sizes : 46 bits physical, 48 bits virtual power management:
Danke Luca Bertoncello (lucabert@lucabert.de)
Am 05.03.20 um 11:44 schrieb Luca Bertoncello:
Am 05.03.2020 11:37, schrieb Andreas Kretschmer:
Hallo Andreas
Du hast meinen Smily übersehen ;-)
Vermutlich... zu viel Stress...
- prüfe die Engine, vielleicht ist ja MyISAM im Einsatz
Nein, alle Tabellen sind InnoDB...
- THP ist ausgeschaltet?
# cat /sys/kernel/mm/transparent_hugepage/enabled always [madvise] never
Soll ich das einschalten? Welches Wert soll ich geben?
nein, ausschalten. Ich lehne mich mal weit aus dem Fenster und kopiere einen Teile eines Artikels aus unserer KnowledgeBase:
===
Issue
The system becomes totally unresponsive for a period of time. This may be caused by the operating system attempting to defragment huge memory pages. During this time the systems seems unresponsive and frozen, or the performance of the system (response times etc.) gets very erratic.
*Transparent Huge Pages* (THP) are enabled by default in Red Hat Enterprise Linux and CentOS 6 and 7 for all applications.
------------------------------------------------------------------------
Resolution
Disabling Transparent Huge Pages (THP)
The controls for THP are found in the sysfs (|/sys|) tree under |/sys/kernel/mm/transparent_hugepage| or |/sys/kernel/mm/redhat_transparent_hugepage|, depending on the distribution and version. In the following we describe the first of these.
The values for |/sys/kernel/mm/transparent_hugepage/enabled| can be one of the following:
* |always| - defragment every time huge pages are requested * |madvise| - defragment every time huge pages are requested with |madvise| * |never| - never defragment huge pages
To disable:
|echo never > /sys/kernel/mm/transparent_hugepage/enabled echo never > /sys/kernel/mm/transparent_hugepage/defrag # Depending on linux kernel version, you may need '0' instead of 'no' echo no > /sys/kernel/mm/transparent_hugepage/khugepaged/defrag |
Then, to prevent the changed values from being reset on server reboot you'll need to update the bootloader, typically grub:
===
der Artikel geht noch weiter, wer es lesen will kann ja Kunde werden ;-)
ob das die Ursache für Deine Probleme ist ist aber fraglich. Wenn das reproduzierbar NUR mit Deinen stored procs passiert wird's das wohl nicht sein ...
Andreas
Am 05.03.2020 13:38, schrieb Andreas Kretschmer:
nein, ausschalten. Ich lehne mich mal weit aus dem Fenster und kopiere einen Teile eines Artikels aus unserer KnowledgeBase:
So, gemacht! Keine Änderung... :(
ob das die Ursache für Deine Probleme ist ist aber fraglich. Wenn das reproduzierbar NUR mit Deinen stored procs passiert wird's das wohl nicht sein ...
Es ist eigentlich reproduzierbar, in Sinne von "es passiert immer, wenn ich die Stored Procedure ausführe"... Ich habe mit dstat -f --bw einige Statistik geholt während des Laufes:
39 26 36 0 0 0: 89 4 7 0 0 0: 34 31 35 0 0 0: 26 22 48 3 0 0| 0 616k|3148B 3463B:4434B 3828B: 0 0 | 0 0 |2486 3715 91 5 4 0 0 0: 38 27 34 0 0 0: 31 36 32 0 0 0: 40 21 33 4 0 1| 0 876k| 52k 58k:4092B 3816B: 0 0 | 0 0 |3052 4337 43 15 40 2 0 0: 22 23 55 0 0 0: 41 14 44 0 0 1: 40 8 35 16 0 0| 0 13M| 59k 50k:4866B 4370B: 0 0 | 0 0 |3637 4798 20 1 79 0 0 0: 23 2 74 1 0 0: 0 0 100 0 0 0: 48 3 39 9 0 0| 0 15M| 13k 9382B: 260B 0 : 0 0 | 0 0 | 627 578 0 0 100 0 0 0: 41 2 57 0 0 0: 0 0 100 0 0 0: 44 6 37 13 0 0| 0 21M| 202B 202B: 330B 0 : 0 0 | 0 0 | 429 338 0 1 99 0 0 0: 56 4 38 2 0 0: 30 2 68 0 0 0: 2 0 87 11 0 0| 0 19M|1022B 402B: 260B 0 : 0 0 | 0 0 | 713 533 2 1 97 0 0 0: 1 1 98 0 0 0: 90 4 5 1 0 0: 0 0 90 10 0 0| 0 21M|1078B 3150B: 190B 42B: 0 0 | 0 0 | 429 307 0 0 100 0 0 0: 6 3 90 1 0 0: 82 7 10 1 0 0: 0 0 90 10 0 0| 0 21M| 610B 502B: 570B 42B: 0 0 | 0 0 | 440 282 1 0 99 0 0 0: 0 0 100 0 0 0: 85 6 5 4 0 0: 2 1 88 9 0 0| 0 8692k| 14k 9638B: 330B 0 : 0 0 | 0 0 | 605 655 1 0 99 0 0 0: 0 1 98 1 0 0: 88 7 4 1 0 0: 0 0 89 11 0 0| 0 21M|1190B 1835B: 130B 0 : 0 0 | 0 0 | 479 390 1 1 98 0 0 0: 1 2 96 1 0 0: 88 6 5 1 0 0: 0 0 89 11 0 0| 0 22M| 502B 636B: 260B 0 : 0 0 | 0 0 | 453 437 0 0 99 0 0 1: 1 1 97 1 0 0: 87 7 5 1 0 0: 2 1 86 11 0 0|4096B 22M|1078B 346B: 130B 0 : 0 0 | 0 0 | 462 372 2 1 97 0 0 0: 8 4 87 1 0 0: 82 3 14 1 0 0: 1 0 87 12 0 0| 0 25M|3136B 2928B: 390B 0 : 0 0 | 0 0 | 562 536 9 2 89 0 0 0: 6 6 88 0 0 0: 82 6 11 1 0 0: 5 6 78 11 0 0| 0 22M| 12k 9040B: 130B 0 : 0 0 | 0 0 |1034 1292 14 2 84 0 0 0: 3 2 93 1 0 1: 72 8 19 1 0 0: 7 9 78 6 0 0| 0 15M| 956B 556B: 260B 0 : 0 0 | 0 0 | 849 1000 30 2 68 0 0 0: 4 2 93 0 0 1: 28 2 70 0 0 0: 26 2 65 7 0 0| 0 17M| 538B 1848B:4254B 6354B: 0 0 | 0 0 | 722 579 89 5 5 1 0 0: 1 1 97 1 0 0: 2 1 96 0 0 1: 0 0 89 11 0 0| 0 20M| 956B 396B: 650B 0 : 0 0 | 0 0 | 499 410 64 3 31 2 0 0: 0 0 99 1 0 0: 0 0 100 0 0 0: 28 2 63 7 0 0| 0 22M|1210B 3340B: 260B 0 : 0 0 | 0 0 | 496 391 2 0 98 0 0 0: 1 1 97 1 0 0: 0 0 100 0 0 0: 88 5 3 4 0 0| 0 22M| 12k 7436B: 130B 0 : 0 0 | 0 0 | 558 462 0 0 100 0 0 0: 1 2 94 3 0 0: 81 7 11 1 0 0: 18 1 79 2 0 0| 0 21M|2730B 2021B: 130B 0 : 0 0 | 0 0 | 473 395 0 0 100 0 0 0: 0 0 89 11 0 0: 86 6 7 1 0 0: 6 1 92 0 0 1| 0 17M|1297B 1027B: 520B 0 : 0 0 | 0 0 | 518 453 0 0 100 0 0 0: 2 1 91 6 0 0: 89 5 5 1 0 0: 0 0 100 0 0 0| 0 12M| 360B 1636B: 260B 0 : 0 0 | 0 0 | 410 318 2 1 96 1 0 0: 2 4 83 11 0 0: 88 5 6 1 0 0: 0 0 98 2 0 0| 0 21M|1190B 1920B: 260B 0 : 0 0 | 0 0 | 512 531 0 0 99 1 0 0: 4 4 80 12 0 0: 82 5 12 1 0 0: 1 1 98 0 0 0| 0 29M| 13k 7730B:4260B 6441B: 0 0 | 0 0 | 659 701 0 0 94 6 0 0: 2 3 95 0 0 0: 91 3 5 1 0 0: 0 3 97 0 0 0| 0 14M| 304B 1644B: 650B 0 : 0 0 | 0 0 | 512 395 7 9 76 8 0 0: 45 5 50 0 0 0: 21 7 71 0 0 0: 28 6 66 0 0 0| 0 21M|1256B 2006B: 200B 0 : 0 0 | 0 0 |1261 1459 20 0 78 2 0 0: 7 8 84 1 0 0: 4 8 88 0 0 0: 66 4 29 0 0 1|4096B 6768k| 956B 630B: 390B 0 : 0 0 | 0 0 | 924 1019 98 2 0 0 0 0: 22 18 60 0 0 0: 9 12 76 1 0 2: 38 9 53 0 0 0| 0 5508k|4295B 14k: 260B 0 : 0 0 | 0 0 |2460 3178
Ist es so schlimm? Ja, man sieht, dass die Festplatte schreibt (bis 29MB), aber ich würde sagen, dass die Kiste das schafft, oder verstehe ich komplett falsch?
Was kann ich noch probieren?
Danke Luca Bertoncello (lucabert@lucabert.de)
Am 05.03.2020 13:54, schrieb Luca Bertoncello:
Verzeihung, ich sehe gerade, dass ich die erste Zeile (mit der Erklärung) nicht kopiert habe...
Also:
-------cpu0-usage--------------cpu1-usage--------------cpu2-usage--------------cpu3-usage------ --dsk/vda-- --net/eth0----net/eth1----net/eth2- ---paging-- ---system-- usr sys idl wai hiq siq:usr sys idl wai hiq siq:usr sys idl wai hiq siq:usr sys idl wai hiq siq| read writ| recv send: recv send: recv send| in out | int csw 39 26 36 0 0 0: 89 4 7 0 0 0: 34 31 35 0 0 0: 26 22 48 3 0 0| 0 616k|3148B 3463B:4434B 3828B: 0 0 | 0 0 |2486 3715 91 5 4 0 0 0: 38 27 34 0 0 0: 31 36 32 0 0 0: 40 21 33 4 0 1| 0 876k| 52k 58k:4092B 3816B: 0 0 | 0 0 |3052 4337 43 15 40 2 0 0: 22 23 55 0 0 0: 41 14 44 0 0 1: 40 8 35 16 0 0| 0 13M| 59k 50k:4866B 4370B: 0 0 | 0 0 |3637 4798 20 1 79 0 0 0: 23 2 74 1 0 0: 0 0 100 0 0 0: 48 3 39 9 0 0| 0 15M| 13k 9382B: 260B 0 : 0 0 | 0 0 | 627 578 0 0 100 0 0 0: 41 2 57 0 0 0: 0 0 100 0 0 0: 44 6 37 13 0 0| 0 21M| 202B 202B: 330B 0 : 0 0 | 0 0 | 429 338 0 1 99 0 0 0: 56 4 38 2 0 0: 30 2 68 0 0 0: 2 0 87 11 0 0| 0 19M|1022B 402B: 260B 0 : 0 0 | 0 0 | 713 533 2 1 97 0 0 0: 1 1 98 0 0 0: 90 4 5 1 0 0: 0 0 90 10 0 0| 0 21M|1078B 3150B: 190B 42B: 0 0 | 0 0 | 429 307 0 0 100 0 0 0: 6 3 90 1 0 0: 82 7 10 1 0 0: 0 0 90 10 0 0| 0 21M| 610B 502B: 570B 42B: 0 0 | 0 0 | 440 282 1 0 99 0 0 0: 0 0 100 0 0 0: 85 6 5 4 0 0: 2 1 88 9 0 0| 0 8692k| 14k 9638B: 330B 0 : 0 0 | 0 0 | 605 655 1 0 99 0 0 0: 0 1 98 1 0 0: 88 7 4 1 0 0: 0 0 89 11 0 0| 0 21M|1190B 1835B: 130B 0 : 0 0 | 0 0 | 479 390 1 1 98 0 0 0: 1 2 96 1 0 0: 88 6 5 1 0 0: 0 0 89 11 0 0| 0 22M| 502B 636B: 260B 0 : 0 0 | 0 0 | 453 437 0 0 99 0 0 1: 1 1 97 1 0 0: 87 7 5 1 0 0: 2 1 86 11 0 0|4096B 22M|1078B 346B: 130B 0 : 0 0 | 0 0 | 462 372 2 1 97 0 0 0: 8 4 87 1 0 0: 82 3 14 1 0 0: 1 0 87 12 0 0| 0 25M|3136B 2928B: 390B 0 : 0 0 | 0 0 | 562 536 9 2 89 0 0 0: 6 6 88 0 0 0: 82 6 11 1 0 0: 5 6 78 11 0 0| 0 22M| 12k 9040B: 130B 0 : 0 0 | 0 0 |1034 1292 14 2 84 0 0 0: 3 2 93 1 0 1: 72 8 19 1 0 0: 7 9 78 6 0 0| 0 15M| 956B 556B: 260B 0 : 0 0 | 0 0 | 849 1000 30 2 68 0 0 0: 4 2 93 0 0 1: 28 2 70 0 0 0: 26 2 65 7 0 0| 0 17M| 538B 1848B:4254B 6354B: 0 0 | 0 0 | 722 579 89 5 5 1 0 0: 1 1 97 1 0 0: 2 1 96 0 0 1: 0 0 89 11 0 0| 0 20M| 956B 396B: 650B 0 : 0 0 | 0 0 | 499 410 64 3 31 2 0 0: 0 0 99 1 0 0: 0 0 100 0 0 0: 28 2 63 7 0 0| 0 22M|1210B 3340B: 260B 0 : 0 0 | 0 0 | 496 391 2 0 98 0 0 0: 1 1 97 1 0 0: 0 0 100 0 0 0: 88 5 3 4 0 0| 0 22M| 12k 7436B: 130B 0 : 0 0 | 0 0 | 558 462 0 0 100 0 0 0: 1 2 94 3 0 0: 81 7 11 1 0 0: 18 1 79 2 0 0| 0 21M|2730B 2021B: 130B 0 : 0 0 | 0 0 | 473 395 0 0 100 0 0 0: 0 0 89 11 0 0: 86 6 7 1 0 0: 6 1 92 0 0 1| 0 17M|1297B 1027B: 520B 0 : 0 0 | 0 0 | 518 453 0 0 100 0 0 0: 2 1 91 6 0 0: 89 5 5 1 0 0: 0 0 100 0 0 0| 0 12M| 360B 1636B: 260B 0 : 0 0 | 0 0 | 410 318 2 1 96 1 0 0: 2 4 83 11 0 0: 88 5 6 1 0 0: 0 0 98 2 0 0| 0 21M|1190B 1920B: 260B 0 : 0 0 | 0 0 | 512 531 0 0 99 1 0 0: 4 4 80 12 0 0: 82 5 12 1 0 0: 1 1 98 0 0 0| 0 29M| 13k 7730B:4260B 6441B: 0 0 | 0 0 | 659 701 0 0 94 6 0 0: 2 3 95 0 0 0: 91 3 5 1 0 0: 0 3 97 0 0 0| 0 14M| 304B 1644B: 650B 0 : 0 0 | 0 0 | 512 395 7 9 76 8 0 0: 45 5 50 0 0 0: 21 7 71 0 0 0: 28 6 66 0 0 0| 0 21M|1256B 2006B: 200B 0 : 0 0 | 0 0 |1261 1459 20 0 78 2 0 0: 7 8 84 1 0 0: 4 8 88 0 0 0: 66 4 29 0 0 1|4096B 6768k| 956B 630B: 390B 0 : 0 0 | 0 0 | 924 1019 98 2 0 0 0 0: 22 18 60 0 0 0: 9 12 76 1 0 2: 38 9 53 0 0 0| 0 5508k|4295B 14k: 260B 0 : 0 0 | 0 0 |2460 3178
Ideen?
Danke Luca Bertoncello (lucabert@lucabert.de)
Moin Luca,
dstat kannte ich noch nicht. Alternativ mal atop, ob was rot leuchtet? Taste d für Disk-IO. Und hast du einfach mal mit dd was auf die MySQL-Partition gefeuert um zu sehen was maximal möglich ist? ( https://www.thomas-krenn.com/de/wiki/Linux_I/O_Performance_Tests_mit_dd )
Grüße,
Falk
On 3/5/20 1:55 PM, Luca Bertoncello wrote:
Am 05.03.2020 13:54, schrieb Luca Bertoncello:
Verzeihung, ich sehe gerade, dass ich die erste Zeile (mit der Erklärung) nicht kopiert habe...
Also:
-------cpu0-usage--------------cpu1-usage--------------cpu2-usage--------------cpu3-usage------ --dsk/vda-- --net/eth0----net/eth1----net/eth2- ---paging-- ---system-- usr sys idl wai hiq siq:usr sys idl wai hiq siq:usr sys idl wai hiq siq:usr sys idl wai hiq siq| read writ| recv send: recv send: recv send| in out | int csw 39 26 36 0 0 0: 89 4 7 0 0 0: 34 31 35 0 0 0: 26 22 48 3 0 0| 0 616k|3148B 3463B:4434B 3828B: 0 0 | 0 0 |2486 3715 91 5 4 0 0 0: 38 27 34 0 0 0: 31 36 32 0 0 0: 40 21 33 4 0 1| 0 876k| 52k 58k:4092B 3816B: 0 0 | 0 0 |3052 4337 43 15 40 2 0 0: 22 23 55 0 0 0: 41 14 44 0 0 1: 40 8 35 16 0 0| 0 13M| 59k 50k:4866B 4370B: 0 0 | 0 0 |3637 4798 20 1 79 0 0 0: 23 2 74 1 0 0: 0 0 100 0 0 0: 48 3 39 9 0 0| 0 15M| 13k 9382B: 260B 0 : 0 0 | 0 0 | 627 578 0 0 100 0 0 0: 41 2 57 0 0 0: 0 0 100 0 0 0: 44 6 37 13 0 0| 0 21M| 202B 202B: 330B 0 : 0 0 | 0 0 | 429 338 0 1 99 0 0 0: 56 4 38 2 0 0: 30 2 68 0 0 0: 2 0 87 11 0 0| 0 19M|1022B 402B: 260B 0 : 0 0 | 0 0 | 713 533 2 1 97 0 0 0: 1 1 98 0 0 0: 90 4 5 1 0 0: 0 0 90 10 0 0| 0 21M|1078B 3150B: 190B 42B: 0 0 | 0 0 | 429 307 0 0 100 0 0 0: 6 3 90 1 0 0: 82 7 10 1 0 0: 0 0 90 10 0 0| 0 21M| 610B 502B: 570B 42B: 0 0 | 0 0 | 440 282 1 0 99 0 0 0: 0 0 100 0 0 0: 85 6 5 4 0 0: 2 1 88 9 0 0| 0 8692k| 14k 9638B: 330B 0 : 0 0 | 0 0 | 605 655 1 0 99 0 0 0: 0 1 98 1 0 0: 88 7 4 1 0 0: 0 0 89 11 0 0| 0 21M|1190B 1835B: 130B 0 : 0 0 | 0 0 | 479 390 1 1 98 0 0 0: 1 2 96 1 0 0: 88 6 5 1 0 0: 0 0 89 11 0 0| 0 22M| 502B 636B: 260B 0 : 0 0 | 0 0 | 453 437 0 0 99 0 0 1: 1 1 97 1 0 0: 87 7 5 1 0 0: 2 1 86 11 0 0|4096B 22M|1078B 346B: 130B 0 : 0 0 | 0 0 | 462 372 2 1 97 0 0 0: 8 4 87 1 0 0: 82 3 14 1 0 0: 1 0 87 12 0 0| 0 25M|3136B 2928B: 390B 0 : 0 0 | 0 0 | 562 536 9 2 89 0 0 0: 6 6 88 0 0 0: 82 6 11 1 0 0: 5 6 78 11 0 0| 0 22M| 12k 9040B: 130B 0 : 0 0 | 0 0 |1034 1292 14 2 84 0 0 0: 3 2 93 1 0 1: 72 8 19 1 0 0: 7 9 78 6 0 0| 0 15M| 956B 556B: 260B 0 : 0 0 | 0 0 | 849 1000 30 2 68 0 0 0: 4 2 93 0 0 1: 28 2 70 0 0 0: 26 2 65 7 0 0| 0 17M| 538B 1848B:4254B 6354B: 0 0 | 0 0 | 722 579 89 5 5 1 0 0: 1 1 97 1 0 0: 2 1 96 0 0 1: 0 0 89 11 0 0| 0 20M| 956B 396B: 650B 0 : 0 0 | 0 0 | 499 410 64 3 31 2 0 0: 0 0 99 1 0 0: 0 0 100 0 0 0: 28 2 63 7 0 0| 0 22M|1210B 3340B: 260B 0 : 0 0 | 0 0 | 496 391 2 0 98 0 0 0: 1 1 97 1 0 0: 0 0 100 0 0 0: 88 5 3 4 0 0| 0 22M| 12k 7436B: 130B 0 : 0 0 | 0 0 | 558 462 0 0 100 0 0 0: 1 2 94 3 0 0: 81 7 11 1 0 0: 18 1 79 2 0 0| 0 21M|2730B 2021B: 130B 0 : 0 0 | 0 0 | 473 395 0 0 100 0 0 0: 0 0 89 11 0 0: 86 6 7 1 0 0: 6 1 92 0 0 1| 0 17M|1297B 1027B: 520B 0 : 0 0 | 0 0 | 518 453 0 0 100 0 0 0: 2 1 91 6 0 0: 89 5 5 1 0 0: 0 0 100 0 0 0| 0 12M| 360B 1636B: 260B 0 : 0 0 | 0 0 | 410 318 2 1 96 1 0 0: 2 4 83 11 0 0: 88 5 6 1 0 0: 0 0 98 2 0 0| 0 21M|1190B 1920B: 260B 0 : 0 0 | 0 0 | 512 531 0 0 99 1 0 0: 4 4 80 12 0 0: 82 5 12 1 0 0: 1 1 98 0 0 0| 0 29M| 13k 7730B:4260B 6441B: 0 0 | 0 0 | 659 701 0 0 94 6 0 0: 2 3 95 0 0 0: 91 3 5 1 0 0: 0 3 97 0 0 0| 0 14M| 304B 1644B: 650B 0 : 0 0 | 0 0 | 512 395 7 9 76 8 0 0: 45 5 50 0 0 0: 21 7 71 0 0 0: 28 6 66 0 0 0| 0 21M|1256B 2006B: 200B 0 : 0 0 | 0 0 |1261 1459 20 0 78 2 0 0: 7 8 84 1 0 0: 4 8 88 0 0 0: 66 4 29 0 0 1|4096B 6768k| 956B 630B: 390B 0 : 0 0 | 0 0 | 924 1019 98 2 0 0 0 0: 22 18 60 0 0 0: 9 12 76 1 0 2: 38 9 53 0 0 0| 0 5508k|4295B 14k: 260B 0 : 0 0 | 0 0 |2460 3178
Ideen?
Danke Luca Bertoncello (lucabert@lucabert.de)
Am 05.03.2020 14:02, schrieb Falk Herzog:
Hallo Falk,
dstat kannte ich noch nicht. Alternativ mal atop, ob was rot leuchtet? Taste d für Disk-IO.
Und was mir einfällt ist, dass WRDSK bei 190M ist... Ist es zu viel?
Und hast du einfach mal mit dd was auf die MySQL-Partition gefeuert um zu sehen was maximal möglich ist? ( https://www.thomas-krenn.com/de/wiki/Linux_I/O_Performance_Tests_mit_dd )
# dd if=/dev/zero of=/root/testfile bs=1G count=1 oflag=direct 1+0 records in 1+0 records out 1073741824 bytes (1.1 GB, 1.0 GiB) copied, 3.46195 s, 310 MB/s
(wir haben nur eine Partition, also /root ist auf derselben Partition wie /var/lib/mysql)
Also, ich sehe, dass die Kiste in der Lage ist, 310MB/s zu schreiben. In diesem Sinne wären die 29MB von MySQL kaum was, oder verstehe ich falsch?
Danke Luca Bertoncello (lucabert@lucabert.de)
Siehst Du richtig, wenn er 300MB schreiben kann, ist das zumindest wohl nicht der Flaschenhals.
Grüße
On 3/5/20 2:10 PM, Luca Bertoncello wrote:
Am 05.03.2020 14:02, schrieb Falk Herzog:
Hallo Falk,
dstat kannte ich noch nicht. Alternativ mal atop, ob was rot leuchtet? Taste d für Disk-IO.
Und was mir einfällt ist, dass WRDSK bei 190M ist... Ist es zu viel?
Und hast du einfach mal mit dd was auf die MySQL-Partition gefeuert um zu sehen was maximal möglich ist? ( https://www.thomas-krenn.com/de/wiki/Linux_I/O_Performance_Tests_mit_dd )
# dd if=/dev/zero of=/root/testfile bs=1G count=1 oflag=direct 1+0 records in 1+0 records out 1073741824 bytes (1.1 GB, 1.0 GiB) copied, 3.46195 s, 310 MB/s
(wir haben nur eine Partition, also /root ist auf derselben Partition wie /var/lib/mysql)
Also, ich sehe, dass die Kiste in der Lage ist, 310MB/s zu schreiben. In diesem Sinne wären die 29MB von MySQL kaum was, oder verstehe ich falsch?
Danke Luca Bertoncello (lucabert@lucabert.de)
Am 05.03.2020 14:23, schrieb Falk Herzog:
Siehst Du richtig, wenn er 300MB schreiben kann, ist das zumindest wohl nicht der Flaschenhals.
Dann bin ich am Ende mit meinem Latein. Und auch mit dem Griechisch... Wenn jemand eine Idee hat, dann bitte!
Danke Luca Bertoncello (lucabert@lucabert.de)
Am 05.03.2020 14:25, schrieb Luca Bertoncello:
Am 05.03.2020 14:23, schrieb Falk Herzog:
Siehst Du richtig, wenn er 300MB schreiben kann, ist das zumindest wohl nicht der Flaschenhals.
Dann bin ich am Ende mit meinem Latein. Und auch mit dem Griechisch... Wenn jemand eine Idee hat, dann bitte!
Vielleicht hatte ich doch noch einen Satz auf Latein übrig...
Ich habe also probiert temporär die ForeignKey-Prüfung zu deaktivieren. Pof! Es wird nichts mehr blockiert...
Kurz über unsere DB-Struktur: - Tabelle "meta" mit statischen Daten (eine autoinkrementelle ID als PK) - Tabellen "value_current" und "value_aggregat1" mit den Werten und eine Referenz an meta. Diese ist die FK, natürlich
Sobald ich SET FOREIGN_KEY_CHECKS=0; bei der Aggregation der Daten habe, wird keine fremde SQL-Anfrage mehr blockiert... Ich frage mich, ob es aber eine bessere Lösung gibt, denn, so wie ich weiß, ich kann diese Prüfung nur "instanzweit" deaktivieren, also während der Aggregation wäre es theoretisch möglich Daten in "value_current" einzutragen, die keine entsprechenden Satz in "meta" haben...
Vorschläge?
Danke Luca Bertoncello (lucabert@lucabert.de)
Am 05.03.20 um 14:45 schrieb Luca Bertoncello:
Vielleicht hatte ich doch noch einen Satz auf Latein übrig...
Ich habe also probiert temporär die ForeignKey-Prüfung zu deaktivieren. Pof! Es wird nichts mehr blockiert...
Kurz über unsere DB-Struktur:
- Tabelle "meta" mit statischen Daten (eine autoinkrementelle ID als PK)
- Tabellen "value_current" und "value_aggregat1" mit den Werten und
eine Referenz an meta. Diese ist die FK, natürlich
Sobald ich SET FOREIGN_KEY_CHECKS=0; bei der Aggregation der Daten habe, wird keine fremde SQL-Anfrage mehr blockiert... Ich frage mich, ob es aber eine bessere Lösung gibt, denn, so wie ich weiß, ich kann diese Prüfung nur "instanzweit" deaktivieren, also während der Aggregation wäre es theoretisch möglich Daten in "value_current" einzutragen, die keine entsprechenden Satz in "meta" haben...
laut https://stackoverflow.com/questions/8538636/does-mysql-foreign-key-checks-af... betrifft das nur die aktuelle Session, es geht aber auch global zu setzen.
Gruselig.
Andreas
Am 05.03.2020 um 17:54 schrieb Andreas Kretschmer:
laut https://stackoverflow.com/questions/8538636/does-mysql-foreign-key-checks-af... betrifft das nur die aktuelle Session, es geht aber auch global zu setzen.
Zumindest mit MariaDB gibt es die Möglichkeit mit SET @@SESSION@FOREIGN_KEY = 0 (oder ähnlich, bin nicht mehr im Büro). Ein paar Tests haben mir bestätigt, dass in einer anderen Session ist die Foreign-Key-Prüfung aktiv...
Grüße Luca
On Wed, Mar 4, 2020 at 3:15 PM Luca Bertoncello lucabert@lucabert.de wrote:
Hallo Leute!
Wir haben im Büro einen Server mit MariaDB 10.1.38 von Debian Repository (Stretch). In der Instanz sind 5 Datenbanken, eine davon etwas groß (~25GB), die andere sehr klein.
In der "großen" Datenbank haben wir ein paar Stored Procedures, die innerhalb einer Transaktion Daten von einer Tabelle in eine andere verschiebt (eine Art Aggregationsverfahren). Die Prozedur in sich funktioniert einwandfrei, allerdings wenn diese läuft werden andere Anfragen (auch in anderen Datenbanken!!!) gesperrt, also ich sehe sie in der Prozessliste aber sie tun nichts eine ganze Zeit lang. Die Prozedur selbst läuft mal einige Sekunden und mal mehreren Minuten, obwohl die Daten zu aggregieren sind mehr oder wenige die selben (~30.000 Sätzen).
Hat jemand eine Erklärung oder wenigstens eine Idee, was ich prüfen kann?
table locking ist in stored procedure gar nicht erlaubt bei mariadb. so ein verhalten kenne ich nicht. ist die server last dabei einfach so hoch dass der server einfach nicht reagiert? kannst du von remote dann nicht mehr auf die db zugreifen oder auch nicht von localhost?
Danke Luca Bertoncello (lucabert@lucabert.de)
Am 04.03.2020 um 19:42 schrieb Markus Bergholz:
Hallo Markus
table locking ist in stored procedure gar nicht erlaubt bei mariadb. so ein verhalten kenne ich nicht.
Ich mache es auch nicht, zumindest nicht explizit/gewollt...
ist die server last dabei einfach so hoch dass der server einfach nicht reagiert?
Nein! 0.5-1, mit 4 CPUs...
kannst du von remote dann nicht mehr auf die db zugreifen oder auch nicht von localhost?
Ich kann mich anmelden und die Prozesse der DB sehen, aber eine Anfrage dauert ewig...
Ideen?
Danke Luca Bertoncello (lucabert@lucabert.de)
Am 04.03.2020 um 21:10 schrieb ronny@seffner.de:
Nein! 0.5-1, mit 4 CPUs...
Die Last kann doch auch auf dem Storage sein.
Es sah nicht wirklich so aus, dass die Kiste viel gemacht hat... Ich muss aber zugeben, dass ich auch keine Last auf der Festplatte gemessen habe... Man muss aber auch sagen, dass der Server eine VM ist, und die Festplatte eine Image-Datei auf der NetApp ist, also es erscheint mir schwer zu glauben, dass gerade das das Problem sein kann...
Danke Luca Bertoncello (lucabert@lucabert.de)
On Wed, Mar 4, 2020 at 9:15 PM Luca Bertoncello lucabert@lucabert.de wrote:
Am 04.03.2020 um 21:10 schrieb ronny@seffner.de:
Nein! 0.5-1, mit 4 CPUs...
Die Last kann doch auch auf dem Storage sein.
Es sah nicht wirklich so aus, dass die Kiste viel gemacht hat... Ich muss aber zugeben, dass ich auch keine Last auf der Festplatte gemessen habe... Man muss aber auch sagen, dass der Server eine VM ist, und die Festplatte eine Image-Datei auf der NetApp ist, also es erscheint mir schwer zu glauben, dass gerade das das Problem sein kann...
oh, das hört sich aber für eine db sehr inperformant an! wenn das stored procedure 30k zeilen aktualisiert, ist das gesamte remote i/o geblockt. wie viel ram hat die kiste? innodb_pool_buffer_size? passt die db in den ram?
Danke Luca Bertoncello (lucabert@lucabert.de)
Am 05.03.2020 08:51, schrieb Markus Bergholz:
Hallo Markus
oh, das hört sich aber für eine db sehr inperformant an!
Das habe ich auch gedacht... Allerdings ist es nicht immer so... Heute früh habe ich wieder probiert: 1938981 Zeilen in 21 Sekunden... Trotzdem konnten weitere SQL-Anfragen (INSERT) nicht sofort durchgeführt werden.
Zur Klärung: 1) die SQL-Anfragen, die nicht sofort durchgeführt werden können, sind INSERT in eine Tabelle (value_current), die vom Stored Procedure auch bearbeitet wird 2) die Stored Procedure _LIEST_ einige Daten aus der Tabelle value_current und speichert sie in eine andere Tabelle (value_aggregat1), dann _LIEST_ die restliche Daten aus value_current und speichert sie in eine temporäre Tabelle (value_current_1), die später per RENAME in value_current geändert wird (die frühere value_current wird in value_current_old umbenannt). Schließlich wird die value_current_old per DROP vernichtet.
wenn das stored procedure 30k zeilen aktualisiert, ist das gesamte remote i/o geblockt.
Ich könnte es mir vorstellen, dass eine kurze Sperrung während der RENAME stattfindet, das ist aber nicht der Fall... Die Sperrung findet tatsächlich während der Lesung aus value_current und Speicherung in value_aggregat1 bzw. value_current_1. Das ist für mich unerklärlich... Und dass auch die Anfragen an _ANDEREN_ Datenbanken deswegen warten müssen kann ich mir auch nicht erklären...
wie viel ram hat die kiste? innodb_pool_buffer_size? passt die db in den ram?
Der Server hat 24GB RAM. Die Einstellungen sind:
innodb_buffer_pool_size = 16G innodb_buffer_pool_instances = 4 innodb_log_file_size = 1G innodb_log_buffer_size = 16M innodb_file_per_table = 1 innodb_io_capacity = 2000 innodb_read_io_threads = 128 innodb_thread_concurrency = 0 innodb_write_io_threads = 128 innodb_use_native_aio = 0
Vorher war innodb_buffer_pool_size war nur 8GB. Nach der Verdoppelung dauert die Stored Procedure deutlich weniger, aber die Zugriffe werden weiterhin gesperrt...
Ideen?
Danke Luca Bertoncello (lucabert@lucabert.de)
On Thu, Mar 5, 2020 at 9:06 AM Luca Bertoncello lucabert@lucabert.de wrote:
Am 05.03.2020 08:51, schrieb Markus Bergholz:
Hallo Markus
oh, das hört sich aber für eine db sehr inperformant an!
Das habe ich auch gedacht... Allerdings ist es nicht immer so... Heute früh habe ich wieder probiert: 1938981 Zeilen in 21 Sekunden... Trotzdem konnten weitere SQL-Anfragen (INSERT) nicht sofort durchgeführt werden.
Zur Klärung:
- die SQL-Anfragen, die nicht sofort durchgeführt werden können, sind
INSERT in eine Tabelle (value_current), die vom Stored Procedure auch bearbeitet wird 2) die Stored Procedure _LIEST_ einige Daten aus der Tabelle value_current und speichert sie in eine andere Tabelle (value_aggregat1), dann _LIEST_ die restliche Daten aus value_current und speichert sie in eine temporäre Tabelle (value_current_1), die später per RENAME in value_current geändert wird (die frühere value_current wird in value_current_old umbenannt). Schließlich wird die value_current_old per DROP vernichtet.
das rename kannst du dir sparen, wenn du einfach eine view value_current anlegst, die dann auf die richtig value_current tabelle zeigt. unter oracle heißt das synonym. oder mysql/mariadb gibt es das leider nicht. aber einen ähnlichen workflow haben wir auch mit 27mio zeilen. das arbeiten mit der view ohne rename beschleunigt den gesamt prozess drastisch bei uns. das funktioniet allerdings dann nur für lesende prozesse. wenn ihr schreibende habt, müsstest du die zugrunde liegende tabelle dann dynamisch ermitteln und verwenden.
wenn das stored procedure 30k zeilen aktualisiert, ist das gesamte remote i/o geblockt.
Ich könnte es mir vorstellen, dass eine kurze Sperrung während der RENAME stattfindet, das ist aber nicht der Fall... Die Sperrung findet tatsächlich während der Lesung aus value_current und Speicherung in value_aggregat1 bzw. value_current_1. Das ist für mich unerklärlich... Und dass auch die Anfragen an _ANDEREN_ Datenbanken deswegen warten müssen kann ich mir auch nicht erklären...
wie viel ram hat die kiste? innodb_pool_buffer_size? passt die db in den ram?
Der Server hat 24GB RAM. Die Einstellungen sind:
innodb_buffer_pool_size = 16G innodb_buffer_pool_instances = 4 innodb_log_file_size = 1G innodb_log_buffer_size = 16M innodb_file_per_table = 1 innodb_io_capacity = 2000 innodb_read_io_threads = 128 innodb_thread_concurrency = 0 innodb_write_io_threads = 128 innodb_use_native_aio = 0
Vorher war innodb_buffer_pool_size war nur 8GB. Nach der Verdoppelung dauert die Stored Procedure deutlich weniger, aber die Zugriffe werden weiterhin gesperrt...
das mit dem gesperrten zugriff erschließt sich mir nicht. ich habe da auch leider keine andere idee und habe nach wie vor den remote storage in verdacht.
Ideen?
Danke Luca Bertoncello (lucabert@lucabert.de)
Am 05.03.2020 09:18, schrieb Markus Bergholz:
das rename kannst du dir sparen, wenn du einfach eine view value_current anlegst, die dann auf die richtig value_current tabelle zeigt. unter oracle heißt das synonym. oder mysql/mariadb gibt es das leider nicht. aber einen ähnlichen workflow haben wir auch mit 27mio zeilen.
Wir müssen aber auch dort schreiben, nicht nur lesen...
das arbeiten mit der view ohne rename beschleunigt den gesamt prozess drastisch bei uns. das funktioniet allerdings dann nur für lesende prozesse. wenn ihr schreibende habt, müsstest du die zugrunde liegende tabelle dann dynamisch ermitteln und verwenden.
Genau! Siehe oben...
das mit dem gesperrten zugriff erschließt sich mir nicht. ich habe da auch leider keine andere idee und habe nach wie vor den remote storage in verdacht.
Schade... Aber mit iotop habe ich auch nichts verdächtiges gesehen... Außerdem, dass ein NetApp nicht hinterher kommt ist schon schwer zu glauben... :)
Danke Luca Bertoncello (lucabert@lucabert.de)
lug-dd@mailman.schlittermann.de
-
Andreas Kretschmer -
Falk Herzog -
Luca Bertoncello -
Markus Bergholz -
ronny＠seffner.de