Tuesday 14 July 2015

How to monitor Netezza performance?

 How to monitor Netezza performance?

Performance of Netezza depends on various factors such as distribution keys on the table, query performance, hardware factors such as number of spus,  data skew i.e. how the data is distributed across the spus, etc

There are different ways to check how your netezza box is performing. First you can use the netezza administration tool to see the number of queries running , queries getting queued, the performance of individual queries. If there are too many queries running then obviously it is going to slow down your server performance. If there are any bad queries pulling lot of data then obviously it will one of the culprit. Put a threshold time out for queries that users are running so that it does not run for ever.You can also check out the netezza performance portal. Provides some extra information on performance.

Data skew on netezza refers to how well the data is distributed on different spus of the netezza server. If some of the spus have more data then the others then it is going to impact the performance. Make sure the data is properly distributed by using appropriate distribution keys.

You can also use certain queries to see the performance of the netezza machine. Some of them are listed below.

To check all the queries running on the system use the below query

select * from SYSTEM..NZ_QUERY_HISTORY

To check the list of successful and all historical queries also try out the below queries:

SELECT * FROM  HIST_admin."$v_hist_successful_queries"

SELECT * FROM  HIST_admin."$v_hist_queries"

select * from   HISTDB.."$v_hist_table_access_stats"

select * from _v_qryhist

select * from _v_qrystat;




We can check distribution of data using performance portal or the netezza administration console, or the explain plan, or nz_skew dba script.