[IQUG] versions and IQ_SYSTEM_MAIN

Mumy, Mark mark.mumy at sap.com
Wed Jun 27 17:03:40 MST 2018


The versioned data is always kept in the main store where the original data resides.  We don’t move it between main stores.  User data and versions of user data are always stored in the user main store.

The size of the main store is being changed.  I’ve got a new sizing guide out for review.  On the system main, though, it’s been updated to this:

IQ_SYSTEM_MAIN Sizing
Sizing the default main store (IQ_SYSTEM_MAIN) is relatively straightforward and based on the size of the user defined dbspaces, in total, as well as the number of nodes in the multiplex.

For databases that are less than 1 TB, it is recommended that IQ_SYSTEM_MAIN be 15-20% of the user defined main space size, with a minimum of 32 GB.  If this instance is migrated to a multiplex, an additional 10 GB of space should be added per node in the multiplex.  For a 2 node system with a 100 GB database, the size would then be 52 GB (32GB base + 10 GB per node).

For databases that exceed 1 TB, it is recommended that IQ_SYSTEM_MAIN be at least 150 GB plus 10 GB for every additional TB.  A 10 TB database would require 250 GB of IQ_SYSTEM_MAIN (150 + 10*10)

For a multiplex, an additionally 10 GB of space should be allocated for each node of the multiplex.  For a 4 node system with a 10 TB database, you would need 290 GB of system main =(150 GB + 10TB*10GB per TB + 4 nodes*10GB each).

Mark

Mark Mumy
Strategic Technology Incubation Group
SAP Global Center of Excellence |  SAP
M +1 347-820-2136 | E mark.mumy at sap.com<mailto:mark.mumy at sap.com>
My Blogs: https://blogs.sap.com/author/markmumy/

https://sap.na.pgiconnect.com/I825063
Conference tel: 18663127353,,8035340905#

From: "iqug-bounces at iqug.org" <iqug-bounces at iqug.org> on behalf of "Alhandra, Hugo, Vodafone Group (External)" <hugo.alhandra at vodafone.com>
Date: Wednesday, June 27, 2018 at 4:58 PM
To: David Rittenhouse <d.rittenhouse at sap.com>, "iqug at iqug.org" <iqug at iqug.org>
Subject: Re: [IQUG] versions and IQ_SYSTEM_MAIN

Thanks David,
So it’s only version metadata, not the version itself, right? As of my understanding version data will go into the user dbspace, right?
Besides 2% of main, Is there any to know the size of those structures? What metadata is stored?

Regards,
—
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>

From: Rittenhouse, David <d.rittenhouse at sap.com>
Sent: 27 de junho de 2018 19:26
To: Alhandra, Hugo, Vodafone Group (External) <hugo.alhandra at vodafone.com>; iqug at iqug.org
Subject: RE: versions and IQ_SYSTEM_MAIN

Hi Hugo,

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

IQ_SYSTEM_MAIN

     *   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
SAP EMEA North
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> [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<mailto: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
https://help.sap.com/viewer/a8937bea84f21015a80bc776cf758d50/16.0.11.12/en-US/a6f91dad84f21015b93990aa5547dccc.html:
“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.
o    Dynamic nature of the data – the capacity to load more data at any time.”
2 - https://help.sap.com/viewer/a8937bea84f21015a80bc776cf758d50/16.0.11.12/en-US/a750120d84f21015a1799f46fb09ee91.html
says:
“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/20180628/a9316427/attachment-0001.html>


More information about the IQUG mailing list