on 2013 Feb 07 5:09 PM
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.
Request clarification before answering.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
If you don't want to change the name of the database then use the -ar switch on dbunload.
You need to rebuild (unload-reload) database with new page size.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)...
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.
Hello, you must be careful when changing the page size. Must make some considerations. See link.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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.
@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.
Congratulations... experimentation rules!
User | Count |
---|---|
60 | |
10 | |
8 | |
8 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.