Tuning database to shorten online backup of BI

D

dlangschied

Member
The time necessary to backup the BI file on our database has crept out and needs to be reigned in. I am trying to decide the best approach. I can bring down the database and truncate the BI, but this is something that I can only do once a quarter. This may be enough, but I tought I would through the sit out there and get some fresh ideas.

Progress 10.1CSP4 on RHEL3.9 (upgrading some day to 5.5).

Database size 34Gb. BI Area 8 Gb. High water 6.5Gb during month end

Startups:
-B 160000
-L 120000
-Mf 10
-bibufs 135
-aibufs 135
-spin 3000
-S prod_01
-N TCP
-bistall
-bithold 7700
-aistall
-n 200
 
Wow. 8 GB seems like a very large BI file. How many users do you have connected day to day?

Regarding the backup performance, is it possible to increase the size of your buffer pool? Are you on 64-bit OpenEdge?

Also, on an unrelated note, 120,000 is a lot of lock table entries. Can you justify that? What is your lock table high-water mark? If you have programs taking that many locks then development should be looking at them. That affects performance and concurrency.

Also unrelated, if you are going to upgrade Red Hat "some day", why not upgrade to something current?
 
We are running QAD eB2.1(SP4) and, unfortunately, we are using GL Report Writer. I do not know if you are familiar with it, but it "SUCKS". It locks a lot of records, and causes a large BI growth. I would love to somehow get the accounting people here to accept alternatives, but so far it has been a bust. It probably could have some bad reports in it that are causing all of this, but I have no idea how to fix. This is a very sore spot for me as it also stores its reports as an image. There is a check box not to store it, but even then it creates the image and then deletes it. Argh! 40% of my database is filled with this crud! QAD has discontinued support, but that only means they wont' help us anymore to fix problems.
 
Are you on 64-bit Progress?

How much RAM do you have?

How much RAM can you throw at this DB? In other words, is this box exclusively for the one DB?

What is your DB block size?
 
BI file growth is caused by a transaction holding open the first BI cluster, so the database doesn't reuse existing BI clusters, it allocates new BI clusters. There isn't a database parameter for that. You need to monitor for long running transactions or for code that produces very large transactions.

You have 10.1C so you can issue kill -s SIGUSR1 <pid> commands from Linux and get the ABL code stack of the processes that are running. That is very helpful trying to track down issues with code.
 
cj_brandt said:
You have 10.1C so you can issue kill -s SIGUSR1 <pid> commands from Linux and get the ABL code stack of the processes that are running. That is very helpful trying to track down issues with code.
Click to expand...

Provided the client(s) in question are running on the server, and you have sufficient permission. You can also do the equivalent on a Windows client, with proGetStack.

Promon (or ProTop :)) will tell you which users have long-running transactions, and you can go from there. In the event that it's a remote client, in 10.1C or later you can also use the Client Request Statement caching feature in promon to see the user's call stack on demand. This should be done sparingly in production, as it does impact client network usage.
 
How long is too long?

You might want to try increasing the bi cluster size.

On the backup try adding -Bp 100. (Presumably you are doing an online probkup?)

Or... implement OE replication and backup the replication target.

Since QAD is no longer supporting you you should feel free to:

1) Upgrade to OpenEdge 10.2B06 or better.
2) Upgrade RH to something current.
3) Completely scrap the standard QAD storage area "design" for something sensible. Which might also have the benefit of improving performance -- which means that locks will be held for less time and bi growth might be significantly reduced.

Be bold. Break the mold. Reach for the Gold.
 
Tom,
The issue is that during the backup of the BI users are complaining that they get kicked off; they are complaining that they cannot get their shipments processed in a timely fashion, etc. I have told them that they just need to be aware that this is an iffy time period for processing. I do want to understand what can be done going forward. I viewed the last four years worth of database logs and found that the BI backup time has gotten progressively longer, from a few seconds to the current 4-5 minute time frame. I did notice a noticeable increase when the database was upgraded to Open Edge10.1C from 30 seconds to 90 seconds. I just don't want this to continue until it becomes a problem.

I did not speak clearly enough; QAD does not promote GL Report Writer with its current version (and for some time). We have support for the QAD product itself, and they will help us with issues, but the probability of getting them to dramatically change the way that the product works is unlikely. We could upgrade to OE based on needs like this, and I will consider this an option. This would occur if the solution lies within an upgrade, and the problem becomes bad enough to deem it worthy. Replication has been looked into in the past for fail-over purposes and was shot down. I do not think that this is a powerful enough argument to go to Replication now. The companies long-term plan is to go to SAP in 2015 and there will be little done to upgrade anything. However, users not being able to ship product is a priority no matter what, so anything is possible; just not probable.

Any dramatic change in storage design would not be ideal. I believe the storage space to not be the issue, but the coding. Plus, the entire GL Report Writer table and index sets are in their own storage area already.

I am interested in changing the cluster size and would welcome suggestions for correct sizing to achieve the desired results with acceptable OS and hardware impact. I am unfamiliar with the -Bp 100 parameter, but I will look that up to see its impact and use.


Thanks for your input!
David
 
What is your current BI block size and cluster size?

What is the reason for -Mf 10?

I don't see why you think a change in storage area design would not be "ideal", if it is a change for the better. And changing it isn't necessarily about optimizing storage space, but rather improving application performance and potentially reducing I/O. Is all of your data in Type II areas, and none in the Schema Area? Are you hesitant to do this because you can't justify the downtime for a dump and load? When is the last time a dump and load was done on this database?

Have you run a dbanalys or idxanalys lately to see the state of your indexes? Maybe they would benefit from a rebuild or compact.

Since you're on 10.1C, if I were you I would count the tables and indexes in the database and then add the -tablerangesize and -indexrangesize startup parameters with the appropriate values (remember to add 7 to the index count, as indexes 1-7 are for system tables). Let me know if you need guidance on that. Then you can collect CRUD stats on your tables and indexes to get an idea of application "hot spots". This can (a) aid storage area redesign and (b) help you push back to the vendor if you find that anything is really out of whack, e.g. if you have index bracketing issues that result in full table scans on large tables.

Your application doesn't know or care which storage area a storage object is in, but your user knows whether their application runs fast or slowly. I think you should reconsider a storage area evaluation. Or just break down and hire a consultant to take a detailed look at your systems, databases, network, and applications, and give you other options.
 
You must log in or register to reply here.
Back
Top