cancel
Showing results for 
Search instead for 
Did you mean: 

How do you change the page size of a database?

Former Member
6,523

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.

Accepted Solutions (1)

Accepted Solutions (1)

MarkCulp
Participant

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

VolkerBarth
Contributor

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
Former Member
0 Kudos

Thanks. Just what I was looking for!

Former Member
0 Kudos

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?

MarkCulp
Participant

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

Answers (3)

Answers (3)

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

0 Kudos

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.

VolkerBarth
Contributor

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

johnsmirnios
Advisor
Advisor
0 Kudos

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.

0 Kudos

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

Use an appropriate page size

Breck_Carter
Participant

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.

0 Kudos

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.

Former Member
0 Kudos

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

Breck_Carter
Participant

Congratulations... experimentation rules!

0 Kudos

Hi Ilia63,

What can I consider as "large db"

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