maria-discuss team mailing list archive
-
maria-discuss team
-
Mailing list archive
-
Message #03537
Re: Mariadb 10.1.13 slow stored procedures
I don't think that this is a procedure performance problem. The problem seems to be character set conversion.
1) set correctly your server and databases default character set and collation (show variables like '%char', alter database)
2) if your connection uses utf8, and your query/procedures contains a 'string', MariaDB considers 'string' as a utf8 string. Make sure you're using latin1 (with the commant-line client it's the default)
To check if the character set is latin1, you can query information_schema.ROUTINES table and check the fields CHARACTER_SET_CLIENT, COLLATION_CONNECTION, DATABASE_COLLATION (or SHOW CREATE PROCEDURE).
Regards,
Federico
--------------------------------------------
Mar 19/4/16, Bruce Carlson <Bruce.Carlson@xxxxxxxxxx> ha scritto:
Oggetto: Re: [Maria-discuss] Mariadb 10.1.13 slow stored procedures
A: "maria-discuss@xxxxxxxxxxxxxxxxxxx" <maria-discuss@xxxxxxxxxxxxxxxxxxx>
Data: Martedì 19 Aprile 2016, 10:21
#yiv4011784495
#yiv4011784495 --
_filtered #yiv4011784495 {panose-1:2 4 5 3 5 4 6 3 2 4;}
_filtered #yiv4011784495 {font-family:Calibri;panose-1:2 15
5 2 2 2 4 3 2 4;}
#yiv4011784495
#yiv4011784495 p.yiv4011784495MsoNormal, #yiv4011784495
li.yiv4011784495MsoNormal, #yiv4011784495
div.yiv4011784495MsoNormal
{margin:0cm;margin-bottom:.0001pt;font-size:11.0pt;}
#yiv4011784495 a:link, #yiv4011784495
span.yiv4011784495MsoHyperlink
{color:#0563C1;text-decoration:underline;}
#yiv4011784495 a:visited, #yiv4011784495
span.yiv4011784495MsoHyperlinkFollowed
{color:#954F72;text-decoration:underline;}
#yiv4011784495 p.yiv4011784495MsoPlainText, #yiv4011784495
li.yiv4011784495MsoPlainText, #yiv4011784495
div.yiv4011784495MsoPlainText
{margin:0cm;margin-bottom:.0001pt;font-size:11.0pt;}
#yiv4011784495 span.yiv4011784495PlainTextChar
{}
#yiv4011784495 .yiv4011784495MsoChpDefault
{}
_filtered #yiv4011784495 {margin:72.0pt 72.0pt 72.0pt
72.0pt;}
#yiv4011784495 div.yiv4011784495WordSection1
{}
#yiv4011784495
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@xxxxxxxx]
Sent: Tuesday, 19 April 2016 10:41 AM
To: maria-discuss@xxxxxxxxxxxxxxxxxxx; 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@xxxxxxxxxx>
ha scritto:
Oggetto:
[Maria-discuss] Mariadb 10.1.13 slow stored procedures
A: "maria-discuss@xxxxxxxxxxxxxxxxxxx"
<maria-discuss@xxxxxxxxxxxxxxxxxxx>
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@xxxxxxxxxxxxxxxxxxx
Unsubscribe :
https://launchpad.net/~maria-discuss
More help :
https://help.launchpad.net/ListHelp
-----Segue allegato-----
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@xxxxxxxxxxxxxxxxxxx
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
