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
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
不正常,不要只看我的测试方法,还要看前面的优化,配置。
http://blog.163.com/digoal@126/blog/static/163877040201221382150858/
http://blog.163.com/digoal@126/blog/static/163877040201221333411196/
优化曲线是这样的,你现在在最左边,也就是刚起步。
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。