Working With the ibtop Tool

A description of all command options available when running ibtop can be found by running the ibtop --help command. For example:

# ibtop --help

ibtop collects the statistics information of the running Hyperstage instance, which include the following:

• /proc/[pid]/stat for CPU/Memory usage under Linux or Windows equivalent.
• show status, such as 'IB%' (MySQL) or show Hyperstage statistics (postgres).
• show engine Hyperstage status (MySQL) or show ibengine status (postgres).
• (IMM only) additional multi-machine specific metrics.

You will not see the output on the screen. All collection goes to file specified by --output-file/-o or --output-dir with a name convention.

Command options include the following:

-h [ --help]

Shows the command usage message.

-H [ --host ] arg

The host on which the IB instance is running. The default is 127.0.0.1.

-P [ --port ] arg

The port number of the IB instance. The default is 5029.

-L [ --login ] arg

The sign-in username. Make sure it has the permission to show specific IB status and statistics. If left empty, ibtop will use postgres for the Hyperstage-PG instance, and root for the Hyperstage-MySQL instance.

-p [ --password ] arg

The sign-in password.

-D [ --database ] arg

The database to connect. If left empty, Hyperstage-PG will use template1, and Hyperstage-MySQL will use information_schema.

-S [ --server-type ] arg

The IB instance type. Valid options are:

• mysql
• postgres

The default value is postgres.

-o [ --outputfile ] arg

The output file name.

-i [ --interval ] arg

The interval, in seconds, between each collection. It can be as frequent as one (1) second. The default value is 60 seconds.

-f [ --flush-on-intervals ] arg

ibtop will keep collection x intervals, and flush into the output file. The default is 60 intervals for each flush.

-R [ --output-directory ] arg

The directory to hold the output files (JSON or CSV format). The default is /tmp (C:\tmp for Windows OS). If output-file is specified, output-file takes precedence. Otherwise, ibtop will use the following name convention:

/tmp/hostname.YYYYMMDD.HHMMSS.SERVERTYPE_HOST_PORT.hyperstage.[gpe | csv].

The .gpe file extension is in JSON format, if --enable-json-output is specified.

-q [ --skip-header ]

Skips header column names when the report is in CSV format. (This does not apply to JSON format.)

-b [ --abort-on-error ]

Aborts collection on error (for example, a lost connection to the underlying IB instance). If left empty, ibtop keeps trying indefinitely to reconnect, and all values in collection will be 0.

-G [ --enable-json-output ]

Enables JSON format output, while turning off CSV output. For details on JSON output format, see Format of JSON Output.

-g [ --debug ]

Debug mode. Shows more verbose messages to help the ibtop developer.

-c [ --config-file ] arg

The configuration file path.

-v [ --version ]

Shows the current ibtop version.

The ibtop tool can be run either remotely or locally on the same server that the Hyperstage database engine is running. To run ibtop and collect database instance metrics, enter a command, such as the following:

$ ibtop -H 192.168.20.105 -P 5030 -L root -S postgres -i 1 -f 10 -o /tmp/colo105.ibtop.csv 

Based on the above command, after 10 seconds (1-second (interval) * 10 (flush-on-interval)), information will be written to the file /tmp/colo105.ibtop.csv.

Note: Once output is collected, it can be opened directly in any spreadsheet software (CSV), json reader (JSON), or loaded into a database like Hyperstage for analysis. By default, for CSV, the first line contains the column header. You can omit the column header by specifying --skip-header=yes in the command line.

$ ibtop --config-file=ibtop.cnf

###==== begin of ibtop.cnf =====
#host=127.0.0.1 
#port=5029 
#login= 
#password= 
#database= 
#server-type=postgres 
#output-file= 
#interval=60 
#flush-on-intervals=60 
#output-directory=/tmp 
#skip-header=no 
#abort-on-error=no 
#enable-json-output=no 
###==== end of ibtop.cnf =====

ibtop is able to optionally collect database process CPU/Memory utilization information from the operating system. To enable this functionally, the following must be true:

• ibtop must be run locally.
• For Linux operating systems, ibtop must be run using the same OS user at the database instance (for example, postgres) or as a super user (for example, root).
• For Windows operating systems, ibtop must be run as a super user (for example, administrator).

Note: Under Linux, the information collection is achieved by executing the cat /proc/[IB instance pid]/stat command. Under Windows, the information collection is achieved by issuing the GetProcessTimes() and GetProcAddress() function calls.

Inside the Hyperstage engine, a global data cache is accessible by all threads. The global data cache consists of three heaps:

• Main Heap
• Large Temporary Heap
• System Heap

All IB data structures are inherited from a base class called TrackableObject, with the acronym TO. All data structures will allocate on one of three heaps. The type of TrackableObject could be one the following listed examples:

• TO_PACK: The actual compressed/decompressed data pack. By default, each pack contains 65536 elements for a column.
• TO_SPLICE: Metadata information including min/max/null/sum of each pack. Because a single DPN structure is small, the engine will store them in splices.
• TO_RSINDEX: Mirror of files from BH_RSI_Repository. It can be CMAP (Character Map) for a string column or HIST (Histogram) for a numeric column.
• TO_FTREE: Mirror for lookup dictionary file (for example, $datadir/table.bht/TA#####.dic)
• TO_SORTER: Temporary structure used by a query when sorting is needed.
• TO_CACHEDBUFFER+TO_INDEXTABLE: Temporary structure used by a query.
• TO_FILTER: Delete mask.
• TO_TEMPORARY: Miscellaneous temporary structure used by a query, (for example, aggregation work buffer, join buffer, and so on).
• others: everything else.

ibtop collects the above heap and TO metrics for both size (in bytes) and block count.

The following tables describe all the information that is collected by ibtop.

ibtop Information - General

ibtop Information - DB Instance CPU/Memory usage from the OS

ibtop Information - from the show Hyperstage statistics command

ibtop Information - from show ibengine status command

When ibtop option enable-json-output=yes, the output file is in JSON format. Each JSON output file has two level-1 sections: meta and data.

• The meta section contains 1 variable named internval. This value is equal to the input variable --interval.
• The data section contains a series of key-value collections. The first key is the timestamp. The value of this key is a nested structure of statistics. The content of this structure is similar to the following:

  ibtop ->
        instance_unique_id ->
                     trend
                     tag
                     config

To generate instance_unique_id, ibtop concatenates server_type, IP, and port. This combination provides a unique identifier in the event of monitoring or comparing two distinct ibtop outputs.

In the nested structure for a given instance_unique_id, ibtop collects the following:

• trend: The collected metrics. This is the same set of data as CSV output.
• tag: The fixed element. This value will always be IBTOP@HYPERSTAGE.
• config: The Hyperstage instance configuration parameters. For example, ServerMainHeapSize.

An example of JSON output is shown below:

{
     "meta": {
         "interval": 1
     },
     "data": {
         "2016-05-02 17:27:56 +0000": {
             "ibtop": {
                 "postgres_127_0_0_1_5029": {
                     "trend": {
                        "gdc_false_wakeup": 0, 
                        "gdc_hits": 0, 
                        "gdc_load_errors": 0, 
                        "gdc_misses": 0, 
                        "gdc_pack_loads": 0, 
                        "gdc_prefetched": 0, 
                        "gdc_read_wait_in_progress": 0,
                        "gdc_readwait": 0, 
                        "gdc_redecompress": 0, 
                        "gdc_released": 0, 
                        "mm_alloc_blocs": 0, 
                        "mm_alloc_objs": 0, 
                        "mm_alloc_pack_size": 0, 
                        "mm_alloc_packs": 0, 
                        "mm_alloc_size": 0, 
                        "mm_alloc_temp": 0, 
                        "mm_alloc_temp_size": 0, 
                        "mm_free_blocks": 0, 
                        "mm_free_pack_size": 0, 
                        "mm_free_packs": 0, 
                        "mm_free_size": 0, 
                        "mm_free_temp": 0, 
                        "mm_free_temp_size": 0,

                        "mm_freeable": 0, 
                        "mm_release1": 0, 
                        "mm_release2": 0, 
                        "mm_release3": 0, 
                        "mm_release4": 0, 
                        "mm_reloaded": 0, 
                        "mm_scale": 5, 
                        "mm_unfreeable": 4, 
                        "readbytes": 0, 
                        "readcount": 0, 
                        "writebytes": 0, 
                        "writecount": 0, 
                        "largetemporaryheap_block": 0, 
                        "largetemporaryheap_size": 0, 
                        "mainheap_block": 12, 
                        "mainheap_size": 4, 
                        "numthreads": 0, 
                        "pid": 0, 
                        "systemheap_block": 0, 
                        "systemheap_size": 0, 
                        "systemcpu": 0, 
                        "cachedbuffer_indextable_block": 0, 
                        "cachedbuffer_indextable_size": 0,

                        "filter_block": 6, 
                        "filter_size": 0, 
                        "ftree_block": 0, 
                        "ftree_size": 0, 
                        "pack_block": 4, 
                        "pack_size": 0, 
                        "rsindex_block": 0, 
                        "rsindex_size": 0, 
                        "sorter_block": 0, 
                        "sorter_size": 0, 
                        "splice_block": 1, 
                        "splice_size": 0, 
                        "temporary_block": 0, 
                        "temporary_size": 0, 
                        "usercpu": 0, 
                        "vmrss": 0, 
                        "vmsize": 0, 
                        "cache_folder_size": 0, 
                        "other_block": 1, 
                        "other_size": 4 
                    }, 
                    "tag": {
                        "add": [
                            "IBTOP@HYPERSTAGE"
                        ]
                    },

                    "config": { 
                        "CfgName": "postgres_127_0_0_1_5029", 
                        "CfgCollectionInterval": "1", 
                        "CacheFolder": "cache", 
                        "ConnectTimeout": "5", 
                        "FET": "0", 
                        "FETInterval": "0", 
                        "HandshakeTimeout": "15", 
                        "IBEngineRevision": 
                        "Hyperstage_4.8.3_r35390_36166", 
                        "KNFolder": "BH_RSI_Repository", 
                        "KNLevel": "1", 
                        "LicenseFile": "hyperstage.lic", 
                        "LoaderSaveThreads": "16", 
                        "LogLevel": "W", 
                        "LogRotateFiles": "9", 
                        "LogRotateSize": "250", 
                        "MemoryHardLimit": "0", 
                        "MemoryLargeTempPercentage": "20", 
                        "MemoryScaleReduction": "0", 
                        "ParallelAggrThreads": "1024", 
                        "ParallelJoinThreads": "1024", 
                        "ParallelScanDPsAtOnce": "1", 
                        "ParallelScanDPsPerThread": "10", 
                        "ParallelScanThreads": "1024", 
                        "ParallelSortThreads": "1024",

                        "PeerCommitTimeout": "120", 
                        "PrefetchQueueLength": "18", 
                        "PrefetchThreads": "6", 
                        "ServerMainHeapSize": "8834", 
                        "ServerMainHeapThreshold": "5", 
                        "SpliceSize": "128", 
                        "SyncBuffers": "0", 
                        "ThrottleLimit": "0", 
                        "ThrottleScheduler": "0", 
                        "ses_LogLevel": "" 
                    }
                }
            }
        },
        "2016-05-02 17:27:57 +0000": {...}, 
        "2016-05-02 17:27:58 +0000": {...}, 
        "2016-05-02 17:27:59 +0000": {...}
    }
}

When the ibtop output file is in CSV format, which is the default, one option for further analysis is to load the data into a Hyperstage table. You can do this by accommodating the create table syntax. An example of this is shown below:

create table ibtop_collection_hyperstage ( 
"timestamp" varchar(32), 
uniqueid varchar(64), 
pid int, 
numthreads int, 
usercpu int, 
systemcpu int, 
vmsize int, 
vmrss int, 
ib_gdc_false_wakeup int, 
ib_gdc_hits int, 
ib_gdc_load_errors int, 
ib_gdc_misses int, 
ib_gdc_pack_loads int, 
ib_gdc_prefetched int, 
ib_gdc_read_wait_in_progress int, 
ib_gdc_readwait int, 
ib_gdc_redecompress int, 
ib_gdc_released int, 
ib_mm_alloc_blocs int, 
ib_mm_alloc_objs int, 
ib_mm_alloc_pack_size int, 
ib_mm_alloc_packs int, 
ib_mm_alloc_size int, 
ib_mm_alloc_temp int, 
ib_mm_alloc_temp_size int, 
ib_mm_free_blocks int, 
ib_mm_free_pack_size int, 
ib_mm_free_packs int, 
ib_mm_free_size int, 
ib_mm_free_temp int, 
ib_mm_free_temp_size int,

ib_mm_freeable int, 
ib_mm_release1 int, 
ib_mm_release2 int, 
ib_mm_release3 int, 
ib_mm_release4 int, 
ib_mm_reloaded int,
ib_mm_scale int, 
ib_mm_unfreeable int, 
ib_readbytes int, 
ib_readcount int, 
ib_writebytes int, 
ib_writecount int, 
system_heap_total_size int, 
main_heap_total_size int, 
large_temporary_heap_size int, 
to_pack_objects_size int, 
to_sorter_objects_size int, 
to_cachedbuffer_to_indextable_objects_size int, 
to_filter_objects_size int, 
to_rsindex_objects_size int, 
to_splice_objects_size int, 
to_temporary_objects_size int, 
to_ftree_objects_size int, 
other_objects_size int, 
system_heap_total_block int, 
main_heap_total_block int, 

large_temporary_heap_block int, 
to_pack_objects_block int, 
to_sorter_objects_block int, 
to_cachedbuffer_to_indextable_objects_block int, 
to_filter_objects_block int, 
to_rsindex_objects_block int, 
to_splice_objects_block int, 
to_temporary_objects_block int, 
to_ftree_objects_block int, 
other_objects_block int, 
cache_folder_size int ) with (engine=hyperstage);