开发者社区> 问答> 正文

用德哥的测试方法,笔记本上跑的pg只有31tps正常吗?

Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.00 0.00 0.00 252.00 0.00 1.98 16.13 144.16 556.94 0.00 556.94 3.97 100.00

(backend)xxxx@Tower:~/workspace/test_postgres/sql$ pgbench -M simple -r -c 8 -f login.sql -j 8 -n -T 180 -h localhost -U digoal
Password:
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
duration: 180 s
number of transactions actually processed: 5592
tps = 31.048187 (including connections establishing)
tps = 31.051705 (excluding connections establishing)
statement latencies in milliseconds:

    0.004246        \setrandom userid 1 20000000
    257.571259      SELECT f_user_login(:userid);

正常吗?
(backend)xxxx@Tower:~/workspace/test_postgres/sql$ uname -a
Linux Tower 3.19.0-25-generic #26~14.04.1-Ubuntu SMP Fri Jul 24 21:16:20 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux
(backend)xxxx@Tower:~/workspace/test_postgres/sql$ free

         total       used       free     shared    buffers     cached

Mem: 3740348 2223060 1517288 313924 115236 1143792
-/+ buffers/cache: 964032 2776316
Swap: 3881980 0 3881980
(backend)xxxx@Tower:~/workspace/test_postgres/sql$ cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 61
model name : Intel(R) Core(TM) i5-5200U CPU @ 2.20GHz
stepping : 4
microcode : 0x21
cpu MHz : 2200.000
cache size : 3072 KB
physical id : 0
siblings : 4
core id : 0
cpu cores : 2
apicid : 0
initial apicid : 0
fpu : yes
fpu_exception : yes
cpuid level : 20
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf eagerfpu pni pclmulqdq dtes64 monitor ds_cpl vmx est tm2 ssse3 fma cx16 xtpr pdcm pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer xsave avx f16c rdrand lahf_lm abm 3dnowprefetch ida arat epb pln pts dtherm tpr_shadow vnmi flexpriority ept vpid fsgsbase tsc_adjust bmi1 avx2 smep bmi2 erms invpcid rdseed adx smap xsaveopt
bugs :
bogomips : 4390.02
clflush size : 64
cache_alignment : 64
address sizes : 39 bits physical, 48 bits virtual
power management:

四核cpu,postgresql-9.3

展开
收起
二哈 2015-12-24 10:49:14 5618 0
2 条回答
写回答
取消 提交回答
  • listen_addresses = '0.0.0.0'

    max_connections = 1000

    superuser_reserved_connections = 13

    unix_socket_permissions = 0700

    tcp_keepalives_idle = 60
    tcp_keepalives_interval = 10
    tcp_keepalives_count = 6

    shared_buffers = 1GB

    maintenance_work_mem = 512MB
    shared_preload_libraries = 'auth_delay,passwordcheck,pg_stat_statements,auto_explain'

    vacuum_cost_delay = 10ms
    vacuum_cost_limit = 10000

    bgwriter_delay = 10ms

    ’# wal_level = logical

    wal_buffers = 16MB

    checkpoint_segments = 64

    checkpoint_timeout = 5min

    ‘# archive_mode = on

    ‘# archive_command = '/bin/date'

    ’# max_wal_senders = 32

    random_page_cost = 2.0

    effective_cache_size = 6GB #我的内存只有8G,80%大约就是6G

    log_destination = 'csvlog'
    logging_collector = on
    log_truncate_on_rotation = on
    log_rotation_age = 1d
    log_rotation_size = 10MB

    log_min_duration_statement = 1000ms

    log_checkpoints = on

    log_connections = on
    log_disconnections = on
    log_error_verbosity = verbose # 在日志中输出代码位置

    log_lock_waits = on
    deadlock_timeout = 1s

    log_statement = 'ddl'

    track_activity_query_size = 2048

    autovacuum = on
    log_autovacuum_min_duration = 0

    shared_preload_libraries = 'pg_stat_statements'

    auth_delay.milliseconds = 5000 # 认证失败, 延迟多少毫秒反馈
    auto_explain.log_min_duration = 5000 # 记录超过多少毫秒的SQL当时的执行计划
    auto_explain.log_analyze = true
    auto_explain.log_verbose = true
    auto_explain.log_buffers = true
    auto_explain.log_nested_statements = true
    pg_stat_statements.track_utility=off
    pg_stat_statements.max = 1000
    pg_stat_statements.track = all

    就用上面的参数,跑的测试,基本上照抄的德哥的优化1的设置,前面加#号的是跟主备相关的测试,因为我的是笔记本,单台机器,所以,没有启用这些参数。


    其实,后面几步,有的已经做了,有的没有,prepared模式没有打开,异步日志没有打开(在用默认的),pgfincore已经做了,用函数代替简单的sql语句,也已经做了。
    你的文章里的机器,一开始就能到300多,我的笔记本,一直在30多打转,不确定是不是初始参数,设得就不合理啊?


    synchronous_commit = off
    wal_writer_delay = 10ms

    加了异步日志,并使用了prepared的结果:

    (backend)xxxxxx@Tower:~/workspace/test_postgres/sql$ pgbench -M prepared -r -c 8 -f login.sql -j 8 -n -T 180 -h localhost -U digoal
    Password:
    transaction type: Custom query
    scaling factor: 1
    query mode: prepared
    number of clients: 8
    number of threads: 8
    duration: 180 s
    number of transactions actually processed: 5792
    tps = 31.978472 (including connections establishing)
    tps = 31.983547 (excluding connections establishing)
    statement latencies in milliseconds:

        0.003740        \setrandom userid 1 20000000
        249.734626      SELECT f_user_login(:userid);
    

    iostat -x显示util项跑满

    avg-cpu: %user %nice %system %iowait %steal %idle

           1.20    0.00    1.45   71.28    0.00   26.07
    

    Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
    sda 0.00 22.00 130.00 40.33 1066.67 657.33 20.24 9.10 53.69 61.83 27.47 5.87 100.00

    2019-07-17 18:23:06
    赞同 展开评论 打赏
  • 公益是一辈子的事, I am digoal, just do it. 阿里云数据库团队, 擅长PolarDB, PostgreSQL, DuckDB, ADB等, 长期致力于推动开源数据库技术、生态在中国的发展与开源产业人才培养. 曾荣获阿里巴巴麒麟布道师称号、2018届OSCAR开源尖峰人物.

    不正常,不要只看我的测试方法,还要看前面的优化,配置。


    http://blog.163.com/digoal@126/blog/static/163877040201221382150858/
    http://blog.163.com/digoal@126/blog/static/163877040201221333411196/
    优化曲线是这样的,你现在在最左边,也就是刚起步。
    1175439502761017587

    2019-07-17 18:23:06
    赞同 展开评论 打赏
问答排行榜
最热
最新

相关电子书

更多
移动互联网测试到质量的转变 立即下载
给ITer的技术实战进阶课-阿里CIO学院独家教材(四) 立即下载
F2etest — 多浏览器兼容性测试整体解决方案 立即下载