DX Infrastructure Management

Expand all | Collapse all

How to tune UMP with MySQL DB

  • 1.  How to tune UMP with MySQL DB

    Posted 06-08-2018 05:48 AM

    We are using UIM with MySQL database and have performance problems with UMP. I found suggestion to tune, but all of it applies for MY SQL Database. Has anybody experience how to tune the MySQL Database?

    One observation is - the database folder contains very large number of files - abour 100 times as many as tables in the database. Also, the mysql probe is reporting "too many temporary tables". I wonder if all this has an influence on the UMP performance?

    Also, we use custom-made (external) CABI dashboards, to invoke them in UMP take ages - has anybody experience how to tune this?

    Many thanks

    Richard



  • 2.  Re: How to tune UMP with MySQL DB

    Posted 06-08-2018 06:52 AM

    Hi Richard,

     

    The following article contains some information on how the data_engine works and some tuning options, including MySQL:

     

    UIM Why do the UIM database tables become so fragm - CA Knowledge 

     

    Have you checked the UIM documentation on installing the product on MySQL? There's a section with parameters for large environments:

     

    Install and Configure Your Database Software - CA Unified Infrastructure Management - 8.5.1 - CA Technologies Documentat… 

     

    And finally some documentation from dev.mysql.com on temporary tables (see excerpt after the URL):

     

    MySQL :: MySQL 5.6 Reference Manual :: 8.4.4 Internal Temporary Table Use in MySQL 

     

    ------

    Internal Temporary Table Storage Engine

    An internal temporary table can be held in memory and processed by the MEMORY storage engine, or stored on disk and processed by the MyISAM storage engine.

     

    If an internal temporary table is created as an in-memory table but becomes too large, MySQL automatically converts it to an on-disk table. The maximum size for in-memory temporary tables is determined from whichever of the values of tmp_table_size and max_heap_table_size is smaller. This differs from MEMORY tables explicitly created with CREATE TABLE: For such tables, only the max_heap_table_size system variable determines how large the table is permitted to grow and there is no conversion to on-disk format.

    ------

     

    Please let us know whether any of the above helps.

     

    Thanks,

    Alex



  • 3.  Re: How to tune UMP with MySQL DB

    Posted 06-11-2018 05:34 AM

    Hi Alex,

    I know that document, unfortunatlely, it is entirely about MS SQL only, no hint how to handle MySQL. I also followed the installation setup and have max_tmp_tables=64.

    Where are all the files comming from?



  • 4.  Re: How to tune UMP with MySQL DB

    Posted 06-11-2018 01:28 AM

    Recent mysql uses file_per_table option so you will see may files.

    I expect you will see many tables in UIM database.

    In Linux, it would result too many file descriptors and RAM usage issues.

    I simply recommend not to use file_per_table.  



  • 5.  Re: How to tune UMP with MySQL DB

    Posted 06-11-2018 05:32 AM

    Hi,

    thanks for your suggestion, but I do not belive this is the cause  - we have about 1.000 Tables and 170.000 files. That is 170 files per table - where does it come from?



  • 6.  Re: How to tune UMP with MySQL DB

    Posted 06-11-2018 05:39 AM

    Thank you very much for input.

    A file is dedicated to one object such as table, index, and partitions.

    In UIM, qos tables (XX_QOS_DATA_YYYY) is being partitioned for every date.

    (One partition is created for today's data, another partition is created for tomorrow's data)



  • 7.  Re: How to tune UMP with MySQL DB

    Posted 06-11-2018 05:48 AM

    OK, thanks, that is an explanation. Then the number of files sounds correct.