用Haproxy来做PostgreSQL的负载均衡

为什么80%的码农都做不了架构师？>>>   

HAProxy提供高可用性、负载均衡以及基于TCP和HTTP应用的代理，可以用来做web或者数据库的负载均衡，在一定范围内读写能力与添加的服务器数量成正比。haproxy用来做mysql负载均衡的文章很多，这里简单介绍下配合postgresql的使用。

一、环境
Centos 6.7
PostgreSQL 9.5.2
Haproxy 1.6
虚机服务列表
haproxy ip 192.168.226.148 (pg agent)
datanode1 ip 192.168.226.133 (pg server)
datanode2 ip 192.168.226.143 (pg server)

二、软件安装

#yum install gcc gcc-c++ make
#groupadd haproxy
#useradd haproxy –g haproxy -s /sbin/nologin
#wget http://www.haproxy.org/download/1.6/src/haproxy-1.6.4.tar.gz
#cd haproxy-1.6.4
#make TARGET=linux2628 PREFIX=/usr/local/haproxy
#make install PREFIX=/usr/local/haproxy
#mkdir -p /etc/haproxy/
#cp examples/haproxy.init /etc/init.d/haproxy
#chmod +x /etc/init.d/haproxy
#ln -s /usr/local/haproxy/sbin/haproxy /usr/sbin/

三、软件配置
1.主要配置文件haproxy.cfg

[root@localhost ~]# vi /etc/haproxy/haproxy.cfg
global
	log 127.0.0.1 local0 info
	maxconn 4096
	user haproxy
	group haproxy
	daemon
	nbproc 1
	chroot /usr/local/haproxy
	pidfile /var/run/haproxy.pid
defaults
	log global
	mode tcp
	option tcplog
	option dontlognull
	option redispatch
	retries 3
	maxconn 2000
	timeout connect 10s
	timeout client 60s
	timeout server 60s
frontend postgres-front
	bind *:1949
	mode tcp
	default_backend postgresql
listen  postgresql
	mode tcp
	option tcplog
	balance leastconn
	#balance roundrobin
	#option mysql-check user haproxy
	#server mysql-1 192.168.184.131:3306 weight 1 check inter 2000 rise 2 fall 3
	#server mysql-2 192.168.184.132:3306 weight 1 check inter 2000 rise 2 fall 3
       option pgsql-check user haproxy
       server postgre_1 192.168.184.131:1949 weight 1 check inter 2000 rise 2 fall 3
       server postgre_2 192.168.184.132:1949 weight 1 check inter 2000 rise 2 fall 3
listen admin-stats
	mode http
	option httplog
	bind 0.0.0.0:8888
	stats enable
	stats refresh 30s
	stats uri /dbs
	stats realm welcome login\ Haproxy
	stats auth admin:admin
	stats admin if TRUE
 [root@localhost ~]#

2.日志文件

[root@localhost ~]# vi /etc/rsyslog.d/haproxy.conf
$ModLoad imudp
$UDPServerRun 514
local0.* /usr/local/haproxy/haproxy.log
[root@localhost ~]# vi /etc/sysconfig/rsyslog
SYSLOGD_OPTIONS="-c 2 -r -m 0"
[root@localhost ~]# service rsyslog restart

四、使用
1.启动关闭

[root@localhost ~]# chkconfig haproxy on
[root@localhost ~]# service haproxy start/stop/restart

2.验证
打开浏览器,输入 http://192.168.226.148:8888/dbs，用户密码是上面配置的admin/admin 会出来如下页面 ，如果一台pg挂了，则相关node行数据会显示显眼的红色

3.服务端验证
在133和143服务器上各建一个库db_kenyon，创建haproxy的用户，在下面建不同的表来验证
133上建表tbl_1
[postgres@localhost ~]$ psql
psql (9.5.2) Type "help" for help.
postgres=# create database db_kenyon;
CREATE DATABASE
postgres=# \c db_kenyon
You are now connected to database "db_kenyon" as user "postgres".
db_kenyon=# create table tbl_1(id int);
CREATE TABLE
同样，143上建表tbl_2

在148上执行验证，148上建postgresql的客户端，并在133和143的pg_hba.conf里设置允许148为trust访问
host all all 192.168.226.148/32 trust
[root@localhost ~]# psql -h127.0.0.1 -p13306 -Uhaproxy db_kenyon -c"\dt"
 List of relations
 Schema | Name | Type | Owner
 --------+-------+-------+----------
public | tbl_2 | table | postgres
(1 row)

[root@localhost ~]# psql -h127.0.0.1 -p13306 -Uhaproxy db_kenyon -c"\dt"
 List of relations
 Schema | Name | Type | Owner
 --------+-------+-------+----------
public | tbl_1 | table | postgres
(1 row)
可以看到实现了负载均衡

五、性能简单压测
在两台服务器的db_kenyon库里建同样的表tbl_1，并插入10000条数据

[postgres@localhost ~]$ psql db_kenyon
psql (9.5.2)
Type "help" for help.

db_kenyon=# select * from tbl_1;
 id 
----
(0 rows)

db_kenyon=# insert into tbl_1 select generate_series(1,10000);
INSERT 0 10000
db_kenyon=# \q
[postgres@localhost ~]$

[postgres@localhost ~]$ more test.conf 
\set naccounts 10000 * :scale
\setrandom id 1 :naccounts
SELECT id FROM tbl_1 WHERE id = :id;

--单台压测
[postgres@localhost ~]$ pgbench -c 25 -j 25 -M prepared -n -s 500 -T 60 db_kenyon  -f ./test.conf 
transaction type: Custom query
scaling factor: 500
query mode: prepared
number of clients: 25
number of threads: 25
duration: 60 s
number of transactions actually processed: 94052
latency average: 15.949 ms
tps = 1619.015406 (including connections establishing)
tps = 1622.670100 (excluding connections establishing)

--加了haproxy压测
[root@localhost ~]# pgbench -c 25 -j 25 -M prepared -n -s 500 -T 60 -h127.0.0.1 -p13306 -Uhaproxy  db_kenyon -f ./test.conf
transaction type: Custom query
scaling factor: 500
query mode: prepared
number of clients: 25
number of threads: 25
duration: 60 s
number of transactions actually processed: 175073
latency average: 8.568 ms
tps = 2916.996809 (including connections establishing)
tps = 2918.372106 (excluding connections establishing)
可以看到tps增加了接近一倍，假如slave节点机子越多，该tps会有更大的提高

六、关键参数及其他
主要挑几个cfg配置文件里的参数说明：
mode tcp ：采用tcp模式
option pgsql-check user haproxy ：haproxy内置的postgres检测模块，后面的haproxy是数据库内用户名
server postgre_1 192.168.184.131:1949 weight 1 check inter 2000 rise 2 fall 3 :设置访问节点服务，每2秒检测超过3次失败则失败，从失败转成成功则需要检测2次，weight是分配权重。同样的模块还有mysql-check 
balance leastconn ：这个算法是最少连接数，还有个注释掉的roundrobin是轮训的方式

系统可以考虑加上keepalived和pgbouncer来搭整套环境，haproxy除了可以做负载均衡，还可以用来做读的ha和简单的状态监控，可以有效降低单台服务器的负载，单台压测cpu基本饱和，加了haproxy后每台cpu还能空出20%出来。

七、参考
http://www.haproxy.com/

转载于:https://my.oschina.net/Kenyon/blog/664102