DBA > Job Interview Questions > Sybase Interview Questions and Answers

How can I improve throughput in Sybase?

More DBA job interview questions and answers at http://dba.fyicenter.com/Interview-Questions/

(Continued from previous question...)

How can I improve throughput in Sybase?

Check the Obvious

First, ensure that you are only replicating those parts of the system that need to be replicated.  Some of this is obvious.  Don't replicate any table that does not need to be replicated.  Check that you are only replicating the columns you need.  Replication is very sophisticated and will allow you to replicate both a subset of the columns as well as a subset of the rows.

Replicate Minimum Columns

Once the replication is set up and synchronised, it is only necessary to replicate those parts of the primary system that actually change.  You are only replicating those rows and columns that need to be replicated, but you only need to replicate the actual changes.  Check that each replication definition is defined using the clause:

create replication definition rep_def_name
with primary...
replicate minimal columns

Second Replication Server

This might be appropriate in a simple environment on systems with spare cycles and limited space on the network.  When Sybase replicates from a primary to a replicate using only one replication server the data is transferred across the network uncompressed.  However, the communication between two replication servers is compressed.  By installing a second replication server it is possible to dramatically reduce the bandwidth needed to replicate your data.

Dedicated Network Card

Obviously, if replication is sharing the same network resources that all of the clients are using, there is the possibility for a bottleneck if the network bandwidth is close to saturation.  If a second replication server is not going to cut it since you already have one or there are no spare cycles, then a second network card may be the answer.

First, you will need to configure ASE to listen on two network connections.  This is relatively straightforward.  There is no change to the client configuration.  They all continue to talk to Sybase using the same connection.  When defining the replication server, ensure that the interfaces/sql.ini entry that it uses only has the second connection in it.  This may involve some jiggery pokery with environment variables, but should be possible, even on NT!  You need to be a little careful with network configuration.  Sybase will communicate with the two servers on the correct address, but if the underlying operating system believes that both clients and repserver can be serviced by the same card, then it will use the first card that it comes to.  So, if you had the situation that all of the clients, ASE and the replication server were on, and the host running ASE had two cards onto this same segment, then it would choose to route all packets through the first card.  OK, so this is a very simplistic error to correct, but similar things can happen with more convoluted and, superficially, better thought out configurations. 

+---------+                                +-----------+   +-----------+
|         |--> NE(1) --> All Clients...    |           |   |           |
| Primary |                                | repserver |   | replicate |
|         |--> NE(2) --------------------->|           |-->|           |
|         |                                |           |   |           |
+---------+                                +-----------+   +-----------+

So, configure NE(1) to be on, say, and NE(2) to be on and all should be well.  OK, so my character art is not perfect, but I think that you get the gist!

No Network Card

If RepServer resides on the same physical machine as either the primary or the replicate, it is possible to use the localhost or loopback network device.  The loopback device is a network interface that connects back to itself without going through the network interface card.  It is almost always uses the IP address  So, by applying the technique described above, but instead of using a dedicated network card, you use the loopback device.  Obviously, the two servers have to be on the same physical machine or it won't work! 

(Continued on next question...)

Other Job Interview Questions