Beschreibung

Die folgende Dokumentation beschreibt die Installation von DB2 ESE Version 10.1 auf CentOS 6.4. Die Datenbank wird mit HADR hochverfügbar betrieben und mit Multi-Temperature Storage beschleunigt.

Die Architektur sieht wie folgt aus:

Folgende IP's werden verwendet:

10.4.12.142 node1
10.4.12.148 node2

Installation

Die Installation der Software muss auf beiden Nodes durchgeführt werden.

DB2

# scp v10.1_linuxx64_server.tar.gz root@node[1|2]:/opt/
# ssh root@node[1|2]
node[1|2] # cd /opt/
node[1|2] # tar -xzvf v10.1_linuxx64_server.tar.gz
node[1|2] # cd server
node[1|2] # ./db2_install

In Standardverzeichnis (/opt/ibm/db2/V10.1) installieren? [ja/nein]
ja

Geben Sie "Beenden" ein, um den Vorgang zu beenden.
***********************************************************
ESE

Wollen Sie DB2 pureCluster Feature installieren? [ja/nein]
nein

Lizenz

Bei der Installation wird automatisch eine 90 Tage Testlizenz installiert. Wenn man DB2 auch über die 90 Tage hinaus verwenden möchte, installiert man die Lizenz:

# scp db2ese_c.lic root@node[1|2]:/opt/
# ssh root@node[1|2]

Installierte Lizenzen auflisten:

node[1|2] # /opt/ibm/db2/V10.1/adm/db2licm -l
Product name:                     "DB2 Enterprise Server Edition"
License type:                     "Trial"
Expiry date:                      "06/27/2013"
Product identifier:               "db2ese"
Version information:              "10.1"
Product name:                     "DB2 Connect Server"
License type:                     "Trial"
Expiry date:                      "06/27/2013"
Product identifier:               "db2consv"
Version information:              "10.1"

Installierte Lizenzen entfernen:

node[1|2] # /opt/ibm/db2/V10.1/adm/db2licm -r db2consv
node[1|2] # /opt/ibm/db2/V10.1/adm/db2licm -r db2ese

Neue Lizenz installieren:

node[1|2] # /opt/ibm/db2/V10.1/adm/db2licm -a /opt/db2ese_c.lic

Instanz

Jetzt kann eine Instanz installiert werden:

node[1|2] # groupadd db2
node[1|2] # useradd -s /bin/bash -g db2 -m -d /opt/ibm/db2/db2fenc1 db2fenc1
node[1|2] # useradd -s /bin/bash -g db2 -m -d /opt/ibm/db2/db2test db2test
node[1|2] # passwd db2test
node[1|2] # /opt/ibm/db2/V10.1/instance/db2icrt -u db2fenc1 db2test
node[1|2] # su - db2test
node[1|2] $ db2 update dbm cfg using SVCENAME DB2_db2test
node[1|2] $ db2set DB2COMM=tcpip
node[1|2] $ db2start

Datenbank

Im nächsten Schritt erstellt man eine Datenbank:

node[1|2] $ db2 create db test automatic storage yes restrictive

Konfiguration

Firewall

Der HADR Port muss zwischen den Nodes freigeschaltet werden:

node1 # vi /etc/sysconfig/iptables
-A INPUT -m state --state NEW -m tcp -p tcp --source 10.4.12.148 --dport 55001 -j ACCEPT
node1 # /etc/init.d/iptables restart
node2 # vi /etc/sysconfig/iptables
-A INPUT -m state --state NEW -m tcp -p tcp --source 10.4.12.142 --dport 55001 -j ACCEPT
node2 # /etc/init.d/iptables restart

HADR

Im ersten Schritt konfiguriert man Node1:

node1 # vi /etc/services
DB2_HADR_1      55001/tcp
node1 # su - db2test
node1 $ mkdir logarch1
node1 $ db2 update database configuration for test using LOGARCHMETH1 disk:/opt/ibm/db2/db2test/logarch1
node1 $ db2 update database configuration for test using LOGINDEXBUILD ON
node1 $ db2 backup db test
node1 $ scp TEST.0.db2test.DBPART000.20130330213023.001 db2test@10.4.12.148:
node1 $ db2 update alternate server for database test using hostname 10.4.12.148 port 60004
node1 $ db2 update db cfg for test using HADR_LOCAL_HOST '10.4.12.142'
node1 $ db2 update db cfg for test using HADR_LOCAL_SVC 'DB2_HADR_1'
node1 $ db2 update db cfg for test using HADR_REMOTE_HOST '10.4.12.148'
node1 $ db2 update db cfg for test using HADR_REMOTE_SVC 'DB2_HADR_1'
node1 $ db2 update db cfg for test using HADR_REMOTE_INST 'db2test'
node1 $ db2 update db cfg for test using HADR_PEER_WINDOW '120'
node1 $ db2 connect to test
node1 $ db2 quiesce database immediate force connections
node1 $ db2 unquiesce database
node1 $ db2 connect reset

Danach Node2:

node2 # vi /etc/services
DB2_HADR_1      55001/tcp
node2 # su - db2test
node2 $ db2 restore db test replace history file
node2 $ mkdir logarch1
node2 $ db2 update database configuration for test using LOGARCHMETH1 disk:/opt/ibm/db2/db2test/logarch1
node2 $ db2 update database configuration for test using LOGINDEXBUILD ON
node2 $ db2 update alternate server for database test using hostname 10.4.12.142 port 60004
node2 $ db2 update db cfg for test using HADR_LOCAL_HOST '10.4.12.148'
node2 $ db2 update db cfg for test using HADR_LOCAL_SVC 'DB2_HADR_1'
node2 $ db2 update db cfg for test using HADR_REMOTE_HOST '10.4.12.142'
node2 $ db2 update db cfg for test using HADR_REMOTE_SVC 'DB2_HADR_1'
node2 $ db2 update db cfg for test using HADR_REMOTE_INST 'db2test'
node2 $ db2 update db cfg for test using HADR_PEER_WINDOW '120'

Jetzt kann HADR gestartet werden:

node2 $ db2 deactivate database test
node2 $ db2 start hadr on database test as standby
node1 $ db2 deactivate database test
node1 $ db2 start hadr on database test as primary

Möchte man auf dem Standby Node lesend zugreifen, müssen folgende Parameter gesetzt werden:

node[1|2] $ db2set DB2_HADR_ROS=ON
node[1|2] $ db2set DB2_STANDBY_ISO=UR

Init Script

Damit DB2 nach einem Neustart des Systems wieder automatisch startet, muss noch ein Init Script eingerichtet werden.

HADR Primary Server:

#!/bin/bash
#
# DB2
#
# chkconfig: 2345 55 25
# description: DB2 v10.1
#
### BEGIN INIT INFO
# Provides: db2
# Required-Start: $local_fs $network $syslog
# Required-Stop: $local_fs $syslog
# Should-Start: $syslog
# Should-Stop: $network $syslog
# Default-Start: 2 3 4 5
# Default-Stop: 0 1 6
# Short-Description: DB2 v10.1
# Description:
### END INIT INFO
# source function library
. /etc/rc.d/init.d/functions
RETVAL=0
DB2_INST="db2test"
DB2_DATABASE="test"
DB2_HADR_STATE="primary by force"
runlevel=$(set -- $(runlevel); eval "echo \$$#" )
start()
{
        echo -n $"Starting DB2: "
        su - -c "db2start && db2 start hadr on database $DB2_DATABASE as $DB2_HADR_STATE" $DB2_INST && success || failure
        RETVAL=$?
        [ $RETVAL -eq 0 ]
        echo
        return $RETVAL
}
stop()
{
        echo -n $"Stopping DB2: "
        su - -c "db2 force application all && db2 deactivate db $DB2_DATABASE && db2stop" $DB2_INST && success || failure
        RETVAL=$?
        [ $RETVAL -eq 0 ]
        echo
}
restart() {
        stop
        start
}
case "$1" in
        start)
                start
                ;;
        stop)
                stop
                ;;
        restart)
                restart
                ;;
        *)
                echo $"Usage: $0 {start|stop|restart}"
                RETVAL=2
esac
exit $RETVAL
node1 # vi /etc/init.d/db2
// siehe weiter oben
node1 # chmod +x /etc/init.d/db2
node1 # chkconfig --add db2

HADR Standby Server:

#!/bin/bash
#
# DB2
#
# chkconfig: 2345 55 25
# description: DB2 v10.1
#
### BEGIN INIT INFO
# Provides: db2
# Required-Start: $local_fs $network $syslog
# Required-Stop: $local_fs $syslog
# Should-Start: $syslog
# Should-Stop: $network $syslog
# Default-Start: 2 3 4 5
# Default-Stop: 0 1 6
# Short-Description: DB2 v10.1
# Description:
### END INIT INFO
# source function library
. /etc/rc.d/init.d/functions
RETVAL=0
DB2_INST="db2test"
DB2_DATABASE="test"
DB2_HADR_STATE="standby"
runlevel=$(set -- $(runlevel); eval "echo \$$#" )
start()
{
        echo -n $"Starting DB2: "
        su - -c "db2 force application all && db2start && db2 start hadr on database $DB2_DATABASE as $DB2_HADR_STATE" $DB2_INST && success || failure
        RETVAL=$?
        [ $RETVAL -eq 0 ]
        echo
        return $RETVAL
}
stop()
{
        echo -n $"Stopping DB2: "
        su - -c "db2 deactivate db $DB2_DATABASE && db2stop" $DB2_INST && success || failure
        RETVAL=$?
        [ $RETVAL -eq 0 ]
        echo
}
restart() {
        stop
        start
}
case "$1" in
        start)
                start
                ;;
        stop)
                stop
                ;;
        restart)
                restart
                ;;
        *)
                echo $"Usage: $0 {start|stop|restart}"
                RETVAL=2
esac
exit $RETVAL
node2 # vi /etc/init.d/db2
// siehe weiter oben
node2 # chmod +x /etc/init.d/db2
node2 # chkconfig --add db2

 

Multi-Temperature Storage

Jedem System stehen zwei zusätzliche virtuelle Festplatten zur Verfügung:

node[1|2] # mkdir /db2/ssd1 -p
node[1|2] # mkdir /db2/sata1

node[1|2] # blkid
/dev/sdb1: UUID="5ab005e4-5193-4b83-8474-4797f7a36165" SEC_TYPE="ext2" TYPE="ext3" 
/dev/sdc1: UUID="ac4c41a5-545b-4667-8b97-7c1a67963f62" SEC_TYPE="ext2" TYPE="ext3"

node[1|2] # vi /etc/fstab
UUID=5ab005e4-5193-4b83-8474-4797f7a36165       /db2/ssd1/      ext3    defaults        0 1
UUID=ac4c41a5-545b-4667-8b97-7c1a67963f62       /db2/sata1/     ext3    defaults        0 1

node[1|2] # mount -a
node[1|2] # chgrp db2 /db2/ -R
node[1|2] # chmod g+w /db2/ -R

Diese beiden Festplatten werden jetzt als Multi-Temperature Storage eingebunden:

node1 # su - db2test
node1 $ db2 connect to test
node1 $ db2 "CREATE STOGROUP sg_hot ON '/db2/ssd1' DEVICE READ RATE 300"
node1 $ db2 "CREATE STOGROUP sg_warm ON '/db2/sata1'"

Jetzt legt man die Tablespaces an:

node1 $ db2 "CREATE TABLESPACE tbsp_2012 USING STOGROUP sg_warm"
node1 $ db2 "CREATE TABLESPACE tbsp_2013 USING STOGROUP sg_hot"

Auf diesen Tablespaces wird dann eine Tabelle partitioniert nach Datum angelegt:

node1 $ db2 "CREATE TABLE sales (order_date DATE, order_id INT, cust_id BIGINT)
             PARTITION BY RANGE (order_date)
             (PART 2012 STARTING ('2012-01-01') ENDING ('2012-12-31') in tbsp_2012,
              PART 2013 STARTING ('2013-01-01') ENDING ('2013-12-31') in tbsp_2013)"

Wenn man nun in der Tabelle Datensätze anlegt, werden diese je nach Jahr auf SSD oder SATA gespeichert:

node1 $ for I in {1..9999}; do
          TEMP_DATE=`date -d "201$((RANDOM%2+2))-$((RANDOM%12+1))-$((RANDOM%28+1))" '+%Y-%m-%d'`
          db2 "insert into sales values ('$TEMP_DATE','$I','`cat /dev/urandom|head -n1|tr -cd -- "[:digit:]"|head -c8`')"
        done

Backup

Auf dem PRIMARY wird täglich ein Full Backup erstellt und die letzten 3 Versionen aufgehoben. Alte Backups werden automatisch gelöscht:

node[1|2] $ db2 connect to test
node[1|2] $ db2 update db cfg using AUTO_DEL_REC_OBJ ON
node[1|2] $ vi db2_backup.sh
#!/bin/bash
if [ "`db2pd -hadr -d test|grep HADR_ROLE|cut -d= -f2|sed -r 's/\s+//'`" == "PRIMARY" ]; then
        let BACK=`date +%s`-259200
        PRUNE_BACK=`date -d @$BACK "+%Y%m%d%H%M%S"`
        db2 backup db test online include logs
        db2 connect to test
        db2 prune history $PRUNE_BACK with force option and delete
fi
node[1|2] $ chmod +x db2_backup.sh
node[1|2] $ crontab -e
30 1 * * * ~/db2_backup.sh

Restore

Das letzte Backup nach einem Crash mit alle gespeicherten Transaktionen kann wie folgt wiederhergestellt werden:

node1 $ db2 deactivate db test
node1 $ TIMESTAMP=`ls TEST*|tail -n1|cut -d. -f5`
node1 $ db2 restore db test taken at $TIMESTAMP replace history file
node1 $ db2 rollforward db test to end of logs and stop
node1 $ scp TEST.0.db2test.DBPART000.20130331171035.001 db2test@10.4.12.148:

node2 $ db2 deactivate database test
node2 $ TIMESTAMP=`ls TEST*|tail -n1|cut -d. -f5`
node2 $ db2 restore db test taken at $TIMESTAMP replace history file
node2 $ db2 deactivate database test
node2 $ db2 start hadr on database test as standby

Performance

Die Performance der einzelnen Queries unterscheiden sich jetzt deutlich voneinander.

SSD:

node1 $ time db2 "select cust_id as customer,count(order_id) as orders from sales \
                  where order_date < '2012-12-31' group by cust_id order by orders \
                  desc fetch first 10 rows only"

CUSTOMER             ORDERS     
-------------------- -----------
                   3          55
                   1          52
                   4          50
                   6          49
                   0          43
                   7          42
                   2          39
                   5          37
                   9          37
                   8          37

real    0m0.098s
user    0m0.031s
sys     0m0.039s

SATA:

node1 $ time db2 "select cust_id as customer,count(order_id) as orders from sales \
                  where order_date > '2013-01-01' group by cust_id order by orders \
                  desc fetch first 10 rows only"

CUSTOMER             ORDERS     
-------------------- -----------
                   4          53
                   6          52
                   1          49
                   5          48
                   3          45
                   0          42
                   8          41
                   9          39
                   2          34
                   7          31

real    0m0.802s
user    0m0.027s
sys     0m0.044s

Gegenprobe über beide Storage Pfade:

node1 $ time db2 "select cust_id as customer,count(order_id) as orders from sales \
                  group by cust_id order by orders \
                  desc fetch first 10 rows only"

CUSTOMER             ORDERS     
-------------------- -----------
                   4         104
                   6         102
                   1         101
                   3         100
                   0          86
                   5          85
                   8          78
                   9          76
                   2          74
                   7          73

real    0m0.904s
user    0m0.031s
sys     0m0.047s

Die Queries auf den SSD's sind damit zirka 8 mal schneller!

Monitoring

Die aktuelle I/O Auslastung kann mit iostat überwacht werden:

# yum install sysstat
# watch -n1 iostat -x

Links