1.数据库减负思路
缓存 + 页面静态化
实时性不高的数据；
数据库优化=sql优化 + 表结构优化 + 数据库分区分表
合并数据库操作，将多次操作合并成一条sql执行。
热点数据分离
主表只保存活跃数据。
数据库读写分离
2.配置优化
pg中与内存有关的配置参数：
2.1-shared_buffers（共享缓存区）
i>工作原理
shared_buffers是一个8KB的数组，postgres在从磁盘中查询数据前，会先查找shared_buffers的页，如果命中，就直接返回，避免从磁盘查询。
多个进程通过共享内存技术来共享缓存中的数据。
shared_buffers存储什么？
表数据；
索引，索引也存储在8K块中；
执行计划，存储基于会话的执行计划，会话结束，缓存的计划也就被丢弃。什么时候加载shared_buffers？
1）在访问数据时，数据会先加载到os缓存，然后再加载到shared_buffers，这个加载过程可能是一些查询，也可以使用pg_prewarm预热缓存。
2）当然也可能同时存在os和shared_buffers两份一样的缓存（双缓存）。
3）查找到的时候会先在shared_buffers查找是否有缓存，如果没有再到os缓存查找，最后再从磁盘获取。
4）os缓存使用简单的LRU（移除最近最久未使用的缓存），而数据库采用的优化的时钟扫描，即缓存使用频率高的会被保存，低的被移除。
ii>优化策略
提高shared_buffers，增加缓存命中率，提高查询效率。
同时为了避免Double Buffering问题，将shared_buffers设置较小，更多的内存留给文件系统使用。
【Double Buffering（双缓存）】问题：
pg的数据文件都存储在文件系统中，os的文件系统也有缓存，这导致pg的数据库副本可能同时存在于共享内存和文件系统中，造成内存利用率低的问题。
Oracle中通过设置Birect I/O避免双缓存问题，但pg不支持。 shared_buffers的大小不应该超过内存的1/4。shared_buffers设置的合理范围
1）windows服务器有用范围是64MB到512MB，默认128MB
2）linux服务器建议设置为25%，亚马逊服务器设置为75%（避免双缓存，数据会存储在os和shared_buffers两份）
os缓存的重要性：数据写入时，从内存到磁盘，这个页面就会被标记为脏页，一旦被标记为脏页，它就会被刷新到os缓存，然后写入磁盘。所以如果os高速缓存大小较小，则它不能重新排序写入并优化io，这对于繁重的写入来说非常致命，因此os的缓存大小也非常重要。给予shared_buffers太大或太小都会损害性能。
2.2-work_mem
为每个进程单独分配的内存，主要用于group by, sort, hash agg, hash join 等操作。
注意：work_mem是每次分配的内存，加入有M个并发进程，每个进程有N个HASH操作，那么需要分配的内存为 MNwork_mem。因此work_mem不宜设置太大，通常保持默认的4MB即可，如果设置的太大超过256MB，很容易因为瞬间的大并发操作导致oom。
2.3-maintenance_work_mem
为每个进程单独分配的内存，主要进行维护操作时需要的内存，如VACUUM、create index、ALTER TABLE ADD FOREIGN KEY等操作需要的内存。
2.4-autovacuum_work_mem
pg9.4版本新增参数。
9.4之后，AutoVacuum的worker进程分配的内存由参数autovacuum_work_mem控制，手动Vacuum时分配的内存由maintenance_work_mem 控制。9.4之前都用maintenance_work_mem 参数。
默认值为-1，表示与maintenance_work_mem 一样。
vacuum 大小 = autovacuum_max_workers * autovacuum_work_mem
2.5-temp_buffers（临时表缓存）
为每个不同的进程单独分配的内存，不在共享内存中，默认为8MB。
2.6-wal_buffers（WAL日志缓存大小）
默认为-1，表示根据shared_buffer的大小自动设置。
2.7-huge_pages（是否使用大页）
默认值为try，表示尽量使用大页。若os未开启大页，不使用大页内存，不影响数据库正常使用。
2.8-effective_cache_size（sql执行中的实际磁盘缓存）
与具体内存分配无关
3.排查sql
-- 查看表结构
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = '表名';
-- 查看慢查询日志是否开启
SHOW log_min_duration_statement;
-- 设置慢查询日志
ALTER DATABASE test SET log_min_duration_statement TO 10000;
--慢sql 添加\watch 1监控
select query,wait_event_type,wait_event from pg_stat_activity
where wait_event is not null and now()-state_change>interval '5 second';
--长wait事件
select query,wait_event_type,wait_event from pg_stat_activity where state='active' and wait_event is not null and now()-state_change>interval '5 second';
-- 查找经常被扫描的大型表
SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan, seq_tup_read / seq_scan AS avg FROM pg_stat_user_tables WHERE seq_scan > 0 ORDER BY seq_tup_read DESC LIMIT 20;
-- 跟踪 vacuum 进度
SELECT * FROM pg_stat_progress_vacuum ;
--连接数查询
select count(*),application_name from pg_stat_activity group by application_name;
4.sql审计
相关配置：
参数调整 说明
log_min_duration_statement sql审计记录的标准，超过该时长的sql将被记录到日志文件。默认为-1，不记录超时sql。
log_statement none默认，不记录；all-记录所有语句；ddl-记录所有数据定义语句；mod记录所有ddl和数据修改语句；
log_min_error_statement 控制日志中是否记录导致数据库出现错误的SQL语句。默认为error
5.pg统计收集器
比较耗费性能，默认关闭，可在postgresql.conf进行配置（ stats_start_collector = true ）
6.调优工具
VACUUM是PostgreSQL中用于清理数据库的工具。它主要删除由更新、删除或过时但未从表中物理删除的元组，从而有助于避免腹胀。使用VACUUM时，建议使用分析关键字，以获取更多详细信息，可以参考PostgreSQL官方文档
pgtune是PostgreSQL的调优工具。它是一个在线工具，用于为PostgreSQL数据库生成优化建议。用户只需输入数据库的配置参数，pgtune便会分析这些参数并给出优化建议。这些建议基于大量的性能测试数据，旨在提高数据库的性能和稳定性。使用pgtune可以帮助数据库管理员更好地调整PostgreSQL的配置，以满足特定的工作负载需求。
PoWA是PostgreSQL的调优工具之一。它是一个用于性能分析和调优的开源工具，专门针对PostgreSQL数据库。PoWA提供了丰富的功能和工具，帮助数据库管理员和开发者识别和解决性能瓶颈。它不仅可以监控数据库的实时性能，还可以生成详细的报告和建议，以优化数据库配置和查询。使用PoWA，可以更有效地利用PostgreSQL的资源，提高查询性能和整体系统效率。
pg_stat_statements是PostgreSQL的一个模块，用于跟踪SQL语句的执行统计信息。这个模块可以提供关于执行时间、调用次数和行数等详细数据，帮助数据库管理员和开发者识别和优化性能瓶颈。通过启用pg_stat_statements，用户可以轻松地查看哪些SQL语句消耗了最多的资源，从而进行相应的优化。此外，它还提供了SQL语句的哈希值，使得即使SQL语句稍有变动，也能准确地追踪到其执行统计。
EXPLAIN是PostgreSQL中的一个重要调优工具，用于分析SQL查询的执行计划。当执行EXPLAIN命令时，PostgreSQL会返回一个详细的查询执行计划，包括查询的各个步骤、使用的索引、扫描的行数等信息。这有助于数据库管理员和开发者理解查询是如何执行的，从而进行相应的优化。通过EXPLAIN，用户可以找出查询中的瓶颈，例如缺少的索引或低效的查询策略，进而改进查询性能。
PostgreSQL的配置参数是数据库性能调优的关键。这些参数决定了数据库如何响应各种操作和负载。为了确保数据库的高效运行，需要根据硬件、工作负载和需求进行调整。例如，shared_buffers参数用于控制数据库用于缓存的数据块数量，而maintenance_work_mem则用于管理维护操作的内存需求。调整这些参数可以显著提高数据库性能。但需要注意的是，不当的配置可能导致性能下降或稳定性问题，因此建议在进行任何重大更改之前进行充分的测试。
auto_explain是PostgreSQL的一个模块，用于自动分析查询执行计划。当启用此模块时，它会为每个执行的SQL查询生成一个解释日志，显示查询的执行计划、执行时间和其他相关信息。这有助于数据库管理员和开发者更好地理解查询是如何在数据库中执行的，从而进行相应的调优。通过auto_explain，用户可以快速识别和修复性能瓶颈，提高数据库的整体性能。
PgCluu是PostgreSQL的调优工具之一。它主要用于性能分析和优化，提供详细的执行计划、索引建议和查询优化建议。与其他调优工具相比，PgCluu的特点在于它不仅提供传统的执行计划分析，还能深入到查询的各个阶段，为用户提供更全面的优化建议。这使得PgCluu在复杂的查询场景和多表联接查询中特别有用。总体来说，PgCluu是一个功能全面的PostgreSQL调优工具，适用于各种性能分析和优化需求。
Database Indexes是PostgreSQL调优的关键工具之一。索引可以显著提高查询性能，特别是对于大数据集。它们允许数据库系统快速定位数据，从而避免全表扫描。然而，不当的索引使用可能导致性能下降或增加存储需求。因此，选择合适的索引策略和定期维护是至关重要的。此外，监控工具可以帮助识别哪些索引最有效，哪些可能需要优化或删除。总体而言，合理使用和管理数据库索引是优化PostgreSQL性能的关键步骤。
Pgwatch2是PostgreSQL的监控工具。它是一个开源项目，专为PostgreSQL数据库设计，用于收集、存储和分析性能指标。该工具提供了丰富的监控功能，包括但不限于查询性能、索引使用情况、磁盘I/O和CPU使用率等。通过Pgwatch2，数据库管理员可以实时了解数据库的运行状态，及时发现并解决问题，从而提高数据库的性能和稳定性。
ANALYZE是PostgreSQL中的一个命令，用于收集统计信息，以优化查询性能。当执行ANALYZE命令时，PostgreSQL会更新其统计信息，这些统计信息对于查询优化器至关重要。优化器使用这些统计信息来选择最有效的执行计划。因此，定期运行ANALYZE可以帮助确保查询优化器做出最佳决策，从而提高查询性能。
评论区