PostgreSQL数据库调优经验

合集下载

postgresql的in查询效率慢的解决方法_概述及解释说明

postgresql的in查询效率慢的解决方法概述及解释说明1. 引言1.1 概述在当今数据库应用中，查询操作是最为常见和核心的任务之一。

而对于PostgreSQL这样功能强大且开源的关系型数据库来说，其性能表现也越来越受到关注。

本文将重点探讨PostgreSQL中in查询效率慢的问题以及解决方法。

1.2 文章结构本文分为以下几个部分进行论述：引言、PostgreSQL的in查询效率慢的问题、解决方法一：使用索引进行优化、解决方法二：拆分in查询为多个子查询、解决方法三：使用临时表进行优化处理以及结论。

通过前期的问题描述和分析，接着给出了三种具体的解决方案，并且在每一种方案下都详细探讨了其原理、适用场景和注意事项等。

1.3 目的本文旨在探讨和解释PostgreSQL中in查询效率慢的原因，并提供相应的优化方案。

通过深入研究不同的解决方法，读者可以更好地理解并掌握如何有效地提高PostgreSQL数据库中in查询操作的效率，从而避免性能瓶颈和优化问题。

**注意: 上述内容请依次按照清晰明了排好段落, 并围绕'概述'、'文章结构' 以及'目的'等三个方面进行撰写, 不要包含任何markdown格式**2. Postgresql的in查询效率慢的问题2.1 in查询的基本原理In查询是一种常见的SQL查询方式，它用于在数据库中检索出符合指定条件的数据。

在Postgresql中，in查询使用IN关键字来实现。

2.2 in查询效率慢的原因分析尽管in查询是一个方便且功能强大的方法，但当处理大量数据或存在复杂条件时，它可能导致查询的效率变慢。

这主要由以下几个因素引起：首先，in 查询对应多个值时，数据库需要逐个匹配每个值，并比较其是否满足条件，这会增加系统资源消耗和执行时间。

其次，在某些情况下，Postgresql优化器无法正确选择索引来加速in 查询。

例如，在某些情况下，如果列上没有适当的索引或者统计信息不准确，优化器可能选择全表扫描而不是使用索引。

PostgreSQL数据库的特点与优势

PostgreSQL数据库的特点与优势PostgreSQL数据库是一种自由、开放源代码的关系型数据库，被广泛地应用于各种应用程序中。

它的特点和优势如下所述。

一、特点1. 高度可靠性PostgreSQL数据库具有出色的稳定性和可靠性，大小型企业和大型组织也可依靠它来处理重要数据。

在出现故障时，PostgreSQL也能够快速地恢复操作正常，数据的完整性能得到极大的保障。

2. 扩展性强PostgreSQL数据库具有扩展性极强的特点，可以非常轻松地实现垂直和水平的扩展。

同时还提供了多种扩展模型，如postgis、hstore等，可以使数据库更加灵活适用于各种数据存储需求。

3. 具有多种编程语言支持PostgreSQL数据库支持各种主流编程语言的API，如C、C++、Java、Python、Perl、Ruby等，使得开发人员可以选择最适合的编程语言开发应用程序，数据处理过程更加灵活和高效。

4. 具有高级特性PostgreSQL实现了许多高级特性，如外键、事务、视图、存储过程和触发器等，将SQL的使用转化为更高效的数据处理方式。

此外，PostgreSQL还支持JSON数据类型，并且提供了全文搜索、元数据查询等功能，便于用户更好地管理数据并增强数据的查询功能。

5. 开放源代码PostgreSQL数据库是以开源方式进行开发的，用户可以免费获取、使用、修改源代码，从而更好地满足其各种需求。

此外，开放源代码使得用户可以充分利用社区的力量，共同开发和维护高质量的数据库产品。

二、优势1. 易于安装和使用PostgreSQL数据库具有良好的安装和使用体验，安装过程简单清晰，同时提供了各种开箱即用的工具和接口，方便用户进行数据管理和操作。

2. 性能强悍PostgreSQL数据库对于读取操作和并发性能方面有着出色的表现。

尤其适合高并发、大数据量的应用场景，可以更好地支持并发读写操作，并提供了丰富的调优参数，可以使得性能得到更好的提升。

数据库性能调优的技巧与方法

数据库性能调优的技巧与方法数据库是现代应用开发中不可或缺的一部分，它负责存储和管理大量的数据，并提供高效的数据访问和查询功能。

然而，在面对大量数据和复杂查询需求时，数据库的性能可能受到挑战。

为了提高数据库的性能和响应能力，我们需要使用一些调优的技巧和方法。

1. 合理设计数据库结构合理设计数据库结构是提高性能的基础。

首先，应该遵循第一范式、第二范式和第三范式，以避免数据冗余和不一致。

其次，应该正确选择和使用数据类型，根据数据的特性来选择合适的数据类型，避免存储不必要的信息。

此外，还应该为每个表创建适当的索引，以便加快查询效率。

2. 优化查询语句查询语句的优化对于提高性能至关重要。

首先，应该避免使用全表扫描，使用索引来加快查询速度。

其次，应该尽量避免使用复杂的子查询和连接操作，可以使用JOIN来替代连接操作。

另外，应该避免使用通配符查询，尽量将查询条件写得更精确，以减少数据库的查询压力。

3. 使用合适的索引索引是提高数据库查询性能的关键。

在设计和创建索引时，应该注意以下几点。

首先，应该根据查询需求和频率来选择合适的列作为索引列。

通常情况下，选择频繁查询和过滤的列作为索引列会更有效。

其次，可以考虑创建复合索引，将多个列作为索引列，以优化多列的查询效率。

另外，应该定期维护和优化索引，删除不必要或者不再使用的索引。

4. 定期统计和优化表格定期统计和优化表格可以提高数据库的性能和查询速度。

通过收集和分析统计信息，我们可以了解哪些表格的数据量较大或者查询频率较高，从而进行相应的优化。

可以使用数据库自带的分析工具或者第三方工具来帮助我们完成这一过程。

5. 分区和分表对于大型数据库，可以考虑使用分区和分表的技术来提高性能。

分区是将一个大型表格分割为多个小的逻辑表格，可以减少查询的开销和提高数据库的可扩展性。

分表是将一个大型表格分割为多个相同结构的物理表格，可以减少单个表格的数据量和查询的复杂性。

6. 缓存数据和查询结果使用缓存是提高数据库性能的一种常用方法。

PostgreSQL数据库性能调优指南

扫描索引的代价
vacuum扫描索引并删除这些TID对应的所有索引项。如果它在扫描完整个表之前耗尽了存放无效元组TID所用的内存，它将停止表扫描，转而扫描索引，以丢弃堆积的TID列表，之后从它中断的位置继续扫描表。对于一个大表，多次扫描索引的代价是非常昂贵的，特别是在表中有很多索引的情况下。如果maintenance_work_mem设置太低，甚至可能需要两次以上的索引扫描。
对数据实时性要求不高的场景，可以把该参数配置成默认的on级别，比如报表统计
该参数配置等级越高，Master节点写延迟越大，性能影响越大
PG参数synchronous_commit指定事务提交所要求的WAL记录同步等级，可以取5个有效值：
① off：事务提交不需要等待WAL日志刷写入（flush）本地磁盘 ② local：事务等待WAL日志刷写入本地磁盘后才提交 ③ remote write：事务等待同步备节点接收到WAL日志并写入操作系统才能提
read
优化数据库配置参数
优化内存资源类参数
控制系统在构建索引时将使用的最大内存量。
为了构建一个B树索引，必须对输入的数据进行排序，如果要排序的数据在maintenance_work_mem设定的内存中放置不下，它将会溢出到磁盘中。
① maintenance_work_mem ②
autovac uum
内存耗尽
当使用缺省配置autovacuum_max_workers = 3，并且假设设置 maintenance_work_mem = 10GB，你将会经常消耗30GB的内存专门用于自动空间清理，这还不包括你可能从前台发起的VACUUM或 CREATE INDEX操作所需的内存。这样，你会很容易把一个小系统的内存耗尽，即便是一个大系统，也可能存在诸多性能问题。

数据库性能调优的常见问题与解决方案

数据库性能调优的常见问题与解决方案数据是现代社会的重要组成部分，而数据库是用于存储和管理大量数据的重要工具。

然而，随着数据量的不断增加和应用需求的提高，数据库性能调优变得越来越重要。

本文将介绍数据库性能调优的常见问题，并提供相应的解决方案。

一、索引设计不合理索引是提高数据库查询性能的重要手段，但不合理的索引设计可能导致数据库性能下降。

常见的索引问题包括过多索引、重复索引、索引列选择不当等。

解决方案：1. 评估业务需求，合理选择索引列，避免冗余索引。

2. 针对经常被查询的列创建合适的索引，提高查询效率。

3. 定期分析索引使用情况，删除或优化不必要的索引，避免过度索引。

二、大量数据读取导致性能下降数据库在处理大量数据读取时容易出现性能下降。

常见问题包括缓存未命中、磁盘IO瓶颈、网络传输慢等。

解决方案：1. 设置适当的数据库缓存，提高数据读取命中率。

2. 使用合适的硬件设备，如快速磁盘和高速网络，缓解瓶颈问题。

3. 合理设计数据模型，减少不必要的数据读取量。

三、查询语句写得不优化数据库查询语句的优化对于提高数据库性能至关重要。

常见问题包括全表扫描、不合理的连接查询、使用子查询效率低等。

解决方案：1. 使用合适的查询语句，避免全表扫描。

尽量使用索引列进行查询，减少不必要的数据扫描。

2. 避免使用过多的连接查询，使用内连接代替外连接，或考虑合适的数据库设计。

3. 减少子查询的使用，合理选择表连接的顺序，优化查询语句执行计划。

四、并发访问冲突并发访问是数据库中常见的情况，但过高的并发量和不合理的并发操作可能导致数据库性能下降和数据一致性问题。

解决方案：1. 合理设计数据库事务，避免死锁和数据冲突。

2. 设置合适的并发控制机制，如锁机制、事务隔离级别等，确保并发操作的正确性。

3. 优化数据库并发瓶颈，如增加服务器资源、合理调整并发连接数等。

五、数据库服务器配置不合理数据库服务器的配置对于性能的提升非常关键。

不合理的配置可能导致性能瓶颈和资源浪费。

数据库性能调优的整体流程与方法

数据库性能调优的整体流程与方法数据库性能调优是提高数据库系统性能的关键步骤之一。

当数据库系统出现性能问题时，通过调优可以帮助优化查询、提高响应速度、增加系统容量等，从而更好地满足业务需求和用户期望。

本文将介绍数据库性能调优的整体流程与方法，以帮助读者深入了解并掌握这一重要技能。

一、性能调优的整体流程数据库性能调优包含以下几个关键步骤：1. 收集性能指标：首先需要收集数据库系统的性能指标，如CPU利用率、内存利用率、磁盘I/O等。

这些指标反映了数据库系统的运行状况，帮助我们定位性能问题的根本原因。

2. 分析问题症结：根据收集到的性能指标，分析性能问题的症结所在。

可能会发现一些明显的性能瓶颈，如查询慢、连接数过高等。

这一步骤是深入了解问题所在的关键，可以采用数据库监控工具、性能剖析工具等来帮助分析。

3. 优化数据库设计：数据库设计是影响数据库性能的重要因素之一。

根据分析结果，考虑优化表结构、索引设计、数据模型等。

在表结构设计方面，可以进行分表、分区等优化；在索引设计方面，需要权衡索引的创建与维护成本。

4. 优化查询语句：查询语句是数据库性能调优的关键点之一。

通过检查查询语句是否合理、是否有优化空间，优化查询语句的执行计划、避免全表扫描等方式，提高查询效率和性能。

5. 调整系统参数：根据具体的数据库产品，调整相应的系统参数。

数据库产品通常提供了一些性能调优的参数，可以根据实际情况进行调整以达到最佳性能。

比如可以调整数据库缓存大小，设置并发连接数等。

6. 硬件升级与优化：当软件调优无法满足性能需求时，可以考虑进行硬件升级与优化。

这可能涉及增加内存、扩容磁盘空间、更换更高性能的存储设备等方面。

此外，优化网络架构、负载均衡等也可以改善数据库系统的性能。

7. 执行测试与监控：在完成调优后，需要进行系统测试和性能监控，以确保调优效果达到预期。

可以使用模拟负载、压力测试工具进行测试，同时监控性能指标来评估系统的性能状况。

PostgreSQL数据库设计原则和最佳实践

PostgreSQL数据库设计原则和最佳实践数据库设计是构建一个高效、可扩展和易维护的系统的关键步骤。

PostgreSQL是一种强大的开源关系型数据库管理系统，具有广泛的功能和扩展性。

本文将介绍一些PostgreSQL数据库设计的原则和最佳实践，以帮助您更好地设计和优化数据库。

1. 使用正确的数据类型正确选择合适的数据类型是数据库设计中至关重要的一步。

不同的数据类型在存储和处理数据时有不同的性能和空间开销。

在PostgreSQL中，有许多数据类型可供选择，如整数、浮点数、文本、日期/时间等。

根据数据的特性和需求，选择最合适的数据类型，以减少存储空间的浪费和提高查询性能。

2. 设计合理的表结构在设计数据库表结构时，应遵循一些最佳实践。

首先，确定正确的主键。

主键应该是唯一且稳定的字段，它能够唯一标识一条记录。

其次，避免使用过多的冗余字段，以减少数据冗余和维护成本。

此外，合理设计表之间的关系，并使用外键来实现数据完整性和一致性。

3. 索引优化索引是提高查询性能的关键之一。

在PostgreSQL中，可以使用B-tree、哈希、GiST等索引类型。

在设计索引时，应根据查询的需求和频率进行优化。

不必为每个字段都创建索引，只需要为经常进行搜索和排序的字段创建索引，可以提高查询效率并减少索引的维护成本。

4. 视图和存储过程的使用视图和存储过程是将逻辑封装在数据库中的强大工具。

视图可以简化复杂的查询，并提供数据安全性。

存储过程可以将一系列的SQL语句封装成一个可重复使用的程序单元，提高数据库的性能和可维护性。

5. 使用事务管理事务管理是确保数据的一致性和完整性的关键机制。

在数据库设计中，应合理使用事务，以保证数据的正确性。

只有当一系列的操作都成功完成时，才将数据持久化到数据库中。

6. 避免过度规范化规范化是数据库设计中常用的一种技术，可以减少数据冗余和提高数据的一致性。

然而，过度规范化会导致查询性能下降，增加查询的复杂度。

如何使用PostgreSQL进行高性能数据库操作

如何使用PostgreSQL进行高性能数据库操作第一章：介绍PostgreSQLPostgreSQL是一种功能强大的开源关系型数据库管理系统，具有高度可定制性和安全性。

它在可扩展性和性能方面都表现出色，广泛用于各种应用程序和系统中。

本章将介绍PostgreSQL的基本概念和特点。

1.1 PostgreSQL的特点PostgreSQL支持丰富的数据类型、复杂的查询和事务处理。

它提供了多线程并发控制机制、高效的查询优化器以及完善的安全性保护机制。

此外，PostgreSQL还支持外键、触发器、存储过程和视图等高级数据库功能。

1.2 PostgreSQL的性能特点PostgreSQL的高性能主要体现在以下几个方面：1) 多线程并发：PostgreSQL使用多线程技术来处理并发请求，提高了数据库处理能力；2) 查询优化器：PostgreSQL的查询优化器能够通过分析查询语句，选择最优的执行计划，提高查询性能；3) 数据索引：PostgreSQL支持多种类型的索引，如B树索引、哈希索引和GiST索引等，用于加速数据的检索；4) 数据缓存：PostgreSQL使用共享缓存来提高数据读取性能；5) 数据分区：PostgreSQL支持数据分区技术，可以将大量数据分散存储在不同的物理设备上，提高数据查询和管理的效率。

第二章：设计高性能数据库模式在使用PostgreSQL进行高性能数据库操作前，需要设计合理的数据库模式。

本章将介绍设计高性能数据库模式的原则和方法。

2.1 数据规范化数据规范化是数据库设计的基本原则，可以减少数据存储冗余，并提高数据一致性和完整性。

合理地规范化数据库模式，可以提高数据库的效率和性能。

2.2 数据索引合理地创建索引可以加速数据的检索操作。

在设计数据库模式时，需要考虑哪些列需要创建索引以及使用何种类型的索引。

2.3 数据分区对于大规模的数据库，可以考虑使用数据分区技术将数据分散存储在不同的物理设备上，提高数据查询和管理的效率。

pgsql 慢sql查询语句

pgsql 慢sql查询语句"解决 PostgreSQL 中慢 SQL 查询语句的方法"在使用 PostgreSQL 数据库时，经常会遇到慢 SQL 查询语句的问题，这可能会影响应用程序的性能和用户体验。

在本文中，我们将探讨一些解决 PostgreSQL 中慢 SQL 查询语句的方法。

1. 使用索引，索引是提高查询性能的重要工具。

确保在经常使用的列上创建索引，这样可以加快查询速度。

可以使用 EXPLAIN ANALYZE 命令来查看查询计划，以确定是否使用了索引。

2. 优化查询语句，仔细审查查询语句，确保它们是最有效的。

避免使用 SELECT ，而是只选择需要的列。

尽量避免使用复杂的子查询和连接操作，这可能导致性能下降。

3. 使用 ANALYZE 命令，ANALYZE 命令可以帮助 PostgreSQL 收集统计信息，以便优化查询计划。

通过定期运行 ANALYZE 命令，可以确保数据库中的统计信息是最新的。

4. 调整配置参数，通过调整 PostgreSQL 的配置参数，可以改善查询性能。

例如，增加 shared_buffers 和 work_mem 的大小，可以提高内存使用效率，从而加快查询速度。

5. 监控慢查询，使用 PostgreSQL 的日志功能来监控慢查询，找出哪些查询语句性能较差。

可以使用 pg_stat_statements 扩展来跟踪查询性能，并找出慢查询的原因。

通过以上方法，我们可以有效地解决 PostgreSQL 中慢 SQL 查询语句的问题，提高数据库性能，从而改善应用程序的用户体验。

希望这些方法能帮助您更好地优化 PostgreSQL 数据库的性能。

数据库性能调优常见问题及解决方案

数据库性能调优常见问题及解决方案数据库作为现代应用系统的核心部分，承担着存储和管理大量数据的重要任务。

在大数据时代，数据库性能的优化成为了至关重要的任务，它直接影响着应用系统的响应时间和用户体验。

然而，在实际应用中，常常会遇到一些常见的性能问题，本文将分析并提供相应的解决方案，希望能够帮助读者解决数据库性能调优中遇到的困难。

问题一：查询慢查询慢是数据库性能调优中常见的问题之一。

造成查询慢的原因可能有很多，这里列举一些常见的原因及对应的解决方案。

1. 缺乏合适的索引：在查询语句中，没有使用到合适的索引会导致全表扫描，从而影响查询性能。

解决方案是分析查询语句，并在关键列上创建合适的索引。

2. 查询语句不优化：有时候，查询语句本身存在性能问题，例如使用不必要的表连接或不恰当的条件，导致查询效率低下。

解决方案是对查询语句进行分析和优化，尽量减少不必要的表连接和条件。

3. 数据库表设计不合理：数据库表的设计不合理，例如表的字段过多、冗余或无关字段的存在，都会导致查询性能下降。

解决方案是对数据库表进行优化，删减冗余字段，合理设计表结构。

问题二：并发冲突并发冲突是指在多用户访问数据库的情况下，由于操作顺序不当造成的数据冲突或一致性问题。

并发冲突也是数据库性能调优中常见的问题之一。

以下是一些常见的并发冲突及解决方案。

1. 脏读：脏读是指在并发环境下，一个事务读取到了另一个未提交事务的数据。

解决方案是通过设置数据库的隔离级别，使用合适的锁机制来避免脏读。

2. 死锁：死锁是指两个或多个事务相互等待对方释放资源而无法继续执行的情况。

解决方案是通过合理的资源调度和死锁检测机制，避免死锁的发生。

3. 更新丢失：更新丢失是指在并发环境下，多个事务同时修改同一条数据时，仅仅有一个事务的修改被生效，而其他事务的修改被覆盖。

解决方案是使用乐观锁或悲观锁机制来保证数据的一致性。

问题三：磁盘IO压力大磁盘IO是数据库性能的瓶颈之一。

postgresql内存关键参数调优（work_mem）

postgresql内存关键参数调优（work_mem）work_mem 参数调优work_mem：在pgsql 8.0之前叫做sort_mem。

postgresql在执⾏排序操作时，会根据work_mem的⼤⼩决定是否将⼀个⼤的结果集拆分为⼏个⼩的和work_mem查不多⼤⼩的临时⽂件。

显然拆分的结果是降低了排序的速度。

因此增加work_mem有助于提⾼排序的速度。

通常设置为实际RAM的2% -4%，根据需要排序结果集的⼤⼩⽽定，⽐如81920(80M)。

备注：以上的官⽅的描述。

但在实际的业务中会有所不同，如纯粹的交易系统（oltp-交易多为⼏⾏内操作，但⽐较频繁）这样的系统⼏乎不涉及到排序操作，或者说涉及的排序操作数据也是相当的少（如⼗、百条数据排序），这样就没有必要去调整该参数。

如业务⽩天是oltp ，⽽晚间是olap（olap-分析系统）。

还有些系统只为数据分析⽽是⽤。

他们的使⽤还是有点区别。

如果排序处理的不合理，很有可能造成服务器利⽤率降低。

排序操作：在平时的sql语句中有好多sql都是有排序的操作，最典型的有group by ORDER BY，DISTINCT，有些连接操作，CREATE INDE X要⽤到排序操作测试案例：服务器：内存24G (该参数只和内存有关，如在同⼀太服务器上测试，其他指标不⽤关⼼)sql语句(该语句是业务真实语句)：insert into dw_analyse_file ( minserid ,rowcount,fname ,imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon , newfilenamecon )select min(serid) as minserial,count(*) as rowcount,fname , imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon , newfilenameconfrom source_analyse_filegroup by fname , imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon , newfilenamecon说明：source_analyse_file据表⼤⼩6508 MB 、⾏数(已计数) 24031104执⾏第⼀组语句：TRUNCATE TABLE dw_analyse_file; --清空表记录set work_mem='8000MB'; --更改参数⼤⼩8Ginsert into dw_analyse_file ( minserid , rowcount, fname , imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon , newfilenamecon )select min(serid) as minserial,count(*) as rowcount,fname , imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon , newfilenameconfrom source_analyse_filegroup by fname , imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon , newfilenamecon执⾏结果：--查询成功: 共计8959657 ⾏受到影响，耗时: 202020 毫秒(ms)。

PostgreSQL性能优化综合案例-2

PostgreSQL性能优化综合案例-2【调优阶段8】1. 压⼒测试pgbench -M prepared -r -c 1 -f /home/postgres/test/login0.sql -j 1 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login0 & pgbench -M prepared -r -c 1 -f /home/postgres/test/login1.sql -j 1 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login1 & pgbench -M prepared -r -c 2 -f /home/postgres/test/login2.sql -j 2 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login2 & pgbench -M prepared -r -c 2 -f /home/postgres/test/login3.sql -j 2 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login3 & pgbench -M prepared -r -c 2 -f /home/postgres/test/login4.sql -j 2 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login4 & 2. 测试结果cat log.log*transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 1number of threads: 1duration: 180 snumber of transactions actually processed: 296485tps = 1647.130827 (including connections establishing)tps = 1647.153173 (excluding connections establishing)statement latencies in milliseconds:0.003394 \setrandom userid 1 40000000.599293 SELECT f_user_login_0(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 1number of threads: 1duration: 180 snumber of transactions actually processed: 270077tps = 1500.414232 (including connections establishing)tps = 1500.434330 (excluding connections establishing)statement latencies in milliseconds:0.004436 \setrandom userid 4000001 80000000.656274 SELECT f_user_login_1(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 2duration: 180 snumber of transactions actually processed: 543390tps = 3018.814281 (including connections establishing)tps = 3018.901510 (excluding connections establishing)statement latencies in milliseconds:0.004553 \setrandom userid 8000001 120000000.652033 SELECT f_user_login_2(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 2duration: 180 snumber of transactions actually processed: 592774tps = 3293.147194 (including connections establishing)tps = 3293.235012 (excluding connections establishing)statement latencies in milliseconds:0.003446 \setrandom userid 12000001 160000000.599297 SELECT f_user_login_3(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 2duration: 180 snumber of transactions actually processed: 593614tps = 3297.831371 (including connections establishing)tps = 3297.946707 (excluding connections establishing)statement latencies in milliseconds:0.003421 \setrandom userid 16000001 200000000.598465 SELECT f_user_login_4(:userid);总计 :tps = 12757.337905 (including connections establishing)3. 瓶颈分析与优化测试中我们使⽤的数据库服务器cpu是8核的服务器, 根据以往的经验, 当活跃的进程数等于核数的2倍时可以发挥CPU的最⼤能⼒.所以我们通过增加并发连接来看看到底有多少性能提升.【调优阶段9】1. 压⼒测试pgbench -M prepared -r -c 2 -f /home/postgres/test/login0.sql -j 2 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login0 &pgbench -M prepared -r -c 2 -f /home/postgres/test/login1.sql -j 2 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login1 &pgbench -M prepared -r -c 4 -f /home/postgres/test/login2.sql -j 4 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login2 &pgbench -M prepared -r -c 4 -f /home/postgres/test/login3.sql -j 4 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login3 &pgbench -M prepared -r -c 4 -f /home/postgres/test/login4.sql -j 4 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login4 &2. 测试结果cat log.log*transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 2duration: 180 snumber of transactions actually processed: 375743tps = 2087.443600 (including connections establishing)tps = 2087.489913 (excluding connections establishing)statement latencies in milliseconds:0.003492 \setrandom userid 1 40000000.949744 SELECT f_user_login_0(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 2duration: 180 snumber of transactions actually processed: 367801tps = 2043.313370 (including connections establishing)tps = 2043.386454 (excluding connections establishing)statement latencies in milliseconds:0.003710 \setrandom userid 4000001 80000000.969828 SELECT f_user_login_1(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 4number of threads: 4duration: 180 snumber of transactions actually processed: 730267tps = 4057.007177 (including connections establishing)tps = 4057.148280 (excluding connections establishing)statement latencies in milliseconds:0.003962 \setrandom userid 8000001 120000000.976372 SELECT f_user_login_2(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 4number of threads: 4duration: 180 snumber of transactions actually processed: 738398tps = 4101.985844 (including connections establishing)tps = 4102.135039 (excluding connections establishing)statement latencies in milliseconds:0.003615 \setrandom userid 12000001 160000000.966314 SELECT f_user_login_3(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 4number of threads: 4duration: 180 snumber of transactions actually processed: 732793tps = 4070.957105 (including connections establishing)tps = 4071.200533 (excluding connections establishing)statement latencies in milliseconds:0.003882 \setrandom userid 16000001 200000000.973208 SELECT f_user_login_4(:userid);tps = 16360.707096 (including connections establishing)tps = 16361.360219 (excluding connections establishing)3. 瓶颈分析与优化继续增加连接,tps还可以再提⾼吗? : 不可以.8核的机器16个活动的会话基本上就到达它的上限了.因此要提⾼tps还可以加CPU.下⾯增加连接到30个的测试结果证明了上⾯的结论.pgbench -M prepared -r -c 6 -f /home/postgres/test/login0.sql -j 6 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login0 & pgbench -M prepared -r -c 6 -f /home/postgres/test/login1.sql -j 6 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login1 & pgbench -M prepared -r -c 6 -f /home/postgres/test/login2.sql -j 6 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login2 & pgbench -M prepared -r -c 6 -f /home/postgres/test/login3.sql -j 6 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login3 & pgbench -M prepared -r -c 6 -f /home/postgres/test/login4.sql -j 6 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login4 & 结果cat log.log*transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 6number of threads: 6duration: 180 snumber of transactions actually processed: 544811tps = 3026.494301 (including connections establishing)tps = 3026.608244 (excluding connections establishing)statement latencies in milliseconds:0.003768 \setrandom userid 1 40000001.973230 SELECT f_user_login_0(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 6number of threads: 6duration: 180 snumber of transactions actually processed: 544485tps = 3024.298399 (including connections establishing)tps = 3024.468785 (excluding connections establishing)statement latencies in milliseconds:0.003735 \setrandom userid 4000001 80000001.974466 SELECT f_user_login_1(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 6number of threads: 6duration: 180 snumber of transactions actually processed: 544778tps = 3025.262019 (including connections establishing)tps = 3025.469901 (excluding connections establishing)statement latencies in milliseconds:0.003707 \setrandom userid 8000001 120000001.973661 SELECT f_user_login_2(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 6number of threads: 6duration: 180 snumber of transactions actually processed: 542008tps = 3010.921306 (including connections establishing)tps = 3011.146550 (excluding connections establishing)statement latencies in milliseconds:0.003662 \setrandom userid 12000001 160000001.983714 SELECT f_user_login_3(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 6number of threads: 6duration: 180 snumber of transactions actually processed: 539505tps = 2996.511493 (including connections establishing)tps = 2996.874239 (excluding connections establishing)statement latencies in milliseconds:0.003768 \setrandom userid 16000001 200000001.992923 SELECT f_user_login_4(:userid);总计 :tps = 15083.487518 (including connections establishing)tps = 15084.567719 (excluding connections establishing)连接数超过2倍核数后根本不会有性能提升了, 这台服务器的潜⼒基本上挖掘得差不多了.接下来就需要通过增加服务器来提升数据库的整体性能了.⾸先要⽤到的是PostgreSQL的流复制, 通过hot standby可以进⾏读写分离, 也就是将SELECT的请求分发到hot standby上. (需要注意跨库事务的问题, 如standby的延时, 这⾥不详细阐述)新建查询函数和插⼊更新函数 :create or replace function f_user_login_sel_0(i_userid int,OUT o_userid int,OUT o_engname text,OUT o_cnname text,OUT o_occupation text,OUT o_birthday date,OUT o_signname text,OUT o_email text,OUT o_qq numeric)as $BODY$declarebeginselect userid,engname,cnname,occupation,birthday,signname,email,qqinto o_userid,o_engname,o_cnname,o_occupation,o_birthday,o_signname,o_email,o_qqfrom user_info_0 where userid=i_userid;return;end;$BODY$language plpgsql;create or replace function f_user_login_sel_1(i_userid int,OUT o_userid int,OUT o_engname text,OUT o_cnname text,OUT o_occupation text,OUT o_birthday date,OUT o_signname text,OUT o_email text,OUT o_qq numeric)as $BODY$declarebeginselect userid,engname,cnname,occupation,birthday,signname,email,qqinto o_userid,o_engname,o_cnname,o_occupation,o_birthday,o_signname,o_email,o_qqfrom user_info_1 where userid=i_userid;return;end;$BODY$language plpgsql;create or replace function f_user_login_sel_2(i_userid int,OUT o_userid int,OUT o_engname text,OUT o_cnname text,OUT o_occupation text,OUT o_birthday date,OUT o_signname text,OUT o_email text,OUT o_qq numeric)as $BODY$declarebeginselect userid,engname,cnname,occupation,birthday,signname,email,qqinto o_userid,o_engname,o_cnname,o_occupation,o_birthday,o_signname,o_email,o_qqfrom user_info_2 where userid=i_userid;return;end;$BODY$language plpgsql;create or replace function f_user_login_sel_3OUT o_userid int,OUT o_engname text,OUT o_cnname text,OUT o_occupation text,OUT o_birthday date,OUT o_signname text,OUT o_email text,OUT o_qq numeric)as $BODY$declarebeginselect userid,engname,cnname,occupation,birthday,signname,email,qqinto o_userid,o_engname,o_cnname,o_occupation,o_birthday,o_signname,o_email,o_qq from user_info_3 where userid=i_userid;return;end;$BODY$language plpgsql;create or replace function f_user_login_sel_4(i_userid int,OUT o_userid int,OUT o_engname text,OUT o_cnname text,OUT o_occupation text,OUT o_birthday date,OUT o_signname text,OUT o_email text,OUT o_qq numeric)as $BODY$declarebeginselect userid,engname,cnname,occupation,birthday,signname,email,qqinto o_userid,o_engname,o_cnname,o_occupation,o_birthday,o_signname,o_email,o_qq from user_info_4 where userid=i_userid;return;end;$BODY$language plpgsql;create or replace function f_user_login_insupd_0(i_userid int)returns int as $BODY$declarebegininsert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr()); update user_session_0 set logintime=now(),login_count=login_count+1 where userid=i_userid; return 0;exceptionwhen others thenreturn 1;end;$BODY$language plpgsql;create or replace function f_user_login_insupd_1(i_userid int)returns int as $BODY$declarebegininsert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr()); update user_session_1 set logintime=now(),login_count=login_count+1 where userid=i_userid; return 0;exceptionwhen others thenreturn 1;end;$BODY$language plpgsql;create or replace function f_user_login_insupd_2(i_userid int)returns int as $BODY$declarebegininsert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr()); update user_session_2 set logintime=now(),login_count=login_count+1 where userid=i_userid; return 0;when others thenreturn 1;end;$BODY$language plpgsql;create or replace function f_user_login_insupd_3(i_userid int)returns int as $BODY$declarebegininsert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr());update user_session_3 set logintime=now(),login_count=login_count+1 where userid=i_userid;return 0;exceptionwhen others thenreturn 1;end;$BODY$language plpgsql;create or replace function f_user_login_insupd_4(i_userid int)returns int as $BODY$declarebegininsert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr());update user_session_4 set logintime=now(),login_count=login_count+1 where userid=i_userid;return 0;exceptionwhen others thenreturn 1;end;$BODY$language plpgsql;hot standby库也需要将数据加载到内存, 具体操作略.【调优阶段10】1. 测试脚本cat log*\setrandom userid 1 4000000SELECT f_user_login_insupd_0(:userid);\setrandom userid 4000001 8000000SELECT f_user_login_insupd_1(:userid);\setrandom userid 8000001 12000000SELECT f_user_login_insupd_2(:userid);\setrandom userid 12000001 16000000SELECT f_user_login_insupd_3(:userid);\setrandom userid 16000001 20000000SELECT f_user_login_insupd_4(:userid);\setrandom userid 1 4000000SELECT f_user_login_sel_0(:userid);\setrandom userid 4000001 8000000SELECT f_user_login_sel_1(:userid);\setrandom userid 8000001 12000000SELECT f_user_login_sel_2(:userid);\setrandom userid 12000001 16000000SELECT f_user_login_sel_3(:userid);\setrandom userid 16000001 20000000SELECT f_user_login_sel_4(:userid);2. 压⼒测试pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel0.sql -j 3 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login_sel0 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel1.sql -j 3 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login_sel1 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel2.sql -j 3 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login_sel2 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel3.sql -j 3 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login_sel3 & pgbench -M prepared -r -c 4 -f /home/postgres/test_zsplit/login_sel4.sql -j 4 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login_sel4 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_insupd0.sql -j 3 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_insupd0 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_insupd1.sql -j 3 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_insupd1 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_insupd2.sql -j 3 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_insupd2 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_insupd3.sql -j 3 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_insupd3 & pgbench -M prepared -r -c 4 -f /home/postgres/test_zsplit/login_insupd4.sql -j 4 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_insupd4 & 3. 测试结果hot standby的测试数据 :cat log.login_sel*transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 3number of threads: 3duration: 180 snumber of transactions actually processed: 552618tps = 3012.767914 (including connections establishing) tps = 3012.877330 (excluding connections establishing) statement latencies in milliseconds:0.003166 \setrandom userid 1 40000000.988247 SELECT f_user_login_sel_0(:userid); transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 3number of threads: 3duration: 180 snumber of transactions actually processed: 750314tps = 4089.671930 (including connections establishing) tps = 4089.771337 (excluding connections establishing) statement latencies in milliseconds:0.003030 \setrandom userid 4000001 8000000 0.726462 SELECT f_user_login_sel_1(:userid); transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 3number of threads: 3duration: 180 snumber of transactions actually processed: 727839tps = 3967.242817 (including connections establishing) tps = 3967.364415 (excluding connections establishing) statement latencies in milliseconds:0.003260 \setrandom userid 8000001 12000000 0.748466 SELECT f_user_login_sel_2(:userid); transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 3number of threads: 3duration: 180 snumber of transactions actually processed: 715952tps = 3903.028278 (including connections establishing) tps = 3903.130455 (excluding connections establishing) statement latencies in milliseconds:0.003077 \setrandom userid 12000001 16000000 0.761439 SELECT f_user_login_sel_3(:userid); transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 4number of threads: 4duration: 180 snumber of transactions actually processed: 964366tps = 5257.974345 (including connections establishing) tps = 5258.120849 (excluding connections establishing) statement latencies in milliseconds:0.003153 \setrandom userid 16000001 20000000 0.753196 SELECT f_user_login_sel_4(:userid); 总计 :tps = 20230.685284 (including connections establishing) tps = 20231.264386 (excluding connections establishing) primary的测试数据 :cat log.login_insupd*transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 3number of threads: 3duration: 180 snumber of transactions actually processed: 745415tps = 4141.145602 (including connections establishing) tps = 4141.250129 (excluding connections establishing) statement latencies in milliseconds:0.716912 SELECT f_user_login_insupd_0(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 3number of threads: 3duration: 180 snumber of transactions actually processed: 737761tps = 4098.582645 (including connections establishing)tps = 4098.704693 (excluding connections establishing)statement latencies in milliseconds:0.003360 \setrandom userid 4000001 80000000.723997 SELECT f_user_login_insupd_1(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 3number of threads: 3duration: 180 snumber of transactions actually processed: 761171tps = 4228.709500 (including connections establishing)tps = 4228.817139 (excluding connections establishing)statement latencies in milliseconds:0.003333 \setrandom userid 8000001 120000000.701648 SELECT f_user_login_insupd_2(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 3number of threads: 3duration: 180 snumber of transactions actually processed: 761960tps = 4233.031271 (including connections establishing)tps = 4233.166856 (excluding connections establishing)statement latencies in milliseconds:0.003306 \setrandom userid 12000001 160000000.700967 SELECT f_user_login_insupd_3(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 4number of threads: 4duration: 180 snumber of transactions actually processed: 999167tps = 5550.893825 (including connections establishing)tps = 5551.246720 (excluding connections establishing)statement latencies in milliseconds:0.003385 \setrandom userid 16000001 200000000.712689 SELECT f_user_login_insupd_4(:userid);总计 :tps = 22252.362843 (including connections establishing)tps = 22253.185537 (excluding connections establishing)QPS :qps = 20230.685284 + (22252.362843 * 2) (including connections establishing)qps = 20231.264386 + (22253.185537 * 2) (excluding connections establishing)4. 瓶颈分析与优化主节点 :avg-cpu: %user %nice %system %iowait %steal %idle56.30 0.00 21.72 4.24 0.00 17.73Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sdc 0.00 2781.50 0.00 93.50 0.00 22876.00 244.66 0.09 0.93 0.90 8.40sdd 0.00 10656.50 0.00 2302.50 0.00 105300.00 45.73 108.00 27.85 0.43 100.05 dm-0 0.00 0.00 0.00 2875.50 0.00 23004.00 8.00 2.56 0.89 0.03 8.30dm-1 0.00 0.00 0.00 12943.00 0.00 103544.00 8.00 569.00 34.94 0.08 100.10 dm-2 0.00 0.00 0.00 2832.50 0.00 22660.00 8.00 2.55 0.90 0.03 8.05dm-3 0.00 0.00 0.00 41.50 0.00 332.00 8.00 0.02 0.54 0.06 0.25dm-4 0.00 0.00 0.00 1.50 0.00 12.00 8.00 0.00 0.00 0.00 0.00dm-5 0.00 0.00 0.00 1.00 0.00 8.00 8.00 0.01 0.00 4.00 0.40dm-6 0.00 0.00 0.00 11545.50 0.00 92364.00 8.00 505.23 33.04 0.08 91.75 dm-7 0.00 0.00 0.00 1396.50 0.00 11172.00 8.00 63.54 50.65 0.15 20.65 standby节点 :avg-cpu: %user %nice %system %iowait %steal %idle0.00 0.00 0.31 12.87 0.00 86.82Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %utilcciss/c0d0 0.00 1222.39 0.00 996.52 0.00 19136.32 19.20 113.22 116.63 1.00 99.55cciss/c0d0p1 0.00 2.99 0.00 1.00 0.00 31.84 32.00 0.10 101.50 101.50 10.10cciss/c0d0p2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00cciss/c0d0p3 0.00 1219.40 0.00 995.52 0.00 19104.48 19.19 113.12 116.64 1.00 99.55cciss/c0d1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00cciss/c0d2 0.00 1384.08 0.00 251.74 0.00 13297.51 52.82 142.31 522.75 3.95 99.55cciss/c0d3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00cciss/c0d4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00cciss/c0d5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00dm-1 0.00 0.00 0.00 1638.81 0.00 13110.45 8.00 946.36 538.61 0.61 99.55dm-2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00dm-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00dm-4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00dm-5 0.00 0.00 0.00 2193.03 0.00 17544.28 8.00 275.53 132.89 0.45 99.55显然IO到达瓶颈了. 为什么每次IO都顶不住呢? 是的, 机械硬盘的随机IOPS能⼒就是这么差, 不要有太⾼的奢望.要提升IOPS要么就⽤⾼端存储要么就选择SSD硬盘. 下次有机会找块ssd硬盘来测试⼀下它的iops能⼒到底有多强.那么这些IO是怎么产⽣的呢?1. 主库的IO来⾃insert和update请求.2. hot standby的IO来⾃stream data recovery.因为我的测试环境没有办法扩存储, 所以这⾥就不通过扩存储来解决这个瓶颈了, 还是加服务器.但是这次加2台服务器, 1台⽤来做hot standby. 另⼀台我要把insert请求剥离过去.也就是总共⽤4台服务器.具体的操作如下 :初始化新增的⽇志库 :create table user_login_rec(userid int,login_time timestamp without time zone,ip inet);create table user_logout_rec(userid int,logout_time timestamp without time zone,ip inet);create or replace function f_user_login_ins(i_userid int)returns int as $BODY$declarebegininsert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr());return 0;exceptionwhen others thenreturn 1;end;$BODY$language plpgsql;主库新增函数 :create or replace function f_user_login_upd_0(i_userid int)returns int as $BODY$declarebeginupdate user_session_0 set logintime=now(),login_count=login_count+1 where userid=i_userid;return 0;exceptionwhen others thenreturn 1;end;$BODY$language plpgsql;create or replace function f_user_login_upd_1(i_userid int)returns int as $BODY$declarebeginupdate user_session_1 set logintime=now(),login_count=login_count+1 where userid=i_userid; return 0;exceptionwhen others thenreturn 1;end;$BODY$language plpgsql;create or replace function f_user_login_upd_2(i_userid int)returns int as $BODY$declarebeginupdate user_session_2 set logintime=now(),login_count=login_count+1 where userid=i_userid; return 0;exceptionwhen others thenreturn 1;end;$BODY$language plpgsql;create or replace function f_user_login_upd_3(i_userid int)returns int as $BODY$declarebeginupdate user_session_3 set logintime=now(),login_count=login_count+1 where userid=i_userid; return 0;exceptionwhen others thenreturn 1;end;$BODY$language plpgsql;create or replace function f_user_login_upd_4(i_userid int)returns int as $BODY$declarebeginupdate user_session_4 set logintime=now(),login_count=login_count+1 where userid=i_userid; return 0;exceptionwhen others thenreturn 1;end;$BODY$language plpgsql;再增加⼀台standby, 流复制过程略, 请参考我写过的流复制环境搭建BLOG.《PostgreSQL HOT STANDBY using Stream》优化当前环境如下,primary : 172.16.3.150standby1 : 172.16.3.33standby2 : 172.16.3.39logdb : 172.16.3.40【调优阶段11】1. 测试脚本postgres@db5-> cat login_ins.sql\setrandom userid 1 20000000SELECT f_user_login_ins(:userid);postgres@db5-> cat login_sel*\setrandom userid 1 4000000\setrandom userid 4000001 8000000SELECT f_user_login_sel_1(:userid);\setrandom userid 8000001 12000000SELECT f_user_login_sel_2(:userid);\setrandom userid 12000001 16000000SELECT f_user_login_sel_3(:userid);\setrandom userid 16000001 20000000SELECT f_user_login_sel_4(:userid);postgres@db5-> cat login_upd*\setrandom userid 1 4000000SELECT f_user_login_upd_0(:userid);\setrandom userid 4000001 8000000SELECT f_user_login_upd_1(:userid);\setrandom userid 8000001 12000000SELECT f_user_login_upd_2(:userid);\setrandom userid 12000001 16000000SELECT f_user_login_upd_3(:userid);\setrandom userid 16000001 20000000SELECT f_user_login_upd_4(:userid);2. 压⼒测试pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel0.sql -j 3 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login33_sel0 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel1.sql -j 3 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login33_sel1 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel2.sql -j 3 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login33_sel2 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel3.sql -j 3 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login33_sel3 & pgbench -M prepared -r -c 4 -f /home/postgres/test_zsplit/login_sel4.sql -j 4 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login33_sel4 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel0.sql -j 3 -n -T 180 -h 172.16.3.39 -p 1921 -U digoal digoal >./log.login39_sel0 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel1.sql -j 3 -n -T 180 -h 172.16.3.39 -p 1921 -U digoal digoal >./log.login39_sel1 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel2.sql -j 3 -n -T 180 -h 172.16.3.39 -p 1921 -U digoal digoal >./log.login39_sel2 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel3.sql -j 3 -n -T 180 -h 172.16.3.39 -p 1921 -U digoal digoal >./log.login39_sel3 & pgbench -M prepared -r -c 4 -f /home/postgres/test_zsplit/login_sel4.sql -j 4 -n -T 180 -h 172.16.3.39 -p 1921 -U digoal digoal >./log.login39_sel4 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_upd0.sql -j 3 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_upd0 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_upd1.sql -j 3 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_upd1 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_upd2.sql -j 3 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_upd2 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_upd3.sql -j 3 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_upd3 & pgbench -M prepared -r -c 4 -f /home/postgres/test_zsplit/login_upd4.sql -j 4 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_upd4 & pgbench -M prepared -r -c 16 -f /home/postgres/test_zsplit/login_ins.sql -j 16 -n -T 180 -h 172.16.3.40 -p 1921 -U digoal digoal >./log.login_ins & 3. 测试结果cat log.login33_sel*transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 3number of threads: 3duration: 180 snumber of transactions actually processed: 1534211tps = 8523.315651 (including connections establishing)tps = 8523.524318 (excluding connections establishing)statement latencies in milliseconds:0.002438 \setrandom userid 1 40000000.346514 SELECT f_user_login_sel_0(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 3number of threads: 3duration: 180 snumber of transactions actually processed: 1533785tps = 8520.894378 (including connections establishing)tps = 8521.168645 (excluding connections establishing)statement latencies in milliseconds:0.002423 \setrandom userid 4000001 80000000.346564 SELECT f_user_login_sel_1(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 3number of threads: 3duration: 180 snumber of transactions actually processed: 1544585tps = 8580.974433 (including connections establishing)tps = 8581.260902 (excluding connections establishing)statement latencies in milliseconds:0.002448 \setrandom userid 8000001 120000000.344071 SELECT f_user_login_sel_2(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 3number of threads: 3。

数据库性能调优方法

数据库性能调优方法数据库性能调优是提高数据库系统性能的重要手段，它在现代信息系统中具有非常重要的作用。

本文将介绍几种常用的数据库性能调优方法，包括索引优化、查询优化、硬件优化以及定期维护等。

一、索引优化索引是数据库性能调优中最常用的方法之一。

通过合理的创建、调整和优化索引，可以极大地提高数据库的查询效率。

以下是一些常见的索引优化方法：1.选择合适的索引类型：根据实际需求选择适合的索引类型，如主键索引、唯一索引、聚簇索引等。

2.缩小索引范围：只对需要进行查询和排序的列创建索引，避免不必要的索引占用存储空间。

3.避免过多的联合索引：过多的联合索引会增加索引维护的成本，降低数据库性能。

4.定期重建和重组索引：删除不需要的索引，重新构建和重组索引，优化索引布局。

二、查询优化查询优化是提高数据库性能的关键环节之一。

通过合理的查询编写和优化，可以减少查询的时间和资源消耗。

以下是一些常见的查询优化方法：1.选择合适的查询语句：根据查询需求选择合适的查询语句，避免不必要的数据量和计算量。

2.使用合适的连接方式：根据实际情况选择适合的连接方式，如内连接、外连接等。

3.使用索引优化查询：利用索引加速查询，避免全表扫描和排序操作。

4.避免使用子查询：尽量避免使用子查询，因为子查询会增加数据库的负载和查询时间。

三、硬件优化硬件优化是提高数据库性能的基础之一。

通过合理的硬件调整和优化，可以提高数据库系统的吞吐量和响应速度。

以下是一些常见的硬件优化方法：1.增加内存容量：增加数据库服务器的内存容量，提高数据的缓存命中率。

2.使用高速存储设备：使用高速存储设备，如固态硬盘（SSD），提高数据库的读写速度。

3.优化磁盘配置：合理配置磁盘阵列，提高数据库的IO性能。

4.定期备份和优化数据库：定期备份数据库，清理无效数据，优化数据库性能。

四、定期维护定期维护是保证数据库系统稳定性和性能的必要手段。

以下是一些常见的定期维护方法：1.定期更新数据库统计信息：通过更新数据库统计信息，数据库优化器可以更好地选择执行计划。

数据库参数调优方法与技巧

数据库参数调优方法与技巧数据库参数调优是提高数据库性能和优化数据库资源利用的重要手段。

通过合理设置数据库参数，可以改善数据库的响应时间、减少数据库运行时的资源消耗，并提升数据库的整体性能。

本文将介绍一些常用的数据库参数调优方法与技巧。

1. 分析数据库性能问题在进行数据库参数调优之前，首先需要分析数据库性能问题。

可以通过数据库性能监控工具或日志来识别数据库的瓶颈，例如处理速度慢、长时间的锁或等待事件等。

2. 确定合适的硬件配置数据库的性能与硬件密切相关，因此，确保数据库服务器具备足够的内存、存储和计算能力是非常重要的。

可以通过增加内存、添加磁盘阵列、升级处理器等方式提升数据库性能。

3. 优化索引合理的索引设计对于提升数据库性能至关重要。

通过分析查询语句和表的访问模式，优化数据库的索引可以减少磁盘IO的次数，提升查询性能。

4. 调整数据库缓存数据库缓存是数据库系统中的一个重要组成部分，它可以存储常用的数据和查询结果。

通过合理调整数据库缓冲区的大小，可以减少磁盘IO的次数，提升数据库查询的速度。

另外，注意设置适当的缓冲区和检查点的参数，以避免发生内存溢出或写入瓶颈。

5. 调整日志参数数据库的事务日志是重要的数据恢复和事务一致性的保证。

通过合理调整日志参数，如日志缓冲区的大小和日志刷新频率，可以提升数据库写入性能并降低事务提交的等待时间。

6. 查询语句优化优化查询语句是提高数据库性能的有效方法。

通过深入了解业务需求和查询语句的执行计划，可以通过重写查询语句、修改表的结构，或增加合适的索引等方式来优化查询性能。

7. 参数适应性调整数据库参数的默认值并不能适应所有场景。

根据业务需求和数据库使用情况，可以适当调整数据库的参数设置，以提高数据库性能。

例如，修改缓冲区的大小、调整并发连接数、调整写入访问的比率等。

8. 定期收集统计信息定期收集数据库的统计信息是数据库性能调优的重要手段之一。

通过收集统计信息，可以优化查询计划，提高查询性能。

数据库性能调优的高级技巧与案例分析分享

数据库性能调优的高级技巧与案例分析分享随着信息技术的快速发展和大数据时代的到来，数据库的作用变得越来越重要。

然而，一旦数据库出现性能问题，将会严重威胁到整个系统的正常运行。

为了解决这一问题，数据库性能调优成为了数据库管理员和开发人员必备的技能之一。

本文将深入探讨数据库性能调优的高级技巧，并通过案例分析与读者分享宝贵的经验。

一、索引优化索引是提高数据库查询性能的关键。

不合理的索引设计会导致查询效率低下、数据更新缓慢等问题。

因此，合理设计和优化索引是数据库性能调优的重要环节之一。

案例：某电商平台的订单表中存在大量重复的索引，导致数据库性能严重下降。

通过删除重复索引和优化查询语句，将查询时间从30秒减少到3秒，大大提高了系统的响应速度。

二、查询优化查询是数据库操作的核心，优化查询语句可以有效提高数据库的性能。

常见的查询优化技巧包括合理选择查询关键字、避免全表扫描、使用索引等。

案例：某教育机构的学生信息查询功能存在严重的性能问题。

通过分析查询语句，对其中的子查询进行优化，从而大幅提升了查询速度和用户体验。

三、表设计优化合理的表设计可以减少数据库的冗余和重复数据，提高数据的存储效率和查询速度。

在数据库性能调优过程中，优化表设计是不可忽视的环节之一。

案例：某社交媒体平台的用户表中存在大量空值和冗余字段，导致数据冗余和查询效率低下。

通过重新设计表结构并使用关联表，成功减少了数据冗余并提高了查询速度。

四、缓存技术的应用利用缓存技术可以将频繁访问的数据存储在内存中，减少对数据库的访问次数，从而提高系统的响应速度和并发能力。

案例：某电商平台的商品信息查询功能存在严重的性能问题。

通过引入缓存技术，将热门商品的信息存储在内存中，大大提高了查询速度和用户体验。

五、分库分表技术当数据库面临数据量过大的情况时，采用分库分表技术可以将数据划分为多个数据库或多个表，从而提高数据库的读写性能。

案例：某金融机构的交易数据量庞大，数据库查询速度非常慢。

数据库性能调优方法与技巧

数据库性能调优方法与技巧数据库性能是一个关键的问题，对于应用程序的性能和响应时间至关重要。

因此，在开发应用程序时，我们需要重点关注数据库性能调优。

本文将介绍一些常用的数据库性能调优方法与技巧，以帮助读者优化数据库的性能。

一、合理设计数据库结构数据库的设计是决定性能的关键。

合理的数据库结构可以提高查询和操作的效率。

以下是一些合理设计数据库结构的方法：1. 规范化数据模型：将数据分解为更小的组件，减少数据的冗余，提高查询的效率。

2. 使用索引：在经常使用的字段上创建索引，可以加快查询速度。

不过需要注意，过多的索引会降低插入和更新的性能。

3. 合理选择数据类型：选择适合存储的数据类型，可以减少存储空间的占用，提高数据库的性能。

二、优化查询语句查询语句是应用程序与数据库之间的桥梁，优化查询语句可以大大提高数据库的性能。

以下是一些优化查询语句的方法：1. 避免全表扫描：尽量使用索引来查询数据，避免全表扫描的开销。

2. 减少查询次数：尽量将多个查询合并为一个查询，减少与数据库的交互次数。

3. 使用适当的关联条件：避免使用不必要的关联条件，只查询所需的数据，减少查询的数据量。

4. 避免使用子查询：子查询的性能通常很低，尽量使用连接查询来替代子查询。

三、配置合理的缓存策略数据库缓存是将热点数据加载到内存中，以加快对热点数据的访问速度。

以下是一些配置合理的缓存策略的方法：1. 增大缓存空间：适当增大数据库的缓存空间，可以提高热点数据的访问速度。

2. 使用LRU算法：最近最少使用（LRU）算法可以优先保留访问频率较高的数据，提高缓存的命中率。

3. 清除过期数据：定期清除过期的缓存数据，避免缓存空间被无效数据占用。

四、合理分配硬件资源合理分配硬件资源可以提高数据库的性能。

以下是一些合理分配硬件资源的方法：1. 使用高性能硬盘：选择性能较好的硬盘，可以提高数据的读写速度。

2. 增加内存容量：适当增加数据库的内存容量，可以提高查询和操作的效率。

postgresql 内存关键参数调优(work_mem)

work_mem 参数调优work_mem：在pgsql 8.0之前叫做sort_mem。

postgresql在执行排序操作时，会根据work_mem的大小决定是否将一个大的结果集拆分为几个小的和work_mem查不多大小的临时文件。

显然拆分的结果是降低了排序的速度。

因此增加work_mem有助于提高排序的速度。

通常设置为实际RAM的2% -4%，根据需要排序结果集的大小而定，比如81920(80M)。

备注：以上的官方的描述。

但在实际的业务中会有所不同，如纯粹的交易系统（oltp-交易多为几行内操作，但比较频繁）这样的系统几乎不涉及到排序操作，或者说涉及的排序操作数据也是相当的少（如十、百条数据排序），这样就没有必要去调整该参数。

如业务白天是oltp ，而晚间是olap（olap-分析系统）。

还有些系统只为数据分析而是用。

他们的使用还是有点区别。

如果排序处理的不合理，很有可能造成服务器利用率降低。

排序操作：在平时的sql语句中有好多sql都是有排序的操作，最典型的有group by ORDER BY，DISTINCT，有些连接操作，CREATE INDE X要用到排序操作测试案例：服务器：内存24G (该参数只和内存有关，如在同一太服务器上测试，其他指标不用关心)sql语句(该语句是业务真实语句)：insert into dw_analyse_file ( minserid ,rowcount,fname ,imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon , newfilenamecon )select min(serid) as minserial,count(*) as rowcount,fname , imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon , newfilenameconfrom source_analyse_filegroup by fname , imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon , newfilenamecon说明：source_analyse_file据表大小6508 MB 、行数(已计数) 24031104执行第一组语句：TRUNCATE TABLE dw_analyse_file; --清空表记录set work_mem='8000MB'; --更改参数大小8Ginsert into dw_analyse_file ( minserid , rowcount, fname , imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon , newfilenamecon )select min(serid) as minserial,count(*) as rowcount,fname , imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon , newfilenameconfrom source_analyse_filegroup by fname , imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon , newfilenamecon执行结果：--查询成功: 共计8959657 行受到影响，耗时: 202020 毫秒(ms)。

PostgreSQL数据库性能调优指南

重写前查询树
LOG: parse tree: DETAIL: {QUERY ......//省略
{ALIAS :aliasname myview :colnames ("id1" "id2") } ......//省略 }
重写后查询树
LOG: rewritten parse tree: DETAIL: (
autovacuum_work_mem (autovacuum_*等系列参数)
估算
如何估算autovacuum_work_mem? 建议：maintenenance_work_mem内存大小大约是最大表的
1/100
shared_buffers work_mem
dynamic_shared_memory_type
{QUERY ......//省略
{ALIAS :aliasname department :colnames ("id1" "id2")
} ......//省略
} )
子查询优化等价谓词/条件表达式优化
外连接优化
逻辑优化
查询优化
物理优化
单表优化两表优化大于两表的优化
ＰＧ的查询优化
并行查询
PostgreSQL数据库性能调优指南
技术创新，变革未来
影响数据库性能的三层架构
小
大
应用层
数据库层
物理层
大
小
目录
物理层优化数据库层优化应用层优化
硬件因素
影响性能的硬件因素
CPU
IO
内存
网络
新型硬件
容器资源弹性伸缩，动态调节PG处理能力

postgreSQL性能参数

postgreSQL性能参数PostgreSQL是一种强大的开源数据库管理系统，它具有可扩展性和高性能的特点。

性能参数在使用PostgreSQL来优化数据库性能和处理大量数据时起着至关重要的作用。

本篇文章将详细介绍一些常用的PostgreSQL性能参数及其作用。

1. shared_buffers: 这是控制PostgreSQL内存缓冲区大小的参数。

它指定了操作系统用于缓存数据库的页的数量。

合理设置该参数可以提高数据库的读取性能。

2. work_mem: 这个参数用于控制每个查询的内存使用量。

它指定了每个工作进程可用的内存大小。

如果查询需要排序、哈希或执行连接等操作，那么它所需的内存将由work_mem参数控制。

通过适当调整该参数，可以提高查询的性能。

3. maintenance_work_mem: 这个参数用于控制维护操作的内存使用量。

维护操作包括 VACUUM，CREATE INDEX，ALTER TABLE，REINDEX等。

合理设置maintenance_work_mem参数可以加速这些操作的执行。

4. effective_cache_size: 这个参数用于告诉PostgreSQL操作系统可以用于缓存的内存大小。

该参数的设置应该基于系统的内存大小和其他应用程序在同一服务器上的内存需求。

6. max_connections: 这个参数用于控制数据库服务器能够同时处理的最大连接数。

适当调整max_connections参数可以提高数据库的并发处理能力。

但是，需要注意的是，较大的max_connections值会增加数据库服务器的内存消耗。

7. autovacuum: 这是一个布尔参数，用于控制是否启用自动清理功能。

自动清理是PostgreSQL中的一项重要功能，它可以自动释放未使用的空间，并更新统计信息，以便查询优化器可以更好地选择查询计划。

8. wal_buffers: 这个参数用于控制WAL（Write-Ahead Logging）缓冲区的大小。