April 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] 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] Password Complexity Plugin
by Adam Scott 01 May '15

01 May '15

Hi, I noticed MySQL 5.6 has a validate_password plugin. Since it's open-source, can we add it to MariaDB 5.5. Or is that bad form, a hot potato, etc.? We need the features of this plugin in our environment, and the PAM integration is looking not a good "fit" (though it's working great). (I'm trying to get it to compile in right now, but it's having issues with the includes so far...working on it). Thanks, Adam C. Scott

4 7

[Maria-discuss] R: Re: issue with 2 node cluster
by absolutely_free＠libero.it 30 Apr '15

30 Apr '15

Hi Alexey, thank you for your reply. Yes I am using wsrep_sst_method="xtrabackup-v2" Should I simply delete ALL from /var/lib/mysql/* and restart mysql again? Thank you again. >----Messaggio originale---- >Da: alexey.yurchenko(a)galeracluster.com >Data: 30/04/2015 15.09 >A: <absolutely_free(a)libero.it> >Cc: <maria-discuss(a)lists.launchpad.net>, "Maria-discuss"<maria-discuss- bounces+alexey.yurchenko=codership.com(a)lists.launchpad.net> >Ogg: Re: [Maria-discuss] issue with 2 node cluster > >On 2015-04-30 15:13, absolutely_free(a)libero.it wrote: >> Hi, >> I have 2 mariadb nodes on Debian 7.8. >> After a crash on first node, I can't restart the second one. > >> how can I solve this issue? >> First node is ok, all databases are available. >> Thank you > >It depends. If you are using rsync or xtrabackup SST method, you can >just delete everything from the data directory. >

1 0

[Maria-discuss] issue with 2 node cluster
by absolutely_free＠libero.it 30 Apr '15

30 Apr '15

Hi, I have 2 mariadb nodes on Debian 7.8. After a crash on first node, I can't restart the second one. The message I see in /var/log/mysql/error.log is: 150430 12:55:48 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... 150430 12:55:48 InnoDB: Error: page 7 log sequence number 565808461991 InnoDB: is in the future! Current system log sequence number 565772838412. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. See InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html InnoDB: for more information. 150430 12:55:48 InnoDB: Error: page 2 log sequence number 565929024045 InnoDB: is in the future! Current system log sequence number 565772838412. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. See InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html InnoDB: for more information. .. .. 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 after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 150430 12:55:48 [ERROR] mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. To report this bug, see http://kb.askmonty.org/en/reporting-bugs We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Server version: 5.5.42-MariaDB-1~wheezy-wsrep-log key_buffer_size=134217728 read_buffer_size=2097152 max_used_connections=0 max_threads=4098 thread_count=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 25383890 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0x0 thread_stack 0x30000 /usr/sbin/mysqld(my_print_stacktrace+0x2b)[0x7f3cc9d0c53b] /usr/sbin/mysqld(handle_fatal_signal+0x422)[0x7f3cc993a4f2] /lib/x86_64-linux-gnu/libpthread.so.0(+0xf0a0)[0x7f3cc900f0a0] /lib/x86_64-linux-gnu/libc.so.6(gsignal+0x35)[0x7f3cc786c165] /lib/x86_64-linux-gnu/libc.so.6(abort+0x180)[0x7f3cc786f3e0] /usr/sbin/mysqld(+0x73b5dc)[0x7f3cc9b795dc] /usr/sbin/mysqld(+0x709617)[0x7f3cc9b47617] /usr/sbin/mysqld(+0x70a41a)[0x7f3cc9b4841a] /usr/sbin/mysqld(+0x6f25d8)[0x7f3cc9b305d8] /usr/sbin/mysqld(+0x6c4733)[0x7f3cc9b02733] /usr/sbin/mysqld(+0x6b72d2)[0x7f3cc9af52d2] /usr/sbin/mysqld(+0x6b812c)[0x7f3cc9af612c] /usr/sbin/mysqld(+0x6bad41)[0x7f3cc9af8d41] /usr/sbin/mysqld(+0x6a3db5)[0x7f3cc9ae1db5] /usr/sbin/mysqld(+0x659d1f)[0x7f3cc9a97d1f] /usr/sbin/mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x48)[0x7f3cc993ce78] /usr/sbin/mysqld(+0x3bb550)[0x7f3cc97f9550] /usr/sbin/mysqld(_Z11plugin_initPiPPci+0x652)[0x7f3cc97fa592] /usr/sbin/mysqld(+0x32b118)[0x7f3cc9769118] /usr/sbin/mysqld(_Z11mysqld_mainiPPc+0x2036)[0x7f3cc976b9b6] /lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xfd)[0x7f3cc7858ead] /usr/sbin/mysqld(+0x321a4d)[0x7f3cc975fa4d] The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash.' how can I solve this issue? First node is ok, all databases are available. Thank you

1 0

[Maria-discuss] New Question: Licensing of Mariadb connector/C
by AskMonty KB 30 Apr '15

30 Apr '15

Hello, A new question has been asked in "MariaDB Connector/C" by kmrsankar. Please answer it at http://mariadb.com/kb/en/licensing-of-mariadb-connectorc/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- hi, I have downloaded the source and binary of Maria DB Connector/C library. I see that one of the files mysql_async.h has a GPL license in it. But as such the whole library was declared as LGPL. Is this single GPL license header contaminates this library ? Thanks, Muthu -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/licensing-of-mariadb-connectorc/

1 0

[Maria-discuss] Calculating memory consumption for MariaDB in Munin plugin
by Anna Jonna Armannsdottir 30 Apr '15

30 Apr '15

Hello, I am new to this list and the issue I am presenting here might surely be answered on the development list, but I do not want to impose this issue on the developers, I just hope some of them listen here. There seems to be general aggreement on the calculation of memory consumption for MariaDB and MySQL. There is however an insignificant dispute but very important dispute over the role of tmp_table_size . https://mariadb.com/kb/en/mariadb/server-system-variables/#tmp_table_size The dispute is whether tmp_table_size is a session variable or a kind of global variable. Thus MariaDB memory consumption is calculated as: max_connections * (sum of session variables) + (global variables) Thus if tmp_table_size is considered to be a session variable, it will be multiplied by max_connections . In my config the settings are: max-connections = 1000 tmp-table-size = 32M max-heap-table-size = 32M In my case this means that MariaDB could consume 1000 + 32M + 1000 + (sum of sess.var.) + (global variables) . This point of view was put forward in a discussion on mysql.com: QUOTE: > I disagree with how the previous comment handles the tmp_table_size > value. They treat it as a single allocation on the global scope when > for memory consumption purposes it is more in line with a per thread > buffer. > > A single connection/query can use a single or multiple temporary > tables in the duration of its processing. The connections do not use a > single temporary table "area" reserved just for that purpose. > > If you are going to use a formula for memory consumption, the > tmp_table-size should be located with the other per thread buffers - > not in the single allocation listing. https://dev.mysql.com/doc/refman/5.0/en/memory-use.html I honestly do not believe, this the case. On this discussion list we have examples that have tmp_table_size = 256 MB and max-connections = 1000 . 256 Gigabytes!!! https://lists.launchpad.net/maria-discuss/msg02376.html On the other hand we have sites like: http://www.mysqlcalculator.com/ This site calculates memory consumption in a way I find more agreeable, by not multiplying tmp_table_size by max_connections. This is an issue in a Munin monitoring plugin that reports mysql connection memory. The calculation code is from lines 1941 to 1949. https://github.com/munin-monitoring/munin/blob/devel/plugins/node.d/mysql_ $data->{'mysql_connection_memory'} = $data->{'read_buffer_size'} + $data->{'read_rnd_buffer_size'} + $data->{'sort_buffer_size'} + $data->{'join_buffer_size'} + $data->{'binlog_cache_size'} + $data->{'thread_stack'} + ( $tmp_table_size >= $max_heap_table_size ? $tmp_table_size : $max_heap_table_size ) + ( $data->{'tokudb_read_buf_size'} || 0 ); # wsrep_thread_count was separated from max_connections for mariadb-5.5.38 https://mariadb.atlassian.net/browse/MDEV-6206 $data->{'mysql_connection_memory'} *= $data->{'max_connections'} + ( $data->{'wsrep_thread_count'} || 0 ); I ask for your opinion and support for correcting this issue, so that we may get correct reports in Munin. -- Anna Jonna Armannsdottir <annaj(a)hi.is> University of Iceland Computing Services -- Anna Jonna Armannsdottir <annajonna(a)gmail.com>

2 1

[Maria-discuss] Simple Password Check plugin Backported to 5.5
by Adam Scott 30 Apr '15

30 Apr '15

It's taken some time, but I have backported the simple_password_check plugin and the password api to MariaDB 5.5 The mysql-test is succeeding on all but the test of the strict_password_validation variable. My question is, should a pure backport include support for strict_password_validation variable ( https://mariadb.com/kb/en/mariadb/server-system-variables/#strict_password_…) ? I suspect yes, but wanted to ask to start a further discussion: namely if I submit this patch will it be included in future MariaDB 5.5 releases? It will help customer enforce password complexity beneficial for security auditing. Let me know and I'll submit patch or submit via recommended method. Thank you, Adam Scott p.s. I sent this message on Apr 27 as a reply to the mailing lists for a thread started back in January, but I haven't seen my reply show up in the public archive. Colin suggested I let you know when I have backported the plugin.

1 0

[Maria-discuss] user map auth plugin
by Felipe Gasper 29 Apr '15

29 Apr '15

So, I recently wrote an auth plugin with the intent of implementing a simple user map, authenticated against the hashed password that would be retrieved via the normal password entry scheme. I got far too far along before realizing that this scheme basically requires that either the client use a plugin to communicate with the server auth plugin, or I use the old MySQL auth plugin. ...or is there some way to re-initialize secure MySQL auth from the server auth plugin?? It would really shoot my intents in the gut if every client that wants to log in with user mapping needs to be 5.5+ and to have a plugin...:-( -FG Sent from my android device.

2 1

[Maria-discuss] Moderator: please REMOVE ME
by grantksupport＠operamail.com 29 Apr '15

29 Apr '15

I deleted my launchpad account. Still getting emails from this ML. Can't re-subscribe to launchpad to unsubscribe from the ML. Catch22 ... Could a kindly Moderator please REMOVE ME? Thx.

2 1