- discuss - lists.mariadb.org

[Maria-discuss] Dynamic Columns ORM
by Tom Worster 23 May '15

23 May '15

In January I was asking about Dynamic Columns because I had started working on an ORM extension for it. It is now tagged 0.1.0 in Github. There probably aren't many here using Yii Framework but at least this shows that some of us like and use Maria's Dynamic Columns. Thank you, Maria. I already got some feedback on IRC from Kolbe who hated it :'( and we'd welcome more of the same :-) https://github.com/tom--/dynamic-ar Yii Framework forum thread: http://www.yiiframework.com/forum/index.php/topic/63995-/ Previous thread started here: https://lists.launchpad.net/maria-discuss/msg02159.html tom[] The empty array without a dollar sign

2 1

[Maria-discuss] mysqldump Spider tables
by Nicolas Payart 21 May '15

21 May '15

Hi list, I am using Spider Storage Engine for multiple purpose. I often create spider tables as a drop-in replacement for Federated(x) tables. But one main difference I notice using spider SE vs Federated is that mysqldump dumps the whole data of remote spider tables whereas it only dumps the table schema of federated tables. I expected these two close storage engines to behave similarly... Is this an expected behaviour of Spider SE (and/or mysqldump)? Is there a workaround to make mysqldump dump only schema of Spider tables, just like it would do with a federated table? Of course, I can use the --no-data option but it's a bit annoying when you have a mix of innodb tables and spider tables in the same database. Thanks, -- Nicolas

2 2

[Maria-discuss] Query Cache with TTL and unconsistent values - remove memcache servers
by Roberto Spadim 20 May '15

20 May '15

Hi guys! I created a MDEV (feature request) sometime ago, https://mariadb.atlassian.net/browse/MDEV-6777 There's a patch ( http://ebergen.net/wordpress/2007/04/23/select-sql_cache_ttl/) but i want change a bit to work "better", it's using "SELECT SQL_CACHE_TTL ..." hint, and could be better change it to "SELECT SQL_CACHE_TTL=9999 ..." to easily change TTL instead of a fixed TTL set by "SET ttl=999;SELECT SQL_CACHE_TTL ..." query, could anyone help me with it? Why? With this we can reduce the memcache use in small/medium systems, reducing app side caching code too the difference between query cache and query cache with ttl is how it's removed from cache, one is removed with table invalidation (consistent cache), and other is removed with time to live even if table change (unconsistent cache) =] help is welcome, thnks guys -- Roberto Spadim

1 0

[Maria-discuss] MariaDB 10.1.4 Progress output
by Rhys Campbell 19 May '15

19 May '15

Note the progress output below. I've seen several like this. i.e... Stage: 1 of 72 'altering table' 350% of stage done. Is this intentional? [cid:image001.png@01D07B7A.526BD6D0] Rhys Campbell Database Administrator TradingScreen, Inc. 23 York House, 5th Floor London WC2B 6UJ Email: rhys.campbell(a)tradingscreen.com<mailto:rhys.campbell@tradingscreen.com> Follow TradingScreen on Twitter<http://twitter.com/#!/TradingScreen> , Facebook<http://www.facebook.com/pages/TradingScreen/214046251945650> and our blog Trading Smarter<tradingsmarter.tradingscreen.com> This message is intended only for the recipient(s) named above and may contain confidential information. If you are not an intended recipient, you should not review, distribute or copy this message. Please notify the sender immediately by e-mail if you have received this message in error and delete it from your system.

5 5

[Maria-discuss] MariaDB Galera Cluster 10.0.19 and 5.5.43 now available
by Daniel Bartholomew 18 May '15

18 May '15

The MariaDB project is pleased to announce the immediate availability of MariaDB Galera Cluster 5.5.43 and 10.0.19. These are Stable (GA) releases. See the Release Notes and Changelogs for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB Galera Cluster 10.0.19 - Release Notes: https://mariadb.com/kb/en/mariadb-galera-cluster-10019-release-notes/ - Changelog: https://mariadb.com/kb/en/mariadb-galera-cluster-10019-changelog/ - Downloads: https://downloads.mariadb.org/mariadb-galera/10.0.19/ MariaDB Galera Cluster 5.5.43 - Release Notes: https://mariadb.com/kb/en/mariadb-galera-cluster-5543-release-notes/ - Changelog: https://mariadb.com/kb/en/mariadb-galera-cluster-5543-changelog/ - Downloads: https://downloads.mariadb.org/mariadb-galera/5.5.43/ 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 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/en/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] query cache dump_at_shutdown/load_at_startup useful + no character set/collation?
by Daniel Black 17 May '15

17 May '15

I was testing out using the query_cache_info plugin (https://mariadb.com/kb/en/mariadb/query_cache_info-plugin/) to pre-cache data into a server after an upgrade. The read only slaves have a fairly high cache hit ratio. select concat('use ', STATEMENT_SCHEMA, ';', STATEMENT_TEXT, ';') as q INTO OUTFILE '/tmp/qc.txt' FIELDS ESCAPED BY '' TERMINATED BY '' from information_schema.QUERY_CACHE_INFO; Query OK, 7122 rows affected (0.03 sec) When loading these it seems the statements have a collation however this isn't stored in the query cache. $ mysql --force < /tmp/qc.txt > /dev/null ERROR 1267 (HY000) at line 119: Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' ERROR 1267 (HY000) at line 2390: Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' ERROR 1267 (HY000) at line 7569: Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' Attempts to force this with --default-character-set={utf8|utf8mb4} also resulted in the same error or same with (echo "set character_set_connection=utf8;"; cat /tmp/qc.txt; ) | time mysql --force.. Are query cache results actually predictable if they have the same statement running in a different session character set/collation? Anyone see a work around to get the restore file usable on all queries? Is asking for query cache dump/load as a feature request (similar to innodb_buffer_pool_dump_at_shutdown/innodb_buffer_pool_restore_at_startup) going to be useful to anyone? -- -- Daniel Black, Engineer @ Open Query (http://openquery.com.au) Remote expertise & maintenance for MySQL/MariaDB server environments.

2 2

[Maria-discuss] performance_schema
by Rhys Campbell 15 May '15

15 May '15

Hi All, Does anyone know if the performance_schema guarantees that every statement executed will appear in the events_statement_summary_by_digest table (excluding those that could be pushed out due to size restrictions). Or is it a "best effort" system? If it's a best effort system what are the circumstances in which a query may not make it to the performance schema? Rhys Campbell Database Administrator TradingScreen, Inc. 23 York House, 5th Floor London WC2B 6UJ Email: rhys.campbell(a)tradingscreen.com<mailto:rhys.campbell@tradingscreen.com> Follow TradingScreen on Twitter<http://twitter.com/#!/TradingScreen> , Facebook<http://www.facebook.com/pages/TradingScreen/214046251945650> and our blog Trading Smarter<tradingsmarter.tradingscreen.com> This message is intended only for the recipient(s) named above and may contain confidential information. If you are not an intended recipient, you should not review, distribute or copy this message. Please notify the sender immediately by e-mail if you have received this message in error and delete it from your system.

2 1

[Maria-discuss] Twitter handles of technical MariaDB guys
by Lukas Lehner 15 May '15

15 May '15

Hi You can recommend me Twitter handles of technical MariaDB guys? I wish to learn, I have immediate DB skills. Lukas

1 0

Re: [Maria-discuss] Innodb Page Compression
by Rhys Campbell 14 May '15

14 May '15

Hi Jan, Just a follow up on my previous email. I understood that simply changing the setting for innodb_compression_algorithm would not change any existing tables. Between each test I was running the employees.sql script from the example database. This contains the following sql at the start of the script…. DROP TABLE IF EXISTS dept_emp, dept_manager, titles, salaries, employees, departments; So I definitely think there’s something up here. I’ve performed a similar set of tests below… SET GLOBAL innodb_compression_algorithm=none; source employees.sql du –sh /var/lib/mysql/employees – 385M <- Note all tables are created here with the PAGE_COMPRESSED=1 option. INNODB_SYS_TABLESPACES shows all are Barrcacuda/Dynamic Next I run the ALTER TABLE … ROW_FORMAT=COMPRESSED PAGE_COMPRESSED=DEFAULT; for each table in the employees database du –sh /var/lib/mysql/employees – 135MB; <- Expected 119MB here, probably due to a log file. SET GLOBAL innodb_compression_algorithm=zlib <- After each compression algorithm change I run alter for each tables as above du –sh /var/lib/mysql/employees – 119MB SET GLOBAL innodb_compression_algorithm=lzo <- Alter run du –sh /var/lib/mysql/employees – 119MB SET GLOBAL innodb_compression_algorithm=lz4 <- Alter run… du –sh /var/lib/mysql/employees – 119MB SET GLOBAL innodb_compression_algorithm=lzma <- alter run du –sh /var/lib/mysql/employees – 119MB SET GLOBAL innodb_compression_algorithm=bzip2 < alter run du –sh /var/lib/mysql/employees – 119MB SET GLOBAL innodb_compression_algorithm=snappy <- alter run du –sh /var/lib/mysql/employees – 119MB SET GLOBAL innodb_use_trim=1; # Now set trim and run above tests again SET GLOBAL innodb_compression_algorithm=zlib # Again the alter table statements are executed as above du –sh /var/lib/mysql/employees – 119MB SET GLOBAL innodb_compression_algorithm=lzo du –sh /var/lib/mysql/employees – 119MB SET GLOBAL innodb_compression_algorithm=lz4 du –sh /var/lib/mysql/employees – 119MB SET GLOBAL innodb_compression_algorithm=lzma du –sh /var/lib/mysql/employees – 119MB SET GLOBAL innodb_compression_algorithm=bzip2 du –sh /var/lib/mysql/employees – 119MB SET GLOBAL innodb_compression_algorithm=snappy du –sh /var/lib/mysql/employees – 119MB Recreating the database without the PAGE_COMPRESSED=1 options, then run ALTER TABLE … ROW_FORMAT=COMPRESSED results in a database size of 119MB. When I attempt the compression without ROW_FORMAT=COMPRESSED I get the same results as I previously poste, i.e. no difference in db size apart from when using lzma. Cheers, Rhys From: Jan Lindström [mailto:jan.lindstrom@mariadb.com] Sent: 14 May 2015 06:14 To: Rhys Campbell Cc: maria-discuss(a)lists.launchpad.net Subject: Re: [Maria-discuss] Innodb Page Compression On Wed, May 13, 2015 at 6:00 PM, Rhys Campbell <Rhys.Campbell(a)tradingscreen.com<mailto:Rhys.Campbell@tradingscreen.com>> wrote: 1. Why when trim is not used is the db size unchanged apart from lzma? SET GLOBAL innodb_compression_algorithm effects only new tables, it does not change existing tables. If you want to change compression algorithm for existing table you need to do: SET GLOBAL innodb_compression_algorithm = lzo; CREATE TABLE t1 (a int not null primary key, b char(200)) engine=innodb page_compressed =1; -- uses lzo SET GLOBAL innodb_compression_algorithm = lz4; -- no change to existing tables ALTER TABLE t1 ENGINE=InnoDB; -- noe table t1 uses compression algorithm lz4; 2. Why is the compressed size the same for all algorithms? Is the correct algorithm being used? No 3. Can I confirm which algorithm has been used for a page. I can’t see anything in information_schema Not really at the moment, I need to think about adding support for this. 4. Status variables / info schema not working? Status variables work only if pages are really compressed or decompressed see 1) 5. ALTER TABLE … PAGE_COMPRESSED = 1 doesn’t work. Must I always do a full dump & load to enable page compression? You have incorrect syntax. ALTER TABLE departments ROW_FORMAT=COMPRESSED means legacy row-compression and if departments table is page compressed you need to specify ALTER TABLE departments ROW_FORMAT=COMPRESSED PAGE_COMPRESSED=DEFAULT; 6. Lzma/9 compression is not that impressive. Is it meant to rival tokudb? (converting to tokudb results in a db size of ~ 54MB and this includes logs!) See 1) tokudb might use different version of lzma as they use bundled one. R: Jan

1 0

Re: [Maria-discuss] Innodb Page Compression
by Rhys Campbell 14 May '15

14 May '15

Hi Jan, That’s for those answers. For #1 I was dropping and recreating all tables after each change to innodb_compression_algorithm. Cheers, Rhys From: Jan Lindström [mailto:jan.lindstrom@mariadb.com] Sent: 14 May 2015 06:14 To: Rhys Campbell Cc: maria-discuss(a)lists.launchpad.net Subject: Re: [Maria-discuss] Innodb Page Compression On Wed, May 13, 2015 at 6:00 PM, Rhys Campbell <Rhys.Campbell(a)tradingscreen.com<mailto:Rhys.Campbell@tradingscreen.com>> wrote: 1. Why when trim is not used is the db size unchanged apart from lzma? SET GLOBAL innodb_compression_algorithm effects only new tables, it does not change existing tables. If you want to change compression algorithm for existing table you need to do: SET GLOBAL innodb_compression_algorithm = lzo; CREATE TABLE t1 (a int not null primary key, b char(200)) engine=innodb page_compressed =1; -- uses lzo SET GLOBAL innodb_compression_algorithm = lz4; -- no change to existing tables ALTER TABLE t1 ENGINE=InnoDB; -- noe table t1 uses compression algorithm lz4; 2. Why is the compressed size the same for all algorithms? Is the correct algorithm being used? No 3. Can I confirm which algorithm has been used for a page. I can’t see anything in information_schema Not really at the moment, I need to think about adding support for this. 4. Status variables / info schema not working? Status variables work only if pages are really compressed or decompressed see 1) 5. ALTER TABLE … PAGE_COMPRESSED = 1 doesn’t work. Must I always do a full dump & load to enable page compression? You have incorrect syntax. ALTER TABLE departments ROW_FORMAT=COMPRESSED means legacy row-compression and if departments table is page compressed you need to specify ALTER TABLE departments ROW_FORMAT=COMPRESSED PAGE_COMPRESSED=DEFAULT; 6. Lzma/9 compression is not that impressive. Is it meant to rival tokudb? (converting to tokudb results in a db size of ~ 54MB and this includes logs!) See 1) tokudb might use different version of lzma as they use bundled one. R: Jan

1 0