MySQL Performance Optimization Guide for HMIS Administrators¶
Overview¶
This guide helps system administrators optimize MySQL database performance to improve HMIS application speed, especially for pharmacy module operations like autocomplete searches and stock management.
When to Use This Guide¶
Apply these optimizations if you experience:
- Slow autocomplete dropdowns in pharmacy sales
- Long loading times when selecting items or stock
- General application slowness during peak usage
- Database timeouts or connection issues
Before You Begin¶
Prerequisites¶
- Administrative access to the MySQL server
- Ability to restart MySQL service
- At least 1GB of available RAM on the server
- Database backup completed (recommended)
System Requirements Check¶
-
Check available memory:
You need at least 2GB total RAM for optimal performance. -
Check current MySQL configuration:
-
Verify MySQL version:
Step 1: Database Index Optimization (Automatic)¶
The HMIS system includes automatic database migration for performance indexes.
Using the Admin Interface¶
-
Access the Migration Page:
- Navigate to:
http://your-server:8080/hmis/faces/admin/database_migration.xhtml - Login with administrator credentials
- Navigate to:
-
Execute Migration v2.1.11:
- Look for "Performance optimization indexes" migration
- Click "Execute All Pending Migrations"
- Wait for completion (approximately 5-15 minutes)
-
Verify Success:
- Check that migration status shows "Executed"
- No errors in the execution log
Expected Results¶
- 50-80% faster autocomplete searches
- Improved stock filtering performance
- Better overall pharmacy module responsiveness
Step 2: MySQL Configuration Optimization (Manual)¶
Important Notice¶
⚠️ These changes require MySQL service restart and will cause temporary downtime. Plan the restart during maintenance hours.
Configuration Steps¶
1. Backup Current Configuration¶
sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.backup.$(date +%Y%m%d)
2. Calculate Optimal Settings¶
Choose your server type and apply the corresponding configuration:
Small Server (2-4GB RAM)¶
# Add to /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
# Memory optimization
innodb_buffer_pool_size = 1024M
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
# Connection optimization
max_connections = 100
query_cache_size = 64M
Medium Server (4-8GB RAM)¶
# Add to /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
# Memory optimization
innodb_buffer_pool_size = 2048M
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
# Connection optimization
max_connections = 150
query_cache_size = 128M
Large Server (8GB+ RAM)¶
# Add to /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
# Memory optimization
innodb_buffer_pool_size = 4096M
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
# Connection optimization
max_connections = 200
query_cache_size = 256M
3. Edit Configuration File¶
-
Open the configuration file:
-
Add the appropriate settings from above at the end of the file
-
Save and exit (Ctrl+X, then Y, then Enter in nano)
4. Restart MySQL Service¶
Ubuntu/Debian:¶
CentOS/RHEL:¶
Traditional init systems:¶
Step 3: Verify Optimization Results¶
1. Check Configuration Applied¶
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'max_connections';"
2. Test Application Performance¶
- Access HMIS pharmacy module
-
Test autocomplete functionality:
- Go to pharmacy sales page
- Try typing in item search boxes
- Notice improved response time
-
Monitor for any issues:
- Check for connection errors
- Verify normal application functionality
3. Performance Monitoring¶
Check these metrics weekly:
# Check database connections
mysql -u root -p -e "SHOW STATUS LIKE 'Threads_connected';"
# Check buffer pool efficiency
mysql -u root -p -e "SHOW ENGINE INNODB STATUS;" | grep -A 5 "BUFFER POOL"
Troubleshooting¶
MySQL Won't Start After Configuration Changes¶
-
Check error logs:
-
Common issues and fixes:
Insufficient memory error:
- Reduce innodb_buffer_pool_size to 512M
- Restart MySQL service
Log file size error:
- Remove old log files: sudo rm /var/lib/mysql/ib_logfile*
- Restart MySQL service
Application Still Slow¶
-
Verify migration executed:
- Check admin migration page
- Ensure v2.1.11 shows as "Executed"
-
Check available memory:
-
Review MySQL settings:
- Ensure configuration changes were saved
- Verify MySQL restart completed successfully
Connection Issues¶
If you see "too many connections" errors:
-
Temporary fix:
-
Permanent fix:
- Increase
max_connectionsin configuration file - Restart MySQL service
- Increase
Regular Maintenance¶
Monthly Tasks¶
-
Optimize database tables:
-
Check disk space:
-
Review slow query log (if enabled)
Monitor Performance Indicators¶
- Autocomplete response time: Should be under 1 second
- Page load times: Pharmacy pages should load within 3-5 seconds
- Database connections: Should stay under 80% of maximum
- Memory usage: MySQL should use allocated buffer pool efficiently
Expected Performance Improvements¶
After completing all optimizations:
- Autocomplete searches: 50-80% faster response time
- Stock filtering: 60-90% performance improvement
- Page loading: 25-50% faster overall
- User experience: Significantly more responsive interface
Support and Documentation¶
For additional technical details, refer to:
- Developer documentation in
developer_docs/database/mysql-performance-configuration.md - MySQL official documentation for your version
- Contact development team for complex issues
Safety Reminders¶
- Always backup your database before making configuration changes
- Test configuration changes during low-usage periods
- Monitor system performance after changes
- Keep backup copies of working configurations
- Document any custom modifications for future reference