Using Netezza ? If answer is yes, you have done the first step in choosing a powerful system for your analytics requirements. Speed, durability, resilience, time to value and most importantly “Simplicity” is built into it.
When you have such an appliance, you would want to take care of it more and use it in right way so as to continue benefitting from the power of Netezza. Although most of the things are automatically taken care of but still here are some tips that you might want to take care of in the following cases:
A) when everything is alright (will you not want it to remain that way):
- Keep a tab on the catalogue size – more often than not, users create some database objects for testing etc but forget to remove after use thereby increasing the catalogue size. You might run into problems related to performance when catalogue size becomes large.
- Check to see if you are making use of zone maps effectively – a very powerful feature of Nz. Take a sample of your queries and do nz_zonemap for the tables involved in the query to get the information to deduct whether filtering with zone map is in effect or not.
- Check your table distributions from time to time. You can use nzAdmin or the web-based performance portal.
- More often than not, use the “nz_” scripts when available for the task you are going to perform where applicable. For example, use nz_groom instead of groom directly.
- Update statistics and groom; timing for doing these is very important. You should run groom in relatively less workload window and update statistics after inserting/deleting/updating larger amount of rows.
- Schedule health monitor check with the help of support (no timeline but quarterly should be enough) and follow the suggestions.
- Schedule disk monitor check with the help of support (no time line again) and follow the suggestions.
- DO NOT change any system parameters without help of support and DO NOT install anything on the system without asking IBM support – your warranty gets voided if you do, as per my information.
Although, you might not want to worry about all of these points but my suggestion would be to at least follow points 6,7 and 8 as normally in Netezza type of system you must be having really big data.
B) when something you feel is wrong:
Well, should not happen but if it happens, your next actions really would be driven by the type of problem that you might encounter. More often than not, the best way is to contact IBM support if you think that you are following best practices and problem is directly related to netezza.
But yes, sometimes in my experience, problems that you encounter may be elsewhere and in that case it is always a good idea to review your analytics setup as a whole which mostly comprises of:
Sequencing of jobs,
Data Model in use,
SQL itself, and
All of these areas, play important role for performance and should be suspected when a problem related to performance occurs.
I will just focus on Netezza in this blog and my worry is that this blog is becoming too big so will try to wrap it up also. Key things in Netezza which should be focused on, are:
- Catalog Size:
Affects auto vacuum which is run at frequent intervals by Nz and also my suggestion would be to do nz_manual_vacuum. In the case of nz_manual_vacuum not only the vacuum of data happens but also reindexing of the entire database happens. A regular vacuum does not and cannot do a reindex. That is why nz_manual_vacuum script is provided. Over a period of time the index files accumulate holes. A reindex cleans this up.
- QHD (Query History Database):
Check in QHD if you have one setup if the queries were running faster earlier or getting to know time window in which something looks wrong. Basically review the data present with you.
Also, at regular intervals, remove old data from the history database based on your need to keep the history
- nzhistcleanupdb -d histdb -t "<date>".
- Distribution, Groom, Zonemaps, CBTs and Statistics:
As mentioned earlier for few of the above, these are the main things that if not correctly used, affect query performance.
Review your Workload Management policy based on the users and groups that would want system access in a particular time window.
There might be some system variable and hardware related factors involved but one should not be reviewing/changing those as IBM support person needs to do that.
I will stop here for this topic – you might want to read my other blog for more information related to Nz: