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