The auto-adjust-mysql-limits script automatically optimizes MySQL configuration values on cPanel servers to improve database performance and prevent resource errors. This command-line tool adjusts three critical MySQL settings: open_files_limit, max_allowed_packet, and innodb_buffer_pool_size based on your server's current workload and resources.
What the Auto-Adjust MySQL Limits Script Does
The /usr/local/cpanel/scripts/auto-adjust-mysql-limits script updates MySQL configuration values for the following cPanel settings:
- open_files_limit (controls how many files MySQL can open simultaneously)
- max_allowed_packet (sets the maximum size of database queries and results)
- innodb_buffer_pool_size (determines memory allocated for InnoDB table caching)
These settings can be configured in the SQL section of WHM's Tweak Settings interface (WHM >> Home >> Server Configuration >> Tweak Settings). The script runs automatically whenever you update MySQL-related tweak settings or when you transfer or restore a cPanel account.
Important: You must run this script as the root user to make system-level MySQL configuration changes.
How to Run the Script
To execute this script on the command line, use the following format:
/usr/local/cpanel/scripts/auto-adjust-mysql-limits [options]
Available Script Options
The script supports the following command-line options:
--verbose
Displays detailed messages about the limits being adjusted when the script runs. This option is useful for troubleshooting or understanding what changes are being made to your MySQL configuration.
Example: /usr/local/cpanel/scripts/auto-adjust-mysql-limits --verbose
--help
Shows all available options and usage information for the script.
When to Use This Script
You should run the auto-adjust-mysql-limits script in these situations:
- After adding multiple new hosting accounts that increase database load
- When experiencing MySQL errors related to file limits or packet sizes
- After upgrading server hardware or adding more RAM
- Following a major change in your server's database workload
- When MySQL performance appears degraded without obvious cause
The script analyzes your current server state and applies appropriate values automatically, eliminating the need for manual MySQL tuning in most cases.
Understanding the Adjusted Settings
open_files_limit
This setting controls how many files MySQL can have open at the same time. A value that's too low causes "Too many open files" errors, while the script calculates an appropriate limit based on your current database usage and account count.
max_allowed_packet
This determines the maximum size for database queries and results. If you experience errors when importing large databases or handling blob data, this setting may need adjustment. The script sets this value based on typical usage patterns for shared hosting environments.
innodb_buffer_pool_size
This controls how much RAM MySQL allocates for caching InnoDB tables and indexes. Proper sizing significantly improves database performance. The script calculates this based on available server memory while leaving sufficient resources for other services.
Frequently Asked Questions
Do I need to restart MySQL after running this script?
The script updates the MySQL configuration files, but some changes require a MySQL restart to take effect. For production servers, schedule the restart during a maintenance window to avoid disrupting active database connections.
Can this script harm my MySQL performance?
No, the script uses conservative calculations designed for shared hosting environments. It sets values that balance performance with resource constraints. However, if you have highly customized MySQL settings, you may want to back up your current configuration before running the script.
How often should I run the auto-adjust-mysql-limits script?
The script runs automatically during account transfers and when you modify MySQL settings in WHM. For manual execution, run it after significant changes to your server's workload, such as adding many new accounts or experiencing database performance issues. Monthly execution is reasonable for growing servers.
What if the automatically calculated values don't work for my server?
You can disable the automatic adjustment options in WHM's Tweak Settings and manually configure the MySQL values. This gives you full control over open_files_limit, max_allowed_packet, and innodb_buffer_pool_size settings. Just ensure you understand the implications of your custom values.
If you need assistance with MySQL optimization or have questions about running this script, the OBHost support team is available 24/7. Contact us at https://www.obhost.net/contact or email support@obhost.org for expert help with your server configuration.
