A Case Study on complete Application and database infrastructure assessment, gap analysis and optimization.

Optimize, Enhance:

1. Inefficient Application DBCP connection pooling
2. Recurring appointment schedule functionality delay
3. Appointments’ schedule submission functionality
4. ECR Document list function
5. Claim processing engine job

1. Configure DBCP optimal attributes in Hibernate configuration file
2. Modify group recurring procedure to run threads parallelly for user batch instead of sequentially running all users in Group
3. CIH Segregation - Implement CDC process for sending scheduled appointments to CIH
4. Tune all loops to avoid additional DB calls (Instead of making DB calls get values from hibernate entity). Fix issues with hibernate flush in Legacy session management
5. Modify database PLSQL procedure to sort result to sort data instead of application logic Fix issues with hibernate flush in Legacy session management
6. Modify claim engine procedure to process organizations parallelly utilizing multithread processing
7. Modify claim engine procedure to process activities or claims parallelly utilizing multithread processing
8. Increase cursor record fetch limit to optimal number to improve memory utilization and performance

Optimize, Enhance, Upgrade:

1. Oracle PL/SQL procedures
2. JDK version and dependent libraries
3. App → DB connectivity
4. JSP and JSF pages
5. Optimize JS and CSS code
6. EMAR screen loading performance
7. Resolve Oracle DB transactions’ hanging issue

1. Implement ORACLE_LOADER utility to process XML data for top five slow running PLSQL procedures
2. Modify procedure flow to utilize parallel processing to reduce processing time
3. Upgrade to JDK17 with latest dependent libs and JAR files
4. Implement pooling wherever possible along with upgrading JDBC drivers to latest versions, or oracle client software
5. Upgrade JSF to JSF 2.x version and utilize newly added functions to improve overall response rendering.
6. Compress long CSS or JS codes utilizing minimizer tools like YUI Compressor or Google Closure Compiler
7. Utilize dynamic content loading techniques such as AJAX or iFrames in JSP and remove unused or hidden DOM elements
8. Evaluate redo log sizes, create with larger sizes to avoid the issue (Oracle DB Checkpoints tuning)

Optimize, Enhance, Improve:

1. User experience (caused by Oracle queries’ slow response times)
2. User experience (caused by inefficient use of MariaDB shared memory resources)
3. Firewalls excessing pings
4. Heavy usage of Static Route in Core and Aggregation Switches
5. Storage infrastructure resources & reduce any overheads (Unity-380 Compute Optimization - CPU better utilization)
6. Optimize I/O Workloads across storage volumes

1. Multiple areas require further analysis and enhancements for Queries & Batch jobs optimization –
   1) Reduce overhead of SQLs with large version count
   2) Better optimizer statistics collection processes,
   3) Indexes tuning,
   4) Improve Bad “cardinality estimates”
2. Increase buffer size - innodb_buffer_pool_size should be set to 70% of available RAM on the VMs
3. Update innodb_buffer_pool_instances to higher value on high-end machines (start with the value of 4)
4. Disable/Restrict ping on firewall outside interface
5. Use Dynamic Routing (OSPFv2) to avoid administration overhead of multiple static routes - OR -
6. Replace static route with dynamic route (preferred) or add correct next hop IP
7. Create gaps in backup schedules for VMs & Filesystems backups.
8. Create two filesystems (instead of current one) to separate VMs vs. Filesystems backups and host on separate SP on Unity
9. Distribute I/O workloads across more volumes

Optimize, Enhance:

1. Enhance Database Infra standard maintenance procedures for better efficiency
2. Improve MariaDB issues triage process
3. Enhance MongoDB ReplicaSet configuration (avoid extended downtime during failover situations) & (replication latency)
4. Build solid ‘Build and Standard Operating Procedures’ for Database Infrastructure
5. Utilize scripts for managing ESXi host configuration settings efficiently
6. Maximize efficiency by utilizing templates for VMs’ deployments

1. OS, DB, and Grid infra stdout/stderr management process enhancements
2. DB backups & scheduling optimization
3. Update configuration of slow_query_log to ‘ON’ and configure log_output to the value of ‘table’
4. Node priority configuration optimization in ReplicaSet - Configure relative eligibility of members in a ReplicaSet to become a primary
5. Disable Chained replication to reduce latency across nodes. in the ReplicaSet
6. Implement monthly/quarterly agile patching process. Automate all such processes and mundane activities using automation:
7. OS, RDBMS and GRID infrastructure agile patching process implementation
8. Database DevOps practices implementation (automation)
9. Create and Implement native vSphere scripts for ESXi host management
10. Review VMs configuration and create a Gold Template for different OS images

Optimize, Enhance:

1. Improve VMs provisioning process
2. Empower Active Directory to Manage ESXi Users
3. Enhance VCenter & vSphere configuration for better manageability
4. Manage VMware ESXi Configuration with Host Profile
5. Enhanced VMWare management solution, ensuring unified visibility

1. Automate ESXi Host Imaging and provisioning process
2. Facilitate ESXi Hosts to leverage Active Directory by joining them to the domain
3. Enhance vCenter alerting - Configure vCenter Mail Server to receive critical alerts when alarm triggered
4. Improve vCenter issues triage - Change Log generation to “verbose”
5. Configure vSphere Distributed Power Management effectively
6. Implement the Proactive HA feature in VMware vCenter
7. Configure Host Isolation feature mechanism to handle management network connection losses in a vSphere HA cluster
8. Utilize VMware Host Profiles to streamline configuration and ensure consistency across ESXi hosts
9. Implement VMware Aria Operations for log management and analysis
10. Enable Predictive DRS for proactive and intelligent virtual machine resource balancing

Improve, Enhance:

1. Improve efficiency of overall Storage Infrastructure
2. Storage Infrastructure better monitoring and alerting implementation
3. Improve inefficient Storage capacity management
4. Implement better operational processes to avoid Storage Over-subscription
5. Misc. optimizations to be considered for Storage Infrastructure better efficiency and management

1. Implement native data reduction features of storage subsystem
2. Implement real-time monitoring, integrated with reporting tools, using Syslog and SNMP configurations on storage arrays
3. Convert non-thin volumes to thin-volumes
4. Currently pool is 282% oversubscribed, and as storage array has a pool of 51 TB, so capacity usage needs to be monitored, and growth should be planned accordingly
5. Configure allowed specific bandwidth limitations for Non-Prod hosts/volumes to minimize non-prod I/O usage impact to Production hosts
6. Enable SNMP Agent on storage array to help manage and monitor storage effectively
7. Configure Syslog on all storage devices
8. Eliminate single point of failure where appliance can failover to each other in case of any issue
9. Configure host I/O Limits for less critical hosts/workloads including test/dev servers to make sure they don’t impact production IOPS

Secure, Enhance:

1. Secure Application code repository credentials
2. Enhance compliance & audit processes in MariaDB
3. Running older NX-OS/IOS Code
4. Restrict Firewall Policies
5. Enhance Network security and enable Activate Lockdown Mode
6. Deactivate vSphere (MOB - Managed Object Browser), to enhance Security
7. Enhance ESXi security by managing passwords and implementing account lockout
8. Better maintain CIM interface security - restrict remote applications' access to the minimum
9. Prevent unauthorized access to ESXi hosts

1. Implement key vault secure services like HashiCorp vault, Secrethub or git-secret to store application credentials
2. Add QUERY_DCL also for auditing users’ configuration and management (grants, add users, revokes etc.)
3. Upgrade NX-OS/IOS to latest cisco safe harbor image
4. Configure rule with specific service or service groups
5. Configure the rule with source and destination specific to IP/Subnets
6. Enable Strict lockdown mode to restrict access
7. Deactivate MOB – It poses a security risk as attackers might exploit it for malicious configuration changes
8. Modify ESXi host's predefined requirements for enhanced security by adjusting the length, character class requirement, or enabling passphrases
9. Control Access for CIM-Based Hardware Monitoring Tools
10. Configure timeout settings for Idle ESXi Shell Sessions