maria-discuss team mailing list archive

[Michael Caplan <michael@xxxxxxxxxxx>]

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