Website performance slower and increased CPU and Disk usage

November 17, 2014 1.8k views

I am using PostgreSQL 9.3 server on my production server. Everything had been working fine for the past couple of months. But since the past week or so, PostgreSQL seems to have becomes very slow with increased CPU and Disk usage.

Here is the graph of CPU, Disk and Bandwidth usage over the past month

Monthly CPU Usage
Monthly Disk Usage
Monthly Bandwidth Usage

I'm using memcached to cache most of the queries. Web pages with few basic queries are faster while pages with complex queries take a lot of time. The server response time for such pages has increased from 0.2 seconds to 4 seconds (measured using Google PageSpeed Insights).

Following is my postgresql.conf tuned using PgTune.

default_statistics_target = 50
maintenance_work_mem = 60MB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 704MB
work_mem = 6MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 240MB
max_connections = 80

Any ideas on what could have gone wrong? I'm actually still not sure if PostrgeSQL is actually the culprit. It could possible be Apache as well. I've optimized Apache by removing unused modules, tuned it to only have a small number of spare children running.

Here is my apache2.conf file

Timeout 30
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 5

<IfModule mpm_prefork_module>
    StartServers          1
    MinSpareServers       1
    MaxSpareServers       3
    MaxClients           10
    MaxRequestsPerChild 3000

<IfModule mpm_worker_module>
    StartServers          1
    MinSpareThreads       5
    MaxSpareThreads      15 
    ThreadLimit          25
    ThreadsPerChild       5
    MaxClients           25
    MaxRequestsPerChild 200

<IfModule mpm_event_module>
    StartServers          1
    MinSpareThreads       5
    MaxSpareThreads      15 
    ThreadLimit          25
    ThreadsPerChild       5
    MaxClients           25
    MaxRequestsPerChild 200

Here is my VirtualHost configuration

<VirtualHost *:80>

    RewriteEngine On
    RewriteCond %{HTTP_HOST}  ^ [nocase]
    RewriteRule ^(.*)$1 [last,redirect=301]


    WSGIDaemonProcess example python-path=/home/abc/example:/home/abc/example/env/lib/python2.7/site-packages
    WSGIProcessGroup example
    WSGIApplicationGroup %{GLOBAL}
    WSGIScriptAlias / /home/abc/example/

    DocumentRoot /home/abc/example

    <Directory />
        Require all granted

    Alias /static/ /home/abc/example/static/

    <Directory /home/abc/example/static>
        Order deny,allow
        Allow from all

    Alias /media/ /home/abc/example/media/

    <Directory /home/abc/example/media>
        Order deny,allow
        Allow from all

    ErrorLog ${APACHE_LOG_DIR}/error.log
    CustomLog ${APACHE_LOG_DIR}/access.log combined


Here is my .htaccess

<FilesMatch "\.(ico|svg|woff|eot|ttf)$">
Header set Cache-Control "max-age=31536000, public"

<FilesMatch "\.(jpg|png|gif|css|js|json)$">
Header set Cache-Control "max-age=604800, public"

<IfModule mod_mime.c>
    AddType application/javascript          js
    AddType application/   eot
    AddType application/x-font-ttf          ttf ttc
    AddType font/opentype                   otf
    AddType application/x-font-woff         woff
    AddType image/svg+xml                   svg svgz 
    AddEncoding gzip                        svgz

<IfModule mod_deflate.c>
    AddOutputFilterByType DEFLATE text/html text/plain text/css application/json
    AddOutputFilterByType DEFLATE application/javascript
    AddOutputFilterByType DEFLATE text/xml application/xml text/x-component
    AddOutputFilterByType DEFLATE application/xhtml+xml application/rss+xml application/atom+xml
    AddOutputFilterByType DEFLATE image/x-icon image/svg+xml application/ application/x-font-ttf font/opentype

I'm getting an average of 1500 visitors per day for the past 20 days, so I'm not sure what's causing an increased bandwidth usage in the past 7-10 days.

My website is a Django application hosted on droplet with the configuration - 1GB Ram, 30GB SSD Disk, Ubuntu 14.04 x64

Any suggestions are appreciated.

1 comment
  • have you installed anything new? do you have access logs? do you use something like phpMyAdmin? django login is captcha protected (not sure how it works never used django)?

    I ask because sometimes is some bot trying to bruteforce your admin/control panels.

Be the first one to answer this question.