June 2015 - discuss - lists.mariadb.org

[Maria-discuss] InnoDB: slow insert (too many fsyncs ?)
by Julien Muchembled 15 Jun '15

15 Jun '15

Hello, I am trying to optimize the insertion of 124 GB of data into InnoDB and it takes much more time than I expected. For the moment this is only a test before migrating prod, using a USB HDD. Prod has much faster storage but I prefer to check if there's anything wrong in my MariaDB setup. The source DB is read as less than 1MB/s with less than 20% CPU usage (<10% for mysqld and <10% for the client app doing the migration). I know that several unsafe settings can be used to import data but I won't be able to use them for the migration of prod. This is because the migration will be done transparently in background, the old db being accessed for not yet migrated data. For the same reason, indexes can't be disabled. Anyway, it was slow that for the moment, I only did a full test with unsafe settings: innodb_flush_log_at_trx_commit = 0 innodb_flush_method = nosync innodb_doublewrite = 0 sync_frm = 0 It took exactly 19.8 hours to import all the data. See attached file for all parameters and detailed statistics. (Sorry for the 'show status' outputs, no idea what I expected to get when I tried 'flush status', whereas 21G were already processed. So 'show status.20941778944' is the output just before the flush, and 'show status' is after everything was finished) The schema of tables can be found here: http://git.erp5.org/gitweb/neoppod.git/blob/HEAD:/neo/storage/database/mysq… 1. fsync Not sure if I read 'show status' correctly, but I'd say it's actually 6 fsyncs per second, and this matches what I see with strace. Why not 0 whereas I disabled them ? There's also less than 1 COMMIT per second, so even without disabling fsyncs I was surprised to see so many in the middle of transactions. 2. Write amplification During my first tests, I saw with iotop that mysqld wrote at 10MB/s. Which means a factor 10. With doublewrite disabled, it's rather a factor 5. These ratios are probably underestimated. For better interpretation of the statistics, I should add that: - source DB is uncompressed but the file format is very compact - most of the imported data ends up in the 'data.value' column, and most values are zlib-compressed, with also deduplication The resulting ibdata is only 70GB. Is this write amplification only caused by indexes & doublewrite ? Thanks, Julien

3 3

[Maria-discuss] Questions regarding Galera Cluster
by Dan Storm 15 Jun '15

15 Jun '15

Hello. I just saw this YouTube video: https://www.youtube.com/watch?v=hYSGsgIyshs It was really informative and it seems like a simple and easy setup. Now, I have a few questions which is regarding maintaining a Galera Cluster, which I hope to get some answers on. 1. A cluster should have an odd number of nodes to avoid brain split. In case I hit the wall with insufficient disk space and I need to scale up my servers, which steps would be the appropriate? My initial thoughts would be taking down one node at a time and add a larger node, what for it to have replicated from the other nodes. Would the - now even numbered of nodes - handle this? And what will have when I do this to the bootstrapped server (the one the others connects to)? 2. According to the video, a slave replication of the cluster would be a way of securing my data in case of disaster. Would creating dumps regularly not suffice? And secondly, in case of hardware failure on one of the nodes, bringing in a new node (probably asking the same as before) would hopefully just replicate the data when it joins again? Thank you for your time. -- Dan Storm

4 3

[Maria-discuss] new table format and CONNECT engine
by Roberto Spadim 14 Jun '15

14 Jun '15

Hi guys, I have a file format that is fixed size, and i want to include at CONNECT engine, anyone could help me? it's a file with 6 columns each one fixed size (1xstring, 1x time64_t, 1x ulong (64bits), 3x double(64bits), each row have 110bytes starting at a fixed position, the file have a header with last update time (time64_t) and a row count (32bits ulong) the file struct is easy but i don't know how to include it with connect engine i just want read access no write thanks :) -- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

3 8

Re: [Maria-discuss] ON DELETE/UPDATE/INSERT Triggers
by Rhys Campbell 12 Jun '15

12 Jun '15

Just to add another one into the mix (and I guess it may be related in some sense)... For ON DELETE triggers it currently fires per row. This impacts performance significantly in some situations when a lot of rows are processed. MSSQL gets around this by creating virtual tables, INSERTED & DELETED (in effect similar to the OLD / NEW tags) and the trigger executes only once... https://msdn.microsoft.com/en-us/library/ms191300.aspx Cheers, Rhys -----Original Message----- From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=tradingscreen.com@lists.launchpad.net] On Behalf Of Federico Razzoli Sent: 12 June 2015 04:02 To: Geoff Montee; Roberto Spadim Cc: MariaDB discuss Subject: Re: [Maria-discuss] DELETE FROM RETURNING In the meanwhile, we can use ON DELETE triggers. But the request is a good idea, because it is a common operation when we want to move some rows to history tables. Federico -------------------------------------------- Gio 11/6/15, Roberto Spadim <roberto(a)spadim.com.br> ha scritto: Oggetto: Re: [Maria-discuss] DELETE FROM RETURNING A: "Geoff Montee" <geoff.montee(a)gmail.com> Cc: "MariaDB discuss" <maria-discuss(a)lists.launchpad.net> Data: Giovedì 11 giugno 2015, 21:09 +1, good idea, today i execute an insert, and a delete 2015-06-11 16:07 GMT-03:00 Geoff Montee <geoff.montee(a)gmail.com>: Hi Rhys, On Wed, Jun 10, 2015 at 1:23 AM, Rhys Campbell <Rhys.Campbell(a)tradingscreen.com> wrote: > " This might be better suited as a feature request on the MariaDB JIRA." > > I got chastised for doing that before so I'm trying to play nice! ;-) > I don't know why you were chastised in the past, but I've added this to the JIRA: https://mariadb.atlassian.net/browse/MDEV-8307 It sure sounds like a useful feature to me. Thanks for the suggestion! Geoff _______________________________________________ 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 -- Roberto Spadim SPAEmpresarial - Software ERPEng. Automação e Controle -----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 _______________________________________________ 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

[Maria-discuss] DELETE FROM RETURNING
by Rhys Campbell 12 Jun '15

12 Jun '15

Hello All, Just regarding the RETURNING feature introduced into MariaDB 10.0.5 https://mariadb.com/kb/en/mariadb/delete/ It would be useful, similar to the OUTPUT clause in MSSQL, if we could INSERT the data sent back from a returning clause in a single statement. Something like... CREATE TABEL deleted_ids ( Id INT NOT NULL PRIMARY KEY ); INSERT INTO deleted_ids DELETE FROM t1 WHERE id < 999 RETURNING id; 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 11

[Maria-discuss] MariaDB 5.5 apt repo for Ubuntu 10.04 no longer exists
by Bryan Traywick 11 Jun '15

11 Jun '15

I noticed today that the MariaDB 5.5 apt repository for Ubuntu 10.04 no longer exists. At first I thought this was due to the EOL for 10.04 but the 10.0 apt repository is still present in the repository configurator. I couldn’t find an announcement announcing the termination of these repositories. Is this a temporary oversight or are these apt repositories gone forever? Thanks, — Bryan Traywick

2 1

[Maria-discuss] New Question: MariaDB service fails to start after InnoDB/XtraDB crash
by AskMonty KB 11 Jun '15

11 Jun '15

Hello, A new question has been asked in "XtraDB and InnoDB" by kushkash. Please answer it at http://mariadb.com/kb/en/mariadb-service-fails-to-start-after-innodbxtradb-… as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi, I've been running a script, inserting a large amount of data to several MySQL tables. Apparently, InnoDB crashed at some point (when ibdata1 reached ~1.6 Gb). I had to restart the computer and when I tried to restart MariaDB service, I got the following errors: Jun 11 08:58:02 honolulu.bi.technion.ac.il mysqld_safe[7970]: 150611 8:58:02 InnoDB: Operating system error number 5 in a file operation. Jun 11 08:58:02 honolulu.bi.technion.ac.il mysqld_safe[7970]: InnoDB: Error number 5 means 'Input/output error'. Jun 11 08:58:02 honolulu.bi.technion.ac.il mysqld_safe[7970]: InnoDB: Some operating system error numbers are described at Jun 11 08:58:02 honolulu.bi.technion.ac.il mysqld_safe[7970]: InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html Jun 11 08:58:02 honolulu.bi.technion.ac.il mysqld_safe[7970]: InnoDB: File operation call: 'read'. Jun 11 08:58:02 honolulu.bi.technion.ac.il mysqld_safe[7970]: InnoDB: Cannot continue operation.' Jun 11 08:58:02 honolulu.bi.technion.ac.il systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE Jun 11 08:58:02 honolulu.bi.technion.ac.il systemd[1]: mariadb.service: control process exited, code=exited status=1 Jun 11 08:58:02 honolulu.bi.technion.ac.il systemd[1]: Failed to start MariaDB database server. Jun 11 08:58:02 honolulu.bi.technion.ac.il systemd[1]: Unit mariadb.service entered failed state. I checked /var/log/mariadb/mariadb.log to find out what might have caused the crash: 140911 10:40:38 InnoDB: Error: Write to file ./ibdata1 failed at offset 0 220200960. InnoDB: 1048576 bytes should have been written, only 0 were written. InnoDB: Operating system error number 28. InnoDB: Check that your OS and file system support files of this size. InnoDB: Check also that the disk is not full or a disk quota exceeded. InnoDB: Error number 28 means 'No space left on device'. InnoDB: Some operating system error numbers are described at InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html 140911 10:40:38 InnoDB: Assertion failure in thread 139942049851136 in file os0file.c line 4382 InnoDB: Failing assertion: ret InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately a150505 07:59:38 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 150505 07:59:38 mysqld_safe WSREP: Running position recovery with --log_error='/var/lib/mysql/wsrep_recovery.SFCcCu' --pid-file='/var/lib/mysql/honolulu.bi.technion.ac.il-recover.pid' 150505 07:59:41 mysqld_safe WSREP: Recovered position 00000000-0000-0000-0000-000000000000:-1 150505 7:59:41 [Note] WSREP: wsrep_start_position var submitted: '00000000-0000-0000-0000-000000000000:-1' 150505 7:59:41 InnoDB: The InnoDB memory heap is disabled 150505 7:59:41 InnoDB: Mutexes and rw_locks use GCC atomic builtins 150505 7:59:41 InnoDB: Compressed tables use zlib 1.2.8 150505 7:59:41 InnoDB: Using Linux native AIO 150505 7:59:41 InnoDB: Initializing buffer pool, size = 128.0M 150505 7:59:41 InnoDB: Completed initialization of buffer pool 150505 7:59:41 InnoDB: highest supported file format is Barracuda. 150505 7:59:41 InnoDB: Waiting for the background threads to start 150505 7:59:42 Percona XtraDB (http://www.percona.com) 5.5.37-MariaDB-35.0 started; log sequence number 458740332 150505 7:59:42 [Note] Plugin 'FEEDBACK' is disabled. 150505 7:59:42 [Note] Server socket created on IP: '0.0.0.0'. 150505 7:59:42 [ERROR] mysqld: Table './mysql/user' is marked as crashed and should be repaired 150505 7:59:42 [Warning] Checking table: './mysql/user' 150505 7:59:42 [ERROR] mysql.user: 1 client is using or hasn't closed the table properly 150505 7:59:42 [ERROR] mysqld: Table './mysql/db' is marked as crashed and should be repaired 150505 7:59:42 [Warning] Checking table: './mysql/db' 150505 7:59:42 [ERROR] mysql.db: 1 client is using or hasn't closed the table properly 150505 7:59:42 [Note] Event Scheduler: Loaded 0 events 150505 7:59:42 [Note] WSREP: Read nil XID from storage engines, skipping position init 150505 7:59:42 [Note] WSREP: wsrep_load(): loading provider library 'none' 150505 7:59:42 [Note] [Debug] WSREP: dummy_init 150505 7:59:42 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.38-MariaDB-wsrep' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server, wsrep_25.10.r3997 150608 04:43:00 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 150608 04:43:00 mysqld_safe WSREP: Running position recovery with --log_error='/var/lib/mysql/wsrep_recovery.7Yb3bw' --pid-file='/var/lib/mysql/honolulu.bi.technion.ac.il-recover.pid' 150608 04:43:13 mysqld_safe WSREP: Failed to recover position: '150608 4:43:01 InnoDB: The InnoDB memory heap is disabled 150608 4:43:01 InnoDB: Mutexes and rw_locks use GCC atomic builtins 150608 4:43:01 InnoDB: Compressed tables use zlib 1.2.8 150608 4:43:01 InnoDB: Using Linux native AIO 150608 4:43:01 InnoDB: Initializing buffer pool, size = 128.0M 150608 4:43:01 InnoDB: Completed initialization of buffer pool 150608 4:43:01 InnoDB: highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 4676403586 150608 4:43:01 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Error: tried to read 1048576 bytes at offset 0 1048576. InnoDB: Was only able to read 655360. 150608 4:43:13 InnoDB: Operating system error number 5 in a file operation. InnoDB: Error number 5 means 'Input/output error'. InnoDB: Some operating system error numbers are described at InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html InnoDB: File operation call: 'read'. InnoDB: Cannot continue operation.' I have to say that there shouldn't be any disk space problem: df -h Filesystem Size Used Avail Use% Mounted on /dev/sda5 819G 27G 750G 4% / devtmpfs 7.9G 0 7.9G 0% /dev tmpfs 7.9G 0 7.9G 0% /dev/shm tmpfs 7.9G 8.9M 7.9G 1% /run tmpfs 7.9G 0 7.9G 0% /sys/fs/cgroup tmpfs 7.9G 12K 7.9G 1% /tmp /dev/sda3 1.9T 123M 1.8T 1% /home /dev/sda2 477M 112M 336M 25% /boot The MariaDB is currently installed in default configuration. I really need help on this issue... 1. What might have cause the problem? Do I have to configure some InnoDB parameters? Maybe the ibdata file size is a problem? 2. How to recover my data and restart the MariaDB service? (I tried all kind of tips I found in other forums, but I couldn't backup the ibdata1 file or start InnoDB in recovery mode). -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/mariadb-service-fails-to-start-after-innodbxtradb-…

1 0

[Maria-discuss] MariaDB 5.5.44 now available
by Daniel Bartholomew 11 Jun '15

11 Jun '15

The MariaDB project is pleased to announce the immediate availability of MariaDB 5.5.44. This is a Stable (GA) release. See the Release Notes and Changelog for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 5.5.44 - Release Notes: https://mariadb.com/kb/en/mariadb-5544-release-notes - Changelog: https://mariadb.com/kb/en/mariadb-5544-changelog - Downloads: https://downloads.mariadb.org/mariadb/5.5.44 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/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] New Question: restrictions on EVENTs in Galera?
by AskMonty KB 11 Jun '15

11 Jun '15

Hello, A new question has been asked in "MariaDB Galera Cluster" by rjasdf. Please answer it at http://mariadb.com/kb/en/restrictions-on-events-in-galera/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Can EVENTs and TRIGGERs be used in Galera Cluster? If so, are there any restrictions? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/restrictions-on-events-in-galera/

1 0

[Maria-discuss] Multi master replication with dynamic IP
by Joseph Onyilo 10 Jun '15

10 Jun '15

Hello, I want setup MariaDB to be able to use dynamic IP address for a multi master replication setup. Although one of the instance will have static IP address. Also, the instances are are in different geographical location. Is this setup possible? Joe.

1 0