Just a little follow up on this issue.
Reducing my innodb-buffer-pool-size (by 10GB) has reduced the amount of
time before swap gets chomped on. But not eliminated it.
I've been looking into Jeremy Cole's work on MySQL "swap insanity"
https://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/
Sure enough, I am seeing a big imbalance between nodes:
N0 : 7370529 ( 28.12 GB)
N1 : 1198462 ( 4.57 GB)
active : 6413818 ( 24.47 GB)
anon : 8513261 ( 32.48 GB)
dirty : 8513259 ( 32.48 GB)
kernelpagesize_kB: 2260 ( 0.01 GB)
mapmax : 221 ( 0.00 GB)
mapped : 83267 ( 0.32 GB)
swapcache : 2 ( 0.00 GB)
I am going to be trying his "numactl --interleave all command" solution
to see how that addresses the issue.
Thanks,
Mike
On 2018-06-15 11:59 AM, Guillaume Lefranc wrote:
VSZ is in theory the max size your process can reach if fully
allocating resources, so usually (if there are no mistakes in the
process) you should have more physical memory than your VSZ (at least
for MySQL, this isn't the case for other workloads).
Regards
Le ven. 15 juin 2018 à 16:53, Michael Caplan <[email protected]
<mailto:[email protected]>> a écrit :
If I'm reading the output correct mysql is currently using 35.8
%MEM, 67GB VSZ, and 23.6GB RSS
Not sure how virtual memory size is calculated, but that seems big....
I'll try dropping my innodb-buffer-pool-size from 50GB to 47GB or
more.
Thanks,
Mike
On 2018-06-15 11:38 AM, Guillaume Lefranc wrote:
Rhys.Campbell, 0 means off since kernel version 3.5.
See reference here:
https://community.hortonworks.com/questions/71095/why-not-set-swappiness-to-zero.html
That's why most people use 1 (safe value)
Michael Caplan: please look at the size of your mysqld process in
"ps aux" command output. Due to overhead it might be actually
bigger than 50G. I've checked settings we use in prod for MariaDB
10.1 on 64G server and I set the buffer pool no bigger than 47G.
Regards
Le ven. 15 juin 2018 à 16:28, <[email protected]
<mailto:[email protected]>> a écrit :
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
<mailto:maria-discuss-bounces%2Brhys.campbell>[email protected]
<mailto:[email protected]>] On Behalf Of
Michael Caplan
Sent: 15 June 2018 14:48
To: Maria Discuss <[email protected]
<mailto:[email protected]>>
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
<https://launchpad.net/%7Emaria-discuss>
Post to : [email protected]
<mailto:[email protected]>
Unsubscribe : https://launchpad.net/~maria-discuss
<https://launchpad.net/%7Emaria-discuss>
More help : https://help.launchpad.net/ListHelp
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
<https://launchpad.net/%7Emaria-discuss>
Post to : [email protected]
<mailto:[email protected]>
Unsubscribe : https://launchpad.net/~maria-discuss
<https://launchpad.net/%7Emaria-discuss>
More help : https://help.launchpad.net/ListHelp
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
