本文共 18510 字，大约阅读时间需要 61 分钟。
pg92@db-172-16-3-39-> ssh-keygen -t rsa
pg92@db-172-16-3-39-> cd .sshpg92@db-172-16-3-39-> lltotal 8.0K-rw------- 1 postgres postgres 887 May 23 07:32 id_rsa-rw-r--r-- 1 postgres postgres 246 May 23 07:32 id_rsa.pub查看公钥内容, 将要拷贝到数据库服务器上.
pg92@db-172-16-3-39-> cat id_rsa.pub ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEAzRL55hHqAqW8HVQ54fpmZ76QEU6NP/dSdu56bNf61+bVDHl/VHEAlQOAdYI3eCsxCv3BmWDiCFR++LjmnRDU7DvTbWZlKk6xmxlWr9uWgHyXbNLrLSqXm8SapS86ATxTxOvT2w5kEgszFtsgoomrCJhQaVLQFU8geL6IXFNr5/g4nK1R2GbQH4eoBFE1a0eh61OhY6+Jq0eaKhZqaLI+Ed8Q5Ce5JjyG8DGhzY2S63OFpncCN2qTjjh8Vhl4SlwF/XZmCZILEfKHUVCi/jKnC068yfcvNl5QmSw2FlELpWFkoxNiCGarSpgXTC3CigBuKmcjR+z7gbHrhbSgnpM4fQ== pg92@db-172-16-3-39.sky-mobi.com在数据库服务器上写入公钥.
[root@db-172-16-3-33 ~]# su - pg93pg93@db-172-16-3-33-> cd .ssh-bash: cd: .ssh: No such file or directorypg93@db-172-16-3-33-> mkdir .sshpg93@db-172-16-3-33-> cd .sshpg93@db-172-16-3-33-> vi authorized_keys
pg93@db-172-16-3-33-> cd ~pg93@db-172-16-3-33-> chmod 700 ~pg93@db-172-16-3-33-> chmod 700 .sshpg93@db-172-16-3-33-> chmod 400 .ssh/authorized_keys验证公钥是否生效, 不需要输入密码则正常.
pg92@db-172-16-3-39-> ssh pg93@172.16.3.33 dateThu May 23 07:37:14 CST 2013在测试机上创建连接到数据库服务器的ssh隧道,
pg92@db-172-16-3-39-> ssh -o CompressionLevel=9 -p 22 -CqTfnN -L *:17100:127.0.0.1:1999 pg93@172.16.3.33pg92@db-172-16-3-39-> netstat -anp|grep 17100(Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.)tcp 0 0 0.0.0.0:17100 0.0.0.0:* LISTEN 12954/ssh# 测试通过隧道连接数据库是否正常.
pg92@db-172-16-3-39-> psql -h 127.0.0.1 -p 17100 -U postgres -d digoalpsql (9.2beta1, server 9.3devel)WARNING: psql version 9.2, server version 9.3. Some psql features might not work.SSL connection (cipher: RC4-SHA, bits: 128)Type "help" for help.digoal=#
pg93@db-172-16-3-33-> cd $PGDATApg93@db-172-16-3-33-> vi pg_hba.conf hostnossl all all 127.0.0.1/32 trustpg_ctl reload再次连接, 无加密.
pg92@db-172-16-3-39-> psql -h 127.0.0.1 -p 17100 -U postgres -d digoalpsql (9.2beta1, server 9.3devel)WARNING: psql version 9.2, server version 9.3. Some psql features might not work.Type "help" for help.digoal=#
pg92@db-172-16-3-39-> pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17100 -U postgres -T 60 -c 16 -j 4 digoaltransaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 16number of threads: 4duration: 60 snumber of transactions actually processed: 1008287tps = 16804.427360 (including connections establishing)tps = 16818.105936 (excluding connections establishing)关闭隧道压缩, 再次测试 :
pg92@db-172-16-3-39-> ps -ewf|grep sshroot 949 1 0 Mar21 ? 00:00:00 /usr/sbin/sshdroot 7681 949 0 May22 ? 00:00:00 sshd: root@pts/0 root 9022 949 0 May22 ? 00:00:00 sshd: root@pts/2 pg92 12954 1 18 07:57 ? 00:00:47 ssh -o CompressionLevel=9 -p 22 -CqTfnN -L *:17100:127.0.0.1:1999 pg93@172.16.3.33pg92 12984 12904 0 08:01 pts/0 00:00:00 grep sshpg92@db-172-16-3-39-> kill 12954pg92@db-172-16-3-39-> ssh -p 22 -o "Compression no" -qTfnN -L *:17100:127.0.0.1:1999 pg93@172.16.3.33测试结果 :
pg92@db-172-16-3-39-> pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17100 -U postgres -T 60 -c 16 -j 4 digoaltransaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 16number of threads: 4duration: 60 snumber of transactions actually processed: 434617tps = 7241.081323 (including connections establishing)tps = 7247.051105 (excluding connections establishing)开启压缩, 并更改加密暗语为blowfish:
pg92@db-172-16-3-39-> ps -ewf|grep sshroot 949 1 0 Mar21 ? 00:00:00 /usr/sbin/sshdroot 7681 949 0 May22 ? 00:00:00 sshd: root@pts/0 root 9022 949 0 May22 ? 00:00:00 sshd: root@pts/2 pg92 13051 1 11 08:04 ? 00:00:18 ssh -p 22 -o Compression=no -qTfnN -L *:17100:127.0.0.1:1999 pg93@172.16.3.33pg92 13067 12904 0 08:06 pts/0 00:00:00 grep sshpg92@db-172-16-3-39-> kill 13051pg92@db-172-16-3-39-> ssh -o CompressionLevel=9 -c blowfish -p 22 -CqTfnN -L *:17100:127.0.0.1:1999 pg93@172.16.3.33测试结果 :
pg92@db-172-16-3-39-> pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17100 -U postgres -T 60 -c 16 -j 4 digoaltransaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 16number of threads: 4duration: 60 snumber of transactions actually processed: 1039471tps = 17323.172100 (including connections establishing)tps = 17338.330403 (excluding connections establishing)
ssh -o CompressionLevel=9 -c blowfish -p 22 -CqTfnN -L *:17100:127.0.0.1:1999 pg93@172.16.3.33ssh -o CompressionLevel=9 -c blowfish -p 22 -CqTfnN -L *:17101:127.0.0.1:1999 pg93@172.16.3.33ssh -o CompressionLevel=9 -c blowfish -p 22 -CqTfnN -L *:17102:127.0.0.1:1999 pg93@172.16.3.33ssh -o CompressionLevel=9 -c blowfish -p 22 -CqTfnN -L *:17103:127.0.0.1:1999 pg93@172.16.3.33ssh -o CompressionLevel=9 -c blowfish -p 22 -CqTfnN -L *:17104:127.0.0.1:1999 pg93@172.16.3.33ssh -o CompressionLevel=9 -c blowfish -p 22 -CqTfnN -L *:17105:127.0.0.1:1999 pg93@172.16.3.33ssh -o CompressionLevel=9 -c blowfish -p 22 -CqTfnN -L *:17106:127.0.0.1:1999 pg93@172.16.3.33ssh -o CompressionLevel=9 -c blowfish -p 22 -CqTfnN -L *:17107:127.0.0.1:1999 pg93@172.16.3.33
pg92@db-172-16-3-39-> ps -ewf|grep ssh|grep -v greproot 949 1 0 Mar21 ? 00:00:00 /usr/sbin/sshdroot 7681 949 0 May22 ? 00:00:00 sshd: root@pts/0 root 9022 949 0 May22 ? 00:00:00 sshd: root@pts/2 pg92 13204 1 0 08:34 ? 00:00:00 ssh -o CompressionLevel=9 -c blowfish -p 22 -CqTfnN -L *:17100:127.0.0.1:1999 pg93@172.16.3.33pg92 13210 1 0 08:34 ? 00:00:00 ssh -o CompressionLevel=9 -c blowfish -p 22 -CqTfnN -L *:17101:127.0.0.1:1999 pg93@172.16.3.33pg92 13216 1 0 08:34 ? 00:00:00 ssh -o CompressionLevel=9 -c blowfish -p 22 -CqTfnN -L *:17102:127.0.0.1:1999 pg93@172.16.3.33pg92 13222 1 0 08:34 ? 00:00:00 ssh -o CompressionLevel=9 -c blowfish -p 22 -CqTfnN -L *:17103:127.0.0.1:1999 pg93@172.16.3.33pg92 13228 1 0 08:34 ? 00:00:00 ssh -o CompressionLevel=9 -c blowfish -p 22 -CqTfnN -L *:17104:127.0.0.1:1999 pg93@172.16.3.33pg92 13234 1 0 08:34 ? 00:00:00 ssh -o CompressionLevel=9 -c blowfish -p 22 -CqTfnN -L *:17105:127.0.0.1:1999 pg93@172.16.3.33pg92 13240 1 0 08:34 ? 00:00:00 ssh -o CompressionLevel=9 -c blowfish -p 22 -CqTfnN -L *:17106:127.0.0.1:1999 pg93@172.16.3.33pg92 13246 1 0 08:34 ? 00:00:00 ssh -o CompressionLevel=9 -c blowfish -p 22 -CqTfnN -L *:17107:127.0.0.1:1999 pg93@172.16.3.33测试 :
pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17100 -U postgres -T 60 -c 2 -j 1 digoal &pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17101 -U postgres -T 60 -c 2 -j 1 digoal &pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17102 -U postgres -T 60 -c 2 -j 1 digoal &pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17103 -U postgres -T 60 -c 2 -j 1 digoal &pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17104 -U postgres -T 60 -c 2 -j 1 digoal &pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17105 -U postgres -T 60 -c 2 -j 1 digoal &pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17106 -U postgres -T 60 -c 2 -j 1 digoal &pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17107 -U postgres -T 60 -c 2 -j 1 digoal &pg92@db-172-16-3-39-> jobs[1] Running pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17100 -U postgres -T 60 -c 2 -j 1 digoal &[2] Running pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17101 -U postgres -T 60 -c 2 -j 1 digoal &[3] Running pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17102 -U postgres -T 60 -c 2 -j 1 digoal &[4] Running pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17103 -U postgres -T 60 -c 2 -j 1 digoal &[5] Running pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17104 -U postgres -T 60 -c 2 -j 1 digoal &[6] Running pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17105 -U postgres -T 60 -c 2 -j 1 digoal &[7]- Running pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17106 -U postgres -T 60 -c 2 -j 1 digoal &[8]+ Running pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17107 -U postgres -T 60 -c 2 -j 1 digoal &测试结果 :
pg92@db-172-16-3-39-> transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 1duration: 60 snumber of transactions actually processed: 221246tps = 3687.366100 (including connections establishing)tps = 3693.281275 (excluding connections establishing)transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 1duration: 60 snumber of transactions actually processed: 224540tps = 3742.294039 (including connections establishing)tps = 3745.909116 (excluding connections establishing)transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 1duration: 60 snumber of transactions actually processed: 222014tps = 3700.200155 (including connections establishing)tps = 3703.833274 (excluding connections establishing)transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 1duration: 60 snumber of transactions actually processed: 225675tps = 3761.186749 (including connections establishing)tps = 3765.324960 (excluding connections establishing)transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 1duration: 60 snumber of transactions actually processed: 226583tps = 3776.300569 (including connections establishing)tps = 3782.679035 (excluding connections establishing)transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 1duration: 60 snumber of transactions actually processed: 230229tps = 3837.095577 (including connections establishing)tps = 3841.695622 (excluding connections establishing)transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 1duration: 60 snumber of transactions actually processed: 226564tps = 3775.985231 (including connections establishing)tps = 3782.328437 (excluding connections establishing)transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 1duration: 60 snumber of transactions actually processed: 218551tps = 3642.426638 (including connections establishing)tps = 3648.666129 (excluding connections establishing)
ssh -o "Compression no" -c blowfish -p 22 -qTfnN -L *:17100:127.0.0.1:1999 pg93@172.16.3.33ssh -o "Compression no" -c blowfish -p 22 -qTfnN -L *:17101:127.0.0.1:1999 pg93@172.16.3.33ssh -o "Compression no" -c blowfish -p 22 -qTfnN -L *:17102:127.0.0.1:1999 pg93@172.16.3.33ssh -o "Compression no" -c blowfish -p 22 -qTfnN -L *:17103:127.0.0.1:1999 pg93@172.16.3.33ssh -o "Compression no" -c blowfish -p 22 -qTfnN -L *:17104:127.0.0.1:1999 pg93@172.16.3.33ssh -o "Compression no" -c blowfish -p 22 -qTfnN -L *:17105:127.0.0.1:1999 pg93@172.16.3.33ssh -o "Compression no" -c blowfish -p 22 -qTfnN -L *:17106:127.0.0.1:1999 pg93@172.16.3.33ssh -o "Compression no" -c blowfish -p 22 -qTfnN -L *:17107:127.0.0.1:1999 pg93@172.16.3.33pg92@db-172-16-3-39-> ps -ewf|grep ssh|grep -v greproot 949 1 0 Mar21 ? 00:00:00 /usr/sbin/sshdroot 7681 949 0 May22 ? 00:00:00 sshd: root@pts/0 root 9022 949 0 May22 ? 00:00:00 sshd: root@pts/2 pg92 13294 1 0 08:38 ? 00:00:00 ssh -o Compression no -c blowfish -p 22 -qTfnN -L *:17100:127.0.0.1:1999 pg93@172.16.3.33pg92 13300 1 0 08:38 ? 00:00:00 ssh -o Compression no -c blowfish -p 22 -qTfnN -L *:17101:127.0.0.1:1999 pg93@172.16.3.33pg92 13306 1 0 08:38 ? 00:00:00 ssh -o Compression no -c blowfish -p 22 -qTfnN -L *:17102:127.0.0.1:1999 pg93@172.16.3.33pg92 13312 1 0 08:38 ? 00:00:00 ssh -o Compression no -c blowfish -p 22 -qTfnN -L *:17103:127.0.0.1:1999 pg93@172.16.3.33pg92 13318 1 0 08:38 ? 00:00:00 ssh -o Compression no -c blowfish -p 22 -qTfnN -L *:17104:127.0.0.1:1999 pg93@172.16.3.33pg92 13324 1 0 08:38 ? 00:00:00 ssh -o Compression no -c blowfish -p 22 -qTfnN -L *:17105:127.0.0.1:1999 pg93@172.16.3.33pg92 13330 1 0 08:38 ? 00:00:00 ssh -o Compression no -c blowfish -p 22 -qTfnN -L *:17106:127.0.0.1:1999 pg93@172.16.3.33pg92 13336 1 0 08:38 ? 00:00:00 ssh -o Compression no -c blowfish -p 22 -qTfnN -L *:17107:127.0.0.1:1999 pg93@172.16.3.33测试 :
pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17100 -U postgres -T 60 -c 2 -j 1 digoal &pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17101 -U postgres -T 60 -c 2 -j 1 digoal &pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17102 -U postgres -T 60 -c 2 -j 1 digoal &pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17103 -U postgres -T 60 -c 2 -j 1 digoal &pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17104 -U postgres -T 60 -c 2 -j 1 digoal &pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17105 -U postgres -T 60 -c 2 -j 1 digoal &pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17106 -U postgres -T 60 -c 2 -j 1 digoal &pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17107 -U postgres -T 60 -c 2 -j 1 digoal &pg92@db-172-16-3-39-> jobs[1] Running pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17100 -U postgres -T 60 -c 2 -j 1 digoal &[2] Running pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17101 -U postgres -T 60 -c 2 -j 1 digoal &[3] Running pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17102 -U postgres -T 60 -c 2 -j 1 digoal &[4] Running pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17103 -U postgres -T 60 -c 2 -j 1 digoal &[5] Running pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17104 -U postgres -T 60 -c 2 -j 1 digoal &[6] Running pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17105 -U postgres -T 60 -c 2 -j 1 digoal &[7]- Running pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17106 -U postgres -T 60 -c 2 -j 1 digoal &[8]+ Running pgbench -M prepared -n -f ./sel.sql -h 127.0.0.1 -p 17107 -U postgres -T 60 -c 2 -j 1 digoal &测试结果 :
pg92@db-172-16-3-39-> transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 1duration: 60 snumber of transactions actually processed: 231898tps = 3864.904506 (including connections establishing)tps = 3871.202723 (excluding connections establishing)transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 1duration: 60 snumber of transactions actually processed: 234955tps = 3915.837110 (including connections establishing)tps = 3924.836512 (excluding connections establishing)transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 1duration: 60 snumber of transactions actually processed: 241359tps = 4022.581549 (including connections establishing)tps = 4032.042374 (excluding connections establishing)transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 1duration: 60 snumber of transactions actually processed: 237272tps = 3954.495436 (including connections establishing)tps = 3960.789268 (excluding connections establishing)transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 1duration: 60 snumber of transactions actually processed: 235486tps = 3924.681501 (including connections establishing)tps = 3933.783948 (excluding connections establishing)transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 1duration: 60 snumber of transactions actually processed: 245445tps = 4090.663073 (including connections establishing)tps = 4097.263762 (excluding connections establishing)transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 1duration: 60 snumber of transactions actually processed: 233128tps = 3885.425157 (including connections establishing)tps = 3889.080854 (excluding connections establishing)transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 1duration: 60 snumber of transactions actually processed: 238585tps = 3976.336212 (including connections establishing)tps = 3982.943184 (excluding connections establishing)
-c cipher_spec Selects the cipher specification for encrypting the session. Protocol version 1 allows specification of a single cipher. The supported values are “3des”, “blowfish”, and “des”. 3des (triple-des) is an encrypt-decrypt-encrypt triple with three different keys. It is believed to be secure. blowfish is a fast block cipher; it appears very secure and is much faster than 3des. des is only supported in the ssh client for interoperability with legacy protocol 1 implementa- tions that do not support the 3des cipher. Its use is strongly discouraged due to cryptographic weak- nesses. The default is “3des”. For protocol version 2, cipher_spec is a comma-separated list of ciphers listed in order of preference. The supported ciphers are: 3des-cbc, aes128-cbc, aes192-cbc, aes256-cbc, aes128-ctr, aes192-ctr, aes256-ctr, arcfour128, arcfour256, arcfour, blowfish-cbc, and cast128-cbc. The default is: aes128-cbc,3des-cbc,blowfish-cbc,cast128-cbc,arcfour128, arcfour256,arcfour,aes192-cbc,aes256-cbc,aes128-ctr, aes192-ctr,aes256-ctr
转载地址：http://yjmxl.baihongyu.com/