[IQUG] versions and IQ_SYSTEM_MAIN

Rittenhouse, David d.rittenhouse at sap.com
Wed Jun 27 11:25:32 MST 2018

Hi Hugo,

IQ_SYSTEM_MAIN contains a number of system structures so it is important to get the sizing right.


     *   Freelist
     *   Checkpoint Area
     *   TLV Replay log
     *   Version metadata
     *   Catalog buffer
     *   MPX status structures

To clarify:  version metadata (about the versions stored in user-defined main space) is stored in IQ_SYSTEM_MAIN.

Below is an excerpt from the IQ Sizing Guide - whenever there are ranges specified I recommend you go with the larger of the values in the range...  so if your database is > 100GB I would make IQ_SYSTEM_MAIN at least 2% of the user-defined main DbSpace (+ a little more if it is a MPX.)

"For databases that exceed 100 GB, it is recommended that IQ_SYSTEM_MAIN be at least 8 GB for a simplex and 16 GB
for a multiplex.  IQ_SYSTEM_MAIN would typically be sized at 1-2% of the user defined main space size
(10-20 GB for a 1 TB database). If this instance is migrated to a multiplex, an additional 0.1-0.3% (1-3 GB per 1 TB)
of space should be added per node in the multiplex.  For a 4 node system with a 1 TB database, the size would be the
16 GB minimum plus 1-3 GB per node (4 nodes) in the multiplex; or 20-28 GB."

Hope that helps,

David Rittenhouse
Centre of Excellence - Database & Data Management
M: +44 (0) 7899 948 295 | E: d.rittenhouse at sap.com<mailto:d.rittenhouse at sap.com> | Web: www.sap.com<http://www.sap.com/>

From: iqug-bounces at iqug.org [mailto:iqug-bounces at iqug.org] On Behalf Of Alhandra, Hugo, Vodafone Group (External)
Sent: 27 June 2018 17:23
To: iqug at iqug.org
Subject: [IQUG] versions and IQ_SYSTEM_MAIN

Hi folks,
I have a question regarding table level versions management in IQ 16.

1 - On IQ16 the reference manual we read
"Sizing Guidelines for Main and Temporary Stores
Three factors influence the space required for the IQ_SYSTEM_MAIN store:
Versioning - the volume of versions maintained varies.

  *   Nature of data and indexes.
     *   Dynamic nature of the data - the capacity to load more data at any time."
2 - https://help.sap.com/viewer/a8937bea84f21015a80bc776cf758d50/
"To delete data when using table-level versioning, you may actually need to increase disk space by adding a dbspace to your IQ main store."
*        3 - we observed versions really use space in user dbspace.
Can someone give a more clear explanation on how space is allocated from IQ_SYSTEM_MAIN and user dbspaces and more details on how the process works internally?
Is there any ratio regarding IQ_SYSTEM_MAIN and iq_main?

Thanks a lot,
Hugo Alhandra
Database Administrator
hugo.alhandra at vodafone.com<mailto:hugo.alhandra at corp.vodafone.pt>

gfi.pt<http://www.gfi.pt/>  I  gfi.pt/jobs<http://www.gfi.pt/jobs>  I  Gfi News<http://www.gfi.pt/gfi-news>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20180627/a5ec7c71/attachment-0001.html>

More information about the IQUG mailing list