[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
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@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
Rhys.Campbell, 0 means off since kernel version 3.5. See reference here: https://community.hortonworks.com/questions/71095/why-not-set-swappiness-to-... 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@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.com@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
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-... 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@swisscom.com mailto:Rhys.Campbell@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 mailto:maria-discuss-bounces%2Brhys.campbell=swisscom.com@lists.launchpad.net mailto:swisscom.com@lists.launchpad.net] On Behalf Of Michael Caplan Sent: 15 June 2018 14:48 To: Maria Discuss <maria-discuss@lists.launchpad.net mailto: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 https://launchpad.net/%7Emaria-discuss Post to : maria-discuss@lists.launchpad.net mailto:maria-discuss@lists.launchpad.net 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 : maria-discuss@lists.launchpad.net mailto:maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss https://launchpad.net/%7Emaria-discuss More help : https://help.launchpad.net/ListHelp
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@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-... 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@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.com@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
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-architectu...
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@eggplant.ws mailto:michael@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-... 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@swisscom.com mailto:Rhys.Campbell@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 mailto:maria-discuss-bounces%2Brhys.campbell=swisscom.com@lists.launchpad.net mailto:swisscom.com@lists.launchpad.net] On Behalf Of Michael Caplan Sent: 15 June 2018 14:48 To: Maria Discuss <maria-discuss@lists.launchpad.net mailto: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 https://launchpad.net/%7Emaria-discuss Post to : maria-discuss@lists.launchpad.net mailto:maria-discuss@lists.launchpad.net 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 : maria-discuss@lists.launchpad.net mailto:maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss https://launchpad.net/%7Emaria-discuss More help : https://help.launchpad.net/ListHelp
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@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-architectu...
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@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-... 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@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.com@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
Resurrecting this thread.
I'm still suffering from MariaDB 10.2.14 ripping threw allocated memory and overflowing into swap. innodb_numa_interleave=1 did not solve the problem. Actually, the problem has become even more aggressive. Ever since enabling full text search on thousands of tables, MariaDB has become way more aggressive in over consuming memory. I'm faced with either hitting OOM Killer, feeding it by creating more swap, or restarting MariaDB every other day. Of course, all three scenarios are unsustainable.
Any one else dealing with a similar issue? https://jira.mariadb.org/browse/MDEV-6319 and https://jira.mariadb.org/browse/MDEV-15344 both seem potentially related.
Thanks,
Mike
On 2018-06-20 11:07 a.m., Guillaume Lefranc wrote:
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@eggplant.ws mailto:michael@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-architectu...
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@eggplant.ws mailto:michael@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-... 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@swisscom.com mailto:Rhys.Campbell@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 mailto:maria-discuss-bounces%2Brhys.campbell=swisscom.com@lists.launchpad.net mailto:swisscom.com@lists.launchpad.net] On Behalf Of Michael Caplan Sent: 15 June 2018 14:48 To: Maria Discuss <maria-discuss@lists.launchpad.net mailto: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 https://launchpad.net/%7Emaria-discuss Post to : maria-discuss@lists.launchpad.net mailto:maria-discuss@lists.launchpad.net 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 : maria-discuss@lists.launchpad.net mailto:maria-discuss@lists.launchpad.net 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 : maria-discuss@lists.launchpad.net mailto:maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Thanks Rhys,
Checked out the page. Will explore this. What's unclear to me is how to figuring out "what's being pushed into swap" from there.
Thanks,
Mike
On 2018-06-15 11:26 AM, Rhys.Campbell@swisscom.com wrote:
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@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
participants (3)
-
Guillaume Lefranc -
Michael Caplan -
Rhys.Campbell＠swisscom.com