High swap usage that goes away when restarting SQL
I've been battling this issue for over a month now and I am fed up with ChatGPT going in circles on me so I am posting here in hopes of a solution (or at least finding the culprit).
On my VPS server (8gb ram/32 cores/CloudLinux) I've had repeated problems with the swap filling up and occasionally bringing down all of my sites until I go in and restart SQL. This frees up the swap and all is well for a while. I've noticed that sometimes it can run just fine for quite a long time while using 99% swap and 65% of the RAM until there is a major traffic spike.
I've tweaked a lot of settings in my.cnf based on the AI recommendations and the suggestions from sqltuner but the problem keeps coming back. Server load is not high. Usually it stays around 1-2 max unless it is overnight and running maintenance/backups. I did notice a couple of sites getting hit hard but I put those behind Cloudflare and in one case password protected the whole folder but the swap issue keeps coming back.
Current my.cnf is below:
[mysqld]
disable-log-bin=1
default-authentication-plugin=mysql_native_password
innodb_buffer_pool_size = 2000M
innodb_log_buffer_size=128M
innodb_redo_log_capacity=1000M
key_buffer_size=16M
table_definition_cache=25000
table_open_cache=8000
performance-schema=ON
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_log_buffer_size=64M
max_connections=90
skip-name-resolve=0
#key_buffer_size=256M
max_allowed_packet=268435456
open_files_limit=65000
innodb_file_per_table=1
mysqlx=0
After several weeks of trying different things and focusing on the idea that SQL is the culprit GPT is now suggesting that it might be something else but it doesn't seem to have a clue what the root cause might be. CGPT suggested I set this logger up to troubleshoot but I have not done so yet:
#!/bin/bash
echo "===== $(date) =====" >> /root/memwatch.log
free -m >> /root/memwatch.log
echo >> /root/memwatch.log
ps -eo pid,ppid,rss,pmem,comm --sort=-rss | head -30 >> /root/memwatch.log
echo >> /root/memwatch.log
Abbreviated sqltuner output is below. Note that it suggests raising innodbbufferpoolsize but I have taken that as high as 4gb but the swap still fills up. I also recently lowered max connections from 150 down to 90 and again, swap still fills up. Also note this is a server filled with Joomla and WP sites if that matters at all.
>> MySQLTuner 2.7.0
-------- Storage Engine Statistics -----------------------------------------------------------------
Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM -ndbcluster -ndbinfo +PERFORMANCE_SCHEMA
Data in InnoDB tables: 10.1G (Tables: 15604)
Data in MyISAM tables: 3.4G (Tables: 7944)
Data in MEMORY tables: 0B (Tables: 299)
Total fragmented tables: 0
Currently running supported MySQL version 8.0.46
-------- Performance Metrics -----------------------------------------------------------------------
Up for: 1d 12h 55m 56s (47M q [360.690 qps], 832K conn, TX: 268G, RX: 13G)
Reads / Writes: 80% / 20%
Binary logging is disabled
Physical Memory : 7.5G
Max MySQL memory : 2.6G
Other process memory: 0B
Total buffers: 2.1G global + 1.9M per thread (90 max threads)
Performance_schema Max memory usage: 390M
Galera GCache Max memory usage: 0B
Maximum reached memory usage: 2.6G (34.85% of installed RAM)
Maximum possible memory usage: 2.6G (35.25% of installed RAM)
Overall possible memory usage with other process is compatible with memory available
Slow queries: 0% (61/47M)
Highest usage of available connections: 82% (74/90)
Aborted connections: 0.00% (24/832433)
CPanel and Flex system skip-name-resolve should be on
Query cache has been removed since MySQL 8.0
Sorts requiring temporary tables: 0% (557 temp sorts / 11M sorts)
✘ Joins performed without indexes: 2077294
Temporary tables created on disk: 0% (39 on disk / 3M total)
Thread cache hit rate: 99% (7K created / 832K connections)
Table cache hit rate: 99% (157M hits / 158M requests)
table_definition_cache (25000) is greater than number of tables (24179)
Open file limit used: 2% (1K/65K)
Table locks acquired immediately: 99% (5M immediate / 5M locks)
-------- Performance schema ------------------------------------------------------------------------
Performance_schema is activated.
Memory used by Performance_schema: 390.8M
Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
✘ Consider migrating 7944 following tables to InnoDB:
General MyIsam metrics:
+-- Total MyISAM Tables : 7944
+-- Total MyISAM indexes : 357.8M
+-- KB Size :16.0M
+-- KB Used Size :15.4M
+-- KB used :96.5%
+-- Read KB hit rate: 99.9% (120M cached / 98K reads)
+-- Write KB hit rate: 82.0% (104K cached / 85K writes)
MyISAM Metrics are disabled since MySQL 8.0.
-------- InnoDB Metrics ----------------------------------------------------------------------------
InnoDB is enabled.
InnoDB Thread Concurrency: 0
InnoDB File per table is activated
InnoDB Buffer Pool size ( 2.0G ) under limit for 64 bits architecture: (17179869184.0G )
✘ InnoDB buffer pool / data size: 2.0G / 10.1G
InnoDB Redo Log Capacity is set to 1000.0M
Hourly InnoDB log write rate: 242M/hour
Your innodb_redo_log_capacity is sized to handle more than 1 hour of writes.
✘ InnoDB buffer pool instances: 8
Number of InnoDB Buffer Pool Chunk: 16 for 8 Buffer Pool Instance(s)
Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
InnoDB Read buffer efficiency: 100.00% (107664023954 hits / 107664675588 total)
✘ InnoDB Write Log efficiency: 74.21% (41166910 hits / 55469976 total)
InnoDB log waits: 0.00% (0 waits / 14303066 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
No replication slave(s) for this server.
Binlog format: ROW
XA support enabled: ON
Semi synchronous replication Master: Not Activated
Semi synchronous replication Slave: Not Activated
This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
/var/log/mysqld.log is > 32MB, you should analyze why or implement a rotation log strategy such as logrotate!
Check warning line(s) in /var/log/mysqld.log file
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_join_buffer_size
MyISAM engine is deprecated, consider migrating to InnoDB
Buffer Key MyISAM set to 0, no MyISAM table detected
Variables to adjust:
join_buffer_size (> 256.0K, or always use indexes with JOINs)
key_buffer_size=0
innodb_buffer_pool_size (>= 10.1G) if possible.
innodb_buffer_pool_instances(=2)
innodb_log_buffer_size (> 64M)
-
Hey there! A few thoughts.
With MySQL only running for about 36 hours, the tuner script may not always give the best advice. Having the service up a bit longer typically lets it give better output, but I understand this may not be an option in your situation.
it might be best to see what the database is doing in real time by running "mysqladmin proc status" on the machine. This will show you all the connections and what they are doing, which might point you in the right direction if one particular site is busy or having an issue with the database.
Ultimately, I don't think there is any particular cPanel issue here, but just something with the database handling your transactions specifically that is causing the problem.
0
Please sign in to leave a comment.
Comments
1 comment