maria-discuss team mailing list archive
-
maria-discuss team
-
Mailing list archive
-
Message #05151
Re: Chewing Through Swap - Swappiness = 0
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 <michael@xxxxxxxxxxx> 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, <Rhys.Campbell@xxxxxxxxxxxx> 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=
>> 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
>> _______________________________________________
>> 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
>>
>
>
Follow ups
References
