Too many '/'s in the title. That just denotes that this is not a new idea but a practice that is more often than not, forgotten. We have essentially become lazy and very dependent on what IT industry offers us but we forget that with very little effort, one can optimally use the product and really make efficient use of investment that was put in.
We have seen/practiced many methods of using the software/appliance/next to come, kind of tools to be used for easing our day to day operations and also telling us about what to do next (analytics), and handle big data to our use, as for past few years we are in a habit of generating a lot of data and not knowing what to do with it - nevertheless, all for our benefit. This statement kind of sums up the IT industry (or not ?).
Now coming to the topic of appliance and in particular Netezza, true claim is obviously to just plug and play and you get analytics at unbelievable speeds and when I myself tried Netezza, I find the claim to be true to the cent, BUT is where I want to focus in this blog. When you are using anything, there are set of unsaid rules that are to aid easy use and to never get below expectations and same is true for any appliance for example, microwave oven - you need to put it on right setting to get the right dish out of it. So there are some etiquette that one should follow in order to get most out of the appliance and I intend to tell just some more things about Netezza in next few paragraphs. I would however put a disclaimer that as in appliance do not change any parameters (based on the list of things I am going to tell you) yourself without IBM support's help, otherwise the warranty is void and stuff as is true in any other appliance.
Etiquettes (Nz DBA):
A) Do not let the NZ system get treated as OLTP system.
This simply means you knowing that your system is for real analytics and not for transaction processing. A lot can be written regarding this topic but I think I have conveyed my point. If not, please ask and I will respond.
B) Do run nz_responders frequently (mostly when problem is reported).
DBA's very good friend is "nz_responders".
At intervals of 10 seconds it gets the information about currently running queries. It gets:
1. Current Time
2. Plan# -> one can get the plan file from files in /nz/kit/log/planshist/current/*.tgz based on the time. This plan is different from what one can generate using explain verbose etc. This is the actual plan which was run with runtimes.
3. Snippet: (3/6) will mean that 3rd snippet of total 6 is currently running.
4. Time: (35/40) Just take first one,i.e., 35 as elapsed seconds of the query.
5. State: Self explanatory.
6. Busy: Number in this column like 184 means number of data slices (loosely linked with term SPU) running for this query.
7. Dataslices: If you see something in this column it means that it is waiting for those particular SPUs.
8. SQL: truncated query text
This more often than not will actually be enough to see if there is a problem and reason for it. I case you spot a problem contact the in-house dev team regarding the problematic query and if that is as expected - call IBM support.
C) Run diagnostic scripts with different options to nzsqa.
Ask your support engineer for a script to get you information about possible problems.
Monitoring GUIs that Nz has also show enough information at high level. Please use this CUI method when you think a problem might occur and need point in time analysis.
D) Directories of interest:
1. /nz/kit/log/postgres/pg.log -> logs all the queries and their timings etc.
2. It is good idea to check /nz/kit/log/spucores directory for any new file that got generated. If yes - spu has core dumped and restarted if system is up. Contact support in that case.
E) Good old toolkit mostly installed in /nz/support/bin (directory structure my be different in different cases, ask your support to provide more info) -> please note that only files starting with "nz_" are script files and pretty self explanatory based on filenames itself. For example:
nz_db_size, nz_columns, nz_stats, nz_zonemap (last one actually is the key to performance in many cases).
F) Know that you have best AMPP system with good backup mechanism in that your data is mirrored on a separate disk so that it's easier to get the data back even when a disk fails.
G) Best thing last:
There is a script for best practices nz_best_practices which is self explanatory. You would really like the output. If in doubt, ask.
I hope this blog helped in some way - there is some information that you will need to dig yourself and that is my intention. Also I will call Nz users and developers to add to the points I have mentioned but not going into details as after all NZ is an appliance.