Database's Page Size Too Big - Refined Solutions and Performance Issuewww.come on babychinai tp ow er.comnt8SDkW

Document:

　　The general rule with regards to the engine page size/database page size relationship in SQL Anywhere is:www.come on babychinai tp ow er.comnt8SDkW

　　The database engine must have a page size which is greater than or equal to the maximum of the page sizes of all databases which are in use by this engine.www.come on babychinai tp ow er.comnt8SDkW

　　If the database(s) is(are) started at the same time as the database engine then the engine page size is set up correctly, according to this rule. If however, the user attempts to dynamically start another database with page size greater than the largest database page size running on the engine, the error message "page size too big" appears.www.come on babychinai tp ow er.comnt8SDkW

　　By default, database is created with a page size of 1024 bytes and, when not contradicts with the above rule, the engine is started with 1024 bytes page size as well.www.come on babychinai tp ow er.comnt8SDkW

　　Refined Solution 1www.come on babychinai tp ow er.comnt8SDkW

　　When it is possible beforehand to determine which databases will be running under a particular database engine(standalone or server), the page size of the engine should be set as the maximum page size of these databases.www.come on babychinai tp ow er.comnt8SDkW

　　Example:www.come on babychinai tp ow er.comnt8SDkW

　　Assume that there are only three database files: db1.db, db2.db and db3.db with page sizes 1024, 1024 and 2048 respectively, reside on a drive C: on a particular server. Then any of the following will set up the correct page size for database engine (server engine) named db1 :www.come on babychinai tp ow er.comnt8SDkW

dbsrv50 c:\db1.db c:\db2.db c:\db3.db
dbsrv50 -gp 2048 c:\db1.db c:\db2.db c:\db3.db
dbsrv50 -gp 2048 -n db1
dbsrv50 -n db1 c:\db3.dbwww.come on babychinai tp ow er.comnt8SDkW

　　The dbinfo.exe utility can be used to verify database page size, ie. dbinfo c:\db1.dbwww.come on babychinai tp ow er.comnt8SDkW

　　Refined Solution 2www.come on babychinai tp ow er.comnt8SDkW

　　Changing the page size of the databases. It can be achieved by creating a new database with a fixed page size, unloading the original database and reload it to the new database with the fixed page size.www.come on babychinai tp ow er.comnt8SDkW

　　Example:www.come on babychinai tp ow er.comnt8SDkW

　　Assume that there are only three database files: db1.db, db2.db and db3.db with page sizes 1024, 1024 and 2048 respectively, reside on a drive C: on a particular server and for a particular reason it is desired to have them have the same page size 2048 bytes each. The db3.db can be left as is, and for each db1.db and db2.db the following can be done, assuming c:\unload exists:www.come on babychinai tp ow er.comnt8SDkW

dbinit -p 2048 c:\db1new.db
dbunload -c "uid=...;pwd=...;dbf=c:\db1.db" -r c:\unload\reload.sql c:\unload
isql -c "uid=dba;pwd=sql;dbf=c:\db1new.db" read c:\unload\reload.sqlwww.come on babychinai tp ow er.comnt8SDkW

　　Performance Issuewww.come on babychinai tp ow er.comnt8SDkW

　　At most one database page can be put into one engine page. Thus if you start two databases, one with a page size of 1K and one with a page size of 4K, the engine will use 4K page size, which means that the 1K page size database pages are only one quarter full. In the case of a 512 byte page size database, at least half of all cache pages will be wasted. This makes the use of 512 byte page size for databases not recommended, although it is still supported for upward compatibility. www.come on babychinai tp ow er.comnt8SDkW

 www.come on babychinai tp ow er.comnt8SDkW