Idea ID: 1637353

uCMDB: SELECT * FROM INFORMATION_SCHEMA.TABLES ... is inherently slow on Postgre

Status : Declined
over 3 years ago

Indicator:

In ../ucmdb_logs/server/cmdb.dal.slow.log you can see quite some entries like this
2018-01-17 01:01:01,432 INFO [UCMDB - scheduler for customer 1, id name: Default Client] - 1215ms 1354742674 SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name=lower(?) and (table_schema=current_schema() or table_type like '%TEMPORARY%'); Values: 'HDMR_DBEXTENT_1'

The statement is called est 160 - 200 times a day.

This is a catalog query so one would expect that this is fast i.e. 1ms or even less.

Analysis:

INFORMATION_SCHEMA.TABLES is a quite complex view in Postgres and according to voices on the web this is inherently slow. See e..g.
https://www.postgresql.org/message-id/ce8eaea2-3008-8cc1-fa39-129e9e82eaa2%40pritambaral.com

Enhancement:

Check whether the check could be rewritten and replace the SQL statement by a rewritten, faster one.. E.g. that it uses pg_tables or one of the base catalog tables.

  • This was declined with a good reason.

    • * First in newer Postgres versions access to catalog has been greatly enhanced..
    • the real root cause behind the behavior that made me to create this enhancement was "unreliable access time to storage devices". Postgres data was stored on a NFS server which operating claimed and demonstrated to have good performance. A series of pg_bench benchmarks performed at random times throughout the day revealed that sometimes performance was acceptable and very often performance dropped below the "what the heck is going on" barrier.

    Those bad effects were completely resolved after the NFS storage had been moved to a different location (here a NetApp appliance).

  • We already did a Postgresql upgrade in the latest version and this is to be used only for small deployments according to the Sizing Guide.

  • Incorrrectly logged as Idea. This may be a UCMDB performance defect. Checking iwth customer if Postgres deployed in supported environment

    UCMDB does support PostgreSQL in production, but not for Enterprise deployments (which require LARGE db deployments) - https://docs.microfocus.com/itom/Universal_CMDB:11.0/SupportMatrix/1.03.0_Databases#PostgreSQL_System_Requirements

     

  • Product is uCDMB 10.33. Suite versions are SMA-SM 2017-07 and 2017-11

    The logfile cmdb.dal.slow.log is produced on uCDMB server.

    ../ucmdb_logs/server/cmdb.dal.slow.log indicates the shared location in the suite where the logs are gathered. (Slightly varying with suite version.)

    The query is called by uCDMB server. For the purpose I can only speculate.

    It is important to notice that this optimization is Postgres specific, similar looking catalog queries on MSSQL or Oracle have a neglegtable execution time

    (and I am almost sure that a similar speed can be achieved on postgres).

  •  can you please add more details here? Where is this being called from, and to do what?