maria-discuss team mailing list archive

[<Rhys.Campbell@xxxxxxxxxxxx>]

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

0 doesn't mean off. If you really want to turn if off then see swapoff / adjust your fstab.

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

I'd say it's an application problem. You simply have more data than RAM. In your case it might be more important to look at swap in/out to see if your server is under pressure. 

I would have a look at the innodb buffer pool page usage by database / table to try and figure out what's being pushed into swap and go from there. http://www.youdidwhatwithtsql.com/innodbbufferpage-queries/2041/ 




-----Original Message-----
From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=swisscom.com@xxxxxxxxxxxxxxxxxxx] On Behalf Of Michael Caplan
Sent: 15 June 2018 14:48
To: Maria Discuss <maria-discuss@xxxxxxxxxxxxxxxxxxx>
Subject: [Maria-discuss] Chewing Through Swap - Swappiness = 0

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



_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp