I’m reading the tuning guide and trying to eke out a little more performance for our app by tweaking storage parameters. The properties our database exhibits which seem potentially of important to storage tuning are:
- tables with small records (typically 3 fields; usually fixed size, but some have varchar)
- few, but large (number of records per) tables
- frequent inserts
- once inserted, data will likely not change (it’s possible for an update operation, but in practice may never happen)
- several indexes per table
- queries usually involve joins and are usually selective
- some queries return somewhat large result sets
- queries currently take longer than we would like
1. Because updates will be very rare, I think it makes sense to set pageReservedSpace to 0%. If an update occurs which expands the size of a row (unlikely but possible), there will be a performance hit (I assume during both the update and subsequent reads of this record, because the documentation implies that part of the row will be written to an overflow page). But my pages will hold 25% more data, which will be a big win for the normal case. Reasonable?
2. Page size is a little less straightforward. The following imply page size should be small:
- not storing large objects
- small columns/records
- selective queries
The following imply page size should be large:
- tables with large numbers of records
- inserts only (?? The documentation says read-only apps should use a large page size. I’m not quite sure of the reasoning for this. Would this also apply to an app such as mine which only performs inserts but not updates?)
The documentation says large page size for indexes is a big performance gain. I was wondering if it makes sense to have small pages for all tables, and large pages for all indexes. Does having heterogeneous page sizes have any adverse effects on the page cache? Does having mismatched page sizes between a table and its indexes have any negative impact?
3. What is the page cache eviction policy? Is it least-frequently-used? Do indexes get preferential treatment? Since some queries pull a large amount of data, I think this could potentially repopulate the entire cache with data which may not be used again. Does this mean it would be wasteful to increase the page cache size?