May 2015 - discuss - lists.mariadb.org

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

Re: [Maria-discuss] fsync necessary for synchronous page flush?
by Xiaofei Du 11 May '15

11 May '15

Hi Jan, Thanks for clarification. I should have used synchronous write, instead of synchronous flush. My point is that I noticed for sync writes, fsync is called to force pages to be on persistent storage. while for AIO pages, fsync is not called to force pages to be on persistent storage. My question here is why fsync is required for sync IOs. Does InnoDB maintain a dirty page table? Is fsync called to guarantee the page to be on persistent storage so that the dirty page table can be updated? If this is the case, when is the dirty page table updated for asynchronous IOs? P.S. I couldn't find code for dirty page table. So I am not sure if InnoDB maintains a dirty page table for recovery. Could you please give me a pointer to related code and related resources? Thanks. Xiaofei On Wed, May 6, 2015 at 10:12 AM, Jan Lindström <jan.lindstrom(a)mariadb.com> wrote: > Hi, > > Terminology is little bit confusing here. Page flushing means that we have > done synchronous write to disk but that does not mean that write is > physically on device yet, therefore there is flush to force it to > persistent storage. > > R: Jan > > On Wed, May 6, 2015 at 12:57 AM, Xiaofei Du <xiaofei.du008(a)gmail.com> > wrote: > >> Hello, >> >> When a dirty page is flushed synchronously in buf_flush_write_block_low, >> fsync is called in the following snippet. I am wondering why >> we need this fsync for synchronous flush? The record should be in >> the log already, so recovery should be able to successfully redo it >> and apply to the disk during recovery. Maybe I am missing something here, >> please let me know if I am wrong. Thanks much! >> >> Xiaofei >> >> >> /* When doing single page flushing the IO is done synchronously and we >> flush the changes to disk only for the tablespace we are working on. */ if >> (sync) { ut_ad(flush_type == BUF_FLUSH_SINGLE_PAGE); fil_flush(buf_page_get_space(bpage)); >> /* true means we want to evict this page from the LRU list as well. */ >> buf_page_io_complete(bpage, true); } >> >> _______________________________________________ >> 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 16

[Maria-discuss] New Question: MariaDb Crashing after some wordpress posts
by AskMonty KB 10 May '15

10 May '15

Hello, A new question has been asked in "MariaDB Community" by nunocarapito. Please answer it at http://mariadb.com/kb/en/mariadb-crashing-after-some-wordpress-posts/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi there. I have 2 wordpress blogs in a host, both using mariaDB. Sometimes when I add a new post, DB crashes, and can't find the reason of that. I got a screenshot of the log (mariadb.log). http://i.imgur.com/fiwA7VE.png?1 I cant find out the reason of it. Can you guys give me any help? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/mariadb-crashing-after-some-wordpress-posts/

1 0

[Maria-discuss] How to unsibscribe
by Vadim Pestovnikov 10 May '15

10 May '15

Hi all, Could you please advice me how to unsibscribe from this mailing list? Unfortunately folowing by this link Unsubscribe : https://launchpad.net/~maria-discuss I couldn't find how to do it. Thank you in advance.

2 1

[Maria-discuss] MariaDB 10.0.19 now available
by Daniel Bartholomew 09 May '15

09 May '15

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.0.19. This is a Stable (GA) release. See the Release Notes and Changelog for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.0.19 - Release Notes: https://mariadb.com/kb/en/mariadb-10019-release-notes - Changelog: https://mariadb.com/kb/en/mariadb-10019-changelog - Downloads: https://downloads.mariadb.org/mariadb/10.0.19 About MariaDB 10.0: - https://mariadb.com/kb/en/what-is-mariadb-100/ 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] MariaDB 10.0.18 now available
by Daniel Bartholomew 08 May '15

08 May '15

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.0.18. This is a Stable (GA) release. See the Release Notes and Changelog for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.0.18 - Release Notes: https://mariadb.com/kb/en/mariadb-10018-release-notes - Changelog: https://mariadb.com/kb/en/mariadb-10018-changelog - Downloads: https://downloads.mariadb.org/mariadb/10.0.18 About MariaDB 10.0: - https://mariadb.com/kb/en/what-is-mariadb-100/ 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

3 13