Database freezes during a "create dbspace"

[claude]

Hi All,

start_iq -v2 : 12.0.1.3027

Sybase IQ/15.4.0.3027/130221/P/ESD 3/Enterprise Linux64 - x86_64 - 2.6.18-194.el5/64bit/2013-02-21 10:52:34

Every hour there is a process that connect to load file on table

What could be the root cause concerning the database which freezes during a « create dbspace for 300G "file system (with VLDB)" » operations ?

Even the dbspace is completed correctly, the database remains frozen for a moment.

Is "huge pages" on Linux might be causing this or ASA or .... ?

Thanks in Advance,

Regards,

Claude

[markmumy · Accepted solution]

This is a filesystem device you are creating, right?  When you create a filesystem device, we have no choice but to touch the entire device to initialize it.  This floods the IO channel.  Also, it extends the time that IQ spends in logic that forces the server to suspend other operations (just like during a checkpoint).  But because the device is a filesystem, this takes much longer to run (minutes rather than sub-second).

Collecting pstacks when it is frozen will confirm this.

Is there any way to pre-allocate your storage so that this isn't done during critical operations?

On a side note....

-- disable hugepages, if enabled.  IQ does not use hugepages.

-- disable transparent hugepages, too.  While any process can take advantage of this, we see some issues at the OS level with this.  It can put too much stress on memory algorithms which then causes IQ to slow down.

Here are some notes of mine that are shortly being integrated into the IQ manuals on the hugepages topics:

Disable HugePages

Hugepages is a mechanism that allows the Linux kernel to utilize the multiple page size capabilities of modern hardware architectures. Linux uses pages as the basic unit of memory, where physical memory is partitioned and accessed using the basic page unit. The default page size is 4096 Bytes in the x86 architecture. Hugepages allows large amounts of memory to be utilized with a reduced overhead. Linux uses “Transaction Lookaside Buffers” (TLB) in the CPU architecture. These buffers contain mappings of virtual memory to actual physical memory addresses. So utilizing a huge amount of physical memory with the default page size consumes the TLB and adds processing overhead.

SAP IQ does not currently use HugePages.  As such, it is imperative that the Linux kernel not be configured to allocate memory for HugePages use.  Any memory that is allocated to HugePages cannot be used by SAP IQ.

A default configured Linux system will typically have just a few MB of memory dedicated to HugePages.  The amount can be determined by running this command:

cat /proc/meminfo |grep Hugepagesize

It will typically return this value:

Hugepagesize: 2048 kB

Should more than just a few MB of memory be allocated to HugePages, it is strongly recommended to change the kernel configuration be set back to default by removing the HugePages options from /etc/sysctl.conf.

For details on HugePages in the Linux kernel, refer to this write-up: https://www.kernel.org/doc/Documentation/vm/hugetlbpage.txt

Disable Transparent HugePages

Starting from RHEL6 and SUSE 11, Transparent HugePages are implemented and enabled by default. They are meant to improve memory management by allowing HugePages to be allocated dynamically by the "khugepaged" kernel thread, rather than at boot time like conventional HugePages.  In certain circumstances, Transparent HugePages have shown to negatively impact SAP IQ performance.  It is strongly recommended to disable Transparent HugePages for SAP IQ systems until this OS feature can be fully tested under the client specific workload.

The performance issue with Transparent HugePages typically manifests itself in the form of high system time.  Using tools like vmstat and top, monitor the user, system, wait, and idle CPU utilization.  The high system utilization is a result of the memory manager as described at https://www.kernel.org/doc/Documentation/vm/transhuge.txt: “As the system ages, allocating huge pages may be expensive as the system uses memory compaction to copy data around memory to free a huge page for use.”

Should the system CPU utilization increase over time, it is possible that the OS is having to spend more time managing the Transparent HugePages and Anonymous memory segements.  Disabling Transparent HugePages can alleviate that bottleneck.

Details on Transparent HugePages can be found here:

https://www.kernel.org/doc/Documentation/vm/transhuge.txt

Details on disabling Transparent HugePages can be found in the “== Boot parameter ==” section of the previous link.

The recommendation is to use the “transparent_hugepage=never” option in the Linux kernel boot loader file.

[claude · Accepted solution]

Thanks for the all answers from the group "very very !! interesting information"

[tayeb_hadjou · Accepted solution]

Hi Claude,

From your last note, you got answers you need.

If you have not any other related questions, comments, please confirm, so the discussion will be closed.

Thanks,

Tayeb.

[saroj_bagai]

You can collect 3 samples of pstack ( 1-2 min ) apart during freeze and send it to us. Also try adding more memory to main and temp cache

[madasamy_arunachalam]

Hi Claude

Check this KBA, if any limitation in size..

2006916 - IQ crash was caused by create database with large dbfile

regards

Swami

[claude]

Hello Madasamy,

Thanks for your advice, but the database doesn't crashes just hangs or freezes. version for IQ it's IQ15.4.0.ESD#3 not IQ16.x ..

thanks

Regards,

Claude

[c_baker]

What Linux are you on?

What are your -iqmc and -iqtc settings?

Are you on file system or raw?

Chris

[claude]

see below the answer Chris,

-->What Linux are you on?

As:

$
cat /etc/redhat-release

Red Hat Enterprise Linux Server release 6.2 (Santiago)

-->What are your -iqmc and -iqtc settings?

As:

# Main Memory - 40 % of total
available RAM is recommended

-iqmc 22118

# Temporary Memory - 60 % of total
available RAM is recommended

-iqtc 33178

-->Are you on file system or raw?

As:

-File system (with VLDB)