• Node name
  • User information
  • Session, transaction, and statement IDs
  • Plan information
  • Operator name
  • Counter name
  • Counter value

    • Counters change from one operator to another.

    Do: Update System Config (If needed)

    You might want to change some system parameters to improve performance. Do this with caution.

    Don’t: Underestimate Data Extraction

    If your query returns a large result set, moving data to the client can take a lot of time. Redirecting client output to /dev/null still implies moving data to the client. Consider instead storing the result set in a LOCAL TEMPORARY TABLE.

    Useful Queries

    The following query checks the data distribution for a given table. This is often useful to look into a plan when no statistics are available:
    select
    projection_name, node_name, sum(row_count) as row_count, sum(used_bytes) as used_bytes, sum(wos_row_count) as wos_row_count, sum(wos_used_bytes) as wos_used_bytes, sum(ros_row_count) as ros_row_count, sum(ros_used_bytes) as ros_used_bytes, sum(ros_count) as ros_count
    from
    projection_storage
    where
    anchor_table_schema = :schema and
    anchor_table_name = :table

    group by 1, 2
    order by 1, 2;

    The following query shows the non-default configuration parameters:
    SELECT
    parameter_name, current_value, default_value, description
    FROM v_monitor.configuration_parameters
    WHERE current_value <> default_value
    ORDER BY parameter_name;

    The following query checks encoding and compression for a given table:
    SELECT cs.projection_name, cs.column_name, sum(cs.row_count) as row_count, sum(cs.used_bytes) as used_bytes, max(pc.encoding_type) as encoding_type, max(cs.encodings) as encodings, max(cs.compressions) as compressions
    FROM
    column_storage cs
    inner join projection_columns pc
    on cs.column_id = pc.column_id
    WHERE
    anchor_table_schema = :schema and
    anchor_table_name = :table
    GROUP BY 1, 2
    ORDER BY 1, 2;

    The following will retrieve the EXPLAIN PLAN for a given query:
    SELECT
    path_line
    FROM v_internal.dc_explain_plans
    WHERE
    transaction_id=:trxid and
    statement_id=:stmtid
    ORDER BY
    path_id, path_line_index;

    The following shows the resource acquisition for a given query:
    SELECT
    a.node_name, a.queue_entry_timestamp, a.acquisition_timestamp,
    ( a.acquisition_timestamp - a.queue_entry_timestamp ) AS queue_wait_time, a.pool_name, a.memory_inuse_kb as mem_kb, (b.reserved_extra_memory_b/1000)::integer as emem_kb, (a.memory_inuse_kb-b.reserved_extra_memory_b/1000)::integer AS rmem_kb, a.open_file_handle_count as fhc, a.thread_count as threads
    FROM
    v_monitor.resource_acquisitions a
    inner join query_profiles b
    on a.transaction_id = b.transaction_id
    WHERE
    a.transaction_id=:trxid and
    a.statement_id=:stmtid
    ORDER BY 1, 2;

    The following gives query events for a given query:
    SELECT event_timestamp, node_name, event_category, event_type, event_description, operator_name, path_id, event_details, suggested_action
    FROM v_monitor.query_events
    WHERE
    transaction_id=:trxid and
    statement_id=:stmtid
    ORDER BY 1;

    The following query shows transaction locks:
    SELECT node_name,(time - start_time) as lock_wait, object_name, scope, result,description
    FROM v_internal.dc_lock_attempts
    WHERE
    transaction_id = :trxid
    ;

    The following query shows threads by profile operator:
    SELECT node_name, path_id, operator_name, activity_id::varchar || ',' || baseplan_id::varchar || ',' || localplan_id::varchar as abl_id, count(distinct(operator_id)) as '#Threads'
    FROM v_monitor.execution_engine_profiles
    WHERE
    transaction_id=:trxid and
    statement_id=:stmtid
    GROUP BY 1,2,3,4
    ORDER BY 1,2,3,4;

    The following query shows how you can retrieve the query execution report:
    SELECT node_name , operator_name, path_id, round(sum(case counter_name when 'execution time (us)' then counter_value else null end)/1000,3.0) as exec_time_ms,
    sum(case counter_name when 'estimated rows produced' then counter_value else null end ) as est_rows,
    sum ( case counter_name when 'rows processed' then counter_value else null end ) as proc_rows,
    sum ( case counter_name when 'rows produced' then counter_value else null end ) as prod_rows,
    sum ( case counter_name when 'rle rows produced' then counter_value else null end ) as rle_pr_rows,
    sum ( case counter_name when 'consumer stall (us)' then counter_value else null end ) as cstall_us,
    sum ( case counter_name when 'producer stall (us)' then counter_value else null end ) as pstall_us,
    round(sum(case counter_name when 'memory reserved (bytes)' then
    counter_value else null end)/1000000,1.0) as mem_res_mb,
    round(sum(case counter_name when 'memory allocated (bytes)' then
    counter_value else null end )/1000000,1.0) as mem_all_mb
    FROM v_monitor.execution_engine_profiles
    WHERE transaction_id = :trxid and statement_id = :stmtid and counter_value/1000000 > 0
    GROUP BY 1, 2, 3
    ORDER BY
    case when sum(case counter_name when 'execution time (us)' then counter_value else null end) is null then 1 else 0 end asc , 5 desc ;

    OpenText

    , ,
    , , , ,

  • From identity provisioning to managing IoT ecosystems

    From identity provisioning to managing IoT ecosystems

    More and more enterprises have begun their journey towards digital transformation. They are creating entirely new types of digital ecosystems that include people, applications, systems and things – both inside and outside the organization. This is an exciting new world. At its heart lies a new generation of identity management technologies and mindsets.

    The pace at which organizations are embracing digital transformation is startling. The digital transformation market is estimated to reach $798.44 billion by 2025 – up from $177.27 billion in 2016. That’s an increase of over 450%. Recent research from OpenText™ into the UK Financial Services sector found that over 60% of companies were either about to or had already deployed digital transformation programs.

    The identity challenge

    Digital transformation represents a massive change in the way that companies operate and conduct business. In its report “2017: A ‘transformative’ year”, AIIM states that digital transformation means re-inventing the business “from the outside in” where customer, employee and partner experiences need to be central to digital transformation initiatives. The trade body suggests: “A new generation of customers and partners, too – requires a dramatically different approach to engagement, specifically one that is personalized, immediate, expressive, and immersive.”

    That sounds fantastic, but the stumbling block is obvious. To take advantage of the opportunities of digital transformation, you need to provide access to your digital ecosystem with the assurance that everyone is who they say they are and that they have the right access to information only when they should.

    Effective identity management becomes the key enabler for successful digital transformation. However, previous approaches to identity management have primarily delivered on helping the IT Help Desk, which is an inside-out approach to identity and access management (IAM). This traditional method applies a trade-off between application security and user convenience that cannot deliver the types of experience that AIIM suggests are necessary.

    The digital ecosystem – comprised of employees, customers, suppliers, partners and other stakeholders – involve too many applications and systems that are often not in the direct control of your IT department. On top of this, disruptive technologies such as IoT are adding new “things” to the ecosystem whose identity has to be provisioned and managed as well.

    Identity management: Responding to the challenge

    While reading a recent research report, I came across a recommendation from Gartner about how organizations should respond to the identity challenge in digital transformation: “Emphasize the benefits of risk-taking to Identity and Access Management innovators”. I’m not sure how many IT security professionals would be happy to take this approach – risk mitigation always seems better than risk taking for sensitive corporate data – and I’m not sure it’s necessary.

    Certainly, the business-to-employee (B2E) approach to identity management is fine if we limit ourselves to only employees and the systems and cloud applications that they need to connect to. I’ve written a previous blog about the need of an ‘outside-in’ model for identity. It requires a collaborative approach to delivering identity assurance – the trust that people are who they say they say are – based around a new generation and mindset of identity management. Such a platform enables you to manage the entire lifecycle of internal and external users as well as their access to all resources across your extended enterprise.

    OpenText IAM

    These platforms – like OpenText™ Core Secure Access – deliver a host of intelligent features, including digital identity management, authentication management, identity event streaming and identity analytics. You have the ability to create a single, central identity for everyone and every thing that can be synchronized across devices, applications, systems and resources. This increases convenience for the user while facilitating information governance and compliance.

    Identity of Things IIoT

    As importantly in the hyper-connected world of digital transformation, the platform goes beyond the establishment of trusted interaction between users and organizations within your digital ecosystem. It enables the secure interoperability of the different systems and things. You have an end-to-end identity infrastructure that manages access, relationships and lifecycle for every element of your digital ecosystem.

    5 key capabilities of an identity management platform

    These platforms are available today. In the case of OpenText™ Covisint, it’s the platform at the core of GM OnStar serving over 12 million people everyday. Key capabilities for an identity management platform include:

    1. Identity provisioning

    Centralizing the process of establishing digital identities for every actor on the digital ecosystem and assigning rights reduces administration and speeds up the onboarding of new users, organizations systems and devices. The most important factor in identity provisioning is the ability to move away from identity silos where each system or application has its own rights. This also make de-provisioning quicker and more effective by only having to deactivate one identity to ensure all access rights are revoked.

    2. Authentication management

    While single sign on (SSO) remains an important tool in identity management, it is no longer sufficient to meet the needs of a digital ecosystem. The vast majority of data breaches in 2017 were the result of credential based cyber attacks. The platform should be able to deliver multi-factor authentication as well as support emerging authentication technologies such as biometrics. The most advanced platform allows for adaptive and risk-based authentication as well as real-time provisioning.

    3. Identity federation

    Identity federation allows multiple organizations to provide access to users across systems and enterprises using the same identification data. The platform manages identity federation establishing a trust relationship between different parties in the ecosystem. As digital transformation progresses, identity federation capabilities grow increasingly important to establish secure and dynamic connections between people, systems, things and services.

    4. Identity governance

    It’s essential that the identity management platform you select contains integrated identity governance capabilities. It should include features such as user administration, privileged identity management, identity intelligence, role-based identity administration and analytics. You must be able to define, enforce, review and audit identity management policies and map your identity function to regulatory compliance requirements and records retention policies.

    5. IDaaS deployment

    As companies move more services to the Cloud, Identity as a Service (IDaaS) is becoming more attractive by delivering highly secure and scalable identity management services that let organizations concentrate on developing the benefits of digital transformation in a constantly evolving digital ecosystem. Recent research showed that 57% of respondents used IDaaS for single sign on and employee portals, while one third used the approach for mobility management and multi-factor authentication.

    With the new generation of identity management platforms, companies can choose to outsource their entire identity management capabilities to a trusted third-party service provider. With OpenText Core Secure Access, you can select on-premises, Cloud or hybrid Cloud deployment to suit your business and security requirements.

    If you’d like know more about how identity management underpins digital transformation, it’s a key topic at Enterprise World in Toronto in July. For a personalized and private meeting, please contact us through the website or email me directly.

    OpenText

    , , , , , ,
    , , , , , , ,

  • Why OEM (white-label) OpenText technology?

    Why OEM (white-label) OpenText technology?

    I know, you’re thinking “Whaaatt? An OEM partnership is not even possible!”

    Well, actually, it is possible and there are a variety of products available — from industry standard ISIS drivers to our analytics packages. You can check out the OpenText™ OEM page for information on all of the products we sell to OEM partners.

    So why would you consider an OEM partnership with OpenText?

    For the majority of industry-specialty vendors or structured data vendors, the advantage is simply that you don’t have to re-invent the wheel for a module that is not core to your product’s value proposition.

    Think of it this way:

    You could invest your research and development budget into building a completely bespoke content management solution, or even open source one. And, yes, you can probably make a good enough module to check off that RFI requirement or satisfy that nagging roadmap question that keeps coming up in customer advisory calls.

    But what happens when the content types change/expand/die? Do you really want to keep spending your R&D budget on upgrades to what is basically a value-add?

    Our OEM program is designed so that you can not only check off that box on the RFI and customer satisfaction questionnaire, but you also get a partner that actively invests — heavily — in products that are outside your sweet spot. In other words, you get a large R&D commit on a budget that makes sense for your roadmap.

    When you work with OpenText, we not only help fill the gap you have identified in information management, but we understand how the product can do more — which lets you focus on your roadmap planning for your embedded products.

    For example, in many transitional markets like healthcare, education, and banking, reliance on paper is slowing the transformation of these industries. Two of our key products in those spaces are OpenText™ Captiva and OpenText™ AppEnhancer (formerly known as ApplicationXtender)— both of which are available as part of our OEM program. Captiva is a top-of-the-line capture solution that can capture and digitize any type of document, email, or form, and then send it via API to any type of system of record. AppEnhancer is a content services solution that electronically stores, organizes, and manages virtually any kind of business content. It’s easy to integrate into applications as a back-end service for management of documents and extraction of data related to your processes. Oh, and by the way, these two products have out-of-the-box integration, so you can have a single embedded solution for any content to store it natively and provide access via your UI.

    How would it work?

    Let’s take healthcare as an example:

    In a perfect world, organizations would have several different information management functions combined in a single, enterprise-wide health information management system with the flexibility to manage various use cases. But, many customers still use multiple systems to handle a variety of information sources. For example, financial information may be managed by the ERP or CPOE application while imaging is handled by DICOM systems.

    To remain your customers’ preferred partner, you’re going to want to offer an application to ease the movement of information across the various administrative and clinical systems. You can either invest in several connectors or you can add the varied elements of a content services platform to your application portfolio.

    I would argue that a healthcare vendor would be better off keeping up with ever-changing clinical needs (and saving their R&D dollars) by adding elements of a white-labeled content services platform.

    There are three areas where adding AppEnhancer as a white-labeled content service provides value versus building extensions to a product offering:

    1. Cost of licensing: AppEnhancer has a flexible pricing model to ensure that OEM partners can build a full business model that incorporates the additional functionality without harming revenue potential.
    2. Maintaining permissions: Most healthcare organizations have a complex cross-departmental network of admission, transcription, and billing administration that is difficult to model without an additional layer of document and process-based security.
    3. Reducing the cost of innovation: OpenText maintains a robust API and SDK kit for AppEnhancer, taking the cost out of maintaining back-end connectivity between the document management repository and your system. As an OpenText partner, you have access to a larger portfolio of integrated products for managing information, including Captiva, OpenText™ LiquidOffice™, OpenText™ RightFax™, or OpenText™ Magellan™.

    The benefits of working with OpenText as an OEM partner means we — not you — worry about ensuring that any type of content can be made manageable by your application. Working with start-ups and large established industry veterans, our OEM team is dedicated to ensuring that we understand how our portfolio of products fit into your roadmap.

    Interested in learning more?

    Contact us for more information.

    Chris Wynder

    ,
    , , , , , ,

  • What Should I do if the Database Performance is Slow?

    What Should I do if the Database Performance is Slow?

    Troubleshoot using the following checklist if your database performance is slow.

    Check if any of the following problems exist:

    Step Task Results
    1 Is the query performance slow? If the query performance is slow, review the Query Performance checklist.

    If the query performance is not slow, go to Step 2.
    2 Is the entire database slow? If the whole database is slow, go to Step 3.

    If the whole database is not slow, your checklist is complete.
    3 Check if all the nodes are UP.
    => SELECT node_name, node_address, node_state FROM nodes WHERE node_state != 'UP';    		 If there is any node DOWN,

    • Investigate why the node is down review the Node Down checklist.
    • Restart the node.
      $ admintools –t restart_nodes –d <database> -s <nodes_address>

    If nodes restarted and the performance improved, your checklist is complete.

    If the node restarted and performance is still slow, go to Step 4.

    If the node did not restart, review the Node Down checklist.
    4 Check if there are too many delete vectors.
    => SELECT count(*) FROM delete vectors;    		 If there are more than 1000 delete vectors, review the Manage Delete Vectors checklist.

    If there are not too many delete vectors, go to Step 5.
    5 Check if epochs are advancing.
    => SELECT current_epoch, ahm_epoch, last_good_epoch, designed_Fault_tolerance, current_fault_tolerance FROM system ;    		 If epochs are not advancing, review the AHM not Advancing checklist.

    If epochs are advancing, go to Step 6.
    6 Check if one node is slower than the others.
    Run a select statement for each node in the cluster and identify the there is a slower node.
    $ `grep -P "^v_" /opt/vertica/config/admintools.conf|awk '{print $3}'|
    awk -F, '{print $1}'`; do echo ----- $host -----; date ; vsql -h
    $host -c ";select /*+kV*/ 1 ;";date ; done     		If one node is slower than the others,

    • Investigate host performance issue.
    • Restart the Vertica process on that node.

    Start:
    $ admintools –t restart_node –d <database> -s
    Stop:
    $ admintools –t stop_node –s <node_ip/Host slow>

    If all the nodes have similar performance, go to Step 7.
    7 Check if the workload is balanced across all the nodes.
    => SELECT node_name,count(*) FROM dc_requests_issued WHERE
    time > sysdate() -1 group by 1 ORDER BY 1;    		 If one node has a heavier workload, distribute the workload to all the nodes.
    Review the documentation on Connection Load balancing.

    If the workload is balanced, go to Step 8.
    8 Check if there are resource rejections.
    => SELECT * FROM resource_rejections ORDER BY last_rejected_timestamp;    		 If there are resource rejections, review the Query Performance checklist.

    If there are no significant resource rejections that justify slowness go to Step 9.
    9 Check if there are sessions in queue.
    => SELECT * FROM resource_queues;    		 If there are queries waiting for resources, go to Step 10.
    10 Check if there are long-running sessions that are using too many resources.
    => SELECT r.pool_name, s.node_name AS initiator_node, s.session_id,
    r.transaction_id, r.statement_id, max(s.user_name) AS user_name,
    max(substr(s.current_statement, 1, 100)) AS statement_running,
    max(r.thread_count) AS threads, max(r.open_file_handle_count)
    AS fhandlers, max(r.memory_inuse_kb) AS max_mem,
    count(DISTINCT r.node_name) AS nodes_count, min(r.queue_entry_timestamp)
    AS entry_time, max(((r.acquisition_timestamp - r.queue_entry_timestamp)))
    AS waiting_queue, max(((clock_timestamp() - r.queue_entry_timestamp)))
    AS running_time FROM (v_internal.vs_resource_acquisitions r
    JOIN v_monitor.sessions s ON (((r.transaction_id = s.transaction_id)
    AND (r.statement_id = r.statement_id)))) WHERE
    (length(s.current_statement) > 0) GROUP BY r.pool_name, s.node_name,
    s.session_id, r.transaction_id, r.statement_id ORDER BY r.pool_name;    		 If there is a statement running for too long and using a high proportion of the box resources, consider stopping the statement, => SELECT interrupt_statement(‘session_id’,’statement_id’);

    Upon statement cancellation, resources should be freed and performance should improve.

    If it does not improve, go to Step 11..

    If the statement does not terminate properly, contact Vertica Technical Support.
    11 Check if any transactions are waiting for locks.
    => SELECT * FROM locks where grant_timestamp is null;    		 If transactions are waiting for locks, identify lock-holding sessions and consider wait for transaction to complete or cancel the session to free locks.
    => SELECT interrupt_stament(‘session_id’,’statement_id’);
    Upon statement completion or cancellation and lock release, performance should improve.

    If it does not improve, go to Step 12.

    If the session does not terminate properly, contact Vertica Technical Support reporting a hang session.
    12 Check the catalog size in memory.
    => SELECT node_name,max(ts) as ts, max(catalog_size_in_MB) as catlog_size_in_MB FROM
    ( SELECT node_name,trunc((dc_allocation_pool_statistics_by_second."time")::TIMESTAMP,
    'SS'::VARCHAR(2)) AS ts, sum((dc_allocation_pool_statistics_by_second.total_memory_max_value - dc_allocation_pool_statistics_by_second.free_memory_min_value))/(1024*1024)
    AS catalog_size_in_MB from dc_allocation_pool_statistics_by_second group by 1,2)
    foo group by 1 ORDER BY 1 limit 50;     		If catalog is larger than the 5% of memory in the host, resource pools should be adjust to free memory needed by the catalog as Vertica process is a risk of being terminated by the kernel with OOM.

    Contact Vertica Technical Support to debug catalog size growth and discuss alternatives to free memory to be used to allocated catalog. Alternatives are:

    • Adjust general pool to use less than 95%.
    • Create an additional Pool of size of the difference needed to accommodate catalog.
    • Adjust the METADATA resource pool to free memory for the catalog

    In Many cases restarting the node could free memory used by catalog, debugging with support will help to determine the best course of action.
    13 Check usage of resident and virtual memory and maps of memory created.

    => SELECT * FROM ( SELECT time, node_name, files_open,
    other_open,sockets_open,virtual_size,resident_size,thread_count,map_count,
    row_number() over (partition by node_name ORDER BY time::timestamp desc)
    as row FROM dc_process_info ) a where row <=3 ;

    		 If Virtual Memory or resident memory is high, monitor to see if the numbers lower.

    If the numbers do not lower, contact Vertica Technical support to debug the issue.

    Restarting the nodes should resolve the issue but a proper debugging should be done, follow Catalog Size Debugging checklist.

    Learn More

    Learn more about Connection Load Balancing in the Vertica Documentation.

    OpenText

    , ,
    ,

  • What Should I do When the Database Node is Down?

    What Should I do When the Database Node is Down?

    When database node is DOWN, troubleshoot using the following checklist.

    Step Task Results
    1 Check whether your database is UP.
    $ admintools -t db_status -s UP    		 If the database is UP, go to Step 2.

    If the database if not UP, restart your database.
    $ admintools -t start_db -d <Database_name> -p <Database_password>

    If the database starts, the checklist is complete.

    If the database does not start, see the Database Process Not Starting checklist.
    2 Identify all the DOWN nodes.
    => SELECT node_name, node_address, node_state FROM nodes WHERE node_state = 'DOWN';    		 Upon identification of all the DOWN node/s, proceed to Step 3.
    3 Check whether you can establish a connection with the DOWN nodes using SSH.
    $ ssh dbadmin @<nodedown_ip>    		 If you can SSH into the node, restart Vertica process on the DOWN node.
    $ admintools -t restart_node -d <database_name> -s <node_host_name or IP>

    • If the restart was successful, the checklist is complete.
    • If the restart failed, go to Step 4.

    If you cannot SSH into the node, contact your system administrator to find if it is a port issue or a network issue.
    4 To find reasons for restart failure, on the DOWN node tail startup.log. A snippet of the tail startup.log is as follows:
    $ tail -f catalog-path/database-name/v_database-name_node_catalog/startup.log
    {
    "node" : "v_cdmt0_node0001",
    "stage" : "Database Halted",
    "text" : Data consistency problems found; Check that all file systems are properly mounted.
    Also, the --force option can be used to delete corrupted data.
    "timestamp" : "2016-07-31 18:17:04.122"
    }
    		 The log results shows the latest state of the DOWN node. Proceed to Step 5 to see these stages.
    5 If the startup.log…

    a. Remains in the
    Waiting for cluster invitestage.

    		 See the Spread Debugging Checklist.
    b. Remains in the Recovery stage. See the Node Recovery Checklist.
    c. Shows an error message, Data inconsistency problems found, restart the node with the force option.
    $ admintools -t restart_node -d Database_name -s node_name --force    		 Upon restart the checklist is complete.
    d. Shows no new data in the startup.log after last restart, check dbLog file for errors. If you cannot resolve errors, contact Vertica Support.
    e. Shows Shutdown Complete but the node is still DOWN.
    tail vertica.log and look for <ERROR> and <PANIC>.    		 Contact Vertica Support with PANIC report, ErrorReport.txt, and scrutinize.

    OpenText

    , ,
    ,

  • What Version of Vertica am I Running?

    What Version of Vertica am I Running?

    The built-in VERSION function returns a VARCHAR that contains your Vertica node’s version information.

    Example:

    dbadmin=> SELECT version();
    version
    ------------------------------------
    Vertica Analytic Database v9.1.0-2
    (1 row)

    The Vertica version is formatted as X.Y.Z-R, where…

    • X.Y is the two-digit Vertica major release number, e.g., 8.1, 9.0 and 9.1
    • Z is the service pack number, e.g., 7.2.3, 8.1.1 and 9.0.1
    • R is the hotfix number, e.g., 9.0.1-9 and 9.1.0-2

    Have Fun!

    OpenText

    , ,
    , ,

    • Stay in the loop!

    Get our most popular content delivered monthly to your inbox.

    How can we help?