Bad performance after upgrade from 7.5 to 7.6

[Former Member]

Hello,

I recently upgraded our MaxDB from 7.5 to 7.6 on opensuse10.3-64bit.

All the details and the history is available here, in thread

After upgrading the database instance works fine, but only for some minutes.

I decreased the cache_size to 50000, which is quite less, the server has 2GB RAM and there's no other service/app running on it.

-

---

Currently the server is under high load (seems like waiting for I/O)

x_cons TESTDB show ac 1 produces

SERVERDB: TISYS
T27    4     -1 UpdCnt       -1 IO Wait (R)           0 0      5        77202(s)
T70    4     -1 UpdCnt       -1 IO Wait (R)           0 0      8        77202(s)
T71    4     -1 UpdCnt       -1 IO Wait (R)           0 0      1        77202(s)
T72    4     -1 UpdCnt       -1 IO Wait (R)           0 0     10        77202(s)
T79    7     -1 User      13709 Vsleep                0 0               544(s)
Console command finished (2009-11-17 21:12:02).
SERVERDB: TESTDB
T27    4     -1 UpdCnt       -1 IO Wait (R)           0 0      4        77413(s)
T70    4     -1 UpdCnt       -1 IO Wait (R)           0 0     11        77413(s)
T71    4     -1 UpdCnt       -1 IO Wait (R)           0 0      4        77413(s)
T72    4     -1 UpdCnt       -1 IO Wait (R)           0 0      8        77413(s)
T79    7     -1 User      13709 Vsleep                0 0               545(s)
Console command finished (2009-11-17 21:12:03).
SERVERDB: TESTDB
T27    4     -1 UpdCnt       -1 IO Wait (R)           0 0      5        77612(s)
T70    4     -1 UpdCnt       -1 IO Wait (R)           0 0     10        77612(s)
T71    4     -1 UpdCnt       -1 IO Wait (R)           0 0     11        77612(s)
T72    4     -1 UpdCnt       -1 IO Wait (R)           0 0      8        77612(s)
T79    7     -1 User      13709 Vsleep                0 0               545(s)

vmstat -n 1 shows

vmstat -n 1
procs     memory    swap io     system     cpu 
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 2  2     72  16656  39548 1441040    0    0   228   321   10   17  0  0 93  7
 2  4     72  16808  39544 1440800    0    0  1664     0  525 1228  1  0  0 99
 1  2     72  15284  39544 1442520    0    0  1680     0  526 1231  0  0  0 100
 0  4     72  15348  39536 1442248    0    0  1648     0  521 1223  1  0  0 99
 1  4     72  16684  39532 1440868    0    0  1696     0  507 1224  0  0  0 100
 0  4     72  16892  39532 1440632    0    0  1672     0  514 1221  1  0  0 100

[Former Member · Accepted solution]

ups,

sorry for the mis-formatted posting. The Preview looks fine, but the final version very ugly, no idea what's going wrong.

All the details and the history is available here, in thread

x_cons TESTDB show ac 1 produces

 
SERVERDB: TISYS T27 4 -1 
UpdCnt -1 IO Wait (R) 0 0 5 77202(s) T70 4 -1 
UpdCnt -1 IO Wait (R) 0 0 8 77202(s) T71 4 -1 
UpdCnt -1 IO Wait (R) 0 0 1 77202(s) T72 4 -1 
UpdCnt -1 IO Wait (R) 0 0 10 77202(s) T79 7 -1 
User 13709 Vsleep 0 0 544(s) 
Console command finished (2009-11-17 21:12:02). 
SERVERDB: TESTDB T27 4 -1 
UpdCnt -1 IO Wait (R) 0 0 4 77413(s) T70 4 -1 
UpdCnt -1 IO Wait (R) 0 0 11 77413(s) T71 4 -1 
UpdCnt -1 IO Wait (R) 0 0 4 77413(s) T72 4 -1 
UpdCnt -1 IO Wait (R) 0 0 8 77413(s) T79 7 -1 
User 13709 Vsleep 0 0 545(s) 
Console command finished (2009-11-17 21:12:03). 
SERVERDB: TESTDB T27 4 -1 
UpdCnt -1 IO Wait (R) 0 0 5 77612(s) T70 4 -1 
UpdCnt -1 IO Wait (R) 0 0 10 77612(s) T71 4 -1 
UpdCnt -1 IO Wait (R) 0 0 11 77612(s) T72 4 -1 
UpdCnt -1 IO Wait (R) 0 0 8 77612(s) T79 7 -1 
User 13709 Vsleep 0 0 545(s) 

vmstat -n 1 shows

 
vmstat -n 1 procs memory swap io system cpu 
r b swpd free buff cache si so bi bo in cs us sy id wa 
2 2 72 16656 39548 1441040 0 0 228 321 10 17 0 0 93 7 
2 4 72 16808 39544 1440800 0 0 1664 0 525 1228 1 0 0 99 
1 2 72 15284 39544 1442520 0 0 1680 0 526 1231 0 0 0 100 
0 4 72 15348 39536 1442248 0 0 1648 0 521 1223 1 0 0 99 
1 4 72 16684 39532 1440868 0 0 1696 0 507 1224 0 0 0 100 
0 4 72 16892 39532 1440632 0 0 1672 0 514 1221 1 0 0 100 

any hints, what to check further ? regards...GERD...

[Former Member · Accepted solution]

...and some more details

OS: opensuse 10.3-64bit, Kernel 2.6.22.5-31-default

DB: MaxDB 7.6.06.03

GUI: DBStudio 7.7.06.09

even the DBStudio cannot finish refreshing the overview pane ... ;-(

any help appreciated....

[lbreddemann · Accepted solution]

Ok...

Have you checked the parameter setup of the db? (there's the parameter checker in the download area - use it!)

Have you checked whether there are any BAD Indexes?

Have you recollected all optimizer statistics?

Have you checked whether the memory (cache) settings are suitable for what is available on your system?

Since you're on linux, have you enabled DIRECTIO ?

Apart from that - start the dbanalyzer, enable time measurement and look out for warnings.

regards,

Lars

WAIT:

Sorry - I just overlooked your x_cons output, until now.

You see there many tasks "UPDCNT".

These are background tasks that are new in 7.6.

It's their job to initially scan all tables and indexes (once! it's only one time!) to collect the filecounter statistics (check the WIKI for more details on this!).

The best would be: leave these processes alone for a night or so.

Anyhow, as soon as you begin to work with the database, these processes will stop their work and only resume it once there is no user workload anymore.

Therefore: yes, the database is active w/o any user activity, but that does not mean that you cannot start the user activity.

That said my other hints should also be followed in general

Edited by: Lars Breddemann on Nov 17, 2009 10:09 PM

[Former Member · Accepted solution]

Hello Lars,

yes, this morning the performance is O.K.

I ran the parameter check script and modified 2 of them (max_cpu and increased the cache_size).

In the "old" DBM-Gui there was the check section where we could ran the update statistics. Where can I find this section in the DB Studio ?

thanks...GERD...

[lbreddemann · Accepted solution]

> yes, this morning the performance is O.K.

> I ran the parameter check script and modified 2 of them (max_cpu and increased the cache_size).

Glad to hear that.

> In the "old" DBM-Gui there was the check section where we could ran the update statistics. Where can I find this section in the DB Studio ?

Hmm.. your questions never end, do they

In DB Studio there are two GUI-supported ways to gather statistics:

1. Via the details page for single tables

2. by activating the automatic statistics update.

As a general recommendation you (as a NON-SAP MaxDB user) should enable the automatic collection of statistics.

Right now, after the upgrade, just run

update statistics *

once in an SQL session for your schemas.

Hmm... you definitively want to review the current MaxDB documentation (http://maxdb.sap.com/documentation) to catch up with the changes in administration.

best regards,

Lars

[Former Member · Accepted solution]

Hello Lars,

thanks for your patience....

After updating the statistics the upgraded database will be tested by our dev-department for checking our app's with this db.

thanks...GERD...