SAP Sybase ASE 15.7 tempdb speed with directio on, off and raw devices




Speed test: Differences in speed for different set up of tempdb


Three temporary databases were set up with varying settings for direct io and dsync.

Each temporary database used the same disk separate from the master database

A basic insert test with two SQL statements was performed on each database.

device setting             Time Taken
--------------------------------------
directio off, dsync off 5 seconds
directio on, dsync off 6 mins 14 secs
directio off, dsync on 5 mins 59 secs


Conclusion of over 60 x slower with direct io or dsync set to on


Having either directio on/true or dsync on/true resulted in a degradation of about 60 times for the insert test compared to the database without the settings.

For this test the main tempdb was also set up with the majority of space without directio/dsync.
Although there was 6mb left on the master device with all original segments, it was only about 1 second slower than the user temporary database without the master device segments.

A further test with another tempdb database with raw devices was shown to take 8 seconds, slower than the "directio off, dsync off" temporary database.


Disclaimer

No liability can be accepted for any loss due to any information found on this page.





Push Symbol. Push Symbol. Door logos



Alastair Cook email:
cookalastair @ btinternet.com

Page created - 15th Nov 2016


Push Pull Door