Penguin install and configuration

[chris]

penguin.webarch.net is a new 2GB RAM virtual server which will replace NewLiveServer and DevelopmentServer for running all non-Drupal sites and is due to go live in early 2013. Drupal sites from the old servers will be migrated to PuffinServer.

This ticket has been created for tracking time and tasks done during the install.

See ​https://tech.transitionnetwork.org/trac/wiki/PenguinServer and also the corresponding PuffinServer.

[chris]

Steps done on the first configuration of puffin ticket:466#comment:2 were followed:

echo "export EDITOR=vim" >> /root/.bashrc
echo "syntax on" >> /root/.vimrc
echo "penguin.webarch.net" > /etc/hostname
aptitude install vim screen sudo wget lynx pwgen rsync postfix metche mutt apticron denyhosts

Generated a passphrase less root ssh key pair for rsyncing data off the quince and kiwi:

ssh-keygen -t rsa -b 2048

Added this public key to /root/.ssh/authorized_keys on kiwi and quince with this at the start of it to limit it's use to the penguin IP address:

from="81.95.52.111" ssh-rsa AAAA...

Created /root/.ssh/config with the following in it:

Host quince
  Hostname quince.transitionnetwork.org
  User root
Host kiwi
  Hostname kiwi.transitionnetwork.org
  User root

Created user accounts for all the existing people who need it, matching the user and group ID's to the ones on quince, and copied across their ssh public keys and sent them email with their passwords, using the following:

export NEWUSER=username
ssh quince grep $NEWUSER /etc/passwd # get the ID
export NEWUSERID=123
addgroup --gid $NEWUSERID $NEWUSER
adduser --gid $NEWUSERID --uid $NEWUSERID $NEWUSER
adduser $NEWUSER sudo
mkdir /home/$NEWUSER/.ssh
chmod 700 /home/$NEWUSER/.ssh
rsync -av quince:/home/$NEWUSER/.ssh/authorized_keys /home/$NEWUSER/.ssh/
chmod 600 /home/$NEWUSER/.ssh/authorized_keys 
chown -R $NEWUSER:$NEWUSER /home/$NEWUSER/.ssh

Edited /etc/sudoers:

#%sudo ALL=(ALL) ALL
%sudo ALL=(ALL) NOPASSWD: ALL

Edited /etc/ssh/sshd_config and restarted ssh:

PermitRootLogin no
AllowGroups sudo sshaccess

Copied across the wild card ssl cert for *.transitionnetwork.org:

mkdir /etc/ssl/transitionnetwork.org/
chmod 700 /etc/ssl/transitionnetwork.org/
rsync -av quince:/etc/ssl/transitionnetwork.org/ /etc/ssl/transitionnetwork.org/

Set up root email:

echo "root: chris@webarchitects.co.uk" >> /etc/aliases
newaliases
/etc/init.d/postfix reload

Edited the TLS settings in /etc/postfix/main.cf and restarted postfix:

# TLS parameters
smtpd_tls_cert_file = /etc/ssl/transitionnetwork.org/transitionnetwork.org.crt
smtpd_tls_key_file = /etc/ssl/transitionnetwork.org/transitionnetwork.org.key
smtpd_tls_CAfile = /etc/ssl/transitionnetwork.org/gandi.pem
smtpd_use_tls=yes
smtpd_tls_auth_only = yes
smtpd_tls_loglevel = 1
smtpd_tls_received_header = yes
smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache
smtpd_tls_session_cache_timeout = 3600s
smtpd_tls_cipherlist = EDH:!EXP:!LOW
smtpd_tls_ask_ccert = yes
smtpd_tls_req_ccert = no

smtp_use_tls = yes
smtp_tls_note_starttls_offer = yes
smtp_enforce_tls = no
smtp_tls_cert_file = /etc/ssl/transitionnetwork.org/transitionnetwork.org.crt
smtp_tls_key_file = /etc/ssl/transitionnetwork.org/transitionnetwork.org.key
smtp_tls_CAfile = /etc/ssl/transitionnetwork.org/gandi.pem
smtp_tls_loglevel = 1
smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache
smtp_tls_session_cache_timeout = 3600s

Sent a test email to root using mutt and then checked that TLS was used by looking at the mail headers.

Installed mysql and added the root password to /root/.my.cnf:

aptitude install mysql-server-5.1

Installed backupninja and created a user on the remote backup server and set up ssh:

aptitude install backupninja hwinfo libhal1 libhd16 debconf-utils
adduser penguin
mkdir /home/penguin/.ssh
vi /home/penguin/.ssh/authorized_keys
chmod 600 /home/penguin/.ssh/authorized_keys
chmod 700 /home/penguin/.ssh
chown -R penguin:penguin /home/penguin/.ssh

Ran ninjahelper and set up system, mysql and rdiff backups and tested that it was all working.

[chris]

Installing nginx, trac and munin, attempting to use the debian versions from backports, following ​http://backports-master.debian.org/Instructions/

echo "deb http://backports.debian.org/debian-backports squeeze-backports main" > /etc/apt/sources.list.d/backports.list
aptitude -t squeeze-backports install "nginx"
aptitude -t squeeze-backports install "trac"
aptitude -t squeeze-backports install "munin"

And following ​http://forums.debian.net/viewtopic.php?f=17&t=80679#p438915

echo 'APT::Default-Release "stable";' >> /etc/apt/apt.conf 

Mediwiki is in testing but not backports, so I'm not sure if we should use the debian package or not.

For nginx ticket:357 was read, and rather than using the dotdeb repos the wheezy ​http://packages.debian.org/wheezy/php5-fpm was considered as dotdeb did cause some problems on kiwi with mysql upgrades. Using dotdeb would also result in their nginx rather than the backports one, so the suggested way of doing it from here seems like the best bet: ​http://serverfault.com/questions/448246/php-fpm-for-nginx-on-debian

[chris]

These are the packages installed from backports:

dpkg -l  | awk '/^ii/ && $3 ~ /bpo[456]0/ {print $2}'
gawk
geoip-database
libcairo2
libfreetype6
libgeoip1
liblog-dispatch-perl
libnet-server-perl
libpixman-1-0
liburi-perl
libxfont1
munin
munin-common
munin-doc
munin-node
munin-plugins-core
munin-plugins-extra
nginx
nginx-common
nginx-full
python-babel
trac
x11-common

Tidying up the apt changes so that everything is in a .d directory:

cd /etc/apt
mv apt.conf apt.conf.d/02stable

Created /etc/apt/preferences.d/security.pref with the following:

Pin: release l=Debian-Security
Pin-Priority: 1000
Package: *

Created /etc/apt/preferences.d/stable.pref with the following:

Pin: release a=stable
Pin-Priority: 990
Package: *

Created /etc/apt/preferences.d/dotdeb.pref:

Package: *
Pin: release o=packages.dotdeb.org
Pin-Priority: 100

Package: php-pear php5-cli php5-common php5-fpm php5-mysql
Pin: release o=packages.dotdeb.org
Pin-Priority: 991

Created /etc/apt/preferences.d/backports.pref:

Package: gawk geoip-database libcairo2 libfreetype6 libgeoip1 liblog-dispatch-perl libnet-server-perl libpixman-1-0 liburi-perl libxfont1 munin munin-common munin-doc munin-node munin-plugins-core munin-plugins-extra nginx nginx-common nginx-full python-babel trac x11-common
Pin: release o=backports
Pin-Priority: 992

Then create /etc/apt/sources.list.d/dotdeb.list with the following in it:

deb http://packages.dotdeb.org squeeze all
deb-src http://packages.dotdeb.org squeeze all

Install the gpg key:

cd
wget http://www.dotdeb.org/dotdeb.gpg
cat dotdeb.gpg | sudo apt-key add -

Update:

apt-get update
apt-get install -t stable php-pear php5-cli php5-common php5-fpm php5-mysql

However this isn't working right as an upgrade would now result in mysql coming from dotdeb:

aptitude upgrade
  The following packages will be upgraded: 
    mysql-common 
apt-show-versions -u 
  mysql-common/squeeze upgradeable from 5.1.66-0+squeeze1 to 5.5.28-1~dotdeb.0

This was fixed by editing /etc/apt/preferences.d/stable.pref and changing "stable" to "squeeze":

Package: *
Pin: release a=squeeze
Pin-Priority: 990

Package: mysql-common
Pin: release a=squeeze
Pin-Priority: 995

Mysql packages are installed:

apt-show-versions | grep mysql
libdbd-mysql-perl/squeeze uptodate 4.016-1
libmysqlclient16/squeeze uptodate 5.1.66-0+squeeze1
mysql-client-5.1/squeeze uptodate 5.1.66-0+squeeze1
mysql-common/squeeze uptodate 5.1.66-0+squeeze1
mysql-server-5.1/squeeze uptodate 5.1.66-0+squeeze1
mysql-server-core-5.1/squeeze uptodate 5.1.66-0+squeeze1
php5-mysql/squeeze uptodate 5.3.19-1~dotdeb.0

[chris]

A test install of media wiki from wheezy was done by enabling wheezy repos by creating /etc/apt/sources.list.d/wheezy.list with the following in it:

deb http://ftp.uk.debian.org/debian/ wheezy main
deb-src http://ftp.uk.debian.org/debian/ wheezy main

And /etc/apt/preferences.d/wheezy.pref with this in it:

Package: *
Pin: release a=wheezy
Pin-Priority: 99

Package: mediawiki
Pin: release a=wheezy
Pin-Priority: 993

Then:

aptitude install -t wheezy mediawiki

But this pulls in mediawiki from squeeze and not wheezy, so I think the apt settings have a problem, this is one for another night!

[chris]

At the meeting on 17th Dec, ticket:471, Jim suggested we use ​http://collectd.org/ rather than munin

[chris]

I have installed a couple scripts that I wrote for other servers to update packages and update the /root/Changelog file, see AptitudeUpdateScript

This has shown up a potential issue, apt-show-versions -b -u lists some available updates:

geoip-database/wheezy
libfreetype6/squeeze-backports
nginx/squeeze-backports
nginx-common/squeeze-backports
nginx-full/squeeze-backports

But these don't get updated with apt-get upgrade or aptitude upgrade or aptitude full-upgrade as they are only manually upgradeable:

apt-show-versions -u 
geoip-database/wheezy *manually* upgradeable from 20121207-1~bpo60+1 to 20130108-1~bpo60+1
libfreetype6/squeeze-backports *manually* upgradeable from 2.4.9-1~bpo60+1 to 2.4.9-1.1
nginx/squeeze-backports *manually* upgradeable from 1.2.1-2~bpo60+1 to 1.2.6-1~dotdeb.0
nginx-common/squeeze-backports *manually* upgradeable from 1.2.1-2~bpo60+1 to 1.2.6-1~dotdeb.0
nginx-full/squeeze-backports *manually* upgradeable from 1.2.1-2~bpo60+1 to 1.2.6-1~dotdeb.0

I don't think we don't want to switch from the squeeze-backports version of nginx to the dotdeb version so those updates can be ignored, for the geoip update:

dpkg -r geoip-database
apt-get install geoip-database=20130108-1~bpo60+1

For libfreetype6 there doesn't seem to be a problem changing back to the squeeze version, so this was done:

dpkg -r --force-depends libfreetype6
aptitude install multiarch-support libfreetype6

The ​notes here were helpful.

Notes on this have been added to PenguinServer#Updates

[chris]

I have made a start on migrating the static archives:

• ​http://2010.archive.transitionnetwork.org/
• ​http://2011.archive.transitionnetwork.org/

Copy the files:

mkdir -p /web/2011.archive.transitionnetwork.org/www
rsync -av kiwi:/web/2011.archive.transitionnetwork.org/www/ /web/2011.archive.transitionnetwork.org/www/

mkdir -p /web/2010.archive.transitionnetwork.org/www
rsync -av kiwi:/web/transitiontowns.org.webarch.net/www/ /web/2010.archive.transitionnetwork.org/www/

Copy nginx config across:

cd /etc/nginx/sites-available
rsync -av kiwi:/etc/nginx/sites-available/archive .
cd /etc/nginx
rsync -av kiwi:/etc/nginx/archive-shared .

These files contain, archive:

# virtual server
# http://nginx.org/en/docs/http/ngx_http_core_module.html#server
server {

        # listen for ipv4
        # http://nginx.org/en/docs/http/ngx_http_core_module.html#listen
        #listen   8000; 
        listen   80;

        # server name and server aliases        
        # http://nginx.org/en/docs/http/ngx_http_core_module.html#server_name 
        server_name  2011.archive.transitionnetwork.org;

        # logs, error log levels: info | notice | warn | error | crit | alert 
        # http://nginx.org/en/docs/http/ngx_http_log_module.html#access_log
        # http://nginx.org/en/docs/ngx_core_module.html#error_log
        access_log  /var/log/nginx/archive.access.log;
        error_log   /var/log/nginx/archive.error.log debug;

        # document root
        # http://nginx.org/en/docs/http/ngx_http_core_module.html#root
        root   "/web/2011.archive.transitionnetwork.org/www";

        # include the config that both archive sites share 
        include  archive-shared;

}

server {

        # listen for ipv4
        # http://nginx.org/en/docs/http/ngx_http_core_module.html#listen
        #listen   8000; 
        listen   80;

        # server name and server aliases        
        # http://nginx.org/en/docs/http/ngx_http_core_module.html#server_name 
        server_name  2010.archive.transitionnetwork.org;

        # logs, error log levels: info | notice | warn | error | crit | alert 
        # http://nginx.org/en/docs/http/ngx_http_log_module.html#access_log
        # http://nginx.org/en/docs/ngx_core_module.html#error_log
        access_log  /var/log/nginx/archive.access.log;
        error_log   /var/log/nginx/archive.error.log crit;

        # document root
        # http://nginx.org/en/docs/http/ngx_http_core_module.html#root
        root   "/web/2010.archive.transitionnetwork.org/www";

        # include the config that both archive sites share 
        include  archive-shared;

}

archive-shared:

        # Shared archive config

        # http://nginx.org/en/docs/http/ngx_http_autoindex_module.html#autoindex
        autoindex  on;

        # document index
        # http://nginx.org/en/docs/http/ngx_http_index_module.html#index
        index  archive.html index.php pmwiki.php;

        # location match
        # http://nginx.org/en/docs/http/ngx_http_core_module.html#location

        # Prevent access to any files starting with a dot, like .htaccess
        # or text editor temp files
        location ~ /\. {
                access_log off;
                log_not_found off;
                deny all;
        }
        # Prevent access to tmp files created by vim
        location ~ .\~$ {
                return 403;
        }

        # /uploads is the upload directory where users can upload files witrh extensions
        # listed in LocalSettings.php so we want to make sure that html / php documents
        # are served as plain text
        location ^~ /uploads/ {
                default_type application/octet-stream;
                types {
                        text/plain              htm html shtml php php5;
                        # whitelist here of all file types allowed to be uploaded,
                        image/gif                                                                       gif;
                        image/jpeg                                                                      jpeg jpg jpe;
                        image/png                                                                       png;
                        text/plain                                                                      txt;
                        application/msword                                                              doc dot;
                        application/rtf                                                                 rtf;
                        application/pdf                                                                 pdf;
                        application/vnd.oasis.opendocument.text                                         odt;
                        application/vnd.ms-excel                                                        xls xlb xlt;
                        application/vnd.ms-powerpoint                                                   ppt pps;
                }
        }

        # deny access to these directories and files
        location ^~ /webprojectblog/            { return 403; }
        location ^~ /scripts/                   { return 403; }
        location ^~ /cookbook/                  { return 403; }
        location ^~ /local/                     { return 403; }
        location ^~ /forum/bb-admin/            { return 403; }
        #location ^~ /forum/bb-templates/       { return 403; }
        location ^~ /forum/my-plugins/          { return 403; }
        location = /forum/bb-config.php         { return 403; }
        location = /forum/bb-config-sample.php  { return 403; }
        location = /forum/bb-cron.php           { return 403; }
        location = /forum/bb-edit.php           { return 403; }
        location = /forum/bb-login.php          { return 403; }
        location = /forum/bb-post.php           { return 403; }
        location = /forum/bb-reset-password.php { return 403; }
        location = /forum/bb-settings.php       { return 403; }
        location = /forum/edit.php              { return 403; }
        location = /forum/register.php          { return 403; }

        # no external access needed
        #location ^~ /forum/bb-includes/        { internal; }

        # Converted apache rules
        # via http://www.anilcetin.com/convert-apache-htaccess-to-nginx/
        location ~ /forum {
                rewrite ^/forum/page/([0-9]+)/?$ /forum/index.php?page=$1 last;
                rewrite ^/forum/forum/([^/]+)/page/([0-9]+)/?$ /forum/forum.php?id=$1&page=$2 last;
                rewrite ^/forum/forum/([^/]+)/?$ /forum/forum.php?id=$1 last;
                rewrite ^/forum/forum/?$ /forum/ redirect;
                rewrite ^/forum/topic/([^/]+)/page/([0-9]+)/?$ /forum/topic.php?id=$1&page=$2 last;
                rewrite ^/forum/topic/([^/]+)/?$ /forum/topic.php?id=$1 last;
                rewrite ^/forum/topic/?$ /forum/ redirect;
                rewrite ^/forum/tags/([^/]+)/page/([0-9]+)/?$ /forum/tags.php?tag=$1&page=$2 last;
                rewrite ^/forum/tags/([^/]+)?$ /forum/tags.php?tag=$1 last;
                rewrite ^/forum/profile/([^/]+)/page/([0-9]+)/?$ /forum/profile.php?id=$1&page=$2 last;
                rewrite ^/forum/profile/([^/]+)/([^/]+)/?$ /forum/profile.php?id=$1&tab=$2 last;
                rewrite ^/forum/profile/([^/]+)/([^/]+)/page/([0-9]+)/?$ /forum/profile.php?id=$1&tab=$2&page=$3 last;
                rewrite ^/forum/profile/([^/]+)/?$ /forum/profile.php?id=$1 last;
                rewrite ^/forum/profile/?$ /forum/profile.php last;
                rewrite ^/forum/view/([^/]+)/page/([0-9]+)/?$ /forum/view.php?view=$1&page=$2 last;
                rewrite ^/forum/view/([^/]+)/?$ /forum/view.php?view=$1 last;
                rewrite ^/forum/rss/topic/([^/]+)?$ /forum/rss.php?topics=$1 last;
                rewrite ^/forum/rss/tags/([^/]+)/?$ /forum/rss.php?tag=$1 last;
                rewrite ^/forum/rss/tags/([^/]+)/topics/?$ /forum/rss.php?tag=$1&topics=1 last;
                rewrite ^/forum/rss/profile/([^/]+)/?$ /forum/rss.php?profile=$1 last;
                rewrite ^/forum/rss/view/([^/]+)/?$ /forum/rss.php?view=$1 last;
                rewrite ^/forum/rss/?$ /forum/rss.php last;
                #rewrite ^/forum/.*$ /forum/index.php last;
                # Pass php requests to php5-fpm
                location ~ \.php?$ {
                        try_files $uri =404;
                        include fastcgi_params;
                        fastcgi_pass unix:/var/run/php5-fpm/phpfpm.sock;
                        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
                        fastcgi_intercept_errors on;
                }
        }

        # Prevent editing and uploading
        # http://wiki.nginx.org/IfIsEvil - but not sure how else to do this
        if ($args ~ "^(.*)action=edit"){
               return 403;
        }
        if ($args ~ "^(.*)action=upload"){
               return 403;
        }

        location /Totnes {
                rewrite ^/Totnes/([^/a-z].*) /Totnes/index.php?n=$1;
        }
        location /Kinsale {
                rewrite ^/Kinsale/([^/a-z].*) /Kinsale/index.php?n=$1;
        }
        location /Skibb {
                rewrite ^/Skibb/([^/a-z].*) /Skibb/index.php?n=$1;
        }

        location / {
                try_files $uri $uri/ @rewrite;
        }

        location @rewrite {
                rewrite ^/([A-Z0-9xa0-xff].*)$ /pmwiki.php?n=$1;
        }

        # Pass php requests to php5-fpm
        location ~ \.php?$ {
                try_files $uri =404;
                include fastcgi_params;
                fastcgi_pass unix:/var/run/php5-fpm/phpfpm.sock;
                fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
                fastcgi_intercept_errors on;
        }

Copy the database across:

cd /tmp/
rsync -av kiwi:/var/backups/mysql/sqldump/bbforum.sql .

Create the database and user:

mysql
mysql> create database bbforum;
mysql> grant all on bbforum.* to 'bbforum'@'bbforum' identified by 'XXX';
mysql> flush privileges;

Insert the database:

cat /tmp/bbforum.sql | mysql bbforum

[chris]

The work last week resulted in static HTML working:

• ​http://2011.archive.penguin.webarch.net/
• ​http://2010.archive.penguin.webarch.net/

But not PHP:

• ​http://2010.archive.penguin.webarch.net/index.php
• ​http://2011.archive.penguin.webarch.net/forum/

This is because the socket specified in /etc/nginx/archive-shared:

fastcgi_pass unix:/var/run/php5-fpm/phpfpm.sock;

Doesn't exist, so following ticket:357#comment:16

mkdir /var/run/php5-fpm
chown www-data:www-data /var/run/php5-fpm

Change these things in /etc/php5/fpm/pool.d/www.conf:

listen = /var/run/php5-fpm/phpfpm.sock
listen.owner = www-data
listen.group = www-data
listen.mode = 0666
listen.allowed_clients = 127.0.0.1,81.95.52.111,penguin.transitionnetwork.org,penguin.webarch.net

And restart apps:

/etc/init.d/php5-fpm restart
/etc/init.d/nginx restart

Now PHP is working:

• ​http://2010.archive.penguin.webarch.net/Main/HomePage

But MySQL is needed for the old forum:

• ​http://2011.archive.penguin.webarch.net/forum/

And that wasn't working because, although the database had been imported a user hadn't been created correctly see ticket:470#comment:7 so this was done to fix it:

mysql mysql
mysql> DROP USER 'bbforum'@'bbforum';
mysql> CREATE USER 'bbforum'@'localhost' IDENTIFIED BY 'xxx';
mysql> GRANT ALL ON bbforum.* TO 'bbforum'@'localhost';
mysql> FLUSH PRIVILEGES;

The Gandi DNS has been updated, we did have a *.archive entry but there isn't really a need for a wild card entry so it's been removed and replaced with:

2010.archive 3600 IN A 81.95.52.111
2011.archive 3600 IN A 81.95.52.111

And these site should soon be running on penguin:

• ​http://2010.archive.transitionnetwork.org/
• ​http://2011.archive.transitionnetwork.org/

[chris]

So, there are some issues with the archive, specifically images are not working on the 2010 site:

​http://2010.archive.transitionnetwork.org/index.php

But they are working here:

​http://2011.archive.transitionnetwork.org/index.php

I'm at a loss for the reason for this and having spent an hour on it I think I should move on, so I have created a seperate ticket for this, ticket:474

[chris]

Setting up Mediawiki, copy the nginx config from kiwi:

cd /etc/nginx/
scp kiwi:/etc/nginx/wiki-shared .
cd /etc/nginx/sites-available/
scp kiwi:/etc/nginx/sites-available/wiki .

The /etc/nginx/wiki-shared file contains:

        # Everything below is the same for http and https

        # server name and server aliases        
        # http://nginx.org/en/docs/http/ngx_http_core_module.html#server_name 
        server_name  wiki.transitionnetwork.org wiki.penguin.webarch.net;

        # document root
        # http://nginx.org/en/docs/http/ngx_http_core_module.html#root
        root   "/web/wiki.transitionnetwork.org/www";

        # document index
        # http://nginx.org/en/docs/http/ngx_http_index_module.html#index
        index  index.php;
        # http://nginx.org/en/docs/http/ngx_http_autoindex_module.html#autoindex
        autoindex off;

        # location match
        # http://nginx.org/en/docs/http/ngx_http_core_module.html#location

        # Prevent access to any files starting with a dot, like .htaccess
        # or text editor temp files
        location ~ /\. {
                return 403;
        }
        # Prevent access to tmp files created by vim
        location ~ .\~$ {
                return 403;
        }

        # Block access to these directories and files 
        location =  /LocalSettings.php  { return 403; }
        location ^~ /maintenance/       { return 403; }
        location ^~ /bin/               { return 403; }
        location ^~ /mw-config/         { return 403; }
        location ^~ /cache/             { return 403; }
        location ^~ /tests/             { return 403; }
        location ^~ /includes/          { return 403; }
        location ^~ /maths/             { return 403; }
        location ^~ /serialized/        { return 403; }
        location ^~ /languages/         { return 403; }

        # /images is the upload directory where users can upload files witrh extensions
        # listed in LocalSettings.php so we want to make sure that html / php documents
        # are served as plain text 
        location ^~ /images/ {
                default_type text/plain;
                types {
                        text/plain              htm html shtml php php5;
                        # whitelist here of all file types allowed to be uploaded, 
                        image/gif                                                                       gif;
                        image/jpeg                                                                      jpeg jpg jpe;
                        image/png                                                                       png;
                        text/plain                                                                      txt;
                        video/x-msvideo                                                                 avi;
                        audio/mpeg                                                                      mpga mpega mp2 mp3 m4a;
                        audio/x-pn-realaudio                                                            ra rm ram;
                        video/mp4                                                                       mp4;
                        image/svg+xml                                                                   svg svgz;
                        application/pdf                                                                 pdf;
                        application/rtf                                                                 rtf;
                        application/msword                                                              doc dot;
                        application/vnd.ms-powerpoint                                                   ppt pps;
                        application/vnd.oasis.opendocument.chart                                        odc;
                        application/vnd.oasis.opendocument.database                                     odb;
                        application/vnd.oasis.opendocument.formula                                      odf;
                        application/vnd.oasis.opendocument.graphics                                     odg;
                        application/vnd.oasis.opendocument.graphics-template                            otg;
                        application/vnd.oasis.opendocument.image                                        odi;
                        application/vnd.oasis.opendocument.presentation                                 odp;
                        application/vnd.oasis.opendocument.presentation-template                        otp;
                        application/vnd.oasis.opendocument.spreadsheet                                  ods;
                        application/vnd.oasis.opendocument.spreadsheet-template                         ots;
                        application/vnd.oasis.opendocument.text                                         odt;
                        application/vnd.oasis.opendocument.text-master                                  odm;
                        application/vnd.oasis.opendocument.text-template                                ott;
                        application/vnd.oasis.opendocument.text-web                                     oth;
                        application/vnd.openxmlformats-officedocument.spreadsheetml.sheet               xlsx;
                        application/vnd.openxmlformats-officedocument.spreadsheetml.template            xltx;
                        application/vnd.openxmlformats-officedocument.presentationml.presentation       pptx;
                        application/vnd.openxmlformats-officedocument.presentationml.slideshow          ppsx;
                        application/vnd.openxmlformats-officedocument.presentationml.template           potx;
                        application/vnd.openxmlformats-officedocument.wordprocessingml.document         docx;
                        application/vnd.openxmlformats-officedocument.wordprocessingml.template         dotx;
                        image/x-photoshop                                                               psd;
                        application/postscript                                                          ps ai eps epsi epsf eps2 eps3;
                        image/tiff                                                                      tiff tif;
                        image/x-icon                                                                    ico;
                        application/vnd.ms-excel                                                        xls xlb xlt;
                }
                # If the files can't be found serve up the front page
                try_files $uri /index.php;
        }

        # This is the main rewrite to get nice URL's
        # http://wiki.nginx.org/MediaWiki
        location / {
                try_files $uri @rewrite;
        }
        location @rewrite {
                # http://blog.bigdinosaur.org/mediawiki-on-nginx/
                rewrite ^/([^?]*)(?:\?(.*))? /index.php?title=$1&$2 last;
        }

And the /etc/nginx/sites-available/wiki contains:

 virtual server
# http://nginx.org/en/docs/http/ngx_http_core_module.html#server
# HTTP Server
server {

        # listen for ipv4
        # http://nginx.org/en/docs/http/ngx_http_core_module.html#listen
        #listen   8000; 
        listen   80;

        # logs, error log levels: info | notice | warn | error | crit | alert 
        # http://nginx.org/en/docs/http/ngx_http_log_module.html#access_log
        # http://nginx.org/en/docs/ngx_core_module.html#error_log
        access_log  /var/log/nginx/wiki.access.log;
        error_log   /var/log/nginx/wiki.error.log   crit;

        # Redirect logins to https
        location ~ .*Special:UserLogin.* {
                #rewrite ^/Special:UserLogin(.*) https://$server_name:4430/Special:UserLogin$1? redirect;
                rewrite ^/Special:UserLogin(.*) https://$server_name/Special:UserLogin$1? redirect;
        }
        if ($args ~ "^(.*)title=Special:UserLogin"){
                #rewrite ^/(.*) https://$server_name:4430/index.php?$args? redirect;
                rewrite ^/(.*) https://$server_name/index.php?$args? redirect;
        }

        include  wiki-shared;

        # Pass php requests to php5-fpm
        location ~ /(api|index|opensearch_desc|redirect|trackback|img_auth|load|thumb)\.php?$ {
                try_files $uri =404;
                include fastcgi_params;
                fastcgi_pass unix:/var/run/php5-fpm/phpfpm.sock;
                fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
                fastcgi_intercept_errors on;
        }
}

# HTTPS Server
server {
        #listen   4430;
        listen   443;
        access_log  /var/log/nginx/wiki.ssl_access.log;
        error_log   /var/log/nginx/wiki.ssl_error.log crit;

        ssl  on;
        ssl_certificate  /etc/ssl/transitionnetwork.org/transitionnetwork.org.chained.pem;
        ssl_certificate_key  /etc/ssl/transitionnetwork.org/transitionnetwork.org.key;
        ssl_protocols  SSLv3 TLSv1 TLSv1.1 TLSv1.2;
        ssl_ciphers  RC4-SHA:HIGH:!ADH:!SSLv2:!aNULL;
        ssl_prefer_server_ciphers   on;

        include  wiki-shared;

        # Pass php requests to php5-fpm
        location ~ /(api|index|opensearch_desc|redirect|trackback|img_auth|load|thumb)\.php?$ {
                try_files $uri =404;
                include fastcgi_params;
                fastcgi_param HTTPS on;
                fastcgi_pass unix:/var/run/php5-fpm/phpfpm.sock;
                fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
                fastcgi_intercept_errors on;
        }

}

Edited these files and changed wiki.dev to wiki.

Copy the live wiki off quince, and delete the things we don't need like old versions of mediawiki:

mkdir /web/wiki.transitionnetwork.org/
rsync -av quince:/web/wiki.transitionnetwork.org/ /web/wiki.transitionnetwork.org/
cd /web/wiki.transitionnetwork.org/
rm -rf mediawiki-1*
rm -rf www/extensions/FCKeditor/

The last change on the wiki, ​https://wiki.transitionnetwork.org/Special:RecentChanges was Jim yesterday, so it's safe to use last nights backup:

cd /tmp/
rsync -av quince:/var/backups/mysql/sqldump/transwiki.sql .
mysql mysql
 mysql> CREATE DATABASE transwiki;
 mysql> GRANT ALL ON transwiki.* TO 'mediawiki'@'localhost' IDENTIFIED BY 'xxxx';
 mysql> FLUSH PRIVILEGES;
 mysql> QUIT;
cat transwiki.sql | mysql transwiki
cd /etc/nginx/sites-enabled
ln -s ../sites-available/wiki 10-wiki
cd /etc/ssl/transitionnetwork.org
scp kiwi:/etc/ssl/transitionnetwork.org/transitionnetwork.org.chained.pem .
/etc/init.d/nginx restart

The nginx transitionnetwork.org.chained.pem was created like this:

cat transitionnetwork.org.crt > transitionnetwork.org.chained.pem
cat GandiStandardSSLCA.pem >> transitionnetwork.org.chained.pem
cat UTNAddTrustServer_CA.pem >> transitionnetwork.org.chained.pem
cat AddTrustExternalCARoot.pem >> transitionnetwork.org.chained.pem

Following a nginx restart the following error is displayed at ​http://wiki.penguin.webarch.net/

CACHE_ACCEL requested but no suitable object cache is present. You may want to install APC.

So:

aptitude install php5-apc

That didn't fix it, and it turns out that an old DB dump had been imported, so importing a new one:

cd /tmp/
scp quince:/var/backups/mysql/bak.mysql.2013-01-21_02.00.16_.tar.gz .
tar -zxvf bak.mysql.2013-01-21_02.00.16_.tar.gz
cd bak.mysql.2013-01-21_02.00.16/
cat *transwiki* | mysql transwiki

Also as APC isn't working these settings were commented out in /web/wiki.transitionnetwork.org/www/LocalSettings.php to get the site working:

#$wgMainCacheType = CACHE_ACCEL;
#$wgMemCachedServers = array();

The wiki is now running on the new server but there appears to be a problem with cookies and authentication:

• ​https://wiki.penguin.webarch.net/Main_Page

So I haven't updated the DNS.

[chris]

Upgrading mediawiki:

cd /web/wiki.transitionnetwork.org/
wget http://download.wikimedia.org/mediawiki/1.19/mediawiki-1.19.3.tar.gz
wget http://download.wikimedia.org/mediawiki/1.19/mediawiki-1.19.3.tar.gz.sig
wget https://www.mediawiki.org/keys/keys.txt
gpg --import keys.txt
gpg --verify mediawiki-1.19.3.tar.gz.sig
tar -zxvf mediawiki-1.19.3.tar.gz
rsync -av mediawiki-1.19.3/ www/
cd www/maintenance/
php update.php 
cd ..
chown root:root -R www/
cd www
chown -R www-data:www-data cache
chown -R www-data:www-data images

The cookie problem was fixed by editing LocalSettings.php:

#$wgCookieDomain     =  "wiki.transitionnetwork.org";
$wgCookieDomain     =  "wiki.penguin.webarch.net";

So now it's OK to switch the DNS -- please don't edit ​https://wiki.transitionnetwork.org/ for the next 30 mins or so!

[chris]

OK, DNS for wiki.transitionnetwork.org has been updated, you will know you have the site off penguin when this page:

​https://wiki.transitionnetwork.org/User:Chris

Matches this page:

​https://wiki.penguin.webarch.net/User:Chris

The documentation still needs updating, wiki:PenguinServer#Websites

[chris]

I've added some notes about Mediawiki, wiki:PenguinServer#wiki.transitionnetwork.org

Installing Piwik, copy the niginx config across and the files and delete some things we don't need:

cd /etc/niginx/sites-available
scp kiwi:/etc/nginx/sites-available/stats . 
mkdir /web/stats.transitionnetwork.org
cd /web/
rsync -av quince:/web/stats.transitionnetwork.org/ stats.transitionnetwork.org/
cd stats.transitionnetwork.org
rm -rf piwik.1*
rm -rf piwik.bak

Copy the database and install it, on quince, backup the databases, then copy across:

ssh quince /usr/local/bin/mysql_backup
cd /tmp/
scp quince:/var/backups/mysql/bak.mysql.2013-01-21_19.41.42_.tar.gz .
tar -zxvf bak.mysql.2013-01-21_19.41.42_.tar.gz 
cd bak.mysql.2013-01-21_19.41.42/
cat *_stats* | mysql stats

Create a Mysql user and database:

mysql mysql
  mysql> CREATE DATABASE stats;
  mysql> GRANT ALL ON stats.* TO 'stats'@'localhost' IDENTIFIED BY 'xxxx';
  mysql> FLUSH PRIVILEGES;
  mysql> QUIT;

Nginx:

cd /etc/nginx
scp kiwi:/etc/nginx/fastcgi_cache_zone.conf .
mkdir -p /var/cache/nginx/fcgicache
chown -R www-data:www-data /var/cache/nginx/fcgicache
mv nginx.conf nginx.conf.2013-01021.bak
scp kiwi:/etc/nginx/nginx.conf .
scp kiwi:/etc/nginx/map_cache_piwik.conf .
/etc/init.d/nginx restart 

Upgrade Piwik, see ​http://piwik.org/docs/update/

cd /web/stats.transitionnetwork.org
cp piwik/config/config.ini.php .
wget http://builds.piwik.org/latest.zip
unzip latest.zip
cp config.ini.php piwik/config/
cd piwik/
php index.php -- "module=CoreUpdater"

All seems to be working OK at ​https://stats.penguin.webarch.net/ so the DNS has been updated and penguin should soon be serving ​https://stats.transitionnetwork.org/

(This comment includes the time spent in a ttech skype meeting -- I was doing the migration during the meeting.)

[chris]

Pwiki is now running on penguin, and it's tracking activity today, but there was no recorded traffic for yesterday, I think because the cron tab wasn't set up, so the crontab was created for www-data:

crontab -e -u www-data

Containing:

# m h  dom mon dow   command
5 0 * * * /web/stats.transitionnetwork.org/piwik/misc/cron/archive.sh > /dev/null

And the script was run on the command line.

[chris]

We now have stats for yesterday in Piwik and I have added stats.transitionnetwork.org to the wiki page, wiki:PenguinServer#stats.transitionnetwork.org and updated the Piwik page, wiki:PiwikServer

I have writtten a penguin TODO list, wiki:PenguinServer#Penguin which has these tasks listed:

• Sort out backups
• Migrate trac
• Migrate the static archive
• Check the mysql and php settings
• Sort out munin (or collect)
• Sort out the apt pinning - there are still some problems with this

[chris]

Backups have been setup using backupninja, the config is in /etc/backupninja.conf and it has the default settings and task config files are in /etc/backup.d/, /etc/backup.d/20.mysql contains:

hotcopy = no
sqldump = yes
compress = no
backupdir = /var/backups/mysql
databases = all
configfile = /etc/mysql/debian.cnf

And /etc/backup.d/90.rdiff contains:

[source]
type = local
keep = 60D

include = /var/spool/cron/crontabs
include = /var/backups
include = /etc
include = /root
include = /home
include = /usr/local/*bin
include = /var/lib/dpkg/status*
include = /web
exclude = /home/*/.gnupg
exclude = /home/*/.local/share/Trash
exclude = /home/*/.Trash
exclude = /home/*/.thumbnails
exclude = /home/*/.beagle
exclude = /home/*/.aMule
exclude = /home/*/gtk-gnutella-downloads
exclude = /var/cache/backupninja/duplicity

[dest]
type = remote
directory = /var/lib/backup/penguin/penguin
host = backup
user = penguin

The ssh host details are set in /root/.ssh/config.

The biggest things that is currently backed up is the Pwiki database, it's 1.6G when dumped as plain text.

A test backup run has been run.

[chris]

Migrating ​http://static.transitionnetwork.org/ first copy the archive across:

mkdir /web/static.transitionnetwork.org
rsync -av quince:/web/static.transitionnetwork.org/ /web/static.transitionnetwork.org/

It is 1.9G in total:

816M    ./ttcon2011
201M    ./ttcon2010
876M    ./tnconf2012

Copy the static nginx config from kiwi:

/etc/nginx/sites-available
scp kiwi:/etc/nginx/sites-available/static .

After editing this is what we have:

# static.transitionnetwork.org http virtual server
# http://nginx.org/en/docs/http/ngx_http_core_module.html#server
server {

        # listen for ipv4
        # http://nginx.org/en/docs/http/ngx_http_core_module.html#listen
        #listen   8000; 
        listen   80;

        # server name and server aliases        
        # http://nginx.org/en/docs/http/ngx_http_core_module.html#server_name 
        server_name  static.transitionnetwork.org static.penguin.webarch.net;

        # logs, error log levels: info | notice | warn | error | crit | alert 
        # http://nginx.org/en/docs/http/ngx_http_log_module.html#access_log
        # http://nginx.org/en/docs/ngx_core_module.html#error_log
        access_log  /var/log/nginx/static.access.log;
        error_log   /var/log/nginx/static.error.log crit;

        # document root
        # http://nginx.org/en/docs/http/ngx_http_core_module.html#root
        root   "/web/static.transitionnetwork.org/www";

        # http://nginx.org/en/docs/http/ngx_http_autoindex_module.html#autoindex
        autoindex  on;

        # document index
        # http://nginx.org/en/docs/http/ngx_http_index_module.html#index
        index  index.html;

        # location match
        # http://nginx.org/en/docs/http/ngx_http_core_module.html#location

        # If the most specific prefix location has the "^~" prefix then regular expressions are not checked.
        # redirect all these to the https version
        # http://nginx.org/en/docs/http/ngx_http_rewrite_module.html#rewrite
        # Some private data
        location ^~ /ttcon2010/private {
                #rewrite  ^/(.*)$  https://static.transitionnetwork.org:4430/$1? permanent;
                rewrite  ^/(.*)$  https://static.transitionnetwork.org/$1? permanent;
        }
        location ^~ /tnconf2012/emails/ {
                #rewrite  ^/(.*)$  https://static.transitionnetwork.org:4430/$1? permanent;
                rewrite  ^/(.*)$  https://static.transitionnetwork.org/$1? permanent;
        }

        # Prevent access to any files starting with a dot, like .htaccess
        # or text editor temp files
        location ~ /\. {
                access_log off;
                log_not_found off;
                deny all;
        }
}

# static.transitionnetwork.org https virtual server
server {
        #listen   4430;
        listen   443;
        server_name  static.transitionnetwork.org static.penguin.webarch.net;
        access_log  /var/log/nginx/static.ssl_access.log;
        error_log   /var/log/nginx/static.ssl_error.log debug;

        ssl  on;
        ssl_certificate  /etc/ssl/transitionnetwork.org/transitionnetwork.org.chained.pem;
        ssl_certificate_key  /etc/ssl/transitionnetwork.org/transitionnetwork.org.key;
        ssl_protocols  SSLv3 TLSv1 TLSv1.1 TLSv1.2;
        ssl_ciphers  RC4-SHA:HIGH:!ADH:!SSLv2:!aNULL;
        ssl_prefer_server_ciphers   on;

        root   "/web/static.transitionnetwork.org/www";
        autoindex  on;
        index  index.html;

        # if a "/" request happens frequently, defining "location = /" will speed up the processing of these 
        # requests, as search terminates right after the first comparison 
        location /ttcon2010/private {
                satisfy any;
                deny all;
                # http://nginx.org/en/docs/http/ngx_http_auth_basic_module.html
                auth_basic "Private Area";
                auth_basic_user_file /web/static.transitionnetwork.org/.htpasswd;
        }
        location /tnconf2012/emails {
                satisfy any;
                deny all;
                # http://nginx.org/en/docs/http/ngx_http_auth_basic_module.html
                auth_basic "Private Area";
                auth_basic_user_file /web/static.transitionnetwork.org/.htpasswd;
        }

        # Prevent access to any files starting with a dot, like .htaccess
        # or text editor temp files
        location ~ /\. {
                access_log off;
                log_not_found off;
                deny all;
        }
}

Enable it for testing:

cd /etc/nginx/sites-enabled/
ln -s ../sites-available/static 25-static
/etc/init.d/nginx configtest
/etc/init.d/nginx restart

After testing the DNS was updated and soon ​https://static.transitionnetwork.org/ will be running via Nginx.

The Penguin wiki page has been updated wiki:PenguinServer#static.transitionnetwork.org

[chris]

Static splash page for ​http://penguin.webarch.net/ and ​http://penguin.transitionnetwork.org/ and ​http://81.95.52.111/

Copying what we have on kiwi and editing files to suit.

mkdir -p /web/penguin.transitionnetwork.org/www
rsync -av kiwi:/web/kiwi.webarch.net/www/ /web/penguin.transitionnetwork.org/www/
cd /etc/nginx/sites-available
scp kiwi:/etc/nginx/sites-available/kiwi penguin
cd /etc/nginx/sites-enabled
ln -s ../sites-available/penguin 05-penguin
/etc/init.d/nginx configtest
/etc/init.d/nginx restart

Now we have a static splash page for the server: ​https://penguin.transitionnetwork.org/ but one thing to look at, is how to make the IP address serve the first virtual host rather than the last:

​http://81.95.52.111/

[chris]

To make the ​http://penguin.transitionnetwork.org/ site the default we need to add the IP address to the server_name and default_server to the listen directive:

listen   80 default_server;
server_name  penguin.transitionnetwork.org penguin.webarch.net 81.95.52.111;

Now we get the right page at these locations:

• ​http://81.95.52.111/
• ​http://foo.penguin.webarch.net/

But default_server should probably be used for the redirects, see wiki:NewLiveServer#Redirects

[chris]

Install NTP:

aptitude install ntp
logchange "libopts25{a} ntp : installed"

[chris]

Domains which are currently redirected by apache, see wiki:NewLiveServer#Redirects

Rewriting for nginx.

cd /etc/nginx/sites-available
vim redirects

all the domains listed on quince were checked, some have not been renewed and these were omitted (the wiki:DomainNames page needs updating), the redirects file contains:

# redirects 
server {
        listen 80;
        server_name transition-japan.org www.transition-japan.org;
        rewrite ^ http://www.transition-japan.net/ permanent;
}

server {
        listen 80;
        server_name transitiontowns.org www.transitiontowns.org;
        rewrite ^/Bellingen(.*)$ http://www.transitionnetwork.org/cms/bellingen permanent;
        rewrite ^/Lewes(.*)$ http://www.transitiontownlewes.org/ permanent;
        rewrite ^/Totnes(.*) http://www.transitiontowntotnes.org/ permanent;
        rewrite ^/Brixton(.*) http://www.transitiontownbrixton.org/ permanent;
        rewrite ^ http://www.transitionnetwork.org$request_uri? permanent;
}

server {
        listen 80;
        server_name     canbritainfeeditself.org      www.canbritainfeeditself.org
                        energydescentactionplan.org   www.energydescentactionplan.org
                        relocalisationmovement.org    www.relocalisationmovement.org
                        relocalisationparty.org       www.relocalisationparty.org
                        relocalizationmovement.org    www.relocalizationmovement.org
                        relocalizationnetwork.com     www.relocalizationnetwork.com
                        relocalizationnetwork.org     www.relocalizationnetwork.org
                        relocalizationparty.org       www.relocalizationparty.org
                        resiliencebuilders.com        www.resiliencebuilders.com
                        resiliencebuilders.org        www.resiliencebuilders.org
                        transitionamerica.org         www.transitionamerica.org
                        transitionaustralia.org       www.transitionaustralia.org
                        transitionbooks.org           www.transitionbooks.org
                        transitionbusiness.org        www.transitionbusiness.org
                        transitioncommunities.org     www.transitioncommunities.org
                        transitionfood.org            www.transitionfood.org
                        transitionireland.org         www.transitionireland.org
                        transitionjournal.org         www.transitionjournal.org
                        transitionmedia.org           www.transitionmedia.org
                        transitionmentoring.org.uk    www.transitionmentoring.org.uk
                        transitionnetwork.org.uk      www.transitionnetwork.org.uk
                        transitionnetwork.co.uk       www.transitionnetwork.co.uk
                        transitionnetworknews.com     www.transitionnetworknews.com
                        transitionnetworknews.org     www.transitionnetworknews.org
                        transitionparty.org           www.transitionparty.org
                        transitionradio.org           www.transitionradio.org
                        transitiontimesonline.org     www.transitiontimesonline.org
                        transitiontoday.org           www.transitiontoday.org
                        transitiontours.com           www.transitiontours.com
                        transitiontours.org           www.transitiontours.org
                        transitiontown.com            www.transitiontown.com
                        transitiontown.net            www.transitiontown.net
                        transitiontown.org            www.transitiontown.org
                        transitionvideo.org           www.transitionvideo.org
                        transitionwales.org           www.transitionwales.org;
        rewrite ^ http://www.transitionnetwork.org$request_uri? permanent;
}

The redirects were enabled:

cd /etc/nginx/sites-enabled
ln -s ../sites-available/redirects 05-redirects
/etc/init.d/nginx configtest
  Testing nginx configuration: nginx: [emerg] could not build the server_names_hash, you should increase either server_names_hash_max_size: 512 or server_names_hash_bucket_size: 64
  nginx: configuration file /etc/nginx/nginx.conf test failed

Due to the large number of server names this needed adding to /etc/nginx/nginx.conf:

server_names_hash_max_size 1024;

See ​http://nginx.org/en/docs/http/server_names.html

The Gandi "Transition Redirects" redirects zone file was going to be updated, bt it's owned by Ed and I don't have permission to update it, Gandi also has a list of all domains using this file, some of them have just expired and some are due to expire soon:

transitionplaces.org            2013-01-17
localtransition.org             2013-01-17
transitionmentoring.org.uk      2013-01-18        
transitionmentoring.org         2013-01-18
relocalizationnetwork.org       2013-02-25        
relocalizationmovement.org      2013-02-25        
relocalizationnetwork.com       2013-02-25        
relocalisationmovement.org      2013-02-25        
transitioncommunities.org       2013-03-25        
transitiontown.org              2013-04-07
transitiontown.net              2013-04-07
transitiontown.com              2013-04-07
resiliencebuilders.org          2013-06-09
transitionbooks.org             2013-06-09
resiliencebuilders.com          2013-06-09
transitiontimesonline.org       2013-08-06        
transitionjournal.org           2013-08-07
transitiontoday.org             2013-08-07
transitionparty.org             2013-08-17
relocalisationparty.org         2013-08-17
relocalizationparty.org         2013-08-17
transitiontours.org             2013-08-18
transitiontours.com             2013-08-18
transition-japan.org            2013-09-01
canbritainfeeditself.org        2013-09-04        
energydescentactionplan.org     2013-10-02        
transitionnetworknews.com       2013-10-17        
transitionnetworknews.org       2013-10-17        
transitionmedia.org             2013-10-20
transitionbusiness.org          2013-11-14
transitionfood.org              2013-11-14
transitionvideo.org             2013-11-17
transitionradio.org             2013-11-17
transitionireland.org           2013-11-17
transitionaustralia.org         2013-11-17
transitionwales.org             2013-11-17
transitionamerica.org           2014-01-17
transitionnetwork.org.uk        2014-01-25        

This is a list of them in alphabetical order:

canbritainfeeditself.org
energydescentactionplan.org
localtransition.org
relocalisationmovement.org
relocalisationparty.org
relocalizationmovement.org
relocalizationnetwork.com
relocalizationnetwork.org
relocalizationparty.org
resiliencebuilders.com
resiliencebuilders.org
transitionamerica.org
transitionaustralia.org
transitionbooks.org
transitionbusiness.org
transitioncommunities.org
transitionfood.org
transitionireland.org
transition-japan.org
transitionjournal.org
transitionmedia.org
transitionmentoring.org
transitionmentoring.org.uk
transitionnetworknews.com
transitionnetworknews.org
transitionnetwork.org.uk
transitionparty.org
transitionplaces.org
transitionradio.org
transitiontimesonline.org
transitiontoday.org
transitiontours.com
transitiontours.org
transitiontown.com
transitiontown.net
transitiontown.org
transitionvideo.org
transitionwales.org

I need to check these are all in the ngnix config and also give Ed the details he need to update the zone file, but first I need to eat.

[chris]

The domains added to the redirect Nginx config:

localtransition.org
transitionmentoring.org
transitionplaces.org

The only domain in the Ngnix config but not using the Gandi "Transition Redirects" zone file is:

transitionnetwork.co.uk

I'm not a tech contact for this domain and it's pointing to the quince IP address -- Ed it needs to be changed to use the "Transition Redirects" zone file if you have permission to do that (make me tech contact if you want then I should be able to do it).

Ed, can you also copy the "Transition Redirects" zone file, edit it to this:

@ 10800 IN A 81.95.52.111
www 10800 IN A 81.95.52.111

Thanks.

[chris]

Ed, the "Transition Towns" zone file also need editing, from:

@ 10800 IN A 81.95.52.88
totnes 10800 IN CNAME redirects.transitionnetwork.org.
www 10800 IN CNAME redirects.transitionnetwork.org.

To:

@ 10800 IN A 81.95.52.111
totnes 10800 IN A 81.95.52.111
www 10800 IN A 81.95.52.111

You should be able to do this here: ​https://www.gandi.net/admin/domain/zone/470186/2/edit first create a new version, then activate it.

(These seems little point in using a CNAME if we also have a IP address in the file in any case.)

I have created an additional virtual host for totnes.transitiontowns.org:

server {
        listen 80;
        server_name totnes.transitiontowns.org;
        rewrite ^ http://www.transitiontowntotnes.org$request_uri? permanent;
}

Though I wonder if it shouldn't point to the archive of the site rather than the latest version:

• ​http://archive.transitiontowntotnes.org/

[chris]

Looking at how to run trac with nginx have read these pages and searched for other useful resources:

• ​http://trac.edgewall.org/wiki/TracFastCgi
• ​http://trac.edgewall.org/wiki/TracNginxRecipe

[chris]

More here:

• ​http://trac.edgewall.org/wiki/TracOnDebian

I think it makes sense to first try to setup Trac with Fast CGI:

• ​http://trac.edgewall.org/wiki/TracFastCgi

And if that doesn't work (for example I can't get it to serve trac with the existing URLs) then use tracd, and nginx as a reverse proxy:

• ​http://trac.edgewall.org/wiki/TracNginxRecipe

See also the notes here:

• ​http://www.guyrutenberg.com/2008/06/04/start-trac-on-startup-initd-script-for-tracd/
• ​http://www.guyrutenberg.com/2009/02/17/starting-tracd-without-root-privileges-at-startup/

[chris]

I have copied the "Transition Redirects" zone to "Transition Redirects (2013-01-23)" and made it active, it contains:

@ 10800 IN A 81.95.52.111
www 10800 IN A 81.95.52.111

The "Transition Towns" domain has been copied to "Transition Towns (2013-01-23)" and made active, this contains:

@ 10800 IN A 81.95.52.111
totnes 10800 IN A 81.95.52.111
www 10800 IN A 81.95.52.111

Once the gandi DNS servers have updated all the redirects will be running off penguin.

[chris]

Looking at installing Trac on penguin, ​http://trac.edgewall.org/wiki/TracOnDebian#AdditionalTracfiles reading the documentation available via zless /usr/share/doc/trac/README.Debian.gz and the documentation of the existing install, wiki:DevelopmentServer#Trac the initial install was done on ticket:1 and the upgrade to 0.12 on ticket:364, there are migration notes here, ​http://trac.edgewall.org/wiki/TracMigrate#TracMigration

On kiwi, dump the database:

cd /web/tech.transitionnetwork.org/trac/db
sqlite3 trac.db ".dump" >> ~/sqlite_trac.sql

On penguin, copy the database across (this will need to be done again after testing):

aptitude install sqlite3
logchange "sqlite3 : installed"
mkdir -p /web/tech.transitionnetwork.org/trac/db
cd /web/tech.transitionnetwork.org/
scp kiwi:sqlite_trac.sql .
cat sqlite_trac.sql | sqlite3  trac.db
mv trac.db /web/tech.transitionnetwork.org/trac/db/
chown -R www-data:www-data /web/tech.transitionnetwork.org/trac/db/

Copy attachments across, on penguin (this will need to be done again after testing):

mkdir -p /web/tech.transitionnetwork.org/trac/attachments/
rsync -av kiwi:/web/tech.transitionnetwork.org/trac/attachments/ /web/tech.transitionnetwork.org/trac/attachments/
chown -R www-data:www-data /web/tech.transitionnetwork.org/trac/attachments/

Install a Trac environment:

trac-admin /web/tech.transitionnetwork.org/trac initenv
  Initenv for '/web/tech.transitionnetwork.org/trac' failed. 
  Directory exists and is not empty.

Clearly this should have been done at the start, so, move it out of the way and try again:

mv /web/tech.transitionnetwork.org/trac/ /web/tech.transitionnetwork.org/trac.bak/
trac-admin /web/tech.transitionnetwork.org/trac initenv
  
  Trac will first ask a few questions about your environment 
  in order to initialize and prepare the project database.
  
   Please enter the name of your project.
   This name will be used in page titles and descriptions.
  
  Project Name [My Project]> Transition Technology
   
   Please specify the connection string for the database to use.
   By default, a local SQLite database is created in the environment
   directory. It is also possible to use an already existing
   PostgreSQL database (check the Trac documentation for the exact
   connection string syntax).
  
  Database connection string [sqlite:db/trac.db]>
  
  Creating and Initializing Project
   Installing default wiki pages
    WikiStart imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/WikiStart
    InterWiki imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/InterWiki
    TracRevisionLog imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracRevisionLog
    WikiFormatting imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/WikiFormatting
    SandBox imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/SandBox
    TracQuery imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracQuery
    TracNotification imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracNotification
    TracAccessibility imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracAccessibility
    TracUnicode imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracUnicode
    InterMapTxt imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/InterMapTxt
    TracBackup imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracBackup
    PageTemplates imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/PageTemplates
    TracModWSGI imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracModWSGI
    TracBrowser imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracBrowser
    TracSupport imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracSupport
    WikiDeletePage imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/WikiDeletePage
    TracNavigation imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracNavigation
    TracPermissions imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracPermissions
    WikiNewPage imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/WikiNewPage
    WikiRestructuredText imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/WikiRestructuredText
    TracTimeline imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracTimeline
    WikiHtml imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/WikiHtml
    TracInstall imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracInstall
    WikiProcessors imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/WikiProcessors
    TracReports imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracReports
    TracTicketsCustomFields imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracTicketsCustomFields
    TracRoadmap imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracRoadmap
    TracGuide imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracGuide
    TracWorkflow imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracWorkflow
    TracEnvironment imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracEnvironment
    TracFastCgi imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracFastCgi
    InterTrac imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/InterTrac
    TracSearch imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracSearch
    TracTickets imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracTickets
    TracRss imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracRss
    TitleIndex imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TitleIndex
    TracWiki imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracWiki
    WikiPageNames imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/WikiPageNames
    RecentChanges imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/RecentChanges
    WikiRestructuredTextLinks imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/WikiRestructuredTextLinks
    TracFineGrainedPermissions imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracFineGrainedPermissions
    TracInterfaceCustomization imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracInterfaceCustomization
    TracLinks imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracLinks
    TracLogging imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracLogging
    CamelCase imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/CamelCase
    TracChangeset imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracChangeset
    TracImport imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracImport
    WikiMacros imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/WikiMacros
    TracCgi imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracCgi
    TracPlugins imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracPlugins
    TracUpgrade imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracUpgrade
    TracModPython imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracModPython
    TracRepositoryAdmin imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracRepositoryAdmin
    TracStandalone imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracStandalone
    TracAdmin imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracAdmin
    TracIni imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracIni
    TracSyntaxColoring imported from /usr/lib/python2.6/dist-packages/trac/wiki/default-pages/TracSyntaxColoring
  
  ---------------------------------------------------------------------
  Project environment for 'Transition Technology' created.
  
  You may now configure the environment by editing the file:
  
    /web/tech.transitionnetwork.org/trac/conf/trac.ini
  
  If you'd like to take this new project environment for a test drive,
  try running the Trac standalone web server `tracd`:
  
    tracd --port 8000 /web/tech.transitionnetwork.org/trac
  
  Then point your browser to http://localhost:8000/trac.
  There you can also browse the documentation for your installed
  version of Trac, including information on further setup (such as
  deploying Trac to a real web server).
  
  The latest documentation can also always be found on the project
  website:
  
    http://trac.edgewall.org/
  
  Congratulations!

The copy across the database and attachments:

cd /web/tech.transitionnetwork.org
cp trac.bak/db/trac.db trac/db/
chown www-data:www-data trac/db/trac.db 
rsync -av trac.bak/attachments/ trac/attachments/
chown -R www-data:www-data trac/attachments/

Test using tracd:

tracd --port 8000 /web/tech.transitionnetwork.org/trac

Everything is looked OK at ​http://penguin.transitionnetwork.org:8000/trac apart from the plugins and config changes, I'll look at importing them next, also the simplest thing might be to use the tracd server and nginx as a reverse proxy.

[chris]

Sorting out the trac.ini file, copy the kiwi one to penguin and diff it with the new one:

cd /web/tech.transitionnetwork.org
mkdir trac.bak/conf/
scp kiwi:/web/tech.transitionnetwork.org/trac/conf/trac.ini trac.bak/conf/
diff trac.bak/conf/trac.ini trac/conf/trac.ini | vim - 

Lots of things will need manually working though, but first the wiki:TimingAndEstimationPlugin

cd /web/tech.transitionnetwork.org
svn co http://trac-hacks.org/svn/timingandestimationplugin/branches/trac0.12/
mv trac0.12/ timingandestimationpluin_trac0.12/
cd timingandestimationpluin_trac0.12/
python setup.py bdist_egg
cp dist/timingandestimationplugin-1.2.8-py2.6.egg ../trac/plugins/

Add the following to /web/tech.transitionnetwork.org/trac/conf/trac.ini

[components]
timingandestimationplugin.* = enabled

Enable the logo, previously this was in a site directory outside the trac directory, see ​http://trac.edgewall.org/wiki/0.12/TracInstall#MappingStaticResources for the reasons why, however if we use tracd this isn't a concern, so:

cd /web/tech.transitionnetwork.org/trac
scp kiwi:/web/tech.transitionnetwork.org/www/site/TransitionNetwork-Logo-Web-Small.jpg .

Edit trac.ini changing:

[header_logo]
alt = TransitionNetwork.org Technology
link = /trac/
src = /trac/TransitionNetwork-Logo-Web-Small.jpg

[logging]
log_type = syslog
log_level = ERROR

[notification]
admit_domains = transitionnetwork.org, trac.transitionnetwork.org, tech.transitionnetwork.org
always_notify_owner = true
always_notify_reporter = true
mime_encoding = base64
sendmail_path = /usr/sbin/sendmail
smtp_default_domain = transitionnetwork.org
smtp_enabled = true
smtp_from = trac@transitionnetwork.org
smtp_from_name = Transiton Technology Trac
smtp_replyto = trac@transitionnetwork.org

[project]
descr = Support and issues tracking for the Transition Network Web Project.

[ticket]
default_component = Drupal modules & settings

[ticket-custom]
billable = checkbox
billable.label = Billable?
billable.order = 3
billable.value = 1
estimatedhours = text
estimatedhours.label = Estimated Number of Hours
estimatedhours.order = 1
estimatedhours.value = 0
hours = text
hours.label = Add Hours to Ticket
hours.order = 2
hours.value = 0
totalhours = text
totalhours.label = Total Hours
totalhours.order = 4
totalhours.value = 0

[trac]
base_url = https://tech.transitionnetwork.org/trac

The test the changes:

tracd --port 8000 /web/tech.transitionnetwork.org/trac

The results in the following at ​http://penguin.transitionnetwork.org:8000/

trac: Error
(The Trac Environment needs to be upgraded. Run "trac-admin /web/tech.transitionnetwork.org/trac upgrade")

So:

trac-admin /web/tech.transitionnetwork.org/trac upgrade

Timing and Estimation needs an upgrade
Upgrading Database
Upgrading reports
Upgrading usermanual
Done Upgrading
Upgrade done.

You may want to upgrade the Trac documentation now by running:

  trac-admin /web/tech.transitionnetwork.org/trac wiki upgrade

Things that need sorting out with trac:

• Authentication, see ​http://penguin.transitionnetwork.org:8000/trac/login
• The logo is a 404
• The Administration section, ​/trac/admin is missing
• After authentication is sorted the timing plugin needs checking
• Nginx, reverse proxy to tracd
• Tracd init script, see ​http://www.guyrutenberg.com/2008/06/04/start-trac-on-startup-initd-script-for-tracd/
• A check needs to be done for charset issues, the site was default_charset = iso-8859-15 (which is ISO Latin 1 with a few different characters, see ​https://en.wikipedia.org/wiki/ISO/IEC_8859-15#Changes_from_ISO-8859-1 ) and now it's default_charset = utf-8
• General testing and checking

[chris]

There are some tracd notes here:

• ​http://security.osmocom.org/trac/wiki/TracStandalone including notes on ​http://security.osmocom.org/trac/wiki/TracStandalone#UsingAuthentication and ​http://security.osmocom.org/trac/wiki/TracStandalone#Servingstaticcontent

Following the notes there to fix the logo issue:

cd /web/tech.transitionnetwork.org/trac
mv TransitionNetwork-Logo-Web-Small.jpg htdocs/

Edit conf/trac.ini:

src = /trac/chrome/site/TransitionNetwork-Logo-Web-Small.jpg 

And now the image works.

More authentication notes here:

• ​http://trac.edgewall.org/wiki/TracStandalone

[chris]

I'M ABOUT TO MIGRATE TRAC, PLEASE DON'T POST ANY TICKETS, COMMENTS TO TICKETS OR EDIT ANY WIKI PAGES FOR AN HOUR OR SO!

Ta!

---

Copy nginx reverse proxy config off kiwi and onto penguin:

cd /etc/nginx/sites-available
scp kiwi:/etc/nginx/sites-available/tech .

Adding a system user for tracd:

addgroup --system tracd
adduser --system --ingroup=tracd --no-create-home --disabled-password tracd

The HOME directory was then set in /etc/passwd to /web/tech.transitionnetwork.org and a directory for the PID was created:

mkdir /var/run/tracd
chown tracd:tracd /var/run/tracd
chown tracd:tracd /web/tech.transitionnetwork.org/trac -R

A directory for a script to start and a script to stop the server was created, following the suggestions here ​http://www.guyrutenberg.com/2009/02/17/starting-tracd-without-root-privileges-at-startup/

mkdir /web/tech.transitionnetwork.org/bin

And these scripts were created, tracd-start:

#!/bin/bash

/usr/bin/tracd  --daemonize \
                --pidfile=/var/run/tracd/tracd.pid \
                --port=8080 \
                --hostname=127.0.0.1 \
                --basic-auth="trac,/web/tech.transitionnetwork.org/.htpasswd,Restricted" \
                /web/tech.transitionnetwork.org/trac

And tracd-stop:

#!/bin/bash

kill `cat /var/run/tracd/tracd.pid`

And this was added to the tracd crontab:

@reboot /web/tech.transitionnetwork.org/bin/tracd-start

Fixing the favicon.ico, aliased in nginx:

mkdir /web/tech.transitionnetwork.org/www/
scp kiwi:/web/tech.transitionnetwork.org/www/favicon.ico /web/tech.transitionnetwork.org/www/

And in trac.ini:

[project]
icon = /favicon.ico

There we a lot of python egg cache errors that were solved (after a lot of faffing) by:

cd /web/tech.transitionnetwork.org
ln -s trac/.python-eggs
echo "export PYTHON_EGG_CACHE=~/trac/.python-eggs" >> /web/tech.transitionnetwork.org/.bashrc

The nginx config for the proxy:

# tech.transitionnetwork.org
# virtual server
# http://nginx.org/en/docs/http/ngx_http_core_module.html#server
server {

        # listen for ipv4
        # http://nginx.org/en/docs/http/ngx_http_core_module.html#listen
        #listen   8000; 
        listen   80;

        # server name and server aliases        
        # http://nginx.org/en/docs/http/ngx_http_core_module.html#server_name 
        server_name  trac.transitionnetwork.org tech.transitionnetwork.org tech.penguin.webarch.net;

        # logs, error log levels: info | notice | warn | error | crit | alert 
        # http://nginx.org/en/docs/http/ngx_http_log_module.html#access_log
        # http://nginx.org/en/docs/ngx_core_module.html#error_log
        access_log  /var/log/nginx/tech.access.log;
        error_log   /var/log/nginx/tech.error.log   crit;

        # Redirect to HTTPS
        location / {
                #rewrite ^/(.*)$ https://tech.transitionnetwork.org:4430/$1 permanent;
                rewrite ^/(.*)$ https://$server_name/$1 permanent;
        }

}

# HTTPS server
#
server {
        #listen   4430;
        listen   443;
        server_name  trac.transitionnetwork.org tech.transitionnetwork.org tech.penguin.webarch.net;
        access_log  /var/log/nginx/tech.ssl_access.log;
        error_log   /var/log/nginx/tech.ssl_error.log crit;

        ssl  on;
        ssl_certificate  /etc/ssl/transitionnetwork.org/transitionnetwork.org.chained.pem;
        ssl_certificate_key  /etc/ssl/transitionnetwork.org/transitionnetwork.org.key;
        ssl_protocols  SSLv3 TLSv1 TLSv1.1 TLSv1.2;
        ssl_ciphers  RC4-SHA:HIGH:!ADH:!SSLv2:!aNULL;
        ssl_prefer_server_ciphers   on;

        # Redirect requests for / to /trac
        location = / {
                        rewrite ^/$ https://$server_name/trac;
        }

        # favicon
        location = /favicon.ico {
             alias /web/tech.transitionnetwork.org/www/favicon.ico;
        }

        # http://trac.edgewall.org/wiki/TracNginxRecipe
        add_header           Front-End-Https    on;

        # http://wiki.nginx.org/LikeApache 
        #location ~ /trac(?<path_info>/.*|$) {
        location /trac {
                proxy_set_header X-Forwarded-Host $host;
                proxy_set_header X-Forwarded-Server $host;
                proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
                proxy_pass http://localhost:8080/trac;
        }

}

Getting the authentication working took some time but it's now all working here:

​https://tech.penguin.webarch.net/trac

So after posting this ticket I'm going to do a final sync of the attachments and database and then update the dns and then check for any things that haven't been migrated off kiwi -- I think this is the last thing to move.

[chris]

OK, the DNS has been updated, tech.transitionnetwork.org and trac.transitionnetwork.org point here, I have also removed the dev.transitionnetwork.org and test.transitionnetwork.org domains so the only domain pointing to kiwi still is kiwi.transitionnetwork.org and I think that can probably be removed.

The documentation has been updated here wiki:PenguinServer#tech.transitionnetwork.org

The gandi DNS server are updating but are not quite there yet:

dig @A.DNS.GANDI.NET tech.transitionnetwork.org
  ;; ANSWER SECTION:
  tech.transitionnetwork.org. 3600 IN     A       81.95.52.111

dig @B.DNS.GANDI.NET tech.transitionnetwork.org
  ;; ANSWER SECTION:
  tech.transitionnetwork.org. 3600 IN     A       81.95.52.78

In the meantime you can use this domain: ​https://tech.penguin.webarch.net/trac

[chris]

I have quickly added Piwik stats to Mediawiki:

cd /web/wiki.transitionnetwork.org/www/extensions
git clone git://github.com/DaSchTour/piwik-mediawiki-extension.git
mv piwik-mediawiki-extension Piwik

Thje site was added to Piwik and then this was then added to /web/wiki.transitionnetwork.org/www/LocalSettings.php:

require_once( "$IP/extensions/Piwik/Piwik.php" );
$wgGroupPermissions['bureaucrat']['viewpiwik'] = true;
$wgPiwikURL = "stats.transitionnetwork.org/";
$wgPiwikIDSite = "3";

It might be also worth adding the stats code to the various archives (and removing the google tracking) and also to this Trac site, but all these things would take longer than this quick fix for Mediawiki.

[chris]

In Piwik there is this warning:

Warning: We've detected the old GeoIP plugin. GeoIP integration is now in Piwik core and this plugin is considered deprecated. New region and city reports will not be shown while this plugin is loaded. Please disable the plugin and configure GeoIP. If you want location data for your old visits, use the script described here then reprocess your reports.

So, following ​http://piwik.org/faq/how-to/#faq_166

cd /etc/nginx
mkdir geoip
wget "http://geolite.maxmind.com/download/geoip/database/GeoLiteCountry/GeoIP.dat.gz"
wget "http://geolite.maxmind.com/download/geoip/database/GeoLiteCity.dat.gz"
gunzip GeoLiteCity.dat.gz
gunzip GeoIP.dat.gz
v GeoLiteCity.dat GeoIPCity.dat

Edit /etc/nginx/nginx.conf and add:

geoip_country  /etc/nginx/geoip/GeoIP.dat;
geoip_city     /etc/nginx/geoip/GeoIPCity.dat;

Edit /etc/nginx/fastcgi_params and add:

fastcgi_param GEOIP_ADDR $remote_addr;
fastcgi_param GEOIP_COUNTRY_CODE $geoip_country_code;
fastcgi_param GEOIP_COUNTRY_NAME $geoip_country_name;

fastcgi_param GEOIP_REGION $geoip_region;
fastcgi_param GEOIP_REGION_NAME $geoip_region_name;
fastcgi_param GEOIP_CITY $geoip_city;
fastcgi_param GEOIP_AREA_CODE $geoip_area_code;
fastcgi_param GEOIP_LATITUDE $geoip_latitude;
fastcgi_param GEOIP_LONGITUDE $geoip_longitude;
fastcgi_param GEOIP_POSTAL_CODE $geoip_postal_code;

Restart Nginx:

/etc/init.d/nginx configtest
/etc/init.d/nginx restart

Note the warning here, ​http://dev.maxmind.com/geoip/geolite

Keep in mind that geolocation data will eventually become out of date. It is recommended you update your databases at least once a month.* The free databases are updated once a month on the first Tuesday.

[chris]

The Piwik Nginx config hasn't been documented here and I have just updated the wiki:PiwikServer page.

The /etc/nginx/sites-available/stats file contains:

# piwik.transitionnetwork.org
# virtual server
# http://nginx.org/en/docs/http/ngx_http_core_module.html#server
server {

        # listen for ipv4
        # http://nginx.org/en/docs/http/ngx_http_core_module.html#listen
        #listen   8000; 
        listen   80;

        # server name and server aliases        
        # http://nginx.org/en/docs/http/ngx_http_core_module.html#server_name 
        server_name stats.transitionnetwork.org stats.penguin.webarch.net;

        # logs, error log levels: info | notice | warn | error | crit | alert 
        # http://nginx.org/en/docs/http/ngx_http_log_module.html#access_log
        # http://nginx.org/en/docs/ngx_core_module.html#error_log
        access_log  /var/log/nginx/piwik.access.log;
        error_log   /var/log/nginx/piwik.error.log   crit;

        # Redirect to HTTPS
        rewrite ^ https://stats.transitionnetwork.org$request_uri? permanent;

}

# HTTPS server
#
server {
        #listen   4430;
        listen   443;
        server_name  stats.transitionnetwork.org stats.penguin.webarch.net;
        access_log  /var/log/nginx/piwik.ssl_access.log;
        error_log   /var/log/nginx/piwik.ssl_error.log crit;

        ssl  on;
        ssl_certificate  /etc/ssl/transitionnetwork.org/transitionnetwork.org.chained.pem;
        ssl_certificate_key  /etc/ssl/transitionnetwork.org/transitionnetwork.org.key;
        ssl_protocols  SSLv3 TLSv1 TLSv1.1 TLSv1.2;
        ssl_ciphers  RC4-SHA:HIGH:!ADH:!SSLv2:!aNULL;
        ssl_prefer_server_ciphers   on;

        root   "/web/stats.transitionnetwork.org/piwik";
        index  index.php;

        # http://wiki.nginx.org/Piwik
        # Disable all methods besides HEAD, GET and POST.
        if ($request_method !~ ^(GET|HEAD|POST)$ ) {
            return 444;
        }

        ## Try all locations and relay to index.php as a fallback.
        location / {
                try_files $uri /index.php?$query_string;
        }

        ## Relay all index.php requests to fastcgi.
        location = /index.php {
                include fastcgi_params;
                fastcgi_param HTTPS on;
                fastcgi_pass unix:/var/run/php5-fpm/phpfpm.sock;
                fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
                fastcgi_intercept_errors on;
                ### Implementation of the microcache concept as presented here:
                ### http://fennb.com/microcaching-speed-your-app-up-250x-with-no-n
                ## FastCGI cache.
                ## cache ui for 5m (set the same interval of your crontab)
                ## The cache zone referenced.
                fastcgi_cache fcgicache;
                ## The cache key.
                fastcgi_cache_key $host$request_uri;
                ## For 200 and 301 make the cache valid for 5 minutes.
                fastcgi_cache_valid 200 301 5m;
                ## For 302 make it valid for 3 minutes.
                fastcgi_cache_valid 302 3m;
                ## For 404 make it valid 1 minute.
                fastcgi_cache_valid 404 1m;
                ## If there are any upstream errors or the item has expired use
                ## whatever it is available.
                fastcgi_cache_use_stale error timeout invalid_header updating http_500;
                ## The Cache-Control and Expires headers should be delivered untouched
                ## from the upstream to the client.
                fastcgi_ignore_headers Cache-Control Expires;
                ## If we have a cookie we should bypass the cache. The same if we have a
                fastcgi_cache_bypass $no_cache;
                fastcgi_no_cache $no_cache;
                ## Add a cache miss/hit status header.
                add_header X-Piwik-Cache $upstream_cache_status;
                ## To avoid any interaction with the cache control headers we expire
                ## everything on this location immediately.
                expires epoch;
        }

        ## Relay all piwik.php requests to fastcgi.
        location = /piwik.php {
                include fastcgi_params;
                fastcgi_param HTTPS on;
                fastcgi_pass unix:/var/run/php5-fpm/phpfpm.sock;
                fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
                fastcgi_intercept_errors on;
                ### Implementation of the microcache concept as presented here:
                ### http://fennb.com/microcaching-speed-your-app-up-250x-with-no-n
                ## The cache zone referenced.
                fastcgi_cache fcgicache;
                ## The cache key.
                fastcgi_cache_key $host$request_uri;
                ## For 200 and 301 make the cache valid for 2 hours.
                fastcgi_cache_valid 200 301 2h;
                ## For 302 make it valid for 30 minutes.
                fastcgi_cache_valid 302 30m;
                ## For 404 make it valid 10 minutes.
                fastcgi_cache_valid 404 10m;
                ## If there are any upstream errors or the item has expired use
                ## whatever it is available.
                fastcgi_cache_use_stale error timeout invalid_header updating http_500;
                ## The Cache-Control and Expires headers should be delivered untouched
                ## from the upstream to the client.
                fastcgi_ignore_headers Cache-Control Expires;
                ## If we have a cookie we should bypass the cache. The same if we have a
                fastcgi_cache_bypass $no_cache;
                fastcgi_no_cache $no_cache;
                ## Add a cache miss/hit status header.
                add_header X-Piwik-Long-Cache $upstream_cache_status;
                ## To avoid any interaction with the cache control headers we expire
                ## everything on this location immediately.
                expires epoch;
        }
        ## Any other attempt to access PHP files redirects to the root.
                location ~* ^.+\.php$ {
                return 302 /;
        }

        ## Redirect to the root if attempting to access a txt file.
                location ~* (?:DESIGN|(?:gpl|README|LICENSE)[^.]*|LEGALNOTICE)(?:\.txt)*$ {
                return 302 /;
        }

        ## Disallow access to several helper files.
                location ~* \.(?:bat|html?|git|ini|sh|svn[^.]*|txt|tpl|xml)$ {
                return 404;
        }

        ## No crawling of this site for bots that obey robots.txt.
                location = /robots.txt {
                return 200 "User-agent: *\nDisallow: /\n";
        }


}                                                                            

And /etc/nginx/fastcgi_cache_zone.conf contains:

fastcgi_cache_path /var/cache/nginx/fcgicache levels=1:2 keys_zone=fcgicache:5M max_size=500M inactive=3h;

The cache currently has 4026 items in it and it's 19M in total, I have added it to the list of things that shouldn't be backed up, in /etc/backup.d/90.rdiff:

exclude = /var/cache/nginx/fcgicache

[chris]

There are some HTTPS settings that need checking and perhaps redirects from addresses like www.wiki.transitionnetwork.org to wiki.transitionnetwork.org need setting up.

HTTPS issues:

This server is vulnerable to the CRIME attack.
Chain issues Contains anchor
Compression Yes INSECURE (more info)
Session resumption No (IDs assigned but not accepted)

​https://www.ssllabs.com/ssltest/analyze.html?d=wiki.transitionnetwork.org&s=81.95.52.111

[chris]

I have just checked the logs on kiwi and it's not been doing anything for some days -- all the sites are now on penguin, so I have shut it down and updated some wiki pages.

[chris]

Xen console access has been setup and documented for wiki:PuffinServer and wiki:PenguinServer on the wiki:XenShell page.

[chris]

Please note that some of the recent emails from Trac, since in was moved to wiki:PenguinServer, contain bug descriptions.

This is caused when a bug, that hasn't had a new comment to it posted since the migration, has a new comment posted to it.

Sorry about this, the "answer" is to scroll to then end of these emails are read the recent comment at the very end of them.

[chris]

Comparing the MySQL settings on wiki:NewLiveServer and wiki:PenguinServer.

Variable	Puffin	Quince
key_buffer	16M	256M
max_allowed_packet	16M	64M
max_connections	100	20
table_cache	64	1028
query_cache_limit	1M	256M
query_cache_size	16M	64M
sort_buffer_size		1M
read_buffer_size		1M
read_rnd_buffer_size		64M
myisam_sort_buffer_size		32M
table_open_cache		4096
join_buffer_size		24576K
tmp_table_size		128M
max_heap_table_size		128M
innodb_buffer_pool_size		256M
innodb_additional_mem_pool_size		20M
innodb_data_home_dir		/var/lib/mysql/ibdata
innodb_data_file_path		ibdata1:1G:autoextend:max:12G
innodb_log_file_size		64M
innodb_log_buffer_size		8M
innodb_log_group_home_dir		/var/lib/mysql/ibdata
innodb_flush_log_at_trx_commit		1
innodb_lock_wait_timeout		50

Check to see what suggestions we can get from ​http://mysqltuner.com/

cd /usr/local/bin
wget --no-check-certificate https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl 

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.66-0+squeeze1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 1G (Tables: 150)
[--] Data in InnoDB tables: 23M (Tables: 62)
[!!] Total fragmented tables: 65

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 45d 23h 59m 59s (1M q [0.419 qps], 84K conn, TX: 15B, RX: 2B)
[--] Reads / Writes: 85% / 15%
[--] Total buffers: 58.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 463.8M (22% of installed RAM)
[OK] Slow queries: 0% (28/1M)
[OK] Highest usage of available connections: 7% (11/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/290.5M
[OK] Key buffer hit rate: 99.8% (110M cached / 256K reads)
[OK] Query cache efficiency: 25.0% (304K cached / 1M selects)
[!!] Query cache prunes per day: 366
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 71K sorts)
[OK] Temporary tables created on disk: 17% (25K on disk / 145K total)
[OK] Thread cache hit rate: 99% (17 created / 84K connections)
[!!] Table cache hit rate: 0% (64 open / 32K opened)
[OK] Open file limit used: 3% (33/1K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[!!] InnoDB data size / buffer pool: 23.7M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 16M)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 23M)

So following these suggestions, the database were backed up using ninjahelper and then a bash script to run "OPTIMIZE TABLE tablename" on every table in every database was written and run.

And the following variables were changed as suggested:

#query_cache_size        = 16M
query_cache_size        = 32M

#table_cache            = 64
table_cache            = 128

innodb_buffer_pool_size = 64M

And mysql was restarted, it would be worth running the perl script again tomorrow to see what it suggests.

[chris]

Comparing php settings on wiki:NewLiveServer and wiki:PenguinServer first for the command line options, /etc/php5/cli/php.ini:

Variable	Puffin	Quince
serialize_precision	17	100
memory_limit	-1	512M

The following values in /etc/php5/cli/php.ini were changed:

;serialize_precision = 17
serialize_precision = 100

;memory_limit = -1
memory_limit = 512M

Comparing /etc/php5/fpm/php.ini on penguin and /etc/php5/apache2/php.ini on quince:

Variable	Penguin	Quince
expose_php	on	off
max_execution_time	30	60
max_input_time	60	120
memory_limit	128M	256M
error_reporting	E_ALL & ~E_DEPRECATED	E_COMPILE_ERROR|E_RECOVERABLE_ERROR|E_ERROR|E_CORE_ERROR
post_max_size	8M	40M
default_charset		"utf-8"
upload_max_filesize	2M	24M

The following variables in /etc/php5/fpm/php.ini were changed:

;max_execution_time = 30
max_execution_time = 60

;max_input_time = 60
max_input_time = 120

;memory_limit = 128M
memory_limit = 256M

;post_max_size = 8M
post_max_size = 125M

;upload_max_filesize = 2M
upload_max_filesize = 100M

And /etc/init.d/php5-fpm was restarted.

[chris]

Replying to chris:

HTTPS issues:

This server is vulnerable to the CRIME attack.
Chain issues Contains anchor
Compression Yes INSECURE (more info)
Session resumption No (IDs assigned but not accepted)

​https://www.ssllabs.com/ssltest/analyze.html?d=wiki.transitionnetwork.org&s=81.95.52.111

Nginx settings from ​BOA were copied and the following:

        ssl_protocols  SSLv3 TLSv1 TLSv1.1 TLSv1.2;
        ssl_ciphers  RC4-SHA:HIGH!ADH:!SSLv2:!aNULL;

Was changed to:

        ssl_protocols  SSLv3 TLSv1 TLSv1.1 TLSv1.2;
        ssl_ciphers  RC4:HIGH:!aNULL:!MD5; 

In all the files in /etc/nginx/sites-available/, however there was still the issue of the ​CRIME vulnerability -- this is caused by the combination of gzipped data and ssl so this section of config from /etc/nginx/nginx.conf, which did apply to all sites:

       gzip on;
       gzip_disable "msie6";
       gzip_vary on;
       gzip_proxied any;
       gzip_comp_level 6;
       gzip_buffers 16 8k;
       gzip_http_version 1.1;
       # text/html isn't needed in the following list for some reason
       gzip_types text/plain text/css application/json application/x-javascript text/xml application/xml application/xml+rss text/javascript;

Was put into a separate file and just included for the non-ssl server sections, this was then tested using ab:

ab -n 1 -v 4 -H "Accept-Encoding: gzip" http://wiki.transitionnetwork.org/Main_Page

But that didn't work as Apache bench uses HTTP 1.0 not 1.1, so it was tested using wget:

wget --server-response --header="Accept-Encoding: gzip" -O /dev/null http://wiki.transitionnetwork.org/Main_Page

But that also didn't work, not sure why, so the ​Firefox Live HTTP Headers plugin was used and this shows that content is being sent gzipped, this is the config used:

# only include this in http sites, see
# https://community.qualys.com/blogs/securitylabs/2012/09/14/crime-information-leakage-attack-against-ssltls
# see
# http://www.howtoforge.com/how-to-save-traffic-with-nginxs-httpgzipmodule-debian-squeeze
gzip on;
#gzip_disable "msie6";
gzip_disable "MSIE [1-6]\.(?!.*SV1)";
gzip_vary on;
gzip_proxied any;
# 1 to 9, 9 is maximum
gzip_comp_level 9;
gzip_buffers 32 16k;
gzip_http_version 1.1;
# text/html isn't needed in the following list for some reason
gzip_types text/plain text/css application/json application/x-javascript text/xml application/xml application/xml+rss text/javascript;

However this didn't solve the CRIME issue -- it turns out that the issue is ​the version on Nginx:

Details on the CRIME attack ​http://isecpartners.com/blog/2012/9/14/details-on-the-crime-attack.html … SSL comp is disabled in nginx since 1.3.2 and 1.2.2, SPDY comp can be configured/turned off

And we have:

dpkg -p nginx | grep Version
Version: 1.2.1-2~bpo60+1

This was solved by fixing the apt pinning problem -- Pin-Priority was set to 1001 in /etc/apt/preferences.d/backports.pref so it now contains:

Package: gawk geoip-database libcairo2 libfreetype6 libgeoip1 liblog-dispatch-perl libnet-server-perl libpixman-1-0 liburi-perl libxfont1 munin munin-common munin-doc munin-node munin-plugins-core munin-plugins-extra nginx nginx-common nginx-full python-babel trac x11-common
Pin: release o=backports
Pin-Priority: 1001

The following packages were upgraded to newer versions:

munin-common munin-doc munin-plugins-core nginx nginx-common nginx-full

And now we have:

dpkg -p nginx | grep Version
Version: 1.2.6-1~dotdeb.0

I don't understand why nginx from dotdeb has been pulled in, we had this for that repo, /etc/apt/preferences.d/dotdeb.pref:

Package: php-pear php5-cli php5-common php5-fpm php5-mysql php5
Pin: release o=packages.dotdeb.org
Pin-Priority: 990

And this, /etc/apt/preferences.d/backports.pref for backports:

Package: gawk geoip-database libcairo2 libfreetype6 libgeoip1 liblog-dispatch-perl libnet-server-perl libpixman-1-0 liburi-perl libxfont1 munin munin-common munin-doc munin-node munin-plugins-core munin-plugins-extra nginx nginx-common nginx-full python-babel trac x11-common
Pin: release o=backports
Pin-Priority: 1001

I need to re-read ​http://wiki.debian.org/AptPreferences -- I still don't fully understand pinning, but using the nginx from dotdeb is OK so this isn't urgent.

There is the "Chain issues: Contains anchor" warning, but this ​isn't worth worrying about.

And there was also a "Session resumption No (IDs assigned but not accepted)" warning, this was solved by following ​this thread and ​these instructions and adding the following to the main http block:

ssl_session_cache shared:SSL:10m;

So now we have ​a "A" overall rating for the HTTPS configuration \o/

Jim is keen on using Collectd rather than Munin, so it the server was removed:

dpkg -r munin

However using the node doesn't have a big overhead so that has been set to generate stats here:

​https://ecodissident.net/munin/webarch.net/penguin.webarch.net/

The exim plugin was deiabled and mysql and nginx ones were enabled, the nginx ones needed libwww-perl installing.

For php-fpm the ​plugin here was installed:

cd /usr/share/munin/plugins
git clone git://github.com/tjstein/php5-fpm-munin-plugins.git
chmod +x php5-fpm-munin-plugins/phpfpm_*

The symlinks were created from /etc/munin/plugins/ and the following needed adding to the default virtual host, /etc/nginx/sites-available/penguin:

        location ~ ^/(status|ping)$ {
                fastcgi_pass unix:/var/run/php5-fpm/phpfpm.sock;
                fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
                fastcgi_intercept_errors on;
                include fastcgi_params;
                access_log off;
                allow 127.0.0.1;
                deny all;
        }

The phpfpm_average and phpfpm_processes plugins were fixed by hardcoding the process:

#PHP_BIN=${phpbin-"php5-fpm"}
PHP_BIN="php-fpm"

And in phpfpm_memory:

#my $PHP_BIN = exists $ENV{'phpbin'} ? $ENV{'phpbin'} : "php5-fpm";
my $PHP_BIN = "php-fpm";

The perl package libcache-cache-perl was needed for the mysql_ plugins.

The /usr/local/bin/mysqltuner.pl script was run again:

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.66-0+squeeze1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 1G (Tables: 150)
[--] Data in InnoDB tables: 23M (Tables: 62)
[!!] Total fragmented tables: 67

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5d 15h 15m 53s (1M q [2.114 qps], 50K conn, TX: 9B, RX: 730M)
[--] Reads / Writes: 80% / 20%
[--] Total buffers: 130.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 535.8M (26% of installed RAM)
[OK] Slow queries: 0% (23/1M)
[OK] Highest usage of available connections: 7% (11/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/285.3M
[OK] Key buffer hit rate: 99.8% (63M cached / 151K reads)
[OK] Query cache efficiency: 25.8% (186K cached / 722K selects)
[!!] Query cache prunes per day: 366
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 55K sorts)
[OK] Temporary tables created on disk: 14% (12K on disk / 89K total)
[OK] Thread cache hit rate: 99% (11 created / 50K connections)
[!!] Table cache hit rate: 0% (128 open / 22K opened)
[OK] Open file limit used: 13% (134/1K)
[OK] Table locks acquired immediately: 99% (701K immediate / 701K locks)
[OK] InnoDB data size / buffer pool: 23.6M/64.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 32M)
    table_cache (> 128)

And the following variables in /etc/mysql/my.cnf were changed and mysql restarted:

query_cache_size        = 64M
table_cache            = 256

Regarding collectd it looks like generating stats from things like php-fpm would need some work, but there is a ​example of how to do it. Personally I think we should probably stick with Munin for now.

[chris]

The multips_memory plugin was enabled to track memory usage on the following processes:

[multips_memory]
env.names php5-fpm munin-node nginx mysql tracd php-cli

[chris]

Looking at some page load time stats from sites on penguin on ​http://tools.pingdom.com/fpt/ the fact that the Piwik webbug is only being served up via https really slows things down, so I have changed the nginx settings so that for http sites the bug can be loaded via http, https sites will still get it via https.

Changing this has reduced to total page load time for ​http://wiki.transitionnetwork.org/Main_Page from 3.8 seconds to 1.4 seconds.

Looking at the stats for ​http://www.transitionnetwork.org/ it's the size of the front page has doubled in the last year, it was around 0.5M and it's now 1.1M, but it's being served up in 0.6 of a second when testing from Amsterdam, which isn't bad.

I think the migration of sites to can not be said to be more-or-less done, one outstanding issue is the server resources -- it's got more than it needs, look at the CPU usage:

• ​https://ecodissident.net/munin/webarch.net/penguin.webarch.net/cpu.html

Load:

• ​https://ecodissident.net/munin/webarch.net/penguin.webarch.net/load.html

And memory usage:

• ​https://ecodissident.net/munin/webarch.net/penguin.webarch.net/memory.html

It's currently a 2GB VPS and it could be easilly dropped down to a 1.5GB VPS, server specs are here:

• ​https://webarch.net/virtual-servers

[chris]

The changes to the Piwik config in ticket:470#comment:43 overlooked the requirement for the login to use HTTPS, so this line was added to the port 80 server configuration:

        # Redirect front page to HTTPS
        rewrite !^/piwik\.*$ https://stats.transitionnetwork.org$request_uri? permanent;

This means that requests for ​http://stats.transitionnetwork.org/piwik.js and ​http://stats.transitionnetwork.org/piwik.php (both used for tracking) won't be redirected but all other requests will be.

The mysqltuning script was run again:

 perl /usr/local/bin/mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.66-0+squeeze1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 1G (Tables: 150)
[--] Data in InnoDB tables: 23M (Tables: 62)
[!!] Total fragmented tables: 66

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 5h 8m 29s (422K q [2.206 qps], 29K conn, TX: 3B, RX: 308M)
[--] Reads / Writes: 76% / 24%
[--] Total buffers: 162.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 567.8M (27% of installed RAM)
[OK] Slow queries: 0% (8/422K)
[OK] Highest usage of available connections: 7% (12/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/290.5M
[OK] Key buffer hit rate: 99.8% (24M cached / 47K reads)
[OK] Query cache efficiency: 29.7% (81K cached / 274K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 25K sorts)
[OK] Temporary tables created on disk: 12% (5K on disk / 41K total)
[OK] Thread cache hit rate: 99% (14 created / 29K connections)
[!!] Table cache hit rate: 9% (256 open / 2K opened)
[OK] Open file limit used: 27% (279/1K)
[OK] Table locks acquired immediately: 99% (267K immediate / 267K locks)
[OK] InnoDB data size / buffer pool: 23.6M/64.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    table_cache (> 256)

The table_cache was increased to 2048.

[chris]

To help keep track of the usage of penguin I have added piwik tracking to the following sites:

• ​https://tech.transitionnetwork.org/
• ​http://static.transitionnetwork.org/
• ​http://2010.archive.transitionnetwork.org/
• ​http://2011.archive.transitionnetwork.org/ (but not the ​http://2011.archive.transitionnetwork.org/forum/ part of the site yet)
• ​http://penguin.transitionnetwork.org/

And gemoved GA from ​http://2011.archive.transitionnetwork.org/ I hope this is OK.

For Trac it was done by creating /web/tech.transitionnetwork.org/trac/templates/site.html containing:

<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:xi="http://www.w3.org/2001/XInclude"
      xmlns:py="http://genshi.edgewall.org/"
      py:strip="">

  <body py:match="body" py:attrs="select('@*')">

    ${select('*|text()')}

    <!--! Add site-specific footer -->
    <div id="sitefooter">
<!-- Piwik -->
<script type="text/javascript">
var pkBaseURL = (("https:" == document.location.protocol) ? "https://stats.transitionnetwork.org/" : "http://stats.transitionnetwork.org/");
document.write(unescape("%3Cscript src='" + pkBaseURL + "piwik.js' type='text/javascript'%3E%3C/script%3E"));
</script><script type="text/javascript">
try {
var piwikTracker = Piwik.getTracker(pkBaseURL + "piwik.php", 7);
piwikTracker.trackPageView();
piwikTracker.enableLinkTracking();
} catch( err ) {}
</script><noscript><p><img src="http://stats.transitionnetwork.org/piwik.php?idsite=7" style="border:0" alt="" /></p></noscript>
<!-- End Piwik Tracking Code -->
    </div>
  </body>
</html>

Following the notes here ​http://trac.edgewall.org/wiki/TracInterfaceCustomization#SiteAppearance

[chris]

I have just added piwik stats to the old ​bbpress forum archive by adding the tracking code to /web/2011.archive.transitionnetwork.org/www/forum/bb-templates/kakumei/footer.php.

[chris]

The mysqltuning script was run again:

perl /usr/local/bin/mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.66-0+squeeze1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 1G (Tables: 150)
[--] Data in InnoDB tables: 23M (Tables: 62)
[!!] Total fragmented tables: 67

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 4d 16h 42m 56s (1M q [3.250 qps], 53K conn, TX: 8B, RX: 819M)
[--] Reads / Writes: 80% / 20%
[--] Total buffers: 162.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 567.8M (27% of installed RAM)
[OK] Slow queries: 0% (20/1M)
[OK] Highest usage of available connections: 7% (12/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/294.0M
[OK] Key buffer hit rate: 99.8% (68M cached / 126K reads)
[OK] Query cache efficiency: 26.6% (252K cached / 951K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 70K sorts)
[OK] Temporary tables created on disk: 14% (17K on disk / 121K total)
[OK] Thread cache hit rate: 99% (22 created / 53K connections)
[!!] Table cache hit rate: 13% (483 open / 3K opened)
[OK] Open file limit used: 12% (530/4K)
[OK] Table locks acquired immediately: 99% (918K immediate / 918K locks)
[OK] InnoDB data size / buffer pool: 23.7M/64.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    table_cache (> 2048)

And the table_cache in /etc/mysql/my.cnf was doubled to 4096 and mysql was restarted.

This probably isn't worth increasing further (and perhaps is now too high) ​setting it too high can make things slower, currently we have 263 tables in total:

mysql information_schema

mysql> SELECT COUNT(*) FROM tables;
+----------+
| COUNT(*) |
+----------+
|      263 |
+----------+
1 row in set (0.01 sec)

[chris]

I ran the mysqltuning script again:

 perl /usr/local/bin/mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.66-0+squeeze1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 1G (Tables: 152)
[--] Data in InnoDB tables: 23M (Tables: 62)
[!!] Total fragmented tables: 68

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 35d 0h 46m 6s (14M q [4.759 qps], 399K conn, TX: 63B, RX: 7B)
[--] Reads / Writes: 85% / 15%
[--] Total buffers: 162.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 567.8M (27% of installed RAM)
[OK] Slow queries: 0% (155/14M)
[OK] Highest usage of available connections: 8% (13/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/320.3M
[OK] Key buffer hit rate: 99.8% (649M cached / 1M reads)
[!!] Query cache efficiency: 18.1% (2M cached / 11M selects)
[!!] Query cache prunes per day: 970
[OK] Sorts requiring temporary tables: 0% (5 temp sorts / 677K sorts)
[OK] Temporary tables created on disk: 12% (130K on disk / 1M total)
[OK] Thread cache hit rate: 99% (46 created / 399K connections)
[!!] Table cache hit rate: 2% (488 open / 18K opened)
[OK] Open file limit used: 6% (536/8K)
[OK] Table locks acquired immediately: 99% (11M immediate / 11M locks)
[OK] InnoDB data size / buffer pool: 23.5M/64.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_limit (> 1M, or use smaller result sets)
    query_cache_size (> 64M)
    table_cache (> 4096)

And I dramatically increased these settings as there is RAM to spare, see:

• ​https://penguin.transitionnetwork.org/munin/transitionnetwork.org/penguin.transitionnetwork.org/memory.html
• ​https://penguin.transitionnetwork.org/munin/transitionnetwork.org/penguin.transitionnetwork.org/multips_memory.html

query_cache_limit       = 24M

query_cache_size        = 512M

[chris]

I ran the mysqltuning script again:

 perl /usr/local/bin/mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.66-0+squeeze1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 1G (Tables: 154)
[--] Data in InnoDB tables: 23M (Tables: 62)
[!!] Total fragmented tables: 68

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 27d 22h 58m 35s (12M q [5.375 qps], 262K conn, TX: 52B, RX: 6B)
[--] Reads / Writes: 86% / 14%
[--] Total buffers: 1.1G global + 2.7M per thread (30 max threads)
[OK] Maximum possible memory usage: 1.2G (58% of installed RAM)
[OK] Slow queries: 0% (151/12M)
[OK] Highest usage of available connections: 40% (12/30)
[OK] Key buffer size / total MyISAM indexes: 512.0M/330.4M
[OK] Key buffer hit rate: 99.9% (553M cached / 673K reads)
[OK] Query cache efficiency: 21.4% (2M cached / 10M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 543K sorts)
[OK] Temporary tables created on disk: 14% (114K on disk / 810K total)
[OK] Thread cache hit rate: 99% (23 created / 262K connections)
[!!] Table cache hit rate: 2% (298 open / 14K opened)
[OK] Open file limit used: 3% (265/8K)
[OK] Table locks acquired immediately: 99% (9M immediate / 9M locks)
[OK] InnoDB data size / buffer pool: 23.2M/64.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    table_cache (> 4096)

Due to the comment on ticket:470#comment:47 it's probably not worth increasing the size of the table_cache

MySQL memory use appears to be stable at around 350MB, see ​https://penguin.transitionnetwork.org/munin/transitionnetwork.org/penguin.transitionnetwork.org/multips_memory.html this seems reasonable with nginx and php-fpm using around 150MB each and server server having 2GB -- there is plenty of slack to cope with load spikes.

I think this ticket can now be closed.