Uyuni database optimisiation

[aaannz]

Project Title:

Uyuni database optimization

Description:

Uyuni is an open source systems management solution that can be used to manage various Linux distributions using a powerful web UI and API.

Uyuni is based on an older project without much database refactoring and optimizations and so its database design is rather old. Previous design decisions are no longer state of the art and are now rather hurting the performance.

Database was designed for more database engines which Uyuni no longer supports, for old postgresql version without useful features added in later version.

Ultimately this project aims for overall database schema refactoring and optimizations, but given scale of the project, actual deliverables will be probably smaller.

Goals:

• database in majority uses NUMERIC datatype as an ID column tied with custom sequence generator. NUMERIC is slow datatype for joins and selects and a plan how to migrate from NUMERIC to BIGINT type and autogenerated sequences instead of custom sequence generator. This includes:
  • changes in database schema
  • changes in mapped Java objects
  • migration workflow
  • performance evaluation of schema migrations
• replace VARCHARs with list constrains to ENUMs
• replace CHAR(1) to BOOLEANs

Stretch goals:

• identify cross joins and replace them by inner or left or right joins when possible
• identify possible table decompositions
• or on the other hand where decomposition went too far and hurts performance
• explore using postgresql partitioning on large tables to improve performance

Deliverables:

• The code as a PR to the Uyuni repository
• Design decisions and evaluation as a Wiki page in Uyuni Wiki

Mentor:

• @aaannz

Skills:

• Java knowledge
• Good SQL knowledge
• Familiarity with PostgreSQL

Skill Level: Medium

Project Size: Medium Sized Project (125 hours)

Get started:

• Join GitHub Discussions and chatrooms: uyuni-project/devel uyuni-project/users
• Get familiar with uyuni
• Set up a development Uyuni server VM, e.g. with sumaform
• Uyuni schema and how updates are done

[RajvardhanT7747]

@aaannz interested to work on this issue
But new to open source platform need your help

[aaannz]

Hi @RajvardhanT7747 and welcome,

see wiki/Uyuni-development-in-no-time for step-by-step instructions how to quickly setup an Uyuni system and get little familiar with it.

Feel free also to join the devel channel on gitter to ask questions.

[RajvardhanT7747]

Sure sir ,
Thank you sir for support