maria-discuss team mailing list archive
Mailing list archive
Re: Chewing Through Swap - Swappiness = 0
Maria Discuss <maria-discuss@xxxxxxxxxxxxxxxxxxx>
Michael Caplan <michael@xxxxxxxxxxx>
Fri, 15 Jun 2018 09:48:34 -0300
Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.8.0
Just found this interesting article on the "The MySQL 'swap insanity'
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.
On 2018-06-15 08:24 AM, Michael Caplan wrote:
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
total used free shared buff/cache
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.
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?
# GENERAL #
user = mysql
default-storage-engine = InnoDB
character-set-server = utf8
collation_server = utf8_general_ci
performance_schema = 0
max_allowed_packet = 16777216
# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP
# SAFETY #
max-allowed-packet = 16777216
max-connect-errors = 1000000
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