CoolThreads Try and Buy Application Resources

Easy Tuning for Sybase ASE on the Sun Fire T2000 Server

A recent exercise of running a lightweight OLTP workload using Sybase ASE on the SF T2000 has shown that a few simple up front tunings and setup can provide a potential for a very large gain in performance. Though not "out-of-the-box", these tunings do not require deep or extensive setup of the system CPUs.

Configure ASE to run 24 engines

The T1 processor (1.2 GHz, 8 core, 32 strand) in concert with Solaris 10 runs OLTP workloads best when all the strands are active and most importantly not blocking each other. The ASE 'engine', though it is a multi-thread process appears to run well where there is an engine per strand - or nearly an engine per strand. The recommendation here is that 24 engines be run, leaving 6 engines to the system.

In the Sybase configuration file the following will direct ASE to startup 24 engine processes (that are multi-thread).

     [Processors]
           max online engines = 24
           number of engines at startup = 24
           statement cache size = DEFAULT

Locking Scheme - try row level locking.

Depending on the workload the locking scheme for shared tables and other objects can have a large effect on performance. For example, unless specified, Sybase ASE will typically default to shared or exclusive table or page locking. In either case (shared or exclusive, table or page) the locking used needs to be examined closely on the T1 as a potential bottleneck. This is a large topic and is dependent on the SQL application logic, workload etc. but what is recommended here is that row-level locking at least be tried.

To determine what kind of locking is being used you can issue the following command during live execution:

    $ isql -Usa -P
     1> use igen
     2> go
     3> sp_lock

If you don't see "Ex_row" then you could be able to benefit by building or altering your tables to use row-level locking.

Sybase ASE allows the DBA to either create a table with row locking or to alter a tables locking scheme after it has been built and loaded. An example of directing ASE to use row level locking on the 'customer' table from the interactive sql utility:

    $ isql -Usa -P
     1> use igen
     2> go
     3> alter table customer lock datarows
     4> go
     Non-clustered index (index id = 2) is being rebuilt.
     1>

Note that the associated index for the table is also adjusted.

The other approach is to actually create the table with row level locking - the "lock datarow" directive was added to the DDL create table script.

     if exists ( select name from sysobjects where name = 'customer' )
         drop table customer
     go
     create table customer (
        custid           numeric (10) not null,
        name             char (30) not null,
        address          char (30) not null,
        zipcode          numeric (5) not null,
        industry_id      numeric (2) not null,
        total_sales      numeric (12,2) not null,
        comments          char (256) not null,
        products         char (679) not null
     ) lock datarows
     on SDATA01
     go