PostgreSQL查找慢查询

统计CPU

ps -u postgres -o %cpu | awk '{s+=$1} END {print "Postgres Total CPU: " s "%"}'

查看活跃查询

SELECT pid, now() - query_start AS duration, query, state FROM pg_stat_activity WHERE state = 'active' ORDER BY duration DESC;
SELECT pid, now() - query_start AS duration, query, state FROM pg_stat_activity WHERE state = 'active' ORDER BY duration DESC LIMIT 5;

查看连接IP和查询语句

SELECT pid, now() - query_start AS 执行时间, usename AS 用户名, 
client_addr AS 连接IP, state AS 状态, query AS 查询语句         
FROM pg_stat_activity WHERE
 pid <> pg_backend_pid() ORDER BY 执行时间 DESC;

查看扫表

SELECT 
    relname AS 表名,
    seq_scan AS 全表扫描次数,
    seq_tup_read AS 全表扫描读取行数,
    idx_scan AS 索引扫描次数,
    idx_tup_fetch AS 索引提取行数
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 10;

启用插件

CREATE EXTENSION IF NOT EXISTS pg_stat_statements; 

 重置自动增续ID

SELECT setval('tag_id_seq', (SELECT MAX(id) FROM tag));

nginx隐藏真实文件路径

给浏览器浏览器返回头

$file=decode($url);
header("X-Accel-Redirect: /privatefiles/$file");

nginx配置

    location /privatefiles {
        internal;  #内部访问
        alias /var/www/html/xaccel/restrictedfiles;
    }   

将文件名url加密， 然后接收解密，通过X-Accel-Redirect再转给nginx返回数据。

为什么要这么做？  一些文件路径在某些原因下不想暴露真实路径，又不通过程序再去读一遍问题直接转交个nginx响应。

转载自：https://dev.to/gbhorwood/nginx-serving-private-files-with-x-accel-redirect-57dl