Chinaunix首页 | 论坛 | 博客

小桥河西

首页　| 　博文目录　| 　关于我

skykiker

  • 博客访问： 2951509
  • 博文数量： 199
  • 博客积分： 1400
  • 博客等级： 上尉
  • 技术积分： 4126
  • 用 户 组： 普通用户
  • 注册时间： 2008-07-06 19:06
个人简介

半个PostgreSQL DBA，热衷于数据库相关的技术。我的ppt分享https://pan.baidu.com/s/1eRQsdAa https://github.com/chenhuajun https://chenhuajun.github.io

文章分类

全部博文（199）

文章存档

2020年（5）

2019年（1）

2018年（12）

2017年（23）

2016年（43）

2015年（51）

2014年（27）

2013年（21）

2011年（1）

2010年（4）

2009年（5）

2008年（6）

我的朋友
最近访客
推荐博文
相关博文
青云云主机的pgbench测试

分类： Mysql/postgreSQL

2015-06-21 03:04:41

在青云的云主机（不是RDS）上做了一个简单的PostgreSQL的pgbench测试。结果如下

1. 环境

VM:1 core 1G mem的低端云主机配置
PostgresQL9.4.2(shared_buffers = 128MB,其它都是默认值)

点击(此处)折叠或打开

  1. [root@xxxxxxx chenhj]# cat /proc/cpuinfo
  2. processor : 0
  3. vendor_id : GenuineIntel
  4. cpu family : 6
  5. model : 44
  6. model name : Westmere E56xx/L56xx/X56xx (Nehalem-C)
  7. stepping : 1
  8. microcode : 1
  9. cpu MHz : 2099.998
  10. cache size : 4096 KB
  11. physical id : 0
  12. siblings : 1
  13. core id : 0
  14. cpu cores : 1
  15. apicid : 0
  16. initial apicid : 0
  17. fpu : yes
  18. fpu_exception : yes
  19. cpuid level : 11
  20. wp : yes
  21. flags : fpu de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 syscall nx lm constant_tsc up rep_good unfair_spinlock pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 x2apic popcnt aes hypervisor lahf_lm
  22. bogomips : 4199.99
  23. clflush size : 64
  24. cache_alignment : 64
  25. address sizes : 40 bits physical, 48 bits virtual
  26. power management:

2. pgbench测试

初始化时把scale因子设成100，数据库的大小大概是1.5GB，超过VM内存大小，所以后面的测试会考验IO能力。

2.1 SELECT only测试

点击(此处)折叠或打开

  1. [chenhj@i-yh5e8qjy ~]$ pgbench postgres -n -S -T 20
  2. transaction type: SELECT only
  3. scaling factor: 100
  4. query mode: simple
  5. number of clients: 1
  6. number of threads: 1
  7. duration: 20 s
  8. number of transactions actually processed: 31586
  9. latency average: 0.633 ms
  10. tps = 1579.263361 (including connections establishing)
  11. tps = 1579.519711 (excluding connections establishing)

  12.
  13. [chenhj@i-yh5e8qjy ~]$ pgbench postgres -n -S -c 40 -T 20
  14. transaction type: SELECT only
  15. scaling factor: 100
  16. query mode: simple
  17. number of clients: 40
  18. number of threads: 1
  19. duration: 20 s
  20. number of transactions actually processed: 50851
  21. latency average: 15.732 ms
  22. tps = 2537.321342 (including connections establishing)
  23. tps = 2552.737465 (excluding connections establishing)
第1遍测试时，可能tps很低，需要再测几次，取数据预热后的结果。

单并发时的top结果:

点击(此处)折叠或打开

  1. [chenhj@xxxxxx ~]$ top
  2. top - 00:58:42 up 14 days, 10:17, 2 users, load average: 0.30, 0.10, 0.05
  3. Tasks: 91 total, 1 running, 90 sleeping, 0 stopped, 0 zombie
  4. Cpu(s): 5.7%us, 3.4%sy, 0.0%ni, 0.0%id, 90.6%wa, 0.0%hi, 0.3%si, 0.0%st
  5. Mem: 1020224k total, 953392k used, 66832k free, 102380k buffers
  6. Swap: 1048572k total, 6856k used, 1041716k free, 559156k cached
  8. PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
  9. 26080 chenhj 20 0 257m 45m 44m D 5.7 4.5 0:00.84 postgres
  10. 16 root 20 0 0 0 0 S 0.7 0.0 0:26.19 kblockd/0
  11. 30 root 20 0 0 0 0 S 0.7 0.0 0:07.86 kswapd0
  12. 26078 chenhj 20 0 13156 1024 864 S 0.7 0.1 0:00.09 pgbench
  13. 644 root 20 0 82012 496 432 S 0.3 0.0 17:16.78 gapd
  14. 774 dbus 20 0 21536 828 628 S 0.3 0.1 3:46.44 dbus-daemon
  15. 26086 root 20 0 99340 4296 3212 S 0.3 0.4 0:00.05 sshd
  16. 1 root 20 0 19360 884 708 S 0.0 0.1 0:24.92 init
  17. ...

40并发时的top结果:

点击(此处)折叠或打开

  1. [chenhj@xxxxxx ~]$ top
  2. top - 01:06:13 up 14 days, 10:24, 3 users, load average: 11.31, 2.63, 0.88
  3. Tasks: 136 total, 2 running, 134 sleeping, 0 stopped, 0 zombie
  4. Cpu(s): 53.0%us, 23.7%sy, 0.0%ni, 0.0%id, 20.0%wa, 0.0%hi, 3.3%si, 0.0%st
  5. Mem: 1020224k total, 951204k used, 69020k free, 80936k buffers
  6. Swap: 1048572k total, 6796k used, 1041776k free, 695264k cached
  8. PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
  9. 26634 chenhj 20 0 14532 1488 864 R 6.9 0.1 0:01.09 pgbench
  10. 26648 chenhj 20 0 257m 29m 28m S 2.3 3.0 0:00.27 postgres
  11. 26636 chenhj 20 0 257m 31m 30m S 2.0 3.2 0:00.30 postgres
  12. 26637 chenhj 20 0 257m 31m 30m S 2.0 3.1 0:00.29 postgres
  13. 26638 chenhj 20 0 257m 31m 30m D 2.0 3.1 0:00.28 postgres
  14. 26639 chenhj 20 0 257m 31m 30m S 2.0 3.2 0:00.28 postgres
  15. 26640 chenhj 20 0 257m 30m 30m S 2.0 3.1 0:00.27 postgres
  16. ...

2.2 TPC-B测试

TPC-B测试的结果和SELECT only类似，单并发的瓶颈在IO+CPU，多并发的瓶颈在CPU。

点击(此处)折叠或打开

  1. [chenhj@xxxxxx ~]$ pgbench postgres -n -T 20
  2. transaction type: TPC-B (sort of)
  3. scaling factor: 100
  4. query mode: simple
  5. number of clients: 1
  6. number of threads: 1
  7. duration: 20 s
  8. number of transactions actually processed: 4494
  9. latency average: 4.450 ms
  10. tps = 223.468810 (including connections establishing)
  11. tps = 223.499750 (excluding connections establishing)

  13. [chenhj@xxxxxx ~]$ pgbench postgres -n -c 40 -T 20
  14. transaction type: TPC-B (sort of)
  15. scaling factor: 100
  16. query mode: simple
  17. number of clients: 40
  18. number of threads: 1
  19. duration: 20 s
  20. number of transactions actually processed: 8938
  21. latency average: 89.505 ms
  22. tps = 445.559512 (including connections establishing)
  23. tps = 447.203517 (excluding connections establishing)
以下是测试期间的top结果
单并发时的top结果 ：

点击(此处)折叠或打开

  1. top - 01:08:23 up 14 days, 10:26, 3 users, load average: 1.52, 1.77, 0.79
  2. Tasks: 98 total, 1 running, 97 sleeping, 0 stopped, 0 zombie
  3. Cpu(s): 21.8%us, 7.8%sy, 0.0%ni, 30.7%id, 39.2%wa, 0.0%hi, 0.3%si, 0.0%st
  4. Mem: 1020224k total, 953208k used, 67016k free, 81232k buffers
  5. Swap: 1048572k total, 6796k used, 1041776k free, 747164k cached
  7. PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
  8. 26866 chenhj 20 0 257m 56m 55m D 24.0 5.7 0:02.25 postgres
  9. 26864 chenhj 20 0 13156 1036 864 S 3.3 0.1 0:00.31 pgbench
  10. ...



40并发时的top结果: 

点击(此处)折叠或打开

  1. top - 01:09:41 up 14 days, 10:28, 3 users, load average: 3.08, 1.95, 0.92
  2. Tasks: 137 total, 1 running, 136 sleeping, 0 stopped, 0 zombie
  3. Cpu(s): 55.2%us, 14.7%sy, 0.0%ni, 0.3%id, 28.4%wa, 0.0%hi, 1.3%si, 0.0%st
  4. Mem: 1020224k total, 943184k used, 77040k free, 81356k buffers
  5. Swap: 1048572k total, 7584k used, 1040988k free, 684020k cached
  7. PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
  8. 26968 chenhj 20 0 14536 1516 864 S 6.3 0.1 0:00.53 pgbench
  9. 26326 root 20 0 177m 9.9m 3784 S 2.0 1.0 0:07.30 iotop
  10. 26970 chenhj 20 0 257m 14m 13m S 2.0 1.4 0:00.16 postgres
  11. 26971 chenhj 20 0 257m 13m 12m S 1.7 1.4 0:00.14 postgres
  12. 26972 chenhj 20 0 257m 13m 12m S 1.7 1.4 0:00.13 postgres
  13. 26974 chenhj 20 0 257m 13m 12m S 1.7 1.4 0:00.13 postgres
  14. 26975 chenhj 20 0 257m 13m 12m S 1.7 1.4 0:00.13 postgres
  15. ...


3. 个人PC虚拟机上的对比测试

下面在我个人PC的虚拟机上做个对比测试

3.1 环境

宿主机
  CPU:AMD Athlon II X4 640 3.0GHz
  MEM:6G
  OS:Win7 64bit
  虚拟机所在存储:Apacer A S510S 128GB
虚拟机
  CPU：4 core
  MEM: 2G
  OS:CentOS release 6.5 (Final)
  PostgreSQL:9.3.4(shared_buffers = 128MB,其它都是默认值)

3.2 pgbench测试

SELECT only测试

点击(此处)折叠或打开

  1. [chenhj@node1 ~]$ pgbench pgbench -n -S -T 20
  2. transaction type: SELECT only
  3. scaling factor: 100
  4. query mode: simple
  5. number of clients: 1
  6. number of threads: 1
  7. duration: 20 s
  8. number of transactions actually processed: 10727
  9. latency average: 1.864 ms
  10. tps = 536.303234 (including connections establishing)
  11. tps = 536.414343 (excluding connections establishing)

  12.
  13. [chenhj@node1 ~]$ pgbench pgbench -n -S -c 40 -T 20
  14. transaction type: SELECT only
  15. scaling factor: 100
  16. query mode: simple
  17. number of clients: 40
  18. number of threads: 1
  19. duration: 20 s
  20. number of transactions actually processed: 65291
  21. latency average: 12.253 ms
  22. tps = 3261.046820 (including connections establishing)
  23. tps = 3303.491893 (excluding connections establishing)
在这里不管单并发还是40并发，性能瓶颈都在CPU,SSD对这点IOPS表示毫无压力,40并发的tps高于单并发的原因在于可以充分利用4个core。
以下是测试过程中的top的结果
单并发时的top结果:

点击(此处)折叠或打开

  1. [root@node1 ~]# top
  2. top - 02:17:52 up 1 day, 15:50, 4 users, load average: 0.22, 0.06, 0.01
  3. Tasks: 153 total, 2 running, 151 sleeping, 0 stopped, 0 zombie
  4. Cpu(s): 3.2%us, 11.2%sy, 0.0%ni, 84.3%id, 1.3%wa, 0.0%hi, 0.0%si, 0.0%st
  5. Mem: 1921956k total, 1847408k used, 74548k free, 4216k buffers
  6. Swap: 2064376k total, 508k used, 2063868k free, 1701720k cached
  8. PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
  9. 19424 chenhj 20 0 257m 105m 104m R 57.1 5.6 0:06.69 postgres
  10. 19422 chenhj 20 0 13156 968 816 S 18.6 0.1 0:02.07 pgbench
  11. 37 root 20 0 0 0 0 S 2.3 0.0 0:02.41 kblockd/3
  12. 17607 chenhj 20 0 256m 5116 4672 S 0.7 0.3 0:01.91 postgres
  13. 59 root 20 0 0 0 0 S 0.3 0.0 0:26.95 kswapd0
  14. 1 root 20 0 19364 752 540 S 0.0 0.0 0:01.77 init

40并发时的top结果:

点击(此处)折叠或打开

  1. [root@node1 ~]# top
  2. top - 02:19:01 up 1 day, 15:52, 4 users, load average: 0.17, 0.07, 0.02
  3. Tasks: 192 total, 7 running, 185 sleeping, 0 stopped, 0 zombie
  4. Cpu(s): 21.1%us, 59.8%sy, 0.0%ni, 2.7%id, 10.2%wa, 3.4%hi, 2.9%si, 0.0%st
  5. Mem: 1921956k total, 1847184k used, 74772k free, 4296k buffers
  6. Swap: 2064376k total, 508k used, 2063868k free, 1650616k cached

  8.   PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
  9. 19434 chenhj 20 0 14532 1432 816 R 13.5 0.1 0:00.57 pgbench
  10. 19437 chenhj 20 0 257m 14m 13m S 12.2 0.8 0:00.45 postgres
  11. 19436 chenhj 20 0 257m 14m 13m D 9.9 0.8 0:00.38 postgres
  12. 19441 chenhj 20 0 257m 14m 13m S 9.9 0.8 0:00.37 postgres
  13. 19470 chenhj 20 0 257m 13m 12m S 9.9 0.7 0:00.37 postgres
  14. 19438 chenhj 20 0 257m 14m 13m R 9.6 0.8 0:00.37 postgres
  15. 19440 chenhj 20 0 257m 14m 13m S 9.6 0.8 0:00.38 postgres
  16. 19442 chenhj 20 0 257m 14m 13m S 9.6 0.8 0:00.38 postgres
  17. 19446 chenhj 20 0 257m 14m 13m R 9.6 0.8 0:00.36 postgres

TPC-B测试
TPC-B测试的结果有些不同，瓶颈都在CPU+IO。

点击(此处)折叠或打开

  1. [chenhj@node1 ~]$ pgbench pgbench -n -T 20
  2. transaction type: TPC-B (sort of)
  3. scaling factor: 100
  4. query mode: simple
  5. number of clients: 1
  6. number of threads: 1
  7. duration: 20 s
  8. number of transactions actually processed: 3251
  9. latency average: 6.152 ms
  10. tps = 162.526702 (including connections establishing)
  11. tps = 162.558331 (excluding connections establishing)

  12.
  13. [chenhj@node1 ~]$ pgbench pgbench -n -c 40 -T 20
  14. transaction type: TPC-B (sort of)
  15. scaling factor: 100
  16. query mode: simple
  17. number of clients: 40
  18. number of threads: 1
  19. duration: 20 s
  20. number of transactions actually processed: 9361
  21. latency average: 85.461 ms
  22. tps = 467.186639 (including connections establishing)
  23. tps = 471.360815 (excluding connections establishing)

比如40并发时，top结果在下面2种之间切换，应该是checkpoint的作用，平均的CPU利用率没有过半。

点击(此处)折叠或打开

  1. [root@node1 ~]# top
  2. top - 02:34:49 up 1 day, 16:07, 4 users, load average: 0.17, 0.57, 0.46
  3. Tasks: 192 total, 15 running, 177 sleeping, 0 stopped, 0 zombie
  4. Cpu(s): 32.1%us, 25.2%sy, 0.0%ni, 33.9%id, 7.6%wa, 0.6%hi, 0.5%si, 0.0%st
  5. Mem: 1921956k total, 1853080k used, 68876k free, 1596k buffers
  6. Swap: 2064376k total, 508k used, 2063868k free, 1659988k cached
  8. PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
  9. 19672 chenhj 20 0 14536 1464 816 R 18.0 0.1 0:00.54 pgbench
  10. 19675 chenhj 20 0 257m 10m 9496 R 6.7 0.6 0:00.20 postgres
  11. 19674 chenhj 20 0 257m 10m 9692 R 6.3 0.6 0:00.19 postgres
  12. 19677 chenhj 20 0 257m 10m 9824 S 6.3 0.6 0:00.19 postgres
  13. 19678 chenhj 20 0 257m 10m 9696 R 6.3 0.6 0:00.19 postgres
  14. 19679 chenhj 20 0 257m 10m 9512 S 6.3 0.6 0:00.19 postgres
  15. ...
  17. [root@node1 ~]# top
  18. top - 02:34:55 up 1 day, 16:07, 4 users, load average: 2.80, 1.11, 0.64
  19. Tasks: 192 total, 1 running, 191 sleeping, 0 stopped, 0 zombie
  20. Cpu(s): 2.5%us, 1.5%sy, 0.0%ni, 34.2%id, 61.7%wa, 0.1%hi, 0.1%si, 0.0%st
  21. Mem: 1921956k total, 1845764k used, 76192k free, 1628k buffers
  22. Swap: 2064376k total, 508k used, 2063868k free, 1651344k cached
  24. PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
  25. 19672 chenhj 20 0 14536 1464 816 S 1.3 0.1 0:01.10 pgbench
  26. 19701 chenhj 20 0 257m 13m 11m S 1.3 0.7 0:00.36 postgres
  27. 19713 chenhj 20 0 257m 13m 11m S 1.3 0.7 0:00.39 postgres
  28. 19676 chenhj 20 0 257m 13m 12m S 1.0 0.7 0:00.37 postgres
  29. 19674 chenhj 20 0 257m 13m 12m S 0.7 0.7 0:00.40 postgres
  30. 19675 chenhj 20 0 257m 13m 12m S 0.7 0.7 0:00.38 postgres
  31. ...

4. 结论

1）青云云主机的IO还是蛮给力了，iops应该在500以上（又额外测了scale因子为10的情况，2并发时的TPC-B的tps能达到869）。
2）本人PC虚机的CPU效能太低，4个核才顶人家云主机的一个核（这也是服务器CPU和家用CPU的差别）。



阅读(3326) | 评论(0) | 转发(0) |
0

上一篇：关于PostgreSQL中的多字段索引之三(gin篇)

下一篇：PostgreSQL gin索引的部分匹配

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6