- discuss - lists.mariadb.org

[Maria-discuss] nofile and nproc settings for mysql user in /etc/security/limits.conf
by Kaushal Shriyan 30 Dec '16

30 Dec '16

Hi, What should be the recommended nproc and nofile settings for mariadb or mysql user in /etc/security/limits.conf or /etc/my.cnf? I am running CentOS Linux version 6.8 Any help will be highly appreciable. Regards, Kaushal

2 1

[Maria-discuss] possible bug? #innodb-encryption-threads
by lejeczek 27 Dec '16

27 Dec '16

hi everyone I'm having a peculiar situation, a problem here with my config, which config is pretty galera-vanilla. Namely, if I set these: plugin-load-add = file_key_management.so file-key-management file-key-management-filename = /var/lib/mysql/.keys.txt innodb_file_per_table = 1 innodb-encrypt-log innodb-encryption-threads = 4 <= # this ! one here I get constant load on four cores, it would fluctuate just under %400 CPU (24*7). I'd assume that innodb-encryption-threads value causes it, and whatever the value might be it will be reflected in CPU usage. I wonder if you too see it. I my case I think it does something more to one system, it crashes the bloody thing to the extent where cold reboot is needed. MariaDB-client-10.1.19-1.el7.centos.x86_64 MariaDB-shared-10.1.19-1.el7.centos.x86_64 MariaDB-common-10.1.19-1.el7.centos.x86_64 MariaDB-server-10.1.19-1.el7.centos.x86_64 best, L

3 2

[Maria-discuss] error on show status query
by l vic 26 Dec '16

26 Dec '16

Have the following error trace from running SHOW STATUS LIKE 'wsrep_%' Any idea what could cause it? 2016-12-21 3:39:24 139668819036928 [Note] InnoDB: Online DDL : End of applying row log 2016-12-21 3:39:24 139668819036928 [Note] InnoDB: Online DDL : Completed 2016-12-21 3:43:29 139668818733824 [Note] InnoDB: Online DDL : Start 2016-12-21 3:43:29 139668818733824 [Note] InnoDB: Online DDL : Start reading clustered index of the table and create temporary files 2016-12-21 03:43:29 7f072e53ab00 InnoDB: Error: Write to file (merge) failed at offset 0. InnoDB: 1048576 bytes should have been written, only 901120 were written. InnoDB: Operating system error number 12. 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 12 means 'Cannot allocate memory'. InnoDB: Some operating system error numbers are described at InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html 2016-12-21 3:43:29 139668818733824 [Note] InnoDB: Online DDL : End of reading clustered index of the table and create temporary files java.sql.SQLNonTransientConnectionException: (conn:17945) Could not read resultset: unexpected end of stream, read 0 bytes from 4 Query is : SHOW STATUS LIKE 'wsrep_%' at org.mariadb.jdbc.internal.util.ExceptionMapper.get(ExceptionMapper.java:132) at org.mariadb.jdbc.internal.util.ExceptionMapper.getException(ExceptionMapper.java:101) at org.mariadb.jdbc.internal.util.ExceptionMapper.throwAndLogException(ExceptionMapper.java:77) at org.mariadb.jdbc.MariaDbStatement.executeQueryEpilog(MariaDbStatement.java:224) at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:258) at org.mariadb.jdbc.MariaDbStatement.executeQuery(MariaDbStatement.java:371) at com.verizon.mesos.Dbcheck.getStatus(Dbcheck.java:153) at com.verizon.mesos.Dbcheck.run(Dbcheck.java:103) at com.verizon.mesos.MariadbTask.handleHealthCheck(MariadbTask.java:310) at com.verizon.mesos.MariadbTask.executeCmd(MariadbTask.java:384) at com.verizon.mesos.MariadbTask.run(MariadbTask.java:450) at com.verizon.mesos.ExecutorTask.executeCmd(ExecutorTask.java:50) at com.verizon.mesos.ExecutorTask.run(ExecutorTask.java:115) at java.lang.Thread.run(Thread.java:745)

2 4

[Maria-discuss] MariaDB 10.2.3 and 5.5.54 now available
by Daniel Bartholomew 24 Dec '16

24 Dec '16

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.2.3 beta and MariaDB 5.5.54 stable (GA). See the Release Notes and Changelogs for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.2.3 - Release Notes: https://mariadb.com/kb/en/mdb-1023-rn/ - Changelog: https://mariadb.com/kb/en/mdb-1023-cl/ - Downloads: https://downloads.mariadb.org/mariadb/10.2.3 About MariaDB 10.2: - https://mariadb.com/kb/en/what-is-mariadb-102/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ MariaDB 5.5.54 - Release Notes: https://mariadb.com/kb/en/mdb-5554-rn/ - Changelog: https://mariadb.com/kb/en/mdb-5554-cl/ - Downloads: https://downloads.mariadb.org/mariadb/5.5.54 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/donate/ We hope you enjoy MariaDB! -- MariaDB Website - http://mariadb.org Twitter - http://twitter.com/mariadbfdn - 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] aria engine error
by Roberto Spadim 22 Dec '16

22 Dec '16

hi guys i got a crash and aria engine don't startup, any help? 161222 03:41:13 mysqld_safe Starting mysqld daemon with databases from /home/mysql/data/local/ 2016-12-22 3:41:13 139878188566400 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release. 2016-12-22 3:41:13 139878188566400 [Note] /usr/mariadb/mariadb/bin//mysqld (mysqld 10.1.5-MariaDB-log) starting as process 19217 ... 2016-12-22 3:41:13 139878188566400 [Note] mysqld: Aria engine: starting recovery recovered pages: 0% 3082852397% 0%161222 3:41:13 [ERROR] mysqld got signal 11 ; 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: 10.1.5-MariaDB-log key_buffer_size=629145600 read_buffer_size=4194304 max_used_connections=0 max_threads=52 thread_count=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1041422 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 0x48000 mysys/stacktrace.c:247(my_print_stacktrace)[0x7f37ee645b0b] sql/signal_handler.cc:153(handle_fatal_signal)[0x7f37ee1dfdb5] /lib64/libpthread.so.0(+0x10d00)[0x7f37ecd72d00] maria/ma_recovery.c:2142(exec_REDO_LOGREC_CLR_END)[0x7f37ee5683b7] maria/ma_recovery.c:588(display_and_apply_record)[0x7f37ee56985d] maria/ma_recovery.c:350(maria_apply_log)[0x7f37ee56d45c] maria/ma_recovery.c:242(maria_recovery_from_log)[0x7f37ee56df1e] maria/ha_maria.cc:3594(ha_maria_init)[0x7f37ee546c2d] sql/handler.cc:512(ha_initialize_handlerton(st_plugin_int*))[0x7f37ee1e2518] sql/sql_plugin.cc:1403(plugin_initialize)[0x7f37ee089e65] sql/sql_plugin.cc:1675(plugin_init(int*, char**, int))[0x7f37ee08add2] sql/mysqld.cc:5064(init_server_components)[0x7f37edfc37e8] sql/mysqld.cc:5659(mysqld_main(int, char**))[0x7f37edfc61ca] /lib64/libc.so.6(__libc_start_main+0xf0)[0x7f37ec3a79f0] /usr/mariadb/mariadb/bin//mysqld(+0x39dbad)[0x7f37edfbabad]

1 2

[Maria-discuss] restore problem with xtrabackup
by l vic 21 Dec '16

21 Dec '16

i have installed mariadb 1.10.13 cluster with user "root", password "pass" I have created test database "galeratest": CREATE DATABASE galeratest;USE galeratest;CREATE TABLE test_table ( id INT PRIMARY KEY AUTO_INCREMENT, msg TEXT ) ENGINE=InnoDB;INSERT INTO test_table (msg) VALUES ("Hello cluster."); backup: xtrabackup --user=root --password=pass --backup --target-dir=/media 161219 21:06:00 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/var/run/mysqld/mysqld.sock' as 'root' (using password: YES). 161219 21:06:00 version_check Connected to MySQL server 161219 21:06:00 version_check Executing a version check against the server... ... ok .. At this point i drop my database : DROP DATABASE galeratest; and trying to restore: xtrabackup --user=root --password=pass --prepare --target-dir=/media cd /media/ rsync -rvt --exclude 'xtrabackup_checkpoints' --exclude 'xtrabackup_logfile' \ ./ /var/lib/mysql chown -R mysql:mysql /var/lib/mysql/ MariaDB [galeratest]> show tables; +----------------------+ | Tables_in_galeratest | +----------------------+ | test_table | +----------------------+ 1 row in set (0.00 sec) MariaDB [galeratest]> select * from test_table; *ERROR 1932 (42S02): Table 'galeratest.test_table' doesn't exist in engine* *Any idea what the problem is? * Thanks,

2 2

[Maria-discuss] MariaDB 10.1.20 now available
by Daniel Bartholomew 15 Dec '16

15 Dec '16

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.1.20. This is a stable (GA) release. See the Release Notes and Changelog for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.1.20 - Release Notes: https://mariadb.com/kb/en/mdb-10120-rn/ - Changelog: https://mariadb.com/kb/en/mdb-10120-cl/ - Downloads: https://downloads.mariadb.org/mariadb/10.1.20 About MariaDB 10.1: - https://mariadb.com/kb/en/what-is-mariadb-101/ 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/donate/ We hope you enjoy MariaDB! -- MariaDB Website - http://mariadb.org Twitter - http://twitter.com/mariadbfdn - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledge Base - http://mariadb.com/kb

1 0

Re: [Maria-discuss] Semaphore hangs
by Jon Foster 15 Dec '16

15 Dec '16

On 12/08/2016 04:16 PM, Jon Foster wrote: > On 12/08/2016 03:13 PM, Daniel Black wrote: >> On 09/12/16 09:43, Jon Foster wrote: >>> On 12/07/2016 06:04 PM, Daniel Black wrote: >>>> On 08/12/16 08:51, Jon Foster wrote: >>>>> We are having trouble with MariaDB hanging due to a "semaphore wait". We >>>>> then have to shut MariaDB down as it typically won't recover, unless it >>>>> restarts itself, which happens if we wait long enough. But if its gone >>>>> on long enough MariaDB won't even shutdown, it hangs indefinitely >>>>> waiting for some other internal service. I don't remember the exact name >>>>> and we've been fast enough I haven't seen it in a while. >>>>> >>>>> We've had the database on two completely different servers and still see >>>>> the problem. Both servers were bought new for this project and are a >>>>> year or less old. They are running all SSD drives, Debian 7 64bit with >>>>> MariaDB 10.1 from the MariaDB APT repository. >>>>> >>>>> Since the XtraDB engine was usually mentioned in the logged messages we >>>>> switched back to the Oracle InnoDB engine. Although this seems to have >>>>> reduced the frequency it didn't fix it. >>>>> >>>>> Can anyone give some advice on fixing this. It really seams like a bug >>>>> in MariaDB. I'll try to provide any needed info. >> [...] So its happened again on Tuesday (12/13) morning, early enough the east coasters got it before I was aware of it (they are 3hrs ahead and I was just getting up). Unfortunately I wasn't able to try the "gdb" request from the previous discussion on this topic. So I've been looking for ways to cross reference all the thread and mutexes mentioned to try and pinpoint where the failure is happening. This crash produced over 430MB of log data. I sliced out the first InnoDB monitor dump (a mere 1.5MB) and stripped it down to just the threads and related messages. I'm still reviewing the logs but I found something I thought was interesting enough I'd throw it out here and see if anyone had any thoughts. There were 4,894 threads listed in the dump. But it appears that everyone was waiting for one thread. Here is what the log said about that one thread: 06:01:42 --Thread 139879467059968 has waited at trx0sys.ic line 431 for 0.00 seconds the semaphore: 06:01:42 Mutex at 0x7f3a09a92068 created file trx0sys.cc line 729, lock var 0 06:01:42 Last time reserved by thread 18446744073709551615 in file not yet reserved line 0, waiters flag 0 I trimmed out the data and server name to shorten the lines. Several interesting things to note: 1. Thread 18446744073709551615 doesn't exist in the InnoDB monitor dump. 2. All of the other thread IDs are 15 digits. This one is 20 digits. 3. Over a thousand other threads are waiting on this one because it apparently has the lock_sys->mutex mutex. All of the remaining threads are waiting on those others. 4. This thread shows a 0 second wait time when many of the other threads say they've been waiting over 250 seconds. Sure looks like the mutex is being held by a non-existent thread. Memory corruption? I'm still looking over the logs so I might find some other stuff or something else to point the finger at. But I thought I'd throw this out there and see if anyone has some insight. Or maybe I should be taking this issue to another list or report it as a bug? THX - Jon -- Sent from my Debian Linux workstation -- http://www.debian.org/intro/about Jon Foster JF Possibilities, Inc. jon(a)jfpossibilities.com 541-410-2760 Making computers work for you!

1 0

Re: [Maria-discuss] Semaphore hangs
by Daniel Black 08 Dec '16

08 Dec '16

On 09/12/16 09:43, Jon Foster wrote: > On 12/07/2016 06:04 PM, Daniel Black wrote: >> On 08/12/16 08:51, Jon Foster wrote: >>> We are having trouble with MariaDB hanging due to a "semaphore wait". We >>> then have to shut MariaDB down as it typically won't recover, unless it >>> restarts itself, which happens if we wait long enough. But if its gone >>> on long enough MariaDB won't even shutdown, it hangs indefinitely >>> waiting for some other internal service. I don't remember the exact name >>> and we've been fast enough I haven't seen it in a while. >>> >>> We've had the database on two completely different servers and still see >>> the problem. Both servers were bought new for this project and are a >>> year or less old. They are running all SSD drives, Debian 7 64bit with >>> MariaDB 10.1 from the MariaDB APT repository. >>> >>> Since the XtraDB engine was usually mentioned in the logged messages we >>> switched back to the Oracle InnoDB engine. Although this seems to have >>> reduced the frequency it didn't fix it. >>> >>> Can anyone give some advice on fixing this. It really seams like a bug >>> in MariaDB. I'll try to provide any needed info. Its either a bug or you are running without sufficient hardware. If you rule out the later I suggest opening a report at jira.mariadb.org >>> TIA - Jon >>> >> Needed info: >> * exact MariaDB version 10.1.X >> * mariadb configuration >> * what is the entire mysql log from the startup until the "semaphore >> wait" >> * how are you determining "semaphore wait" >> * is a SHOW FULL PROCESS LIST possible? if so contents? >> * when in a stalled state run on a commmand line (with gdb installed) >> gdb --batch --eval-command="thread apply all bt" program $(pidof mysqld) >> > Version: 10.10.19+maria-1~wheezy, but we started having problems with > 5.5.x which after a few updates prompted us to try 10.1. > > Configuration: > ----------------------- > [mysqld_safe] > socket = /var/run/mysqld/mysqld.sock > nice = -10 Niceing below 0 isn't that productive. -10 might be interfering with kernel IO processes that are needed to enable userspace threads to resume. No high IO/CPU/RAM should be running on the same host however if you do -1 or -2 might help achieve a higher priority than the other process. > [mysqld] > user = mysql > pid-file = /var/run/mysqld/mysqld.pid > socket = /var/run/mysqld/mysqld.sock > port = 3306 > basedir = /usr > datadir = /ssd/mysql > tmpdir = /ssd/tmp > lc_messages_dir = /usr/share/mysql > lc_messages = en_US > skip-external-locking > # NOTE: these two were recently added to troubleshoot > ignore_builtin_innodb > plugin_load=innodb=ha_innodb.so > bind-address = 127.0.0.1 > skip-name-resolve > max_connections = 8000 At the time of the crash you seem to have about 4k threads zgrep 12:35:18 /tmp/mozilla_dan0/mysql-crash-20161207.log.gz | grep ' --Thread' | cut -f 8 -d ' ' | sort -u | wc -l 4158 I suspect you don't have enough grunt in hardware to support this. I do suspect that this level is significantly above standard load. I'd restrict max_connections to something you have seen your hardware handle. > connect_timeout = 5 > wait_timeout = 60 > max_allowed_packet = 1M > thread_cache_size = 100 > sort_buffer_size = 1M > bulk_insert_buffer_size = 16M > tmp_table_size = 32M > max_heap_table_size = 10M > join_buffer_size = 1M > myisam_recover = BACKUP > key_buffer_size = 128M > open-files-limit = 65535 > table_open_cache = 15000 > myisam_sort_buffer_size = 128M > concurrent_insert = 2 > read_buffer_size = 256K > read_rnd_buffer_size = 256K > query_cache_limit = 1M > query_cache_size = 16M > slow_query_log=0 > slow_query_log_file = /var/log/mysql/mariadb-slow.log > long_query_time = 3 > log_slow_verbosity = query_plan > binlog_format = ROW > expire_logs_days = 10 > max_binlog_size = 100M > default_storage_engine = InnoDB > innodb_buffer_pool_size = 2600M > innodb_log_buffer_size = 256M > innodb_file_per_table = 1 > innodb_open_files = 400 > innodb_io_capacity = 400 > innodb_adaptive_hash_index = 0 > # These two were added just prior to this last crash. > innodb_buffer_pool_dump_at_shutdown = 1 > innodb_buffer_pool_load_at_startup = 1 > innodb_flush_log_at_trx_commit = 0 Setting ^ to 0 will mean less IO however it means that you can loose stuff on your expected crash. > innodb_support_xa = 0 > sync_binlog = 0 > ----------------------- > > Logs: This most recent case is about 19M uncompressed. I have hosted it > on my website here: > http://www.jfpossibilities.com/static/pickup/mysql-crash-20161207.log.gz > If you would like it delivered some other way let me know. I have about > 300M of logs from a previous crash situation. That time was particularly > bad since for some reason it would hang again within ten minutes of > being restarted. By limiting the connections on our webservers I was > able to baby it to get it going again. > > I determined "semaphore wait" from the zillions of messages in the logs > that say it. We shut down this one pretty quick. But I usually recognize > the symptoms fairly early on: the number of DB connections escalates > from ~1k up to the max 8k. The number of running queries as shown with > "show processlist" escalates along with it going from less than 10 to > hundreds or thousands depending on how long we wait. sounds like max_connections of 100 might be more suitable. Matches thread_cache_size. > Then too the Apache > connections on the web servers escalate. And of course the "load factor" > on all the servers jumps up. Icinga usually gives us the heads up. Is apache on the same machine? If so you configured Apache so that it doesn't consume more memory and leave MySQL with no headroom. Of course its better to have it on a different host. Make sure Icinga doesn't show swapping. Check your read and write latency on the SSD. > Obviously your last two requests will have to wait until we are having a > problem again. But I have gdb installed and ready to go... I think. I > don't know much about it. SHOW ENGINE INNODB STATUS would be good too. experiment with gdb on a test machine in the same way. Even if its a different process.

1 0

[Maria-discuss] HDF5 and storage engine
by Roberto Spadim 08 Dec '16

08 Dec '16

Hi guys, does anyone know if mariadb/mysql have a storage engine with read access to HDF5 file format? https://support.hdfgroup.org/HDF5/ thanks -- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

2 1