felicia@miller.cs.uwm.edu (05/20/91)
Hello all the DB gurus... there's a problem I thought many some helpful
people may care to listen to...
I am working on a project that is on a IBM fault-tolerant machine,
multi-processes (total 3, m68030 or 68020), and database very intensive
environment. DB in used intensively for both decision making, and
information processing. ( Disclaimer:: I did not design the
database,infact, it's not created with much of design in it. It just
happened! Usually the projects not like this, but this one is...)
DBMS we have is Sybase (whatever the lastest version is
w/Online-transcation Proc.) Currently, the system overall thru-put is
bottle-necked by Sybase Server; ie. the overrall system is slow-down
because applications have to wait for database informations.
This is what I know about the system performance:
CPU 1 - running all the applications
CPU 2 - delicately to Sybase db Server.
CPU 3 - other misc.
When system is busy, CPU1's usage = 50%, when CPU2 (server) is used up
100%. However, the applications are getting a lot of deadlocks' prob.,
and wait-state for server to return info. requested.
As I was told, the problem is even more obvious, when there are reports
generated at the same time.
The reports are BIG reports. They could consist of hundreds of pages
for one report. SO... the main completes (from my peers) are that we
should not be doing teh following things:
1.) The whole database application shold NOT be using Order-by,
and group-by, having caluses.
Their reasons: It takes too much CPU time.
My complain : These clauses are basically only used for reports.
If we don't use it for reports, and if there's not
way to index the table to obtain the result,
WHAT? sort by a C program? or using OS 'sort'
command? What do you think?
2.) To generate reports, we should not be using tables to keep the
data required by reports; ie. like temporary tables to store
report data from the databse.
Reasons: too many writes performed... cpu intensive...
My opinion: OK, perhaps we write it to external (flat) files,
and start another db Server on the thrid CPU, read
the files as external tables by 2nd server, then
process it for report generation.
Therefore, reports are only run on the second
server (3rd CPU).
Does that sound good to you? Can any suggest any
other solutions? And don't you think I should be
able to allow to use sort,group-by clauses this way?
Your reponses (opinions, suggesttions, comments, complains?) will be
read and very much appreciated, please just e-mail, since I maynot new
reads for a long time... Thanks.
- felicia C.
felicia@miller.cs.uwm.edu
felicia@cvax.cs.uwm.edu
(414) 332-7657 ..leave a message only if you have something useful
to say. Thanks.