Not the answer you need?
Register and ask your own question!

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

Joe GIbbsJoe GIbbs EntrantCurrent User Role Novice
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

Comments

  • matthewbmatthewb Senior [email protected] Percona Staff Role
    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.
  • matthewbmatthewb Senior [email protected] Percona Staff Role
    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&#64;othala modules]$ cd $HOME
    [drmac&#64;othala ~]$ mkdir joetest
    [drmac&#64;othala ~]$ cd $_
    [drmac&#64;othala joetest]$ vagrant init centos/7
    ...
    [drmac&#64;othala joetest]$ vagrant up
    ...
    [drmac&#64;othala joetest]$ vagrant ssh
    [vagrant&#64;localhost ~]$
    [vagrant&#64;localhost ~]$ sudo -i
    [root&#64;localhost ~]# cd /opt/
    [root&#64;localhost opt]#
    [root&#64;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&#64;localhost opt]#
    [root&#64;localhost opt]# tar -xzf Percona-Server-5.7.23-23-Linux.x86_64.ssl101.tar.gz
    [root&#64;localhost opt]#
    [root&#64;localhost opt]# groupadd mysql
    [root&#64;localhost opt]# useradd -r -g mysql -s /bin/false mysql
    [root&#64;localhost opt]# ln -s /opt/Percona-Server-5.7.23-23-Linux.x86_64.ssl101 mysql
    [root&#64;localhost opt]# cd mysql/
    [root&#64;localhost mysql]#
    [root&#64;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&#64;localhost mysql]#
    [root&#64;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&#64;localhost: Sagty-Hkq0ei
    [root&#64;localhost mysql]#
    [root&#64;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&#64;localhost mysql]#
    [root&#64;localhost mysql]# bin/mysql_ssl_rsa_setup
    [root&#64;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&#64;localhost mysql]#
    [root&#64;localhost mysql]# mkdir /var/log/mysql
    [root&#64;localhost mysql]# mkdir /var/run/mysql
    [root&#64;localhost mysql]# chown mysql:mysql /var/log/mysql /var/run/mysql
    [root&#64;localhost mysql]#
    [root&#64;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&#64;localhost mysql]# bg
    [1]+ bin/mysqld_safe --user=mysql &
    [root&#64;localhost mysql]#
    [root&#64;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&#64;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&#64;localhost mysql]#
    [root&#64;localhost mysql]#
    [root&#64;localhost mysql]# cat /etc/redhat-release
    CentOS Linux release 7.5.1804 (Core)
    [root&#64;localhost mysql]#
    [root&#64;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&#64;localhost mysql]#
    [root&#64;localhost mysql]# ldd --version
    ldd (GNU libc) 2.17
    
    
  • matthewbmatthewb Senior [email protected] Percona Staff Role
    Just tried it on CentOS 6.10 with libc 2.12 without issue, so looks like my suspicion was wrong about libc version.
    [root&#64;localhost mysql]# cat /etc/redhat-release
    CentOS release 6.10 (Final)
    [root&#64;localhost mysql]#
    [root&#64;localhost mysql]# ldd --version
    ldd (GNU libc) 2.12
    
    [root&#64;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&#64;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'&#64;'localhost' (using password: NO)
    
    [root&#64;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 GIbbsJoe GIbbs Entrant Current User Role Novice
    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
  • matthewbmatthewb Senior [email protected] Percona Staff Role
    Hi Joe,
    Have you tried using our docker images to manage so many MySQL instances on 1 machine? Maybe you could leverage MySQL-Sandbox (https://mysqlsandbox.net/) as another option.
  • Joe GIbbsJoe GIbbs Entrant Current User Role Novice
    Docker add a layer that is not needed. The challenge is to have separate mysql instance on separate drives.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.