GP - function to report on AO tables bloated tables which may require Vacuum

 To view  AO table bloat view click here

SCHEMANAME
TABLENAME

env PGOPTIONS="-c client_min_messages=WARNING" psql $PSQL_CONN_PARMS -c "select nspname as \"Schema\", relname as \"Table\", count(*) as \"# files\", sum(case when compaction_possible='t' then 1 else 0 end) as \"# files compactible\", max(percent_hidden) as \"Max % of Hidden Rows\" from (select n.nspname, c.relname, (gp_toolkit.__gp_aovisimap_compaction_info(c.oid)).* from pg_class c join pg_namespace n on n.oid = c.relnamespace where nspname like lower('$SCHEMANAME') and relname like lower('$TABLENAME') and array_to_string(c.reloptions,',') like '%appendonly=true%' ) a group by nspname, relname order by nspname, 5 desc"
Location: Bengaluru, Karnataka, India

Comments

Post a Comment

Popular posts from this blog

How to set Optimizer at database level in greenplum

How can I turn ON optimizer at Database level? You can set the optimier ON at database level using below command; ALTER DATABASE databasename SET OPTIMIZER = ON; Once this is done, kindly do reconnect to Database to see the change. How do I check if optimizer is set (ON/OFF) in a given database? You can use the show command as below to check psql -d databasename -c 'show optimizer'
Read more

Changing the gpmon Password

Greenplum Command Center manages the gpperfmon database using the gpmon database role. To change the gpmon password, follow these steps: 1. Log in to Greenplum Database as a superuser and change the gpmon password with the ALTER ROLE command: # ALTER ROLE gpmon WITH PASSWORD 'new_password'; 2. Update the password in the .pgpass file, which is used by Greenplum Command Center. The default location for this file is the gpadmin home directory (~/.pgpass). The .pgpass file contains a line with the gpmon password. Replace the existing password with the new password. *:5432:gpperfmon:gpmon:new_password The file should be owned by gpadmin and RW-accessible by gpadmin only. 3. Restart Greenplum Command Center with the gpcmdr utility. $ gpcmdr --restart
Read more

GP - Kerberos errors and resolutions

Issue:1. Ticket granting has expired with the error message, ERROR: psql: GSSAPI continuation error: Unspecified GSS failure. The Minor code may also display the error message, GSSAPI continuation error: Ticket expired. Solution: Create a new ticket on the Master server using the following command: kinit -k -t /home/gpadmin/gpdb-kerberos.keytab db_user Note: -t is the keytab file and db_user is the user. Issue:2. The library required to run the kinit command does not exist in the LD_LIBRARY_PATH  set in the Greenplum binaries, producing the following error message: ERROR: kinit: relocation error: kinit: symbol krb5_get_init_creds_opt_set_fast_ccache_name, version krb5_3_MIT not defined in file libkrb5.so.3 with link time reference Solution: Unset the LD_LIBRARY_PATH. IMPORTANT NOTE: The above fix will cause the following error when you try to run Postgres commands:  ERROR: psql: error while loading shared libraries: libpq.so.5: cannot open shared object ...
Read more