June 2015 - discuss - lists.mariadb.org

Re: [Maria-discuss] Can't connect to local MySQL server through socket
by Sylvain Raybaud 24 Jun '15

24 Jun '15

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 24/06/2015 15:00, Guillaume Lefranc wrote: > > > Nope, the database is empty (I just created it with > mysql_install_db; also all nodes are fresh installs). Only the > first node has SYNCED in its log indeed: # grep SYNCED > buildroot.err 700101 0:01:10 [Note] WSREP: Shifting JOINED -> > SYNCED (TO: 0) > > The two others show neither SYNCED or any reference to mysql.sock. > > > > I forgot to mention in my previous mail that after starting mariadb > in the first node I run: > > mysql -u root --execute="SET GLOBAL > wsrep_cluster_address='gcomm://';" > > > Don't do the above. Since you are starting the cluster with > --wsrep_new_cluster, that's enough. I'm not sure of what it would > do but I'd rather avoid to bootstrap the cluster two times. If I don't, the cluster does not start at all, all I get is a disconnected cluster of size 0 on node #1, exactly as described here: https://mariadb.com/blog/mariadb-galera-cluster-quirk-bootstrapping > >> Does this mean that mariadb tries to sync nodes but for some >> reason the script hangs, the nodes are never sync and remain >> unusable? > > I'm afraid that must be something along those lines. I have the > following suspicions: > > * SELinux or Apparmor not disabled, causing the SST to hang > forever. * Ports not open on the firewall (for the record you need > 3306, but also 4444, 4567 and 4568). > Ok, I'm following this lead. I'll let you know. > -- Guillaume Lefranc Consultant / RemoteDBA Services - MariaDB > Corporation Ab - -- Sylvain Raybaud www.green-communications.fr -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBAgAGBQJViqtSAAoJEEkkwl4JtJ9yDtMP/RC8SkWQSd4iU2FMJn8Hj1LW K4Y9n0FETIu7pUpFiqAg13l+4ocEblv+SlYPPzAI5bdpBPVTA+EG7ypkyQgolPxX 5TQUUGjIM0rN675IVcDQalX8KPytSR8LkNDvB95oE71fKjOB9Ne71P8YpfdRMZgU 9Y7HMFPxckJulZOcoKb/4erOC9txgn69MY9YZ4QVQKahxK6vKfnbOOkS88SHNojy KYeLrMWR6Jk0wmPz4ivMveRzJfGrCI8cxWGi26btxGfhWXUvq2/Wv0nYrfByN9ZH kRd7Lo5RHnTzwT+EGNVtgkesA1L6UNgUo/N3fW8hGSnpIFuK+4CMcHjAsG6IxXVl iX1uP4H56OydRZh3tzdnY7+vjAN6dz6J6bo1X/mtaUMcKk+f/whkbxy6+Ggeq92d gWjoECDPJUd4R5JzTy4uMDhstg3RKX0KL32AGajPUJmFLAhmA9vkSRh1Wz9MVIBQ /cPMSndhLFjlSRJAfdh2GHy8y1xCjLHxR7k1qQqf7AhKTYCE2Pq21ZTYRdJZkP6S vo0W+KXcV4CvR0+rT15DJCUodwSak+r6GOCJ86koVs64JtcSyuLaqTKvtn0AjX4y r+JG5G9WwKcbAx77BQk75EmL86rnuICFaXhXqe6HYs0Pi7xFuKCtLYZrAFtn2r5H YhApUDevV8dNh4Vwl6wn =iVBf -----END PGP SIGNATURE-----

1 0

Re: [Maria-discuss] Can't connect to local MySQL server through socket
by Sylvain Raybaud 24 Jun '15

24 Jun '15

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 24/06/2015 14:38, Guillaume Lefranc wrote: > Also if I'm correct, you must have rsync running on the processlist > of node 2. As mentioned in my previous email, I don't have much data in this db. However, I do have rsync running on nodes #2 and #3, even long after I started mariadb: # ps aux | grep rsync 464 mysql sh -c wsrep_sst_rsync --role 'joiner' --address '192.168.1.80' --auth '' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --parent '457' --binlog '/var/lib/mysql/mariadb-bin' 465 mysql {wsrep_sst_rsync} /bin/bash -ue /usr//bin/wsrep_sst_rsync --role joiner --address 192.168.1.80 --auth --datadir /var/lib/mysql/ --defaults-file /etc/mysql/my.cnf --parent 457 --binlog /var/lib/mysql/mariadb-bin 493 mysql rsync --daemon --no-detach --port 4444 --config /var/lib/mysql//rsync_sst.conf 14589 root grep rsync Does this mean that mariadb tries to sync nodes but for some reason the script hangs, the nodes are never sync and remain unusable? - -- Sylvain Raybaud www.green-communications.fr -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBAgAGBQJViqk1AAoJEEkkwl4JtJ9yawwP/jH6XsdNLwjYgo40baiMn6/e lo6uls7yq+GHnw3GC3R/YBXYqRpBWv2rPi6hKf1wFnXlyP7rN3xY3zR3bnICH0G9 Y/y+RLdeWdzEFncfz7Vxq/kY771ITjAsha+eGdC9ShUkC61FyQ1lOXjj/5N6eHpS UQhkuoHJoP8/sdLN6Q3jnBUOUKZZIpSG7QsRG+Th8RuERPkJP+slDAkTsvM1qe6+ kCSaQugfUrtobxm5glAGSYlprRQ2EojTYDnS+qpqeBKwwAPA8g+u/a25AayDl5MB 32fWBH6iy2DjqnXnRdeS7BX/jhGAXvy8zJGylnfLakRQFDEUScpjZlOB2Bp0UtuC qKRkV35eDDu1LTGHCbWu5dQx1D3TsbSIi571Ffiw4vZtwVgTRsT8LLV8opnA6aa1 ixbEfz3P5lujB4cDASMDsrbTYHQ4MU78G1QnXjVRMQS7PwPaDjAk/zDW9krengyS jfZSR7w1fYOsOyb0elxEl2tzkW6pDoGhAQ3P3ETM++ruyv2gjoxYVaN+GH7vPlee Ooz75NoDPg6t97TfOP1B60QhSHK7Xwp7ISBSJIZd1LXM64MtgxB2vh+bTdGV/C5x JXVvHmihBdsqX+4e78WSxoydb4ZyBDRLG+0jwDmPoUcYuYYxvtH1DYgdGBgfeN54 T5+/U42/4oSSZ1C+HSGz =OXeD -----END PGP SIGNATURE-----

1 0

Re: [Maria-discuss] Can't connect to local MySQL server through socket
by Sylvain Raybaud 24 Jun '15

24 Jun '15

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 24/06/2015 14:38, Guillaume Lefranc wrote: > Sylvain, > > the logs tell that node 2 and 3 have joined the cluster, but that > they are not yet in sync. This is very clear in logs of node 2: > > 700101 0:00:35 [Note] WSREP: *State transfer required:* Group > state: 7cf7113a-1dd3-11b2-a623-76b507fb8ed5:0 Local state: > 00000000-0000-0000-0000-000000000000:-1 700101 0:00:35 [Note] > WSREP: New cluster view: global state: > 7cf7113a-1dd3-11b2-a623-76b507fb8ed5:0, view# 11: Primary, number > of nodes: 3, my index: 0, protocol version 3 700101 0:00:35 > [Warning] WSREP: *Gap in state sequence. Need state transfer.* > 700101 0:00:35 [Note] WSREP: Running: 'wsrep_sst_rsync --role > 'joiner' --address '192.168.1.80' --auth '' --datadir > '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --parent > '420' --binlog '/var/lib/mysql/mariadb-bin' ' > > I assumed that you started your cluster empty, which might not be > the case. If you have several gigabytes of data on the first node, > you have to allow some time until rsync has finished copying the > data to the other nodes. You can do that by tailing the error log > of node 2 until the SYNCED state keyword appears. Also if I'm > correct, you must have rsync running on the processlist of node 2. Nope, the database is empty (I just created it with mysql_install_db; also all nodes are fresh installs). Only the first node has SYNCED in its log indeed: # grep SYNCED buildroot.err 700101 0:01:10 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 0) The two others show neither SYNCED or any reference to mysql.sock. I forgot to mention in my previous mail that after starting mariadb in the first node I run: mysql -u root --execute="SET GLOBAL wsrep_cluster_address='gcomm://';" but you probably guessed since I'd get a different error if I hadn't. - -- Sylvain Raybaud www.green-communications.fr -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBAgAGBQJViqhmAAoJEEkkwl4JtJ9y7ioP/A5sGSaNrJ8jJhPaaN38XIEQ UAjUxUFyQQBEuzmvo3LY5PoTXKNXjWzhMHL6+bFqvIYb7RXa2mC/jm/gowuNIE8v jBz8NOjUbH/nayWFQquguEOe1EBO2671CNWsGmpqzKkxj5CVq4SykTk34+d6xUye 7EubHiOUKYK5BuILY3zBaVzxGeBlHaZsZCx7C65a81bgB+0wOMFQED2u5u24VbwA pm6+q+psR8O0H6EdeMzgPCvxgQZeGi+HahXWAZuts3/mkTbFpEgFeg/xt8x9jIg9 vz6K6Q5TD43NaMs+XD86Z4mgGPBaEapZKc9YQV8Ge58gngkgZt9L4q+mi//ISZ1z CCbsQlUd6JqLethWLAIr0VdXLU26P28kVza4aAqjnkl9Pz1s4CmweSg4tuo7avXk DDV9//7kOFbAKdLJX+egpHu+fb+WwgWeSKNzdauKjTGfH+qPSPb10j2GSowedEK/ WzCWsZb0GZyn8gHRPMDC6olSs+daCp8imWqKw70LOAjCzMcBrFG/ynMWB8vBoHqU Q5KgEhfUStDLMMZkqgI1Ov91LZDPPG2zr0mjwe7+k7xc5GO6gtih/F8atruT2PRu cdgEvapcR8Qp+fsBlzVdeK15yVrxaWrZDDhh+YaYuLS2kzDW3KGwqHUNYykhyLcl QgaIDguJcH4ye95Sb2Cv =CpJL -----END PGP SIGNATURE-----

1 0

[Maria-discuss] Can't connect to local MySQL server through socket
by Sylvain Raybaud 24 Jun '15

24 Jun '15

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi I'm setting up a 3 nodes cluster of mariadb+galera. The cluster seems to be created properly: mysql -u root -p -e 'SELECT VARIABLE_VALUE as "cluster size" FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="wsrep_cluster_size"' Enter password: +--------------+ | cluster size | +--------------+ | 3 | +--------------+ Problem is, I can only connect to mysql on the first node I create. On the other nodes it gives me: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111 "Connection refused") /tmp/mysql.sock does exist and has correct permissions. However it does not seem to be in listening state since it is not shown by netstat -l. I feel this must be some simple configuration problem but I'm quite stuck. My configuration can be found at: http://pastebin.com/gVie8uKp Note that since I'm compiling and running mariadb in buildroot I do not have /etc/mysql/debian.cnf. Any idea? Thanks, - -- Sylvain Raybaud www.green-communications.fr -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBAgAGBQJVipzlAAoJEEkkwl4JtJ9yn8MP/A3Ld0XxNmkO8DCh2znYud6u E3Wiuw35v+wD0EZx05H4nFT2eI/4G8tMCtWy1B0hJx0qiuyjMItXKMqL6yOIrZfx cA4WAikesCwrLCXZgmL5xj0/Ow+8hzNLctCxMNAmJvYUPcW4YyJkinA3C1ch65aa arrfg1/Z8B40VyapjeOc1T7jr97S1465+YMmB6G5aD/3aFBORYWEjqhMSutRKhfJ K7J6KIFQQwrL0gRqtiPwsMxsD2QW/bc8hQQvMz0Nuy7iriY3YZCCvPqtFA+r8cnp 6P4GB0Bp5YL7HY7SO5YuAKiUYmtb5koBoD1oMtuFBVNIFLTP5X8yWErZr2pZXRbx 97oCBWisp3VqQjPCJJBYLrmP4qx5aYU6+C/vcc51Kr+OEFOLeyszVFM85bPx5+ZL d8W5iuOUSBpguQ3P3dQV1PnBWGqe0FcotSHnDSQMD6lxFfQ/s09er3okqob/Wo1k cqKkqPfjMNUaXcE4yf6/UN/ytNMOnPyHVPQNaIsQmiS+TYoIEtf6T2nItMjfoCAz OJ1PaACOzXrvx3rClT/ay6w/TAjILM0O9GiIYsHhdJRrapgu59r57olZouTbmooo VpU7fKq9LnF3dgZOMFW1VBnegfseXWuyCmzXEXx7fiNpvzEukmDrhFpPAuNYdJ9Y Md+Mm44tp+FpWhFfrU6u =ybAn -----END PGP SIGNATURE-----

2 2

[Maria-discuss] Bug in MAX() function?
by Benoit Panizzon 23 Jun '15

23 Jun '15

Hi all I stumbled over that problem, while trying to graph mail traffic.. I have two different counters for emails sent with authentication and without. Aggregated per hour. I would like to total the both values per row, to create a graph of outgoing emails per hour. MariaDB [maildb]> select mail_out_anon,mail_out_auth from domaincounters; +---------------+---------------+ | mail_out_anon | mail_out_auth | +---------------+---------------+ | 8 | 58 | | 8 | 48 | | 4 | 63 | | 9 | 53 | | 2 | 36 | | 0 | 12 | | 3 | 2 | | 0 | 2 | | 0 | 6 | | 0 | 2 | | 0 | 9 | | 0 | 44 | | 14 | 63 | | 0 | 96 | | 7 | 43 | | 4 | 61 | | 2 | 43 | | 2 | 66 | | 0 | 86 | | 6 | 77 | | 6 | 55 | | 0 | 63 | | 6 | 48 | | 2 | 52 | +---------------+---------------+ > select mail_out_anon+mail_out_auth as mail_out_total from domaincounters; +-----------------------------+ | mail_out_anon+mail_out_auth | +-----------------------------+ | 70 | | 56 | | 67 | | 62 | | 38 | | 12 | | 5 | | 2 | | 6 | | 2 | | 9 | | 44 | | 77 | | 96 | | 50 | | 65 | | 45 | | 68 | | 86 | | 83 | | 61 | | 63 | | 54 | | 54 | +-----------------------------+ Now I need the max value of that addition to auto-scale the graph. MariaDB [maildb]> select MAX(mail_out_anon+mail_out_auth) +----------------------------------+ | MAX(mail_out_anon+mail_out_auth) | +----------------------------------+ | 187 | +----------------------------------+ What did MariaDB count here? I was expecting a value 96 which is the largest value of all the additions. Also trying various ways of first using GREATEST() always returns a way to high value. Is it a bug, or did I do something wrong? Mit freundlichen Grüssen Benoit Panizzon -- I m p r o W a r e A G - ______________________________________________________ Zurlindenstrasse 29 Tel +41 61 826 93 07 CH-4133 Pratteln Fax +41 61 826 93 02 Schweiz Web http://www.imp.ch ______________________________________________________

6 13

[Maria-discuss] View missing from EXPLAIN after ORDER BY removed from definition
by Rhys Campbell 23 Jun '15

23 Jun '15

Hello All, I had an interesting problem presented to me this morning. A seemingly simple query was running for ages and filling up /tmp causing MariaDB to crash. Now, this isn't perhaps the best designed database but the query didn't seem to be that bad. T1 is a table but t2 is a view (modified definitions provided below) SELECT SQL_NO_CACHE p.col1 FROM t1 p LEFT OUTER JOIN t2 ot ON p.col1 = ot.col2 LIMIT 500; This has the following explain plan... id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY P1 index \N -- 4 \N 85977207 100 Using index; Using temporary; Using filesort 1 PRIMARY p1 ref IX_1 IX_1 8 -- 1 100 Using where; Using index 3 DEPENDENT SUBQUERY p2 ref IX_1 IX_1 8 -- 1 100 Using where; Using index To rectify this I modified an index that was use by the view (note the key_len = 8 in above, went to 106 with the new index when EXPLAINed separately.). I also removed the ORDER BY from the view. After this the query performance much better returning in ~ 250 milliseconds. Removal of the ORDER By was primarily responsible for the performance improvement. However the EXPLAIN plan now returns this... id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY p index \N idx 4 \N 85977207 100 Using index Why is the view now missing from the EXPLAIN? Is this a bug or is there something else going on here? Cheers, Rhys CREATE TABLE `t1` ( `col1` BIGINT(20) NOT NULL, `col2` ENUM(val1, val2, val3) COLLATE latin1_bin NOT NULL, `col3` CHAR(2) CHARACTER SET latin1 NOT NULL, `col4` INT(10) UNSIGNED DEFAULT NULL, `col5` TINYINT(1) DEFAULT '1', `col6` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `col7` VARCHAR(100) CHARACTER SET latin1 DEFAULT NULL, `col8` VARCHAR(10) CHARACTER SET latin1 DEFAULT NULL, `col9` VARCHAR(32) CHARACTER SET latin1 DEFAULT NULL, `col10` VARCHAR(10) CHARACTER SET latin1 DEFAULT NULL, `col11` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, `col12` VARCHAR(1024) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, `col13` DECIMAL(16,6) DEFAULT NULL, `col14` DECIMAL(16,6) DEFAULT NULL, `col15` BIGINT(20) DEFAULT NULL, `col16` BIGINT(20) DEFAULT NULL, `col17` BIGINT(20) DEFAULT NULL, `col18` BIGINT(20) DEFAULT NULL, `col19` CHAR(8) CHARACTER SET latin1 DEFAULT NULL, `col20` VARCHAR(45) CHARACTER SET latin1 DEFAULT NULL, `col21` VARCHAR(45) CHARACTER SET latin1 DEFAULT NULL, `col22` VARCHAR(45) CHARACTER SET latin1 DEFAULT NULL, `col23` VARCHAR(45) CHARACTER SET latin1 DEFAULT NULL, `col24` VARCHAR(45) CHARACTER SET latin1 DEFAULT NULL, `col25` VARCHAR(45) CHARACTER SET latin1 DEFAULT NULL, `col26` VARCHAR(45) CHARACTER SET latin1 DEFAULT NULL, `col27` VARCHAR(45) CHARACTER SET latin1 DEFAULT NULL, `col28` VARCHAR(45) CHARACTER SET latin1 DEFAULT NULL, `col29` VARCHAR(45) CHARACTER SET latin1 DEFAULT NULL, `col30` VARCHAR(45) CHARACTER SET latin1 DEFAULT NULL, PRIMARY KEY (`col1`), .. /* REMOVED **/ KEY `idx_lastupdated` (`col6`) ) ENGINE=TokuDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin `compression`='tokudb_zlib' The join view has the following definition... SELECT /* COLS REMOVED */ FROM `t2` `p1` WHERE (`p1`.`col1` = (SELECT `p2`.`col2 ` FROM `t2` `p2` WHERE ((`p2`.`col2` = `p1`.`mcol2`) AND (`p2`.`source` = 'src')) ORDER BY `p2`.`col3` DESC LIMIT 1)) ORDER BY `p1`.`col2`; 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.

3 5

[Maria-discuss] Upgrading mariadb-galera-server
by Tom Worster 19 Jun '15

19 Jun '15

Can I upgrade a production cluster from 10.0.15 to 10.0.19 while the cluster remains in service, upgrading one node at a time?

4 7

[Maria-discuss] MariaDB 10.0.20 now available
by Daniel Bartholomew 18 Jun '15

18 Jun '15

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.0.20. This is a Stable (GA) release. See the Release Notes and Changelog for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.0.20 - Release Notes: https://mariadb.com/kb/en/mariadb-10020-release-notes - Changelog: https://mariadb.com/kb/en/mariadb-10020-changelog - Downloads: https://downloads.mariadb.org/mariadb/10.0.20 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] New Question: How can i add another new slave to an existing replication configuration?
by AskMonty KB 16 Jun '15

16 Jun '15

Hello, A new question has been asked in "Standard Replication" by wschwauer. Please answer it at http://mariadb.com/kb/en/how-can-i-add-another-new-slave-to-an-existing-rep… as the person asking the question may not be subscribed to the mailing list. -------------------------------- How can i add another new slave to an existing master slave replication configuration? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/how-can-i-add-another-new-slave-to-an-existing-rep…

1 0

[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