The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

We have discovered, quite by accident, that the performance of our database application improved drastically when we increase the page size. To test this, though, we created a new database file with the new page size and started our process a new on a test machine.

There are existing installations of our software in our client base at this time. Is there any way to upgrade their existing databases to the new page size? If so, how is it done? I can't find anything in the documentation.

asked 07 Feb '13, 17:09

TonyV's gravatar image

TonyV
1.2k333967
accept rate: 75%

edited 07 Feb '13, 17:12

When you do your testing with the smaller page size, are you also starting with a new database file or are you testing with a database that has some "history" to it? If you are not starting with a new database when using the smaller page size, it may be worth a try just to confirm that it was the difference in page size that caused the performance change and not the layout of the contents of the old database.

(07 Feb '13, 23:44) John Smirnios

We have. Performance with the old page size was consistent whether we started empty or with one that had been running for a while. We were near our wits end and just tried the larger database size, not expecting any improvement. We were pleasantly surprised.

(08 Feb '13, 08:49) TonyV

There is no method to change the page size of a database. As Dmitri has said, you need to rebuild the database - i.e. create a new database with the new page size and then unload your schema and data from the old database and load it into your new database. There are a couple of ways of doing this - see the section of database rebuilds for full details. The method that you need to use will depend on whether your database is involved in replication or not - the docs cover this.

Example: Assuming you are not using replication, and you wanted your new database to have a 8K page size, then the steps would be:

1: create your new database using dbinit

dbinit -p 8k new.db

2: use dbunload to copy the schema and data from the old database to the new:

dbunload -c "UID=DBA;PWD=sql;DBN=old;DBF=old.db" -ac "UID=DBA;PWD=sql;DBN=new;DBF=new.db"

HTH

permanent link

answered 07 Feb '13, 19:24

Mark%20Culp's gravatar image

Mark Culp
22.3k9129262
accept rate: 40%

1

Under Mark's assumption, you can also do it in one step when using option -an instead of -ac, by adding -ap to specify the page size of the new database:

dbunload -c "UID=DBA;PWD=sql;DBN=old;DBF=old.db" -an new.db -ap 8k
(08 Feb '13, 04:45) Volker Barth

Thanks. Just what I was looking for!

(08 Feb '13, 08:49) TonyV

I just thought of something -- If I want the new database to end up with the same file name as the old database, I'm going to have to do the dbunload twice, right?

(08 Feb '13, 08:51) TonyV
Replies hidden
1

If you don't want to change the name of the database then use the -ar switch on dbunload.

(08 Feb '13, 09:02) Mark Culp

You need to rebuild (unload-reload) database with new page size.

permanent link

answered 07 Feb '13, 17:38

Dmitri's gravatar image

Dmitri
1.5k41132
accept rate: 11%

Hello, you must be careful when changing the page size. Must make some considerations. See link.

Use an appropriate page size

permanent link

answered 08 Feb '13, 06:49

Walmir%20Taques's gravatar image

Walmir Taques
600283143
accept rate: 13%

2

4K is almost always the best choice, 8K sometimes helps for large tables and especially indexes, and the other settings should never be used: 2K, 16K, 32K.

(08 Feb '13, 09:15) Breck Carter
Replies hidden

In my opinion, for large DB the performance increases if the increase in the page size (from 4K to 8K or 16K) for a decrease in the depth of the index from 4 to 3. Make sure to check this hypothesis, if your 4K-database has a depth of indices = 4. You will definitely see a significant improvement in performance for "without depth of indices = 4" (8K or 16K) - database.

(08 Feb '13, 12:04) Ilia63

@BreckCarter: Here's the thing. I've found that in our application, a page size of 16K has improved the database performance above even the performance increase we saw at 8K. I have a Panasonic Toughbook with an Intel Core 2Duo 1.6 GHZ CPU, 2 GB of ram and a single hard drive. This machine is actually over powered compared to most machines that will be running our software. And it's not a database server; this is an embedded database application. With 4K & 8K pages, as our process progressed, you would see occasions where the machine would stop responding because the hard drive activity was pegged at 100%. Yet, with 16K pages, this isn't happening. I beleive we're going with 16K pages based on these results.

(08 Feb '13, 12:49) TonyV
1

Congratulations... experimentation rules!

(08 Feb '13, 13:39) Breck Carter

Hi Ilia63,

What can I consider as "large db"

Honestly still could not figure out :(. Database size? 1, 2, 3..10 GBytes???

(01 Dec '15, 15:31) Walmir Taques

Our db performance was improved by upgrading page size to 4k.

On windows Process Monitor can be used to see the actual disk reads as they occur and may help determine if page size is causing performance issues.

Page size is one of the Performance Tips.

permanent link

answered 10 Feb '13, 23:07

Mark's gravatar image

Mark
2265917
accept rate: 25%

1

Our db performance was improved by upgrading page size to 4k.

Well, IMHO the long-time 2K default was one of the rare not-optimal choices within SQL Anywhere's defaults... - I guess that one should have been increased to 4K earlier than it was (with v10, IIRC)...

(11 Feb '13, 07:10) Volker Barth

I expect 2K page size can be particularly disadvantageous if you are using a new "Advanced Format" (4K sector size) drive. Even if you are usinge a 4K page size database with an Advanced Format drive, it is worth ensuring that your drive has been partitioned with an OS that is aware of those drives and will properly align the start of the partition.

(11 Feb '13, 10:11) John Smirnios
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×238
×8

question asked: 07 Feb '13, 17:09

question was seen: 1,424 times

last updated: 01 Dec '15, 15:31