Another Sybase Performance Problem 
Author Message
 Another Sybase Performance Problem

Actually it is not a problem yet, but i would appreciate your opinion
because
I am yet to attend Sybase DBA and Performance Tuning Training.

I am at the beginning of a large project of reengineering of a system
which processes real time measurements.
The system is receiving a packet of data approx. 100 bytes long
every 25 millisec. Each packet contain a number of readings. It has to
be
disassembled and individual readings to be compared with the previous
value. If it is within the aperture range it will be ignored. If not it
has to be
stored along with the time stamp. Then some of the measurements require
special processing (calibration, triggering alarms, etc.)
Current system stores everything in a set of flat files with the
hardcoded
processing. New requirements mandate extended and flexible processing
and
it has to be done within 1-1.5 sec. The biggest problem is "when it is
rain - it is
downpour," i.e. if something goes wrong than most of the measurements
fluctuate
like crazy.

My questions are:
1. Can Sybase handle such kind of a load?
    1.1 If yes, then what version do I need and what type of box would
be needed?
        (Solaris 2.x or HP-UX are preffered)
    1.2 If not, what DBMS engine would you recommend?
2. Because time constraints for storing measurements and processing them
can
differ in the order of magnitude, I was thinking to break it into two
atomic transactions.
At the same time I'd like to use triggers to fire off processing. Can
triggers span over
transaction boundaries and if yes, how to do it?

T hanks in advance.



Tue, 29 Aug 2000 03:00:00 GMT
 Another Sybase Performance Problem

Gary>
Gary> The system is receiving a packet of data approx. 100 bytes long
Gary> every 25 millisec.

or 40 per sec.

Gary> processing. New requirements mandate extended and flexible processing
Gary> and
Gary> it has to be done within 1-1.5 sec.

What has to be done within 1-1.5 seconds?  If we have an
input stream of 40/s and we have a 1-1.5 second requirement
per, we'll need to have multiple RDBMS.

Gary> The biggest problem is "when it is
Gary> rain - it is
Gary> downpour," i.e. if something goes wrong than most of the measurements
Gary> fluctuate
Gary> like crazy.

Can you definie "downpour"?  That is, what rate are we
talking about?

Gary> My questions are:
Gary> 1. Can Sybase handle such kind of a load?

It's hard to say...  you need to translate your application
into IOPS and see.  Also, I suspect that you might be
looking at a three tier architecture but this is simply a
suspicion...

Gary>     1.1 If yes, then what version do I need and what type of box would
Gary> be needed?
Gary>         (Solaris 2.x or HP-UX are preffered)

or SGI?  :-)

Gary> At the same time I'd like to use triggers to fire off
Gary> processing. Can triggers span over transaction
Gary> boundaries and if yes, how to do it?

No they cannot.
--
Pablo Sanchez              | Ph # (650) 933.3812          Fax # (650) 933.2821

-------------------------------------------------------------------------------
I am accountable for my actions.   http://reality.sgi.com/pablo [ /Sybase_FAQ ]



Tue, 29 Aug 2000 03:00:00 GMT
 Another Sybase Performance Problem

Gary,

While I can not comment on the performance issue out of direct experience I
have designed a number of accounting systems rountines which like your own
have to be completed within a set time frame.

For the speeds you require I do not suggest updating the RDBMS directly,
 regardless of whoose RDBMS you are using.

My suggection would be to build a global results table in an appropriate
memory structure. Run three threads in your application, one to read and
update the memory table , the second thread to store the table every 30
to 60 seconds and the third to publish the table to a server app on a
separate machine.

Thread 1 :  This is to stop the database thrashing.
             If you used something like Delphi the compiled code would run with
'plenty' of
                 spare 'time' when updating the memory structure.

Thread 2 :  Ensures you have a stored image of your data every X seconds.

Thread 3 :  Posting the table to a Server Application on another machine
allows a number of client terminals to
                be 'hooked up' without placing extra demands on the sybase
server.

I have started using TCP/IP component to give me the the ability to
broardcast to any number of client terminals.
These terminals only have to the able to support a  Twinsock stack ( No
browser required).  
Compiled programs to show the status of 50 devices would be about 500kb in
size. Thus even 386 machines
would be sufficient.

Maybe this starts some ideas.

Richard



Quote:
> Actually it is not a problem yet, but i would appreciate your opinion
> because
> I am yet to attend Sybase DBA and Performance Tuning Training.

> I am at the beginning of a large project of reengineering of a system
> which processes real time measurements.
> The system is receiving a packet of data approx. 100 bytes long
> every 25 millisec. Each packet contain a number of readings. It has to
> be
> disassembled and individual readings to be compared with the previous
> value. If it is within the aperture range it will be ignored. If not it
> has to be
> stored along with the time stamp. Then some of the measurements require
> special processing (calibration, triggering alarms, etc.)
> Current system stores everything in a set of flat files with the
> hardcoded
> processing. New requirements mandate extended and flexible processing
> and
> it has to be done within 1-1.5 sec. The biggest problem is "when it is
> rain - it is
> downpour," i.e. if something goes wrong than most of the measurements
> fluctuate
> like crazy.

> My questions are:
> 1. Can Sybase handle such kind of a load?
>     1.1 If yes, then what version do I need and what type of box would
> be needed?
>         (Solaris 2.x or HP-UX are preffered)
>     1.2 If not, what DBMS engine would you recommend?
> 2. Because time constraints for storing measurements and processing them
> can
> differ in the order of magnitude, I was thinking to break it into two
> atomic transactions.
> At the same time I'd like to use triggers to fire off processing. Can
> triggers span over
> transaction boundaries and if yes, how to do it?

> T hanks in advance.



Fri, 01 Sep 2000 03:00:00 GMT
 Another Sybase Performance Problem

Quote:


> Gary>
> Gary> The system is receiving a packet of data approx. 100 bytes long
> Gary> every 25 millisec.

> or 40 per sec.

That is right. You sure can divide.

Quote:

> Gary> processing. New requirements mandate extended and flexible processing
> Gary> and
> Gary> it has to be done within 1-1.5 sec.

> What has to be done within 1-1.5 seconds?  If we have an
> input stream of 40/s and we have a 1-1.5 second requirement
> per, we'll need to have multiple RDBMS.

Let me clarify.Data is really coming at 40 packets/sec rate.One packet contains
1-20 data items.
Not every item need to be processed and recorded. Only if the value of the
particular
data item differ from the previous recorded value by certain margin. These margins

depends on the absolute value and are also stored in the database in the sort of
semi-static
table. When application starts, it reads initial values and margins from the
database and starts
monitoring incoming stream. Usually one or two dataitems in say couple dozens of
packets
will require to be processed, i.e. calibrated, stored, etc. That is why there are
different timing
requirements.

Quote:
> Gary> The biggest problem is "when it is
> Gary> rain - it is
> Gary> downpour," i.e. if something goes wrong than most of the measurements
> Gary> fluctuate
> Gary> like crazy.

> Can you definie "downpour"?  That is, what rate are we
> talking about?

I meant that in the worst case all data items in every packet will require
processing, i.e. calibration,recording, special handling.

Quote:

> Gary> My questions are:
> Gary> 1. Can Sybase handle such kind of a load?

> It's hard to say...  you need to translate your application
> into IOPS and see.

How can you possibly do it while still in the middle of design stage?IOPS heavily
depend on architecture and strategy and that is what
I am trying to come up with.

Quote:
> Also, I suspect that you might be
> looking at a three tier architecture but this is simply a
> suspicion...

Can you elaborate on this, please? What do you have in mind?

Quote:
> Gary>     1.1 If yes, then what version do I need and what type of box would
> Gary> be needed?
> Gary>         (Solaris 2.x or HP-UX are preffered)

> or SGI?  :-)

This is unlikely.

Quote:

> Gary> At the same time I'd like to use triggers to fire off
> Gary> processing. Can triggers span over transaction
> Gary> boundaries and if yes, how to do it?

> No they cannot.

Thank you for one definite answer.
Quote:
> --
> Pablo Sanchez              | Ph # (650) 933.3812          Fax # (650) 933.2821

> -------------------------------------------------------------------------------
> I am accountable for my actions.   http://reality.sgi.com/pablo [ /Sybase_FAQ ]



Fri, 01 Sep 2000 03:00:00 GMT
 Another Sybase Performance Problem

Quote:

Gary>
>> or 40 per sec.

Gary>
Gary> That is right.

Cool.  Not a big rate... of course, I see below that this
isn't really the rate.  Rather, each packet may contain
upwards of 20 data items.  If all 40/s explode to 20 data
items:

        40 * 21 = 840/s

Now you're talking a good load.

Gary> You sure can divide.

No, not really.  I can type.  You can too.  Try bc(1).  Boy
this sounds like Pat the Bunny.

Gary> < stuff snipped >

Okay, what you need to do is size for the peak.  The peak,
is 40 packets/s where each packet may contain a max of 20
data items.

Several questions come to mind:

1) During a "downpour", what percent of the 40 packets/s
   will have 20 data items?

2) Can you translate how a packet will be stored in a
   database?  Specifically, how does a packet translate to a
   set of tables.  What are the rows and row sizes in said
   tables?

Quote:
>> It's hard to say...  you need to translate your application
>> into IOPS and see.

Gary>
Gary> How can you possibly do it while still in the middle
Gary> of design stage?

It depends where you are in the design stage.  Have you
finished the ER diagram?  If so, there's nothing to stop
you.  If you haven't, you're jumping the gun on worrying
about the design anyway.  

Gary> IOPS heavily depend on architecture
Gary> and strategy and that is what I am trying to come up
Gary> with.

Yes and no.  IOPS depend on access and data layout.  After
completing the ER diagram you'll have both.  As I said, if
you haven't completed your ER diagram, get busy.  :-)... if
you have, you're okay to proceed.  Of course you aren't
going to get it 100% correct.  You'll get close and with
that, you'll see if it can be done and what type of
architecture you'll need in order to support your
application.  You can get pretty close.  Especially if you
can answer "2)" because after that, it's pretty darn simple
to figure IOPS.  You know (or should know) how many IOPS a
disk drive can do, you know how many IOPS a controller can
do... you should know how many IOPS an RDBMS engine can do
(roughly) so it's pretty simple...

Gary> Can you elaborate on this, please? What do you have in mind?

A three tier architecture is something like follows:

[ client ] <--> [ tp monitor  ] <-->  [ server ]

The idea is that you have many clients mux'ing into the
transaction monitor (tp monitor) and the tp monitor feeds
a server.  You can have many servers.  The server can be
anything you program the tp monitor to link to:  Sybase,
Informix, Oracle, flat files, RMS...  The tp monitor can be
setup to do data sensitive routing so that you can have
multiple RDBMS's.  The tp monitor monitors queues for each
connection and you can add more queues for a given
"class"... take a look at BEA's Tuxedo.  

btw, when TPC-C's are done, they are typically done using a
three tier architecture.  That's how one can report 1000's
of connections per RDBMS.  Really it's 1000's of clients
mux'd via the tp monitor.

Quote:
>> or SGI?  :-)

Gary>
Gary> This is unlikely.

Oh well... it sounds like you might need the bandwidth...

Gary>
Gary> Thank you for one definite answer.
Gary>

No problem, provide relevant data and you'll get s'more.
Even if you decide to use HP I'll still respond.  :-)

Funny, why aren't you asking your HP SE's to answer these
questions?  Oh never mind...
--
Pablo Sanchez              | Ph # (650) 933.3812          Fax # (650) 933.2821

-------------------------------------------------------------------------------
I am accountable for my actions.   http://reality.sgi.com/pablo



Fri, 01 Sep 2000 03:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Sybase performance problem

2. MSQL and Sybase Performance Problem

3. Sybase performance problem?

4. Sybase Performance Problems

5. SQL Server Performance Problem - Good query performance, bad update performance

6. Performance problems with Delphi & Sybase System 11

7. Sybase and jConnect performance problem

8. Performance problems with Delphi & Sybase System 11

9. Performance Problem with Sybase on AIX

10. Pentium Pro Performance Problems with Sybase SQL server and Novell 4.11

11. Help with performance problems in an Access/Sybase SQL Anywhere data base in Christchurch, New Zealand


 
Powered by phpBB® Forum Software