Swap exhausted 99%, how to recycle swap?

A

Alucn

Aug 3 14:17:14 <user.crit> kernel: swap_pager: out of swap space
Aug 3 14:17:14 <user.crit> kernel: swp_pager_getswapspace(11): failed
How can a swap be recycled without stopping the service when only the MYSQL service is installed?
Thank you all！
20230803.png
 
This really looks like you have too little RAM to support your workload. I don't think there's a way to relax swap usage other than stopping the "memory hog". You *could* try whether adding more swap helps, if most of the RAM isn't actively used, this could be the case.
 
You could also try building databases/mysql57-server with PERFSCHM=off. This reduces memory usage by roughly 30%.
OTOH - if this is a database server it should definitely get more RAM to work with. databases under memory pressure usually become nearly unusable for any serious work.
 
6GB inactive, 6GB laundry - any chance that this is a ZFS pool with a manually set ARC size that is rather "big" (compared to the system's resources)?
The 6GB laundry certainly feel "much" to me but of course it heavily depends on the overall system workload & configurations.
 
jbo said:
any chance that this is a ZFS pool with a manually set ARC size that is rather "big" (compared to the system's resources)?
Click to expand...
FreeBSD's top(1) would show ARC if this machine would use ZFS. So I assume it's just caused by very random writes across a large address space (maybe typical for a database?).

Anyways, if you have 16G of RAM and your database server allocates 14G (with 10G being resident), you have too little RAM...
 
zirias@ said:
FreeBSD's top(1) would show ARC if this machine would use ZFS. So I assume it's just caused by very random writes across a large address space (maybe typical for a database?).

Anyways, if you have 16G of RAM and your database server allocates 14G (with 10G being resident), you have too little RAM...
Click to expand...
The file system is UFS，The database is approximately 4G in size, with a maximum of 10389991 table data
 
4G of swap for 16G memory? Don't do that.
gpw928 said:
you can swap to a file system resident file. See example 11.1.
Click to expand...
This is what you should do to fix the problem immediately.

In the long term, more RAM or expand the swap size permanently. There used to be a rule of thumb that swap should be 1.5x RAM but now that ram is huge, that gets ridiculous. Swapping on SSDs also murders them, so there's that to consider as well.
 
Alain De Vos said:
mariadb/mysql has a lot of tunables for memory usage...
Click to expand...
innodb_dedicated_server = 1
Enabling Automatic Configuration for a Dedicated MySQL Server
This is my MYSQL configuration file
Code: 
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
user = mysql
port = 3306
socket = /tmp/mysql.sock
server_id = 100
#bind_address = 127.0.0.1
basedir = /usr/local
datadir = /var/db/mysql
tmpdir = /var/db/mysql_tmpdir
log_error = /var/db/mysql/error.log
replica_load_tmpdir = /var/db/mysql_tmpdir
secure_file_priv = /var/db/mysql_secure
authentication_policy = mysql_native_password
explicit_defaults_for_timestamp = 1
max_connections = 3000
max_connect_errors = 6000
max_allowed_packet = 256M
gtid_mode = ON
enforce_gtid_consistency = 1
innodb_file_per_table = 1
innodb_dedicated_server = 1
innodb_data_home_dir = /var/db/mysql
innodb_log_group_home_dir = /var/db/mysql
innodb_data_file_path = ibdata1:128M:autoextend
innodb_temp_data_file_path = ibtmp1:128M:autoextend
innodb_flush_method = O_DIRECT
sync_binlog = 1
sync_relay_log = 1
binlog_cache_size = 16M
binlog_expire_logs_auto_purge = OFF
binlog_expire_logs_seconds = 3280000000
log_replica_updates = 1
log_bin = master
#relay_log = relay
#relay_log_purge = 1
#relay_log_recovery = 1
skip_name_resolve = 1
net_retry_count = 16380
innodb_io_capacity = 2000
innodb_io_capacity_max = 20000
#read_only = ON
#super_read_only = ON
event_scheduler = OFF
mysqlx = OFF
mysqlx_port = 33060
mysqlx_socket = /tmp/mysqlx.sock
mysqlx_bind_address = 127.0.0.1,::1
[mysqldump]
max_allowed_packet = 512M
quote_names
quick
 
max_connections is ridiculously high. Several buffers are multiplied by the max_connections count, which will increase the memory usage beyond what the machine is actually capable of. I suggest you run databases/mysqltuner and look at the suggestions. Just looking at these raw numbers don't mean anything, many of these parameters are going to depend on the size of your database and how it is used.

If you really need that many database connections you have basically two options, first is to add more memory to this machine, and I mean a lot more, 16 GB isn't going to cut it. Second option is to spread the load over multiple (slave) databases. It's common to have your read actions load-balanced over multiple database servers to increase performance, writes will be done to a single master. Typical database loads are 80% reads and 20% writes.
 
SirDice said:
It's common to have your read actions load-balanced over multiple database servers to increase performance, writes will be done to a single master.
Click to expand...
JFTR, if you develop your own software, this approach can be taken to the next level by designing dedicated "read models". A read model is a data model (e.g. in some SQL database a table) that contains the complete flattened data in exactly the format needed for one reading use case, so it can be queried without any joins/lookups and displayed without any transformation, which can often give another significant performance boost. You basically deal data redundancy for performance, it's the job of some syncer to fill the read models (and do possibly necessary transformations). For more info on that, see the CQRS design pattern.
 
covacat, that's a trade-off to carefully consider. Triggers will update the read models synchronously, which of course is an advantage, but could slow down writing operations. Sometimes, an "eventually consistent" approach can be better (using a separate syncer working asynchronously). It really depends on your scenario.
 
mysql optimizer sucks at times at complex queries with multiple tables so adding redundant fields to minimize the number of tables works
using straight_joins also works (sometimes) but its not consistent over mysql versions (unless you hardwire all the optimizer options)
also "active record" stuff blows goats
 
It's not just MySQL though. Let's put it straight, the normalization ideas that came with relational databases don't scale well. It looked like a great idea back then, but nowadays, we know better. Most "huge" web applications (including e.g. commercial social networks) use some form of CQRS combined with "eventual consistency", because that's the only viable way to scale to their size and amount of activity... (and they often don't even use relational databases at least for storing the read models, document-based databases often perform better)

Edit: I just realized the thread derailed a bit and I guess that's my fault ... sorry! ?
 
SirDice said:
max_connections is ridiculously high. Several buffers are multiplied by the max_connections count, which will increase the memory usage beyond what the machine is actually capable of. I suggest you run databases/mysqltuner and look at the suggestions. Just looking at these raw numbers don't mean anything, many of these parameters are going to depend on the size of your database and how it is used.

If you really need that many database connections you have basically two options, first is to add more memory to this machine, and I mean a lot more, 16 GB isn't going to cut it. Second option is to spread the load over multiple (slave) databases. It's common to have your read actions load-balanced over multiple database servers to increase performance, writes will be done to a single master. Typical database loads are 80% reads and 20% writes.
Click to expand...
mysqltuner Not very supportive of MYSQL 8.0
root@freebsd:~ # mysqltuner
>> MySQLTuner 1.9.9
* Jean-Marie Renouard <jmrenouard@gmail.com>
* Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.pl/
>> Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 8.0.31
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/db/mysql/error.log exists
[--] Log file: /var/db/mysql/error.log (6K)
[OK] Log file /var/db/mysql/error.log is not empty
[OK] Log file /var/db/mysql/error.log is smaller than 32 Mb
[OK] Log file /var/db/mysql/error.log is readable.
[!!] /var/db/mysql/error.log contains 12 warning(s).
[!!] /var/db/mysql/error.log contains 1 error(s).
[--] 3 start(s) detected in /var/db/mysql/error.log
[--] 1) 2023-08-03T10:06:06.903932Z 0 [System] [MY-010931] [Server] /usr/local/libexec/mysqld: ready for connections. Version: '8.0.31' socket: '/tmp/mysql.sock' port: 3306 Source distribution.
[--] 2) 2022-12-20T06:12:18.619402Z 0 [System] [MY-010931] [Server] /usr/local/libexec/mysqld: ready for connections. Version: '8.0.31' socket: '/tmp/mysql.sock' port: 3306 Source distribution.
[--] 3) 2022-12-20T05:51:11.923333Z 0 [System] [MY-010931] [Server] /usr/local/libexec/mysqld: ready for connections. Version: '8.0.31' socket: '/tmp/mysql.sock' port: 3306 Source distribution.
[--] 2 shutdown(s) detected in /var/db/mysql/error.log
[--] 1) 2023-08-03T09:58:57.815617Z 0 [System] [MY-010910] [Server] /usr/local/libexec/mysqld: Shutdown complete (mysqld 8.0.31) Source distribution.
[--] 2) 2022-12-20T06:10:54.046630Z 0 [System] [MY-010910] [Server] /usr/local/libexec/mysqld: Shutdown complete (mysqld 8.0.31) Source distribution.

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 9.8G (Tables: 64)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Views Metrics -----------------------------------------------------------------------------

-------- Triggers Metrics --------------------------------------------------------------------------

-------- Routines Metrics --------------------------------------------------------------------------

-------- Security Recommendations ------------------------------------------------------------------
[--] Skipped due to unsupported feature for MySQL 8

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 18h 11m 12s (61K q [0.402 qps], 18K conn, TX: 4G, RX: 706M)
[--] Reads / Writes: 0% / 100%
[--] Binary logging is enabled (GTID MODE: ON)
[--] Physical Memory : 16.0G
[--] Max MySQL memory : 767.5G
[--] Other process memory: 0B
[--] Total buffers: 12.0G global + 257.9M per thread (3000 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 14.1G (88.02% of installed RAM)
[!!] Maximum possible memory usage: 767.5G (4806.93% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (12/61K)
[OK] Highest usage of available connections: 0% (8/3000)
[OK] Aborted connections: 0.04% (8/18893)
[--] Query cache have been removed in MySQL 8
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 293 sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (0 on disk / 204 total)
[OK] Thread cache hit rate: 99% (8 created / 18K connections)
[OK] Table cache hit rate: 99% (1M hits / 1M requests)
[OK] table_definition_cache (2000) is greater than number of tables (391)
[OK] Open file limit used: 0% (6/470K)
[OK] Table locks acquired immediately: 100% (9 immediate / 9 locks)
[OK] Binlog cache memory access: 100.00% (643076 Memory / 643076 Total)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by P_S: 72B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] MyISAM Metrics are disabled on last MySQL versions.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 12.0G/9.8G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0.78125%): 48.0M * 2/12.0G should be equal to 25%
[!!] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk: 96 for 8 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.64% (64634321 hits/ 64866515 total)
[!!] InnoDB Write Log efficiency: 74.74% (7700343 hits/ 10303358 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2603015 writes)

-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine not available.

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] This server is acting as master for 1 server(s).
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[OK] This replication slave is running with the read_only option enabled.
[!!] This replication slave is lagging and slave has 1 second(s) behind master host.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Check warning line(s) in /var/db/mysql/error.log file
Check error line(s) in /var/db/mysql/error.log file
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Buffer Key MyISAM set to 0, no MyISAM table detected
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
key_buffer_size=0
innodb_log_file_size should be (=1G) if possible, so InnoDB total log files size equals 25% of buffer pool size.
innodb_buffer_pool_instances(=12)
Click to expand...
 
Alucn said:
Not very supportive of MYSQL 8.0
Click to expand...
Works fine, it's just the security bit that isn't checked. But we're not interested in that now anyway.

Code: 
[--] Reads / Writes: 0% / 100%
Is this a backup slave? It's a read-only slave as far as I can see. But nothing is ever read from it. Then why do you have max_connections = 3000? 10 is probably enough.

Code: 
[!!] Maximum reached memory usage: 14.1G (88.02% of installed RAM)
[!!] Maximum possible memory usage: 767.5G (4806.93% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
So, you're way overdrawn here. Notice the max possible memory usage? That's a direct consequence of setting max_connections very high.

Code: 
[OK] InnoDB buffer pool / data size: 12.0G/9.8G
Ok, this is mostly what you want, enough buffer pool to fit the entire database in it. But if you have a 10 GB database then 16 GB just isn't enough and you really need to add more memory to this machine. With a 12 GB buffer pool you only have 4GB left for the other buffers, and the OS would like to use a bit of memory too.

Code: 
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0.78125%): 48.0M * 2/12.0G should be equal to 25%
This needs fixing. It's not going to change the memory usage very much but it will improve the overall performance.
 
SirDice said:
工作正常，只是未检查安全位。但无论如何我们现在对此不感兴趣。

[代码]
[--] 读/写：0% / 100%
[/代码]
这是备用奴隶吗？据我所知，它是一个只读从站。但从未从中读取到任何内容。那为什么你有max_connections = 3000？10 可能就足够了。

[代码]
[!!] 最大内存使用量：14.1G（已安装 RAM 的 88.02%）
[!!] 最大可能内存使用量：767.5G（已安装 RAM 的 4806.93%）
[!!] 其他进程可能使用的总体内存超出了内存
[/代码]
所以，你在这里已经透支了。注意最大可能的内存使用量吗？这是将 max_connections 设置得非常高的直接结果。

[代码]
[确定] InnoDB缓冲池/数据大小：12.0G/9.8G
[/代码]
好的，这基本上就是您想要的，足够的缓冲池可以容纳整个数据库。但如果您有 10 GB 数据库，那么 16 GB 还不够，您确实需要向该计算机添加更多内存。对于 12 GB 的缓冲池，您只剩下 4GB 供其他缓冲区使用，并且操作系统也想使用一点内存。

[代码]
[!!]比率InnoDB日志文件大小/InnoDB缓冲池大小（0.78125％）：48.0M * 2/12.0G应等于25％
[/代码]
这需要修复。它不会太大地改变内存使用，但会提高整体性能。
Click to expand...
感谢你的回复！
 
You must log in or register to reply here.
Back
Top