Fillfactor tuning

From PTAGISWiki

For fillfactors, it depends on the storage structure, data volatility, and frequency of modification. For example:

• BTREE should always be set to 100% since the remaining space will not be used once an associated data page (next page pointer) is assigned
• For ISAM:
  • 100% if it is going to be static
  • 70%-80% if the table is active and will be modified on a periodic basis (e.g., monthly or quarterly)
  • 50%-65% if the table is highly active and/or will not be modified on a regular basis
  • NOTE: BTREE is almost always better than ISAM, so 99% of the time you are better off selecting BTREE
• For HASH:
  • If you want to spread the activity (e.g., keep phone operators on separate data pages for concurrency reasons) then set the fillfactor very low (5%-15%)
  • For normal tables, 35%-60% works well. This is a bit trial-and-error as you need to modify with a particular fillfactor, look at the overflow page count (from help table) after the modify, and redo if required.
  • Remember that minpages is as important as fillfactor when working with HASH tables
• For HEAP tables, always set to 100% since most activity (updates should occur in-place if it does not expand the row size) happens on the current page.
  • HEAP is almost never a good choice - even for things like audit tables, since there will likely be concurrency issues on the last page, and reads will likely cause table scans.

Indexing is all about minimizing I/O. You want to get as much useful data in as few I/Os as possible. Ingres is currently TID limited to 512 rows / page, so bigger pages are usually not good for indexes (e.g., I never use 8K for system catalogs). For a high-performance configuration I will typically have 2-3 page sizes (usually 2K, 8K, and 16K), and then pick the best page size for each table and index. Most indexes will fall into 2K pages, and most tables will fall into 8K pages (the var_page.xls spreadsheet will provide a good starting point for making those decisions). Sometimes I will use a separate page size for global temporary tables because not doing that while extensively using the lightweight tables will cause good production data to be displaced and increasing I/O.

Don't forget about compression, as that can be helpful as well. It also has the potential to improve checkpoint performance as well.

The caveat to all of this is that experience in a specific environment trumps rules of thumb. ROT are a good starting point, but it is the beginning, not the end, of the tuning process. If the table or index is growing significantly, then consider setting the extend_size value higher. Also, look for page overflow (ISAM and HASH) and tune accordingly if you start noticing more than just a few pages of overflow. In addition to unnecessary I/O, overflow causes more pages to be locked, possibly escalating to a table-level lock, and contributing to concurrency problems.