- discuss - lists.mariadb.org

[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

[Maria-discuss] doubt about best engine
by Roberto Spadim 14 May '15

14 May '15

hi guys, i'm with doubt about the "best" engine (best = no INSERT lag ~0.100s or less, good SELECT speed ~ 1 to 10 seconds is ok) i have two tables, they only "receive" INSERT and SELECT queries (no DELETE/UPDATE/ALTER) they increase 200MB/day and 3.000.000 rows/day my doubt is, how to have small database size and good read rate? it have ~ 104 inserts / second, but each insert is multi values like ... INSERT INTO table VALUES (),(),(),(),(),() .... in other words... today i'm using aria (it's crash safe) and i don't have problems with concurrent insert / table locks etc, my doubt is about table size and read speed with bigger database i'm considering using spider engine or any other shard system with >100GB , or maybe a big raid-6 system, server is a xeon 8core with 16GB and 2hd 500GB RAID1 SAS (>180MB/s) the client side is a C++ program that i don't have access to change and recompile it, in other words i can only change server side and load balance data table is something like: (datetime decimal(22,6),value decimal(22,6)) the SELECT code use most part of recent data ~5 DAYS, in outlier queries something near to ~1 MONTH ago ideas are wellcome -- Roberto Spadim

3 2

[Maria-discuss] Innodb Page Compression
by Rhys Campbell 13 May '15

13 May '15

Hello All, I have compiled mariadb 10.1.4 from source to have a play with innodb page compression. Not sure if these are bug or misunderstanding on my part, probably a combination of both... I am running in a CentOS 7 x64 VirtualBox VM 10.1.4-MariaDB-wsrep. The compile support for page compression seems to have worked ok... SHOW STATUS LIKE 'innodb_have%'; [cid:image001.png@01D08D88.E9019C90] My experiments have been with the following example database... https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-f… When this is loaded onto a default install of mariadb (InnoDB Antelope / no compression) The db size is... du -sh /var/lib/mysql/employees - 385MB The when I run ALTER TABLE .. ROW_FORMAT=COMPRESSED the db size is... du -sh /var/lib/mysql/employees - 119MB Next I performed a series of reloads of this database after changing the compression algorithm. Note the total db size doesn't change apart from when compression is lzma. SET GLOBAL innodb_compression_algorithm='zlib'; du -sh /var/lib/mysql/employees - 385MB SET GLOBAL innodb_compression_algorithm='lz4'; du -sh /var/lib/mysql/employees - 385MB SET GLOBAL innodb_compression_algorithm='lzo'; du -sh /var/lib/mysql/employees - 385MB SET GLOBAL innodb_compression_algorithm='lzma'; du -sh /var/lib/mysql/employees - 308MB SET GLOBAL innodb_compression_algorithm='bzip2'; du -sh /var/lib/mysql/employees - 385MB SET GLOBAL innodb_compression_algorithm='snappy'; du -sh /var/lib/mysql/employees - 385MB SET GLOBAL innodb_compression_algorithm='lzma'; SET GLOBAL innodb_compression_level=9; du -sh /var/lib/mysql/employees - 232M SET GLOBAL innodb_compression_algorithm='zlib'; SET GLOBAL innodb_compression_level=9; du -sh /var/lib/mysql/employees - 385MB Note if you then try to execute.. ALTER TABLE departments ROW_FORMAT=COMPRESSED; Table storage engine 'InnoDB' does not support the create option 'PAGE_COMPRESSED' On further reading the documentation<https://mariadb.com/kb/en/mariadb/innodbxtradb-page-compression/> I noted the "Persistent Trim" section. So I tried... SET GLOBAL innodb_compression_algorithm='zlib'; SET GLOBAL innodb_compression_level=9; SET GLOBAL innodb_use_trim=ON; du -sh /var/lib/mysql/employees - 320MB Note the db size is smaller by 65MB FYI innodb_page_compression_saved = 0 at this point. Next I tried the following settings.. vi /etc/my.cnf.d/innodb_compression.cnf innodb_file_format=Barracuda innodb_file_format_max=Barracuda innodb_compression_algorithm=zlib innodb_compression_level=9 innodb_use_trim=1 innodb_use_fallocate=1 du -sh /var/lib/mysql/employees - 232M SET GLOBAL innodb_compression_algorithm='lz4'; du -sh /var/lib/mysql/employees - 232M innodb_page_compression_saved = 0 # All other counters for SHOW STATUS LIKE '%compres%' are zero SET GLOBAL innodb_compression_algorithm='lzma'; du -sh /var/lib/mysql/employees - 232M SET GLOBAL innodb_compression_algorithm='bzip2'; du -sh /var/lib/mysql/employees - 232M SET GLOBAL innodb_compression_algorithm='snappy'; du -sh /var/lib/mysql/employees - 232M innodb_page_compression_saved = 0 # All other counters for SHOW STATUS LIKE '%compres%' are zero information_schema tables INNODB_CMP, INNODB_CMP_RESET, INNODB_CMPMEM & INNODB_CMP_RESET all zero. So I guess the questions here would be... 1. Why when trim is not used is the db size unchanged apart from lzma? 2. Why is the compressed size the same for all algorithms? Is the correct algorithm being used? 3. Can I confirm which algorithm has been used for a page. I can't see anything in information_schema 4. Status variables / info schema not working? 5. ALTER TABLE ... PAGE_COMPRESSED = 1 doesn't work. Must I always do a full dump & load to enable page compression? 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!) Cheers, 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.

1 0

[Maria-discuss] New Question: license question about libmysqlclient
by AskMonty KB 12 May '15

12 May '15

Hello, A new question has been asked in "MariaDB Connector/C" by seanhan. Please answer it at http://mariadb.com/kb/en/license-question-about-libmysqlclient/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi: I just downloaded and installed the mariadb-10.0.17-linux-x86_64. From the lib directory, I found the libmysqlclient.a and other related files. My question is, are they built from the native mariadb connector? Are they GPL or LGPL. Thank you!! Sean -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/license-question-about-libmysqlclient/

1 0