Run as root requirement for NUMA support features not in documentation

[markus_albe]

Documentation about NUMA support features here www.percona.com/doc/percona-server/5.5/performance/innodb_numa_support.html does not mention the fact that one needs to start mysqld_safe as root for this to work.

Possible workaround would be to deliver a sudoers entry:

[root@percona1 ~]# cat /etc/sudoers.d/percona.numa.drop_caches
Cmnd_Alias DROP_CACHES = /sbin/sysctl -q -w vm.drop_caches=3,/sbin/sysctl vm.drop_caches=3
mysql ALL=(root) NOPASSWD: DROP_CACHES

And then call the sysctl command in mysqld_safe through "sudo" when it detects is not running as root.

[Nilnandan Joshi (nilnandan-joshi)]

Hi Marcos,

With sandbox, I'm able to start it without sudo or root. Am I missing anything?

nilnandan.joshi@bm-support01:~/sandboxes/msb_5_6_15$ cat my.sandbox.cnf | egrep "flush|numa"
flush_caches=1
numa_interleave=1
nilnandan.joshi@bm-support01:~/sandboxes/msb_5_6_15$

nilnandan.joshi@bm-support01:~/sandboxes/msb_5_6_15$ ./start
. sandbox server started
nilnandan.joshi@bm-support01:~/sandboxes/msb_5_6_15$ ps -ef | grep 5_6_15 | grep njoshi
nilnand+ 23278 1 0 04:23 pts/9 00:00:00 /bin/sh /home/njoshi/5.6.15/bin/mysqld_safe --defaults-file=/home/njoshi/sandboxes/msb_5_6_15/my.sandbox.cnf
nilnand+ 23550 23278 2 04:23 pts/9 00:00:00 /home/njoshi/5.6.15/bin/mysqld --defaults-file=/home/njoshi/sandboxes/msb_5_6_15/my.sandbox.cnf --basedir=/home/njoshi/5.6.15 --datadir=/home/njoshi/sandboxes/msb_5_6_15/data --plugin-dir=/home/njoshi/5.6.15/lib/mysql/plugin --log-error=/home/njoshi/sandboxes/msb_5_6_15/data/msandbox.err --pid-file=/home/njoshi/sandboxes/msb_5_6_15/data/mysql_sandbox56151.pid --socket=/tmp/mysql_sandbox56151.sock --port=56151
nilnandan.joshi@bm-support01:~/sandboxes/msb_5_6_15$

nilnandan.joshi@bm-support01:~/sandboxes/msb_5_6_15$ cat /proc/23550/numa_maps | grep mysqld
00400000 interleave:0-1 file=/home/njoshi/5.6.15/bin/mysqld mapped=1301 active=1 N0=929 N1=372
0113e000 interleave:0-1 file=/home/njoshi/5.6.15/bin/mysqld anon=112 dirty=112 mapped=169 active=112 N0=112 N1=57
nilnandan.joshi@bm-support01:~/sandboxes/msb_5_6_15$
nilnandan.joshi@bm-support01:~/sandboxes/msb_5_6_15$ numactl --hardware
available: 2 nodes (0-1)
node 0 cpus: 0 2 4 6 8 10 12 14 16 18 20 22
node 0 size: 96642 MB
node 0 free: 23713 MB
node 1 cpus: 1 3 5 7 9 11 13 15 17 19 21 23
node 1 size: 96764 MB
node 1 free: 29832 MB
node distances:
node 0 1
  0: 10 20
  1: 20 10
nilnandan.joshi@bm-support01:~/sandboxes/msb_5_6_15$ id
uid=10101(nilnandan.joshi) gid=10000(percona) groups=10000(percona),10002(recoverybox),10016(support),10028(xmpp_support)
nilnandan.joshi@bm-support01:~/sandboxes/msb_5_6_15$

[markus_albe (markus-albe)]

The feature failing to work was drop caches. Maybe open some large files, start again and see if it's actually dropping the caches?

[Nilnandan Joshi (nilnandan-joshi)]

Hi Marcos,

Can you please explain what exactly I need to check? drop caches is failing to work? OR we can't start mysql without root/sudo for using NUMA support features ? It would be great help if you can provide the exact steps to reproduce it. Thanks.

[Raghavendra D Prabhu (raghavendra-prabhu)]

The problem here is that, the 'run as root requirement' (well, more of euid 0) is not checked for the sysctl:

=====
  elif ! sysctl -q -w vm.drop_caches=3
  then
    log_error "sysctl failed, check the error message for details"
    exit 1
  fi
===

So, that is the bug.

It is done, for instance, for thp setting

  if [ $(id -u) -ne 0 ]; then
    log_error "mysqld_safe must be run as root for setting transparent huge pages!"
    exit 1

[markus_albe (markus-albe)]

Yes, it's [somewhat silently] failing to drop caches as Raghu explained, which is one of the things that must be done for the NUMA
balancing to work properly.

No additional steps to reproduce; Look at logs.

[Nilnandan Joshi (nilnandan-joshi)]

nilnandan.joshi@bm-support01:~$ cat bug.sh
if ! sysctl -q -w vm.drop_caches=3
  then
    log_error "sysctl failed, check the error message for details"
    exit 1
  fi
nilnandan.joshi@bm-support01:~$
nilnandan.joshi@bm-support01:~$ ./bug.sh
sysctl: permission denied on key 'vm.drop_caches'
nilnandan.joshi@bm-support01:~$
nilnandan.joshi@bm-support01:~$ sudo ./bug.sh
nilnandan.joshi@bm-support01:~$

[Laurynas Biveinis (laurynas-biveinis)]

This is both a doc bug and code bug. Leaving this bug for the docs, and opened bug 1432509 for the code.

[Hrvoje Matijakovic (hrvojem)]

Fixed here: https://github.com/percona/percona-server/pull/42 and https://github.com/percona/percona-server/pull/41

[Wagner Bianchi (wagner-bianchi)]

It's happening yet...

[root@mpshard16-chi backup]# /bin/systemctl start mysqld.service
^C
161208 14:48:50 mysqld_safe sysctl failed, check the error message for details

[root@xxx backup]# mysqld --version
mysqld Ver 5.6.29-76.2 for Linux on x86_64 (Percona Server (GPL), Release 76.2, Revision ddf26fe)

[Laurynas Biveinis (laurynas-biveinis)]

Wagner, please follow bug 1432509 for the code bug.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1611