April 2017 - discuss - lists.mariadb.org

[Maria-discuss] MariaDB Server maintenance policy for 10.0
by Colin Charles 27 Apr '17

27 Apr '17

Hi! I just read: https://mariadb.org/about/maintenance-policy/ It says the 5 year boundary date for support ending for 10.0 is 31 Mar 2019, and later goes on to specify why 5.5 has a later EOL, "The MariaDB Foundation may decide to maintain selected releases beyond the normal 5 years if they are in exceptionally widespread use. As MariaDB 5.5 is included in multiple prominent Linux distributions with a lifespan beyond 2017, the Foundation is committed in maintaining it until 2020." Is SUSE Enterprise Linux Server 12, with MariaDB Server 10.0, not considered a “prominent Linux distribution”? SLES12 released in 2014, comes with MariaDB Server 10.0, and requires at least 10 years of support (https://www.suse.com/releasenotes/x86_64/SUSE-SLES/12/). Just checking to see if the maintenance policy above is in error Thanks -- Colin Charles, http://bytebot.net/blog/ twitter: @bytebot | skype: colincharles "First they ignore you, then they laugh at you, then they fight you, then you win." -- Mohandas Gandhi

3 4

Re: [Maria-discuss] Are some MyISAM settings used for Aria
by Federico Razzoli 24 Apr '17

24 Apr '17

Hi Jocelyn, The article you linked is quite old, so I wanted to check the source code to find if something changed. I didn't investigate much but I searched in MariaDB's master branch via GitHub. The search strings were 'read_rnd_buffer_size' and 'read-rnd-buffer-size'. I found nothing except for some test files and the variable initialization at server startup. I repeated the same searches in MySQL master branch, and again, I found only tests and initialization. Can you explain this? The variable is unused now? Federico -------------------------------------------- Lun 24/4/17, jocelyn fournier <jocelyn.fournier(a)gmail.com> ha scritto: Oggetto: Re: [Maria-discuss] Are some MyISAM settings used for Aria A: "Sales" <info(a)smallbusinessconsultingexperts.com> Cc: "Maria Discuss" <maria-discuss(a)lists.launchpad.net> Data: Lunedì 24 Aprile 2017, 09:16 Hi Steve, Le 24 avr. 2017 3:28 AM, "Sales" <info(a)smallbusinessconsultingexperts.com> a écrit : On Apr 23, 2017, at 4:12 PM, jocelyn fournier <jocelyn.fournier(a)gmail.com> wrote: Hi Steve, Le 23/04/2017 à 19:32, Sales a écrit : On our system, Mariadb 10.1.22, with aria_used_for_temp_tables = ON, we have a set of MyISAM settings carried over from an earlier setup. Those settings are: key_buffer_size=256M myisam_sort_buffer_size = 64M join_buffer_size=512K bulk_insert_buffer_size=512M read_rnd_buffer_size = 1M read_rnd_buffer_size and join_buffer_size are not MyISAM specific settings, did you try to modify/not modify only those ones ? HTH, Jocelyn Really? So, the doc is wrong? https://mariadb.com/kb/en/ mariadb/server-system- variables/#read_rnd_buffer_ size Says, MyISAM. So, are you saying that is mistaken? If so, that would make sense At least according to https://www.percona.com/blog/2007/07/24/what-exactly-is-read_rnd_buffer_siz… it's not. BTW it has been updated in the mysql manual to mention it optimizes mrr for any engine :https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_read_rnd_buffer_size MariaDB KB would need an update as well. Jocelyn _______________________________________________ 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

3 4

[Maria-discuss] Are some MyISAM settings used for Aria
by Sales 24 Apr '17

24 Apr '17

On our system, Mariadb 10.1.22, with aria_used_for_temp_tables = ON, we have a set of MyISAM settings carried over from an earlier setup. Those settings are: key_buffer_size=256M myisam_sort_buffer_size = 64M join_buffer_size=512K bulk_insert_buffer_size=512M read_rnd_buffer_size = 1M Looking in the information_schema, we have one entire schema of tables still using MyISAM (third party system), and, this schema is not used in any daily processing. The only other MyISAM tables are in the mysql schema. So, nothing else, not one table. The way I understand aria, it should be used instead of MyISAM even for tmp tables given our settings. We have a long running set of programs that do things over night to manage products, many millions of them, ends up processing at least 50 million rows of various tables, thousands of mysql statements. This process has no MyISAM as noted. When I disable the settings above for MyISAM, a significant runtime increase is noted, I’ve repeated this 5 times now over a few week period. The increase is 20%, pretty significant, and that 20% never occurs when not changing settings. So, it would seem unlikely that I am just that unlucky. I am curious as to why those settings for MyISAM would have an impact? There are certainly tmp tables in the process (or internally created tmp tables) and the rest is innodb. Any good potential reason I am missing? I have not really looked into aria much before, read most of the mariadb doc. Maybe I missed something key, it appears likely I did! We have the following somewhat corresponding aria settings: aria_pagecache_buffer_size = 256M aria_sort_buffer_size = 256M Steve

2 3

[Maria-discuss] R: Are some MyISAM settings used for Aria
by Federico Razzoli 24 Apr '17

24 Apr '17

Steve, aria_used_for_temp_tables only affects internally created temporary tables, not the temp tables created explicitly by applications. So I suggest: * check if your applications create MyISAM (temporary) tables * use the slow log (long_query_time=0, min_examine_rows=0) and pt-query-digest to check which queries are slower when you comment those settings Federico -------------------------------------------- Dom 23/4/17, Sales <info(a)smallbusinessconsultingexperts.com> ha scritto: Oggetto: [Maria-discuss] Are some MyISAM settings used for Aria A: maria-discuss(a)lists.launchpad.net Data: Domenica 23 Aprile 2017, 19:32 On our system, Mariadb 10.1.22, with aria_used_for_temp_tables = ON, we have a set of MyISAM settings carried over from an earlier setup. Those settings are: key_buffer_size=256M myisam_sort_buffer_size = 64M join_buffer_size=512K bulk_insert_buffer_size=512M read_rnd_buffer_size = 1M Looking in the information_schema, we have one entire schema of tables still using MyISAM (third party system), and, this schema is not used in any daily processing. The only other MyISAM tables are in the mysql schema. So, nothing else, not one table. The way I understand aria, it should be used instead of MyISAM even for tmp tables given our settings. We have a long running set of programs that do things over night to manage products, many millions of them, ends up processing at least 50 million rows of various tables, thousands of mysql statements. This process has no MyISAM as noted. When I disable the settings above for MyISAM, a significant runtime increase is noted, I’ve repeated this 5 times now over a few week period. The increase is 20%, pretty significant, and that 20% never occurs when not changing settings. So, it would seem unlikely that I am just that unlucky. I am curious as to why those settings for MyISAM would have an impact? There are certainly tmp tables in the process (or internally created tmp tables) and the rest is innodb. Any good potential reason I am missing? I have not really looked into aria much before, read most of the mariadb doc. Maybe I missed something key, it appears likely I did! We have the following somewhat corresponding aria settings: aria_pagecache_buffer_size = 256M aria_sort_buffer_size = 256M Steve _______________________________________________ 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

3 3

Re: [Maria-discuss] Are some MyISAM settings used for Aria
by Federico Razzoli 23 Apr '17

23 Apr '17

Once you see which queries are slower, you can find which changes affected the query. Execution plans are not expected to change when you resize a buffer, but you can measure the effect of resizing the buffers. SHOW GLOBAL STATUS LIKE 'Handler%' can also help you to measure WHICH PART of a query becomes slower/fast after changing a variable. Usually those buffers should be left on their default values. If you really need to change them for some queries (which is not common) the application should do it at session level to avoid impacting the whole workload. Federico -------------------------------------------- Dom 23/4/17, Sales <info(a)smallbusinessconsultingexperts.com> ha scritto: Oggetto: Re: [Maria-discuss] Are some MyISAM settings used for Aria A: "Federico Razzoli" <federico_raz(a)yahoo.it> Cc: maria-discuss(a)lists.launchpad.net Data: Domenica 23 Aprile 2017, 22:59 > On Apr 23, 2017, at 3:00 PM, Federico Razzoli <federico_raz(a)yahoo.it> wrote: > > Steve, > > aria_used_for_temp_tables only affects internally created temporary tables, not the temp tables created explicitly by applications. > So I suggest: > * check if your applications create MyISAM (temporary) tables > * use the slow log (long_query_time=0, min_examine_rows=0) and pt-query-digest to check which queries are slower when you comment those settings > > Federico > Yes, the default engine for tables is innodb as evidenced by default_storage_engine, so, those are not using MyISAM. The default_tmp_storage_engine defaults to nothing, so, according to the doc, this means it uses default_storage_engine, which means innodb again. I was merely pointing out that all temp tables are not using MyISAM, or at least should not be. Have used the slow query log and Percona toolkit with various length settings and did not give much benefit sadly. It showed some queries running longer, but, that did not help too much as far as why since the queries were using optimal query paths. We have many long running queries and that’s fine as these are calculations and mass imports from suppliers, pricing, etc. We’ve just been unable to make sense of the why of it. A second potential hint is mariadb 10.1 has performed slower than mariadb 5.5 on the same machine, and thus far, we’ve been unable to isolate why. It’s not slower by a lot, maybe 5%. The word MyISAM does not exist anywhere in our code. Simple grep -ri shows this. It’s puzzling to me. Kind of makes me want to explicitly set default_tmp_storage_engine just to see if any effect. I am just checking if anything might still use MyISAM despite these settings. It’s not a big deal as the settings don’t use a tremendous amount of memory by any means, but, was hoping to just size MyISAM all the way down. And then became curious as to why this might be. Thanks!

1 0

[Maria-discuss] MariaDB Galera Cluster 5.5.55 and Connector/J 2.0.0 now available
by Daniel Bartholomew 22 Apr '17

22 Apr '17

The MariaDB project is pleased to announce the immediate availability of MariaDB Galera Cluster 5.5.55 Stable (GA) and MariaDB Connector/J 2.0.0 Release Candidate (RC). See the Release Notes and Changelogs for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB Galera Cluster 5.5.55 - Release Notes: https://mariadb.com/kb/en/mgc-5555-rn/ - Changelog: https://mariadb.com/kb/en/mgc-5555-cl/ - Downloads: https://downloads.mariadb.org/mariadb-galera/5.5.55 About MariaDB Galera Cluster: - https://mariadb.com/kb/en/what-is-mariadb-galera-cluster/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ MariaDB Connector/J 2.0.0 - Release Notes: https://mariadb.com/kb/en/mcj-200-rn/ - Changelog: https://mariadb.com/kb/en/mcj-200-cl/ - Downloads: https://downloads.mariadb.org/connector-java/2.0.0/ About MariaDB Connector/J: - https://mariadb.com/kb/en/about-mariadb-connector-j/ - - 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/mariadbfdn - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledge Base - http://mariadb.com/kb

1 0

Re: [Maria-discuss] MySQL Workbench Fork?
by Federico Razzoli 20 Apr '17

20 Apr '17

Looks like a lot of people want to use GUIs. Which is absolutely legit, but frankly, I don't agree that any database GUI should be considered enterprise software. Selecting checkboxes and selecting from drop-down menus is much slower, and much more error-prone, than writing SQL queries. I recognize that they allow you to avoid learning ALTER TABLE syntax, which potentially turns a person with no specific skill into a database architect. Is this an enterprise feature? Also, you never really know what you are doing. Some examples? * You read the database size. Where did the GUI read this value? I want to see system tables and file sizes, not values with vague names. * Ok, you just issued an SQL statement. But first, the GUI transparently ran tons of SET statements. What you did is what you wanted to do, or is it the random result of GUI's authors overactive creativity? :) Please, don't misunderstand me. I'm not saying that GUIs are useless or badly designed. But I wouldn't call them enterprise software, and I would not suggest them in a professional environment. Cheers, Federico -------------------------------------------- Gio 20/4/17, <info(a)smallbusinessconsultingexperts.com> ha scritto: Oggetto: Re: [Maria-discuss] MySQL Workbench Fork? A: maria-discuss(a)lists.launchpad.net, "Zach Lym" <zachlym(a)indolering.com> Data: Giovedì 20 Aprile 2017, 21:51 Totally agree. It's an necessary part for us. So many features missing. Tired of their warning too about mariadb. Steve Fatula On Apr 20, 2017, 2:13 PM -0500, Zach Lym <zachlym(a)indolering.com>, wrote: I convinced my boss to use MariaDB for a new project but it appears that the workbench is incompatible with new features of MariaDB. I've looked at the alternatives listed on the website, but they are all either proprietary or Windows only. I just can't sell my boss on switching to MariaDB without workbench. Free software isn't free, but I think Workbench is a big driver of commercial and enterprise use. It's easy to kvetch from the outside, but I feel as though MariaDB has just lost some ground. Has management considered forking Workbench? Thank you, -Zach Lym _______________________________________________ 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 _______________________________________________ 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] innodb table single column index max length
by butler 18 Apr '17

18 Apr '17

we know the mysql have a limit that the max index length on a single column is 767.I want know how the 767 come from ? why not 799 or 800 etc.

2 2

[Maria-discuss] MariaDB 10.3.0 Alpha and 5.5.55 Stable now available
by Daniel Bartholomew 18 Apr '17

18 Apr '17

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.3.0 Alpha and MariaDB 5.5.55 Stable (GA). See the Release Notes and Changelog for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.3.0 - Release Notes: https://mariadb.com/kb/en/mdb-1030-rn/ - Changelog: https://mariadb.com/kb/en/mdb-1030-cl/ - Downloads: https://downloads.mariadb.org/mariadb/10.3.0 About MariaDB 10.3: - https://mariadb.com/kb/en/what-is-mariadb-103/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ MariaDB 5.5.55 - Release Notes: https://mariadb.com/kb/en/mdb-5555-rn/ - Changelog: https://mariadb.com/kb/en/mdb-5555-cl/ - Downloads: https://downloads.mariadb.org/mariadb/5.5.55 About MariaDB 5.5: - https://mariadb.com/kb/en/what-is-mariadb-55/ 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/mariadbfdn - 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] logrotate
by Daniel Black 18 Apr '17

18 Apr '17

Quick proof of concept logrotate that hasn't really been changed in a while. The aim is to get this closer to a state for distro maintainers to use directly. By using a dedicated SQL user this shouldn't conflict with an existing user root user (which users always use despite the ability create other users with SUPER privs). As users will occasional change the password on the root without taking into account that logrotate typically uses the same user. Relying on users to update /root/.my.cnf is unreliable. Giving selinux permissions to allow logrotate read files under /root is also a little excessive. Using a dedicated mysqladmin.logrotate this won't conflict with existing mysqladmin group. Adds slow query log and general log to the logrotate script. Binary logs are deliberately omitted (MDEV-11610). https://mariadb.com/kb/en/mariadb/sql_error_log-plugin/ seems to have its own rotation) Proposed changes: https://github.com/MariaDB/server/compare/10.2...grooverdan:10.2-logrotate?… mysqladmin --local uses SET SESSION SQL_LOG_BIN=0 so I'll change its implementation to use FLUSH LOCAL ... which would preserve the need to use RELOAD only privs for logrotate. Thoughts? Difficulties? Suggestions? ref: https://jira.mariadb.org/browse/MDEV-11610 https://jira.mariadb.org/browse/MDEV-5662

3 4