framework/db: use HikariCP as default and improvements

[shwstppr]

Description

Allows configuring connection pool library for database connection. As default, replaces dbcp2 connection pool library with more performant HikariCP.

db.<DATABASE>.connectionPoolLib property can be set in the db.properties to use the desired library. Set dbcp for using DBCP2 Set hikaricp or <EMPTY> for using HikariCP

Per docs, if the mysql connector is JDBC2 compliant then it should use the Connection.isValid API to test a connection. (https://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html#isValid-int-)

This would significantly reduce query lags and API throughput, as for every SQL query one or two SELECT 1 are performed every time a Connection is given to application logic.

This should only be accepted when the driver is JDBC4 complaint.

As per the docs, the connector-j can use /* ping */ before calling SELECT 1 to have light weight application pings to the server: https://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-j2ee-concepts-connection-pooling.html

Types of changes

• [ ] Breaking change (fix or feature that would cause existing functionality to change)
• [ ] New feature (non-breaking change which adds functionality)
• [ ] Bug fix (non-breaking change which fixes an issue)
• [x] Enhancement (improves an existing feature and functionality)
• [ ] Cleanup (Code refactoring and cleanup, that may add test cases)
• [ ] build/CI
• [ ] test (unit or integration test code)

Feature/Enhancement Scale or Bug Severity

Feature/Enhancement Scale

• [ ] Major
• [ ] Minor

Bug Severity

• [ ] BLOCKER
• [ ] Critical
• [ ] Major
• [ ] Minor
• [ ] Trivial

Screenshots (if appropriate):

How Has This Been Tested?

How did you try to break this feature and the system with this change?

[shwstppr]

@blueorangutan package

[blueorangutan]

@shwstppr a [SL] Jenkins job has been kicked to build packages. It will be bundled with KVM, XenServer and VMware SystemVM templates. I'll keep you posted as I make progress.

[codecov[bot]]

Codecov Report

Attention: Patch coverage is 53.50877% with 53 lines in your changes missing coverage. Please review.

Project coverage is 15.57%. Comparing base (d7ca05e) to head (a2d8f96). Report is 50 commits behind head on main.

Files with missing lines	Patch %	Lines
...ain/java/com/cloud/utils/db/TransactionLegacy.java	55.45%	45 Missing and 4 partials :warning:
...c/main/java/com/cloud/upgrade/DatabaseCreator.java	0.00%	3 Missing :warning:
...n/java/com/cloud/utils/db/ConnectionConcierge.java	0.00%	1 Missing :warning:

Additional details and impacted files

@@             Coverage Diff              @@
##               main    #9518      +/-   ##
============================================
- Coverage     15.57%   15.57%   -0.01%     
- Complexity    12037    12055      +18     
============================================
  Files          5501     5506       +5     
  Lines        482219   482880     +661     
  Branches      62044    59617    -2427     
============================================
+ Hits          75125    75217      +92     
- Misses       398791   399351     +560     
- Partials       8303     8312       +9     

Flag	Coverage Δ
uitests	4.17% <ø> (+<0.01%)	:arrow_up:
unittests	16.35% <53.50%> (-0.01%)	:arrow_down:

Flags with carried forward coverage won't be shown. Click here to find out more.

:umbrella: View full report in Codecov by Sentry.
:loudspeaker: Have feedback on the report? Share it here.

[blueorangutan]

Packaging result [SF]: ✔️ el8 ✔️ el9 ✔️ debian ✔️ suse15. SL-JID 10632

[rohityadavcloud]

@blueorangutan package

[blueorangutan]

@rohityadavcloud a [SL] Jenkins job has been kicked to build packages. It will be bundled with KVM, XenServer and VMware SystemVM templates. I'll keep you posted as I make progress.

[blueorangutan]

Packaging result [SF]: ✔️ el8 ✔️ el9 ✔️ debian ✔️ suse15. SL-JID 10730

[shwstppr]

@blueorangutan test

[blueorangutan]

@shwstppr a [SL] Trillian-Jenkins test job (ol8 mgmt + kvm-ol8) has been kicked to run smoke tests

[blueorangutan]

[SF] Trillian test result (tid-11125) Environment: kvm-ol8 (x2), Advanced Networking with Mgmt server ol8 Total time taken: 50273 seconds Marvin logs: https://github.com/blueorangutan/acs-prs/releases/download/trillian/pr9518-t11125-kvm-ol8.zip Smoke tests completed. 128 look OK, 0 have errors, 11 did not run Only failed and skipped tests results shown below:

Test	Result	Time (s)	Test File
all_test_vm_strict_host_tags	Skipped	---	test_vm_strict_host_tags.py
all_test_vnf_templates	Skipped	---	test_vnf_templates.py
all_test_volumes	Skipped	---	test_volumes.py
all_test_vpc_ipv6	Skipped	---	test_vpc_ipv6.py
all_test_vpc_redundant	Skipped	---	test_vpc_redundant.py
all_test_vpc_router_nics	Skipped	---	test_vpc_router_nics.py
all_test_vpc_vpn	Skipped	---	test_vpc_vpn.py
all_test_webhook_delivery	Skipped	---	test_webhook_delivery.py
all_test_webhook_lifecycle	Skipped	---	test_webhook_lifecycle.py
all_test_host_maintenance	Skipped	---	test_host_maintenance.py
all_test_hostha_kvm	Skipped	---	test_hostha_kvm.py

[github-actions[bot]]

This pull request has merge conflicts. Dear author, please fix the conflicts and sync your branch with the base branch.

[shwstppr]

@blueorangutan package

[blueorangutan]

@shwstppr a [SL] Jenkins job has been kicked to build packages. It will be bundled with KVM, XenServer and VMware SystemVM templates. I'll keep you posted as I make progress.

[blueorangutan]

Packaging result [SF]: ✔️ el8 ✔️ el9 ✔️ debian ✔️ suse15. SL-JID 10826

[shwstppr]

@blueorangutan test

[blueorangutan]

@shwstppr a [SL] Trillian-Jenkins test job (ol8 mgmt + kvm-ol8) has been kicked to run smoke tests

[blueorangutan]

[SF] Trillian test result (tid-11202) Environment: kvm-ol8 (x2), Advanced Networking with Mgmt server ol8 Total time taken: 63749 seconds Marvin logs: https://github.com/blueorangutan/acs-prs/releases/download/trillian/pr9518-t11202-kvm-ol8.zip Smoke tests completed. 138 look OK, 1 have errors, 0 did not run Only failed and skipped tests results shown below:

Test	Result	Time (s)	Test File
test_01_create_redundant_VPC_2tiers_4VMs_4IPs_4PF_ACL	Error	771.12	test_vpc_redundant.py
test_02_redundant_VPC_default_routes	Error	1144.52	test_vpc_redundant.py

[shwstppr]

@blueorangutan package

[blueorangutan]

@shwstppr a [SL] Jenkins job has been kicked to build packages. It will be bundled with KVM, XenServer and VMware SystemVM templates. I'll keep you posted as I make progress.

[blueorangutan]

Packaging result [SF]: ✔️ el8 ✔️ el9 ✔️ debian ✔️ suse15. SL-JID 10861

[shwstppr]

@rohityadavcloud I've addressed your comment and added changes to configure the connection pool library using db.properties. (details in the description)

@blueorangutan package

[blueorangutan]

@shwstppr a [SL] Jenkins job has been kicked to build packages. It will be bundled with KVM, XenServer and VMware SystemVM templates. I'll keep you posted as I make progress.

[blueorangutan]

Packaging result [SF]: ✔️ el8 ✔️ el9 ✔️ debian ✔️ suse15. SL-JID 10863

[shwstppr]

@blueorangutan test

[blueorangutan]

@shwstppr a [SL] Trillian-Jenkins test job (ol8 mgmt + kvm-ol8) has been kicked to run smoke tests

[blueorangutan]

[SF] Trillian test result (tid-11235) Environment: kvm-ol8 (x2), Advanced Networking with Mgmt server ol8 Total time taken: 61543 seconds Marvin logs: https://github.com/blueorangutan/acs-prs/releases/download/trillian/pr9518-t11235-kvm-ol8.zip Smoke tests completed. 131 look OK, 2 have errors, 6 did not run Only failed and skipped tests results shown below:

Test	Result	Time (s)	Test File
test_08_upgrade_kubernetes_ha_cluster	Failure	475.80	test_kubernetes_clusters.py
test_03_secured_to_nonsecured_vm_migration	Error	348.20	test_vm_life_cycle.py
all_test_volumes	Skipped	---	test_volumes.py
all_test_vpc_ipv6	Skipped	---	test_vpc_ipv6.py
all_test_vpc_redundant	Skipped	---	test_vpc_redundant.py
all_test_vpc_router_nics	Skipped	---	test_vpc_router_nics.py
all_test_vpc_vpn	Skipped	---	test_vpc_vpn.py
all_test_webhook_delivery	Skipped	---	test_webhook_delivery.py

[shwstppr]

clgtm, question about long term maintenance; this will only work for known types of connection pools due to the createDataSource() implementation (and nothing else), right?

@DaanHoogland yes. Currently, only DBCP2 and HikariCP are the supported connection pooling libraries. Earlier, only DBCP2 was used but now HikariCP is the default and the users can change it back to DBCP2 (setting db.<cloud/cloud_usage/simulator>.connectionPoolLib=dbcp) using db.properties if needed.

[shwstppr]

@blueorangutan test matrix

[blueorangutan]

@shwstppr a [SL] Trillian-Jenkins matrix job (EL8 mgmt + EL8 KVM, Ubuntu22 mgmt + Ubuntu22 KVM, EL8 mgmt + VMware 7.0u3, EL9 mgmt + XCP-ng 8.2 ) has been kicked to run smoke tests

[blueorangutan]

[SF] Trillian Build Failed (tid-11253)