Feb 032016
 

Test platform: UpCloud VPS (10vCPU / 32GB) with 50GB MAXIOPS

Different setups compared in this benchmark (only single run):

  • MariaDB 10.1.11 (single node)
  • MariaDB 10.1.11 Galera (three nodes in same DC)
  • MariaDB 10.1.11 Galera (three nodes, one node in another DC)
  • MariaDB 10.1.11 Galera (all three nodes in different DC:s, in the same continent)

Benchmark software used was sysbench-0.5 OLTP from Percona repositories with 24 tables and about 12GB of data. InnoDB configuration changes from (MariaDB repository debian 8) package the defaults are:

  • innodb_buffer_pool_size = 16G
  • innodb_buffer_pool_instances = 16
  • innodb_log_buffer_size = 4G
  • innodb_io_capacity = 10000
  • innodb_flush_neighbors = 0

Benchmark command (read-only):

  • for each in 1 2 4 8 16 32 64; do sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --db-driver=mysql --oltp-table-size=2000000 --mysql-db=sysbench --mysql-user=root --mysql-password=jeespoks --max-time=300 --max-requests=0 --oltp-read-only --oltp-tables-count=24 --oltp-reconnect-mode=random --num-threads=$each run > result-$each.txt; sleep 10; done
 Transactions /s  MariaDB 10.1 (1xFra)  Galera (3xFra)  Galera (1xHel,2xFra)  Galera (1xHel,1xFra,1xLon)
 Prepare time (8t, s)  7m 13.109s  18m 20.911s  51m 19.007s  70m 30.954s
 1 thread, read-only  431.91  428.18  28.23  20.12
 2 threads, read-only  823.80  797.25  44.45  38.72
 4 threads, read-only  1456.73  1289.39  64.45  62.86
 8 threads, read-only  1964.09  1319.78  127.07  122.77
16 threads, read-only  1671.03  1320.83  241.63  218.91
32 threads, read-only  1584.25  1307.16  466.30  399.04
64 threads, read-only  1605.12  1331.82  784.52  581.57

 

Benchmark command (read-write):

  • for each in 1 2 4 8 16 32 64; do sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --db-driver=mysql --oltp-table-size=2000000 --mysql-db=sysbench --mysql-user=root --mysql-password=jeespoks --max-time=300 --max-requests=0 --oltp-tables-count=24 --oltp-reconnect-mode=random --num-threads=$each run > result-$each.txt; sleep 10; done
Transactions /s MariaDB 10.1 (1xFra) Galera (3xFra) Galera (1xHel,2xFra) Galera (1xHel,1xFra,1xLon)
1 thread, read-write  435.76  393.46  27.20  20.31
2 threads, read-write  818.18  742.99  43.41  38.59
4 threads, read-write  1462.60  1230.78  64.21  61.87
8 threads, read-write  1967.40  1305.09  119.53  118.12
16 threads, read-write  1677.83  1321.16  219.10  221.30
32 threads, read-write  1569.35  1321.29  469.44  374.98
64 threads, read-write  1596.89  1328.96  782.56  548.70

 

TODO: rerun & try different optimizations

Nov 202014
 

Quick reminder how to recompile Bind9 with MySQL SDB:

  • Prepare build environment
apt-get install build-essential fakeroot dpkg-dev devscripts
cd /usr/src/
apt-get build-dep bind9
  • Get source
apt-get source bind9/wheezy
  • Copy SDB files into place

mysql-bind$: cp mysqldb.h ../bind9-9.8.4.dfsg.P1/bin/named/include/
mysql-bind$: cp mysqldb.c ../bind9-9.8.4.dfsg.P1/bin/named/

  • Configure (read instructions from the web-page), quick diffs below
bind9-9.8.4.dfsg.P1/bin/named/main.c:
...
#include <dlz/dlz_dlopen_driver.h>
+#include <named/mysqldb.h>
...
+ mysqldb_init();
+
ns_server_create(ns_g_mctx, &ns_g_server);
...
ns_server_destroy(&ns_g_server);

+ mysqldb_clear();
+
ns_builtin_deinit();
...
bind9-9.8.4.dfsg.P1/bin/named/Makefile.in:
...
-DBDRIVER_OBJS =
-DBDRIVER_SRCS =
-DBDRIVER_INCLUDES =
-DBDRIVER_LIBS =
+DBDRIVER_OBJS = mysqldb.@O@
+DBDRIVER_SRCS = mysqldb.c
+DBDRIVER_INCLUDES = -I/usr/include/mysql -fno-omit-frame-pointer -g -pipe -Wno-uninitialized -g -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing
+DBDRIVER_LIBS = -L/usr/lib -lmysqlclient
...
  • Update changelog (dch) and rebuild package (debuild -us -uc)
Nov 202014
 

As a reminder, how to enable serial console under KVM.

Hypervisor (CentOS 7):
– no changes required if required pty -devices are created automatically (-chardev pty,id=charserial0 -device isa-serial,chardev=charserial0,id=serial0 in guest command line)
–  if not found, you need the following bit in the devices section of virtual guests XML-file (modifying usually requires a full shutdown-start sequence for the virtual):

<serial type='pty'>
  <target port='0'/>
</serial>
<console type='pty'>
  <target type='serial' port='0'/>
</console>

Guest (Debian 7):
– modify /etc/default/grub:

GRUB_CMDLINE_LINUX_DEFAULT="quiet"
#GRUB_TERMINAL=console

->

GRUB_CMDLINE_LINUX_DEFAULT="console=ttyS0 quiet"
GRUB_TERMINAL=serial
GRUB_SERIAL_COMMAND="serial"

Uncomment the following line from /etc/inittab:

T0:23:respawn:/sbin/getty -L ttyS0 9600 vt100

Run update-grub and reboot virtual machine – now you should be able to use virsh console at the hypervisor.

<edit-16.1.2015>
– Added XML-configuration for serial/console
– Dropped 9600bps speed configuration
</edit>

Oct 172014
 

Idea is to create haproxy -configuration where it’ll try to make sure to always use a working backend server. Usually all the tutorials suggest using haproxy with “option mysql-check” – which is not enough this time, because MariaDB with Galera might answer correctly for this poll but still be unavailable in DONOR -state.

Prerequirements:

  • Working MariaDB Galera Cluster
  • Haproxy installed on the application server
  • xinetd installed on the database server

 

In the application server, we create haproxy frontend which is listening at 127.0.0.1:3306 for database connections, has weighted loadbalancing and is checks database servers in their port 9200, and has an administrative interface (for debug) in port 9600. And always check configuration and scripts, never just copy & paste from the internet.

# cat /etc/haproxy/haproxy.cfg

global
log /dev/log local0
log /dev/log local1 notice
chroot /var/lib/haproxy
stats socket /run/haproxy/admin.sock mode 660 level admin
stats timeout 30s
user haproxy
group haproxy
daemon
node APP1
description APP1.macronet.fi
maxconn 40000
spread-checks 3
quiet

defaults
log global
mode tcp
option tcp-smart-accept
option tcp-smart-connect
option dontlognull
option redispatch
timeout check 3500
timeout queue 3500
timeout connect 3500
timeout client 10000
timeout server 10000

userlist STATS
group admin users admin
user admin insecure-password adminpass
user stats insecure-password userpass

listen adminpage
bind *:9600
mode http
stats enable
stats refresh 60s
stats uri /
acl AuthOkay_ReadOnly http_auth(STATS)
acl AuthOkay_Admin http_auth_group(STATS) admin

listen galeracluster
bind 127.0.0.1:3306
mode tcp
balance leastconn
timeout client 60000
timeout server 60000
option tcpka
option httpchk
option allbackups
server DB1 192.168.10.21:3306 check port 9200 weight 128
server DB2 192.168.10.22:3306 check port 9200 weight 64
server DB3 192.168.10.23:3306 check port 9200 weight 32

 

In database server, we need to create a solution which listens in port 9200 and tells HAProxy that it’s OK. Theres quite many scripts already going around the ‘net, this is just one mix and match of ’em.  wsrep_local_state = 4  == SYNCED  == OK!

# cat /opt/galeracheck.sh

#!/bin/bash
#
# This script checks if a mysql server is healthy running on localhost. It will
# return:
# "HTTP/1.x 200 OK\r" (if mysql is running smoothly)
# - OR -
# "HTTP/1.x 500 Internal Server Error\r" (else)
#
# The purpose of this script is make haproxy capable of monitoring mysql properly
#

MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3306"
MYSQL_USERNAME="HAProxy"
MYSQL_PASSWORD="HAProxyPassword"
MYSQL_OPTS="-N -q -A"
TMP_FILE="/tmp/mysqlchk.$$.out"
ERR_FILE="/tmp/mysqlchk.$$.err"
FORCE_FAIL="/tmp/proxyoff"
MYSQL_BIN="/usr/bin/mysql"
CHECK_QUERY="show global status where variable_name='wsrep_local_state'"
preflight_check()
{
for I in "$TMP_FILE" "$ERR_FILE"; do
if [ -f "$I" ]; then
if [ ! -w $I ]; then
echo -e "HTTP/1.1 503 Service Unavailable\r\n"
echo -e "Content-Type: Content-Type: text/plain\r\n"
echo -e "\r\n"
echo -e "Cannot write to $I\r\n"
echo -e "\r\n"
exit 1
fi
fi
done
}
return_ok()
{
echo -e "HTTP/1.1 200 OK\r\n"
echo -e "Content-Type: text/html\r\n"
echo -e "Content-Length: 43\r\n"
echo -e "\r\n"
echo -e "<html><body>MariaDB Galera is OK!</body></html>\r\n"
echo -e "\r\n"
rm $ERR_FILE $TMP_FILE
exit 0
}
return_fail()
{
echo -e "HTTP/1.1 503 Service Unavailable\r\n"
echo -e "Content-Type: text/html\r\n"
echo -e "Content-Length: 42\r\n"
echo -e "\r\n"
echo -e "<html><body>MariaDB Galera is *down*!</body></html>\r\n"
sed -e 's/\n$/\r\n/' $ERR_FILE
echo -e "\r\n"
rm $ERR_FILE $TMP_FILE
exit 1
}
preflight_check
if [ -f "$FORCE_FAIL" ]; then
echo "$FORCE_FAIL found" > $ERR_FILE
return_fail;
fi
$MYSQL_BIN $MYSQL_OPTS --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD -e "$CHECK_QUERY" > $TMP_FILE 2> $ERR_FILE
if [ $? -ne 0 ]; then
return_fail;
fi
status=`cat $TMP_FILE | awk '{print $2;}'`

if [ $status -ne 4 ]; then
return_fail;
fi

return_ok;

 

As you must have noticed, it needs permissions on the database server:

MariaDB [(none)]> GRANT USAGE *.* to 'HAProxy' IDENTIFIED BY 'HAProxyPassword';

 

To make this listen in a port, use something like the following xinetd -service:

# cat /etc/xinetd.d/galeracheck
# default: on
# description: galeracheck
service galeracheck
{
flags = REUSE
socket_type = stream
port = 9200
wait = no
user = nobody
server = /opt/galeracheck.sh
log_on_failure += USERID
disable = no
# only_from = 0.0.0.0/0
# recommended to put the IPs that need to connect exclusively (security purposes)
per_source = UNLIMITED
}

 

And you’ll need to add the service to /etc/services and restart xinetd:

echo "galeracheck 9200/tcp # Galera clustercheck" >> /etc/services

 

After all of this, your result should be something like this:

# nc 192.168.10.21 9200
HTTP/1.1 200 OK

Content-Type: text/html

Content-Length: 43



<html><body>MariaDB Galera is OK!</body></html>

And your application should be able to query the database backend successfully through HAProxy.

Sep 172014
 

Note-to-self:

  1. Create hosts to the new instance with items you want to monitor
  2. Map itemids between instances to specify item data you want to copy from the old instance
  3. Copy history(_log,_str,_text, _uint)- and trends(_uint) data, enjoy:
  •  INSERT INTO zabbix2.history (itemid,clock,value,ns) SELECT ‘25188’,clock,value,ns FROM zabbix1.history WHERE itemid=’90090000000025470′;
  • INSERT IGNORE INTO zabbix2.trends (itemid,clock,num,value_min,value_avg,value_max) SELECT ‘25188’,clock,num,value_min,value_avg,value_max FROM zabbix1.trends where itemid=’90090000000025470′;