A few questions

[Gonzo]

Hi, i just finished a Progress DBA course last week and now ive come back to work and im looking at our database and im shocked at how poorly it was setup for us.

our DB has been running very poorly for a while now and i would like to make some changes to speed it up but before i do i need to ask a few things so i dont go and screw anything up.

1. i want to do analyze the tables with tabanals (or what ever the command is) but im worried that itll slow the DB down and not sure how long it will take, our database is 15gb and its constantly in use for printing invoices and entering sales so i cant do it in down time. just wondering how long it would take to do this and will it impact the users and if so by how much?

2. For some reason all the data in within our DB is in the schema area and the data area is empty rofl, could this impact the DB performance? in my book from my course it says "Don't store application data in the schema area" and i assume thats for good reason, if i move these tables from the schema to a new storage area will it help reporting speed?

3. This maybe hard to answer but ill try anyway, some of the reports we run can take up to 20 minutes to create and our invoices can take up to 15 minutes to generate, to me this seems pathetic, what could i do to reduce this time? ive noticed we dont have a BIW running and our buffer size is at the default 20000 which i plan to increase to 200000 since we have 4gb of RAM on the server and over 2gb free. im hoping these will help alot but im looking to get maximum reporting speed.

Well that will do for now, Thanks in advance for any replies.

 

[mongoljustin]

Q[1] tabanaly is a heavish job, but there is no way to accurately measure its system load or runtime, as many factors will influence that result.
* level of scatter on table data
* number of record continuations in table data
* DB size
* data weight in DB
* Index's
* disk speeds
* memory
* cpu allocation and associated hardware Bus speeds
* size of DB
* record sizes
* progress 10.x or 9.x

the best way is to run it in a "down time" and do the measurements. that way you have baseline going forward.

15 gb is not a big db on decent hardware ....

Q[2]

yep. once you have your tab analysis , you can decide on where you can best allocate your individual table and indexes to support them.
but dba 101 is get them out of the schema area.
your not currently using your DB correctly .....

Q[3]
performance is a multi angle problem, but certainly your low hanging fruit is to

* Tune -B as large as possible to get you hit buffer ratio to 99%
* turn on -spin (enterprise) if you have multi CPU
* use Type II clusters
* Seperarate large table to their own area
* set the clusre and block sizes in you BI file to larger values eg 16,32
* set cluster size in Type II to 512 for fast growing tables
* (enterprise) start bi writers, ai writers and APW to stop your application clients from having to do the checkpoints and flushes to disk from memory.
* seperate Bi, DB and AI onto seperate disks
* check the code to make sure no process is doing whole index scans.

in conclusion, you should be able to make you db processes go much faster ....

 

[marksward]

I'd suggest buying one of Dan Foremans datebase tuning books. They are good.

You need to write a benchmarking program (even if this is just a for each [whatever] no-lock). To work out you're baseline. Work with read only stuff first. Tweak one thing at a time. Create a test database so you can muck around in normal hours without bringing down the production db.

Starters:
-B as big as possible (avoiding memory going to page file though!)
start biw
start apw (s)

After that look at programs in the application (x-ref and as a starter search the x-ref for whole-index and sort-access). The biggest gains are usually in fixing thoughtless code.

Record you're results in a spreadsheet as you go along (its sadly exciting to see what you have achieved!)


Once you have got things as good as you can. Look at more involved changes (putting tables in specifice areas, type II storage etc).

Mark

 

[tamhas]

Well, the good news is that it probably won't be hard to make big improvements!

But, there is also a lot of work to get things fully modernized. Start with some baseline tests, make a change, and note the response. After you have made some progress, you might come back for some more specific advice, but it sounds like you know where to start.

You should always indicate Progress version since that will dictate what tools are available.

You might want to look at Tom Bascom's ProTop for looking at how you are doing on specific issues.

Consider restoring a backup to a development machine to play with. This would offload the the production system for things like the tabanalysis and would allow you to experiment with various scripts for moving tables into new areas and the like.

 

[Gonzo]

Thanks for the replies guys, i managed to do a tabanalys from the backup of our database and its shocking, our most commonly used tables have a scatter factor of 5-7 so i think im going to dump and load them into thier own storage areas.

problem i had on our test database though is when i delete the table from the data dictionary it crashes, is there a way to delete a table from command line?

also one other thing whats the command to move a table? i forgot what it was and i cant find it in my book anywhere.

Thanks.

EDIT: one other thing, i have a CD that i got from my DBA course which has a database on it called ATM and its used for benchmarking, now we used this DB for testing but it didnt work straight up due to directory problems, im wondering does anyone know aboutt his little DB from the CD and if so tell me where to put it?

inside the file there is a batch file used for creating the DB and it has this line in it

"set CURDIR=C:\PROGRESS\CLASS\ATM"

so im assuming thats where my files all need to be, problem is when i build the DB and it tries to start it tells me there is no server for it, any help on this would be great cause if i can get this to work itll help me optimize my work DB alot better and easier.

Thanks.