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 <mich...@eggplant.ws> 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 <mich...@eggplant.ws> 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.campb...@swisscom.com> 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....@lists.launchpad.net] On Behalf Of Michael Caplan >>> Sent: 15 June 2018 14:48 >>> To: Maria Discuss <maria-discuss@lists.launchpad.net> >>> 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@lists.launchpad.net >>> Unsubscribe : https://launchpad.net/~maria-discuss >>> More help : https://help.launchpad.net/ListHelp >>> _______________________________________________ >>> Mailing list: https://launchpad.net/~maria-discuss >>> Post to : maria-discuss@lists.launchpad.net >>> Unsubscribe : https://launchpad.net/~maria-discuss >>> More help : https://help.launchpad.net/ListHelp >>> >> >> > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : maria-discuss@lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp >
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
