Ubuntu 16.04下PostgreSQL高磁盘I/O引发Django AJAX请求缓慢求助

Hey Jack, let's tackle this frustrating slow AJAX issue you're facing. Since you've already ruled out server and code problems, focusing on PostgreSQL and that high disk I/O is the right call. Here's a practical, step-by-step plan to diagnose and fix this:

Before jumping into config changes, let's make sure PostgreSQL is the one hammering the disk:

• Fire up iostat -x 5 in your terminal—this will show continuous disk stats every 5 seconds. Keep an eye on the %util column; anything over 80% means your disk is maxed out. Check if postgres is the top process using I/O.
• Use iotop -o to see real-time per-process I/O usage. This will directly tell you if PostgreSQL is the main culprit behind the high I/O.

PostgreSQL's default config is often too conservative for production. Adjust these key parameters in /etc/postgresql/9.5/main/postgresql.conf (remember to restart PostgreSQL after changes with sudo systemctl restart postgresql):

• Shared Buffers: This is PostgreSQL's main memory cache. On a dedicated DB server, set it to ~25% of your total RAM (e.g., 2GB if you have 8GB RAM). The default is usually way lower, forcing PostgreSQL to hit the disk more often.
• Checkpoint Controls: Frequent checkpoints cause sudden disk I/O spikes. Increase checkpoint_segments to 32 (this controls how much WAL data is written before a checkpoint) and set checkpoint_completion_target = 0.9—this spreads out the checkpoint I/O over time instead of dumping it all at once.
• Work Mem: If your AJAX queries involve sorts or joins, too little work_mem forces PostgreSQL to write temporary files to disk. Bump it up incrementally (try 16MB instead of the default 4MB) and monitor temp file usage with:

  SELECT temp_files, temp_bytes FROM pg_stat_database;
  

• WAL Buffers: Set wal_buffers = 16MB to reduce frequent small writes to the WAL (Write-Ahead Log) file.

Even if your code is identical to Windows, the data volume or index setup on your Ubuntu server might be different. Let's track down slow queries:

• Temporarily enable slow query logging by setting log_min_duration_statement = 100 in postgresql.conf (this logs any query taking over 100ms). Check the logs at /var/log/postgresql/postgresql-9.5-main.log to find the queries tied to your AJAX calls.
• For each slow query, run EXPLAIN ANALYZE in psql to see the execution plan. Look for Seq Scan (full table scan) instead of Index Scan—that means you're missing an index. Add the necessary indexes to speed up those queries.
• Update PostgreSQL's statistics with ANALYZE VERBOSE;—this makes sure the query planner has accurate data to create efficient plans (sometimes stats get stale on production servers).

High I/O could also be a sign of a slow or failing disk:

• Run smartctl -a /dev/sda (replace /dev/sda with your actual disk device) to check for SMART errors. This will tell you if the disk has bad sectors or is nearing failure.
• Make sure your filesystem is optimized. If you're using ext4 (the default on Ubuntu 16.04), add noatime to the mount options in /etc/fstab—this disables logging of file access times, which cuts down on unnecessary disk writes.
• If you're still using a spinning HDD, upgrading to an SSD will make a massive difference—PostgreSQL relies heavily on random reads/writes, which SSDs handle way faster than HDDs.

Even though you said code is fine, a small config difference could be causing issues:

• In your Django settings.py, make sure CONN_MAX_AGE is set to a reasonable value (like 60) to reuse database connections instead of creating a new one for every AJAX call—this reduces overhead.
• Ensure you're using the latest psycopg2 adapter. Run pip install --upgrade psycopg2-binary to update it; older versions might have I/O bottlenecks.

Walk through these steps one by one, starting with the I/O monitoring to confirm PostgreSQL is the issue. Most likely, it's a combination of conservative PostgreSQL settings and missing indexes, but checking the disk health is crucial too.

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