on 11-17-2009 8:40 PM
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
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...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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...
> 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
User | Count |
---|---|
93 | |
10 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.