maria-discuss team mailing list archive

[Guillaume Lefranc <guillaume@xxxxxxxxxxxx>]

Hi Michael

If you're using MariaDB 10.2 and above, the following config
parameter: innodb_numa_interleave=1
should solve your issue.

Hope that helps
GL

Le mer. 20 juin 2018 à 14:48, Michael Caplan <michael@xxxxxxxxxxx> a écrit :

> 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 <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
>>>
>>
>>
> _______________________________________________
> 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
>