August 2018 - discuss - lists.mariadb.org

[Maria-discuss] why stdout? -> [Note] /usr/libexec/mysqld (mysqld 10.0.20-MariaDB) starting as process 115096
by Reindl Harald 05 Jul '20

05 Jul '20

Aug 6 12:24:24 testserver mysqld: 150806 12:24:24 [Note] /usr/libexec/mysqld (mysqld 10.0.20-MariaDB) starting as process 115096 ... why is this now stdout instead mysql logs? on systemd based distributions that spits now to the global syslog and finally on machines restarting 2 instances every hour for backup reasons it annoys until 10.0.19 or 10.0.20 that was not this way

5 13

[Maria-discuss] MariaDB-10.2.11 Long Time To Start
by Michael Caplan 15 Mar '19

15 Mar '19

Hi, I'm setting up a replication slave based off of MySQL 5.6. There are a large number of tables (400K). The MySQL 5.6 master takes around 10 minutes to start up, where the new MariaDB 10.2 slave takes close to 30 minutes. What is making the startup take soo long? In the below mysql log, you can see in the startup sequence between "Highest supported file format is Barracuda." and "InnoDB: 128 out of 128 rollback segments are active." the startup process takes the majority of its time: 2018-01-07 9:38:15 139980432365760 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2018-01-07 9:38:15 139980432365760 [Note] InnoDB: Uses event mutexes 2018-01-07 9:38:15 139980432365760 [Note] InnoDB: Compressed tables use zlib 1.2.8 2018-01-07 9:38:15 139980432365760 [Note] InnoDB: Using Linux native AIO 2018-01-07 9:38:15 139980432365760 [Note] InnoDB: Number of pools: 1 2018-01-07 9:38:15 139980432365760 [Note] InnoDB: Using SSE2 crc32 instructions 2018-01-07 9:38:15 139980432365760 [Note] InnoDB: Initializing buffer pool, total size = 54G, instances = 8, chunk size = 128M 2018-01-07 9:38:18 139980432365760 [Note] InnoDB: Completed initialization of buffer pool 2018-01-07 9:38:18 139918113195776 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2018-01-07 9:38:18 139980432365760 [Note] InnoDB: Highest supported file format is Barracuda. 2018-01-07 10:11:48 139980432365760 [Note] InnoDB: 128 out of 128 rollback segments are active. 2018-01-07 10:11:48 139980432365760 [Note] InnoDB: Creating shared tablespace for temporary tables 2018-01-07 10:11:48 139980432365760 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2018-01-07 10:11:48 139980432365760 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2018-01-07 10:11:48 139980432365760 [Note] InnoDB: Waiting for purge to start 2018-01-07 10:11:48 139980432365760 [Note] InnoDB: 5.7.20 started; log sequence number 1394303471760 2018-01-07 10:11:48 139919461500672 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool 2018-01-07 10:11:48 139980432365760 [Note] Server socket created on IP: '::'. 2018-01-07 10:11:49 139980432365760 [Note] Reading of all Master_info entries succeded 2018-01-07 10:11:49 139980432365760 [Note] Added new Master_info '' to hash table 2018-01-07 10:11:49 139980432365760 [Note] /usr/sbin/mysqld: ready for connections. Version: '10.2.11-MariaDB-10.2.11+maria~xenial-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 mariadb.org binary distribution Any ideas why? I've included my my.conf file below. Thanks, Mike # Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208 # Configuration name usaddxprdsqlpm3 generated formcaplan(a)labnet.net at 2017-12-22 13:49:58 [mysql] # CLIENT # port = 3306 socket = /var/lib/mysql/mysql.sock [mysqladmin] # CLIENT # port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] # GENERAL # user = mysql default-storage-engine = InnoDB socket = /var/lib/mysql/mysql.sock pid-file = /var/lib/mysql/mysql.pid character-set-server = utf8 collation_server = utf8_general_ci performance_schema = 0 # MyISAM # key-buffer-size = 32M myisam-recover = FORCE,BACKUP # SAFETY # max-allowed-packet = 20M max-connect-errors = 1000000 skip-name-resolve sysdate-is-now = 1 innodb = FORCE # DATA STORAGE # datadir = /var/lib/mysql/ # BINARY LOGGING # log-bin = /var/lib/mysql/mysql-bin binlog_format = MIXED server-id = 3 expire-logs-days = 7 sync-binlog = 1 # REPLICATION # read-only = 1 skip-slave-start = 1 log-slave-updates = 1 relay-log = /var/lib/mysql/relay-bin slave-net-timeout = 60 sync-master-info = 1 sync-relay-log = 1 sync-relay-log-info = 1 # CACHES AND LIMITS # tmp-table-size = 32M max-heap-table-size = 32M query-cache-type = 0 query-cache-size = 0 max-connections = 500 thread-cache-size = 50 open-files-limit = 65535 table-definition-cache = 4096 table-open-cache = 10240 # INNODB # innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 512M innodb-flush-log-at-trx-commit = 1 innodb-file-per-table = 1 innodb-buffer-pool-size = 54G # LOGGING # log-error = /var/lib/mysql/mysql-error.log log-queries-not-using-indexes = 0 slow-query-log = 0 slow-query-log-file = /var/lib/mysql/mysql-slow.log [mariadb] plugin-load-add=file_key_management file_key_management_filename=/var/lib/mysql/keys.enc file_key_management_filekey=FILE:/var/lib/mysql/mysql.key file_key_management_encryption_algorithm=AES_CTR encrypt_binlog=0

3 19

[Maria-discuss] Chewing Through Swap - Swappiness = 0
by Michael Caplan 09 Mar '19

09 Mar '19

Hi, I'm trying to figure out why my recently put into production MariaDB is so swap hungry. I'm running 10.2.14, with roughly 300GB data (1000K +/- tables). 95% tables are innodb. I have 64GB RAM, with INNODB buffer pool size set to 50GB (full my.cnf below). The OS is Ubuntu 16.04.4. This is a dedicated MariaDB server. I have swappiness set to 0. I started with available swap set to just shy of 1GB. When swap got to 85% used, I bumped swap to 3GB. A day later, swap again was 85% used, and I bumped it to 5GB. A day later, swap again was 85% used, so I bumped it to 15GB. 2 days later 50% of the available swap was used. I restarted the DB moments ago, freeing up all 15GB but 128MB of swap: total used free shared buff/cache available Mem: 62G 20G 4.7G 266M 37G 41G Swap: 14G 128M 14G My understanding is that the OS should never allocate swap when swappiness is 0, so that has me confused. I've had similar issues with MySQL 5.6 (what I upgraded from) dipping into swap, but that instance had swappiness set to 1, and never consumed more than 5GB of swap. Questions: 1) How much of this is an OS issue vs MariaDB issue? 2) What MariaDB config vars should I be looking at to fix this issue? 3) What linux config vars should I be looking at to fix this issue? Thanks, Mike [mysqld] # GENERAL # user = mysql default-storage-engine = InnoDB character-set-server = utf8 collation_server = utf8_general_ci performance_schema = 0 max_allowed_packet = 16777216 sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" # MyISAM # key-buffer-size = 32M myisam-recover = FORCE,BACKUP # SAFETY # max-allowed-packet = 16777216 max-connect-errors = 1000000 skip-name-resolve sysdate-is-now = 1 innodb = FORCE local_infile = 0 secure_auth = 1 safe_user_create = 1 skip_symbolic_links = 1 wait_timeout = 28800 # DATA STORAGE # datadir = /var/lib/mysql/ # BINARY LOGGING # log-bin = /var/lib/mysql/mysql-bin binlog_format = MIXED server-id = 3 expire-logs-days = 7 sync-binlog = 1 # REPLICATION # read-only = 1 skip-slave-start = 1 log-slave-updates = 1 relay-log = /var/lib/mysql/relay-bin slave-net-timeout = 60 sync-master-info = 1 sync-relay-log = 1 sync-relay-log-info = 1 # CACHES AND LIMITS # tmp-table-size = 32M max-heap-table-size = 32M query-cache-type = 0 query-cache-size = 0 max-connections = 500 thread-cache-size = 50 open-files-limit = 65535 table-definition-cache = 4096 table-open-cache = 10240 # INNODB # innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 512M innodb-flush-log-at-trx-commit = 2 innodb-file-per-table = 1 innodb-buffer-pool-size = 50G

3 8

[Maria-discuss] How does InnoDB delete rows?
by Arlindo Neto 13 Sep '18

13 Sep '18

Hi everyone. I have some questions regarding InnoDB deletion process. As far as I know, when we issue a DELETE command to a row stored in a InnoDB table, the row contents are stored in the UNDO Log, and the contents of the row are only deleted when the last transaction after the DELETE transaction has finished. But what actually happens to the row that was deleted? Is any disk space ever released to the OS, or are the contents simply erased? Defragmenting a table is only done through OPTIMIZE TABLE, or are there any automatic threads responsible for rearranging the rows? And if the are no such threads, how do we maintain tables which suffer from many deletions? I hope I made myself clear. Thanks in advance, Arlindo Neto.

5 7

[Maria-discuss] Aria Crash
by Roberto Spadim 31 Aug '18

31 Aug '18

Hi guys I was using mariadb and database crashed recovering give me this error and crash any idea what could be done? mariadb 10.1.5 recovered pages: 0% 3631826282% 0%180830 12:01:21 [ERROR] mysqld got signal 11 ; -- Roberto Spadim

1 2

[Maria-discuss] Publish / Subscribe
by Roberto Spadim 31 Aug '18

31 Aug '18

Hi guys, we have nosql connections with memcache/handlersocket Could be possible integrate a publish subscribe system? I'm thinking about something like redis infrastructure, there's some jira related MDEV https://jira.mariadb.org/browse/MDEV-5263?jql=text%20~%20%22publish%22 any interest or ideas in this area? -- Roberto Spadim

1 0

[Maria-discuss] mariadb CONNECT XML table type question
by Björn Hayer 29 Aug '18

29 Aug '18

Hi, I am using CONNECT for some XML files, but lately came to a problem I was unable to solve so far. The XML has the following structure: <PhysicalLocation> <Name>Address</Name> <Area type="countrycode"> <Value>DE</Value> </Area> <Area type="postalcode"> <Value>70173</Value> </Area> <Area type="municipality"> <Value>Stuttgart</Value> </Area> </PhysicalLocation> Is there a way to get a column for the value in e.g. area type=„countrycode"? Any help is very much appreciated! Thanks! Björn

1 0

[Maria-discuss] Looking for mariadb-plugin-aws-key-management package on Ubuntu 18.04
by James Colannino 27 Aug '18

27 Aug '18

Hi everyone, I'm new to the list. Please forgive me if this is a dumb question, but I've been following this document: https://mariadb.com/kb/en/library/aws-key-management-encryption-plugin-setu… I'm using Ubuntu 18.04, and after adding the MariaDB repos and installing MariaDB, I get to the point where I need to install the mariadb-plugin-aws-key-management package and am told that this package isn't found. I see that other plugin packages exist, but not this one. I've done a ton of Googling, and the only clue I've been able to find is that an old cached package list of a mirror used to have this package, while the current package list for the same mirror doesn't. Was this package removed at some point? If so, I can't find any documentation online that says this occurred. I'm really confused and frustrated and hoping to get this plugin installed. Thanks in advance! :)

1 1

[Maria-discuss] Is disabling doublewrite safe on ZFS?
by Gionatan Danti 25 Aug '18

25 Aug '18

Hi all, as by subject: is disabling doublewrite safe on ZFS (and/or other CoW filesystems as BTRFS)? Background information: ZFS is a CoW/transactional filesystem, meaning that writes are atomic: they fully commit or are rolled backup to latest "stable" version. This lend many peoples to claim not only that disabling doublewrite is safe when InnoDB runs on top of a ZFS storage, but even that it is the *right* thing to do for increase InnoDB write performance. The reason is that when ZFS recordsize is set the same as InnoDB page/record size, no partial page write can happen. Some evidence: http://assets.en.oreilly.com/1/event/21/Optimizing%20MySQL%20Performance%20… However, I am not fully committed (pun intended!) to this idea. While I surely appreciate ZFS write atomicity, and how it *does* protect from system-wide crash (ie: powerloss), I fear that an InnoDB/MariaDB crash *can* lead to partial page writes. If, for example, the mysqld process crashes (or it is killed) when copying an internal buffer during a write() call, I can imagine the filesystem will receive wrong/partial data, which it will happily write to the main storage pool (as it know nothing of internal data consistency from InnoDB point of view). I understand that this failure scenario should be *really* rare, as the critical operation (buffer copy from mysqld to system pagecache/ARC via write()) is extremely fast compared to the real data flush to stable storage (meaning that the "vulnerable time window" is very small). However, it remain different from 100% safety. Moreover, it really backfired in the past: https://www.percona.com/blog/2015/06/17/update-on-the-innodb-double-write-b… From my understanding, disabling doublebuffer is really 100% safe only when enabling atomic writes on *a supported hardware* (https://mariadb.com/kb/en/library/atomic-write-support/). Am I missing something? Am I over-thinking it, maybe? Thanks. -- Danti Gionatan Supporto Tecnico Assyoma S.r.l. - www.assyoma.it email: g.danti(a)assyoma.it - info(a)assyoma.it GPG public key ID: FF5F32A8

5 15

[Maria-discuss] Fwd: CVE list for connectors
by Michal Schorm 21 Aug '18

21 Aug '18

Hello, On this page: https://mariadb.com/kb/en/library/security/ I can find CVEs that affect MariaDB server and releases they were fixed in. Other useful page is this one: https://mariadb.com/kb/en/library/security-vulnerabilities-in-oracle- mysql-that-did-not-exist-in-mariadb/ -- I'd like to ask you to also list CVEs applicable for connectors and versions of connectors (CONC, CONJ, ODBC) they were fixed in, since there is not direct relationship between: 1) server nad CONC 2) ODBC and CONC Server is (or atleast should be) built on top of released version of the CONC, not the latest Git content. Atleast downstreams starts to use this scheme (separate server and client library - the CONC), so they depend only on the released versions of the CONC. -- For example CVE-2018-3081 should be fixed in CONC 3.0.5, however since nor git commit, nor CONC release notes says it explicitly, I can only guess. -- Michal Schorm Associate Software Engineer Core Services - Databases Team Red Hat

1 0