Just found this interesting article on the "The MySQL 'swap insanity' problem":

https://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architectu...

Has anyone else tried the mentioned solutions?

- Forcing interleaved allocation with numactl --interleave=all. - Flushing Linux’s buffer caches just before mysqld startup with sysctl -q -w vm.drop_caches=3. - Forcing the OS to allocate InnoDB’s buffer pool immediately upon startup, using MAP_POPULATE where supported (Linux 2.6.23+), and falling back to memset otherwise.

One of the solutions seems dependent on the Twitter fork that was absorbed by the now removed XtraDB.

Thanks,

Mike

On 2018-06-15 08:24 AM, Michael Caplan wrote:

Hi,

I'm trying to figure out why my recently put into production MariaDB is so swap hungry.

I'm running 10.2.14, with roughly 300GB data (1000K +/- tables). 95% tables are innodb.  I have 64GB RAM, with INNODB buffer pool size set to 50GB (full my.cnf below).  The OS is Ubuntu 16.04.4. This is a dedicated MariaDB server.

I have swappiness set to 0.

I started with available swap set to just shy of 1GB.  When swap got to 85% used, I bumped swap to 3GB.  A day later, swap again was 85% used, and I bumped it to 5GB.  A day later, swap again was 85% used, so I bumped it to 15GB.  2 days later 50% of the available swap was used.  I restarted the DB moments ago, freeing up all 15GB but 128MB of swap:

total        used        free      shared buff/cache available Mem:            62G         20G        4.7G        266M 37G         41G Swap:           14G        128M         14G

My understanding is that the OS should never allocate swap when swappiness is 0, so that has me confused.

I've had similar issues with MySQL 5.6 (what I upgraded from) dipping into swap, but that instance had swappiness set to 1, and never consumed more than 5GB of swap.

Questions:

1) How much of this is an OS issue vs MariaDB issue?

2) What MariaDB config vars should I be looking at to fix this issue?

3) What linux config vars should I be looking at to fix this issue?

Thanks,

Mike

[mysqld]

# GENERAL # user                           = mysql default-storage-engine         = InnoDB character-set-server           = utf8 collation_server               = utf8_general_ci performance_schema             = 0 max_allowed_packet             = 16777216 sql_mode                       = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# MyISAM # key-buffer-size                = 32M myisam-recover                 = FORCE,BACKUP

# SAFETY # max-allowed-packet             = 16777216 max-connect-errors             = 1000000 skip-name-resolve sysdate-is-now                 = 1 innodb                         = FORCE local_infile                   = 0 secure_auth                    = 1 safe_user_create               = 1 skip_symbolic_links            = 1 wait_timeout                   = 28800

# DATA STORAGE # datadir                        = /var/lib/mysql/

# BINARY LOGGING # log-bin                        = /var/lib/mysql/mysql-bin binlog_format                  = MIXED server-id                      = 3 expire-logs-days               = 7 sync-binlog                    = 1

# REPLICATION # read-only                      = 1 skip-slave-start               = 1 log-slave-updates              = 1 relay-log                      = /var/lib/mysql/relay-bin slave-net-timeout              = 60 sync-master-info               = 1 sync-relay-log                 = 1 sync-relay-log-info            = 1

# CACHES AND LIMITS # tmp-table-size                 = 32M max-heap-table-size            = 32M query-cache-type               = 0 query-cache-size               = 0 max-connections                = 500 thread-cache-size              = 50 open-files-limit               = 65535 table-definition-cache         = 4096 table-open-cache               = 10240

# INNODB # innodb-flush-method            = O_DIRECT innodb-log-files-in-group      = 2 innodb-log-file-size           = 512M innodb-flush-log-at-trx-commit = 2 innodb-file-per-table          = 1 innodb-buffer-pool-size        = 50G