postgreSql的用法问题,连接数满了,这要怎么配置啊?

wybwsk

问题描述

原来是用mysql,想换postgreSql,边学边用。
我按文档配置好postgreSql,

'pgsql' => [
         'driver'   => 'pgsql',
         'host'     => '127.0.0.1',
         'port'     => 5432,
         'database' => 'webman',
         'username' => 'webman',
         'password' => '',
         'charset'  => 'utf8',
         'prefix'   => '',
         'schema'   => 'public',
         'sslmode'  => 'prefer',
         'pool' => [ // 连接池配置,仅支持swoole/swow驱动
            'max_connections' => 200, // 最大连接数
            'min_connections' => 1, // 最小连接数
            'wait_timeout' => 3,    // 从连接池获取连接等待的最大时间,超时后会抛出异常
            'idle_timeout' => 60,   // 连接池中连接最大空闲时间,超时后会关闭回收,直到连接数为min_connections
            'heartbeat_interval' => 50, // 连接池心跳检测时间,单位秒,建议小于60秒
        ],
     ]

然后启动服务器。
webman的配置是
'count' => cpu_count() * 4,

然后写代码,查询数据库。
接着,开navicat,连接数据库,查询,运行

SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    state,
    now() - query_start as duration,
    query
FROM pg_stat_activity 
ORDER BY duration DESC

SELECT count(*) FROM pg_stat_activity;

连接数是94.

pid application_name    state   duration    query
1791211 Navicat idle    00:13:28.257054 SELECT c.conkey FROM pg_constraint c WHERE c.contype = 'p' and c.conrelid = 12226
1791132 Navicat idle    00:10:52.445714 SELECT count(*) FROM pg_stat_activity
1791266     idle    00:00:46.581399 select 1
1791179     idle    00:00:43.263744 DEALLOCATE pdo_stmt_0000001a
1791083     idle    00:00:11.596558 select 1
1791085     idle    00:00:11.595856 select 1
1791082     idle    00:00:11.595803 select 1
1791084     idle    00:00:11.595655 select 1
1791086     idle    00:00:11.595306 select 1
1791087     idle    00:00:11.591624 select 1
1791088     idle    00:00:11.59151  select 1
1791092     idle    00:00:11.591247 select 1
1791093     idle    00:00:11.590441 select 1
1791089     idle    00:00:11.590261 select 1
1791095     idle    00:00:11.58956  select 1
1791090     idle    00:00:11.589042 select 1
1791096     idle    00:00:11.588876 select 1
1791091     idle    00:00:11.587806 select 1
1791094     idle    00:00:11.58746  select 1
1791097     idle    00:00:11.586942 select 1
1791098     idle    00:00:11.559248 select 1
1791099     idle    00:00:11.526996 select 1
1791112     idle    00:00:11.281504 select 1
1791113     idle    00:00:11.274128 select 1
1791100     idle    00:00:10.595131 select 1
1791104     idle    00:00:10.594903 select 1
1791103     idle    00:00:10.59485  select 1
1791101     idle    00:00:10.594813 select 1
1791105     idle    00:00:10.590142 select 1
1791108     idle    00:00:10.590115 select 1
1791106     idle    00:00:10.590104 select 1
1791109     idle    00:00:10.589318 select 1
1791110     idle    00:00:10.588197 select 1
1791102     idle    00:00:10.567088 select 1
1791107     idle    00:00:10.561812 select 1
1791111     idle    00:00:10.364475 select 1
1791117     idle    00:00:10.282194 select 1
1791119     idle    00:00:10.281916 select 1
1791118     idle    00:00:10.280612 select 1
1791120     idle    00:00:10.280394 select 1
1791121     idle    00:00:10.278003 select 1
1791122     idle    00:00:10.276242 select 1
1791123     idle    00:00:10.275965 select 1
1791125     idle    00:00:10.273959 select 1
1791124     idle    00:00:10.273256 select 1
1791126     idle    00:00:10.241968 select 1
1791127     idle    00:00:10.157744 select 1
1791128     idle    00:00:10.154399 select 1
1791129     idle    00:00:10.151239 select 1
1791133     idle    00:00:09.245063 select 1
1791134     idle    00:00:09.15609  select 1
1791136     idle    00:00:09.154931 select 1
1791137     idle    00:00:09.151459 select 1
1791138     idle    00:00:09.150483 select 1
1791139     idle    00:00:09.150307 select 1
1791140     idle    00:00:09.147949 select 1
1791135     idle    00:00:09.12682  select 1
1791141     idle    00:00:09.070697 select 1
1791142     idle    00:00:09.046019 select 1
1791150     idle    00:00:09.045597 select 1
1791145     idle    00:00:09.045096 select 1
1791146     idle    00:00:09.045064 select 1
1791143     idle    00:00:09.044662 select 1
1791144     idle    00:00:09.044553 select 1
1791147     idle    00:00:09.041565 select 1
1791148     idle    00:00:09.041095 select 1
1791151     idle    00:00:09.040947 select 1
1791152     idle    00:00:09.039819 select 1
1791149     idle    00:00:09.037323 select 1
1791153     idle    00:00:08.917683 select 1
1791154     idle    00:00:08.902584 select 1
1791157     idle    00:00:08.901689 select 1
1791155     idle    00:00:08.901231 select 1
1791158     idle    00:00:08.900945 select 1
1791161     idle    00:00:08.897259 select 1
1791159     idle    00:00:08.896814 select 1
1791160     idle    00:00:08.895491 select 1
1791162     idle    00:00:08.89238  select 1
1791130     idle    00:00:08.841519 select 1
1791131     idle    00:00:08.761094 select 1
1791156     idle    00:00:08.564987 select 1
1791167     idle    00:00:08.157842 select 1
1791163     idle    00:00:07.759545 select 1
1791164     idle    00:00:07.523503 select 1
1791171     idle    00:00:05.708753 select 1
1791081     idle    00:00:01.217967 select 1

有大量的心跳连接。这是正常的吗?

因为原来是用的mysql,用配置文件,装好后直接定逻辑,不用管这些。
然后我换成PQ后,跑了半天写业务逻辑,然后webman重启和navicat连接都出现了连接数满,拒绝服务的问题。才来查这个。

我就是本地开发,为啥会出现连接数的问题啊?200个都满了。
我把pool配置删除了也一样。也会出现过一段时间连接数满的问题。

有没有大佬提供一下解决问题的思路。

132 1 0
1个回答

luoyue

最大连接数计算公式:max_connections * 进程数。连接池在每个进程中都是独立的。

比如4核服务器,pgsql的最大连接数是200x4x4=3200,这超出了pgsl的默认200的连接数,可以适当调大pgsql的参数

  • wybwsk 7天前

    所以这是正常的吗?我在本地调API接口,调1-3次,这个请求数就会加1,他结束的都不自动关闭的?

  • luoyue 7天前

    连接池会进行长连接,是正常现象,超过idle_timeout时间后会自动断开

  • wybwsk 7天前

    谢谢,懂了,所以默认的pool配置要保留,然后需要安装swoole或者swow,默认没装这个,这驱动只作用数据库吧,其他的业务都是传统的fpm的那种写法。

🔝