SQLAnywhere 8.0.1 Optimization Question

This is a multi-part message in MIME format.

------=_NextPart_000_010B_01C27B53.F72FC440
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I was asked recently to recommend whether the log file for a database =
accessed by approx. 300 people should be stored on a separate network =
drive.  Right now the system accessing the data is stil in development, =
so we have not determined how the databasee will actually respond with =
300 users. =20

I have heard that there are pros and cons to keeping the log file apart =
from the db.  Could anyone please offer their suggestions?  Thanks in =
advance!

------=_NextPart_000_010B_01C27B53.F72FC440
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 5.50.4522.1800" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>I was asked recently to recommend =
whether the log=20
file for a database accessed by approx. 300 people should be stored on a =

separate network drive.&nbsp; Right now the system accessing the data is =
stil in=20
development, so we have not determined how the databasee will actually =
respond=20
with 300 users.&nbsp; </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I have heard that there are pros and =
cons to=20
keeping the log file apart from the db.&nbsp; Could anyone please offer =
their=20
suggestions?&nbsp; Thanks in advance!</FONT></DIV></BODY></HTML>

------=_NextPart_000_010B_01C27B53.F72FC440--

0
Jan
10/24/2002 3:53:27 PM
sybase.sqlanywhere.general 32637 articles. 22 followers. Follow

8 Replies
333 Views

Similar Articles

[PageSpeed] 20

The .DB and .LOG files should be located on drives that are locally
attached to the CPU on which the server is running. SAN drives are OK
but ordinary LAN-accessible network drives are *not* acceptable... LAN
I/O is not guaranteed.

Storing the .DB and .LOG files on separate physical drives gives some
performance improvement by forcing separate drive heads to be used;
the .DB  head moves randomly over the database file when checkpoints
occur and dirty pages are written, whereas the .LOG head moves
sequentially as log records are appended.

RAID drives throw all that into a cocked hat. AFAIK RAID 5 is not the
best for database performance, RAID 0/1 is better. With RAID you are
leaving head choice up to the system.

FYI .DB I/O does *not* necessarily occur when a commit is executed,
only when a checkpoint occurs. Checkpoints occur according to a magic
formula discussed in the Help. This is done for performance.

Breck



On Thu, 24 Oct 2002 11:53:27 -0400, "Jan K. van Dalen"
<vandalen@mindspring.com> wrote:

>I was asked recently to recommend whether the log file for a database accessed by approx. 300 people should be stored on a separate network drive.  Right now the system accessing the data is stil in development, so we have not determined how the databasee will actually respond with 300 users.  
>
>I have heard that there are pros and cons to keeping the log file apart from the db.  Could anyone please offer their suggestions?  Thanks in advance!

bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
-----
Visit http://www.risingroad.com for Tips and Techniques
[TeamSybase]
0
Breck
10/24/2002 4:09:38 PM
One clarification. While DB I/O is not tied to commits, Log file I/O is
synchronous with COMMIT statements.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
 choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

"Breck Carter" <NOSPAM__bcarter@risingroad.com> wrote in message
news:996gru0klp2bknfk06qn7if7hb56geq4aa@4ax.com...
> The .DB and .LOG files should be located on drives that are locally
> attached to the CPU on which the server is running. SAN drives are OK
> but ordinary LAN-accessible network drives are *not* acceptable... LAN
> I/O is not guaranteed.
>
> Storing the .DB and .LOG files on separate physical drives gives some
> performance improvement by forcing separate drive heads to be used;
> the .DB  head moves randomly over the database file when checkpoints
> occur and dirty pages are written, whereas the .LOG head moves
> sequentially as log records are appended.
>
> RAID drives throw all that into a cocked hat. AFAIK RAID 5 is not the
> best for database performance, RAID 0/1 is better. With RAID you are
> leaving head choice up to the system.
>
> FYI .DB I/O does *not* necessarily occur when a commit is executed,
> only when a checkpoint occurs. Checkpoints occur according to a magic
> formula discussed in the Help. This is done for performance.
>
> Breck
>
>
>
> On Thu, 24 Oct 2002 11:53:27 -0400, "Jan K. van Dalen"
> <vandalen@mindspring.com> wrote:
>
> >I was asked recently to recommend whether the log file for a database
accessed by approx. 300 people should be stored on a separate network drive.
Right now the system accessing the data is stil in development, so we have
not determined how the databasee will actually respond with 300 users.
> >
> >I have heard that there are pros and cons to keeping the log file apart
from the db.  Could anyone please offer their suggestions?  Thanks in
advance!
>
> bcarter@risingroad.com
> Mobile and Distributed Enterprise Database Applications
> -----
> Visit http://www.risingroad.com for Tips and Techniques
> [TeamSybase]


0
Robert
10/25/2002 3:18:40 PM
We'll put aside a discussion of COOPERATIVE_COMMITS and
DELAYED_COMMITS for the moment, because I can't find my Motrin :)

When you say "log file I/O is synchronous with COMMIT statements"
someone might get the impression that changes are not written to the
log until a transaction is committed. What actually happens (according
to the Help, anyway) is that the log contains all changes, by all
transactions whether committed or not, that were made before the most
recent COMMIT. 

So if I update a million rows, and you do a COMMIT, all *my* changes
are written to the log even though I could still do a ROLLBACK. So the
log can contain uncommitted changes. But log file I/O is indeed
synchronous with COMMITs.

That brings up the following (silly?) point... The Help also says that
the log file does not contain any uncommitted transactions made after
the most recent COMMIT. If that is true, then a database being heavily
updated with a long-running transaction in single-user mode will wait
for a long time before writing ANY data to the log file. Then, when
the COMMIT happens, the drive light comes on for a half-hour.

A multi-million-row MobiLink download falls into this category.
Perhaps performance could be improved by having a second connection
which does nothing except issue COMMIT statements every few seconds.
Then the log file I/O would be spread out over time.

Unless log file I/O is *not* synchronous with COMMITs at all. I'm not
sure it should be, performance-wise, but once again I am...

Breck Just Guessing :)

On Fri, 25 Oct 2002 11:18:40 -0400, "Robert Waywell"
<rwaywell@ianywhere.com> wrote:

>One clarification. While DB I/O is not tied to commits, Log file I/O is
>synchronous with COMMIT statements.

bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
-----
Visit http://www.risingroad.com for Tips and Techniques
[TeamSybase]
0
Breck
10/25/2002 8:24:12 PM
The server maintains a buffer (64K) for changes to be written to the
transaction log. As transactions perform updates, changes are appended to
this in-memory buffer. When the buffer fills up, its contents are flushed
out to the log file and the buffer accumulation renews afresh. When a
transaction commits, all pages in the buffer upto that point are written out
to disk. It is not necessary to defer writing out the log file changes until
transaction(s) commit. When a transaction does commit, however, it is
essential that all changes for at least that transaction be written to the
log file for the commit to be successful.

However, as Breck points out, changes for all transactions are interwoven
into the same log. It is, therefore, not true that the log file does not
*physically* contain uncommitted changes for a transaction -- it does. These
uncommitted changes are, however, not *logically* present because during
recovery any uncommitted changes in the log file are rolled back before
recovery completes.

Hope this helps (of course, if any of the above turns out to be true, I am
sure John will set me straight),

--
-anil
Query Processing, iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer


"Breck Carter" <NOSPAM__bcarter@risingroad.com> wrote in message
news:gg8jru4ndhl0bracbb50mm0coata10hf32@4ax.com...
> We'll put aside a discussion of COOPERATIVE_COMMITS and
> DELAYED_COMMITS for the moment, because I can't find my Motrin :)
>
> When you say "log file I/O is synchronous with COMMIT statements"
> someone might get the impression that changes are not written to the
> log until a transaction is committed. What actually happens (according
> to the Help, anyway) is that the log contains all changes, by all
> transactions whether committed or not, that were made before the most
> recent COMMIT.
>
> So if I update a million rows, and you do a COMMIT, all *my* changes
> are written to the log even though I could still do a ROLLBACK. So the
> log can contain uncommitted changes. But log file I/O is indeed
> synchronous with COMMITs.
>
> That brings up the following (silly?) point... The Help also says that
> the log file does not contain any uncommitted transactions made after
> the most recent COMMIT. If that is true, then a database being heavily
> updated with a long-running transaction in single-user mode will wait
> for a long time before writing ANY data to the log file. Then, when
> the COMMIT happens, the drive light comes on for a half-hour.
>
> A multi-million-row MobiLink download falls into this category.
> Perhaps performance could be improved by having a second connection
> which does nothing except issue COMMIT statements every few seconds.
> Then the log file I/O would be spread out over time.
>
> Unless log file I/O is *not* synchronous with COMMITs at all. I'm not
> sure it should be, performance-wise, but once again I am...
>
> Breck Just Guessing :)
>
> On Fri, 25 Oct 2002 11:18:40 -0400, "Robert Waywell"
> <rwaywell@ianywhere.com> wrote:
>
> >One clarification. While DB I/O is not tied to commits, Log file I/O is
> >synchronous with COMMIT statements.
>
> bcarter@risingroad.com
> Mobile and Distributed Enterprise Database Applications
> -----
> Visit http://www.risingroad.com for Tips and Techniques
> [TeamSybase]


0
anil
10/26/2002 3:19:38 AM
So log file I/O is very *loosely* coupled to COMMITs, and there is no
point in writing "silly.exe" to occasionally force a dummy commit...
log file output is forced every 64K... correct?

Perhaps the Help needs work...

=====
Adaptive Server Anywhere Database Administration Guide
  15. Database Administration Utilities
    The Log Translation utility
      Log translation utility options
Include uncommitted transactions (-a)     The transaction log contains
any changes made before the most recent COMMIT by any transaction.
Changes made after the most recent commit are not present in the
transaction log.
=====

....since the the log file *can* contain changes made after the most
recent commit if they were forced out when the buffer filled.

All of this makes sense, but it deserves a page of its own in the
Help.

Breck



On Fri, 25 Oct 2002 23:19:38 -0400, "anil k goel"
<anilgoel@ianywhere.com> wrote:

>The server maintains a buffer (64K) for changes to be written to the
>transaction log. As transactions perform updates, changes are appended to
>this in-memory buffer. When the buffer fills up, its contents are flushed
>out to the log file and the buffer accumulation renews afresh. When a
>transaction commits, all pages in the buffer upto that point are written out
>to disk. It is not necessary to defer writing out the log file changes until
>transaction(s) commit. When a transaction does commit, however, it is
>essential that all changes for at least that transaction be written to the
>log file for the commit to be successful.
>
>However, as Breck points out, changes for all transactions are interwoven
>into the same log. It is, therefore, not true that the log file does not
>*physically* contain uncommitted changes for a transaction -- it does. These
>uncommitted changes are, however, not *logically* present because during
>recovery any uncommitted changes in the log file are rolled back before
>recovery completes.
>
>Hope this helps (of course, if any of the above turns out to be true, I am
>sure John will set me straight),

bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
-----
Visit http://www.risingroad.com for Tips and Techniques
[TeamSybase]
0
Breck
10/26/2002 3:23:35 PM
Yes; correct. The only essential and *tight* coupling (maintaining the order
of changes in the log file, of course, is a given) between a commit and log
file I/O is that all changes for the committing transaction have be forced
out to the log file before the commit can be considered complete. It does
not matter when and how these changes are written out as long as they are
all done before commit is complete. To avoid having to incur a long wait
(the half hour disk illumination you talked about) at COMMIT time, the
server will not wait until commit time to force out all changes at once.

To reiterate, the log file will almost always contain uncommitted changes as
long as there are multiple concurrent update transactions in play --
whenever one transaction commits, it forces out any uncommitted changes for
all other transactions at that point of time. The buffer does not even have
to fill up for this flushing to happen. The wording for "dbtran -a" below
likely can use some improvements.

--
-anil
Query Processing, iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer


"Breck Carter" <NOSPAM__bcarter@risingroad.com> wrote in message
news:3fclru4cibhrnqmm2lp3do9jn0tl61brr6@4ax.com...
> So log file I/O is very *loosely* coupled to COMMITs, and there is no
> point in writing "silly.exe" to occasionally force a dummy commit...
> log file output is forced every 64K... correct?
>
> Perhaps the Help needs work...
>
> =====
> Adaptive Server Anywhere Database Administration Guide
>   15. Database Administration Utilities
>     The Log Translation utility
>       Log translation utility options
> Include uncommitted transactions (-a)     The transaction log contains
> any changes made before the most recent COMMIT by any transaction.
> Changes made after the most recent commit are not present in the
> transaction log.
> =====
>
> ...since the the log file *can* contain changes made after the most
> recent commit if they were forced out when the buffer filled.
>
> All of this makes sense, but it deserves a page of its own in the
> Help.
>
> Breck
>
>
>
> On Fri, 25 Oct 2002 23:19:38 -0400, "anil k goel"
> <anilgoel@ianywhere.com> wrote:
>
> >The server maintains a buffer (64K) for changes to be written to the
> >transaction log. As transactions perform updates, changes are appended to
> >this in-memory buffer. When the buffer fills up, its contents are flushed
> >out to the log file and the buffer accumulation renews afresh. When a
> >transaction commits, all pages in the buffer upto that point are written
out
> >to disk. It is not necessary to defer writing out the log file changes
until
> >transaction(s) commit. When a transaction does commit, however, it is
> >essential that all changes for at least that transaction be written to
the
> >log file for the commit to be successful.
> >
> >However, as Breck points out, changes for all transactions are interwoven
> >into the same log. It is, therefore, not true that the log file does not
> >*physically* contain uncommitted changes for a transaction -- it does.
These
> >uncommitted changes are, however, not *logically* present because during
> >recovery any uncommitted changes in the log file are rolled back before
> >recovery completes.
> >
> >Hope this helps (of course, if any of the above turns out to be true, I
am
> >sure John will set me straight),
>
> bcarter@risingroad.com
> Mobile and Distributed Enterprise Database Applications
> -----
> Visit http://www.risingroad.com for Tips and Techniques
> [TeamSybase]


0
anil
10/26/2002 4:13:51 PM
I should add that the discussion below talks about a "live" log file, i.e.,
one that is currently in use by a server. When the server shuts down, the
following additional points apply. If the server goes away abnormally, then
naturally the following discussion still applies. However, if the server
shuts down properly, then the transaction log only contains complete
transactions and that is probably what the help file is referring to when
talking about dbtran. In a "clean" transaction log, there are no
"uncommitted" changes -- all changes in the log belong to a transaction that
either committed or rolled back.

--
-anil
Query Processing, iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer


"anil k goel" <anilgoel@ianywhere.com> wrote in message
news:ofAvRzQfCHA.197@forums.sybase.com...
> Yes; correct. The only essential and *tight* coupling (maintaining the
order
> of changes in the log file, of course, is a given) between a commit and
log
> file I/O is that all changes for the committing transaction have be forced
> out to the log file before the commit can be considered complete. It does
> not matter when and how these changes are written out as long as they are
> all done before commit is complete. To avoid having to incur a long wait
> (the half hour disk illumination you talked about) at COMMIT time, the
> server will not wait until commit time to force out all changes at once.
>
> To reiterate, the log file will almost always contain uncommitted changes
as
> long as there are multiple concurrent update transactions in play --
> whenever one transaction commits, it forces out any uncommitted changes
for
> all other transactions at that point of time. The buffer does not even
have
> to fill up for this flushing to happen. The wording for "dbtran -a" below
> likely can use some improvements.
>
> --
> -anil
> Query Processing, iAnywhere Solutions Engineering
>
> Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
> Developer Community at http://www.ianywhere.com/developer
>
>
> "Breck Carter" <NOSPAM__bcarter@risingroad.com> wrote in message
> news:3fclru4cibhrnqmm2lp3do9jn0tl61brr6@4ax.com...
> > So log file I/O is very *loosely* coupled to COMMITs, and there is no
> > point in writing "silly.exe" to occasionally force a dummy commit...
> > log file output is forced every 64K... correct?
> >
> > Perhaps the Help needs work...
> >
> > =====
> > Adaptive Server Anywhere Database Administration Guide
> >   15. Database Administration Utilities
> >     The Log Translation utility
> >       Log translation utility options
> > Include uncommitted transactions (-a)     The transaction log contains
> > any changes made before the most recent COMMIT by any transaction.
> > Changes made after the most recent commit are not present in the
> > transaction log.
> > =====
> >
> > ...since the the log file *can* contain changes made after the most
> > recent commit if they were forced out when the buffer filled.
> >
> > All of this makes sense, but it deserves a page of its own in the
> > Help.
> >
> > Breck
> >
> >
> >
> > On Fri, 25 Oct 2002 23:19:38 -0400, "anil k goel"
> > <anilgoel@ianywhere.com> wrote:
> >
> > >The server maintains a buffer (64K) for changes to be written to the
> > >transaction log. As transactions perform updates, changes are appended
to
> > >this in-memory buffer. When the buffer fills up, its contents are
flushed
> > >out to the log file and the buffer accumulation renews afresh. When a
> > >transaction commits, all pages in the buffer upto that point are
written
> out
> > >to disk. It is not necessary to defer writing out the log file changes
> until
> > >transaction(s) commit. When a transaction does commit, however, it is
> > >essential that all changes for at least that transaction be written to
> the
> > >log file for the commit to be successful.
> > >
> > >However, as Breck points out, changes for all transactions are
interwoven
> > >into the same log. It is, therefore, not true that the log file does
not
> > >*physically* contain uncommitted changes for a transaction -- it does.
> These
> > >uncommitted changes are, however, not *logically* present because
during
> > >recovery any uncommitted changes in the log file are rolled back before
> > >recovery completes.
> > >
> > >Hope this helps (of course, if any of the above turns out to be true, I
> am
> > >sure John will set me straight),
> >
> > bcarter@risingroad.com
> > Mobile and Distributed Enterprise Database Applications
> > -----
> > Visit http://www.risingroad.com for Tips and Techniques
> > [TeamSybase]
>
>


0
anil
10/26/2002 4:48:02 PM
Teach me to give a 'quick' clarification. ;-)

    Anil has already covered the gory details, but my point was that the
operations being recorded in the log file had to be flushed to disk in order
for the COMMIT to complete.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
 choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

"Breck Carter" <NOSPAM__bcarter@risingroad.com> wrote in message
news:gg8jru4ndhl0bracbb50mm0coata10hf32@4ax.com...
> We'll put aside a discussion of COOPERATIVE_COMMITS and
> DELAYED_COMMITS for the moment, because I can't find my Motrin :)
>
> When you say "log file I/O is synchronous with COMMIT statements"
> someone might get the impression that changes are not written to the
> log until a transaction is committed. What actually happens (according
> to the Help, anyway) is that the log contains all changes, by all
> transactions whether committed or not, that were made before the most
> recent COMMIT.
>
> So if I update a million rows, and you do a COMMIT, all *my* changes
> are written to the log even though I could still do a ROLLBACK. So the
> log can contain uncommitted changes. But log file I/O is indeed
> synchronous with COMMITs.
>
> That brings up the following (silly?) point... The Help also says that
> the log file does not contain any uncommitted transactions made after
> the most recent COMMIT. If that is true, then a database being heavily
> updated with a long-running transaction in single-user mode will wait
> for a long time before writing ANY data to the log file. Then, when
> the COMMIT happens, the drive light comes on for a half-hour.
>
> A multi-million-row MobiLink download falls into this category.
> Perhaps performance could be improved by having a second connection
> which does nothing except issue COMMIT statements every few seconds.
> Then the log file I/O would be spread out over time.
>
> Unless log file I/O is *not* synchronous with COMMITs at all. I'm not
> sure it should be, performance-wise, but once again I am...
>
> Breck Just Guessing :)
>
> On Fri, 25 Oct 2002 11:18:40 -0400, "Robert Waywell"
> <rwaywell@ianywhere.com> wrote:
>
> >One clarification. While DB I/O is not tied to commits, Log file I/O is
> >synchronous with COMMIT statements.
>
> bcarter@risingroad.com
> Mobile and Distributed Enterprise Database Applications
> -----
> Visit http://www.risingroad.com for Tips and Techniques
> [TeamSybase]


0
Robert
10/28/2002 3:30:47 PM
Reply: