Server uses 100% swap after fresh mysql installation

Question

I set up a new server with 220G RAM and Ubuntu 22.04.1 LTS.

After installing MySQL I changed the mysql config to:

innodb_buffer_pool_size = 170G
innodb_buffer_pool_instances = 64
innodb_buffer_pool_chunk_size = 134217728
innodb_log_file_size = 13G
collation_server = utf8_unicode_ci
character_set_server = utf8
sql-mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,IGNORE_SPACE,NO_ENGINE_SUBSTITUTION"
user = mysql
datadir = /mnt/data/mysql/data
tmpdir = /mnt/data/mysql/tmp
key_buffer_size = 32M
thread_cache_size = 32
myisam-recover-options = BACKUP
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_bin = /mnt/data/mysql/log/mysql-bin.log
max_binlog_size = 2G

/mnt/data is a lvm partition with 400G.

I also installed MySQL-Exporter and node-exporter. Before I have installed both exporters, the database import worked without any problem, but if want to import the database (ca. 150G uncompressed) now, the SWAP partition gets fully occupied, but the RAM is only used up to 85%.

Now I want to find out which process is responsible for the SWAP load.

How can I list how much swap each process uses?

If possible I want to keep the swappiness value at default.

Edit:

• Increased the SWAP to 64 GiB
• For the import I reduced the swappiness to 10
• After the import I set the swappiness to 50

The server has been running for 2 months and after the import I did not have any issues with the swap partition.

Answer 1

I like swappiness=1 for a mostly-MySQL server.

To help chase down the problem, lower innodb_buffer_pool_size. Start with, say, 50GB. After all, the buffer pool is a "cache".

When you "import" 150G of data, where does it live? Perhaps the importer is loading the entire thing into RAM? (That would be a poor design.) Most compression/uncompression tools are quite happy to use only a small amount of RAM, yet can read/write very large files on disk.

Remote backup

If you backup is on host "backup" and you are reloading mysql on "db", consider running this on "backup":

zcat backup.sql.gz | mysql -h db ...

That eliminates needing to use disk space on "db" for the zip. Also, it probably takes less overall time. (I do not think it will affect "swap" usage on either machine.)

(Similarly, you could take the dump from "backup".)

Answer 2

As simple solution, you can use mysqltuner script

Adjust dirty pages settings:

sysctl -a|grep vm.dirty

Same for shared memory settings:

sysctl -a|kernel.shm

I don't think that adjusting vm.swappiness will do a lot if your mysql instance is not using RAM and eats 100% of swap. Consider checking RAM usage with ps_mem utility.

Reference: https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/8/html/managing_monitoring_and_updating_the_kernel/adjusting-kernel-parameters-for-database-servers_managing-monitoring-and-updating-the-kernel

Answer 3

This sounds like an aggressive swappiness configuration which I assume is high so the system starts swapping early.

A systems default value is high (60%) which depending on the environment may or may not be adequate.

You can check your current swappiness value using;

$ sysctl vm.swappiness

As you seem to be aggressively swapping, setting a lower value would be beneficial.

To change the value, you can use;

$ sysctl vm.swappiness=10

Answer 4

Not that I noticed it. But I am worried, that the performance will suddenly get poor or MySQL will crash. At the moment I only have 4G of swap. Only MySQL should run on the server and the swap partition should never be used. I could increase the swap partition (it's also a lvm partition), but ideally it will never be used.

You have no issues. You want your swap to be used.

When you start up a machine, lots of processes run. Many of them will dirty memory that will never be used during the entire time the system remains running. For example, startup code faults in for programs that only startup once.

You want this junk to be written to swap for three reasons:

1. This stuff cannot be evicted from RAM without first writing it to swap. The system cannot prove it will never be accessed. Some of it, for example, may contain data needed to do a clean shutdown of some server process. Discarding the only copy would violate sanity guarantees.

2. If the system is ever under memory pressure, I/O will be precious. Performance will benefit from being able to evict this data from memory. But that can only be done by writing it to swap first. If it has already been written to swap, then there will be no need to write this data to swap when I/O is precious. So, by design, it is written to swap whenever it is "free" to do so.

3. By evicting this junk from memory, more RAM is useable as an I/O cache. This means more clean pages that are being re-used can be kept in memory, boosting performance.

Having 4GB of junk that can be evicted because it will likely never be used on a system with over 200GB of RAM is entirely reasonable and there is absolutely no reason to not want it to be written to swap.