Centos服务器内存占用异常求助：Apache+MySQL站点内存飙升

Hey Amir, let's break down this memory creep issue on your CentOS server step by step. You suspect MySQL is the culprit, which makes sense, but let's confirm first and work through fixes that are easy to follow even if you're not a full-time sysadmin.

Don't just rely on the top overview—let's zoom in on specific processes:

• Check MySQL alone: Run top -p $(pidof mysqld) to watch only the MySQL process. Pay attention to RSS (actual physical memory used) and VSZ (virtual memory). If these numbers keep climbing over time, MySQL is definitely part of the problem.
• Check Apache too: Run top -p $(pidof httpd) to monitor Apache's processes. If you see dozens of httpd child processes with growing memory usage, Apache might have a memory leak too.
• Peek inside MySQL: Log into MySQL and run SHOW GLOBAL STATUS LIKE 'Threads_connected';—if this number keeps going up and never drops, your app might not be closing database connections properly.

Let's tackle the common MySQL memory hogs:

a. Fix Stale Database Connections #

If you saw Threads_connected spiking, your app might be leaving connections open. Let's adjust MySQL's timeout settings to clean up idle connections:

1. Edit your MySQL config file (usually /etc/my.cnf or /etc/mysql/my.cnf) and add these lines:

   wait_timeout = 300
   interactive_timeout = 300
   

   (This sets idle connections to close after 5 minutes—way better than the default 8 hours.)
2. Restart MySQL: systemctl restart mysqld

b. Tune the InnoDB Buffer Pool #

The InnoDB buffer pool is MySQL's biggest memory user. If it's set too large, it can eat up all your server's memory:

• Check the current size: Run mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
• Adjust it to 50-60% of your server's physical memory (e.g., if you have 8GB RAM, set it to 4G). Add this to your my.cnf:

  innodb_buffer_pool_size = 4G
  

• Restart MySQL to apply the change.

c. Hunt Down Slow/Resource-Hungry Queries #

Bad queries can cause memory to bloat over time. Let's enable MySQL's slow query log to find them:

1. Add these lines to my.cnf:

   slow_query_log = 1
   slow_query_log_file = /var/log/mysql/slow.log
   long_query_time = 2
   

   (This logs any query that takes longer than 2 seconds.)
2. Restart MySQL, let your site run for a few hours, then look at the log: cat /var/log/mysql/slow.log

• Look for queries with high Rows_examined values—these are scanning way more data than they need. Adding indexes to the tables used in these queries can drastically reduce memory and CPU usage.

d. Check for MySQL Bugs #

If you're running an older MySQL version (pre-5.7), there are known memory leak bugs. Consider upgrading to a stable LTS version (like 5.7 or 8.0) if you can—just make sure to back up your database first!

Sometimes Apache's child processes leak memory over time. Let's tweak its settings to mitigate this:

1. Edit your Apache config (usually /etc/httpd/conf/httpd.conf) and adjust these values:

   MaxRequestWorkers 150  # Adjust based on your RAM—150 is safe for 8GB of memory
   MaxRequestPerChild 1000  # Restarts each child process after 1000 requests to clear leaks
   

2. Restart Apache: systemctl restart httpd

After making changes, keep an eye on things to make sure memory stops creeping:

• Run vmstat 5 to watch memory and swap usage—if swap is being used heavily, your server might just need more RAM.
• Check MySQL's connection count periodically with mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Threads_connected';" to ensure it stays stable.

These steps should help you get that memory usage under control. Start with the connection timeout and buffer pool tweaks first—those are usually the quickest wins!

内容的提问来源于stack exchange，提问作者Amir Ebrahimi