maria-discuss team mailing list archive

[Michael Caplan <michael@xxxxxxxxxxx>]

Just found this interesting article on the "The MySQL 'swap insanity' 
problem":

https://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/


Has anyone else tried the mentioned solutions?

- Forcing interleaved allocation with numactl --interleave=all.
- Flushing Linux’s buffer caches just before mysqld startup with sysctl 
-q -w vm.drop_caches=3.
- Forcing the OS to allocate InnoDB’s buffer pool immediately upon 
startup, using MAP_POPULATE where supported (Linux 2.6.23+), and falling 
back to memset otherwise.

One of the solutions seems dependent on the Twitter fork that was 
absorbed by the now removed XtraDB.


Thanks,

Mike




On 2018-06-15 08:24 AM, Michael Caplan wrote:
> 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