Update filedirectory counter dead slow... (Note 1315198)

[Former Member]

MaxDB 7.6.06.04

BW 7.0

DB-Size: ~ 800 GB

After a compression of some cubes I see since a long time in x_cons sh ac 1

T80    4     -1 UpdCnt       -1 IO Wait (R)           0 0     15        2057873(s)
T81    4     -1 UpdCnt       -1 IO Wait (R)           0 0     35        2057873(s)
T82    4     -1 UpdCnt       -1 IO Wait (R)           0 0     40        2057873(s)
T85    4     -1 UpdCnt       -1 IO Wait (R)           0 0      6        2057873(s)
T212   7     -1 User      10200 Vsleep                0 0               2146(s)

and

sqlcli=> select cancelcount, tablename from SYSUPDATECOUNTERWANTED
| CANCELCOUNT        | TABLENAME                        |
| ------------------ | -------------------------------- |
|                  0 | /BIC/B0000195000                 |
|                  0 | /BIC/B0000222000                 |
|                  0 | /BIC/B0000471000                 |
|                  0 | /BIC/B0000475000                 |

I'm "waiting" for that to finish to do a heterogeneous copy (Linux x64 --> HP-UX). If I start the migration now, R3szchk will take ages to find out the tablesizes since it's done sequentially for those tables. R3szchk does a "select count(*) from <tablename> into ??" which does not use prefetching.

According to the optimizer statistics e. g. table /BIC/B0000195000 has roughly 3.000.000 pages. With an avg. I/O rate of 6 - 8 ms for a page on that system and hence 125 pages/sec estimating the table size will take > 6 hours.

What can I do to speed that up - aside from running "check data with update" in admin mode and waiting another 24 hours to finish?

Markus

[lbreddemann · Accepted solution]

> What can I do to speed that up - aside from running "check data with update" in admin mode and waiting another 24 hours to finish?

Unfortunately there is no real option for speed up here.

What I would try would be to set the CACHE SIZE to a value as large as possible and set LRU_FOR_SCAN to true. Next thing would be to force a full table scan that uses prefetching.

Once the scan is done most blocks of the table should be found in the cache and thus don't need to be read by the special tasks.

As I said, this would be try - it might not result in a brutto speedup.

Anyhow, since the update of the filecounter statistics is only necessary for those tables that have been created before the database was on MaxDB 7.6 this kind of problem should gradually vanish.

regards,

Lars

[markus_doehr2 · Accepted solution]

Hi Lars,

> Unfortunately there is no real option for speed up here.

> Anyhow, since the update of the filecounter statistics is only necessary for those tables that have been created before the database was on MaxDB 7.6 this kind of problem should gradually vanish.

Not sure of that...

Due to the nature of a BI system and the way it's implemented many tables are dropped and recreated.

From our production BI system:

sqlcli=> select count(*) from SYSUPDATECOUNTERWANTED

| EXPRESSION1        |
| ------------------ |
|              21221 |
1 row selected (6 msec)

The system was upgraded from 7.5 to 7.6 at Janurary, 24th 2007 - so there is more than two years of time to update the counters

That instance is currently on 7.6.05.14.

The machine is busy almost 24 h - there may be some minutes where nothing is running, the load is not high but it's always doing "something" and that time may not be enough to get all the counters updated.

Markus

[lbreddemann · Accepted solution]

> > Anyhow, since the update of the filecounter statistics is only necessary for those tables that have been created before the database was on MaxDB 7.6 this kind of problem should gradually vanish.

>

> Not sure of that...

>

> Due to the nature of a BI system and the way it's implemented many tables are dropped and recreated.

Well, by the design of the filcounter this problem really should only affect tables that have been created before there were filecounters.

For any newly created table/index/object the filecounter is automatically created.

> From our production BI system:

>

>

sqlcli=> select count(*) from SYSUPDATECOUNTERWANTED
> 
> | EXPRESSION1        |
> | ------------------ |
> |              21221 |
> 1 row selected (6 msec)

>

> The system was upgraded from 7.5 to 7.6 at Janurary, 24th 2007 - so there is more than two years of time to update the counters

Hmmm... the downside here is: you used the system in that timeframe

No, really, the task that performs the initial counting stops once the database is used by some other task.

The new notes

1315198 Filedirectory-Counter werden nicht aktualisiert

1316540 DBAnalyzer: User task ... processing same command since...

cover that as well.

What would be interesting would be to see how many of your tables without entries in the filecounters have been created after the upgrade

select t.owner, t.tablename, t.createdate, t.createtime from 
  files f join tables t on f.fileid=t.tableid
where f.entrycount is null
 and t.createdate <= '2007-01-24'

> The machine is busy almost 24 h - there may be some minutes where nothing is running, the load is not high but it's always doing "something" and that time may not be enough to get all the counters updated.

Yep - that's the point here.

To be honest: if it were up to me, I would say, that once you upgrade to 7.6 you've to perform a CHECK DATA WITH UPDATE once. Best would be if this would automatically update the optimizer stats (which is unfortunately does not...).

But I see of course that this would mean a too long downtime very often.

regards,

Lars

updated query

[markus_doehr2 · Accepted solution]

> Well, by the design of the filcounter this problem really should only affect tables that have been created before there were filecounters.

> For any newly created table/index/object the filecounter is automatically created.

Hmm...

> The new notes

> 1315198 Filedirectory-Counter werden nicht aktualisiert

> 1316540 DBAnalyzer: User task ... processing same command since...

> cover that as well.

See the "subject" of that posting here - that's why I created it )

I modified the statement because the list was too long:

sqlcli BIP=> select count(*) from  files f join tables t on f.fileid=t.tableid where f.entrycount is null 
and t.createdate <= '2007-01-24'
| EXPRESSION1        |
| ------------------ |
|              16100 |
1 row selected (3.072 sec)

> To be honest: if it were up to me, I would say, that once you upgrade to 7.6 you've to perform a CHECK DATA WITH UPDATE once. Best would be if this would automatically update the optimizer stats (which is unfortunately does not...).

I did that on a copy of the system about three weeks ago. It took more than 60 hours - with 84 raw volumes - so highly parallel. We have certain tables with > 100,000,000 entries, since the parallellism is only cross table, some CheckData UKT was running for LONG time because it read those tables in 8 kb pages one after the other (e. g. table /BIC/B0000587000 with ~ 6,760,000 pages with an avg. read time of 100 pages/sec. is 18+ hours for that single table). If one takes into account that we can't influence the sequence in which the tables are checked those runtimes are unrealistic.

Since almost two years we're unable to run CheckData on our systems, for the ERP it's even worse; neither in admin nor in online mode because in admin mode it takes too long and in online mode the shared locks are killing our business...

Markus

[lbreddemann · Accepted solution]

> See the "subject" of that posting here - that's why I created it )

arghs - you're right.. hmm..

> I modified the statement because the list was too long:

>

>

sqlcli BIP=> select count(*) from  files f join tables t on f.fileid=t.tableid where f.entrycount is null 
> and t.createdate <= '2007-01-24'
> | EXPRESSION1        |
> | ------------------ |
> |              16100 |
> 1 row selected (3.072 sec)

Ah ok - I made a mistake - obviously I wanted to know, how many tables are there that have been created AFTER the upgrade and lack their filecounter entry.

Could you run the statement with 't.createdate >= '2007-01-24' once again?

> I did that on a copy of the system about three weeks ago. It took more than 60 hours - with 84 raw volumes - so highly parallel. We have certain tables with > 100,000,000 entries, since the parallellism is only cross table, some CheckData UKT was running for LONG time because it read those tables in 8 kb pages one after the other (e. g. table /BIC/B0000587000 with ~ 6,760,000 pages with an avg. read time of 100 pages/sec. is 18+ hours for that single table). If one takes into account that we can't influence the sequence in which the tables are checked those runtimes are unrealistic.

>

> Since almost two years we're unable to run CheckData on our systems, for the ERP it's even worse; neither in admin nor in online mode because in admin mode it takes too long and in online mode the shared locks are killing our business...

Well, I can only agree here. This issue is hanging around for quite some time now, but as it seems it never made it to the top priority-wise...

Sorry to say that, but all I can propose is to open another support message for this to gather development attention and eventually a re-priorization of the problem.

regards,

Lars

[markus_doehr2 · Accepted solution]

> Ah ok - I made a mistake - obviously I wanted to know, how many tables are there that have been created AFTER the upgrade and lack their filecounter entry.

> Could you run the statement with 't.createdate >= '2007-01-24' once again?

I wondered already a bit...

sqlcli=> select count(*) from  files f join tables t on f.fileid=t.tableid where f.entrycount is null and 
t.createdate >= '2007-01-24'

| EXPRESSION1        |
| ------------------ |
|                  0 |

So we have no tables created after that date? I seriously doubt that...

> > Since almost two years we're unable to run CheckData on our systems, for the ERP it's even worse; neither in admin nor in online mode because in admin mode it takes too long and in online mode the shared locks are killing our business...

>

> Well, I can only agree here. This issue is hanging around for quite some time now, but as it seems it never made it to the top priority-wise...

"quite some time" is good - I see that "shared lock problem" since ADABAS 6.1 (or was it 3.2?) It was "fixed" for the update statistics - so since then (may be some months ago) we're able to run regularly UPDSTAT without impacting business. That was also something which couldn't be done for some years before (yes, we were running production system with statistics that were many years old and just updating them at the point where we thought it could help for a specific table only).

> Sorry to say that, but all I can propose is to open another support message for this to gather development attention and eventually a re-priorization of the problem.

I think I'll create an OSS call for this.

Markus

[lbreddemann · Accepted solution]

>

sqlcli=> select count(*) from  files f join tables t on f.fileid=t.tableid where f.entrycount is null and 
> t.createdate >= '2007-01-24'
> 
> | EXPRESSION1        |
> | ------------------ |
> |                  0 |

>

> So we have no tables created after that date? I seriously doubt that...

No - don't get that wrong.

You've no tables created after that date that miss their file directory statistics.

This was just to ensure that the tables missing them are in fact from a pre 7.6 database.

> > Well, I can only agree here. This issue is hanging around for quite some time now, but as it seems it never made it to the top priority-wise...

>

> "quite some time" is good - I see that "shared lock problem" since ADABAS 6.1 (or was it 3.2?) It was "fixed" for the update statistics - so since then (may be some months ago) we're able to run regularly UPDSTAT without impacting business. That was also something which couldn't be done for some years before (yes, we were running production system with statistics that were many years old and just updating them at the point where we thought it could help for a specific table only).

Hmm... why not?

Since the statistics never need to be new but just accurate this can be OK of course.

And when we look to Oracle - we even deliver specific statistics for tables.

If one looks at statistics as a model about data distribution in the database than even old stats may lead to reasonable results. If I remember correctly we've even a consulting note for Oracle proposing that approach..

> I think I'll create an OSS call for this.

Sure - I'd do the same.

Please make sure to reference to this conversation.

best regards,

Lars

[markus_doehr2 · Accepted solution]

> No - don't get that wrong.

> You've no tables created after that date that miss their file directory statistics.

Ah - ok - yes, didn't read the join completely... (my bad).

> Hmm... why not?

TCO?

> Since the statistics never need to be new but just accurate this can be OK of course.

How can you know aside from waiting a user to complain? How to check that accuracy aside from getting complaints from users? The check jobs only check for a different number of records since the last run, there is no method which I know to find out, if the statistics really reflect the actual situation or not...

> If one looks at statistics as a model about data distribution in the database than even old stats may lead to reasonable results. If I remember correctly we've even a consulting note for Oracle proposing that approach..

Yes - true - but if I need to do an update statistics on a weekend on a big table in the ERP just because it takes hours to complete and producing a shared lock then it's not very convenient. Given the alleged TCO reduction propagation and the comparison to other databases it's a bit antagonistic doing that as we did before. However, that problem is gone now (thankfully).

Markus

[lbreddemann · Accepted solution]

> > Hmm... why not?

>

> TCO?

Not so quick with the killer argument.

> > Since the statistics never need to be new but just accurate this can be OK of course.

>

> How can you know aside from waiting a user to complain? How to check that accuracy aside from getting complaints from users? The check jobs only check for a different number of records since the last run, there is no method which I know to find out, if the statistics really reflect the actual situation or not...

You're right - you cannot.

Of course the best thing would be to have a frictionless permanent adaption of the optimizer information, so that it is always best informed.

Anyhow - for some rather static SAP application, say ERP, it is possible to stay with fixed statistics.

These will lead to the same access plans everytime - so when the execution plans were OK when the statistics were created and the data developed in a linear way (more of the same), then the plans will still be OK after some time.

> Yes - true - but if I need to do an update statistics on a weekend on a big table in the ERP just because it takes hours to complete and producing a shared lock then it's not very convenient. Given the alleged TCO reduction propagation and the comparison to other databases it's a bit antagonistic doing that as we did before. However, that problem is gone now (thankfully).

You're right again. Anyhow, the TCO reduction is surely not based on one missing/present feature.

I think, you know better than many, that MaxDB is really growing further into the kind of database usage required today (and it has been in the recent years).

Only a few years ago, databases in the TBs where really huge - now it's day to day work.

And of course, there will always be the point, where the easy one-size-fits-all approach of MaxDB won't fit perfectly.

That's when one may think about the much more complex systems, that need more attention but also provide something for it.

To my point of view, using MaxDB primarily saves money due to its "just-running" behavior in most setups and rather not due to some prime features others cannot offer.

But, ok, that's just my opinion and surely not any kind of official statement (disclaimer off).

regards,

Lars

[markus_doehr2 · Accepted solution]

> > > Hmm... why not?

> >

> > TCO?

>

> Not so quick with the killer argument.

) (see below)

<...>

> Anyhow - for some rather static SAP application, say ERP, it is possible to stay with fixed statistics.

> These will lead to the same access plans everytime - so when the execution plans were OK when the statistics were created and the data developed in a linear way (more of the same), then the plans will still be OK after some time.

Sure - yes. And it worked for quite some time as I said.

> I think, you know better than many, that MaxDB is really growing further into the kind of database usage required today (and it has been in the recent years).

I agree.

> To my point of view, using MaxDB primarily saves money due to its "just-running" behavior in most setups and rather not due to some prime features others cannot offer.

Don't get me wrong - I don't blame MaxDB development, in contrary. I always get primary support, I often have the possibility of testing not-yet-released builds/features which I highly appreciate.

I am really happy I don't have to install and maintain some dozen Oracle installation (in sense of installed binaries) with all the patching and parametrization for really small databases as e. g. in a J2EE instance, I'm really happy about that and yes, for those cases the database just runs and runs and runs and all you need to do is to check that there's enough space and do regular backups, FULL ACK.

> You're right again. Anyhow, the TCO reduction is surely not based on one missing/present feature.

Due to the nature of "SAP contracts" and the fact, that they are bound solely to a database you choose, which means: "one for all" it's not good to generically advertise the TCO thing because customers simply have no choice.

If one chooses MaxDB then you choose it for all your instances because the licenses and maintenance you pay rely on that. That means consequently that it's also used for a BI system or for a (bigger) ERP or CRM or whatever you use. And then it's certainly not something that runs yourself and where you need to invest time for optimizing/checking as you would do for all other databases - and at some points even more.

This is all not a problem for me/us, those facts, I just have a very bad feeling when a "TCO" reduction is advertised generally or comparisons of databases A, B, C and MaxDB are shown, if there are immediately at least five not-so-unusual cases coming to my mind, where things are not "TCO"-friendly or other things are even next to impossible (compared to other DBMS'ses for certain scenarios). If that is done like this, one has to allow me to jump on that wagon and bring the killer argument

> But, ok, that's just my opinion and surely not any kind of official statement (disclaimer off).

Yes - I know - and I wouldn't take it as such.

Markus

[lbreddemann · Accepted solution]

> This is all not a problem for me/us, those facts, I just have a very bad feeling when a "TCO" reduction is advertised generally or comparisons of databases A, B, C and MaxDB are shown, if there are immediately at least five not-so-unusual cases coming to my mind, where things are not "TCO"-friendly or other things are even next to impossible (compared to other DBMS'ses for certain scenarios). If that is done like this, one has to allow me to jump on that wagon and bring the killer argument

Yes, I know this slide of course. I don't like it either.

It's marketing blah-blah without real substance to measure and compare.

Sometimes I'm just happy not to be on the sales-side of life...

regards,

Lars