Percona Server for MySQL 5.7 testing 'drop in replacement' claim

[Joe_GIbbs]

Good morning,

Please read the following post and then answer.

How is this a drop in replacement for MySQL 5.7 community version if I can’t get a test db instance up and running?

We run multiple MySQL db instances (standard directory structure is included below) on many of our hosts using the Community Edition for 5.7. I keep hearing how great Percona Server is and want to set up some benchmark tests. I also use tar installs it /opt (specifically /opt/db) with symmlinks to the long dir names for each version so that we have a short name in the scripts (real examples is included below).

All this gives me great flexibility on what db instances I can run using different versions. One of the big keys that makes this work is a separate mysql db instance startup script in /db/mysql/port/scripts that forces all the variables (config file, log dir, data dir, etc) to the files in that instance.

I can’t seem to get the percona server to actually start up one of my test instances. All i get is the following Segmentation fault message. I have tried several version and that didn’t help. Have copied over the mysql.server file and managed to some of the directory and files name correct, expect for the my.cnf file and error.log.

i can’t use /etc/my.cnf and don’t want to get into multi master my.cnf files.

I can’t move the data dir to /var at all for multiple reasons.

After spending a solid 6 hours of work time I would like some feedback on how to setup the Percona server to use existing db instances without rebuilding everything that we do.

/opt/db/percona-5.7.20/bin/mysqld_safe: line 217: 5857 Segmentation fault LD_PRELOAD=/opt/db/Percona-Server-5.7.20-19-Linux.x86_64.ssl101/lib/mysql/libjemalloc.so.1 nohup /opt/db/percona-5.7.20/bin/mysqld --basedir=/opt/db/percona-5.7.20 --datadir=/db1/mysql/3506/data --plugin-dir=/opt/db/percona-5.7.20/lib/mysql/plugin --log-error=tdbadbvs2001.err --pid-file=/db1/mysql/3506/configs/my.pid < /dev/null >> /db1/mysql/3506/data/tdbadbvs2001.err 2>&1

The base directory path ends in the port number for that db instance. ==> /db1/mysql/3306
And has the following directories under it
blogs # binary logs
configs # config, pid and socket files
data # data goes here
logs # errorlog, slow query log, general query log, etc
rlogs # relay log files
scripts # start up script for this db instance
tlogs # transaction log files
tmp . # a tmp dir
tmpdir1 # 1st new tmp dir location
tmpdir2 # 2nd new tmp dir location
tmpdir_repl # tmp dir for replication

SW dir structure
ls -l /opt/db
mysql → mysql-5.6.21-linux-glibc2.5-x86_64
mysql-5.5 → mysql-5.5.28-linux2.6-x86_64
mysql-5.5.28-linux2.6-x86_64
mysql-5.6 → mysql-5.6.21-linux-glibc2.5-x86_64
mysql-5.6.21-linux-glibc2.5-x86_64
mysql-5.6.25-linux-glibc2.5-x86_64
mysql-5.7 → mysql-5.7.20-linux-glibc2.12-x86_64
mysql-5.7.20-linux-glibc2.12-x86_64

Thanks
Joe Gibbs

[matthewb]

Hi Joe,
Looks more like a problem with the jemalloc library than with MySQL. Have you tried disabling jemalloc? In whichever file you are using for my.cnf parameters, add a section for [mysqld_safe] and add the line ‘malloc-lib =’ (just malloc-lib equals, nothing after the equals) You really only need jemalloc if you plan on using the TokuDB engine or have > 128GB of memory. Your glibc versions are very old. What OS are you on? This might be why the included jemalloc isn’t loading.

[matthewb]

Hi Joe,
Percona-Server is indeed a drop-in replacement for Community MySQL. We have hundreds of thousands of installations world-wide. If it wasn’t easy, if it wasn’t drop-in, we wouldn’t be as popular as we are. I quickly spun up a CentOS7 VM and installed/setup PS5.7 using binary installation (like you) without any changes to the default config (ie: my install used jemalloc without issue) and it worked like a charm.

I see libc versions 2.5, 2.6, and 2.12 in your file list. 2.12 is usually associated with CentOS 6.5. Is that what you are running? If you cannot upgrade to libc2.14, I would suggest disabling jemalloc to get things working.

[drmac@othala modules]$ cd $HOME
[drmac@othala ~]$ mkdir joetest
[drmac@othala ~]$ cd $_
[drmac@othala joetest]$ vagrant init centos/7
...
[drmac@othala joetest]$ vagrant up
...
[drmac@othala joetest]$ vagrant ssh
[vagrant@localhost ~]$
[vagrant@localhost ~]$ sudo -i
[root@localhost ~]# cd /opt/
[root@localhost opt]#
[root@localhost opt]# curl -O https://www.percona.com/downloads/Percona-Server-LATEST/Percona-Server-5.7.23-23/binary/tarball/Percona-Server-5.7.23-23-Linux.x86_64.ssl101.tar.gz
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 416M 100 416M 0 0 2112k 0 0:03:21 0:03:21 --:--:-- 2906k
[root@localhost opt]#
[root@localhost opt]# tar -xzf Percona-Server-5.7.23-23-Linux.x86_64.ssl101.tar.gz
[root@localhost opt]#
[root@localhost opt]# groupadd mysql
[root@localhost opt]# useradd -r -g mysql -s /bin/false mysql
[root@localhost opt]# ln -s /opt/Percona-Server-5.7.23-23-Linux.x86_64.ssl101 mysql
[root@localhost opt]# cd mysql/
[root@localhost mysql]#
[root@localhost mysql]# ls -la
total 140
drwxrwxr-x. 10 root root 4096 Sep 12 18:14 .
drwxr-xr-x. 3 root root 130 Oct 23 21:39 ..
drwxrwxr-x. 2 root root 4096 Sep 12 18:14 bin
-rw-r--r--. 1 root root 17987 Sep 3 18:20 COPYING
-rw-r--r--. 1 root root 34520 Sep 3 18:43 COPYING.AGPLv3
-rw-r--r--. 1 root root 17987 Sep 3 18:43 COPYING.GPLv2
-rw-rw-r--. 1 root root 1703 Sep 12 18:14 COPYING-jemalloc
-rw-r--r--. 1 root root 17987 Sep 3 18:20 COPYING-test
drwxr-xr-x. 2 root root 101 Sep 12 18:14 docs
drwxrwxr-x. 3 root root 4096 Sep 12 18:14 include
drwxrwxr-x. 5 root root 240 Sep 12 18:14 lib
drwxrwxr-x. 4 root root 30 Sep 12 18:14 man
drwxrwxr-x. 10 root root 4096 Sep 12 18:14 mysql-test
-rw-r--r--. 1 root root 2211 Sep 3 18:43 PATENTS
-rw-r--r--. 1 root root 4118 Sep 3 18:43 README.md
-rw-r--r--. 1 root root 2478 Sep 3 18:20 README.MySQL
-rw-r--r--. 1 root root 353 Sep 3 18:20 README-test
drwxrwxr-x. 28 root root 4096 Sep 12 18:14 share
drwxrwxr-x. 2 root root 90 Sep 12 18:14 support-files
[root@localhost mysql]#
[root@localhost mysql]# bin/mysqld --initialize --user=mysql
2018-10-23T21:40:25.529080Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-10-23T21:40:25.529588Z 0 [ERROR] Can't find error-message file '/usr/local/Percona-Server-5.7.23-23-Linux.x86_64.ssl101/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2018-10-23T21:40:25.977462Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-10-23T21:40:26.123375Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-10-23T21:40:26.207939Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 40d4fae8-d70c-11e8-9221-525400c9c704.
2018-10-23T21:40:26.209493Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-10-23T21:40:26.392716Z 0 [Warning] CA certificate ca.pem is self signed.
2018-10-23T21:40:26.426367Z 1 [Note] A temporary password is generated for root@localhost: Sagty-Hkq0ei
[root@localhost mysql]#
[root@localhost mysql]# ls -la /var/lib/mysql/
total 110668
drwxr-x---. 5 mysql mysql 4096 Oct 23 21:40 .
drwxr-xr-x. 29 root root 4096 Oct 23 21:40 ..
-rw-r-----. 1 mysql mysql 56 Oct 23 21:40 auto.cnf
-rw-------. 1 mysql mysql 1680 Oct 23 21:40 ca-key.pem
-rw-r--r--. 1 mysql mysql 1120 Oct 23 21:40 ca.pem
-rw-r--r--. 1 mysql mysql 1120 Oct 23 21:40 client-cert.pem
-rw-------. 1 mysql mysql 1680 Oct 23 21:40 client-key.pem
-rw-r-----. 1 mysql mysql 423 Oct 23 21:40 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Oct 23 21:40 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Oct 23 21:40 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Oct 23 21:40 ib_logfile1
drwxr-x---. 2 mysql mysql 4096 Oct 23 21:40 mysql
drwxr-x---. 2 mysql mysql 8192 Oct 23 21:40 performance_schema
-rw-------. 1 mysql mysql 1676 Oct 23 21:40 private_key.pem
-rw-r--r--. 1 mysql mysql 452 Oct 23 21:40 public_key.pem
-rw-r--r--. 1 mysql mysql 1120 Oct 23 21:40 server-cert.pem
-rw-------. 1 mysql mysql 1676 Oct 23 21:40 server-key.pem
drwxr-x---. 2 mysql mysql 8192 Oct 23 21:40 sys
[root@localhost mysql]#
[root@localhost mysql]# bin/mysql_ssl_rsa_setup
[root@localhost mysql]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mysql/mysqld.log
pid-file=/var/run/mysql/mysql.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[root@localhost mysql]#
[root@localhost mysql]# mkdir /var/log/mysql
[root@localhost mysql]# mkdir /var/run/mysql
[root@localhost mysql]# chown mysql:mysql /var/log/mysql /var/run/mysql
[root@localhost mysql]#
[root@localhost mysql]# bin/mysqld_safe --user=mysql
mysqld_safe Adding '/opt/Percona-Server-5.7.23-23-Linux.x86_64.ssl101/lib/mysql/libjemalloc.so.1' to LD_PRELOAD for mysqld
Logging to '/var/log/mysql/mysqld.log'.
2018-10-23T21:42:12.428177Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
^Z
[1]+ Stopped bin/mysqld_safe --user=mysql
[root@localhost mysql]# bg
[1]+ bin/mysqld_safe --user=mysql &
[root@localhost mysql]#
[root@localhost mysql]# bin/mysql -uroot -p -S /var/lib/mysql/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.23-23

Copyright (c) 2009-2018 Percona LLC and/or its affiliates
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set password = 'Sagty-Hkq0ei11';
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)

mysql> SHUTDOWN;
Query OK, 0 rows affected (0.00 sec)

mysql> Bye
[root@localhost mysql]# 2018-10-23T21:43:24.898203Z mysqld_safe mysqld from pid file /var/run/mysql/mysql.pid ended

[1]+ Done bin/mysqld_safe --user=mysql
[root@localhost mysql]#
[root@localhost mysql]#
[root@localhost mysql]# cat /etc/redhat-release
CentOS Linux release 7.5.1804 (Core)
[root@localhost mysql]#
[root@localhost mysql]# uname -a
Linux localhost.localdomain 3.10.0-862.2.3.el7.x86_64 #1 SMP Wed May 9 18:05:47 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
[root@localhost mysql]#
[root@localhost mysql]# ldd --version
ldd (GNU libc) 2.17

[matthewb]

Just tried it on CentOS 6.10 with libc 2.12 without issue, so looks like my suspicion was wrong about libc version.

[root@localhost mysql]# cat /etc/redhat-release
CentOS release 6.10 (Final)
[root@localhost mysql]#
[root@localhost mysql]# ldd --version
ldd (GNU libc) 2.12

[root@localhost mysql]# bin/mysqld_safe --user=mysql
mysqld_safe Adding '/opt/Percona-Server-5.7.23-23-Linux.x86_64.ssl101/lib/mysql/libjemalloc.so.1' to LD_PRELOAD for mysqld
2018-10-23T22:28:04.254832Z mysqld_safe Logging to '/var/log/mysqld.log'.
2018-10-23T22:28:04.287828Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

[root@localhost mysql]# tail /var/log/mysqld.log
2018-10-23T22:28:04.628570Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2018-10-23T22:28:04.628607Z 0 [Note] IPv6 is available.
2018-10-23T22:28:04.628620Z 0 [Note] - '::' resolves to '::';
2018-10-23T22:28:04.628646Z 0 [Note] Server socket created on IP: '::'.
2018-10-23T22:28:04.629080Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2018-10-23T22:28:04.630275Z 0 [Note] InnoDB: Buffer pool(s) load completed at 181023 22:28:04
2018-10-23T22:28:04.640704Z 0 [Note] Event Scheduler: Loaded 0 events
2018-10-23T22:28:04.640846Z 0 [Note] /opt/mysql/bin/mysqld: ready for connections.
Version: '5.7.23-23' socket: '/var/lib/mysql/mysql.sock' port: 3306 Percona Server (GPL), Release 23, Revision 500fcf5
2018-10-23T22:28:55.064241Z 2 [Note] Access denied for user 'root'@'localhost' (using password: NO)

[root@localhost mysql]# ps -Af | grep mysql
root 3506 3497 0 22:28 pts/1 00:00:00 /bin/sh bin/mysqld_safe --user=mysql
mysql 3660 3506 0 22:28 pts/1 00:00:01 /opt/mysql/bin/mysqld --basedir=/opt/mysql --datadir=/var/lib/mysql --plugin-dir=/opt/mysql/lib/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock

Works without jemalloc too:

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
malloc-lib=

[Joe_GIbbs]

Thanks for the replies. Will take another crack at it. One of the requirements is that we can run multiple mysql instances on one host. Using different mount points for each db instance means that I can stop one and clone it if needed without impacting anything else on that host. So that means all of the files related to that instance runs under a specific drive (e.g. /db1) and nothing resides on /var /etc.

Leveraging the hardware when possible is one of the current strategies we follow.

WIll take another try, i suspect I will need to modify the provided startup script to force it to use a non-standard my.cnf file
Thx
Joe Gibbs

[matthewb]

Hi Joe,
Have you tried using our docker images to manage so many MySQL instances on 1 machine? Maybe you could leverage MySQL-Sandbox ([url]https://mysqlsandbox.net/[/url]) as another option.

[Joe_GIbbs]

Docker add a layer that is not needed. The challenge is to have separate mysql instance on separate drives.