VPS上MariaDB性能优化求助：内存不足与慢查询问题

Hey Tom, let’s work through your MariaDB performance and memory bottlenecks step by step. Your 6-core/16GB VPS should have no trouble handling a WordPress site with LiteSpeed+LS Cache, so the issues you’re seeing (near-zero free memory, massive slow query logs, disk temp tables) are almost certainly down to misconfigured database settings or inefficient queries. Here’s how to fix this:

First: Diagnose Where Your Memory Is Going #

Before tweaking settings, confirm what’s eating up your RAM:

• Fire up htop or top to check process memory usage. Is MariaDB hogging most of it, or are LiteSpeed/PHP-FPM processes taking too much?
• For CloudLinux, run lveinfo to verify your LVE container’s memory limits—if your host capped you below 16GB, that’ll directly restrict MariaDB’s available resources.

MariaDB Configuration Tweaks (my.cnf/my.ini) #

Adjust these core parameters to match your hardware. Remember to restart MariaDB after making changes:

1. Memory Allocation (Fix Low RAM + Reduce Disk Temp Tables) #

• innodb_buffer_pool_size: This is InnoDB’s most critical memory setting. Allocate 50-60% of your physical RAM (so 8G-9G for your 16GB VPS). This caches table data and indexes, cutting down disk IO drastically.
• tmp_table_size & max_heap_table_size: Set these to the same value (256M-512M works well here). Temp tables bigger than this will hit disk—raising these reduces unnecessary disk temp table creation.
• query_cache_size: For MariaDB 10.2+, set this to 0 and disable query_cache_type. Query cache hurts performance in high-concurrency setups, and LS Cache already handles page-level caching for WordPress.
• key_buffer_size: If you still have any MyISAM tables (uncommon in modern WP, but possible with old plugins), set this to 256M. Ignore if all tables are InnoDB.

2. CPU & Concurrency Optimization #

• max_connections: Set to 100-200 (150 is a safe starting point). Too many connections waste memory without benefit.
• innodb_thread_concurrency: Set to twice your CPU core count (12 for your 6-core VPS). This prevents InnoDB from overwhelming your CPU with too many concurrent threads.
• innodb_flush_log_at_trx_commit: If you don’t need strict ACID compliance, set this to 2 instead of the default 1. It boosts write performance while keeping data safe.

Fix Slow Queries & Inefficient Joins #

A 500MB slow query log in 12 hours is a red flag—let’s clean this up:

1. Analyze slow logs with mysqldumpslow:

   mysqldumpslow -s t /var/log/mysql/slow.log
   

   This shows you the longest-running, most frequent slow queries. Focus on joins and queries missing indexes.
2. Add missing indexes: WordPress’s wp_postmeta and wp_comments tables are common culprits. Use EXPLAIN on slow queries to check if the key column is NULL (meaning no index is used). For example, add a composite index for postmeta queries:

   CREATE INDEX idx_postmeta_key_value ON wp_postmeta(meta_key, meta_value(100));
   

3. Optimize join queries: Avoid unnecessary joins, split complex joins into simpler queries, or replace inefficient joins with subqueries. Always ensure join columns have indexes.
4. Clean up WordPress data: Delete spam comments, post revisions, and unused postmeta entries. Smaller tables mean faster queries.

WordPress & LiteSpeed Tuning #

Don’t forget the frontend layer—LS Cache can reduce database load drastically:

• Enable LS Cache’s Object Cache: Use Redis or Memcached to cache WordPress options, sessions, and object data in memory. This cuts down direct database requests.
• Tune PHP-FPM: In LiteSpeed’s PHP settings, set max_children to 20-30. Too many PHP processes will eat into your RAM. Aim for each PHP process to use ~256MB or less.
• Prune redundant plugins: Disable any plugins you don’t use, especially those that run frequent database queries (like outdated stats or SEO tools).

CloudLinux-Specific Adjustments #

• Check MySQL Governor: If enabled, make sure it’s not restricting MariaDB’s CPU/RAM usage. Let the database use the resources your VPS has available.
• Update PHP: Use cloudlinux-selector to switch to a modern, stable PHP version (8.1+). Newer PHP versions have better memory management and faster execution.

After making these changes, keep an eye on your monitoring tool to track memory usage, slow query counts, and disk IO. Tweak parameters as needed based on real-world performance.

内容的提问来源于stack exchange，提问作者Tom R