Configuring Swapping Policies¶
Virtual DataPort provides two ways to avoid memory overflows when dealing with huge datasets:
Stop retrieving data from a source when the size of these data that have not been processed, exceeds a certain limit.
Enable the swapping mechanism, which stores in secondary storage the intermediate results of the execution of a query, if their size exceeds a certain limit. It also swaps the intermediate results of the sorting operations (queries with an
The section Configuring the Memory Usage and Swapping Policy of the Administration Guide explain these methods in detail and how to change the default values of the settings that control them.
You can also change these values in the following elements, by executing certain VQL statements:
A specific database, with the statement
ALTER DATABASE(see section Creating and Modifying Virtual DataPort Databases)
A query, by adding the appropriate parameters to the
CONTEXTclause of the statement.
You should not modify the swapping configuration of the views or the databases. Most of the time, changing this configuration does not provide any improvement in the performance of the queries and certain values can even have a negative effect. This is why, there is no graphical support to change these settings on databases nor views.
Example 1: Modifying the memory usage parameters of a database to set the following parameters:
The “Maximum size in memory of each node” to 100
The “Maximum size of blocks stored in swap” to 150
The “Maximum size in memory of each intermediate result” to 50
ALTER DATABASE testing MEMORYCONFIG ( SWAP ON ( SWAPSIZE 100 SWAPBLOCKSIZE 150 ) MAXRESULTSIZE 50 )
Example 2: Disabling swapping in a view:
ALTER VIEW V SWAP OFF;
Example 3: Enabling swapping in a view, setting the “Max size” to 100 Mb and the “Result maximum size” to 25 Mb:
ALTER VIEW V SWAP ON SWAPSIZE 100 MAXRESULTSIZE 25;
Example 4: Running a query and disabling swapping. By adding the
CONTEXT clause of the query, the swapping will
be disabled, even if it is enabled in the view. That is because the
value of the parameters
SWAPSIZE of the
clause override the settings of the view.
SELECT … CONTEXT ('SWAP' = 'OFF')
Example 5: Running a query with swapping enabled and a swap “Max size” of 100 Mb:
SELECT … CONTEXT ('SWAP' = 'ON', 'SWAPSIZE' = '100' )