User Guide : 16. Monitoring
 
Share this page                  
Monitoring
View Information about a Database
Use the vwinfo command to display information about a database that uses Vector tables.
By default, vwinfo displays various statistics about the database. You can supply options on the command to display other information. Issue vwinfo ‑h to see the list of available options.
To display database statistics
Issue any one of the following commands at the operating system prompt. All these commands produce the same output:
vwinfo dbname
vwinfo -s dbname
vwinfo --stats dbname
Example statistics output is shown under vwinfo Statistics Display.
To display the active configuration for the database
Issue either of these commands, which produce the same output:
vwinfo ‑c dbname
vwinfo ‑‑config dbname
The current configuration settings for the specified database are displayed. Here is an excerpt from a typical output:
+----------------------------+-----------------------------+
|config                      |value                        |
+----------------------------+-----------------------------+
|cbm.block_size              |524288                       |
|cbm.bufferpool_size         |536870912                    |
|cbm.compression_lz4_enabled |false                        |
|cbm.group_size              |8                            |
+----------------------------+-----------------------------+
To display the disk usage of tables
Issue either of these commands, which produce the same output:
vwinfo ‑T dbname
vwinfo ‑‑table_block_use dbname
To limit the output to a specified table, use the ‑t (or ‑‑table) option, as follows:
vwinfo ‑T ‑t tablename dbname
Here is an excerpt from a typical output:
+------------------------+------------------------+--------------------+
|schema_name             |table_name              |block_count         |
+------------------------+------------------------+--------------------+
|user1                   |part                    |                  30|
|user1                   |partsupp                |                 208|
+------------------------+------------------------+--------------------+
Note:  Disk usage is shown in the number of disk blocks.
To display the PDT usage of tables
Issue either of these commands, which produce the same output:
vwinfo -M dbname
vwinfo --table_pdt_use dbname
To limit the output to a specified table, use the -t (or --table) option, as follows:
vwinfo -M -t tablename dbname
To display information on open transactions and active sessions
Issue either of these commands, which produce the same output:
vwinfo -o dbname
vwinfo ‑‑open_transactions dbname
vwinfo Statistics Display
Here is an example of the statistics output from the vwinfo command:
+--------------------------------------+------------------------------------+
|stat                                  |value                               |
+--------------------------------------+------------------------------------+
|memory.memcontext_allocated           |30920304                            |
|memory.memcontext_maximum             |214748364                           |
|memory.memcontext_peak                |41797856                            |
|memory.memcontext_virtual_allocated   |30920304                            |
|memory.memcontext_virtual_maximum     |70368744177664                      |
|memory.memcontext_virtual_peak        |66593025904                         |
|memory.index_allocated                |3579844                             |
|memory.update_allocated               |0                                   |
|memory.update_maximum                 |53687091                            |
|memory.committed_transactions         |0                                   |
|memory.bufferpool_maximum             |3148349440                          |
|memory.other_allocated                |19360                               |
|bm.block_size                         |524288                              |
|bm.group_size                         |8                                   |
|bm.columnspace_used_blocks            |8                                   |
|bm.bufferpool_total_blocks            |24197                               |
|bm.bufferpool_free_blocks             |24197                               |
|bm.bufferpool_used_blocks             |0                                   |
|bm.bufferpool_cached_blocks           |0                                   |
|system.active_sessions                |0                                   |
|system.log_file_size                  |33239111                            |
|system.threshold_log_condense         |33554432                            |
|server.port_number                    |54375                               |
|server.pid                            |12800                               |
+--------------------------------------+------------------------------------+
Fields are as follows:
memory.memcontext_allocated
The total memory, in bytes, allocated and administered by Vector Server memory pooling. It includes the total memory used by currently running sessions and global server memory. It includes memory.updates_allocated, memory.committed_transactions, memory.index_allocated.
memory.memcontext_maximum
The maximum allowed amount of physical memory, in bytes. If this threshold is reached, some queries will stop working. It is configured with the [memory] max_memory_size configuration parameter.
memory.memcontext_peak
The maximum amount of memory, in bytes, allocated at one single moment by running sessions during the lifetime of the server.
This information is useful for dimensioning the system. If this amount is close to the maximum allowed amount of physical memory, the server may return out-of-memory (OOM) errors. Consider changing the maximum allowed amount of physical memory on the [memory]max_memory_size configuration parameter and possibly adding more physical memory to the server.
memory.memcontext_virtual_allocated
The amount of virtual memory, in bytes, allocated by the currently running queries. In most cases, this amount is higher than the amount of physical memory. It indicates the address space the server is using rather than an amount of allocated memory. This memory is allocated only when the server will use it, and is then displayed in memory.memcontext_allocated. The server allocates more address space than memory to avoid memory fragmentation and copying when certain memory areas grow. You can influence this behavior with the [memory]max_overalloc configuration parameter.
memory.memcontext_virtual_maximum
The maximum allowed amount of allocated virtual memory, in bytes
memory.memcontext_virtual_peak
The maximum amount of virtual memory, in bytes, allocated at one single moment by running sessions during the lifetime of the server. This value will always be lower than the maximum allowed amount of allocated virtual memory (memory.memcontext_virtual_maximum).
Note:  If this amount is close to the maximum, the server is running out of address space. A possible solution is lowering or disabling overallocation through the [memory]max_overalloc configuration parameter.
memory.index_allocated
The total memory, in bytes, allocated for in-memory indexes (min-max indexes and secondary indexes, if supported)
memory.update_allocated
The total memory, in bytes, used by memory-buffered updates of the committed transactions. The amount of this memory can be reduced by the automatic propagation mechanism (see Management of In-memory Updates on page 21). It can also be freed manually by issuing the statement MODIFY...TO COMBINE; COMMIT; on tables with in-memory updates, which will propagate the in-memory updates to disk (see Propagate In-memory Updates to Disk).
memory.update_maximum
The maximum amount of memory, in bytes, that can be used by memory-buffered updates. If memory.update_allocated reaches this limit, propagation (see Management of In-memory Updates on page 21) is triggered. If propagation is not successful, and the limit is reached, the memory-buffered updates may stop being accepted.
memory.committed_transactions
The total memory, in bytes, used by stored committed transactions. Vector keeps some information about a transaction in memory as long as any overlapping transaction is still running. This information is needed for potential conflict resolution. When this value becomes too high, the user must commit or abort some or all current sessions to free the memory occupied by the committed transactions. A typical cause for this value becoming high is a single old transaction that is still active.
memory.bufferpool_maximum
The total size, in bytes, of the buffer pool (disk block cache)
memory.other_allocated
(Linux only) Miscellaneous memory allocated, in bytes, outside the memory pooling.
bm.block_size
The disk block size used by this database. It is configured with the [cbm] block_size configuration parameter.
Note:  Changing this parameter is possible only before creating a database. Later changes are ignored.
bm.group_size
The group size used by this database. It is configured with the [cbm] group_size configuration parameter.
Note:  Changing this parameter is possible only before creating a database. Later changes are ignored.
bm.columnspace_used_blocks
The number of blocks used in total over all files and all locations
bm.bufferpool_total_blocks
The size of a buffer pool (disk data cache), in blocks. You can compute the total allowed physical size by multiplying this value by bm.block_size. This value is a sum of bm.bufferpool_free_blocks and bm.bufferpool_used_blocks.
bm.bufferpool_free_blocks
The number of blocks in the buffer pool that can be used for new data
bm.bufferpool_used_blocks
The number of blocks in the buffer pool that are currently locked by queries. This includes only blocks that are currently being used by scans and write to disk operations, which typically operate on only one block per column at a time. For statistics on cached data, see bm.bufferpool_cached_blocks.
bm.bufferpool_cached_blocks
The number of blocks in the buffer pool that contain cached data from disk
system.active_sessions
The number of currently running queries
system.log_file_size
Current size, in bytes, of the transaction log file
system.threshold_log_condense
Size, in bytes of the transaction log file at which the system tries to compact it
server.port_number
The port used for internal communication between the client and server
server.pid
The system process ID of the x100_server (iix100) process
vwinfo Transactions Display
The information provided by vwinfo -o can answer question such as:
How many active sessions are there?
Do some sessions use vast amount of memory? Which ones?
How much memory is used for updates?
Are there transactions with uncommitted changes?
Here is an example of the Transactions output from the vwinfo command:
+-------------------+-----------------+----------------+--------------+-------------------+--------------+-----------+---------+------------------------+
|x100_session_id |ingres_session_id|x100_thread_id |x100_tx_id |total_memory |pdts_memory |has_updates|read_only|x100_query |
+-------------------+-----------------+----------------+--------------+-------------------+--------------+-----------+---------+------------------------+
|                  9|00007FC21890C000 |00007F76E7FFF700|           306|             178224|             0|          0|        1|                        |
|                  4|00007FC234D40C00 |00007F76ED371700|           314|             287208|             0|          1|        0|                        |
|                 20|00007FC218A338C0 |00007F76ECB70700|           318|             178224|             0|          0|        0|                        |
|                 21|0000000000000000 |00007F76E77FE700|           320|             427554|             0|          0|        0|                        |
|                 82|0000000000000000 |00007FB88FD97700|            95|             309168|             0|          0|        0|Project(  HashJoinN(....|
|                 81|00000000028AA780 |00007FB890598700|            94|             244080|             0|          0|        0|                        |
+-------------------+-----------------+----------------+--------------+-------------------+--------------+-----------+---------+------------------------+
Fields are as follows:
x100_session_id
X100 session ID
ingres_session_id
Ingres session ID in hex
x100_thread_id
X100 thread ID in hex
x100_tx_id
X100 transaction ID. In between sessions with open transactions, sessions without open transactions (x100_tx_id = 0) are listed with the most recently connected shown first.
total_memory
Total memory, in bytes, consumed by the transaction
pdts_memory
Memory, in bytes, consumed by PDTs
has_updates
Whether transaction has uncommitted updates
read_only
Whether the transaction is read only (1) or read write (0)
x100_query
Currently executed Vector query
vwinfo PDT Usage Display
The information provided by vwinfo -M can be used to identify tables that are good candidates for MODIFY...TO COMBINE operations to free main memory used by the PDTs (Positional Delta Trees).
Here is an excerpt from a typical output:
+------------+-----------+------------+------------------+------------------+-------------------+
|schema_name |table_name |pdt_mem     |pdt_inserts_count |pdt_deletes_count |pdt_modifies_count |
+------------+-----------+------------+------------------+------------------+-------------------+
|user1       |part       | 2304       | 3                | 0                | 0                 |
+------------+-----------+------------+------------------+------------------+-------------------+
Fields are as follows:
schema_name
The schema holding the table
table_name
The name of the table
pdt_mem
The amount of memory, in bytes, used by the PDTs for the table
pdt_inserts_count
The number of inserted tuples currently stored in the PDTs for the table
pdt_deletes_count
The number of tuples marked as deleted currently stored in the PDTs for the table
pdt_modifies_count
The number of modified table fields currently stored in the PDTs for the table. The maximum number is: number of tuples times number of columns.
View Query Profile Information
Information on the execution plan of recent queries can be accessed through the following predefined views:
iivwquery
iivwprof
iivwprof_last
To access a profiling view
1. Connect to the database. For example:
sql mydb
Note:  If connecting to a remote server, you must use vnode syntax. For details, see Connecting to a Remote Server.
2. Run a query.
3. Select data from the desired profiling view. For example:
SELECT * FROM iivwprof_last
Note:  Views with the name iivwprof_*_global (for example iivwquery_global) contain information on all sessions (not only the current session). Views iivwquery_global and iivwprof_global have an additional attribute “session_id”, which is an 8-byte integer.
iivwquery View
The iivwquery view provides information about the recently executed queries. It provides the following attributes:
query_id
Internal query ID. Can be used to identify information in other profiling tables.
start_time
The time the query started, in seconds
execution_time
The time taken to execute the query, in seconds, excluding the SQL parsing and optimization phases
query_text
The first characters of internally constructed query
iivwprof View
The iivwprof view contains the main information about the execution stages of recently executed queries. It contains the following attributes:
node_id
Internal ID of the node on which a given operator was executed.
query_id
Internal query ID for a given query.
Note:  Multiple records from the same query presented in the iivwprof table will have the same query_id.
op_id
Operator ID within a given query. Operators are individual processing stages of a query.
name
The name of a given operator. The indentation in the name is used to present the parent/child relationship.
ticks
The time spent in a given operator, in CPU clocks. Can be used to identify the most time-consuming operators.
tuples
The number of tuples produced by a given operator. Can be used to detect “exploding” data plans, as in the case of an unintended Cartesian product.
himem
The peak memory usage of a given operator in bytes. Can be used to identify the most memory-consuming operators.
data_blocks
The number of data blocks a given operator consumed. Data blocks are discussed in [cbm] block_size parameter and I/O Settings in the System Administrator Guide.
Note:  This information is provided for MScan (table scanning) operators only, not for disk-spilling operators.
async_io
The number of blocks for which the asynchronous data loading was performed. For details, see Interpreting I/O Information in IIVWPROF View.
sync_io
The number of blocks for which the synchronous data loading was performed. For details, see Interpreting I/O Information in IIVWPROF View.
Interpreting I/O Information in IIVWPROF View
The data_blocks, async_io, and sync_io attributes in the iivwprof view are related and correspond to how I/O is performed in Vector.
Data is divided into I/O blocks (as configured on the [cbm] block_size parameter). Whenever the query processing layer needs to access a given data block, a data_blocks counter is increased. If this block is not in memory, this causes a synchronous (blocking) load request, increasing the sync_io counter. At the same time, if the query knows it will require additional blocks in the future, an asynchronous (non-blocking) load (sometimes called look-ahead load or prefetching) for some future blocks is issued (this allows overlapping processing and I/O). If these blocks are not in memory, the async_io parameter is increased. Note that if the “synchronous” request for the same block is issued before the “asynchronous” look-ahead request is finalized, the sync_io counter for that block will also be increased.
This logic translates to the following “rules of thumb:”
If async_io is zero (or low), the entire (or most) of the processed data is in memory and no I/O was performed. This is often a desired situation from the performance perspective.
If async_io is high, close to data_blocks, the buffer pool might not be able to keep the used data in memory, causing extra I/O. Increasing [cbm] bufferpool_size may help.
If sync_io is low, the “asynchronous” look-ahead loads are fulfilled fast, before data blocks are actually needed for processing. This means the query is CPU-bound.
If sync_io is high, the I/O subsystem is not fast enough to fulfill the “asynchronous” look-ahead requests, making the query I/O-bound. This can often be addressed by using a storage system providing a higher I/O bandwidth.
iivwprof_last View
The iivwprof_last view contains the same information as the iivwprof view, but for the last query only.
Examples of Using Query Profiling Information
These examples of using query profiling views use the DBT-3 schema and dataset of size 1 GB. The section How to Run the Benchmark describes how to create and load this dataset.
Let us run the following simple query:
SELECT r_name FROM region WHERE r_name LIKE 'A%'\g
Executing . . .
 
+-------------------------+
|r_name                   |
+-------------------------+
|AFRICA                   |
|AMERICA                  |
|ASIA                     |
+-------------------------+
(3 rows in 0.034527 secs)
We can now see that this query appeared in the “iivwquery” view:
SELECT query_id, start_time, execution_time FROM iivwquery\g
Executing . . .
 
+-------------+-------------------------------------------------+--------------+
|query_id     |start_time                                       |execution_time|
+-------------+-------------------------------------------------+--------------+
|           29|2012-12-12 05:18:45.480209                       |      0.000833|
+-------------+-------------------------------------------------+--------------+
(1 row in 0.003182 secs)
In this view, we see queries from the current session only. Queries can be identified, for example, by the “start_time” attribute. We see that this query has an ID of “29”.
We can now select the profiling information for this query:
SELECT query_id, op_id, name, ticks, tuples, himem FROM iivwprof WHERE query_id = 29\g
Executing . . .
 
+-------------+------------+--------------------+---------------+---------------+---------------+
|query_id     |op_id       |name                |ticks          |tuples         |himem          |
+-------------+------------+--------------------+---------------+---------------+---------------+
|           29|           1|  MScan(region)     |         181224|              5|         146439|
|           29|           2| Select             |           6091|              3|          16271|
|           29|           3|Project             |           1542|              3|          16271|
+-------------+------------+--------------------+---------------+---------------+---------------+
(3 rows in 0.035141 secs)
We see that three Operators are used by this query. The “MScan(region)” operator (with op_id 1) scanned the “region” table and produced 5 “tuples”, as this table contains 5 records. The Select operator selected records matching our selection filter, producing 3 tuples. Finally, the Project operator was used to format the result.
Looking at the “ticks,” we see that Project and Select consume extremely little time (keep in mind that 1 tick on a 2 GHz machine is only 0.5 nanosecond). The MScan operator uses slightly more, as it needs to perform more complex actions, such as checking if the data is in memory and decompressing it. Similarly, the memory usage is trivial for Project and Select, and slightly higher for MScan.
For a more complex example, let’s run this query:
SELECT COUNT(*) FROM lineitem JOIN orders ON l_orderkey = o_orderkey WITH MAX_PARALLEL 1\g
Executing . . .
 
+----------------------+
|col1                  |
+----------------------+
|               6001215|
+----------------------+
(1 row in 0.764545 secs)
After looking up the query_id for this query (as shown in the previous example), we can see the profiling information for this query by running:
SELECT query_id, op_id, name, ticks, tuples, himem FROM iivwprof WHERE query_id = 31\g
Executing . . .
 
+-------------+------------+--------------------+---------------+---------------+---------------+
|query_id     |op_id       |name                |ticks          |tuples         |himem          |
+-------------+------------+--------------------+---------------+---------------+---------------+
|           31|           1|       Scan(lineitem|      121779356|        6001215|         113897|
|           31|           2|      MScan(orders) |       34302310|        1500000|         113897|
|           31|           3|     HashJoinN      |     1847879241|        6001215|       60045807|
|           31|           4|    Aggr(DENSE)     |        4883348|              1|         196156|
|           31|           5|   Project          |          46154|              1|          16271|
|           31|           6|  As(IISAGG2_0)     |           1079|              1|          16271|
|           31|           7| Reuse              |           7377|              0|          16271|
|           31|           8|Project             |           3019|              1|          16271|
+-------------+------------+--------------------+---------------+---------------+---------------+
(8 rows in 0.002574 secs)
We focus on operators 1 through 4. (Operators 5 through 8 are “management” operations, which we will ignore here.) There are two MScan operators scanning the “lineitem” and “order” tables and producing respectively 6 million and 1.5 million tuples. These tables are combined in the HashJoinN operator, responsible for performing the JOIN construct in SQL. To perform that operation, a significant amount of memory is used—about 60 MB. This operator also consumes most of the query time. See that the output count of the HashJoinN operator matches the count of the “lineitem” table, because every tuple matches something in the “order” table. Finally, the count of the join result is counted in the Aggr operator.
Configure Query Profiling
By default, query profiling is turned on. The profiling feature can be turned off by setting the [server] profiling parameter to false.
The maximum number of profiles to be kept in memory can be configured using the [server] max_profiles_per_database and max_profiles_per_session configuration parameters.
System Catalogs for Query Profiling Views
The views that provide profiling information on Vector queries are based on the system catalogs iivwprof_query, iivwprof_op, iivwprof_io, iivwprof_stage, iivwprof_expr, and iivwprof_parse_tree. For more information, see “System Catalogs” in the SQL Language Guide.