My MySQL procedure is randomly running really slow

Question

I'm running a MySQL database on a Raspberry Pi 3b+ and I'm using some stored procedures. If I run them, just randomly they are really slow.

Here is my code:

DROP procedure IF EXISTS `Zeiterfassung`.`auswertung_jahr`;
DELIMITER $$
CREATE PROCEDURE `Zeiterfassung`.`auswertung_jahr` (IN `var_benutzer_id` INT(11), IN `var_jahr` INT(11))
BEGIN
#Variablen
DECLARE var_letzte_id, var_auswertung_jahr_id, var_sum_arbeitszeit_soll, var_sum_arbeitszeit_ist, var_sum_arbeitszeit_saldo, var_ferien_tage, var_diff_tage, var_arbeitspensum INT DEFAULT 0;
DECLARE var_sum_ferien_ist, var_sum_ferien_soll, var_sum_ferien_saldo, var_sum_krank_saldo, var_ferien_uebertrag, var_arbeitszeit_uebertrag FLOAT DEFAULT 0;
DECLARE var_eintritt, var_austritt, var_datum_von DATE;

#Abfrage, dieser Monat
SELECT auswertung_jahr_id INTO var_auswertung_jahr_id FROM auswertung_jahr WHERE jahr = var_jahr AND benutzer_id = var_benutzer_id;
IF var_auswertung_jahr_id = 0 THEN #wenn Jahr vorhanden
#wenn kein Jahr vorhanden Eintrag erstellen
    INSERT INTO auswertung_jahr (benutzer_id, jahr) VALUES (var_benutzer_id, var_jahr);
    SET var_letzte_id = LAST_INSERT_ID();
ELSE
    SET var_letzte_id = var_auswertung_jahr_id;
END IF;

#Eintritt, Austritt wird gebraucht, um Anteil an Ferien in diesem Jahr zu berechnen.
SELECT
benutzer.eintritt, benutzer.austritt, benutzer.uebertrag_ueberzeit, benutzer.uebertrag_ferien, benutzer.arbeitspensum, benutzer.ferien_tage
INTO
var_eintritt, var_austritt, var_arbeitszeit_uebertrag, var_ferien_uebertrag, var_arbeitspensum, var_ferien_tage
FROM benutzer
WHERE benutzer.benutzer_id = var_benutzer_id;

#Summen für diesen Monat werden ausgerechnet
SELECT
IFNULL( SUM(arbeitszeit_soll), 0 ), IFNULL( SUM(arbeitszeit_ist), 0 ), IFNULL( SUM(ferien_saldo), 0 ), IFNULL( SUM(krank_saldo), 0 )
INTO
var_sum_arbeitszeit_soll, var_sum_arbeitszeit_ist, var_sum_ferien_ist, var_sum_krank_saldo
FROM
auswertung_monat
WHERE benutzer_id = var_benutzer_id AND jahr = var_jahr;

#Ferien SOLL ausrechnen
#Eintritt und Austritt im selben Jahr
IF var_jahr = YEAR(var_eintritt) AND var_jahr = YEAR(var_austritt) THEN
    SELECT DATEDIFF(var_austritt, var_eintritt) INTO var_diff_tage;
    SELECT var_ferien_tage / 365 * (var_diff_tage + 1) INTO var_sum_ferien_soll;
#Nur Eintritt in diesem Jahr
ELSEIF var_jahr = YEAR(var_eintritt) THEN
    #Eintritt
    SELECT CONCAT(var_jahr,'-12-31') INTO var_datum_von;
    SELECT DATEDIFF(var_datum_von, var_eintritt) INTO var_diff_tage;
    SELECT var_ferien_tage / 365 * (var_diff_tage + 1) INTO var_sum_ferien_soll;
#Nur Austritt in diesem Jahr
ELSEIF var_jahr = YEAR(var_austritt) THEN
    SELECT CONCAT(var_jahr,'-01-01') INTO var_datum_von;
    SELECT DATEDIFF(var_austritt, var_datum_von) INTO var_diff_tage;
    SELECT var_ferien_tage / 365 * (var_diff_tage + 1) INTO var_sum_ferien_soll;
#Sonst wurde ganzes Jahr gearbeitet = volle Anzahl Ferien
ELSE
    SELECT var_ferien_tage INTO var_sum_ferien_soll;
END IF;

#Bei Teilzeitarbeit wird das Ferien SOLL noch heruntergerechnet
SET var_sum_ferien_soll = var_sum_ferien_soll / 100 * var_arbeitspensum;

#Nach dem Prozentualen Ferien Ergebnis wird noch der Übertrag dazugerechnet, falls dieses Jahr das Eintrittsjah ist
IF var_jahr = YEAR(var_eintritt) THEN
    #Übertrag von Ferien aus z.B. anderer Software werden dazugerechnet aber nur einmalig (Eintrittsjahr)
    UPDATE
    auswertung_jahr
    SET
    ferien_uebertrag = var_ferien_uebertrag,
    arbeitszeit_uebertrag = var_arbeitszeit_uebertrag * 3600
    WHERE
    auswertung_jahr_id = var_letzte_id;
END IF;

#Ferien SALDO ausrechnen
SET var_sum_ferien_saldo = var_sum_ferien_soll - var_sum_ferien_ist;

#Arbeitszeit SALDO ausrechnen
SET var_sum_arbeitszeit_saldo = var_sum_arbeitszeit_soll - var_sum_arbeitszeit_ist;

#Summen eintragen
UPDATE
auswertung_jahr
SET
arbeitszeit_soll = var_sum_arbeitszeit_soll,
arbeitszeit_ist = var_sum_arbeitszeit_ist,
arbeitszeit_saldo = var_sum_arbeitszeit_saldo,
ferien_ist = var_sum_ferien_ist,
ferien_soll = var_sum_ferien_soll,
ferien_saldo = var_sum_ferien_saldo,
krank_saldo = var_sum_krank_saldo
WHERE
auswertung_jahr_id = var_letzte_id;
END$$

I think it can't have something to do with the code because I can execute the procedure and mostly its very fast. Then if I repeat the procedure with the exact same data it randomly slow.

Here is a picture where I executed exactly the same procedure a few times in a row with exactly the same data:

If I look at the system-load with TOP there is nothing else using power..

Here is an example from slow-log:

    CALL auswertung_jahr(5,2019);
    # Time: 190430 20:13:43
    # User@Host: root[root] @ localhost []
    # Thread_id: 2  Schema: Zeiterfassung  QC_hit: No
    # Query_time: 3.389289  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 5
    # Rows_affected: 0
    SET timestamp=1556648023;
    CALL auswertung_jahr(5,2019);
    # Time: 190430 20:13:44
    # User@Host: root[root] @ localhost []
    # Thread_id: 2  Schema: Zeiterfassung  QC_hit: No
    # Query_time: 0.186437  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 5
    # Rows_affected: 0
    SET timestamp=1556648024;

First over 3 seconds, second 0.18 seconds

I disabled the cache:

SET SESSION query_cache_type=0;

When posting stored procedures, you should always include the actual CREATE PROCEDURE complete with parameters. That said, something to consider when checking this kind of performance is that MySQL can cache query results, so that consecutive executions of the exact same query are not always actually completely reprocessed. — Uueerdo, Apr 30 '19 at 17:44
Ok, I attached the full code sorry for that. I know that caching can make some difference but 8 seconds? I also disabled caching for testing nothing changed.. — dida110, Apr 30 '19 at 17:55
I would [enable slow query](https://stackoverflow.com/a/22609627/460557) logs and set it to 1 second then inspect it, you may be able to see exactly which query is running slow on your procedure — Jorge Campos, Apr 30 '19 at 18:03
Ok, my guess is that the branching of you procedure is causing the issue, I mean, it rely on times/dates to run/not run some queries... What I would do then would be create a table logtable(executionstart, placed_called, timestamp), then I would create a variable in the procedure code `var_executionstart = now()`; and add an insert command on all steps of the procedure like `insert into logtable values (var_executionstart, '#Abfrage, dieser Monat', now());` ... `insert into logtable values (var_executionstart, '#wenn kein Jahr vorhanden Eintrag erstellen', now());` and then you inspect the log — Jorge Campos, Apr 30 '19 at 18:52
Are there any Triggers? Views? What Engine? Please provide `SHOW CREATE TABLE`. — Rick James, May 02 '19 at 04:09
I tested a lot and I'm more confused than before. There are some triggers but I deleted them all nothing changed. Then I was doing an normal insert just for testing and I had the same problem. `MariaDB [Zeiterfassung]> INSERT INTO speed_test (integer_value) VALUES (5); Query OK, 1 row affected (0.50 sec) MariaDB [Zeiterfassung]> INSERT INTO speed_test (integer_value) VALUES (5); Query OK, 1 row affected (2.25 sec) MariaDB [Zeiterfassung]> INSERT INTO speed_test (integer_value) VALUES (5); Query OK, 1 row affected (0.16 sec) ` As you can see for the same insert once 0.16 sec. once 2.25 — dida110, May 02 '19 at 15:06

score 0 · Answer 1 · answered May 06 '19 at 09:45

I'm pretty sure now, that this slow performance, is normal with Raspberry Pi and InnoDB... If I test the same inserts with an MyISAM table it's much faster and there are inserts which suddenly takes more than 2 seconds instead 0.17 second.

Here an example with InnoDB:

Here an example with MyISAM:

The difference is tremendous!

My MySQL procedure is randomly running really slow

1 Answers1