MySQL and NUMA

I recently discussed NUMA in my Couchbase NUMA post. All the same principles apply with MySQL and NUMA.
NUMA imbalances in MySQL can cause extreme swapping. This configuration was performed on Mysql 5.6 community edition and CentOS 6.5.

To begin install numactl if it is already not installed on your MySQL server.

yum install numactl

Issue the command below on your physical MySQL server.

numactl --hardware

As one can see the output of the “numactl –hardware” command shows the imbalance between NUMA nodes on this particular server. Node 0 has 11,565MB free and Node 1 has 26,192MB free.

available: 2 nodes (0-1)
node 0 cpus: 0 2 4 6 8 10 12 14 16 18 20 22
node 0 size: 32722 MB
node 0 free: 11565 MB
node 1 cpus: 1 3 5 7 9 11 13 15 17 19 21 23
node 1 size: 32768 MB
node 1 free: 26192 MB
node distances:
node   0   1
  0:  10  20
  1:  20  10

The solution is to add a script to the /etc/mysql.cnf file.
This causes memory allocation to be performed in a round robin fashion across NUMA nodes.

STEP 1. Create a script file

vim /usr/sbin/mysqld_numactl

STEP 2. Add the following content to the file

# work around to startup mysqld using numactl
numactl=/usr/bin/numactl        # Adjust the path as needed
mysqld=/usr/sbin/mysqld         # Adjust the path as needed
# use exec to avoid having an extra shell around.
exec $numactl --interleave all $mysqld "$@"

STEP 3. Make script executable

chmod 750 /usr/sbin/mysqld_numactl

STEP 4. update the /etc/my.cnf

[mysqld_safe]
mysqld=mysqld_numactl

STEP 5. restart mysql to verify configuration change

service mysql restart

UPDATE:9/24/2015
With most major Linux distributions switching over to Systemd for init. I have updated this post. With Systemd in CentOS 7 it is much easier to use numactl to start up MySQL. I have provided a sample mysql.service file below.

vim /etc/systemd/system/mysql.service

Content of mysql.service

[Unit]
Description=MySQL Server
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql

# Use the nice program to set the server's scheduling priority to the given value
nice=0

PIDFile=/opt/mysql/data/mysqld.pid
LimitCore=unlimited

# Execute pre and post scripts as root
PermissionsStartOnly=true

# Needed to create system tables

# Start main service
#ExecStart=
ExecStart=/usr/bin/numactl --interleave all /opt/mysql/bin/mysqld --defaults-file=/opt/mysql/my.cnf --datadir=/mnt/cp_san/mysql/data/data --socket=/tmp/mysql.sock

Environment="TZ=America/Chicago"
Environment="LD_PRELOAD=/opt/mysql/lib/libtcmalloc_minimal.so"

WorkingDirectory=/opt/mysql
# Use this to switch malloc implementation
#EnvironmentFile=-/etc/sysconfig/mysql

#Restart=on-failure
LimitNOFILE=infinity
LimitMEMLOCK=infinity

RestartPreventExitStatus=1

PrivateTmp=false

Reference: http://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s