- discuss - lists.mariadb.org

[Maria-discuss] Performance lags with MariaDB built from source
by Nisarg Shah 05 Dec '19

05 Dec '19

System config: OS: Ubuntu 18.04 CPU: 2x Intel Xeon E5-2660 (40 logical cores) RAM: 160GB I built a debug build of MariaDB server 10.5 from the Github source and tried to load and execute the TPC-H benchmarks on it with a database size of 1GB. While loading the data and executing queries, I observed that MariaDB takes an unusually large amount of time (more than 10x compared to other databases like QuickStep or MonetDB) to load the data and execute queries (more than 10 minutes per query, compared to less than a minute for other databases). I'm running the server with following options - ./bin/mysqld --user=nisargs --datadir=/fastdisk/mariadb-server-install-2/data/ --innodb-read-io-threads=40 --innodb-write-io-threads=40 --innodb-buffer-pool-size=16G --innodb-buffer-pool-instances=40 I just wanted to get some eyes on this and check if there is something wrong with the command line options (or whether I can tweak some options to improve performance). I'm just trying to conduct some benchmarking experiments and not very familiar with databases. Thanks Nisarg

3 3

[Maria-discuss] Migrating a Galera cluster to new hardware and major Maria version
by Tom Worster 06 Nov '19

06 Nov '19

I've three servers with Debian 9, Maria 10.1 and running a Galera cluster. We're upgrading the hardware by getting three new servers. They will have Debian 10 and Maria 10.3. What are my options for accomplishing this with minimal service downtime? One option I can think of Using a mysqldump snapshot, set up Maria on one of the new servers to replicate as an asyncronous slave from one node of the existing cluster.Set up Maria on the two remaining new servers in a cluster with that async slave.When all looks good, migrate workload to the new cluster. Does that seem reasonable? Tom

2 3

[Maria-discuss] What's the agenda of https://mariadb.org/2019-developers-unconference-shanghai/
by workplaylifecycle workplaylifecycle 03 Nov '19

03 Nov '19

2 1

[Maria-discuss] SSL issue with Windows MariaDB client
by Kenneth Penza 29 Oct '19

29 Oct '19

Good morning, Whilst testing SSL of a MariaDB server version 10.4.8 running Linux from a Windows 10 machine I noted that connection using MySQL client (mysql-8.0.18-winx64) connects successfully, however connections with MariaDB client (mariadb-10.4.8-winx64) fails. In case of MariaDB I have downloaded the file ( https://downloads.mariadb.org/interstitial/mariadb-10.4.8/winx64-packages/m…) whilst for MySQL client I used ( https://dev.mysql.com/downloads/file/?id=490026) C:\temp\mariadb-10.4.8-winx64>mysql --user=penzk001 --password --host=<hostname> --port=3306 --tls-version=TLSv1.2 --ssl-ca=c:\temp\CACert.pem Enter password: ******** ERROR 2026 (HY000): Unknown SSL error (0x80090308) C:\temp\mariadb-10.4.8-winx64\bin> cd ..\mysql-8.0.18-winx64\bin C:\temp\mysql-8.0.18-winx64\bin> mysql --user=penzk001 --password --host=<hostname> --port=3306 --tls-version=TLSv1.2 --ssl-ca=c:\temp\CACert.pem Welcome to the MySQL monitor. Commands end with ; or \g. ... mysql>\s ... SSL: Cipher in use is DHE-RSA-AES128-GCM-SHA256 ... mysql> To ensure that the SSL certificate is valid I also tried "--ssl-mode=VERIFY_IDENTITY" with the mysql-8.0.18 client and it worked fine. Regards Kenneth

2 3

[Maria-discuss] Fwd: database corrupted when switching from MySQL to MariaDB on Ubuntu 19.04
by bapt x 21 Oct '19

21 Oct '19

@Andrei all the error messages I found were included in my original email, let me know how I can provide additional information if no one can reproduce the problem. I forgot to include maria-discuss(a)lists.launchpad.net, you can see my reply below. ---------- Forwarded message --------- From: bapt x <baptx.is(a)gmail.com> Date: Wed, 16 Oct 2019 at 10:35 Subject: Re: [Maria-discuss] database corrupted when switching from MySQL to MariaDB on Ubuntu 19.04 To: Gordan Bobic <gordan.bobic(a)gmail.com> Thanks for the information. It looks like I did everything properly since I was able to reproduce the problem several times with a clean install of Ubuntu 19.04 on VirtualBox. I think if someone else tries the steps I explained, he can reproduce the problem. Now it would be nice to know if there is a way to recover the data. If MariaDB was able to corrupt the data, there should be a way to reverse engineer the process and restore the data. Maybe a developer that knows well MariaDB upgrade system has a solution. On Wed, 16 Oct 2019 at 10:23, Gordan Bobic <gordan.bobic(a)gmail.com> wrote: > I don't know if it is recoverable but it sounds like you missed the step > of always needing a full, clean shutdown between upgrades with > innodb_fast_shutdown=0. Then you can delete ib_logfile*, and upgrade. > > On Wed, 16 Oct 2019, 09:19 bapt x, <baptx.is(a)gmail.com> wrote: > >> Hello, >> >> On Ubuntu 19.04, which uses packages mariadb-server 10.3.17 and >> mysql-server 5.7.27, I noticed that if I wanted to switch from MySQL to >> MariaDB, the database is corrupted and there is a complete data loss >> even if I switch back to MySQL. >> In the previous version of Ubuntu, switching from MySQL to MariaDB did >> not manage to import data automatically (unlike Debian) but at least it >> created a backup of the data in /var/lib/mysql-5.7/ folder which is not >> done anymore. >> >> Here is the error message I saw during install when trying to use the >> database corrupted by MariaDB and switching back to MySQL: >> "MySQL has been frozen to prevent damage to your system. Please see >> /etc/mysql/FROZEN for help." >> >> And in /var/log/mysql/error.log: >> "[ERROR] InnoDB: Unsupported redo log format. The redo log was created >> with MariaDB 10.3.17. Please follow the instructions athttp://dev.mysql.com/doc/refman/5.7/en/upgrading-downgrading.html" >> >> I was able to reproduce the issue with a clean installation of Ubuntu >> 19.04 in VirtualBox. >> >> Do you know where the problem comes from and if it is possible to fix >> the binary data from */var/lib/mysql/* to make it work with either MySQL >> or MariaDB? >> It looks like MariaDB tried to convert the data ("[ERROR] InnoDB: >> Unsupported redo log format") but now it fails with both MySQL and MariaDB. >> Is it possible to revert the changes done by MariaDB to make the data >> work again with MySQL? (and then do a proper backup with mysqldump) >> >> Thanks. >> >> _______________________________________________ >> Mailing list: https://launchpad.net/~maria-discuss >> Post to : maria-discuss(a)lists.launchpad.net >> Unsubscribe : https://launchpad.net/~maria-discuss >> More help : https://help.launchpad.net/ListHelp >> >

3 8

[Maria-discuss] lock_wait_timeout default and best practice values
by jkmdb＠w3.org 18 Oct '19

18 Oct '19

Hi, When migrating from mariadb 10.1 to 10.3 we noticed that the default value for lock_wait_timeout went from 1 year to 1 day. Although 1 day seems more reasonable than 1 year, it seems pretty high for a lock_wait. Does anyone know why this value is so high? Are there any rule of thumbs for a good timeout value that won't degrade the system and yet allow it to operate as intended? I know that part of the answer is "it depends on which operations your applications are doing". What I don't know is if beyond those applications, mariadb needs a specific timeout to function properly, for example, when doing a mariabackup or flushing tables. Is anyone else using a timeout value other than the default ones? My apologies for the naivete of this question. Thanks! --josé

2 1

[Maria-discuss] database corrupted when switching from MySQL to MariaDB on Ubuntu 19.04
by bapt x 16 Oct '19

16 Oct '19

Hello, On Ubuntu 19.04, which uses packages mariadb-server 10.3.17 and mysql-server 5.7.27, I noticed that if I wanted to switch from MySQL to MariaDB, the database is corrupted and there is a complete data loss even if I switch back to MySQL. In the previous version of Ubuntu, switching from MySQL to MariaDB did not manage to import data automatically (unlike Debian) but at least it created a backup of the data in /var/lib/mysql-5.7/ folder which is not done anymore. Here is the error message I saw during install when trying to use the database corrupted by MariaDB and switching back to MySQL: "MySQL has been frozen to prevent damage to your system. Please see /etc/mysql/FROZEN for help." And in /var/log/mysql/error.log: "[ERROR] InnoDB: Unsupported redo log format. The redo log was created with MariaDB 10.3.17. Please follow the instructions athttp://dev.mysql.com/doc/refman/5.7/en/upgrading-downgrading.html" I was able to reproduce the issue with a clean installation of Ubuntu 19.04 in VirtualBox. Do you know where the problem comes from and if it is possible to fix the binary data from */var/lib/mysql/* to make it work with either MySQL or MariaDB? It looks like MariaDB tried to convert the data ("[ERROR] InnoDB: Unsupported redo log format") but now it fails with both MySQL and MariaDB. Is it possible to revert the changes done by MariaDB to make the data work again with MySQL? (and then do a proper backup with mysqldump) Thanks.

4 8

[Maria-discuss] Performance of multiple queries on 10.1 vs 10.3 and 10.4
by Andre Nathan 11 Oct '19

11 Oct '19

Hi I have a trivial PHP script using mysqli::multi_query (error handling ommited): $queries = "..."; $db = new mysqli("localhost", $user, $pass, $database); $db->multi_query($queries); $db->close(); Here $queries refers to a large number (hundreds) of CREATE TABLE, ALTER TABLE and INSERT queries derived from a database migration system. When I run this script against MariaDB 10.1, it returns immediately, and a SHOW PROCESSLIST on the MariaDB console shows tables being created after the script has finished running. In other words, it's as if the queries run asynchronously. If I upgrade to 10.3 or 10.4, the behavior changes and becomes "synchronous": the script takes minutes to run and only finishes once every query has run. Is this a known behavior on later MariaDB versions? Is there a configuration setting that can restore the behavior from 10.1? I've tested this on an Ubuntu 18.04 VM with packages installed from MariaDB's APT repositories, using default configurations. Thanks in advance, Andre

1 0

[Maria-discuss] Limited Unicode Support?
by Björn Keil 11 Oct '19

11 Oct '19

Hello, I hope this is the proper mailing list to ask such questions, I apologise if it isn't. I am having some problems with unusual Unicode characters in my MariaDB database. $ mariadb --version mariadb Ver 15.1 Distrib 10.3.17-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 $ sudo ./mariadb.php [sudo] Passwort für bjoern: Query: INSERT INTO `test` SET `string` = '🙋 Huhu. wie geht es dir?' Inserted: '🙋 Huhu. wie geht es dir?' Returned: '???? Huhu. wie geht es dir?' SHOW VARIABLES LIKE 'character%': character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_filesystem binary character_set_results utf8 character_set_server latin1 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ As you can see here, MariaDB does not take the character '🙋' ( https://www.fileformat.info/info/unicode/char/1f64b/index.htm ) and instead replaces it with four question marks and I have no idea why. I've attached the PHP code for the example. I would be most grateful for any suggestion. Regards, Björn Keil

4 4

[Maria-discuss] Performance tuning sought for MariaDB
by JCA 09 Oct '19

09 Oct '19

I am running MariaDB 10.0.34 on a Slackware 14.2 system. I have a C application that interacts with MariaDB in the following way: 1. Read data from a FIFO. 2. Insert the data into a table in a MariaDB database, if absent, or modify it according to certain specific criteria otherwise. This works as expected. The problem that I have is that data are being written to the FIFO at a fast rate. In order to be able to keep up, at any given time my application reads the data available at the FIFO, and spawns a thread to process the chunk of data just read. It is in this thread that all the database interaction takes place. In order to deal with this, I have the following entries in my /etc/my.cnf file: # this is read by the standalone daemon and embedded servers [server] # this is only for the mysqld standalone daemon [mysqld] # thread_handling=pool-of-threads # log=/var/log/mysqld.log # this is only for embedded server [embedded] # This group is only read by MariaDB servers, not by MySQL. # If you use the same .cnf file for MySQL and MariaDB, # you can put MariaDB-only options here [mariadb] # log=/var/log/mysqld.log general_log_file = /var/lib/mysql/mysql.log # general_log = 1 # transaction-isolation = READ-COMMITTED # key_buffer = 1280M # 128MB for every 1GB of RAM # sort_buffer_size = 1M # 1MB for every 1GB of RAM # read_buffer_size = 1M # 1MB for every 1GB of RAM # read_rnd_buffer_size = 1M # 1MB for every 1GB of RAM # thread_concurrency = 24 # Based on the number of CPUs # so make it CPU*2 # thread-handling=pool-of-threads # innodb_flush_log_at_trx_commit != 1 # open_files_limit = 50000 thread-handling=pool-of-threads max_connections = 1000 table_open_cache = 800 query_cache_type = 0 innodb_buffer_pool_size = 512M innodb_buffer_pool_instances = 10 innodb_adaptive_hash_index_partitions = 20 innodb_lock_wait_timeout = 5000 With this, my application can keep up with the FIFO writer, but - depending on the circumstances - my database can't. As I am writing this, there are over 1300 threads connected to my database; any command that I issue at the mysql CLI takes over one minute to return. I am keeping track on how long each thread takes to complete, and that is of the order of hundreds of seconds - sometimes thousands. Each thread is itself simple, in that it just issues a couple of simple MariaDB commands. Currently my table consists of 1.6 million entries, and growing - on this basis, I expect that things will get only worse. Each entry,however, will never require more than a couple of hundred bytes of storage. The operations that can be undertaken on entries are insertion, deletion and modification, the latter being straightforward - like e.g. incrementing a counter or replacing a short string. My system has 24 GB of RAM and 12 cores. Occasionally all the cores are fully busy with MariaDB activity, but most of the time barely one or two are. I am a newbie when it comes to interacting with MariaDB - please, bear with me. I know I must use a single database and a single table. I also know - because of the nature of the data that are being written to the FIFO - that the probability for two different threads to be operating on the same entry in the table at the same time is negligible - i.e. for all practical purposes, that will not happen. What I need is advice on how to configure my instance of MariaDB to perform optimally in the scenario above. In particular, I would like for it to make better use of all the cores available - in essence, to parallelize the database operations as much as possible. Feedback from the experts will be much appreciated.

6 11