Problems with (amount of?) indexes on MaxDB v7.5.00.44

[Former Member]

Hello,

I try to find the reason for long responsetimes in combination with longer timeranges in select-statements

Actually we try to renew our support-contract for MaxDB - according to Jörg Hoffmeister (Development Manager) I/we can/should try to solve this problem through the forum.

As we are running our databases in a 24/7 environment with our own CRM-system, I've recovered the database on a dedicated server...

The backup-size of the database is about 22785 MB (no BLOBS - pure ASCII)

SERVERDBSIZE;MAXDATAPAGENO;MAXPERM;MAXUSEDPAGES;USEDPERM;PCTUSEDPERM;USEDTMP;PCTUSEDTMP;UNUSED;PCTUNUSED;UPDATEDPERM;SERVERDBFULL;USEDBLOCKS;LOGSIZE;USEDLOG;PCTUSEDLOG;LOGNOTSAVED;PCTLOGNOTSAVED;LOGSINCEBACKUP;LOGSEGMENTSIZE;SAVEPOINTS;CHECKPOINTS;

3932160;?;3932160;2913050;2747415;69;78;0;1184666;30;12449;NO;2747423;130807;2799;2;2799;2;0;21845;43083;0

I was playing around in the backup-instance to locate the performance problem (which occur here too)

In one scenario I've tried to upgrade the DB-engine -> failed.

Figured out that the "load_systab" (on the live-db too) fails (v7.5.00.44)

Installing comments for SYSINFO tables

-

-

---

Traceback----

-

---

File "/opt/sdb/7500/env/installib.py", line 375, in connectAndInstall

installRoutine (session, options)

File "/opt/sdb/7500/env/lsystab.py", line 170, in install

stopIfSqlNotOK (session, "CALL SYSVERIFYCATALOG");

-

-

---

Command----

-

---

CALL SYSVERIFYCATALOG

-

-

---

Error----

-

---

loader.LoaderError: SQL CREATE TABLE FAILED WITH ERROR -4003 (error position: 1)

Kicked all triggers, procedures and indexes - viola now it works

==================================

Installation successfully finished

==================================

Recovered the mess again - found that the indexes on a specific table make the problems (this table is involved in the long response-times - makes sense)

Played around a litte bit more (drop and create index) and found the number of indexes seems to play a role

(deleted indexes first "from the one side" tried to load_systab after each - then restored database - deleted indexes in reverse order)

The Database contains 561 userdefined indexes

Reducing the number of HS.indexes from 61 to 38 seems to get the "load_systab" work agin (independend from which index)

Deleting alternativ "bigger" indexes from other tables had no effect.

The latest creation time of the HS.indexes are from 2006-06-13 - problems are reported since 2007-12-15

Seems in that time range where no index-changes... the table contains 2796075 rows

Testequipment/hardware is a DELL PE 2950 with 2GB RAM, 2x DUAL Intel(R) Xeon(TM) CPU 3.00GHz

DISK LSI Logic SAS based MegaRAID RAID5 ~ 250GB

OS: Gentoo 2007.0, Kernel 2.6.23-gentoo-r8 x86_64

Modified kernelparameter to:

kernel.sem = 4096 512000 1600 2048

kernel.shmall = 65959424

kernel.shmmax = 1055352832

kernel.msgmax = 16384

If it makes sense i could post the complete tabledefinition and DB-parameters here...

I have not really ideas which DB-parameters to screw around...

Which db-parameters have control on such an effect?

Best regards,

Harald

[Former Member · Accepted solution]

Thanks again -

while my "Check database structure" where running - I've read a parallel thread -

"Performace Question"

which seems to point in the direction of the performance probs with our database.

Some tables have more than 15 million rows - maybe the "ALTER TABLE <tablename> SAMPLE 10 PERCENT" will fix the problem.

The updated and "clean" state of the test-db will help to find the cause anyway...

Should I set this thead to answered till I have more facts available?

Best regards,

Harald.

[Melanie · Accepted solution]

Hello Harald,

this thread is mainly about the problem with load_systab when you have more than 38 indexes on one table. This problem is solved with version 7.6.03 (as well as with version 7.5.00.47).

So I would recommend to close this thread and to open another one if you need more help concerning the performance problems.

Best regards,

Melanie

[Former Member]

Ok - thanks

I hope that I can find the right settings

Best regards,

Harald.

[Former Member]

Thanks for the quick response and sorry - the update was a little bit tedious ...

I've updated my playground to MaxDB v7.6.03.15

> maxdb-all-linux-64bit-x86_64-7_6_03_15 # ./SDBUPD

>

>

> MaxDB INSTANCE UPDATE

> *********************

>

>

> starting installation We, Mar 05, 2008 at 08:16:42

> operating system: Linux X86-64 2.6.23 gentoo-r8-C2SMPv1 GLIBC 2.6.1

> callers working directory: /apps/install/maxdb/maxdb-all-linux-64bit-x86_64-7_6_03_15

> installer directory: /apps/install/maxdb/maxdb-all-linux-64bit-x86_64-7_6_03_15

> archive directory: /apps/install/maxdb/maxdb-all-linux-64bit-x86_64-7_6_03_15

>

>

> beginning to check sap db instances

> existing instance:

> 0: SCHADENU "/opt/sdb/7500" 7.6.3.15

> 1: none

> please enter instance id: 0

> please enter database manager operators name: CONTROL

> please enter database manager operators password:

> start new instance update

> finding instance type...

> checking mmap support...

> finding starting release...

> finding migration strategy...

> looking for running instances...

> checking paramfile modifications...

> checking volume access...

> checking data consistency for database migration...

> Value of MCOD-Parameter is NO

> checking SAP DB instance "SCHADENU" successfully We, Mar 05, 2008 at 08:16:56

>

> ...

> ...

> ...

>

> checking consistence of package data in install registry...

> check files... ok

> skipping package

> installation of MaxDB Server finished successfully We, Mar 05, 2008 at 08:17:01

> finding instance type...

> checking mmap support...

> starting release already known

> migration strategy already known

> running finalize check...

> current database state is OFFLINE

> checking parameters...

> switch database state to ADMIN

> switch database state to ONLINE

> loading system tables...

> checking catalog...

> MaxDB instance "SCHADENU" updated successfully We, Mar 05, 2008 at 08:17:34

> maxdb-all-linux-64bit-x86_64-7_6_03_15 #

Hope this satisfies the state updated....

Started SQL-Studio ->

CHECK CATALOG

> > 2. Execute the following SQL statement:

> >

> > CHECK CATALOG

> ++++ Execute +++++++++++++++++++++++++++++++

> Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed

> CHECK CATALOG

> Statement successfully executed. No Result

> Execution Time: 08:31:41.937 - 08:31:42.858 (00.921 sec)

SELECT * FROM domain.parameters

> > 3. Execute the following SQL statement:

> >

> > SELECT * FROM domain.parameters

> ++++ Execute +++++++++++++++++++++++++++++++

> Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed

> SELECT * FROM domain.parameters

> Statement successfully executed. No Result

> Execution Time: 08:32:48.734 - 08:32:48.764 (00.030 sec)

Was it expected to get no results?

Meanwhile I let check the databasestructures....

Best regards,

Harald.

[lbreddemann]

>

> Was it expected to get no results?
> 

After the upgrade to 7.6.03 - yes. The bug is fixed there.

KR Lars

[Melanie]

Hello Harald,

the problem you are describing is known and documented in the SAP error system in a SAP note.

The note says:

Error -4003 when you load system tables

Symptom
An error occurs when you load MaxDB version 7.5.00 Build 43 or Build 44.
The installation log indicates that error -4003 occurred when loading
the system tables:

Installing Database Manager tables
Installing SYSINFO tables
Installing comments for SYSINFO tables
         Traceback
  File "K:\SAPDB\TEP\DB\env\installib.py", line 375, in
connectAndInstall
    installRoutine (session, options)
  File "K:\SAPDB\TEP\DB\env\lsystab.py", line 170, in install
    stopIfSqlNotOK (session, "CALL SYSVERIFYCATALOG");
         Command
CALL SYSVERIFYCATALOG
         Error
loader.LoaderError: [-25010] SQL [-31010] CREATE TABLE FAILED WITH
ERROR -4003 (error position: 1)

Reason and Prerequisites
The database procedure SYSVERIFYCATALOG is called in MaxDB version
7.5.00 Build 43 and higher when loading system tables. This database
procedure contains errors.

PTS 1147956

Solution

1.  Create a backup of the file lsystab.py in the directory
    <dependent_path>\env (DEFAULT: sapdb\<SID>\db\env).

2.  Edit this file. Delete the following line:

    stopIfSqlNotOK (session, "CALL SYSVERIFYCATALOG");

    This line appears near the end of the file.

3.  Restart SDBUPD now.

After you have updated the database and loaded the system tables successfully, please exeucte the following steps and post the result:

1. Log on to SQL Studio as user SYSDBA (for superdba).

2. Execute the following SQL statement:

CHECK CATALOG

This statement is likely to return error 300.

3. Execute the following SQL statement:

SELECT * FROM domain.parameters

Best regards,

Melanie