config file does not work as expected

MySQL & MariaDB Percona XtraDB Cluster 5.x
#1

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:

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

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/percona-xtradb-cluster.conf.d/

/etc/mysql/percona-xtradb-cluster.conf.d/mysqld.cnf

# 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

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 |
| [B]wait_timeout[/B] | 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).

#3

Hi scott,

Here is the folder contents of /etc/mysql.

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.

#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?

#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.

#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.

#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.

#8

Hi Scott, thanks for assisting cwhsu so far! I will highlight this post to the team to see if they have any insight.

#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!

#10

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

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_wait_timeout
[LIST]
[*]On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the globalinteractive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout.
[/LIST] this 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.

#11

Ah! that explains everything. Thanks for the support!

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