|
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. |
|
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 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. |
|
Hello, you must be careful when changing the page size. Must make some considerations. See link. 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
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. 1
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
|
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.
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.