Projekt mspr@job1

How I move the MSPR site from job4 to job1

Preparation

• Install standard opensuse 13.1
• Optional: add repositories pacman, nvidia, vlc, css
• Install the following packages

zypper install apache2 php5 hg apache2-mod_php5 php5-gd php5-devel php5-pear gcc-c++ boost-devel libapr1-devel libapr-util1-devel

• Install db2 express-c 10.5
  • unpack nlpack inside expc for german messages
  • generate response file with db2setup, add key LIC_AGREEMENT=ACCEPT, then restart db2setup with option -r respfile
• Create instance db2mspr
  • do not directly mount the home directory during install or skeleton files will not be copied
• Configure instance like db2inst4 on job4

db2 update dbm cfg using numdb 2 federated yes DFT_MON_BUFPOOL on DFT_MON_LOCK on \
 DFT_MON_SORT on DFT_MON_STMT on DFT_MON_TABLE on DFT_MON_UOW on \
 INSTANCE_MEMORY 1048576 SHEAPTHRES 400000 INTRA_PARALLEL YES

• Create connect user db2inst2 (required because tables have this schema)
• Create loader user mspr (loads data from mspr web/xml pages)
• Clone mspr repository to /home/joachim/workspace/mspr (for scripts and docs)
• Install pdo for db2 (see howto in database/)
  • Generate lib

 zypper install gcc autoconf php5 php5-pear php5-devel
 pecl channel-update pecl.php.net
 pecl install -n --ignore-errors pdo_ibm
 DB2 install directory location? : /opt/ibm/db2/V10.5/

• • Or with php7 and db2 11.5 on 15.1 and an installed dsdriver package (that I used for python db2 access)

 zypper install gcc autoconf php7 php7-pear php7-devel
 pecl channel-update pecl.php.net
 pecl install -f -n --ignore-errors pdo_ibm
 DB2 install directory location? : /home/db2mspr/dsdriver/

• • Or with php8 and db2 11.5 on tumbleweed (just reused the old dsdriver package)

 zypper install php8-devel
 pecl channel-update pecl.php.net
 pecl install -f -n --ignore-errors pdo_ibm
 DB2 install directory location? : /home/db2mspr/dsdriver/

needs apache restart

• • Activate lib (create /etc/php5/conf.d/pdo_ibm.ini)

 ; comment out next line to disable pdo_ibm extension in php
 extension=pdo_ibm.so

• • Tell apache2 about db2 (opensuse 12.3, in 13.1 this does no longer work, fix see below)

 cp /usr/lib/systemd/system/apache2.service /etc/systemd/system/
 vi /etc/systemd/system/apache2.service

• • Use forking and edit ExecStart to use bash with db2profile to start apache2

 Type=Forking
 ExecStart=/bin/bash -c '. /home/db2mspr/sqllib/db2profile; /usr/sbin/start_apache2 -D SYSTEMD -DFOREGROUND -k start'

• • Tell systemd about the new configuration file

 systemctl daemon-reload

• Other settings in /etc/sysconfig/apache2 (DB2INSTANCE only works with older sysvinit opensuses or again with systemd on opensuse 13.1)

APACHE_SERVERADMIN="joachim.banzhaf@googlemail.com"
APACHE_SERVERNAME="banzhaf.chickenkiller.com"
DB2INSTANCE=db2mspr

• For Dev site add line in /etc/apache2/listen.conf

Listen 801

• Install scripts for import and web
  • all in web/ except gen_* to /srv/www/htdocs, then move images/ to mspr/images
  • util/mspr-* to /usr/local/bin
  • database/ta_mqt_refresh.sh to /usr/local/bin
• edit proxy destination (add test port :801 to url)

cd /srv/www/htdocs
for i in image index mainframe naviframe titleframe; do sed -i 's/\(homeip.net\)/\1:801/' $i.php; done

• Create password file for db connection /etc/apache/db2inst2.passwd, chown root:mspr, chmod 640
• Create group mspr with members mspr, db2mspr, wwwrun

make mspr

wget http://ftp.halifax.rwth-aachen.de/apache//logging/log4cxx/0.10.0/apache-log4cxx-0.10.0.tar.gz
tar xzvf apache-log4cxx-0.10.0.tar.gz
cd apache-log4cxx-0.10.0
./configure
make
sudo make install

if this fails, add #include <cstring> (and one time <cstdio>) in the failing cpp's

now go to mspr directory and make && cp -av mspr /usr/local/bin.

Maybe adding -lpthread to Makefile and running ldconfig is necessary before mspr can be linked and executed

Test Installation

• restore database from last backup and logs from job4 and document steps (needs 30-100 GB)

db2 restore db mspr on /home/db2mspr
db2 restore db mspr logs logtarget `db2 get db cfg for mspr | grep SQLOGDIR | awk -F '= ' '{print $2}'`
db2 rollforward db mspr to end of logs and stop
db2 activate db mspr

• check db settings are the same as on job4 (should be ok)
• Create portforward public :801 to job1:801
• Check if pages show correct results
• Enable hugepages (2048 * 2MB -> 4GB for kvm and db2 bufferpools)

echo "vm.nr_hugepages = 2048" >>/etc/sysctl.conf
sysctl -p   # may require reboot (check with cat /proc/meminfo)
echo "hugetlbfs       /hugepages  hugetlbfs       defaults        0 0" >>/etc/fstab
mkdir /hugepages
mount /hugepages
su - db2mspr -c 'db2set DB2_LARGE_PAGE_MEM=*'

• Check and tune performance

Surprisingly the better server has worse results on some queries (olympics/highscore). A remedy was setting optimization level to 0 - strange!

Level 0 wasnt good for all queries, so I have to implement a per statement opt level mechanism

• Setup cron for data imports
• Testrun for data import (check home directory requirement for mspr)

next task:

Production Switch

• disable update on job4
• restore db again
• edit proxy destination (remove test port :801 from url)

cd /srv/www/htdocs
for i in image index mainframe naviframe titleframe; do sed -i 's/:801//' $i.php; done

• test again
• remove public :801 forward
• forward public :80 to job1:80
• make apache listen to :80 instead of :801
• final tests