寻觅生命中的那一片浅草......

PostgreSQL简介

怎么用了PostgreSQL?

PostgreSQL,在国内应该是没有MySQL那么流行,但对它的介绍,通常是和Oracle比,我们也只在2个地方用了:

  1. Zabbix,一开始是MySQL,后来agent端出现很多unreachable,才用了PostgreSQL,但换数据库并没有解决问题,最后是采用了表分区才解决了
  2. 大数据,CDH这个Hadoop发行版,默认使用的是PostgreSQL 8.4(2009年发行),有几个数据库连接 ,长期占用CPU达50%,花了点时间去看,但最终也没有解决。而且版本太旧,目前搜到的资料,都不太适用

安装

官方提供了各系统,各版本的rpm包,直接yum或者rpm即可

rpm -ivh https://download.postgresql.org/pub/repos/yum/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-3.noarch.rpm
yum -y install libxslt uuid
yum -y install postgresql93-server postgresql93-contrib postgresql93-libs postgresql93

上面的安装可能会提示证书有问题,此时可以去看看yum仓库的地址,然后手动wget回来安装即可

grep baseurl /etc/yum.repos.d/pgdg-93-centos.repo 

初始化和连接

# 初始化数据,数据库文件目录在/var/lib/pgsql/9.3/data/
/etc/init.d/postgresql-9.3 initdb

# 启动
/etc/init.d/postgresql-9.3 start

连接

# 以系统用户连接
# 连接,切换到postgres,然后就可以直接连接了
su - postgres
psql
# 通过用户密码连接,只能通过环境变量指定密码
PGPASSWORD=xxx /usr/bin/psql -U zabbix
# 把连接信息写到文件
cat /root/.pgpass
localhost:5432:*:test_user:user_password
# 通过指定用户名即可连接 
psql -U test_user

认证

它和MySQL一样,也是基于用户和主机进行认证的,配置文件如下,就是和数据在一起

# 修改此文件
# 需要以root身份进行reload:/etc/init.d/postgresql-9.3  reload

/var/lib/pgsql/9.3/data/pg_hba.conf

文件内容

# "local" is for Unix domain socket connections only
# 类型 数据库             用户           地址          认证方式
local   all             all                        peer
# IPv4 local connections:
host    all             all       127.0.0.1/32   ident
host    replication     replica     192.168.8.198/32    md5

认证类型

local的话,就是不指定主机名或者指定主机名为localhost,它默认使用unix socket进行连接/tmp/.s.PGSQL.5432,5432会随着监听端口改变,例如改为7432,则socket文件名就会变成/tmp/.s.PGSQL.7432

其他的就是通过host:port这样的tcp方式访问

认证方式

数据库和用户没什么要说的,这里讲下认证方式

  • ident,就是用PostgreSQL所在机器的系统用户,例如上面提到的切换到postgres用户,当系统用户和PostgreSQL里的用户名一致,就可以连接
  • peer,和ident一样,只是它是在客户端系统进行识别
  • md5,就是密码认证
  • trust,只验证主机,不验证用户名和密码
  • password,这个没去了解

使用

以下指令为输入psql进入PostgreSQL控制台输入

\?:
    打印PostgreSQL帮助
\h:
    打印SQL帮助
\c   :  
    可以通过\c databasename  切换数据库
\l    :
    数据库列表的详细信息
\q   :
    退出数据库
\d或\dt:
      该数据库下所有表的详细信息
\d  tablename:
      该表的表结构
\df 
    打印函数
\df+ partition_every_day
    打印partition_every_day的信息
\du
    列出所有用户

alter user postgres with password 'xxx'
    修改用户名密码 
      

以下指令在系统终端执行

# 创建数据库
createdb test_db

# 备份数据库,dumpall为备份所有库
pg_dump/pg_dumpall

# 备份结构
pg_dump --schema-only

# 执行sql文件

psql -f xxx.sql

schema

目前我无法说清楚这是什么东西,大概理解为是一种隔离机制吧,默认只有$userpublic,如果你的表有分区,那么你虽然在打印的时候可以看到,但你要drop分区的时候,则会提示找不到

# 显示Schema的搜索路径
SHOW search_path;

# 从上面的输出可以看出,默认没有partitions,我们加入,如果不加入,在drop table的时候,就会提示does not exist
SET search_path TO "$user",partitions,public;

# 可以drop了,CASCADE的作用是把关联的触发条件删除,这触发条件,其实就是什么数据应该插入此分区的,现在已经是2017了,自然不会有2015的数据需要插入,所以可以放心删除
drop table history_uint_2015_01_11 CASCADE;

# 当然也可以不用设置,直接加partitions前缀

drop table partitions.history_uint_2015_01_11 CASCADE;

性能

  • shared_buffers,缓存最近查询的数据页,设置为系统内存的25%,但不应该超过8G
  • effective_cache_size,单次查询可用缓存,如果是专门的PostgreSQL服务器,设置为系统总内存一半
  • work_mem,用于排序、表扫描、哈希关联等操作的最大内存量,如果是多用户并发进行简单查询,那么此设置很小也没有问题,如果执行复杂查询,可以调大
  • maintenance_work_mem,用于vaccum(回收内存和磁盘空间)操作的内存总量。不应该大于1G。

一些工具

pgAdmin

是PostgreSQL官方的图形界面管理客户端

  • 版本3可以管理PostgreSQL 8.4
  • 版本4可以管理PostgreSQL 9.x

pgsniff

从网络流量中提取SQL,测试支持9.3版本,8.4版本不支持

# 最后一个-,意思是输出到终端
pgsniff -d eth1 -l -

还有一个叫pgshark,没测试

书籍

  • PostgreSQL 9 Admin Cookbook,有中文版
  • PostgreSQL即学即用
  • PostgreSQL 9.0性能调校,一定不要看中文版

shadowsocket和ipset实现科学上网

1. 背景

我们有了专线接入的,而基于shadowsocket,网上的资料已经无法访问了,经过一翻摸索,终于又还原出来

2. 使用原有软件

上面专线版本,我们已经安装了一些软件,这里可以继续用

  • dnsmasq
  • pdnsd

3. shadowsocket配置

服务器端已经有了,这里讲下客户端,CentOS 6安装shadowsocket 3.x比较麻烦,这里安装2.x的

配置yum源

sudo cat > /etc/yum.repos.d/librehat-shadowsocks.repo <<EOF
[librehat-shadowsocks]
name=Copr repo for shadowsocks owned by librehat
baseurl=https://copr-be.cloud.fedoraproject.org/results/librehat/shadowsocks/epel-6-$basearch/
type=rpm-md
skip_if_unavailable=True
gpgcheck=1
gpgkey=https://copr-be.cloud.fedoraproject.org/results/librehat/shadowsocks/pubkey.gpg
repo_gpgcheck=0
enabled=1
enabled_metadata=1
EOF

安装

sudo yum install shadowsocks-libev

连接shadowsocket

# 下面需要以root身份运行,主要是ulimit
ulimit -SHn 65535
# -v参数为输出详细信息,在debug的时候有用
# 要用ss-redir才行,ss-local不支持iptables REDIRECT的方式
su - ssproxy bash -c "ss-redir -s ss_svr_ip -p 58000 -k abc_pass -m aes-256-cfb -l 1080 -b 0.0.0.0 -v"

4. 设置dnsmasq

sudo cat > /data/conf/dnsmasq/ss_kexue.conf <<EOF
server=/facebook.com/127.0.0.1#1053
ipset=/facebook.com/ss_kexue
EOF
# 另外需要修改dnsmasq.conf,让它包含ss_kexue.conf,并重启dnsmasq

5. 设置ipset

# 新增hash表
sudo /usr/sbin/ipset -N ss_kexue iphash
# 添加facebook的ip倒表里
sudo ipset add ss_kexue facebook_ip
# 从旧的hash表里移除
sudo ipset del kk_kexue facebook_ip

6. 设置iptables

# 需要写到配置文件
sudo iptables -t nat -A PREROUTING -p tcp -m set --match-set ss_kexue dst -j REDIRECT --to-port 1080
sudo iptables -A RH-Firewall-1-INPUT -s 192.168.0.0/16 -p tcp --dport 1080 -j ACCEPT
sudo iptables -A RH-Firewall-1-INPUT -s 172.16.28.0/23 -p tcp --dport 1080 -j ACCEPT

好了,现在应该可以了

参考资料

lsiutil设置磁盘带宽

1. 问题

DELL R410自带RAID卡是SAS6IR,机械盘接上去带宽是3G,而SSD,无论是三星还是闪迪,都只有1.5G,查看指令

./lsiutil -p1 -a 69,8,21,4,0,0

有问题的结果,其中1.5处为接SSD的端口

SAS1068E's links are 3.0 G, 3.0 G, 3.0 G, 1.5 G, off, off, off, off

2. 解决

2.1 方法一

换个RAID卡,如 PERC 6i或者H700,贵

2.2 方法二

换pcie口的SSD,价格是sata口的两倍,贵

2.3 方法三

可以通过lsiutil(要求Version 1.62或以上)设置磁盘的带宽,此机接了4个盘,所以,具体设置,请根据实际情况选择

[root@localhost src]# ./lsiutil -p1 -a 69

LSI Logic MPT Configuration Utility, Version 1.63, June 4, 2009

1 MPT Port found

     Port Name         Chip Vendor/Type/Rev    MPT Rev  Firmware Rev  IOC
 1.  /proc/mpt/ioc0    LSI Logic SAS1068E B3     105      00192f00     0

Main menu, select an option:  [1-99 or e/p/w or 0 to quit] 69

Seg/Bus/Dev/Fun    Board Name       Board Assembly   Board Tracer
 0   2   0   0     SAS6IR                                            

# 输入13进行操作
Main menu, select an option:  [1-99 or e/p/w or 0 to quit] 13  

# 以下3行,直接回车即可
SATA Maximum Queue Depth:  [0 to 255, default is 8] 
Device Missing Report Delay:  [0 to 2047, default is 0] 
Device Missing I/O Delay:  [0 to 255, default is 0] 

PhyNum  Link      MinRate  MaxRate  Initiator  Target    Port
   0    Enabled     1.5      3.0    Enabled    Disabled  Auto
   1    Enabled     1.5      3.0    Enabled    Disabled  Auto
   2    Enabled     1.5      3.0    Enabled    Disabled  Auto
   3    Enabled     1.5      3.0    Enabled    Disabled  Auto
   4    Disabled    1.5      3.0    Enabled    Disabled  Auto
   5    Disabled    1.5      3.0    Enabled    Disabled  Auto
   6    Disabled    1.5      3.0    Enabled    Disabled  Auto
   7    Disabled    1.5      3.0    Enabled    Disabled  Auto

# 此处选择3,是我们接SSD的位置
Select a Phy:  [0-7, 8=AllPhys, RETURN to quit] 3
# 直接回车
Link:  [0=Disabled, 1=Enabled, default is 1] 
# 设置最小带宽,关键操作,选择1
MinRate:  [0=1.5 Gbps, 1=3.0 Gbps, default is 0] 1
# 以下4行,直接回车
MaxRate:  [0=1.5 Gbps, 1=3.0 Gbps, default is 1] 
Initiator:  [0=Disabled, 1=Enabled, default is 1] 
Target:  [0=Disabled, 1=Enabled, default is 0] 
Port:  [0 to 7 for manual config, 8 for auto config, default is 8] 

PhyNum  Link      MinRate  MaxRate  Initiator  Target    Port
   0    Enabled     1.5      3.0    Enabled    Disabled  Auto
   1    Enabled     1.5      3.0    Enabled    Disabled  Auto
   2    Enabled     1.5      3.0    Enabled    Disabled  Auto
   # 注意看下面一行,MinRate为3.0代表设置正确
   3    Enabled     3.0      3.0    Enabled    Disabled  Auto
   4    Disabled    1.5      3.0    Enabled    Disabled  Auto
   5    Disabled    1.5      3.0    Enabled    Disabled  Auto
   6    Disabled    1.5      3.0    Enabled    Disabled  Auto
   7    Disabled    1.5      3.0    Enabled    Disabled  Auto

# 以下4行,直接回车
Select a Phy:  [0-7, 8=AllPhys, RETURN to quit] 
Persistence:  [0=Disabled, 1=Enabled, default is 1] 
Physical mapping:  [0=None, 1=DirectAttach, 2=EnclosureSlot, default is 2] 
Number of Target IDs to reserve:  [0 to 32, default is 8] 

# 输入0退出设置
Main menu, select an option:  [1-99 or e/p/w or 0 to quit] 0

经过以上设置,重启系统,再次确认,带宽已经变为3,cp大量小文件,性能也提升了50%

参考资料

https证书链

应同事要求,把git的地址修改成了https,应用后,浏览器访问是正常的,但git命令行访问时,偶尔会提示证书有问题:

Peer certificate cannot be authenticated with known CA certificates

遇到一两个反馈,就让他们禁用证书检查,或者改用ssh,但反馈多了,觉得这体验的确不够好,虽然安全和方便二者不可兼得,但我们这明明是Symantec颁发的权(mian)威(fei)证书,理论上不应该有问题才对。今天有空,就试着解决下。

犹记得(多么熟悉的三个字,其实是代表了年纪大)上次内网某https应用,有位同事用Chrome访问,死活都提示证书有问题,但其他人又正常,百思不得骑姐。后来是web同事发现并解决了问题,说是证书链不完整,去把中间证书下载回来,加到证书里就解决了。

本次估计也是,但怎么验证?如果是外网,直接用ssllabs_https_check,会打印完整证书链,将网站打印出来的证书和服务器上配置的进行对比,如果不一致,则证明少了中间证书。但我们是内网啊,这网站无法访问到,这时只能放狗+祭出openssl神器了。

查看证书

openssl s_client -showcerts -connect git.example.com:443 -servername git.example.com

Certificate chain节内容,如果下面的开头和结束只出现1次,则代表证书不完整

-----BEGIN CERTIFICATE-----
证书内容
-----END CERTIFICATE-----

至此,我们证实了只有1个证书了,那怎么加入中间证书?

  • 颁发机构一般有提供的
  • 如果当时的压缩包已经不知去向,如果你有2个https应用都是同一个CA颁发的,那么可以用上面openssl的方法去另外一个https应用拿

修改httpd配置,此处列出完整配置

<VirtualHost *:443>                                                                                                                 
    ServerName git.example.com
    CustomLog logs/git.example.com_access.log common
    SSLEngine on
    SSLCertificateFile "/etc/httpd/cert/git.example.com.crt"
    SSLCertificateKeyFile "/etc/httpd/cert/git.example.com.key"
    # 此配置为本次新添加
    SSLCertificateChainFile "/etc/httpd/cert/git.example.com_root_bundle.crt"
    SSLProtocol all -SSLv2 -SSLv3
    ProxyPreserveHost On
    ProxyRequests Off
<Location />
    ProxyPass http://127.0.0.1:8088/
    ProxyPassReverse http://127.0.0.1:8088/
</Location>
</VirtualHost>

最后优雅地重新加载配置

sudo /etc/init.d/httpd graceful

再次用openssl检测,此时服务器端已经返回了完整的证书链,git也正常了。BTW,https水太深了。

为什么浏览器正常呢?因为浏览器会递归请求证书,而递归请求其实很慢,所以也就有了- OCSP Stapling,简单说就是,服务器去帮你把中间证书请求了,一起返回给客户端。

通过XFW访问MySQL无返回的问题

1. 症状概述

最近我们新加了一台机,在xx的机器上访问MySQL,可以正常连上,执行show databases等马上有结果的指令,可以正常返回,但如果表记录又上千万,select count(1) from xxx的时候,则会没有返回。

犹记得几个月前,我们在C机有个脚本通过http方式提交数据到远程机器S,S将结果存到MySQL,同样也是经过XFW,由于处理时间比较长(大概1分钟吧),C这边一直卡住, 因为没有收到S的返回,但在S通过tcpdump抓包发现,S是有返回的,而且尝试多次返回,说明什么?连接断了呗,但C和S都感知不到这个断开的操作,感觉是XFW检测到此数据连接没有数据发送,把它断了。

最后是在S上加了索引,使得存入MySQL操作秒返回,才解决了这个问题。天下武功,唯快不破,和XFW斗亦如是。

2. 排查过程

2.1 MySQL版本

首先说明下本次新加的机器,我们升级MySQL到5.7,以往的机器都是5.5,那是不是5.7的问题呢?

2.1.1 SUSE系统

以下都是在C(SUSE系统)上连接:

1.连MySQL 5.7(A)时,实际早就挂了(服务器端top看MySQL的CPU占用),但客户端这边还是阻塞状态,过了很久才显示:

ERROR 2013 (HY000): Lost connection to MySQL server during query

2.连MySQL 5.5时(B),偶尔会卡住,在服务器端抓包,没有数据过来,意味着实际上还没有连到过去,但如果能连过去,一般可以获取到返回

但我们后来通过tcpdump抓包发现,第一点的结论其实是错的,因为在MySQL的CPU占用降下去之后,从3306端口是有数据发出去的,也就是并没有挂,查询有结果,并有发出去,但客户端没有收到,C端等到超时了才退出并报错

2.1.2 CentOS系统

刚好有1台CentOS系统的机器,测试下,结果和上面SUSE系统一样

2.2 TCP的问题

2.2.1 SUSE系统

基于上面一开始提到的http提交数据的问题, 我们又感觉是tcp的问题,怎么验证?我们在A上开了ssh的密码登录,然后在C上ssh到A,然后A上执行mysql连接,再进行查询,结果发现,也是没有返回,最后提示

Disconnected; connection lost (Connection closed.).

那很明显了,就是tcp连接断开了

2.2.2 CentOS系统

上网查询发现CentOS上,ssh有一个维持心跳的参数,于是试了下

# 5s发送一次心跳包
ssh -o ServerAliveInterval=5 -pxxx username@A

通过上面的参数连接后,发现此时可以正常获取结果了

记得关闭A上的ssh密码登录

3. 解决

3.1 libkeepalive.so

通过以上测试,发现问题出现在tcp,那就有解决的方向了,但MySQL是没有这种维持心跳的参数的,但我们找到了一个libkeepalive.so的软件,通过它封装下,可以设置应用层的心跳,那就不用设置内核参数了

LD_PRELOAD=libkeepalive.so KEEPIDLE=5 KEEPCNT=3 KEEPINTVL=5 mysql  -h xxx
  • KEEPIDLE,空闲多久就开始发送心跳,此处为5
  • KEEPINTVL,多久发送一次心跳,此处为5
  • KEEPCNT,一共尝试多少次,此处为3次,如果3次都发送不成功,则认为失败,不再尝试

但比较遗憾, 没有生效,无论是SUSE还是CentOS

3.2 内核参数

我们在C端把内核参数设置下,感觉是比较重(需要修改内核参数)的解决办法。

# 多久探测一次,此处为5s
sysctl -w net.ipv4.tcp_keepalive_intvl=5
# 空闲多久后开始探测,此处为5s
sysctl -w net.ipv4.tcp_keepalive_time=5

通过上述设置,再直接用mysql连接就正常了。

以下是系统默认值,net.ipv4.tcp_keepalive_probes的意思是,每「net.ipv4.tcp_keepalive_time 」间隔,就探测一次,探测「net.ipv4.tcp_keepalive_probes」次,此处为9次,就停止探测

net.ipv4.tcp_keepalive_time = 30
net.ipv4.tcp_keepalive_probes = 9
net.ipv4.tcp_keepalive_intvl = 5

为避免影响其他程序,建议只在测试时通过命令行设置,在测试完后,通过sysctl -p还原为系统默认配置

其他

2017年七月
« 6月    
 12
3456789
10111213141516
17181920212223
24252627282930
31