How to increase Database memory usage?

joergschaberjoergschaber Vertica Customer ✭
in General Discussion

Hi,

yesterday we updated our Vertica database from 26.1.0-2 to 26.2.0. After the update the observed a significant loss of database performance, i.e. queries run extremely slow, affecting critically our business.
Analysing the issue, we observe a drastic drop in memory usage, from 37% to 4%. From our side there were no changes in terms of resource pools or general database usage.
Any idea what the reason might be? Can we increase the memory usage again?

Regards,
Jörg

Answers

  • moshegmosheg Vertica Employee Administrator

    My strongest suspicion would be one of the following:

    • MAXQUERYMEMORYSIZE or MAXMEMORYSIZE changed
    • PLANNEDCONCURRENCY increased
    • The effective memory available to the GENERAL pool was reduced
    • Metadata/catalog memory is now reducing the memory available to GENERAL
    • Maybe some queries are spilling because memory grants are too small

    For an urgent production issue, I would recommend opening a support ticket and including the following outputs:
    SELECT version();

    SELECT *
    FROM resource_pools
    ORDER BY name;

    SELECT *
    FROM resource_pool_status
    ORDER BY node_name, pool_name;

    SELECT *
    FROM configuration_parameters
    WHERE parameter_name ILIKE '%memory%'
    OR parameter_name ILIKE '%metadata%';

    If available, also include the EXPLAIN plan and qprof output for one affected slow query, ideally from both before and after the upgrade. This should help determine whether the performance degradation is caused by resource-pool or memory-grant changes, or by a different optimizer plan after the upgrade.

This discussion has been closed.