Create IQ DB and best practices

[former_member199543]

hello IQ world!

I will have to work on POC project where SAP IQ 16 is used as main DWH and i would like to get experts overview about the parameters, when we create IQ db from the scratch. There are bunch of parametrs, which makes sense for experts, but if you are not in touch with this 24/7 , then this can turn into a nightmare The size of DB is expected to be around 1.5-2 Tb, no extra wide tables - max 15 columns, biggest fact table expected to be around 700m, max up to 1 billion rows.

According to best practices, i think that Page Size can be 4096 IQ Page Size 131072.

What about IQ Main dbspaze size and Local temporary DB size? Does it makes sense to set these values close to expected max size?

regards, Mike

[markmumy]

Have you looked at my IQ Hardware Sizing Guide?

Don't change the SA page size (leave it at 4k).  I would use an IQ page size of 128 as well, just due to the database size.

What hardware?  How many CPUs and cores?  Hyper threading?  How much memory?  What kind of storage?  How many physical disks?

What is your expected user load?  Concurrent queries and loads?  Performance metrics?

Mark

[former_member199543]

Brilliant as always , thank you, will inspect that Sizing Guide. In the very first stage we will be using Vmware virtual machine, most likely 2 CPU with 4 cores each with 64Gb of memory, Windows box, later we will migrate to SUSE, i SSD drive. SAP DS Job server and Designer also will be running on the same box.

Regarding usage in general, data load itself will be done in non office hours, expected volumes 70-Gb, due to complexity of source system we need to fully reload few fact tables from the staging area (no delta is possible here). yes, concurrent queries and loads. SAP Dataservices will be used there and SAP BI for reporting puproses, final result is few snowflake schemas, so no extra complex queries will be sent to DB, logic is handled in ETL.

Ok, I leave both page sizes with default values, main store will set to 10g with the same value in reserved, what about temp store, i think 200gb and 100g as reserve.

The problem here is that IQ differs from other DB vendors and all these parameters are relatively complex to understand if developer do not have 10+ project experience, besides very rarely(yours as exeception) we can find well written guides how to implement this and that. If we set these parameters not correctly, then IQ runs slower even compared to MySQL.