Skip to main content

High swap usage that goes away when restarting SQL

maestroc

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)

 

Comments

2 comments

Date Votes
  • cPRex Jurassic Moderator

    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
  • victor rege
    • Edited

    A few things stand out in your config and tuner output that point toward a specific culprit rather than a general "tune more RAM" fix:

    1. Buffer pool instances vs. size mismatch
    You have innodb_buffer_pool_size = 2000M split across 8 buffer pool instances. MySQL recommends roughly 1GB minimum per instance — at 2GB total split 8 ways, each instance is only ~250MB, which hurts efficiency and fragments memory unnecessarily. Either drop innodb_buffer_pool_instances to 1-2, or raise the pool size significantly if you go with 8.

    2. The real leak suspect: performance_schema
    Your performance-schema=ON is using 390MB and on a memory-constrained 8GB box with 15,600+ InnoDB tables and heavy table churn, performance_schema's internal memory tables can grow unpredictably over time — this fits your exact symptom (swap climbs gradually, resets on restart). Try setting performance-schema=OFF (or heavily limiting instrumentation) for a week and watch swap behavior. This is a very common cause of "slow memory creep that a restart fixes."

    3. MyISAM tables (7,944 of them!)
    MyISAM doesn't respect innodb_buffer_pool_size — it uses OS page cache and key_buffer_size instead. With that many MyISAM tables (likely old Joomla/WP installs or leftover cache tables), the OS is caching all that in page cache, competing with everything else for RAM and pushing things into swap under load spikes. Migrating them to InnoDB (as the tuner also suggested) would consolidate memory management under one engine.

    4. Table cache is very large
    table_open_cache=8000 and table_definition_cache=25000 — with ~24,000 total tables, this is reasonable, but combined with the above two issues, it adds sustained memory pressure that never gets released back to the OS (a well-known glibc/MySQL memory fragmentation issue, especially on high table-count servers with frequent connection churn — you have 832K connections in 36 hours).

    5. Quick diagnostic step
    Before changing anything else, run this during a high-swap period:

     
    cat /proc/$(pidof mysqld)/status | grep -i vm

    and compare VmRSS (actual resident memory) vs what free -m reports as used. If MySQL's own RSS is much lower than total swap usage, MySQL isn't the actual leak — something else (php-fpm, an unbounded process for a Joomla/WP site) is filling RAM and MySQL is just the collateral damage when it can't allocate anything fast, which is a very plausible alternative explanation for "restarting SQL fixes it" — restarting SQL frees several hundred MB instantly and buys headroom, even if it wasn't the actual leaking process.

    Suggested next steps in order:

    1. Run the mysqladmin proc status moderator suggested during a spike
    2. Turn off performance_schema temporarily and monitor
    3. Set up that memory-watch cron script and let it run through a swap-filling cycle so you can see what's actually growing minute by minute
    4. Check ps aux --sort=-%mem at the same time to rule out php-fpm/Apache/LiteSpeed workers as the real memory hog

    Happy to help interpret the memwatch log output once you've collected it.

    1

Please sign in to leave a comment.

Didn't find what you were looking for?

New post