July 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] New testsuite option
by Michal Schorm 13 Aug '18

13 Aug '18

Hello, I take care of MariaDB in Fedora & RHEL. We run the testsuite there. Every time, in every build, with --big-test. Time to time it happens that some of the tests fail. For example during a rebuild. For such cases, I'd use a way to tell MTR what is a unstable test treshold, which means "how many tests can fail to still count the testsuite run as a success". Most likely, I'd use it with value from 1-5. Any larger group of failing tests needs to get my attention. Anyway I still could spare myself of the unstable tests. -- (Asking before filling any JIRA feature request) 1) Is there already some way to do that? 2) Do you find such feature usefull? 3) Any ideas how to call such option? --max-test-fail is already used -- Michal Schorm Associate Software Engineer Core Services - Databases Team Red Hat

2 3

[Maria-discuss] How to get rid of foreign keys to non existant temporary tables?
by Bernhard Weller 30 Jul '18

30 Jul '18

Hello, I have a server running MariaDB 10.1.29-MariaDB-6+b1 (on Debian). I recently tried upgrading a concrete5 installation from 8.3.2 to 8.4.1 which failed running a SQL statement: ALTER TABLE AreaLayoutsUsingPresets ADD CONSTRAINT FK_7A9049A1385521EA FOREIGN KEY (arLayoutID) REFERENCES AreaLayouts (arLayoutID) ON UPDATE CASCADE ON DELETE CASCADE With an error: SQLSTATE[HY000]: General error: 1005 Can't create table `concrete5`.`#sql-215_264a4` (errno: 121 "Duplicate key on write or update") Now after investigating that command should probably be done with foreign key checks disabled - but I'm not sure on that one. The problem which I'm having now is, that I have two foreign key entries in INNODB_SYS_FOREIGN which are for temporary tables which don't exist, so I have no idea how to remove them: ID FOR_NAME REF_NAME N_COLS TYPE concrete5/FK_3322FA75FD71026C concrete5/#sql-2a01_48f4 concrete5/Users 1 1 concrete5/FK_7A9049A1385521EA concrete5/#sql-215_26264 concrete5/AreaLayouts 1 5 I've tried creating a table with the corresponding name (#mysql50##sql-2a01_48f4) and referenced colums (uID, int(10)) for the first one. But executing ALTER TABLE `#mysql50##sql-2a01_48f4` DROP FOREIGN KEY FK_3322FA75FD71026C Results in error #1091. I'm a bit lost now - the update will certainly not work while these foreign keys are in the system blocking the unique name. Any ideas? Bernhard Weller

1 0

[Maria-discuss] Galera partitioning question
by Ryan Delgrosso 29 Jul '18

29 Jul '18

Greetings all, I am running MariaDB 10.2.16 on CentOS in AWS and am seeing a sporadic cluster partitioning and rejoining issue with seemingly no explicable cause. * I have elements in 3 different AWS availability zones in a single galera cluster * Monitoring logs I see this message: /Jul 29 05:33:53 server01 mysqld: 2018-07-29 5:33:53 139633883080448 [Note] WSREP: (eabb848a, 'tcp://0.0.0.0:4567') connection to peer 392b9516 with addr tcp://172.31.17.60:4567 timed out, no messages seen in PT3S/ * I have tried forcing a 1500byte MTU as some others sources mentioned jumbo framing could negatively impact galera replication. * Running prolonged packet captures between nodes i cannot seem to find anything else wrong, network connectivity isn't interrupted and no service restarts occur. * These partition events happen multiple times per day. Has anyone seem this sporadic cluster disconnect and re-join issue in a similar env? I did not previously note this behavior on 10.1. Any help is much appreciated. -Ryan

1 0

[Maria-discuss] Mysql replication into galera cluster Errors
by Ton Wittenberg 28 Jul '18

28 Jul '18

Hello All, currently i have one master ( Mariadb 10.0.15) most of oure databases and tables are still myisam. i made a galera cluster with 3 nodes on mariadb 10.3.8 i made a dump the master, imported it into galera cluster, and mysql replication is running but im encoutering Alot of errors: Jul 28 00:30:32 db100 mysqld[7615]: 2018-07-28 0:30:32 3807 [Warning] WSREP: SQL statement was ineffective thd: 3807 buf: 401 Jul 28 00:30:32 db100 mysqld[7615]: schema: mytable Jul 28 00:30:32 db100 mysqld[7615]: QUERY: COMMIT Jul 28 00:30:32 db100 mysqld[7615]: => Skipping replication the idea is to discontinue the 10.0.15 machine and replace it with a 3 node galera cluster. and to setup mysql replication into galera. then start inserting in galera. and then shutdown the old master to provide a smooth migration. what could this be or how can i further debug this and find out why i receive this errors? Kind regards Ton Wittenberg

1 0

[Maria-discuss] community contribution measurement page
by Daniel Black 23 Jul '18

23 Jul '18

Folks, I've prepared a community contribution measurement page: http://measure-mariadb.ozlabs.org/repo/mariadb/server-outside-org.html This is largely the code base of https://github.com/MeasureOSS/Measure known errors: * I've yet to create the organisation aspects of this site. * some top page may be distorted by non-mariadb respositories Insights and suggested improvements welcome (especially with code) :-)

3 4

[Maria-discuss] MariaDB 10.3.8 does not install on Debian 8 and 9?
by Vimal Kumar V. 19 Jul '18

19 Jul '18

Dear Friends, I am trying to install MariaDB 10.3.8 on Debian 8&9. When I try to add following repo key, apt-key adv --recv-keys --keyserver SKS key server <http://keyserver.ubuntu.com/> 0xF1656F24C74CD1D8 I got following error message, Executing: /tmp/apt-key-gpghome.YltUOLRxyc/gpg.1.sh --recv-keys --keyserver SKS key server <http://keyserver.ubuntu.com/> 0xF1656F24C74CD1D8 gpg: keyserver receive failed: Server indicated a failure Please guide me to how to bypass the problem. -- Vimal Kumar V. Technical Assistant Mahatma Gandhi University Kottayam, Kerala, India-686 560 My Home Page: http://www.vimalkumar.info My Blogs: http://linuxhalwa.blogspot.com http://kohageek.blogspot.in http://dspacegeek.blogspot.in http://moovandan.blogspot.in --------------------------------------------------------------------------- "I forget what I was taught. I only remember what I have learnt" -Patrick White

2 1