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.

Console: Performance warning: Page size too small for database "dbname"

7 posts in General Discussion (old) Last posting was on 2008-02-27 17:40:34.0Z
Michael Ray Posted on 2008-02-18 16:45:53.0Z
Reply-To: "Michael Ray" <ray6@SoftwareForToday.com>
From: "Michael Ray" <ray6@SoftwareForToday.com>
Newsgroups: sybase.public.sqlanywhere
Subject: Console: Performance warning: Page size too small for database "dbname"
Lines: 29
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47b9b641$1@forums-1-dub>
Date: 18 Feb 2008 08:45:53 -0800
X-Trace: forums-1-dub 1203353153 10.22.241.152 (18 Feb 2008 08:45:53 -0800)
X-Original-Trace: 18 Feb 2008 08:45:53 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:64
Article PK: 866518

Do I need to be concerned about this console message?
Performance warning: Page size too small for database "dbname"

I built a new database with the Sybase Central wizard and selected a 2048
page size.
Running a network database server service (created in Sybase Central) on
windows server 2003 R2.
Server:
D:\Program Files\SQL Anywhere 10\win32\dbsrv10.exe
The configuration options of the service:
-gp 2048
-n dbname
-c 16m
-x tcpip
"D:\Program Files\SaleOrder\db\dbname.db"
-n dbname
I removed the -gp 2048 option and the warning still appears. The service is
only running 1 database. My PB application which uses this database appears
to be fine.

My personal database server on XP Pro development PC does not send this
warning.
SQLAnywhere version 10.0.1.3619.
--
Michael Ray


Breck Carter [sqlanywhere.blogspot.com] Posted on 2008-02-18 19:40:06.0Z
From: "Breck Carter [sqlanywhere.blogspot.com]" <NOSPAM__breck.carter@gmail.com>
Newsgroups: sybase.public.sqlanywhere
Subject: Re: Console: Performance warning: Page size too small for database "dbname"
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__breck.carter@gmail.com
Message-ID: <ubnjr3l7j732tppme7h7ghfn0v9aonhp7k@4ax.com>
References: <47b9b641$1@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 18 Feb 2008 11:40:06 -0800
X-Trace: forums-1-dub 1203363606 10.22.241.152 (18 Feb 2008 11:40:06 -0800)
X-Original-Trace: 18 Feb 2008 11:40:06 -0800, vip152.sybase.com
Lines: 51
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:65
Article PK: 866519

The page size for the actual dbname.db is 2048 or smaller, and the
engine thinks that is too small for good performance.

4096 is the value most folks use for best performance, sometimes 8192,
but never 2048 or smaller. Changing the page size requires the
database file to be recreated (dbunload, dbinit, dbisql reload).

Of course, if performance is OK, then nothing needs to be done.

The dbsrv10 -gp 2048 parameter is redundant; it sets the page size
used by the engine, by default it is set to the page size of the first
database being created which is 2048 for you. The parameter is there
in case you wanted to start a second database that has a 4096 page
size, in which case you would want the server to use 4096 otherwise
you'd get an error when the second one was started (a hard error, not
the soft warning you're getting)... but that's getting off topic.

Breck

On 18 Feb 2008 08:45:53 -0800, "Michael Ray"

<ray6@SoftwareForToday.com> wrote:

>Do I need to be concerned about this console message?
> Performance warning: Page size too small for database "dbname"
>
>I built a new database with the Sybase Central wizard and selected a 2048
>page size.
>Running a network database server service (created in Sybase Central) on
>windows server 2003 R2.
>Server:
> D:\Program Files\SQL Anywhere 10\win32\dbsrv10.exe
>The configuration options of the service:
> -gp 2048
> -n dbname
> -c 16m
> -x tcpip
> "D:\Program Files\SaleOrder\db\dbname.db"
> -n dbname
>I removed the -gp 2048 option and the warning still appears. The service is
>only running 1 database. My PB application which uses this database appears
>to be fine.
>
>My personal database server on XP Pro development PC does not send this
>warning.
>SQLAnywhere version 10.0.1.3619.

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter@risingroad.com


Michael Ray Posted on 2008-02-18 22:31:53.0Z
Reply-To: "Michael Ray" <ray6@SoftwareForToday.com>
From: "Michael Ray" <ray6@SoftwareForToday.com>
Newsgroups: sybase.public.sqlanywhere
References: <47b9b641$1@forums-1-dub> <ubnjr3l7j732tppme7h7ghfn0v9aonhp7k@4ax.com>
Subject: Re: Console: Performance warning: Page size too small for database "dbname"
Lines: 90
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47ba0759@forums-1-dub>
Date: 18 Feb 2008 14:31:53 -0800
X-Trace: forums-1-dub 1203373913 10.22.241.152 (18 Feb 2008 14:31:53 -0800)
X-Original-Trace: 18 Feb 2008 14:31:53 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:66
Article PK: 866521

1. Can I turn off just this message? With a db server option?

2. Is there some undocumented reason for the upward escalation of page
sizes? Are these newer databases less efficient at storing data?

3. Can a 2048 page size be safely used with v10? Is there something going on
that I don't know about? OR is it entirely possible that the engine's guess
is wrong in my case?
I've worked with every version of ASA/SQL Anywhere since 6.0. 1024 page
sizes have worked wonderfully for me in the past. I started using 2048 with
v9.02.
Usually, my tables are very compact with 20 columns or less (integer,
dates, tinyint, varchar 30 -50, small numerics). I've read everything I can
about table design and performance tips and have had great performance in
these PB apps.

4. I wonder how "most folks" decided that 4096 was best.
Is it that large hard drives are inexpensive?
Is it that they don't know that 2048 would work just as well?
Are they just reading the DB documentation that says 4096 should be
minimum?

5. Why does the documentation say that 4096 should be minimum?


Thanks Breck.
--
Michael Ray

"Breck Carter [sqlanywhere.blogspot.com]" <NOSPAM__breck.carter@gmail.com>

wrote in message news:ubnjr3l7j732tppme7h7ghfn0v9aonhp7k@4ax.com...
> The page size for the actual dbname.db is 2048 or smaller, and the
> engine thinks that is too small for good performance.
>
> 4096 is the value most folks use for best performance, sometimes 8192,
> but never 2048 or smaller. Changing the page size requires the
> database file to be recreated (dbunload, dbinit, dbisql reload).
>
> Of course, if performance is OK, then nothing needs to be done.
>
> The dbsrv10 -gp 2048 parameter is redundant; it sets the page size
> used by the engine, by default it is set to the page size of the first
> database being created which is 2048 for you. The parameter is there
> in case you wanted to start a second database that has a 4096 page
> size, in which case you would want the server to use 4096 otherwise
> you'd get an error when the second one was started (a hard error, not
> the soft warning you're getting)... but that's getting off topic.
>
> Breck
>
> On 18 Feb 2008 08:45:53 -0800, "Michael Ray"
> <ray6@SoftwareForToday.com> wrote:
>
>>Do I need to be concerned about this console message?
>> Performance warning: Page size too small for database "dbname"
>>
>>I built a new database with the Sybase Central wizard and selected a 2048
>>page size.
>>Running a network database server service (created in Sybase Central) on
>>windows server 2003 R2.
>>Server:
>> D:\Program Files\SQL Anywhere 10\win32\dbsrv10.exe
>>The configuration options of the service:
>> -gp 2048
>> -n dbname
>> -c 16m
>> -x tcpip
>> "D:\Program Files\SaleOrder\db\dbname.db"
>> -n dbname
>>I removed the -gp 2048 option and the warning still appears. The service
>>is
>>only running 1 database. My PB application which uses this database
>>appears
>>to be fine.
>>
>>My personal database server on XP Pro development PC does not send this
>>warning.
>>SQLAnywhere version 10.0.1.3619.
>
> --
> Breck Carter http://sqlanywhere.blogspot.com/
>
> RisingRoad SQL Anywhere and MobiLink Professional Services
> breck.carter@risingroad.com


Jeff Albion (Sybase iAnywhere) Posted on 2008-02-19 15:31:13.0Z
From: "Jeff Albion (Sybase iAnywhere)" <firstname.lastname@ianywhere.com>
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
Newsgroups: sybase.public.sqlanywhere
Subject: Re: Console: Performance warning: Page size too small for database "dbname"
References: <47b9b641$1@forums-1-dub> <ubnjr3l7j732tppme7h7ghfn0v9aonhp7k@4ax.com> <47ba0759@forums-1-dub>
In-Reply-To: <47ba0759@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47baf641$1@forums-1-dub>
Date: 19 Feb 2008 07:31:13 -0800
X-Trace: forums-1-dub 1203435073 10.22.241.152 (19 Feb 2008 07:31:13 -0800)
X-Original-Trace: 19 Feb 2008 07:31:13 -0800, vip152.sybase.com
Lines: 82
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:67
Article PK: 866520


Michael Ray wrote:
> 1. Can I turn off just this message? With a db server option?

Use the "-qp" switch on server start-up.

> 2. Is there some undocumented reason for the upward escalation of page
> sizes? Are these newer databases less efficient at storing data?

If by "upward escalation", you mean we changed the default page size
from 2048 to 4096, this was due to the fact that 4K page sizes generally
perform better for most (!) of our customer's general applications. We
of course make assumptions about the size of rows in tables, how many
rows can be fit to a page, efficiency in disk reading (more consecutive
sector reads, lowering disk seek time), etc.

This may or may not be for you. If you see a performance warning though,
we generally have a good reason to issue it. As above, if you know your
data better than we do, just use "-qp."

> 3. Can a 2048 page size be safely used with v10? Is there something going on
> that I don't know about? OR is it entirely possible that the engine's guess
> is wrong in my case?

It's not "wrong" or "unsafe" it just "may not be an optimal use of your
hard disk." You as a developer should know (through performance
tests/profiling of your application) what page size your application
best performs on - 2048, 4096, or 8192. I'm assuming the performance
warning is there for a good reason though.

> I've worked with every version of ASA/SQL Anywhere since 6.0. 1024 page
> sizes have worked wonderfully for me in the past. I started using 2048 with
> v9.02.
> Usually, my tables are very compact with 20 columns or less (integer,
> dates, tinyint, varchar 30 -50, small numerics). I've read everything I can
> about table design and performance tips and have had great performance in
> these PB apps.

Great! If it's working as-is and you're happy with the performance,
stick with what you have. Although we highly recommend that you consider
testing with 4096 in case you might see an even BIGGER performance
improvement. :) (If you don't, go back to 2048!)

> 4. I wonder how "most folks" decided that 4096 was best.
> Is it that large hard drives are inexpensive?
> Is it that they don't know that 2048 would work just as well?
> Are they just reading the DB documentation that says 4096 should be
> minimum?

"Most folks" are relying on several internal performance tests that our
query optimization team had performed when deciding what a good
"default" page size would be. This test considered:

1) Size of indexes
2) Size of rows
3) Size of available cache
4) Number of I/O reads that are required
5) Scattered reads

(Scattered Reads:
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/1001/en/html/dbugen10/ug-performance-s-5691504.html
)

If you are working with Windows, a minimum page size of 4 KB allows the
database server to read a large contiguous region of database pages on
disk directly into the appropriate place in cache, bypassing the 64 KB
buffer entirely. This feature can significantly improve performance.


> 5. Why does the documentation say that 4096 should be minimum?

For all of those reasons. :)

Regards,

--
Jeff Albion, Product Support Analyst
Sybase iAnywhere

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs :
http://downloads.sybase.com/swd/summary.do?baseprod=144&client=ianywhere&timeframe=0


Breck Carter [sqlanywhere.blogspot.com] Posted on 2008-02-19 21:02:34.0Z
From: "Breck Carter [sqlanywhere.blogspot.com]" <NOSPAM__breck.carter@gmail.com>
Newsgroups: sybase.public.sqlanywhere
Subject: Re: Console: Performance warning: Page size too small for database "dbname"
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__breck.carter@gmail.com
Message-ID: <9ugmr395i80urdg8d6vr9so7gbbkmgtjbt@4ax.com>
References: <47b9b641$1@forums-1-dub> <ubnjr3l7j732tppme7h7ghfn0v9aonhp7k@4ax.com> <47ba0759@forums-1-dub> <47baf641$1@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 19 Feb 2008 13:02:34 -0800
X-Trace: forums-1-dub 1203454954 10.22.241.152 (19 Feb 2008 13:02:34 -0800)
X-Original-Trace: 19 Feb 2008 13:02:34 -0800, vip152.sybase.com
Lines: 17
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:69
Article PK: 866522

On 19 Feb 2008 07:31:13 -0800, "Jeff Albion (Sybase iAnywhere)"

<firstname.lastname@ianywhere.com> wrote:

>Michael Ray wrote:
>> 1. Can I turn off just this message? With a db server option?
>
>Use the "-qp" switch on server start-up.

I did not know that, thanks!

Breck

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter@risingroad.com


Breck Carter [sqlanywhere.blogspot.com] Posted on 2008-02-19 21:01:13.0Z
From: "Breck Carter [sqlanywhere.blogspot.com]" <NOSPAM__breck.carter@gmail.com>
Newsgroups: sybase.public.sqlanywhere
Subject: Re: Console: Performance warning: Page size too small for database "dbname"
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__breck.carter@gmail.com
Message-ID: <ucfmr314ppcflmisau469hlbucnn5rqcfb@4ax.com>
References: <47b9b641$1@forums-1-dub> <ubnjr3l7j732tppme7h7ghfn0v9aonhp7k@4ax.com> <47ba0759@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 19 Feb 2008 13:01:13 -0800
X-Trace: forums-1-dub 1203454873 10.22.241.152 (19 Feb 2008 13:01:13 -0800)
X-Original-Trace: 19 Feb 2008 13:01:13 -0800, vip152.sybase.com
Lines: 122
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:68
Article PK: 866523

On 18 Feb 2008 14:31:53 -0800, "Michael Ray"

<ray6@SoftwareForToday.com> wrote:

>1. Can I turn off just this message? With a db server option?

No, but you can safely ignore it.

>2. Is there some undocumented reason for the upward escalation of page
>sizes? Are these newer databases less efficient at storing data?

4K page sizes have always been the most efficient for most databases.
There has been no upward escalation, but the server has been improved
to do a "rule of thumb" calculation. It looks at your database file
size and if it's large enough it makes a suggestion.

The Foxhound product doesn't even bother with a calculation. It just
displays any page size smaller than 4K as a "Database Curiosity" which
is Foxhound's way of politely saying "You might want to look at this"
because, historically, in most cases it's a problem.

>3. Can a 2048 page size be safely used with v10?

Yes.

>Is there something going on
>that I don't know about?

The page size determines a lot of things. In particular, it determines
how many index entries will fit on a page, and that determines how
"deep" an index structure will grow. Small page size implies deep
index, and deep indexes are slow. The V10 help has a long discussion
of the subject.

>OR is it entirely possible that the engine's guess
>is wrong in my case?

It's an optimization thing. You can only know the truth by
experimentation, so of course the engine's guess can be wrong.

>I've worked with every version of ASA/SQL Anywhere since 6.0. 1024 page
>sizes have worked wonderfully for me in the past. I started using 2048 with
>v9.02.

Nothing says it won't work well. Nothing's saying it couldn't work
better, either... not without experimentation.

BUT (and this is the BIG BUT for ALL performance tuning)... if it
ain't broke, don't fix it. If you are happy with performance, don't
change the page size. In my book on SQL Anyhwere I quote the
following:

Rules on Optimization
Rule 1. Don't do it.
Rule 2. (for experts only) Don't do it yet.

>Usually, my tables are very compact with 20 columns or less (integer,
>dates, tinyint, varchar 30 -50, small numerics). I've read everything I can
>about table design and performance tips and have had great performance in
>these PB apps.
>
>4. I wonder how "most folks" decided that 4096 was best.
> Is it that large hard drives are inexpensive?
> Is it that they don't know that 2048 would work just as well?
> Are they just reading the DB documentation that says 4096 should be
>minimum?

For most folks, it comes with experience... databases often perform
better with 4K page sizes, and almost never perform worse (IMO the
only place to use a smaller page size is on one of those diskless
devices with strange memory performance limitations).

FWIW, outside SQL Anywhere and in the larger computer world, I have
heard 4K mentioned many times over many years as a kind of standard
chunk size for performance.

SQL Anywhere 10 no longer has a limit on the number of rows that can
fit on a single page, so it is even *more* likely than before that a
larger page size can improve performance.

>5. Why does the documentation say that 4096 should be minimum?

Because it is a good idea in 99% of the cases?

Seriously, the default page size was 2K for many years and a LOT of
folks (like me) asked that it be changed. Over the years the NUMBER
ONE performance problem at my client sites has been the accidental use
of the 2K page size, and making the change is both technically boring
AND administratively traumatic, a combination which leads to overnight
upgrades that are truly depressing... end rant :)

Anyway, the key phrase is "performance problem"... I have never
changed a page size without proof that it is causing a problem, and
that usually comes in the form of slow queries, deep indexes and so
on. If you don't have a performance problem, then don't change.

Here is one place in the V10 docs where it specifically makes the
suggestion:

=====
SQL Anywhere® Server - SQL Usage > Monitoring and Improving
Performance > Performance improvement tips
...
Scattered reads
If you are working with Windows, a minimum page size of 4 KB allows
the database server to read a large contiguous region of database
pages on disk directly into the appropriate place in cache, bypassing
the 64 KB buffer entirely. This feature can significantly improve
performance.
=====

FWIW 8K is sometimes warranted; I have seen it make a huge improvement
in performance over 4K in some cases. However, 16K and 32K are to be
avoided.

Breck


--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter@risingroad.com


Michael Ray Posted on 2008-02-27 17:40:34.0Z
Reply-To: "Michael Ray" <ray6@SoftwareForToday.com>
From: "Michael Ray" <ray6@SoftwareForToday.com>
Newsgroups: sybase.public.sqlanywhere
References: <47b9b641$1@forums-1-dub> <ubnjr3l7j732tppme7h7ghfn0v9aonhp7k@4ax.com> <47ba0759@forums-1-dub> <ucfmr314ppcflmisau469hlbucnn5rqcfb@4ax.com>
Subject: Re: Console: Performance warning: Page size too small for database "dbname"
Lines: 23
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47c5a092$1@forums-1-dub>
Date: 27 Feb 2008 09:40:34 -0800
X-Trace: forums-1-dub 1204134034 10.22.241.152 (27 Feb 2008 09:40:34 -0800)
X-Original-Trace: 27 Feb 2008 09:40:34 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:70
Article PK: 866526

Thank you Jeff and Breck, for your thoughtful responses. I read both (and
related links) several times and considered my options.

I ended up creating a new database with a 4096 page size. I didn't feeling
comfortable turning off the console performance messages (in case something
else came up), and I didn't want to leave my client with a database that was
complaining. There was no choice to do page size testing (budget
restrictions). The performance warning is now gone.

Because this project had just started, the effort to create a new db and
transfer existing data (ISQL export / import with .txt) was no problem
really.
I imagine the warning had something to do with preallocating 1GB for the db
on the production Windows server. On my development winXP system (with
personal db server), I preallocated 2MB and did not receive a warning.

Thanks again for your help. I learned a few things...
-
Michael Ray