SQL Anywhere Feedback - CREATE EVENT ... TYPE "CHECKPOINT"

Author

CREATE EVENT ... TYPE "CHECKPOINT"

Breck Carter [TeamSybase]

2005-06-16, 11:23 am

Please implement TYPE "CHECKPOINT" for events, and fire the event
after each checkpoint completes during the period where the database
is handling connections; i.e., it is not necessary to fire the event
for the checkpoints performed at database startup and shutdown.

This would enable a performance monitor application to record the
frequency of checkpoints as well as facilitating row count queries
(SELECT table_name, "count" FROM SYSTABLE) that would efficiently
yield accurate numbers at known points in time.

Breck

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Rob Waywell

2005-06-24, 1:24 pm

The first benefit - recording the frequency of checkpoints - is mostly
provided by the server log today.

The second benefit - doing count queries immediately following the
checkpoint may not be that straightforward. Assuming multiple users, the
count may have changed by the time the count query gets executed.

--
-----------------------------------------------
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://case-express.sybase.com/cx/c...sc?CASETYPE=Bug

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 [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:qc53b1li59ts3gl
38auuirc2fct26icjme@
4ax.com...
> Please implement TYPE "CHECKPOINT" for events, and fire the event
> after each checkpoint completes during the period where the database
> is handling connections; i.e., it is not necessary to fire the event
> for the checkpoints performed at database startup and shutdown.
>
> This would enable a performance monitor application to record the
> frequency of checkpoints as well as facilitating row count queries
> (SELECT table_name, "count" FROM SYSTABLE) that would efficiently
> yield accurate numbers at known points in time.
>
> Breck
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Breck Carter [TeamSybase]

2005-06-26, 9:23 am

On 24 Jun 2005 11:41:33 -0700, "Rob Waywell"
<rwaywell@no_spam.ianywhere.com> wrote:

>The first benefit - recording the frequency of checkpoints - is mostly
>provided by the server log today.

Yes, but AFAIK the server log is not available to be *read* by a
stored procedure on-the-fly so the data can be stored in a table,
queried dynamically, displayed on a big monitor, analyzed
historically, etc.

>The second benefit - doing count queries immediately following the
>checkpoint may not be that straightforward. Assuming multiple users, the
>count may have changed by the time the count query gets executed.

Yes, that's true. It's actually worse: if 10 rows were deleted and 10
rows inserted during a sample interval, the change in row count would
be zero, mistakenly indicating "no activity".

However, it's a tradeoff: performance of SELECT "count" FROM SYSTABLE
is very good. An accurate alternative (hundreds of triggers to count
every insert and delete) might bog things down... as well as being a
pain to maintain :)

Of course, forcing a CHECKPOINT is dangerous from a performance point
of view... in this case, management's desire to know what's going on
outweighs that danger. And, as it turns out, it hasn't hurt.

The purpose is performance measurement; e.g., "how many rows were
inserted in the last 10 minutes?" It is up and running, on a server
with hundreds of active users and millions of rows, and is suprisingly
accurate. Deletes are rare (as they are in most applications), and if
an insert is missed in one sample, it's picked up by the next one.

Breck

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Bruce Hay

2005-06-27, 9:23 am

For reading the server log, see the sa_get_server_messag
es procedure and the
property functions it uses.

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

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:1cetb1dsvf1frik
5vusari3l7lock60eli@
4ax.com...
> On 24 Jun 2005 11:41:33 -0700, "Rob Waywell"
> <rwaywell@no_spam.ianywhere.com> wrote:
>
>
> Yes, but AFAIK the server log is not available to be *read* by a
> stored procedure on-the-fly so the data can be stored in a table,
> queried dynamically, displayed on a big monitor, analyzed
> historically, etc.
>
>
> Yes, that's true. It's actually worse: if 10 rows were deleted and 10
> rows inserted during a sample interval, the change in row count would
> be zero, mistakenly indicating "no activity".
>
> However, it's a tradeoff: performance of SELECT "count" FROM SYSTABLE
> is very good. An accurate alternative (hundreds of triggers to count
> every insert and delete) might bog things down... as well as being a
> pain to maintain :)
>
> Of course, forcing a CHECKPOINT is dangerous from a performance point
> of view... in this case, management's desire to know what's going on
> outweighs that danger. And, as it turns out, it hasn't hurt.
>
> The purpose is performance measurement; e.g., "how many rows were
> inserted in the last 10 minutes?" It is up and running, on a server
> with hundreds of active users and millions of rows, and is suprisingly
> accurate. Deletes are rare (as they are in most applications), and if
> an insert is missed in one sample, it's picked up by the next one.
>
> Breck
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Breck Carter [TeamSybase]

2005-06-27, 9:23 am

On 27 Jun 2005 06:16:02 -0700, "Bruce Hay"
< h_a_y@i~a~n~y~w~h~e~
r~e.c_o_m> wrote:

>For reading the server log, see the sa_get_server_messag
es procedure and the
>property functions it uses.

Doh!

"Oh, Dorothy, I wish I had a brain!"

Breck

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Sponsored Links