[Maria-discuss] R: Mariadb 10.1.13 slow stored procedures
Stored procedures are slow in MariaDB and MySQL. I reported a bug for this, and Elena Stepanova's comment explains the reason, or maybe one of the reasons. The bug is: https://jira.mariadb.org/browse/MDEV-8254 MariaDB 10.1 is not slower than other versions. From my tests, MySQL 5.7 is almost thrice slower (wtf...), and in one case it caused the OOM to kill mysqld (wtf!?!?). Suggestion: try to avoid using @user_variables. Replace them with local_variables. NEW. and OLD. variables in triggers are ok. Queries are ok. I think that the only problem is access to variables. Regards, Federico -------------------------------------------- Mar 19/4/16, Bruce Carlson <Bruce.Carlson@nepean.com> ha scritto: Oggetto: [Maria-discuss] Mariadb 10.1.13 slow stored procedures A: "maria-discuss@lists.launchpad.net" <maria-discuss@lists.launchpad.net> Data: Martedì 19 Aprile 2016, 01:24 HI All, Just a quick question to start with, does anybody know why MariaDB 10.1.13 is extremely slow running stored procedures. I have searched for as much as I can find and have made sure the database and table character sets and compilation seta are identical but still stored procedures with lots of joins and internal variables is running hundreds of times slower than the old version 5.3. I have checked all system variables and they are identical in both systems (where they exist in both systems) Any assistance or advice would be greatly appreciated. If I need to supply more information please let me know. Both versions are running on windows server 2008 R2 Kind Regards, Bruce Carlson -----Segue allegato----- _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Thanks for that Federico, I read a little more on the net including the bug report you pointed me to however I did a little more digging and found the following:- Using one of the 476 stored procedures I have in my database, when the procedure passes the command string to the database it adds the following highlighted bits:- SELECT SUM(tblsub.fieldX-tblsub.fieldy) AS Bal FROM tblsub join tblmain on tblsub.keyfield=tblmain.keyfield WHERE tblsub.criteria1= NAME_CONST('refnum',_utf8'S000730128000' COLLATE 'utf8_general_ci') and tblmain.criteria2<5; ‘refnum’ is a passed in parameter. The procedeure is a function and returns a double. When I run this in a client (heidiSQL) it takes between 1 minute and 13 seconds (slowest) to 40.451 seconds (fastest) When I change the where clause to read:- WHERE tblsub.criteria1= NAME_CONST('refnum',_latin1'S000730128000' COLLATE 'latin1_swedish_ci') and tblmain.criteria2<5; The query takes 0.078 seconds (slowest) 0.045 (fastest) The following system character set variables are set to “latin1” but stored procedures still try and convert where criteria or passed in parameters to ‘utf8’ Character_set_client Character_set_connection Character_set_database Character_set_results Character_set_server All local tables are InnoDB with latin1_swedish_ci default collation therefore I assume they are stored using the latin1 character set. Is there any way to force MariaDB to use latin1 in stored procedures? Also I must point out that this is happening in MariaDB 10.1.7 and 10.1.13 that I’ve tested but does not happen in MariaDB 5.3.4 which has been my production version since 5.3.4 was released and I must add it has never failed. I’m trying to upgrade to 10.1.xxx to take advantage of the connect engine which I have tested for several months now and found to be extremely useful. However I can not put version 10.1.xxx into service until this issue is sorted. Kind Regards, Bruce Carlson -----Original Message----- From: Federico Razzoli [mailto:federico_raz@yahoo.it] Sent: Tuesday, 19 April 2016 10:41 AM To: maria-discuss@lists.launchpad.net; Bruce Carlson Subject: R: [Maria-discuss] Mariadb 10.1.13 slow stored procedures Stored procedures are slow in MariaDB and MySQL. I reported a bug for this, and Elena Stepanova's comment explains the reason, or maybe one of the reasons. The bug is: https://jira.mariadb.org/browse/MDEV-8254 MariaDB 10.1 is not slower than other versions. From my tests, MySQL 5.7 is almost thrice slower (wtf...), and in one case it caused the OOM to kill mysqld (wtf!?!?). Suggestion: try to avoid using @user_variables. Replace them with local_variables. NEW. and OLD. variables in triggers are ok. Queries are ok. I think that the only problem is access to variables. Regards, Federico -------------------------------------------- Mar 19/4/16, Bruce Carlson <Bruce.Carlson@nepean.com<mailto:Bruce.Carlson@nepean.com>> ha scritto: Oggetto: [Maria-discuss] Mariadb 10.1.13 slow stored procedures A: "maria-discuss@lists.launchpad.net<mailto:maria-discuss@lists.launchpad.net>" <maria-discuss@lists.launchpad.net<mailto:maria-discuss@lists.launchpad.net>> Data: Martedì 19 Aprile 2016, 01:24 HI All, Just a quick question to start with, does anybody know why MariaDB 10.1.13 is extremely slow running stored procedures. I have searched for as much as I can find and have made sure the database and table character sets and compilation seta are identical but still stored procedures with lots of joins and internal variables is running hundreds of times slower than the old version 5.3. I have checked all system variables and they are identical in both systems (where they exist in both systems) Any assistance or advice would be greatly appreciated. If I need to supply more information please let me know. Both versions are running on windows server 2008 R2 Kind Regards, Bruce Carlson -----Segue allegato----- _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net<mailto:maria-discuss@lists.launchpad.net> Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (2)
-
Bruce Carlson -
Federico Razzoli