PPAS下安装 pg_stat_statements过程记录
安装 过程 记录 pg stat statements
2023-09-11 14:20:28 时间
磨砺技术珠矶,践行数据之道,追求卓越价值
回到上一级页面: PostgreSQL统计信息索引页 回到顶级页面:PostgreSQL索引页
PostgreSQL中,如何安装pg_stat_statements,网上有很多这方面的论述。这里说说如何在PPAS下安装它:
实验一:首先,看是否可以不安装,直接拿来用:
[root@rhjp001 ~]# su - enterprisedb -bash-3.2$ pwd /opt/PostgresPlus/9.2AS -bash-3.2$ ./bin/psql -d edb psql (9.2.1.3) "help" でヘルプを表示します. edb=# select count(*) from pg_stat_statements; ERROR: pg_stat_statements must be loaded via shared_preload_libraries edb=#
实验二:直接创建:
edb=# create extension pg_stat_statments; ERROR: 拡張機能の制御ファイル "/opt/PostgresPlus/9.2AS/share/extension/pg_stat_statments.control" をオープンできませんでした: そのようなファイルやディレクトリはありません edb=#
实验三:改配置文件后,再创建:
-bash-3.2$ pwd /opt/PostgresPlus/9.2AS/data -bash-3.2$ vim postgresql.conf -bash-3.2$ cat postgresql.conf | grep preload shared_preload_libraries = '$libdir/dbms_pipe,$libdir/edb_gen,pg_stat_statements' #local_preload_libraries = '' -bash-3.2$
重新启动后,创建,其实已经不用创建了。
[root@rhjp001 ~]# su - enterprisedb -bash-3.2$ pwd /opt/PostgresPlus/9.2AS -bash-3.2$ ./bin/psql -d edb psql (9.2.1.3) "help" でヘルプを表示します. edb=# create extension pg_stat_statements; ERROR: 拡張機能 "pg_stat_statements" はすでに存在します edb=#
[root@rhjp001 ~]# su - enterprisedb -bash-3.2$ ./bin/psql -d edb psql (9.2.1.3) "help" でヘルプを表示します. edb=# select count(*) from pg_stat_statements; count ------- 47 (1 行) edb=#
也就是说,其实在PPAS安装好之后,如果想要使用 pg_stat_statements ,只要配置postgresql.conf的 shared_preload_libraries 就可以了。
卸载ppas,重新安装,然后再重新开始吧:
配置postgresql.conf:
把 shared_preload_libraries = '$libdir/dbms_pipe,$libdir/edb_gen'
改成:
shared_preload_libraries = '$libdir/dbms_pipe,$libdir/edb_gen,pg_stat_statements'
重新启动数据库: service ppas-9.2 restart
-bash-3.2$ ./bin/psql -d edb psql (9.2.1.3) "help" でヘルプを表示します. edb=# select count(*) from pg_stat_statements; count ------- 42 (1 行) edb=#
这个pg_stat_statements,主要可以用于区分运行最慢的sql文:
例如:
edb=# \d pg_stat_statements; ビュー "enterprisedb.pg_stat_statements" 列 | 型 | 修飾語 ---------------------+------------------+-------- userid | oid | dbid | oid | query | text | calls | bigint | total_time | double precision | rows | bigint | shared_blks_hit | bigint | shared_blks_read | bigint | shared_blks_dirtied | bigint | shared_blks_written | bigint | local_blks_hit | bigint | local_blks_read | bigint | local_blks_dirtied | bigint | local_blks_written | bigint | temp_blks_read | bigint | temp_blks_written | bigint | blk_read_time | double precision | blk_write_time | double precision | edb=#
查找最慢的10条sql文(这里用的是累计时间)
edb=# select * from pg_stat_statements order by total_time desc limit 10; -[ RECORD 1 ]-------+------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- userid | 10 dbid | 14000 query | drop extension pg_stat_statements; calls | 2 total_time | 3099.557 rows | 0 shared_blks_hit | 673 shared_blks_read | 27 shared_blks_dirtied | 9 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 temp_blks_read | 0 temp_blks_written | 0 blk_read_time | 0 blk_write_time | 0 -[ RECORD 2 ]-------+------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- userid | 10 dbid | 14000 query | create database gaodb owner gao; calls | 1 total_time | 2068.82 rows | 0 shared_blks_hit | 54 shared_blks_read | 4 shared_blks_dirtied | 7 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 temp_blks_read | 0 temp_blks_written | 0 blk_read_time | 0 blk_write_time | 0 -[ RECORD 3 ]-------+------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- userid | 10 dbid | 14000 query | CREATE TEMP TABLE pga_tmp_zombies(jagpid int4) calls | 3 total_time | 1061.702 rows | 0 shared_blks_hit | 479 shared_blks_read | 109 shared_blks_dirtied | 37 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 temp_blks_read | 0 temp_blks_written | 0 blk_read_time | 0 blk_write_time | 0 -[ RECORD 4 ]-------+------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- userid | 10 dbid | 14000 query | SELECT count(*) As count, pg_backend_pid() AS pid FROM pg_class cl JOIN pg_na mespace ns ON ns.oid=relnamespace WHERE relname=? AND nspname=? calls | 3 total_time | 929.614 rows | 3 shared_blks_hit | 6 shared_blks_read | 15 shared_blks_dirtied | 0 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 temp_blks_read | 0 temp_blks_written | 0 blk_read_time | 0 blk_write_time | 0 -[ RECORD 5 ]-------+------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- userid | 10 dbid | 14000 query | SELECT COUNT(*) FROM pg_proc WHERE proname = ? AND pronamespace = ( SELECT oid FROM pg_namespace WHERE nspname = ?) AND prorettype = (SELECT oid ROM pg_type WHERE typnam e = ?) AND proargtypes = ? calls | 3 total_time | 337.312 rows | 3 shared_blks_hit | 15 shared_blks_read | 15 shared_blks_dirtied | 0 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 temp_blks_read | 0 temp_blks_written | 0 blk_read_time | 0 blk_write_time | 0 -[ RECORD 6 ]-------+------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- userid | 10 dbid | 14000 query | SELECT pgagent.pgagent_schema_version() calls | 3 total_time | 321.264 rows | 3 shared_blks_hit | 12 shared_blks_read | 12 shared_blks_dirtied | 0 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 temp_blks_read | 0 temp_blks_written | 0 blk_read_time | 0 blk_write_time | 0 -[ RECORD 7 ]-------+------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- userid | 10 dbid | 14000 query | DROP TABLE pga_tmp_zombies calls | 3 total_time | 282.334 rows | 0 shared_blks_hit | 360 shared_blks_read | 21 shared_blks_dirtied | 0 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 temp_blks_read | 0 temp_blks_written | 0 blk_read_time | 0 blk_write_time | 0 -[ RECORD 8 ]-------+------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- userid | 10 dbid | 14000 query | create extension pg_stat_statements; calls | 1 total_time | 155.641 rows | 0 shared_blks_hit | 585 shared_blks_read | 22 shared_blks_dirtied | 25 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 temp_blks_read | 0 temp_blks_written | 0 blk_read_time | 0 blk_write_time | 0 -[ RECORD 9 ]-------+------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- userid | 16684 dbid | 16685 query | create table gaotab(id integer); calls | 1 total_time | 143.838 rows | 0 shared_blks_hit | 195 shared_blks_read | 59 shared_blks_dirtied | 21 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 temp_blks_read | 0 temp_blks_written | 0 blk_read_time | 0 blk_write_time | 0 -[ RECORD 10 ]------+------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- userid | 16684 dbid | 16685 query | select pg_available_extensions(); calls | 2 total_time | 92.16 rows | 100 shared_blks_hit | 5 shared_blks_read | 1 shared_blks_dirtied | 0 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 temp_blks_read | 0 temp_blks_written | 0 blk_read_time | 0 blk_write_time | 0 edb=#
需要注意,只有管理员用户才可以看到这个视图。
回到上一级页面: PostgreSQL统计信息索引页 回到顶级页面:PostgreSQL索引页
磨砺技术珠矶,践行数据之道,追求卓越价值
相关文章
- 【转】linux下ipython的安装过程
- Ubuntu 安装配置MySQL,并使用VS的Server Explorer UI界面远程管理MySQL
- ElasticSearch-5.0安装head插件
- Ubuntu 10.4 下安装gSOAP安装过程和问题解决
- CentOS 7.4安装nodejs & nginx & pm2
- CentOS6.6下安装Redmine2.6(整合Apache2.4)
- 新版SourceTree免帐号登录安装
- macos安装android studio(Android Studio 2021.1.1)
- ubuntu21.04(linux):用apt安装nginx/php/mysql/phpmyadmin(开发环境)
- Linux配置keepalived实现nginx高可用安装过程记录
- 如何手动下载并安装 Visual Studio Code 的 SAP Fiori tools - Extension Pack 扩展
- Android 11.0 PackageManagerService APK的安装过程
- WINDOWS SERVER 2003 R2 AD域控安装过程实践
- VMware虚拟机安装Ubuntu16-18系统超详细过程(含下载地址)
- Python2.7安装教程
- Linux下安装Oracle的过程和涉及的知识点-系列6
- Centos 5.5 安装Mysql5.5过程
- ambari安装过程中的一些误区(跳坑指南)(ambari的两个bug修复)
- DISPLAY not set. Please set the DISPLAY and try again 的处理方法(Oracle安装比较常出现)
- 成功解决解决VM软件安装Linux的Ubuntu过程,打开时 Operating System not found
- 【过程记录】ssh配置免密登录/anaconda环境迁移/gcc_g++安装切换
- QEMU安装Ubuntu 20.04的完整过程
- QEMU安装Windows 11的完整过程
- 操作系统实战-almalinux的安装过程