Operating system paging affects queries that need buffers which exceed the free memory available. Some of this paging is necessary, especially as you allocate more and more physical memory to your buffer caches. However, if you overallocate the physical memory to your buffer caches, the operating system paging occurs much more frequently, and it can cause your entire system to thrash. The reverse is true as well: IQ thrashes if you do not allocate enough memory to your buffer caches.
Buffer manager thrashing occurs when the operating system chooses less optimum buffers to page out to disk, which forces the buffer manager to make extra reads from disk to bring those buffers back to memory. Since Sybase IQ knows which buffers are the best candidates to flush out to disk, you want to avoid this operating system interference by reducing the overall number of page outs.
When you set buffer sizes, keep in mind the following trade-off:
If the IQ buffer cache is too large, the operating system is forced to page as Sybase IQ tries to use all of that memory.
If the IQ buffer cache is too small, then Sybase IQ thrashes because it cannot fit enough of the query data into the cache.
If you are experiencing dramatic performance problems, you should monitor paging to determine if thrashing is a problem. If so, then reset your buffer sizes as described in “Managing buffer caches”.
If you monitor paging and determine that thrashing is a problem, you can also limit the amount of thrashing during the execution of a statement which includes a query that involves hash algorithms. Adjusting the HASH_THRASHING_PERCENT database option controls the percentage of hard disk I/Os allowed before the statement is rolled back and an error is returned.
The default value of HASH_THRASHING_PERCENT is 10%. Increasing HASH_THRASHING_PERCENT permits more paging to disk before a rollback and decreasing HASH_THRASHING_PERCENT permits less paging before a rollback.
Queries involving hash algorithms that executed in earlier
versions of IQ may now be rolled back when the default HASH_THRASHING_PERCENT limit
is reached. IQ reports the error Hash insert
thrashing detected or Hash find
thrashing detected. Take one or more of the
following actions to provide the query with the resources required
for execution:
Relax the paging restriction by increasing the value of HASH_THRASHING_PERCENT.
Increase the size of the temporary cache (DBA only). Keep in mind that increasing the size of the temporary cache reduces the size of the main cache.
Attempt to identify and alleviate why IQ is misestimating one or more hash sizes for this statement. For example, check that all columns that need an LF or HG index have one. Also consider if a multicolumn index is appropriate.
Decrease the value of the database option HASH_PINNABLE_CACHE_PERCENT.
For more information on these database options, see the sections “HASH_THRASHING_PERCENT option” and “HASH_PINNABLE_CACHE_PERCENT option” in Chapter 2, “Database Options” of the Sybase IQ Reference Manual.
To identify possible problems with a query, generate a query plan by running the query with the temporary database options QUERY_PLAN = 'ON' and QUERY_DETAIL = 'ON', then examine the estimates in the query plan. The generated query plan is in the message log file.
