MAXDB too slow !!

[Former Member]

Hello,

We are running MaxDB 7.6.06.03

on CentOS 5.3 with 16 GB RAM

MaxDB seems to be consuming the whole RAM ! the 16 GB

"
MemTotal:     16440216 kB
MemFree:        508564 kB
Buffers:        288496 kB
Cached:        3269404 kB
SwapCached:      12764 kB
Active:       12115428 kB
Inactive:      2377076 kB
HighTotal:           0 kB
HighFree:            0 kB
LowTotal:     16440216 kB
LowFree:        508564 kB
SwapTotal:    12289716 kB
SwapFree:     12163328 kB
Dirty:            4240 kB
Writeback:           0 kB
AnonPages:    10933132 kB
Mapped:         121976 kB
Slab:          1216804 kB
PageTables:     164460 kB
NFS_Unstable:        0 kB
Bounce:              0 kB
CommitLimit:  20509824 kB
Committed_AS: 23502340 kB
VmallocTotal: 34359738367 kB
VmallocUsed:    275992 kB
VmallocChunk: 34359462263 kB
HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
Hugepagesize:     2048 kB
"

is this normal for Maxdb please ?

Configuration Parameters:

Cashe_size 1548032
Instance_tyoe OLTP
Log_Mirrored NO
Log_Segment_size 21333
Maxbackupdevs 2
MaxCPU 2
Maxdatavolumes 40040
Maxlogvolumes 2
Maxusertaks 500
MCOD NO


Data Area: 
Volumes 2
totalsize 30.000,000 MB


Log Area

No. of log Volumes 1
Total Size 500,00 MB



ache Sizes Size 
    
I/O Buffer Cache 12.094,00 MB 
    Data Cache 12.090,58 MB 
    Converter 2,39 MB 
    Misc. 1,03 MB 
Catalog Cache 250,50 MB 
Sequence Cache 0,01 MB 
 
   Cache Accesses 
  Accesses Successful Unsuccessful Hit Rate 
          
Data Cache 1.257.150.612 1.256.627.690 522.922 99,96% 
    SQL Data 1.257.089.604 1.256.566.682 522.922 99,96% 
    OMS Data 0 0 0 100,00% 
    History/Undo 61.008 61.008 0 100,00% 
Catalog Cache 26.144.043 25.192.771 952.028 96,36% 
Sequence Cache 0 0 0 100,00% 
 
 
Cache-Specific Parameter Settings 
  Value 
    
CACHE_SIZE 12.094,00 MB 
CAT_CACHE_SUPPLY 250,50 MB

Thanks alot for any help in advance

[lbreddemann · Accepted solution]

HI there,

if you're a SAP customer, please do open a support message.

In general most often bad query performance in MaxDB is due to:

- wrong parameter setup

- missing/wrong indexes

Most other reasons are rather seldom.

Therefore make sure to have the paramters setup as recommended.

Also start the DBAnalyzer to gather some performance specific information.

Finally please review how much memory you can actually allow the database to take.

As soon as the database kernel needs to page out memory that should be allocated by the Cache you should DOWNSIZE the cache.

regards,

Lars

[Former Member · Accepted solution]

Thanks Simon, Thanks Lars,

@Simon: yes it is x86_64

USE_OPEN_DIRECT YES

@Lars

I will downsize it.

I am unfor. not a SAP Customer, btw. in almost all FAQ notes there is a (SMP Login required), it is maybe a stupid Question but what is SMP login please?

[Melanie · Accepted solution]

>

> I am unfor. not a SAP Customer, btw. in almost all FAQ notes there is a (SMP Login required), it is maybe a stupid Question but what is SMP login please?

SMP login is 'Service MarketPlace login' - for SAP customers only.

So you won't be able to view these notes. Sorry for that.

You're only chance is to check the information available in the wiki.

Regards, Melanie

[Former Member · Accepted solution]

thanks Melanie

It is a pity, because all notes required Smp login that is in the case you meant this wiki: https://www.sdn.sap.com/irj/scn/wiki?path=/display/maxdb/faqSAPNotes

anyway:

I came to the company and found them using MaxDB so i have to live with it but could you please help me finding the Variable where you give the RAM limit to the Database?

I have installed MaxDB on a test machine to become more familiar with its administration, and in the installation i could say: i want maxdb to use: 8 GB RAM

but i cant find the config file where i can edit this 8 GB RAM

could you please tell where to edit it?

thanks alot

[lbreddemann · Accepted solution]

Hi there,

the variable (better known to be a parameter) you're looking for is:

[CACHE_SIZE|http://maxdb.sap.com/doc/7_6/ef/eb3d408ae01e24e10000000a1550b0/frameset.htm]

Concerning the performance issues, you really should start the DBAnalyzer and check the DBAN.prt file for warnings.

regards,

Lars

[Former Member · Accepted solution]

Hi Lars,

I have changed the Cash_Size "using Database Manager - Instance - Configurations- Parameters" yesterday after you posted me your advice, and was running the DBAnalyzer already:

I haved changed the Cash_Size first to default = result: Database crashed complitly und unfor. DBAnalyzer was stoped before that.

i keept trying to change "increase" the Cash_Size to get the DB running and only arround the old size:1548032 started the DB to work again!!!!

some log of the DBAN.prt:

===== #0          at 2009-05-27 03:43:20
*  I   
*  I  Version information:
*  I  --------------------
*  I  Database Analyzer configuration file version 7.6.06 Build 003-123-202-135
*  I  Kernel version: Kernel    7.6.06   Build 003-123-202-135
*  I   
*  I  System information:
*  I  -------------------
*  I  Number of CPU's 4, processor type: x86_64
*  I  Physical memory 16054 MB, virtual memory 12001 MB, memory allocated from instance: 12897.18 MB
*  I  Operating system: Linux 2.6.18-128.1.6.el5 #1 SMP Wed Apr 1 09:10:25 EDT 2009
*  I   
*  I  Instance configuration information:
*  I  -----------------------------------
* W1  No configuration check of instance PDB has been performed so far!
*  I  Number of CPUs (MAXCPU) usable by instance: 2, max user tasks: 500
*  I  Size of data cache 1547587 pages, 19.59% in use, size of converter cache 314 pages
*  I  Number of data volumes 2, usable size 3839996 pages, used size 563967 pages (14%)
*  I  PACKET_SIZE is 131072
* W3  Log automatic overwrite is set to 'on' !
*  I


more warnings:

* W1  User task 503 processing same command since 901s, 4445 dispatches, application pid 22437

* W1  52531 primary key range accesses, selectivity 3.64%: 6072272 rows read, 221315 rows qualified

* W2  2 index range accesses, selectivity 2.34%: 20599 rows read, 481 rows qualified

* W1  UKT8: commands: 1755533, dispatches: 61201933, user task 521 is running, 4 tasks are waiting for CPU
* W1  UKT8: user task 547 is waiting for CPU in state 'Command wait'


some info:

*  I  CPU utilization: instance xx: 42.4% (usr: 42.06%, sys: 0.33%), host: 398.45% (usr: 247.5%, sys: 150.94%, idle: 1.78%)

thanks for your help

[lbreddemann · Accepted solution]

> I have changed the Cash_Size "using Database Manager - Instance - Configurations- Parameters" yesterday after you posted me your advice, and was running the DBAnalyzer already:

> I haved changed the Cash_Size first to default = result: Database crashed complitly und unfor. DBAnalyzer was stoped before that.

Hi again...

Ok, first thing: it's a CACHE and has nothing to do with money (cash) ...;-)

It's the memory area where the database puts the data it will work on. It's meant to speed up data processing as the data does not need to be read from disk again.

There is no default-size for the cache size.

And the database really shouldn't crash when you set it to a smaller value.

Be aware that the units for the parameter is not kilobytes but PAGES - thats 8K chunks of data.

So, when you want to setup a cache of say 5 GB you'd set the CACHE_SIZE parameter to 655360 (=> 655360*8KB = 5242880 KB = 5120 MB = 5GB).

What kind of error message did you get in the KNLDIAG after changing the CACHE_SIZE??

> * I Kernel version: Kernel 7.6.06 Build 003-123-202-135

> * I Number of CPU's 4, processor type: x86_64

> * I Physical memory 16054 MB, virtual memory 12001 MB, memory allocated from instance: 12897.18 MB

> * I Operating system: Linux 2.6.18-128.1.6.el5 #1 SMP Wed Apr 1 09:10:25 EDT 2009

> * I Number of CPUs (MAXCPU) usable by instance: 2, max user tasks: 500

> * I Size of data cache 1547587 pages, 19.59% in use, size of converter cache 314 pages

> * I Number of data volumes 2, usable size 3839996 pages, used size 563967 pages (14%)

> * I PACKET_SIZE is 131072

> * W3 Log automatic overwrite is set to 'on' !

Ok, we already see that the data cache really is a too large.

It can take up to 1547587 pages but currently only 563967 pages (ca. 36% of the cache size) are used.

Every byte that the cache is larger than your data is wasted memory.

Therefore usually the cache is sized to be approx. 1%-5% of the database size.

In your case, setting the cache to 65536 pages (512MB) should be more than enough.

Lars

[lbreddemann · Accepted solution]

2nd part of my reply... (due to the "unformatting" of forum entries that exceed 5000 characters).

> * W1 User task 503 processing same command since 901s, 4445 dispatches, application pid 22437

> * W1 52531 primary key range accesses, selectivity 3.64%: 6072272 rows read, 221315 rows qualified

> * W2 2 index range accesses, selectivity 2.34%: 20599 rows read, 481 rows qualified

> * W1 UKT8: commands: 1755533, dispatches: 61201933, user task 521 is running, 4 tasks are waiting for CPU

Well, ok, these are warnings about untuned SQL statements.

You should review your statements and the execution plans for them and check if there are indexes missing (there very likely are missing indexes!)

> * I CPU utilization: instance xx: 42.4% (usr: 42.06%, sys: 0.33%), host: 398.45% (usr: 247.5%, sys: 150.94%, idle: 1.78%)

Really - check the parameter setup! [https://www.sdn.sap.com/irj/scn/wiki?path=/display/maxdb/sapMaxDBParameter+Recommendations]

regards,

Lars

[Former Member · Accepted solution]

sorry for the late feedback,

thank Lars Breddemann it worked

[Former Member]

Thanks Lars,

I will try to change the Cash size again "over the weekend", maybe there was another reason for the crash, i will check the kernel log too.

[simon_matter]

>

> Hello,
> 
> We are running MaxDB 7.6.06.03
> on CentOS 5.3 with 16 GB RAM

That's x86_64, right?

You gave it ~12G of CACHE_SIZE which doesn't look very wrong.

If you run other memory consuming applications on the same server you may want to reduce it. Apart from that, do you use direct I/O? That can be very important because otherwise you fill your memory with useless filesystem cache. Make sure you have

USE_OPEN_DIRECT YES

and depending on how you make backups also

USE_OPEN_DIRECT_FOR_BACKUP YES

IIRC that wasn't the default with your release so you have to set it.

Regards,

Simon