MySQL Bugs: #57241: NUMA aware mysqld

Bug #57241	NUMA aware mysqld_safe/mysqld
Submitted:	5 Oct 2010 8:00
Reporter:	Mikiya Okuno	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Since NUMA architecture is getting common on x86 systems, "swap insanity" problem is getting a common and serious problem, which would make the running system almost unusable due to thrashing.

There are several options to solve this problem.

- Add "numa=off" on kernel boot parameter
- Use "numactl --interleave all" in mysqld_safe script
- Use set_mempolicy(2)/libnuma in mysqld

Because "swap insanity" is a really bad problem, we should let MySQL Server to use interleaving mode anyway.

How to repeat:
Use a machine which has more than one NUMA node, and allocate more than half of total system memory for mysqld via e.g. innodb_buffer_pool_size.

Suggested fix:
See above.

* libnuma should be statically linked to mysqld; it's license is LGPLv2.
* If we implement it using numactl in mysqld_safe, that should be controlled via option because not every system has libnuma and numactl.

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

http://blog.jcole.us/2012/04/16/a-brief-update-on-numa-and-mysql/

http://www.percona.com/doc/percona-server/5.5/performance/innodb_numa_support.html

I simply cannot comprehend how this is still open. This goes far beyond "S4 (Feature request)" IMO. It's a crippling issue on just about every dedicated database server built in the past five years. Just pull this patch already:
https://github.com/twitter/mysql/commit/19cf63c596c0146a72583998d138190cc285df5c

http://engineering.linkedin.com/performance/optimizing-linux-memory-management-low-latency...

http://bugs.mysql.com/bug.php?id=72811
(Set NUMA mempolicy for optimum mysqld performance)

https://www.flamingspork.com/blog/2015/07/08/the-sad-state-of-mysql-and-numa/

I think the lack of this feature hurts MySQL as a whole more and more everyday. I can't recall anything that has been discussed so often but has had such little attention.

While I feel "S4" Severity probably made sense in 2010, I do not agree that this is appropriate anymore.

Probably resolved by the accepted user patch for Bug #72811. Does anyone think that this feature request is still needed or should we close it? Mikiya? Change note for that is:

"as of the upcoming 5.6.27, 5.7.9, 5.8.0 releases:

The new innodb_numa_interleave read-only configuration option allows you
to enable the NUMA interleave memory policy for allocation of the InnoDB
buffer pool. When innodb_numa_interleave is enabled, the NUMA memory
policy is set to MPOL_INTERLEAVE for the mysqld process. After the InnoDB
buffer pool is allocated, the NUMA memory policy is set back to
MPOL_DEFAULT. This option is only available on NUMA-enabled systems.

Thanks to Stewart Smith for the patch."

James Day, MySQL Senior Principal Support Engineer, Oracle

Looks like it might still be needed or at least a more complete fix of Bug #57241 needs to be done. See bug #78953.

I meant to say a more complete fix of Bug #72811.