Emergency

config file does not work as expected

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • config file does not work as expected

    It seems that percona does not set the config as I expected, but I already set the configs in mysqld.cnf under percona-xtradb-cluster.conf.d folder.

    This is the percona cluster version:
    Code:
    Server version: 5.7.20-18-57-log Percona XtraDB Cluster (GPL), Release rel18, Revision 4a4da7e, WSREP version 29.24, wsrep_29.24
    /etc/mysql/my.cnf
    Code:
    !includedir /etc/mysql/conf.d/
    !includedir /etc/mysql/percona-xtradb-cluster.conf.d/
    /etc/mysql/percona-xtradb-cluster.conf.d/mysqld.cnf
    Code:
    # Template my.cnf for PXC
    # Edit to your requirements.
    [mysqld]
    server-id=1
    datadir=/var/lib/mysql
    socket=/var/run/mysqld/mysqld.sock
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    log-bin
    log_slave_updates
    expire_logs_days=7
    wait_timeout=86400
    innodb_buffer_pool_size = 300M # (adjust value here, 50%-70% of total RAM) 
    innodb_log_file_size = 64M
    innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0
    innodb_flush_method = O_DIRECT
    slow_query_log=1
    slow_query_log_always_write_time=1
    #log_queries_not_using_indexes=1
    #log_output='FILE'
    
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    The wait_timeout is not 86400 as expected
    Code:
    mysql> show variables like "%wait_time%";
    +-------------------------------------+----------+
    | Variable_name                       | Value    |
    +-------------------------------------+----------+
    | innodb_lock_wait_timeout            | 50       |
    | innodb_print_lock_wait_timeout_info | OFF      |
    | lock_wait_timeout                   | 31536000 |
    | wait_timeout                        | 28800    |
    +-------------------------------------+----------+
    4 rows in set (0.00 sec)
    I tried to use systemctl to reload and restart mysql, but nothing changed.
    Is there anything wrong about the config?

  • #2
    Hi cwhsu;

    28800 is the default value for wait_timeout, so either your option file is not being used, or that particular setting is getting overwritten by another option file. I'd first check the ownership and permissions on your "mysqld.cnf" option file. Normally the permissions should be something like "640" (chmod 640), and owned by whatever user you run your DB as (i.e. chown mysql:mysql). If that is not the issue, then I'd start looking at the other option files that can be used by default to see if the setting is getting overwritten somewhere else (especially /etc/my.cnf).
    Scott Nemes
    http://www.linkedin.com/in/scottnemes

    Comment


    • #3
      Hi scott,

      Here is the folder contents of /etc/mysql.

      Code:
      ls 
      drwxr-xr-x  2 root root 4096 Jan 23 23:22 conf.d/
      -rw-r--r--  1 root root  380 Dec 13 20:58 my.cnf
      -rw-r--r--  1 root root  839 Jan 22  2017 my.cnf.fallback
      -rw-r--r--  1 root root  380 Dec 13 20:58 my.cnf.old
      -rw-r--r--  1 root root  380 Oct 24 01:18 percona-xtradb-cluster.cnf
      drwxr-xr-x  2 root root 4096 Feb  6 16:38 percona-xtradb-cluster.conf.d/
      
      ls percona-xtradb-cluster.conf.d/
      -rw-r--r-- 1 root root   78 Oct 24 01:18 client.cnf
      -rw-r--r-- 1 root root  876 Feb  6 16:38 mysqld.cnf
      -rw-r--r-- 1 root root  450 Feb  6 16:29 mysqld_safe.cnf
      -rw-r--r-- 1 root root 1064 Oct 24 01:18 wsrep.cnf
      The strange thing is that the owner and group is not mysql.mysql, but the service is runnable. Hence, I don't think that is an issue. IMO, mysql first tries to load config from /etc/my.cnf and based on the include directive, it would then load config from the included directories. I try to add an incorrect config in percona-xtradb-cluster.conf.d/mysqld.cnf and restart mysql which failed as expected. So it does read the config in mysqld.cnf. But configs in mysqld.cnf are still being overridden, and there are no other configs in other fiiles. I am very confused about this. Anyway, thanks for the response.

      Comment


      • #4
        ok, I find the reason why I think it's not working as expected. I actually want to set wait_timeout in the config, but wait_timeout=30000 does not work. However, when I add interactive_timeout=30000 to config, It actually works for both wait_timeout and interactive_timeout. Is there anything about this in the percona document? How do I know which is the correct config parameters?

        Comment


        • #5
          Hi cwhsu;

          Yeah that's odd. I tested it out locally and am able to set both individually. Basically the wait_timeout variable is for non-interactive connections, and interactive_timeout is for interactive connections. Would be interesting to see if there is an XtraDB-cluster specific reason for it.

          Otherwise I'd try setting wait_timeout directly in /etc/my.cnf or /etc/mysql/my.cnf under the [mysqld] tag to see if it happens to pick it up that way. Still seems likely there is an option file issue going on there somewhere.
          Scott Nemes
          http://www.linkedin.com/in/scottnemes

          Comment


          • #6
            That's really odd. It looks like a bug to me because they should be able to set individually. Where should I report this?

            Well, I try to move wait_timeout to /etc/mysql/my.cnf under [mysqld] tag, and it still does not work.

            Comment


            • #7
              Hi Cwhsu;

              I'd see if lorraine.pocklington is able to ask support for you (she's the community manager here). I'm guessing it's not a bug, and just something we're missing, so hopefully they could be of assistance.
              Scott Nemes
              http://www.linkedin.com/in/scottnemes

              Comment


              • #8
                Originally posted by scott.nemes View Post
                Hi Cwhsu;

                I'd see if lorraine.pocklington is able to ask support for you (she's the community manager here). I'm guessing it's not a bug, and just something we're missing, so hopefully they could be of assistance.
                Hi Scott, thanks for assisting cwhsu so far! I will highlight this post to the team to see if they have any insight.

                Comment


                • #9
                  Let me know if I can provide more details for you guys. Becuase I actually launch a new EC2 to test this with my install script. It's basically just what percona provides in the online document, and I use apt to install percona. Thanks for the help!

                  Comment


                  • #10
                    As per MySQL doc (PS and PXC inherit upstream semantics)

                    https://dev.mysql.com/doc/refman/5.7...r_wait_timeoutthis means wait_timeout value is defined by global wait_timeout or interactive_timout value.


                    I tried setting checking for client terminal (mysql) and given that it is an interactive terminal, session inherited interactive_timeout

                    [setting in my.cnf]
                    wait_timeout=86400
                    interactive_timeout=1000000

                    mysql> select @@global.interactive_timeout;
                    +------------------------------+
                    | @@global.interactive_timeout |
                    +------------------------------+
                    | 1000000 |
                    +------------------------------+
                    1 row in set (0.00 sec)

                    mysql> select @@session.interactive_timeout;
                    +-------------------------------+
                    | @@session.interactive_timeout |
                    +-------------------------------+
                    | 1000000 |
                    +-------------------------------+
                    1 row in set (0.00 sec)

                    mysql> select @@global.wait_timeout;
                    +-----------------------+
                    | @@global.wait_timeout |
                    +-----------------------+
                    | 86400 |
                    +-----------------------+
                    1 row in set (0.00 sec)

                    mysql> select @@session.wait_timeout;
                    +------------------------+
                    | @@session.wait_timeout |
                    +------------------------+
                    | 1000000 |
                    +------------------------+
                    1 row in set (0.00 sec)

                    Hope this help clarify the confusion.

                    Comment


                    • #11
                      Ah! that explains everything. Thanks for the support!

                      Comment

                      Working...
                      X