Netezza Performance Server

 View Only
Expand all | Collapse all

Percentage limit of NPS total Storage Usage.

  • 1.  Percentage limit of NPS total Storage Usage.

    Posted Wed February 07, 2024 06:26 AM

    Hi All,

    Is there a limit on the Total Storage Usage Percentage on NPS? Below which it is not possible to go down?

    On my NPS Production Environment by executing the command /nz/support/bin/nz_storage_stats

    it results Remaining about 19% 

    Total Storage
         Available        (TiB)   134.128
        .....
    .................
        Remaining          (%)   19.065

    Which is the Percentage limit?

    Thanks all.

    Bye
    Andrea



    ------------------------------
    Andrea Ceccotti
    ------------------------------


  • 2.  RE: Percentage limit of NPS total Storage Usage.

    Posted Tue March 12, 2024 08:09 AM

    Hi
    As far I'm aware there was no limit to Database/Data size in Netezza systems: except physical storage capacity. 
    Since NPS / (CP4D) share vast majority of the code with Netezza: I presume it's the same. 
    That is why is so important to keep your data "clean":
    - groom tables (with proper backup/groom sequence or just go for BACKUPSET NONE)
    - use distribute on random and enable random dist chunk
    - for small tables do not use distribution organization (otherwise will use 3MB on every dataslice)
    - developers have nasty tendency to spawn objects (tables) everywhere: drop those on sight (if nobody admit using it: check first in HISTDB what/who/why?)





    ------------------------------
    Adam Matusewicz
    ------------------------------

    Original Message


  • 3.  RE: Percentage limit of NPS total Storage Usage.

    Posted Tue March 12, 2024 11:48 AM
    Hi Adam,

    thanks a lot for your answer what I need to know is what percentage of usage or free can be reached on storage. This is my current situation on NPS Production Appliance:
    Total Storage
         Available        (TiB)   134.128
         Allocated        (TiB)   102.472
         Allocated          (%)   76.399
         Remaining        (TiB)   31.656
         Remaining          (%)   23.601

    For Remaining percentage now at 23% but we arrived to 18% how is the limit before Netezza stops itself, if I remember well it does not arrive to 0% or close to it, it stops before.

    What doo you mean for 
    - for small tables do not use distribution organization (otherwise will use 3MB?

    Thanks a lot.

    Bye 
    Andrea



    Original Message


  • 4.  RE: Percentage limit of NPS total Storage Usage.

    Posted Tue March 12, 2024 12:01 PM

    Hi Andrea
    If system will reach 100% on single dataslice: it will stop (as Huw wrote in other post). Check output of nz_skew and redistribute table(s) with large skew to random or choose better distrubution keys. 

    As for 3MB: when table have distribution it is by default allocated 3MB extent on every dataslce: regardless it will use them or not. 
    We saw many occasions where customers kept small tables (for example country codes, postal codes etc.) and used distrubtion on one column: so small few KB table were distributed on all dataslices and (depending on your system size) used 200-500 MB instead keeping it on single dataslice - use 3MB and broadcast table data when needed from single dataslice instead all of them. 



    ------------------------------
    Adam Matusewicz
    ------------------------------

    Original Message


  • 5.  RE: Percentage limit of NPS total Storage Usage.

    Posted Tue March 12, 2024 12:12 PM
    Edited by Rajshekar (Shekar) Iyer Tue March 12, 2024 12:13 PM

    Netezza will throw alert if one or more data slices reached capacity between 80-85, 85 to 90 and 90-95.  At these levels, the system may not go down, but it is possible that queries that act on a lot of data can get aborted as there is no space to store intermediate results.  Reference 

    What doo you mean for 
    - for small tables do not use distribution organization (otherwise will use 3MB?

    When tables are defined with distribution, it is spread across all data slices.  For small tables, it could result in a lot of space being allocated but not used.  In such cases if the table has RANDOM distribution, Netezza uses space efficiently  Link.  



    ------------------------------
    Rajshekar (Shekar) Iyer
    ------------------------------

    Original Message


  • 6.  RE: Percentage limit of NPS total Storage Usage.

    Posted Wed March 13, 2024 10:01 AM

    Adam, thank you for this reply. Could you clarify a little further on the following:
    'Enable Random Dist Chunk'? We use random distribution for nearly all of our tables, most of which are small tables. I've not heard of random dist chunk throughout our years of netezza use, nor even when investigating the immense table size used exapansion that we saw when migrating from on-prem NPS to Azure hosted NPS.
    For small tables do not use distribution organization, I assume that means simply use distribute on random? We use datastage to drop and create a good number of our tables with the random distribution set, most everything else is a truncate and load operation, also using distribute on random. We have no specified any organize on properties for tables. Is there an easy way to check what the organize on values are for multiple tables? Is there a size threshold before setting an organize column?



    ------------------------------
    Jackson Eyton
    ------------------------------

    Original Message


  • 7.  RE: Percentage limit of NPS total Storage Usage.

    Posted Wed March 13, 2024 10:10 AM
    It is postgresql.conf setting:
    https://www.ibm.com/docs/en/psfa/7.2.1?topic=extents-enabling-random-chunk-distributions
     
    The default value of the system.enableRandomDistributionChunkSize registry variable is no (off).
     
    But again: this is for 7.X (old Netezza). 
     
    I believe that this setting is ON by default on NPS (at least 11.2.X)
    [nz@dev3majcp4d nz]$ nzsql -c "show enable_random_dist_chunk"
    NOTICE:  ENABLE_RANDOM_DIST_CHUNK is on
    SHOW VARIABLE



    ------------------------------
    Adam Matusewicz
    ------------------------------

    Original Message


  • 8.  RE: Percentage limit of NPS total Storage Usage.

    Posted Wed March 13, 2024 02:11 PM

    > We have no specified any organize on properties for tables
    Netezza works best when you organize large tables on columns that have higher chances on being used in joins and are fairly evenly distributed.  This will ensure least data movement during the execution of the query (co-located joins).  I would highly encourage setting a distribution for large tables Link

    > Is there an easy way to check what the organize on values are for multiple tables?
    You could use nz cli on your cloud instance Link .  nz_ddl_table can show ddl for all tables.  Can be restricted by database.  Look for DISTRIBUTE ON in the definition.  

    > Is there a size threshold before setting an organize column?

    Its really dependent on your system size.  Use nz_db_size and start with tables < 50 MB.  



    ------------------------------
    Rajshekar (Shekar) Iyer
    ------------------------------

    Original Message


  • 9.  RE: Percentage limit of NPS total Storage Usage.

    Posted Tue March 12, 2024 08:28 AM

    Further to Adam's response - please bear in mind that if a single data slice gets 100% full, then the entire system stops.

    So if your total disk space is not around 703TB and you're wondering why the percentage is lower than expected, this is most likely due to uneven data distribution across the data slices caused by distributing on highly skewed columns.

    Run the nz_skew command to get a feel for which objects may need to be redistributed to get a more even distribution (which will not only improve the amount of disk space available, but also likely improve query performance). 

    Finally, as Adam suggests, remove any old dead versions of records that may be consuming storage space unnecessarily by running the nz_groom command.

    Hope that's helpful….

    Huw



    ------------------------------
    Huw Ringer
    ------------------------------

    Original Message


  • 10.  RE: Percentage limit of NPS total Storage Usage.

    Posted Wed March 20, 2024 07:02 PM

    It is good practice to have X% of free space for maintenance and ETL processing, depending on how much transient temp space is used by maintenance or ETL workloads.  Typical ETL workflows use 5-20% of the system storage, every system is different and should be monitored during maintenance and heavy ETL processing.

    On NPS 11.2.x there is a new nz_storage_history table that tracks daily storage growth of the databases/tables in the Netezza environment.  It is important to know the systems storage growth rate for storage capacity planning.  This script can be run from the NPS host as follows:

    Add the following to the nz users crontab:

    */5 * * * * source $HOME/.bashrc ; /nz/support/bin/nz_query_history HISTDB -query -sysutil -gra -storage > /dev/null 2>&1

    You can remove any of the options to the nz_query_history script as desired, the -storage option will create a new table (if not exist) in the HISTDB database called: nz_storage_history.  The storage stats will populate once per day, now you can create queries to track the growth rate at the database level and/or table level.  A few views will be created the first time the nz_query_history runs with the -storage option.

    Table:   NZ_STORAGE_HISTORY

    Views:  NZ_STORAGE_HISTORY_BY_SCHEMA
                   NZ_STORAGE_HISTORY_BY_DATABASE
                   NZ_STORAGE_HISTORY_SUMMARY



    ------------------------------
    DANIEL HANCOCK
    ------------------------------

    Original Message


  • 11.  RE: Percentage limit of NPS total Storage Usage.

    Posted Thu March 21, 2024 08:31 AM
    Hi All,

    thanks a lot Daniel for your feedback/analysis, just one further info please, so a low percentage of free Storage can not only be a problem for space in general but it can also affect system performance? (maintenance or ETL workloads).
    Is it so?

    Thanks
    Bye
    Andrea



    Original Message


  • 12.  RE: Percentage limit of NPS total Storage Usage.

    Posted Thu March 21, 2024 09:23 AM


    ------------------------------
    DANIEL HANCOCK
    ------------------------------

    Original Message