2

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.

Zystrix
  • 21
  • 1
  • Is there an actual problem? Is performance poor? How much swap do you have? – David Schwartz Sep 19 '22 at 21:07
  • "220G" -- That's an odd RAM size; please explain. – Rick James Sep 19 '22 at 23:41
  • @RickJames My bad, it is 220Gi, so 7*32GB – Zystrix Sep 20 '22 at 06:30
  • @DavidSchwartz 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. – Zystrix Sep 20 '22 at 06:36
  • Red Hat suggests SWAP size of 20% of RAM. They have been successful enough, I would use their suggestion and make a 44G SWAP size. And hope it is never used. – Wilson Hauck Sep 22 '22 at 20:49

4 Answers4

0

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".)

Rick James
  • 2,058
  • 5
  • 11
  • If I want to import a full backup, I copy the compressed sql scripts to the server and then I import them like `zcat backup.sql.gz | mysql -u user -p[my_password] Database` Edit: The compressed scripts are about 9G in size. – Zystrix Sep 20 '22 at 06:15
  • @Zystrix - Thanks. I still can't explain "swap", but see what I added to my Answer. – Rick James Sep 20 '22 at 22:20
0

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

GioMac
  • 4,444
  • 3
  • 24
  • 41
-1

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
HatLess
  • 130
  • 6
  • Bad advice. You *want* to write data to swap as early as possible. Right now, I/O is not precious. If you wait until I/O is precious to write data to swap, those I/Os will impact performance. Right now, they don't because the system is not I/O bound. – David Schwartz Sep 20 '22 at 07:21
  • @DavidSchwartz What? Writing directly to swap as early as possible impacts IO negatively as the swap space uses storage meaning IO will immediately be impacted. The more aggressively a system swaps, the more IO hit you will get. – HatLess Sep 24 '22 at 22:17
  • Writing directly to swap *before* I/O is under significant load means that you won't need to write it out later when I/O is precious because you are under load. At somen point in the future, this system may be under memory pressure. Do you want it to start writing the garbage it needs to evict from memory to swap then when it needs I/O to do the swapping? Better to do it before you need to because when you need to do it, you'll need to do lots of other things too. – David Schwartz Sep 25 '22 at 04:47
-1

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.

David Schwartz
  • 31,215
  • 2
  • 53
  • 82
  • How much swap would recommend? I'm a little bit confused, because at first (without mysql-exporter, node-exporter) the swap was barely used (0.1%). – Zystrix Sep 20 '22 at 08:51
  • @Zystrix It probably won't make much difference for a system like this. Likely the swap is just holding copies of data that's in memory anyway, so it's having no effect on performance. If the system never encounters memory pressure, it won't matter. – David Schwartz Sep 20 '22 at 09:24