Monthly Archives

2 Articles

Testing out UAC

Posted by skahler on

I haven’t been a big fan of Greenplum’s performance monitoring tools. It’s been a couple years though so it’s time to try them again and see what we’ve got. So just got UAC up and running today and it’s looking fairly nice.

Finding trouble spots

Posted by skahler on

I’ve been fighting some database performance issues recently and started to use the following query to look for tables that are being used that are showing that they have 0 rows. These are likely to be unanalyzed tables being used in queries. We have queries to look through the entire database for potential unanalyzed tables, but it takes much less resources just to look at what’s currently in flight and try to address what we are currently hammering on. There are a lot of other table in there that I don’t need to join on for the visible data set, but I’ve got them there in case I need to start ungrouping things and pulling in more specific data.

SELECT n.nspname AS "schema_name",                                       
       c.relname AS "object_name",                                       
       c.reltuples,                                                      
       c.relpages                                                 
FROM pg_locks l,                                                       
     pg_class c,                                                       
     pg_database d,                                                    
     pg_stat_activity s,                                               
     pg_namespace n                                               
WHERE l.relation = c.oid                                          
AND l.database = d.oid                                            
AND l.pid = s.procpid                                             
AND c.relnamespace = n.oid                                        
AND c.relkind = 'r'                                               
AND n.nspname !~ '^pg_'::text                                     
AND c.reltuples = 0                                               
GROUP BY 1, 2, 3, 4                                               
ORDER BY 1, 2;