SQL Anywhere Feedback - Page Size by Table

Author

Page Size by Table

Michael Gould

2005-10-27, 7:41 am

What would be the downside if we were allowed to set table page sizes on a
per table basis? It would seem to me that we could then have the ability to
fine tune some accesses to the to the row level without certain types of
locks causing problems. We have about 4 tables in our system that are used
for 90% of all transactions. If we could designate one row per page on some
of these tables it might improve the row locking that takes place from time
to time.

Best Regards,

Michael Gould

Breck Carter [TeamSybase]

2005-10-27, 7:41 am

SQL Anywhere never uses page-level locking, only row-level or
table-level (and table-level is rare).

AFAIK the one-row-per-page trick is an old ASE dodge from the days
they used to say "nobody needs row-level locking!"

So... please describe your locking issues, "it's a subject I have much
interest in." - Esmarelda Villa Lobos in Pulp Fiction

I am thinking of putting a "block sniffer" program out into the
world... it runs on a different computer, tracks and displays blocked
and blocking connections in green (no blocks) and red (long blocks) so
you can see it across the room :)... lemme know if you are interested
in a beta.

Breck

On 20 Oct 2005 06:56:01 -0700, "Michael Gould" <mgould@omnicc.com>
wrote:

>What would be the downside if we were allowed to set table page sizes on a
>per table basis? It would seem to me that we could then have the ability to
>fine tune some accesses to the to the row level without certain types of
>locks causing problems. We have about 4 tables in our system that are used
>for 90% of all transactions. If we could designate one row per page on some
>of these tables it might improve the row locking that takes place from time
>to time.
>
>Best Regards,
>
>Michael Gould
>

--
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

Michael Gould

2005-10-27, 7:41 am

Breck,

It might be interesting. I seem to have a issue from time to time where
users claim that our system is "locking" up. I've spent more than a year
trying to track it down and both the application code and the stored
procedures and triggers look just fine. I watched via the monitor and
calling sa_locks() and other stored procedures for a day at a time and I
don't see anything that I would classify as a block.

I know that ASA does a row level lock on all data but was wondering if we
could determine individual page sizes wouldn't the storage be more efficent?
Wouldn't the optimizer be quicker if it knew that all data needed for a
table is on a single page and didn't need any extentions?

The Block sniffer would be an excellent tool to put in my toolbox. I'm
definately interested in the beta.

Regards,

Mike Gould

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:kgafl1l69oqc1ou
jpd6oem8ne5546k026u@
4ax.com...
> SQL Anywhere never uses page-level locking, only row-level or
> table-level (and table-level is rare).
>
> AFAIK the one-row-per-page trick is an old ASE dodge from the days
> they used to say "nobody needs row-level locking!"
>
> So... please describe your locking issues, "it's a subject I have much
> interest in." - Esmarelda Villa Lobos in Pulp Fiction
>
> I am thinking of putting a "block sniffer" program out into the
> world... it runs on a different computer, tracks and displays blocked
> and blocking connections in green (no blocks) and red (long blocks) so
> you can see it across the room :)... lemme know if you are interested
> in a beta.
>
> Breck
>
>
> On 20 Oct 2005 06:56:01 -0700, "Michael Gould" <mgould@omnicc.com>
> wrote:
>
>
> --
> 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-10-27, 7:41 am

Varying the page size probably has *huge* implications for the
development team; e.g., there might have to be separate RAM caches for
the different page sizes.

I know it would have huge implications for administration, endlessly
fiddling with page sizes, whether to put this index or that in this
buffer pool or that, this table or that... like interviewing angels
for positions on the head of a pin :)

Having said all that, there probably are applications crying out for
varying page sizes... post your suggestion on the futures ng.

In the meantime, you can check for page splits, see if you really have
a problem; from the book:

CREATE PROCEDURE p_table_fragmentatio
n ( IN @owner_name VARCHAR ( 128
) )
RESULT ( table_name VARCHAR ( 128 ),
rows UNSIGNED INTEGER,
row_segments UNSIGNED BIGINT,
segments_per_row DOUBLE,
table_pages UNSIGNED BIGINT,
extension_pages UNSIGNED BIGINT )
BEGIN
SELECT *
INTO #sa_table_fragmentat
ion
FROM sa_table_fragmentati
on ( owner_name = @owner_name );
SELECT #sa_table_fragmentat
ion.TableName AS table_name,
#sa_table_fragmentat
ion.rows AS rows,
#sa_table_fragmentat
ion.row_segments AS row_segments,
#sa_table_fragmentat
ion.segs_per_row AS segments_per_row,
sa_table_stats.table_page_count AS table_pages,
sa_table_stats.ext_page_count AS extension_pages
FROM #sa_table_fragmentat
ion
INNER JOIN sa_table_stats()
ON sa_table_stats.table_name =
#sa_table_fragmentat
ion.TableName
WHERE sa_table_stats.creator = @owner_name
ORDER BY table_name;
END;

SELECT * FROM p_table_fragmentatio
n ( 'DBA' );

SELECT table_name,
segments_per_row,
CAST ( IF rows = 0
THEN 0
ELSE CAST ( extension_pages AS DOUBLE ) /
row_segments
ENDIF AS FLOAT ) AS extension_pages_per_
segment,
IF segments_per_row >= 1.05
THEN 'Fragmentation!'
ELSE IF extension_pages_per_
segment >= 2
THEN 'Fragmentation!'
ELSE ''
ENDIF
ENDIF AS fragmentation
FROM p_table_fragmentatio
n ( 'DBA' )
ORDER BY table_name;

On 21 Oct 2005 05:07:54 -0700, "Michael Gould" <mgould@omnicc.com>
wrote:

>Breck,
>
>It might be interesting. I seem to have a issue from time to time where
>users claim that our system is "locking" up. I've spent more than a year
>trying to track it down and both the application code and the stored
>procedures and triggers look just fine. I watched via the monitor and
>calling sa_locks() and other stored procedures for a day at a time and I
>don't see anything that I would classify as a block.
>
>I know that ASA does a row level lock on all data but was wondering if we
>could determine individual page sizes wouldn't the storage be more efficent?
>Wouldn't the optimizer be quicker if it knew that all data needed for a
>table is on a single page and didn't need any extentions?
>
>The Block sniffer would be an excellent tool to put in my toolbox. I'm
>definately interested in the beta.
>
>Regards,
>
>Mike Gould
>
>
>
>
>"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
>message news:kgafl1l69oqc1ou
jpd6oem8ne5546k026u@
4ax.com...
>

--
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-10-27, 7:41 am

On 21 Oct 2005 05:07:54 -0700, "Michael Gould" <mgould@omnicc.com>
wrote:

>The Block sniffer would be an excellent tool to put in my toolbox. I'm
>definately interested in the beta.

I will send you an email when it's ready to download. I assume you
have SQL Anywhere V9 installed; block sniffer needs it to *run*, but
will work with V7 *target* databases or later.

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

Michael Gould

2005-10-27, 7:41 am

Yes I have both 8 and 9 installed.

Regards,

Mike Gould

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:b3rhl19clsuuhrb
e2jkdjko6cqjdv0g3m8@
4ax.com...
> On 21 Oct 2005 05:07:54 -0700, "Michael Gould" <mgould@omnicc.com>
> wrote:
>
>
> I will send you an email when it's ready to download. I assume you
> have SQL Anywhere V9 installed; block sniffer needs it to *run*, but
> will work with V7 *target* databases or later.
>
> 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

Dmitri

2005-10-27, 7:41 am

Breck Carter [TeamSybase] wrote:

> I am thinking of putting a "block sniffer" program out into the
> world... it runs on a different computer, tracks and displays blocked
> and blocking connections in green (no blocks) and red (long blocks) so
> you can see it across the room :)... lemme know if you are interested
> in a beta.

Interesting indeed - if you need one more beta tester, please mail me
(dim-at-mail15-dot-com).

Dmitri.

Breck Carter [TeamSybase]

2005-10-27, 7:41 am

I will send you an email when it's ready to download... I guess this
means I have to get moving :)

Breck

On 21 Oct 2005 06:45:12 -0700, Dmitri <NOdimSPAM@mail15.com> wrote:

>Breck Carter [TeamSybase] wrote:
>
>
>Interesting indeed - if you need one more beta tester, please mail me
>(dim-at-mail15-dot-com).
>
>Dmitri.

--
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