Postgresql
活動連接數和剩餘連接數
我想獲得有關一段時間內連接峰值數量的統計資訊。
我知道
pg_stat_activity
觀點,喜歡select count(*) from pg_stat_activity
,但我認為這種方法不是很聰明。是否有其他視圖或表格可以提供我需要的資訊?
此 SQL 將幫助您
select max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal from (select count(*) used from pg_stat_activity) t1, (select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) t2, (select setting::int max_conn from pg_settings where name=$$max_connections$$) t3
結果:
max_conn | used | res_for_super | res_for_normal ---------+------+---------------+---------------- 100 | 2 | 3 | 95 (1 row)
你可以把它放在shell中:
#!/bin/bash for (( c=1; c<=3600; c++ )) do gsql -U pgdba -W pgdba -p 6432 -c "sql" >> /home/pgdba/res_data.log sleep 1 # once per second done
或者您可以將結果記錄到表中,然後執行
postgres=# copy restbl to '/home/pgdba/res.csv' csv header;
獲取結果 csv 文件。