ASE1570 VS ASE1254

[Former Member]

I have a simple test about insert speed between ASE1570 and ASE1254.

Any idea to improve the throughput about ASE1570.

Testing Env:

ASE1570 SP130

ASE1254 ESD11.2

running on the same box.

Here's the testing steps:

use tempdb

go

create table MM (

MM01   int                              not null  ,

MM02  numeric(8,0)                     identity  ,

MM03  char(1)                          not null  ,

MM04  char(8)                              null  ,

MM05  datetime                         not null  ,

MM06  char(7)                          not null  ,

MM07  char(5)                          not null  ,

MM08  char(1)                          not null  ,

MM09  char(7)                          not null  ,

MM10  char(7)                          not null  ,

MM11  char(1)                          not null  ,

MM12  char(20)                         not null  ,

MM13  char(10)                             null  ,

MM14  char(10)                             null  ,

MM15  char(1)                          not null  ,

MM16  char(1)                              null  ,

MM17  char(1)                              null  ,

MM18  char(1)                          not null  ,

MM19  char(4)                          not null  ,

MM20  char(1)                              null  ,

MM21  char(3)                              null  ,

MM22  char(1)                              null  ,

MM23  smallmoney                       not null  ,

MM24  smallmoney                           null  ,

MM25  smallmoney                           null  ,

MM26  smallint                         not null  ,

MM27  smallint                             null  ,

MM28  smallint                             null  ,

MM29  int                                  null  ,

MM30  int                                  null  ,

MM31  smallint                             null  ,

MM32  char(1)                              null  ,

MM33  char(1)                              null  ,

MM34  char(1)                              null  ,

MM35  char(1)                              null  ,

MM36  char(1)                              null  ,

MM37  char(7)                          not null  ,

MM38  char(1)                          not null  ,

MM39  char(6)                          not null  ,

MM40  char(5)                          not null  ,

MM41  char(1)                          not null  ,

MM42  char(7)                          not null  ,

MM43  char(1)                          not null  ,

MM44  char(6)                          not null  ,

MM45  char(5)                          not null  ,

MM46  char(1)                              null  ,

CONSTRAINT PK_MMTSF PRIMARY KEY NONCLUSTERED ( MM01,MM02 ))

go

edit a sql file for example 1.sql:

use tempdb

go

truncate table MM

go

select getdate() as DT into #TT

go

INSERT INTO MM (MM01,MM03,MM04,MM05,MM06,MM07,MM08,MM09,MM10,MM11,MM12,MM13,MM14,MM15,MM16,MM17,MM18,MM19,MM20,MM21,MM22,MM23,MM24,MM25,MM26,MM27,MM28,MM29,MM30,MM31,MM32,MM33,MM34,MM35,MM36,MM37,MM38,MM39,MM40,MM41,MM42,MM43,MM44,MM45,MM46) VALUES

(1,'I','549     ','2015-01-01 10:08:17.306','A100000','ZZ001','O','A100000','0000333','1','TTO0100000          ','          ','          ','S',' ',' ','0','F905','S','000','L',100.0000,0.0000,0.0000,10,0,0,10,0,0,'O','O',' ','I',' ','TXO    ','C','201508','09000',' ','       ',' ','      ','     ','1')

go 5000

select datediff(ms,DT,getdate()) from #TT

go

and using isql to exec the 1.sql:

the result will look like:

ASE1570

/home/sybase/ase/phsu > isql -Usa -SSYBASE -Psybase -i1.sql

(1 row affected)

(1 row affected)

5000 xacts:

-----------

        6176

ASE1254

/home/sybase/ase/phsu > isql -Usa -SASE1254 -P -i1.sql

(1 row affected)

(1 row affected)

5000 xacts:

-----------

        3476

We can see ASE1254 is faster then ASE1570, any idea to improve ASE1570 throughput?

[former_member182259 · Accepted solution]

Actually, this most likely stems back to the 12.5 to 15.0 migration fun that you may not remember.   Remember, in 15.0, there was a huge change in the optimizer adding a lot of new capabilities - those new capabilities extended the code path, which made optimization longer.  You are issuing 5000 individual discrete insert statements - each of which has to be individually optimized before execution.   Cory gave a couple of applicable hints:

1 - enable the statement cache PLUS turn on literal autoparameterization.    This will cause static SQL to be more like the fully prepared statements it should have been in an OLTP app.

2 - while the data writes might not get staged to disk directly, with 5000 individual statements, you are pummeling the transaction log.  A key difference (as others have alluded to) is *any* physical difference in HW, file system, OS configuration etc. dealing with device IO as related to the log device.  A key metric to consider is #3 in Cory's email - specifically focusing on WaitEventID's 54 & 55 - which can be grabbed from both the 12.5 and 15.x servers by prefacing the query with 'select * from master..monProcessWaits where WaitTime>0 order by WaitEventID' - then run the SQL - then repeat the same query and delta the WaitEvents by ID to see the diffs

....all the other comments made by others also apply.

[Former Member]

Hi All,

Thanks for quick reply.

I had done lots of test, different platform(AIX,HPia64, SuSE), version(1254,1503,1570), optgoals(oltp,dss,mix), delay commit(true,false),saync log(true, false),statement cache(20480,0),literal auto(0,1),dsync(on ,off),direct io(on off)

If keep all default seting, we said go 5000 times exec 20 sec.

delay commit true: 10 sec

dsync off, direct io off: 10 sec

statement cache 20480 + literal 1: 10 sec

delay commit true + statement cache 20480 + literal 1: 5 sec

seems found the best case.

BUT:

SAPNOTES: 1955065, 1967964,1983968,2020543,2077161,2092670,2149164,2184618 make me step back.

I use the same testing steps and change the db from ASE to Oracle and SQL.

O and S seems better then ASE >"<

[jayrijnbergen]

If you want to compare performance of different RDBMS systems, you better look at industry standard RDBMS benchmarks. These will give a better indication of what you can expect.

IMHO One process doing 5000 times a single insert is not really a good indication for choosing Sybase, Oracle or MSSQL (or any other RDBMS).

If you want to be sure where your applications perform best, you'll have to run tests on all platforms. But usually an application is optimized for a specific RDBMS. So not so easy to move (unless you want to move to ASE and use Exodus )

[Former Member]

> I use the same testing steps and change the db from ASE to Oracle and SQL.

> O and S seems better then ASE

I can't comment on Oracle but I'm looking at MS-SQL and in my experience (albeit limited) MS-SQL is better "out of the box". Sybase with some effort seems to perform better after tuning. That may be down to I don't

yet as much experience with MS-SQL as Sybase or could be many other reasons.

However, the difference in performance (again with my limited experience of one app) isn't large enough to really feature in the choice of one app or another for us. We can just buy a slightly bigger machine.

Better to look at what features you really want out of a RDBMS.

[jayrijnbergen]

I guess your application is more then just insert statements

It might be quite useful to run some tests on 15.7 with different optimization goals.

If your app is mostly oltp processing, then set it to allrows_oltp.

[Former Member]

Christopher,

The changes between those versions are too numerous to list.

You would need to post your configuration for people to review and you need to understand what is happening in your own system.

Here are some ideas...

1. ASE 15.x has a different packet size by default from versions prior to ASE 15.0.

Try your tests with equal packet sizes of 512 bytes.

isql [your normal stuff here]  -A512

2. turn on statement cache -- sp_configure "statement cache"

3. collect information from monProcessWaits to see the changes in the wait events.

     select * from monProcessWaits where SPID=@@SPID

[Former Member]

Some obvious questions :

1) Are the specs of hardware of the 2 servers exactly the same ?

2) What type of disk do you have on each ? Is it SAN or Direct disk ?

3) Are the caches and other configs the same ?

4) Is there anything else running on the machines ?

5) Is the Sybase 15.7 server in thread mode or process mode ? (We've used both and process mode was better "out of the box" and thread mode was better after tuning).

6) Are you using the same page size ?

7) Was the lock scheme for both tables allpages ?

I haven't compared 12.5 on the same hardware as 15.0 and 15.7 but its feels as though 15.7 is faster than 15.0.

I'd expect these to perform at a similar speed. Can tyou run it with sysmon and set statistics io,time on as well.

BTW - Personally I'd upgrade to a later version of15.7 SP 134 (we didn't find SP130 very stable but obviously each persons experience may vary)

[c_baker]

I agree with the 'threaded mode' vs. 'process mode' question.  Are you shutting down the other server when performing the test?  How many cores, engines in 12.5 and threads in syb_default_pool in 15.7.

Also, you are using tempdb.  What type of device are you using for tempdb and how is caching set up.

Even in 15.7, one of the simplest things to remember is that, unless you select to tune it during configuration, the 'Default Data Cache' is still only set to 8MB.  What are your caches set to?

Again, too many variables to discuss effectively.

Chris