开发者社区> 问答> 正文

conf文件中影响数据库性能的配置项有哪些?

conf文件中影响数据库性能的配置项有哪些?
最好有一些解释,谢谢

展开
收起
pgnosql 2015-12-23 10:05:20 4537 0
1 条回答
写回答
取消 提交回答
  • 公益是一辈子的事, I am digoal, just do it. 阿里云数据库团队, 擅长PolarDB, PostgreSQL, DuckDB, ADB等, 长期致力于推动开源数据库技术、生态在中国的发展与开源产业人才培养. 曾荣获阿里巴巴麒麟布道师称号、2018届OSCAR开源尖峰人物.

    标题应该改一下呢?加上PostgreSQL吧。


    有几篇更详细的讲PostgreSQL性能优化的
    http://yq.aliyun.com/articles/214
    http://yq.aliyun.com/articles/215
    还有专门讲参数优化的视频

    PostgreSQL 性能优化视频(18集全) : 
    18. 性能优化培训 - 综合优化案例
    http://www.tudou.com/programs/view/UeXudDhDaHU/
    17. 性能优化培训 - 性能分析工具3, pg_statsinfo的使用(与DBA培训同集)
    http://www.tudou.com/programs/view/5SUCgb7_hsY/
    16. 性能优化培训 - 性能分析工具2, pg_stat_statements
    http://www.tudou.com/listplay/JW66CCxpr-s/xkOD3u8kQkE.html
    (注意,里面有一些描述有问题。pg_stat_statements是在数据库启动时加载。 运行过程有write操作(和PG版本有个,以前的版本可以不持久化,没有write操作,现在的版本都有write操作),数据库关闭时fsync。)
    (在run到hook处时加载。)
    15. 性能优化培训 - 性能分析工具1, sar
    http://www.tudou.com/listplay/JW66CCxpr-s/qX8HOgBZu2M.html
    14. 性能优化培训 - PostgreSQL压力测试工具pgbench讲解
    http://www.tudou.com/listplay/JW66CCxpr-s/OUl0DLhiJwg.html
    13. 性能优化培训 - 如何让数据库输出好的执行计划, 访问开关, 指定表关联顺序, 遗传算法
    http://www.tudou.com/listplay/JW66CCxpr-s/dS2x85nosBw.html
    12. 性能优化培训 - PostgreSQL锁的详解
    http://www.tudou.com/listplay/JW66CCxpr-s/OsRGPcGEL9M.html
    11. 性能优化培训 - PostgreSQL事务隔离级别讲解
    http://www.tudou.com/listplay/JW66CCxpr-s/2sqzjiuqKFY.html
    10. 性能优化培训 - 函数的三种稳定性状态对优化器的影响分解讲解 
    http://www.tudou.com/programs/view/p6E3oQEsZv0/
    9. 性能优化培训 - PostgreSQL trace & debug (跟踪和调试)
    http://www.tudou.com/programs/view/SbglCp2T3t4/
    8. 性能优化培训 - 执行计划缓存管理, 绑定变量接口
    http://www.tudou.com/programs/view/kwmilXD7JEw/
    7. 性能优化培训 - auto_explain插件, 索引扫描引发的heap page scan被放大的实例讲解
    http://www.tudou.com/programs/view/LwMWC4ZpOhU/
    6. PostgreSQL 性能优化培训 - 执行计划成本因子(page scan cost, cpu cost)的校准方法实例讲解
    http://www.tudou.com/programs/view/yQ0SzBqx_4w/
    5. PostgreSQL 性能优化培训 - 行评估算法讲解, 成本计算实例讲解
    http://www.tudou.com/programs/view/3zgOuh7kbfs/
    4. PostgreSQL 性能优化培训 - explain 实例讲解
    http://www.tudou.com/programs/view/QztOh_hCFKw
    3. PostgreSQL 性能优化培训 - explain输出结构信息详解
    http://www.tudou.com/programs/view/OZSUbOFZ0U4
    2. PostgreSQL 性能优化培训 - 统计信息详解, 成本因子介绍
    http://www.tudou.com/programs/view/oA1v5sDFq3Q/
    1. PostgreSQL 性能优化培训 - 授课环境搭建讲解
    http://www.tudou.com/programs/view/AVCbdfl9rH8/

    建议的参数项

    echo "----->>>---->>>  获取postgresql.conf配置: "
    grep '^\ *[a-z]' $PGDATA/postgresql.conf|awk -F "#" '{print $1}'
    echo "建议: "
    echo "    主备配置尽量保持一致, 配置合理的参数值."
    echo -e "    建议修改的参数列表如下  ( 假设操作系统内存为128GB, 数据库独占操作系统, 数据库版本9.4.x )  : 
    echo ""
    listen_addresses = '0.0.0.0'            # 监听所有IPV4地址
    port = 1921                             # 监听非默认端口
    max_connections = 4000                  # 最大允许连接数
    superuser_reserved_connections = 20     # 为超级用户保留的连接
    unix_socket_directories = '.'           # unix socket文件目录最好放在$PGDATA中, 确保安全
    unix_socket_permissions = 0700          # 确保权限安全
    tcp_keepalives_idle = 30                # 间歇性发送TCP心跳包, 防止连接被网络设备中断.
    tcp_keepalives_interval = 10
    tcp_keepalives_count = 10
    shared_buffers = 16GB                   # 数据库自己管理的共享内存大小
    huge_pages = try                        # 尽量使用大页, 需要操作系统支持, 配置vm.nr_hugepages*2MB大于shared_buffers.
    maintenance_work_mem = 512MB            # 可以加速创建索引, 回收垃圾(假设没有设置autovacuum_work_mem)
    autovacuum_work_mem = 512MB             # 可以加速回收垃圾
    shared_preload_libraries = 'auth_delay,passwordcheck,pg_stat_statements,auto_explain'           # 建议防止暴力破解, 密码复杂度检测, 开启pg_stat_statements, 开启auto_explain, 参考 http://blog.163.com/digoal@126/blog/static/16387704020149852941586  
    bgwriter_delay = 10ms                   # bgwriter process间隔多久调用write接口(注意不是fsync)将shared buffer中的dirty page写到文件系统.
    bgwriter_lru_maxpages = 1000            # 一个周期最多写多少脏页
    max_worker_processes = 20               # 如果要使用worker process, 最多可以允许fork 多少个worker进程.
    wal_level = logical                     # 如果将来打算使用logical复制, 最后先配置好, 不需要停机再改.
    synchronous_commit = off                # 如果磁盘的IOPS能力一般, 建议使用异步提交来提高性能, 但是数据库crash或操作系统crash时, 最多可能丢失2*wal_writer_delay时间段产生的事务日志(在wal buffer中). 
    wal_sync_method = open_datasync         # 使用pg_test_fsync测试wal所在磁盘的fsync接口, 使用性能好的.
    wal_buffers = 16MB
    wal_writer_delay = 10ms
    checkpoint_segments = 1024              # 等于shared_buffers除以单个wal segment的大小.
    checkpoint_timeout = 30min
    checkpoint_completion_target = 0.2
    archive_mode = on                       # 最好先开启, 否则需要重启数据库来修改
    archive_command = '/bin/date'           # 最好先开启, 否则需要重启数据库来修改, 将来修改为正确的命令例如, test ! -f /home/postgres/archivedir/pg_root/%f && cp %p /home/postgres/archivedir/pg_root/%f
    max_wal_senders = 32                    # 最多允许多少个wal sender进程.
    wal_keep_segments = 2048                # 在pg_xlog目录中保留的WAL文件数, 根据流复制业务的延迟情况和pg_xlog目录大小来预估.
    max_replication_slots = 32              # 最多允许多少个复制插槽
    hot_standby = on
    max_standby_archive_delay = 300s        # 如果备库要被用于只读, 有大的查询的情况下, 如果遇到conflicts, 可以考虑调整这个值来避免conflict造成cancel query.
    max_standby_streaming_delay = 300s      # 如果备库要被用于只读, 有大的查询的情况下, 如果遇到conflicts, 可以考虑调整这个值来避免conflict造成cancel query.
    wal_receiver_status_interval = 1s
    hot_standby_feedback = on
    random_page_cost = 2                    # 根据IO能力调整
    effective_cache_size = 100GB            # 调整为与内存一样大, 或者略小(减去shared_buffer). 用来评估OS PAGE CACHE可以用到的内存大小.
    log_destination = 'csvlog'
    logging_collector = on
    log_truncate_on_rotation = on
    log_rotation_size = 10MB
    log_min_duration_statement = 1s
    log_checkpoints = on
    log_connections = on
    log_disconnections = on
    log_error_verbosity = verbose           # 在日志中输出代码位置
    log_lock_waits = on
    log_statement = 'ddl'
    autovacuum = on
    log_autovacuum_min_duration = 0
    autovacuum_max_workers = 10
    autovacuum_naptime = 30s                # 快速唤醒, 防止膨胀
    autovacuum_vacuum_scale_factor = 0.02   # 当垃圾超过比例时, 启动垃圾回收工作进程
    autovacuum_analyze_scale_factor = 0.1
    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
    2019-07-17 18:20:41
    赞同 2 展开评论 打赏
问答排行榜
最热
最新

相关电子书

更多
DTCC 2022大会集锦《云原生一站式数据库技术与实践》 立即下载
阿里云瑶池数据库精要2022版 立即下载
2022 DTCC-阿里云一站式数据库上云最佳实践 立即下载