MySQL: mysqld_safe options

Is there a way to specify these options in configuration file?

I've tried to add them into [mysqld_safe] section but it doesn't work:

[mysqld_safe]
numa-interleave = 1
flush-caches = 1

-

# ps -ww -lfC mysqld_safe
F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
4 S root     17180     1  0  80   0 - 26549 wait   15:06 pts/1    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/mysql.pid

Please note that I'm using Percona server 5.5.30-30.2

EDIT: 28 Aug 2013 - 08:34:

Unfortunately I can't find this line in /etc/init.d/mysql:

# grep -n "interleave" < /etc/init.d/mysql
# echo $?
1

I'm able to manually invoke these options:

mysqld_safe --datadir=/mnt/data/mysql --pid-file=/mnt/data/mysql/db3.improve.pid --numa-interleave --flush-caches

but with option file there is still default policy

# cat /proc/$(pidof mysqld)/numa_maps | cut -d ' ' -f 2 | uniq
default

There must be some way other than hard coding these options into init script or manually staring MySQL daemon...

EDIT: 29 Aug 2013 - 08:38:

I've just tested it with Percona Server 5.5.28-29.1.

I still don't see numa related entries in /etc/init.d/mysql however there are in mysqld_safe script:

# grep -n "interleave" /usr/bin/mysqld_safe 
21:numa_interleave=0
67:  --numa-interleave          Run mysqld with its memory interleaved
215:      --numa-interleave) numa_interleave=1 ;;
777:# Set mysqld's memory interleave policy.
780:if true && test $numa_interleave -eq 1
785:    log_error "numactl command not found, required for --numa-interleave"
788:  elif ! numactl --interleave=all true
794:  cmd="$cmd numactl --interleave=all"
795:elif test $numa_interleave -eq 1
797:  log_error "--numa-interleave is not supported on this platform"

I've tested other options like nice, open_files_limit and they work fine so it looks like the only option is to hard-code it into init script

I believe Percona already has that option built-in.

Inside /etc/init.d/mysql, somewhere I saw something like

# numactl --interleave=all

When I discovered this, every time I installed Oracle's MySQL, I added that command to /etc/init.d/mysql.

If you want to be sure, just run this:

grep -n "interleave" < /etc/init.d/mysql

and you will see which line that appears.

I have discussed this before

• Jul 22, 2013 : MySQL 5.5 High CPU Usage

• Mar 06, 2013 : mysqld_safe version different than mysqld?

• Dec 30, 2012 : Mysql slowly uses memory until it starts to use swap

Based on the link you posted, you should be able to activate those features starting from Percona Server 5.5.28. My posts mentioning numactl were evidently from earlier versions of Percona Server.

In light of all this, the same thing applies to flush-caches.
UPDATE 2013-08-28 09:54 EDT

If you have Percona Server 5.5.30 and there are no options, go install Percona Server 5.5.28 or 5.5.29 on a Dev Server and test it out there. Check /etc/init.d/mysql as follows:

# grep -n "numa" /etc/init.d/mysql

If you find it, then Percona must have missed putting that option on 5.5.30 (which would be very surprising). As the quick fix, take the mysqld_safe line from 5.5.28/29 and hardcode it into the mysqld_safe invocation in 5.5.30.

At the very least, take the line you said works:

mysqld_safe --datadir=/mnt/data/mysql --pid-file=/mnt/data/mysql/db3.improve.pid --numa-interleave --flush-caches

and imbed that into /etc/init.d/mysql for 5.5.30 where mysqld_safe is called.

I hope that human error (Percona missing this one (highly unlikely)) is the reason from this problem and not something much deeper.

StackExchange Database Administrators Q#48818, answer score: 2