- discuss - lists.mariadb.org

Re: [Maria-discuss] Mariadb 10.1.13 slow stored procedures
by Federico Razzoli 19 Apr '16

19 Apr '16

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(a)nepean.com> ha scritto: Oggetto: Re: [Maria-discuss] Mariadb 10.1.13 slow stored procedures A: "maria-discuss(a)lists.launchpad.net" <maria-discuss(a)lists.launchpad.net> 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@yahoo.it] Sent: Tuesday, 19 April 2016 10:41 AM To: maria-discuss(a)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(a)nepean.com> ha scritto: Oggetto: [Maria-discuss] Mariadb 10.1.13 slow stored procedures A: "maria-discuss(a)lists.launchpad.net" <maria-discuss(a)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(a)lists.launchpad.net 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(a)lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

1 0

[Maria-discuss] R: Mariadb 10.1.13 slow stored procedures
by Federico Razzoli 19 Apr '16

19 Apr '16

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(a)nepean.com> ha scritto: Oggetto: [Maria-discuss] Mariadb 10.1.13 slow stored procedures A: "maria-discuss(a)lists.launchpad.net" <maria-discuss(a)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(a)lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

2 1

[Maria-discuss] HikariCP + searching archives
by Stu Smith 19 Apr '16

19 Apr '16

Hello, I'm curious if there is a way to search the archives for this list - I couldn't find one, so I think I'm about to ask a simple question, but here goes: I noticed the MariaDB connector/J docs give an example using the HikariCP connection pool. Was there any reason for this? I saw they filed bugs in the mariadb jira, so they have put some effort into working with the community - and they have pretty graphs showing how fast they are. We're not really too worried about optimizing it yet - just adding a connection pool as a best practice kind of thing to a rest service hitting mariadb. Just wanted to make sure there weren't any caveats to using it with connector/J or if it was picked as the example for any particular reason. Also - any thoughts on pooling being a best practice to start off with or an optimization you should put in when you need it, and feel like tweaking pool lifetimes, etc... Thanks! Take care, -stu

4 4

[Maria-discuss] Mariadb 10.1.13 slow stored procedures
by Bruce Carlson 19 Apr '16

19 Apr '16

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

1 0

[Maria-discuss] MariaDb Questions
by Ghazi Btissam 18 Apr '16

18 Apr '16

Hi , I have a lot of questions about MariaDb if you can help please: - - Comment on effectue le purge d’une table volumineuse (question partitionning à cause de ses contraintes) ? - - - How the purge is done under MariaDB for volumineous tables (as the partitionning has a big constraint: no foreign keys)? - Are there any compatibility constraints between the storage engine? - Did MariaDb insure the plugins support ( the storage engine pligins...)? - - Is there any equivalent to the AUDIT_POLICIES of Oracle ? - - TDE (Transparent data encryption) is there any equivalent or a plugin? - - How does MariaDb accept a givin plugin in database,what are the conditions? - - How does MariaDB accomplish the encryption? - - As the Oracle package does'nt exist, how MariaDB structure its code ? - - Is there a plugin which can call a Java proc in MariaDB , other than the UDF function (as MariaDB cannot call directly a java proc)? - - What is the usage of virtual column ? Many Thanks and Regards

1 0

[Maria-discuss] MariaDB 10.2.0 Alpha now available
by Daniel Bartholomew 18 Apr '16

18 Apr '16

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.2.0. This is an Alpha release. See the Release Notes and Changelog for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.2.0 - Release Notes: https://mariadb.com/kb/en/mdb-1020-rn/ - Changelog: https://mariadb.com/kb/en/mdb-1020-cl/ - Downloads: https://downloads.mariadb.org/mariadb/10.2.0 About MariaDB 10.2: - https://mariadb.com/kb/en/what-is-mariadb-102/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ - - MariaDB Webinars - - - - - - - - - - - - - - - - - - - - - - - - - There are several upcoming MariaDB-focused webinars and many previously held ones available to watch on an on-demand basis at: - https://mariadb.com/news-events/webinars - - MariaDB Books - - - - - - - - - - - - - - - - - - - - - - - - - - There is an ever-growing library of MariaDB books available to help you get the most out of MariaDB. See the MariaDB Books page for details and links: - https://mariadb.com/kb/en/mariadb/books/ - - User Feedback plugin - - - - - - - - - - - - - - - - - - - - - - - MariaDB includes a User Feedback plugin. This plugin is disabled by default. If enabled, it submits basic, completely anonymous MariaDB usage information. This information is used by the developers to track trends in MariaDB usage to better guide development efforts. If you would like to help make MariaDB better, please add "feedback=ON" to your my.cnf or my.ini file! See http://mariadb.com/kb/en/user-feedback-plugin for more information. - - Quality - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The project always strives for quality, but in reality, nothing is perfect. Please take time to report any issues you encounter at: - http://mariadb.org/jira - - Support MariaDB - - - - - - - - - - - - - - - - - - - - - - - - - If you would like to contribute to the MariaDB Foundation, please see the "contributing" and "donations" pages. We also have merchandise available in a cafepress store. All proceeds go to support the MariaDB Foundation. - https://mariadb.com/kb/en/contributing - https://mariadb.org/donate/ We hope you enjoy MariaDB! -- MariaDB Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledge Base - http://mariadb.com/kb

1 0

[Maria-discuss] MariaDb Entreprise edition (ColumnStore)
by Ghazi Btissam 18 Apr '16

18 Apr '16

Hi, 1- Is MariaDb Enterprise open source software (and all its components and modules? 2- Regarding the Spider engine: the feature allows 2 spider tables from different servers to share each of them partitions from a master spider which hold those partitions? please give some explainations Thanks

2 1

[Maria-discuss] MariaDb tablespaces?
by Ghazi Btissam 18 Apr '16

18 Apr '16

Hi, Is there tablespace in MariaDb like Oracle?if yes which storage engine use them and how? Thanks

2 1

[Maria-discuss] R: MariaDb tablespaces?
by Federico Razzoli 18 Apr '16

18 Apr '16

Hi, In current MariaDB versions, InnoDB has 2 types of tablespaces: * system tablespace * per-table tablespace There is a variable called innodb_file_per_table, you can change it at runtime. When a new table is created with innodb_file_per_table=1, the table has its own tablespace (an .ibd file in the database directory, which contains both data and indexes). When a table is created while innodb_file_per_table=0, it is written into the system tablespace. The system tablespace also contains other information, like the InnoDB data dictionary, un logs, etc. I see no particular advantage in creating new tables into the system tablespace - I consider it a feature inherited from old versions, when there were no alternatives. Remembers that the system tablespace never shrinks, even if you drop a table. Also, when using a dedicated tablespace, you can decide where the new table will be placed (see CREATE TABLE options). And you can backup only some tables, if you want to. This also enables table compression and encryption. In MySQL 5.7, you can group multiple tables into one tablespace - which means, not necessarily the system tablespace. From what I see in this page, I think (and hope) and this feature will also be in MariaDB 10.2: https://mariadb.com/kb/en/mariadb/plans-for-10x/ Other storage engines don't have tablespaces: a table is stored in a file (or in 2 files, if data and indexes are stored separately). Regards, Federico -------------------------------------------- Lun 18/4/16, Ghazi Btissam <btissam.ghazi(a)gmail.com> ha scritto: Oggetto: [Maria-discuss] MariaDb tablespaces? A: maria-discuss(a)lists.launchpad.net Data: Lunedì 18 Aprile 2016, 12:38 Hi, Is there tablespace in MariaDb like Oracle?if yes which storage engine use them and how? Thanks -----Segue allegato----- _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss(a)lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

1 0

[Maria-discuss] New Question: when archive can support index to speed up select peformance?
by AskMonty KB 17 Apr '16

17 Apr '16

Hello, A new question has been asked in "Archive" by yixuan_huang. Please answer it at http://mariadb.com/kb/en/when-archive-can-support-index-to-speed-up-select-… as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hello, when i used innodb, w/o index it will cost 2 mins for select, w/ index (two columns), it only costs 20 secs. due to large size of innodb, i convert innodb to archive engine, it save space lots, but failed to create index. previously, i want to extend primary key with those two columns, but failed due to max length should be 8 bytes. any suggestions? thanks! -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/when-archive-can-support-index-to-speed-up-select-…

1 0