微信群：数据库Hacker, 已超200人，现在无法通过扫描直接加入。需要入群的朋友，请直接微信联系我(个人微信：_iihero)，标上您的全名_数据库Hacker作为备注即可。
前言
关于 work_mem: 可以看看：https://postgresqlco.nf/doc/zh/param/work_mem/
设置在写入临时磁盘文件之前查询操作(例如排序或哈希表)可使用的最大内存容量。 如果指定值时没有单位，则以千字节为单位。默认值是 4 兆字节 (
4MB)。 注意对于一个复杂查询， 可能会并行运行好几个排序或者哈希操作；每个操作都会被允许使用这个参数指定的内存量，然后才会开始写数据到临时文件。同样，几个正在运行的会话可能并发进行这样的操作。因此被使用的总内存可能是work_mem值的好几倍，在选择这个值时一定要记住这一点。ORDER BY、DISTINCT和归并连接都要用到排序操作。哈希连接、基于哈希的聚集以及基于哈希的IN子查询处理中都要用到哈希表。
对开发人员，大都基于成熟的 framework 或者库，编写自己的业务逻辑。不希望在代码当中加入零零碎碎的东西。对于零碎的改动，大多是比较排斥的。
作为 DBA 或者数据库架构师，你要想说服开发人员加入某方面的改动，就得让他意识到，不改带来的坏处。同时，如果能提供如何修改的好的建议，将更有助于两者之间的良好协作。
分析
就拿普通的 spring-boot JPA 而言，你定义简单的访问数据库的调用，都不会细到 work_mem 一级。如下边的代码：
mport javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;@Entity
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String firstName;
private String lastName; // Getters and setters
}
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import java.util.stream.Stream;public interface UserRepository extends JpaRepository<User, Long> {
@Query("SELECT u FROM User u")
Stream<User> findAllByCustomQueryAndStream();
}
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.transaction.Transactional;
import java.util.function.Consumer;
import java.util.stream.Stream;@Service
public class UserService {
@Autowired
private UserRepository userRepository;
@Transactional
public void processLargeResultSet(Consumer<User> consumer) {
try (Stream<User> stream = userRepository.findAllByCustomQueryAndStream()) {
stream.forEach(consumer);
}
}
}
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;@RestController
@RequestMapping("/users")
public class UserController {
@Autowired
private UserService userService;
@GetMapping("/process")
public String processUsers() {
userService.processLargeResultSet(user -> {
// Process each user
System.out.println("Processing user: " + user.getFirstName() + " " + user.getLastName());
});
return "Processing started";
}
}
上边的代码都是数据库无关的。一个简单的代码片段就会自动为你生成相关的数据库查询：
@Query("SELECT u FROM User u")
Stream<User> findAllByCustomQueryAndStream();
简单示例
可是我要调整 work_mem，怎么办？并且，你所在的 DB service 不让你直接修改配置。你受到很多限制，怎么办？
看看这个实例， 默认的 work_mem 大小为 4MB.
show work_mem;
work_mem|
--------+
4MB |
-- 执行某样表中的一个普通查询
explain analyze SELECT d.sdkType, d.userAgent, COUNT(DISTINCT lower(d.userName)) FROM sto_device d WHERE d.tenant_Name = :instanceId
AND d.lastConnected > :lastAccessTime AND d.sdkType is not null GROUP BY d.sdkType, d.userAgent;
QUERY PLAN |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
GroupAggregate (cost=334033.18..348345.56 rows=33909 width=47) (actual time=477.414..647.530 rows=5 loops=1) |
Group Key: sdktype, useragent |
-> Gather Merge (cost=334033.18..346901.57 rows=110490 width=55) (actual time=319.189..349.809 rows=110323 loops=1) |
Workers Planned: 2 |
Workers Launched: 2 |
-> Sort (cost=333033.16..333148.26 rows=46038 width=55) (actual time=312.408..317.384 rows=36774 loops=3) |
Sort Key: sdktype, useragent |
Sort Method: external merge Disk: 2176kB |
Worker 0: Sort Method: external merge Disk: 2616kB |
Worker 1: Sort Method: external merge Disk: 2168kB |
-> Parallel Seq Scan on sto_device d (cost=0.00..329467.39 rows=46038 width=55) (actual time=0.015..291.179 rows=36774 loops=3) |
Filter: ((sdktype IS NOT NULL) AND (lastconnected > '2020-10-17 00:00:00'::timestamp without time zone) AND ((tenant_name)::text = '06ab413a-5b02-4682-a221-3e14947f45b5'::text))|
Rows Removed by Filter: 334841 |
Planning Time: 0.134 ms |
Execution Time: 647.943 ms |
这个时候，我们能看到使用到了 external merge Disk。 不能得到足够的内存。如果我们将 work_mem 设置到 8MB.
set work_mem='8MB';
--得到的查询计划如下：
QUERY PLAN |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
GroupAggregate (cost=334033.18..348345.56 rows=33909 width=47) (actual time=468.481..611.926 rows=5 loops=1) |
Group Key: sdktype, useragent |
-> Gather Merge (cost=334033.18..346901.57 rows=110490 width=55) (actual time=321.805..348.069 rows=110323 loops=1) |
Workers Planned: 2 |
Workers Launched: 2 |
-> Sort (cost=333033.16..333148.26 rows=46038 width=55) (actual time=317.768..320.248 rows=36774 loops=3) |
Sort Key: sdktype, useragent |
Sort Method: quicksort Memory: 4854kB |
Worker 0: Sort Method: quicksort Memory: 4943kB |
Worker 1: Sort Method: quicksort Memory: 5346kB |
-> Parallel Seq Scan on sto_device d (cost=0.00..329467.39 rows=46038 width=55) (actual time=0.015..300.629 rows=36774 loops=3) |
Filter: ((sdktype IS NOT NULL) AND (lastconnected > '2020-10-17 00:00:00'::timestamp without time zone) AND ((tenant_name)::text = '06ab413a-5b02-4682-a221-3e14947f45b5'::text))|
Rows Removed by Filter: 334841 |
Planning Time: 0.127 ms |
Execution Time: 501.970 ms |
这里用到的是 quicksort Memory。充分的利用了内存，而不是磁盘文件。
上边的 set work_mem 都是基于会话连接一级的。在操作完成之后，可以使用reset work_mem，将 work_mem 恢复到默认值。
怎么使用?
连接（会话）一级:
get connection
set work_mem='8MB';
call SQL;
reset work_mem;
这里头，call SQL，开发人员是可以直接调用 JPA 的。set 和 reset 这种操作，他们估计也是会排斥的。
事务一级：
begin tx;
set local work_mem='8MB';
call SQL;
commit tx;
事务一级的控制，就显得比较细致。大部分场合，在连接一级控制就好。
可以采用下边的伪逻辑, 在具体的实现代码里头：
set work_mem = ...;
begin tx;
call SQL; // JPA call
commit tx;
finally {
reset work_mem;
}
但即便是这样，开发人员也会觉得不太舒服。毕竟编写出来的代码跟别的数据库又不太一样。
既然是 connection 一级的，我们可以转换一下思路，将它放到初始化的时候。
大多数连接池都提供相关的配置项：
HikariConfig config = new HikariConfig();
config.setPoolName("HikariPool-" + id);
config.setConnectionTimeout(dsProps.getConnectionTimeout());
config.setMaximumPoolSize(dsProps.getMaxPoolSize());
config.setMinimumIdle(dsProps.getMinPoolSize());
config.setConnectionTestQuery("SELECT 1");
config.setJdbcUrl(connUrl);
config.setUsername(username);
config.setPassword(password);
config.setConnectionInitSql("set work_mem='8MB'");
.......
HikariDataSource dataSource = new HikariDataSource(config);
上边的代码即可将 work_mem 定制值放到每一个 connection 当中。
实际上，如果查看 Hikari 的实现，你会发现：
executeSql(connection, config.getConnectionInitSql(), true);
private void setupConnection(final Connection connection) throws ConnectionSetupException
{
try {
if (networkTimeout == UNINITIALIZED) {
networkTimeout = getAndSetNetworkTimeout(connection, validationTimeout);
}
else {
setNetworkTimeout(connection, validationTimeout);
}
if (connection.isReadOnly() != isReadOnly) {
connection.setReadOnly(isReadOnly);
}
if (connection.getAutoCommit() != isAutoCommit) {
connection.setAutoCommit(isAutoCommit);
}
checkDriverSupport(connection);
if (transactionIsolation != defaultTransactionIsolation) {
connection.setTransactionIsolation(transactionIsolation);
}
if (catalog != null) {
connection.setCatalog(catalog);
}
if (schema != null) {
connection.setSchema(schema);
}
executeSql(connection, config.getConnectionInitSql(), true);
setNetworkTimeout(connection, networkTimeout);
}
catch (SQLException e) {
throw new ConnectionSetupException(e);
}
}
它是在 connection 的初始化的时候，强制执行这条 SQL 语句的。因而还算是有效的。
JDBC 中如何搞？
参照上边的 Hikari config, 你可以在获取 connection 以后，立即执行：set work_mem。
注意：如果使用 options 配置，work_mem 后边的单引号 quota 需要去掉。
Properties props = new Properties();
props.setProperty("user", "mydb");
props.setProperty("password", "test123");
props.setProperty("options", "-c search_path=public,pg_catalog -c statement_timeout=90000 -c work_mem='10MB'");
Connection conn = DriverManager.getConnection("jdbc:postgresql://10.47.43.138:5555/mydb?ApplicationName=MyApp",
props);
其中:
-c work_mem='10MB'"
在这里会出错。
但是改成：
props.setProperty("options", "-c search_path=public,pg_catalog -c statement_timeout=90000 -c work_mem=10MB");
这样反而能工作了。有点奇葩。能工作就好。尽情享用吧。
个人微信：_iihero
CSDN: iihero
墨天轮： https://www.modb.pro/u/16258 (Sean)
pgfans: iihero
往期导读:
1. PostgreSQL中配置单双向SSL连接详解
2. 提升PSQL使用技巧：PostgreSQL中PSQL使用技巧汇集(1)
3. 提升PSQL使用技巧：PostgreSQL中PSQL使用技巧汇集(2)
4. PostgreSQL SQL的基础使用及技巧
5. PostgreSQL开发技术基础:过程与函数
6. PostgreSQL中vacuum 物理文件truncate发生的条件
7. PostgreSQL中表的年龄与Vacuum的实验探索：Vacuum有大用
8. PostgreSQL利用分区表来弥补AutoVacuum的不足
9. 也聊聊PostgreSQL中的空间膨胀与AutoVacuum
10. 正确理解SAP BTP中hyperscaler PG中的IOPS (AWS篇)
