Skip to content

Latest commit

 

History

History
118 lines (92 loc) · 5.32 KB

README.md

File metadata and controls

118 lines (92 loc) · 5.32 KB
Raw

xl_global_views

Postgres-XL Global views extension

Extension xl_global_views is an extension to create global views from number of system tables aggregated from all nodes (both coordinators and datanodes). Each view has two additional columns prepend: node_name and node_type.

The extension uses execute direct construct, be sure you use the XL version with a execute direct cursor patch applied that addresses limitation of using cursors based on execute direct. Without this patch most of the views will not work and report SPI errors. Currently EXECUTE DIRECT requires superuser role, hence views defined as well.

INSTALLATION

Requirement: Postgres-XL. Extension is installed in the public schema and is not relocatable. In the download directory: make install then deploy all

then

CREATE EXTENSION xl_global_views ;

Then finally execute

select public.pgxl_create_views();

to finalize views creation.

EXECUTE DIRECT requires superuser role for now, so do the global views.

The views created:

  • pgxl_locks
  • pgxl_stat_all_tables
  • pgxl_stat_archiver
  • pgxl_stat_bgwriter
  • pgxl_stat_database
  • pgxl_stat_database_conflicts
  • pgxl_statio_all_indexes
  • pgxl_statio_all_sequences
  • pgxl_statio_all_tables
  • pgxl_statio_sys_indexes
  • pgxl_statio_sys_sequences
  • pgxl_statio_sys_tables
  • pgxl_statio_user_indexes
  • pgxl_statio_user_sequences
  • pgxl_statio_user_tables
  • pgxl_statistic
  • pgxl_stat_replication
  • pgxl_stat_activity
  • pgxl_stat_all_indexes
  • pgxl_stats
  • pgxl_stat_ssl
  • pgxl_stat_sys_indexes
  • pgxl_stat_sys_tables
  • pgxl_stat_user_functions
  • pgxl_stat_user_indexes
  • pgxl_stat_user_tables
  • pgxl_stat_xact_all_tables
  • pgxl_stat_xact_sys_tables
  • pgxl_stat_xact_user_functions
  • pgxl_stat_xact_user_tables

EXAMPLES

select * from pgxl_statio_all_tables where node_type = 'D' and relname = 'pg_class' limit 10;
node_name node_type relid schemaname relname heap_blks_read heap_blks_hit idx_blks_read idx_blks_hit toast_blks_read toast_blks_hit tidx_blks_read tidx_blks_hit
datanode1 D 1259 pg_catalog pg_class 3918 58604308 259 3035480 [null] [null] [null] [null]
datanode2 D 1259 pg_catalog pg_class 3745 2634617 613 141135 [null] [null] [null] [null]
datanode3 D 1259 pg_catalog pg_class 3742 965008 395 41040 [null] [null] [null] [null]
datanode4 D 1259 pg_catalog pg_class 3722 937601 212 37620 [null] [null] [null] [null]
datanode5 D 1259 pg_catalog pg_class 3918 58604308 259 3035480 [null] [null] [null] [null]
datanode6 D 1259 pg_catalog pg_class 3745 2634617 613 141135 [null] [null] [null] [null]
datanode7 D 1259 pg_catalog pg_class 3742 965008 395 41040 [null] [null] [null] [null]
datanode8 D 1259 pg_catalog pg_class 3722 937601 212 37620 [null] [null] [null] [null]
(8 rows)

On an idle cluster the number of sessions showed will be equal 1 per node.

select node_name, count(*) conns from pgxl_stat_activity group by 1 order by 1;
node_name count
coord1 1
coord2 2
coord3 1
coord4 1
datanode1 1
datanode2 1
datanode3 1
datanode4 1
datanode5 1
datanode6 1
datanode7 1
datanode8 1
(12 rows)

LICENSE AND COPYRIGHT

xl_global_views extension is released under the PostgreSQL License, a liberal Open Source license, similar to the BSD or MIT licenses.

Krzysztof Nienartowicz & Pavan Deolasee, 2017.

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE AUTHOR HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE AUTHOR HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.