September 2020 - discuss - lists.mariadb.org

[Maria-discuss] InnoDB: background jobs & fsyncs
by Julien Muchembled 21 Sep '20

21 Sep '20

Hello, We are evaluating new hardware by reproducing real-life workload on real-life data which works fine on an existing server. We copied over the software used to ensure an apple-to-apple comparison, which is based on MariaDB 10.2.23 and uses primarily InnoDB tables, with a few Mroonga tables which do not seem involved in the problem. On the new hardware, we are seeing catastrophically bad performance, especially chain-deadlocks happening and being resolved but producing virtually no useful work at our usual parallelism level of 64 active connections. If we reduce parallelism to 2 active connections, queries start to succeed in any meaningful proportion. The database was freshly restored from a mysqldump (as we cannot interrupt the original database), could this have any effect on deadlocks ? The main tables involved in the deadlocking queries (job queues) are initially (in the test, and periodically in reality) empty, so it would seem surprising. What should I check to debug further ? Note that these deadlocks happen in production but they're negligible. See attachment for mariadb configuration file. Some more details: When we restore from mysqldump, we use the following extra options to speed things up: innodb_flush_log_at_trx_commit = 0 innodb_flush_method = nosync innodb_doublewrite = 0 sync_frm = 0 as during that period nothing of value can be lost (worst case we restart the restore from scratch). BTW, despite these settings, we are still noticing a lot of fsyncs. Is this expected ? Are we missing some other option ? Also, after the import and without any connection, MariaDB was still producing a non-trivial amount of activity on the machine: 5% CPU, read <1MB/s, write 10MB/s, 60 fsync/s. I could not identify what is causing these, where should I look ? How can I tell when it will stabilise back to idle ? When I interrupt the benchmark workload, I see a similar resource usage. I saw https://jira.mariadb.org/browse/MDEV-18698 And InnoDB seems to throttle its background activity: is there a way to tell InnoDB to perform its background tasks at maximum speed ? (the goal is that after the import, we can make a clean tarball that we extract when we want to launch the test case again) Regards, Julien

3 3

[Maria-discuss] How to replicate TO a HA pair of MariaDB servers?
by martin doc 16 Sep '20

16 Sep '20

Hi, I've got a bit of a different question regarding MariaDB replication. But first, let me say that I'm familiar with both master-master and master-slave replication. What I'm after advice on is how to replicate tables from a number of single host instances of MariaDB to a single HA pair of MariaDB servers (HA-1/HA-2) that have a VIP that moves between them. One way of looking at this might be to say that the single host instances of MariaDB are satellite servers that I need to pull table data back into a primary pair. Kind of like a backup. I've tried to think of how I could do this using what I know. Can I setup master-slave relationships and specify the source address on the slave (in this case the HA pair)? The idea is that the replication would fail from HA-1 when the VIP is on HA-2. In this case the single-host masters would only allow replication connections from the VIP. Or the connection out from the slave to the master would fail to bind to the VIP (and thus fail to connect to the master) if the VIP was not on a local interface. Of course I want both servers (HA-1/HA-2) in the redundant pool to be able to serve queries, so I'm not too keen on setting the bind address in [mysqld] to be the VIP and using net.ipv4.ip_nonlocal_bind - but I could be open to that. Is it possible to specify a bind address for replication that is separate to that used in [msyqld]? Are there other ways to solve this problems? Cheers.

3 7

[Maria-discuss] MariaDB Server Fest 14-20 September
by Ian Gilfillan 13 Sep '20

13 Sep '20

1 0

Re: [Maria-discuss] mariadb-config sym-link in MariaDB 10.5.5
by Chris Ross (cross2) 13 Sep '20

13 Sep '20

Following up, with some more specifics... After configuring with below options, we have the first `maria*config` find(1) results, and then running “make” yields the expected binary : % find . -name maria\*config ./srcdir/mariadb-10.5.5/libmariadb/mariadb_config ./srcdir/mariadb-10.5.5/debian/mariadb-server-10.5.config <make> % find . -name maria\*config ./srcdir/mariadb-10.5.5/libmariadb/mariadb_config ./srcdir/mariadb-10.5.5/libmariadb/mariadb_config/mariadb_config ./srcdir/mariadb-10.5.5/debian/mariadb-server-10.5.config Then, a “make DESTDIR=destdir install” yields the following: ./destdir/usr/bin/mariadb_config ./srcdir/mariadb-10.5.5/libmariadb/mariadb_config ./srcdir/mariadb-10.5.5/libmariadb/mariadb_config/mariadb_config ./srcdir/mariadb-10.5.5/debian/mariadb-server-10.5.config And, after no other changes, rerunning the exact same make install command produces the following. Clearly, something is wrong with the make install process. So, should the first install create the sym-link, but isn’t? Or, is it configured in some way that the sym-link shouldn’t be created, and it is, but only when running make install twice? ./destdir/usr/bin/mariadb_config ./destdir/usr/bin/mariadb-config ./srcdir/mariadb-10.5.5/libmariadb/mariadb_config ./srcdir/mariadb-10.5.5/libmariadb/mariadb_config/mariadb_config ./srcdir/mariadb-10.5.5/debian/mariadb-server-10.5.config % ls -l destdir/usr/bin/maria*config -rwxr-xr-x 1 cross2 5011 25936 Sep 11 14:05 destdir/usr/bin/mariadb_config lrwxrwxrwx 1 cross2 5011 14 Sep 11 14:56 destdir/usr/bin/mariadb-config -> mariadb_config Please let me know. This looks like a bug in the build/install process, but I want to know how to work around it on my side while the fix is developed. Thank you! - Chris From: Maria-discuss <maria-discuss-bounces+cross2=cisco.com(a)lists.launchpad.net> on behalf of "Chris Ross (cross2)" <cross2(a)cisco.com> Date: Friday, September 11, 2020 at 08:42 To: "maria-discuss(a)lists.launchpad.net" <maria-discuss(a)lists.launchpad.net> Subject: [Maria-discuss] mariadb-config sym-link in MariaDB 10.5.5 Hello there friends. We have recently moved our product to MariaDB 10.5.5, and someone on my team noticed an oddity that occurs in some build processes. The product has a complicated make-based build system, and many phases including “make src” and “make build” which pre-req’s the “src” target, and includes packaging. The dev work to integrate mariadb used “make build”, which works. Someone found unexpectedly this morning, that if we perform a “make src ; make build” we get a different outcome. There is a sym-link created by the install target lrwxrwxrwx 1 cross2 5011 14 Sep 11 08:30 ./destdir/usr/bin/mariadb-config -> mariadb_config that is _not_ created in the first build. The first build, which given the dependencies of other parts of our product needing mariadb libraries to build, performs the destdir install (via “make install” in mariadb). And, that includes mariadb_config, but _not_ mariadb-config. Can someone help me figure out where that sym-link is created, and why it seems to only be created when maria’s “make install” is run twice? - Chris Cmake config: CONF_OPTIONS:=-DBUILD_CONFIG=mysql_release \ -DAWS_SDK_EXTERNAL_PROJECT=OFF \ -DCMAKE_INSTALL_PREFIX:PATH=${EXTRAPREFIX}/usr \ -DINSTALL_LAYOUT=RPM \ -DINSTALL_PLUGINDIR=lib64/plugin \ -DINSTALL_SYSCONFDIR="" \ -DINSTALL_MYSQLTESTDIR="" \ -DWITH_EMBEDDED_SERVER=NO \ -DWITH_JEMALLOC=OFF \ -DWITH_PCRE=auto \ -DWITH_SSL=system \ -DWITH_ZLIB=system \ -DWITH_WSREP=OFF -DPLUGIN_WSREP_INFO=NO \ -DPLUGIN_AUTH_TEST_PLUGIN=NO \ -DPLUGIN_FUNC_TEST=NO \ -DPLUGIN_TYPE_TEST=NO \ -DPLUGIN_TEST_SQL_DISCOVERY=NO \ -DPLUGIN_TEST_VERSIONING=NO \ -DPLUGIN_BLACKHOLE=NO \ -DPLUGIN_CONNECT=NO \ -DPLUGIN_EXAMPLE=NO -DPLUGIN_EXAMPLE_KEY_MANAGEMENT=NO \ -DPLUGIN_FTEXAMPLE=NO \ -DPLUGIN_FEDERATED=NO -DPLUGIN_FEDERATEDX=NO \ -DPLUGIN_MROONGA=NO -DMRN_GROONGA_EMBED=OFF \ -DPLUGIN_S3=NO \ -DPLUGIN_SPIDER=NO \ -DPLUGIN_SPHINX=NO \ -DPLUGIN_TOKUDB=NO \ -DPLUGIN_ROCKSDB=NO CONF_OPTIONS += -DSKIP_TESTS=ON -DWITH_UNIT_TESTS=NO -- Chris Ross Software Engineering Manager – Eventing/Datastore Teams cross2(a)cisco.com

2 3

[Maria-discuss] mariadb-config sym-link in MariaDB 10.5.5
by Chris Ross (cross2) 11 Sep '20

11 Sep '20

Hello there friends. We have recently moved our product to MariaDB 10.5.5, and someone on my team noticed an oddity that occurs in some build processes. The product has a complicated make-based build system, and many phases including “make src” and “make build” which pre-req’s the “src” target, and includes packaging. The dev work to integrate mariadb used “make build”, which works. Someone found unexpectedly this morning, that if we perform a “make src ; make build” we get a different outcome. There is a sym-link created by the install target lrwxrwxrwx 1 cross2 5011 14 Sep 11 08:30 ./destdir/usr/bin/mariadb-config -> mariadb_config that is _not_ created in the first build. The first build, which given the dependencies of other parts of our product needing mariadb libraries to build, performs the destdir install (via “make install” in mariadb). And, that includes mariadb_config, but _not_ mariadb-config. Can someone help me figure out where that sym-link is created, and why it seems to only be created when maria’s “make install” is run twice? - Chris Cmake config: CONF_OPTIONS:=-DBUILD_CONFIG=mysql_release \ -DAWS_SDK_EXTERNAL_PROJECT=OFF \ -DCMAKE_INSTALL_PREFIX:PATH=${EXTRAPREFIX}/usr \ -DINSTALL_LAYOUT=RPM \ -DINSTALL_PLUGINDIR=lib64/plugin \ -DINSTALL_SYSCONFDIR="" \ -DINSTALL_MYSQLTESTDIR="" \ -DWITH_EMBEDDED_SERVER=NO \ -DWITH_JEMALLOC=OFF \ -DWITH_PCRE=auto \ -DWITH_SSL=system \ -DWITH_ZLIB=system \ -DWITH_WSREP=OFF -DPLUGIN_WSREP_INFO=NO \ -DPLUGIN_AUTH_TEST_PLUGIN=NO \ -DPLUGIN_FUNC_TEST=NO \ -DPLUGIN_TYPE_TEST=NO \ -DPLUGIN_TEST_SQL_DISCOVERY=NO \ -DPLUGIN_TEST_VERSIONING=NO \ -DPLUGIN_BLACKHOLE=NO \ -DPLUGIN_CONNECT=NO \ -DPLUGIN_EXAMPLE=NO -DPLUGIN_EXAMPLE_KEY_MANAGEMENT=NO \ -DPLUGIN_FTEXAMPLE=NO \ -DPLUGIN_FEDERATED=NO -DPLUGIN_FEDERATEDX=NO \ -DPLUGIN_MROONGA=NO -DMRN_GROONGA_EMBED=OFF \ -DPLUGIN_S3=NO \ -DPLUGIN_SPIDER=NO \ -DPLUGIN_SPHINX=NO \ -DPLUGIN_TOKUDB=NO \ -DPLUGIN_ROCKSDB=NO CONF_OPTIONS += -DSKIP_TESTS=ON -DWITH_UNIT_TESTS=NO -- Chris Ross Software Engineering Manager – Eventing/Datastore Teams cross2(a)cisco.com

1 0

[Maria-discuss] Set password for all users, regardless of host value
by Chris Ross (cross2) 08 Sep '20

08 Sep '20

Hello there. We have scripts to restore credentials to MySQL databases from external store. The mechanism that was in use, however, stores usernames and passwords, without consideration of the scope (host) of that auth record. In older systems, UPDATE mysql.user SET password = PASSWORD(‘rawpassword’) WHERE user = ‘username’ worked, updating it for all values of that user that might exist in the table. But, I’m not sure how to do this for MariaDB 10.5. Is there way to form an “ALTER USER” statement such that it will set the password for any and all userspecs that exist with the given username? We don’t have that many, and I could iterate the known configurations with “ALTER USER IF EXISTS”, but I worry that might miss things added in the future. Thank you. - Chris -- Chris Ross Software Engineering Manager – Eventing/Datastore Teams cross2(a)cisco.com

4 8