Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

direct i/o on tempdb devices - which is correct ?

7 posts in General Discussion Last posting was on 2010-08-30 00:12:55.0Z
Murali Posted on 2010-08-22 01:29:37.0Z
Sender: 39a2.4c707ba4.1804289383@sybase.com
From: Murali
Newsgroups: sybase.public.ase.general
Subject: direct i/o on tempdb devices - which is correct ?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c707d81.39ce.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 21 Aug 2010 18:29:37 -0700
X-Trace: forums-1-dub 1282440577 10.22.241.41 (21 Aug 2010 18:29:37 -0700)
X-Original-Trace: 21 Aug 2010 18:29:37 -0700, 10.22.241.41
Lines: 29
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29489
Article PK: 78718

Hello,

We use ASE 15.03 ESD#3 on AIX 5.3 using filesystem (FS) for
tempdb devices.

disk init, by default, uses direct I/O, for the tempdb
devices. ( confirmed FS is NOT mounted with direct I/O )

However, manual says:

" Devices used for databases for which recovery is not
important (for example, tempdb), may have dsync set to
“false.” For these devices, enabling directio may have
an adverse performance effect, so you should carefully
review device use before you enable directio "

Ref:http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc31654.1502/html/sag1/sag1391.htm

If directio may have adverse effects, why disk init uses it
by default for tempdb devices ?

If indeed directio improves performance, why manuals say
otherwise ?

Though its simple to use sp_deviceattr to turn off
directio,am trying to understand which is the right thing to
do ?

Thanks


"Mark A. Parsons" <iron_horse Posted on 2010-08-22 12:26:44.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: direct i/o on tempdb devices - which is correct ?
References: <4c707d81.39ce.1681692777@sybase.com>
In-Reply-To: <4c707d81.39ce.1681692777@sybase.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c711784$1@forums-1-dub>
Date: 22 Aug 2010 05:26:44 -0700
X-Trace: forums-1-dub 1282480004 10.22.241.152 (22 Aug 2010 05:26:44 -0700)
X-Original-Trace: 22 Aug 2010 05:26:44 -0700, vip152.sybase.com
Lines: 38
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29490
Article PK: 78723

There's nothing in the 'disk init' command that allows the dataserver to ascertain how the device will be used (data?
log? tempdb? something else? a mix of these?).

It's up to the DBA to determine which io attribute (dsync, directio) to enable/disable. This can be done with the 'disk
init' command or, as you've noted, with the sp_deviceattr stored proc.

Murali wrote:
> Hello,
>
> We use ASE 15.03 ESD#3 on AIX 5.3 using filesystem (FS) for
> tempdb devices.
>
> disk init, by default, uses direct I/O, for the tempdb
> devices. ( confirmed FS is NOT mounted with direct I/O )
>
> However, manual says:
>
> " Devices used for databases for which recovery is not
> important (for example, tempdb), may have dsync set to
> “false.” For these devices, enabling directio may have
> an adverse performance effect, so you should carefully
> review device use before you enable directio "
>
> Ref:http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc31654.1502/html/sag1/sag1391.htm
>
> If directio may have adverse effects, why disk init uses it
> by default for tempdb devices ?
>
> If indeed directio improves performance, why manuals say
> otherwise ?
>
> Though its simple to use sp_deviceattr to turn off
> directio,am trying to understand which is the right thing to
> do ?
>
> Thanks


"Mark A. Parsons" <iron_horse Posted on 2010-08-22 13:58:35.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: direct i/o on tempdb devices - which is correct ?
References: <4c707d81.39ce.1681692777@sybase.com> <4c711784$1@forums-1-dub>
In-Reply-To: <4c711784$1@forums-1-dub>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c712d0b$1@forums-1-dub>
Date: 22 Aug 2010 06:58:35 -0700
X-Trace: forums-1-dub 1282485515 10.22.241.152 (22 Aug 2010 06:58:35 -0700)
X-Original-Trace: 22 Aug 2010 06:58:35 -0700, vip152.sybase.com
Lines: 53
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29491
Article PK: 78719


>> " Devices used for databases for which recovery is not
>> important (for example, tempdb), may have dsync set to
>> “false.” For these devices, enabling directio may have
>> an adverse performance effect, so you should carefully
>> review device use before you enable directio "

... snip ...

>> If indeed directio improves performance, why manuals say
>> otherwise ?

The use of dsync/directio (typically) means the dataserver has to wait for a write to complete on the physical disk
before the dataserver considers the write to be 'successful' (aka guaranteed write). While this *wait* will extend the
time it takes to complete the associated database action, the guaranteed write to disk is required to insure
recoverability (in the case of a dataserver/machine/disk subsystem failure).

If dsync/directio are disabled then a dataserver write may finish more quickly due to the disk subsystem *caching* the
write IO. This means the associated dataserver action completes more quickly but there's no guarantee the IO made it to
the physical disk. If the dataserver/machine/disk subsystem fails between the successful disk cache write and the
physical disk write, you could end up losing data (ie, the dataserver thinks the data is on disk while the disk has no
record of the data existing).

For important databases (eg, master, RSSDs, user databases), especially in production environments, the recoverability
of the data usually takes precedence over speed. In these scenarios the dataserver must wait for physical disk writes
to complete 'successfully', with the caveat that the associated database action takes longer to complete.

For trivial/development databases where recoverability is not a concern, or for databases that are rebuilt from scratch
at dataserver startup (eg, temporary databases), guaranteed disk writes are not as important. In these scenarios a
successful write to disk cache is sufficient, with the added benefit that the associated database action completes more
quickly.

So, generally speaking:

dsync/directio enabled : guaranteed disk writes, guaranteed recoverability, associated database actions take more time
to complete

dsync/directio disabled : no guarantee of writes to physical disks, no guarantee of recoverability, associated database
actions take less time to complete

------------------

Now-a-days there are lots of ways to configure disk subsystems ... raw disks, cached disks, cached file systems,
journaled file systems, multiple layers of logical disk management, sans (w/ and w/out cache), etc, etc, etc ...

Some of these configurations may support dsync/directio operations by the dataserver while actually performing the write
to cache, ie, the physical disk write takes place at a later time. For some systems this may be ok if the disk
subsystem vendor can guarantee that those cache writes will always make their way to disk.

The issue here is that regardless of which attribute settings (dsync/directio) are used for dataserver devices, it's the
DBA's responsibility to insure the disk subsystem can really guarantee disk writes for those database actions that
require guaranteed recoverability.