Performance warning: Page size too small for database

Classic Servoy
1

In the logfiles of Sybase I am getting the warning:```
Performance warning: Page size too small for database “assyst”

I have been looking in Sybase Central but cant seem to find a way to increase the page-size. The database is about 210 Mb 
Any suggestions how to increase the page size?

Thankx
2

I have been googling but cant seem to find a way to calculate the correct page-size.
Anyone has suggestions if a database is about 200 Mb?
Any help is appreciated
Thankx

3

Hi.
I have the same problem.
The only way I think you can change it is to use Sybase Central and the ‘Unload Database’ utility. This should allow you to ‘backup’ your database and reload it, at which point you should be able to specify a new page size.
I think the page size is related to the total size of all your tables columns sizes added together (probably excluding memo/blobs). I haven’t done this yet myself, so I suggest your play with backups and experiment.
I hope this points you in the right direction :)
Please reply with your results.

Rafi

4

Hi Tweetie,

Found a couple of links which may help you:
http://edocs.bea.com/wlp/docs81/db/sybase.html

http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sag/@Generic__BookTextView/54022;pt=53978

Hope these are of use

Cheers
Harry

5

Thankx for the info…
I just made a new db with the a higher page-size. 4096 seems to be enough for a database of 200 Mb. Did a unload and load of the data.
But now I have other issues

I. 01/14 01:07:03. Performance warning: Database "log_data" has a page size of 2048 that does not match maximum of 4096 set for server, causing inefficient use of cache
I. 01/14 01:07:03. Performance warning: Database "servoy_repository" has a page size of 2048 that does not match maximum of 4096 set for server, causing inefficient use of cache
I. 01/14 01:07:03. Performance warning: Database "pcdb" has a page size of 2048 that does not match maximum of 4096 set for server, causing inefficient use of cache

Does changing the pagesize for the Servoy repository influences the speed of the repository?

6

QUestion to the Servoy-guys:
Does changing the pagesize to 4096 for the Servoy repository influences the speed/performance of the repository?

7

Changing the database page size can have a significant effect on performance. However, the difference you see really depends on your database schema and application usage.
For example, if your application queries read many/all rows from the result set or your rows are larger than a page in size, you may benefit from a larger page size, since it would mean more rows fit on a page, which leads to less IO to read the result set (if it is not already in cache).
Keep in mind that in version 9, you are limited to 255 rows per page, so if you have very small rows, you can potentially waste space. The limit has been removed in v10.
Most people find that 4k pages work pretty well, and that has become the default for new databases in v10. The other page sizes (1k, 2k, 8k, 16k) can help in specific scenarios. The only way to know for sure is to try it out in your test environment.

If you are interested in performance tuning the database further, check out this whitepaper which contais several tips on improving SQL Anywhere performance. It is old, but the concepts still apply:
http://www.ianywhere.com/downloads/whit … rmance.pdf

8

On a related note, I recently found that SQL Anywhere was adjusting its cache size too frequently and too wildly (as reported in the dbsrv10 session log in the terminal window), and it seemed to be affecting performance. So I added the parameters:

-c 2g -ca 0

to my options list for dbsrv10.

-c 2g sets cache size to 2 GB

-ca 0 tells it not to adjust the cache.

So far this seems to have enhanced performance a lot. If anyone knows of any reason this might not be a good idea please let me know.

By the way, I recommend the SQL Anywhere consulting services of Breck Carter of Rising Road Consulting, as well his book SQL Anywhere Studio 9 Developer’s Guide. Breck has helped me out a lot with getting the most out of SQL Anywhere.

9

amcgilly:
On a related note, I recently found that SQL Anywhere was adjusting its cache size too frequently and too wildly (as reported in the dbsrv10 session log in the terminal window), and it seemed to be affecting performance. So I added the parameters:

-c 2g -ca 0

to my options list for dbsrv10.
-c 2g sets cache size to 2 GB
-ca 0 tells it not to adjust the cache.
So far this seems to have enhanced performance a lot. If anyone knows of any reason this might not be a good idea please let me know.

Hi,
I am trying this on a test server with 4Gb ram, but Sybase won’t start up, saying ‘Not Enough Memory’. I changed to ‘-c 1500m’ to use 1.5Gb, and it started, but I don’t understand why it can’t take 2gb, with nothing else really running & Task Manager showing about 3.5gb free!
Any ideas?
Thanks,
Rafi