Wiki
Clone wikiv_pnfs / Home
Introduction
This dCache work consists of:
- pnfs_dir('dir'), a stored procedure returning the Chimera namespace starting from an arbitrary 'dir' ( in the dir name please remove the initial / e.g. 'pnfs' and not '/pnfs')
- v_pnfs, the materialized view of pnfs_dir('pnfs') ; you can use it to run selects like select * from v_pnfs where path like '/pnfs/psi.ch/cms/trivcat/user/bstomumu%';
- pnfs_dirs('/dir'), a Python stored procedure returning all the Chimera dirs together with their sizes, starting from an arbitrary '/dir'
- v_pnfs_top_dirs, the materialized view of pnfs_dirs('/pnfs'), already ordered by size; LIVE DEMO
- dc_find, an optional Python 2.6 tool to easily search in v_pnfs by the Linux command line ; it's a user tool, a dCache admin will use SQL instead
Installing the SQL code in your Chimera DB is very simple ; in my case I assign the SQL code to the DB user 'nagios' ; you can simply erase that instruction or safely ignore the related Postgresql errors :
#!bash psql -U postgres -d chimera -e -f ./pnfs_dir.sql psql -U postgres -d chimera -e -f ./v_pnfs.sql yum install python-ordereddict yum install postgresql93-plpython # or yum install postgresql94-plpython psql -U postgres -d chimera -e -f ./pnfs_dirs.sql psql -U postgres -d chimera -e -f ./v_pnfs_top_dirs.sql
Requirements:
- dCache [ 2.2 - 2.6 - 2.10 - 2.13 ] Chimera
- PostgreSQL >= 8.4, because of the WITH Queries
- PostgreSQL >= 9.3 Materialized Views ; the usage of the Materialized Views is highly recommended but not strictly required ; they will significantly speed up the queries execution; if you're still running a PG version < 9.3 then simply erase the 'MATERIALIZED' infix from the SQL code, but it would be simpler and better to upgrade to PG 9.3.
- To run dc_find:
- Python 2.6 module psycopg2
- Python 2.6 module PrettyTable
Nice article to understand the with recursive queries executed on a tree modelled in SQL, like the Chimera tree.
** v_pnfs ****
#!sql psql (9.4.1) Type "help" for help. chimera=# \dS+ v_pnfs Materialized view "public.v_pnfs" Column | Type | Modifiers | Storage | Stats target | Description ---------+--------------------------+-----------+----------+--------------+------------- depth | integer | | plain | | itype | integer | | plain | | iio | integer | | plain | | ipnfsid | character varying | | extended | | path | text | | extended | | iname | character varying | | extended | | isize | bigint | | plain | | iuid | integer | | plain | | igid | integer | | plain | | ictime | timestamp with time zone | | plain | | iatime | timestamp with time zone | | plain | | imtime | timestamp with time zone | | plain | | icrtime | timestamp with time zone | | plain | | imode | integer | | plain | | adler32 | character varying(128) | | extended | | pools | text | | extended | | View definition: WITH partial_v_pnfs AS ( SELECT pnfs_dir.depth, pnfs_dir.itype, pnfs_dir.iio, pnfs_dir.ipnfsid, pnfs_dir.path, pnfs_dir.iname, pnfs_dir.isize, pnfs_dir.iuid, pnfs_dir.igid, pnfs_dir.ictime, pnfs_dir.iatime, pnfs_dir.imtime, pnfs_dir.icrtime, t_level_2.imode FROM pnfs_dir('pnfs'::character varying) pnfs_dir(depth, itype, iio, ipnfsid, path, iname, isize, iuid, igid, ictime, iatime, imtime, icrtime) LEFT JOIN t_level_2 ON pnfs_dir.ipnfsid::text = t_level_2.ipnfsid::text ), t_locationinfo_aggregated AS ( SELECT t_locationinfo.ipnfsid, string_agg(t_locationinfo.ilocation::text, ' '::text) AS ilocation_aggregated FROM t_locationinfo GROUP BY t_locationinfo.ipnfsid ), partial_v_pnfs2 AS ( SELECT partial_v_pnfs.depth, partial_v_pnfs.itype, partial_v_pnfs.iio, partial_v_pnfs.ipnfsid, partial_v_pnfs.path, partial_v_pnfs.iname, partial_v_pnfs.isize, partial_v_pnfs.iuid, partial_v_pnfs.igid, partial_v_pnfs.ictime, partial_v_pnfs.iatime, partial_v_pnfs.imtime, partial_v_pnfs.icrtime, partial_v_pnfs.imode, t_locationinfo_aggregated.ilocation_aggregated FROM partial_v_pnfs LEFT JOIN t_locationinfo_aggregated ON partial_v_pnfs.ipnfsid::text = t_locationinfo_aggregated.ipnfsid::text ORDER BY partial_v_pnfs.path DESC ) SELECT partial_v_pnfs2.depth, partial_v_pnfs2.itype, partial_v_pnfs2.iio, partial_v_pnfs2.ipnfsid, partial_v_pnfs2.path, partial_v_pnfs2.iname, partial_v_pnfs2.isize, partial_v_pnfs2.iuid, partial_v_pnfs2.igid, partial_v_pnfs2.ictime, partial_v_pnfs2.iatime, partial_v_pnfs2.imtime, partial_v_pnfs2.icrtime, partial_v_pnfs2.imode, t_inodes_checksum.isum AS adler32, partial_v_pnfs2.ilocation_aggregated AS pools FROM partial_v_pnfs2 LEFT JOIN t_inodes_checksum ON t_inodes_checksum.ipnfsid::text = partial_v_pnfs2.ipnfsid::text;
** v_pnfs_top_dirs ****
#!sql chimera=> \dS+ v_pnfs_top_dirs Materialized view "public.v_pnfs_top_dirs" Column | Type | Modifiers | Storage | Stats target | Description --------+-----------------------------+-----------+----------+--------------+------------- totmb | integer | | plain | | depth | integer | | plain | | iuid | integer | | plain | | igid | integer | | plain | | ictime | timestamp without time zone | | plain | | iatime | timestamp without time zone | | plain | | path | character varying | | extended | | View definition: SELECT pnfs_dirs.totmb, pnfs_dirs.depth, pnfs_dirs.iuid, pnfs_dirs.igid, pnfs_dirs.ictime, pnfs_dirs.iatime, pnfs_dirs.path FROM pnfs_dirs('/pnfs'::character varying) pnfs_dirs(totmb, depth, ipnfsid, path, iuid, igid, ictime, iatime, imtime) ORDER BY pnfs_dirs.totmb DESC, pnfs_dirs.path;
Updated