Excellent software and practical tutorials
MySQL Write Performance Optimization Guide in CyberPanel
When using CyberPanel, you may encounter slow MySQL writes. Normally, there are no abnormalities in database writes, but when the website traffic increases, the database will become very slow.
At this time, you will find that the CPU and memory are still idle, less than 30% are occupied, and there is also surplus bandwidth. Why does database writing become slow?
The default installed database is not optimized. Let's see how to optimize the MySQL database.
Optimizing MySQL's write performance involves adjusting hardware, configuration, query design, and monitoring. Depending on the actual workload (such as high concurrent writes), optimization can improve performance by 2-10 times.
Here's how to optimize MySQL in CyberPanel:
First, install the monitoring tool: sudo apt install mysql-tuner-perl, and run perl mysql-tuner.pl to get preliminary suggestions, such as increasing innodb_buffer_pool_size.
Open the database configuration file
vi /etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld] bind-address = 127.0.0.1 # Bind to local to avoid remote abuse default_storage_engine = InnoDB # Default InnoDB (supports transactions) character-set-server = utf8mb4 # Support emoji collation-server = utf8mb4_unicode_ci
If it is not in the configuration file, add it in [mysqld].
Optimize the InnoDB buffer pool. Write bottlenecks often occur in the InnoDB log, buffer, and locks. Adjust gradually and monitor changes.
Set innodb_buffer_pool_size = 70% system RAM
innodb_buffer_pool_size = 44G innodb_buffer_pool_instances = 8 # Multiple instances reduce lock contention
In high-performance web applications, MariaDB (MySQL-compatible) as a database engine often fails to fully utilize the server's potential with its default configuration. For example, with 64GB of RAM, the default InnoDB buffer pool is only 128MB, far less than the recommended 70% of RAM (approximately 45GB). This can lead to frequent disk I/O, query latency, and write bottlenecks. According to MariaDB's official benchmark and Percona report from 2025, the TPS (transactions per second) with an unoptimized configuration is only 30% at its peak. However, with optimization, this can increase by 4-8 times, especially in high-concurrency scenarios like e-commerce or CMS.
Optimizing MySQL in CyberPanel isn't a one-time effort; it's an iterative process. Starting with basic configuration and combining it with dashboard tools and monitoring, you can significantly improve performance and avoid common bottlenecks. Remember to test load (e.g., with JMeter simulations) after optimization and run mysql-tuner regularly. By 2025, with the rise of AI-driven database tools, CyberPanel integration will become even smarter, but manual tuning will remain the foundation. Once optimized, your website will be more efficient and help your business soar.
Optimizing Excessive Disk Creation of MySQL Temporary Tables: A Practical Guide to Reducing the Threshold from 46% to 25%
In MySQL performance tuning, the location where temporary tables are created directly impacts query efficiency and resource consumption. Your current situation shows Created_tmp_disk_tables / (Created_tmp_tables + Created_tmp_disk_tables) * 100 = 46%, far exceeding the recommended threshold of 25%. This means that nearly half of these temporary tables are stored on disk rather than in memory, resulting in a surge in I/O overhead, doubling query latency, and even causing bottlenecks under high concurrency. Based on the formula (value > 25), this triggers warnings in tools like mysql-tuner. Temporary tables are often used in GROUP BY, ORDER BY, or DISTINCT operations. When memory is insufficient, MySQL automatically switches to disk (MyISAM engine). This shouldn't happen on a server with 64GB of RAM, but it can indicate suboptimal configuration or query design issues.
First, adjust the parameters simultaneously—set max_heap_table_size = tmp_table_size = 1024MB (occupies 1.6% of RAM)—by adding this line to the [mysqld] section of my.cnf , restarting MariaDB, and testing. Monitor SHOW GLOBAL STATUS LIKE 'Created_tmp%'; , aiming to reduce it to < 25%. If this doesn't work, rewrite the query: analyze slow SQL with EXPLAIN, split BLOB fields into external storage, or enable query caching. Combine this with Percona Toolkit's pt-query-digest to analyze logs and iterate on optimizations. Ultimately, not only will the temporary table memory hit rate reach over 80%, but overall performance will improve by 2-4 times, transforming your database from barely surviving to highly responsive.
tmp_table_size = 1024M max_heap_table_size = 1024M
MySQL long_query_time setting optimization: A guide to reducing 10 seconds to 1-5 seconds
In MySQL performance monitoring, the long_query_time parameter defines the slow query log threshold: queries exceeding this value are recorded as "slow queries" for subsequent analysis and optimization. Your current setting (10 seconds or longer) means that only extremely slow queries (such as complex joins or large table scans) are captured, missing many potential bottlenecks and causing performance issues to remain hidden. According to MySQL documentation and Percona best practices, the default 10-second threshold is suitable for low-load testing environments, but for production servers (with daily query volume >100,000), it makes the slow query log too sparse, failing to identify issues such as missing indexes or lock contention in a timely manner. The result is a subtle increase in database response time, a degraded user experience, and a decrease in TPS (transactions per second) of 20-50%.
Current Analysis: The long_query_time threshold of 10 is directly validated based on a formula (value >= 10), which triggers warnings in tools like mysql-tuner. The 10-second threshold ignores "medium-slow queries" of 1-5 seconds, which often offer high-return optimization opportunities (e.g., analysis using EXPLAIN). In high-traffic scenarios, it's recommended to adjust the threshold to 1-5 seconds: 1 second is suitable for real-time applications (such as e-commerce), 2-3 seconds is general-purpose, and 5 seconds is suitable for reporting workloads. Shortening the threshold will result in richer logs, but the log size (/var/log/mysql/mariadb-slow.log) should be monitored to avoid disk pressure.
long_query_time = 5 # Recommended 1-5 seconds, based on load testing slow_query_log = 1 # Enable slow query log (if not enabled) slow_query_log_file = /var/log/mysql/mariadb-slow.log log_slow_verbosity = query_plan,explain # Record execution plan log_queries_not_using_indexes = 1 # Record queries that do not use indexes log_slow_admin_statements = 1 # Record administrative statements
After tuning, the slow query log will capture more details, helping to quickly identify issues and improving overall query speed by over 30%. However, the log file will increase in size (10-100MB per day). Regularly rotate it using logrotate /var/log/mysql/mariadb-slow.log. Under high load, combine EXPLAIN and index optimization to avoid excessive logging. Regularly run mysql-tuner to check thresholds to ensure they match your actual needs (for example, adjust from 2 seconds to 3 seconds).
By making these adjustments, your MySQL database will be more agile, helping your website run more efficiently. Optimization is an ongoing process, and combining it with monitoring tools like Grafana will yield even better results.
The following is a complete MySQL configuration that can be used in a production environment
# # These groups are read by MariaDB server. # Use it for options that only the server (but not clients) should see # this is read by the standalone daemon and embedded servers [server] # this is only for the mysqld standalone daemon [mysqld] # # * Basic Settings # #user = mysql pid-file = /run/mysqld/mysqld.pid basedir = /usr #datadir = /var/lib/mysql #tmpdir = /tmp # Broken reverse DNS slows down connections considerably and name resolve is # safe to skip if there are no "host by domain name" access grants #skip-name-resolve # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address = 127.0.0.1 # # * Fine Tuning # key_buffer_size = 512M max_allowed_packet = 2G thread_stack = 192K thread_cache_size = 1024 tmp_table_size = 1024M max_heap_table_size = 1024M open_files_limit = 0 #key_buffer_size = 128M #max_allowed_packet = 1G #thread_stack = 192K #thread_cache_size = 8 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched #myisam_recover_options = BACKUP #max_connections = 100 #table_cache = 64 myisam_recover_options = BACKUP max_connections = 9999 table_open_cache = 40000 # # * Logging and Replication # # Note: The configured log file or its directory need to be created # and be writable by the mysql user, eg: # $ sudo mkdir -m 2750 /var/log/mysql # $ sudo chown mysql /var/log/mysql # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # Recommend only changing this at runtime for short testing periods if needed! #general_log_file = /var/log/mysql/mysql.log #general_log = 1 # When running under systemd, error logging goes via stdout/stderr to journald # and when running legacy init error logging goes to syslog due to # /etc/mysql/conf.d/mariadb.conf.d/50-mysqld_safe.cnf # Enable this if you want to have error logging into a separate file #log_error = /var/log/mysql/error.log # Enable the slow query log to see queries with especially long duration #log_slow_query_file = /var/log/mysql/mariadb-slow.log #log_slow_query_time = 10 #log_slow_verbosity = query_plan,explain #log-queries-not-using-indexes #log_slow_min_examined_row_limit = 1000 long_query_time =5 # Recommended 1-5 seconds, based on load testing slow_query_log = 1 # Enable slow query log (if not enabled) slow_query_log_file = /var/log/mysql/mariadb-slow.log log_slow_verbosity = query_plan,explain # Record execution plan log_queries_not_using_indexes = 1 # Record queries not using indexes log_slow_admin_statements = 1 # Record administrative statements # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replica, see README.Debian about other # settings you may need to change. #server-id = 1 #log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 #max_binlog_size = 100M # # * SSL/TLS # # For documentation, please read # https://mariadb.com/kb/en/securing-connections-for-client-and-server/ #ssl-ca = /etc/mysql/cacert.pem #ssl-cert = /etc/mysql/server-cert.pem #ssl-key = /etc/mysql/server-key.pem #require-secure-transport = on # # * Character sets # # MySQL/MariaDB default is Latin1, but in Debian we rather default to the full # utf8 4-byte character set. See also client.cnf character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci # # * InnoDB # # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! # Most important is to give InnoDB 80 % of the system RAM for buffer use: # https://mariadb.com/kb/en/innodb-system-variables/#innodb_buffer_pool_size #innodb_buffer_pool_size = 8G innodb_buffer_pool_size = 48G innodb_buffer_pool_instances = 16 innodb_log_file_size = 12G innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_io_capacity = 2000 innodb_read_io_threads = 8 innodb_write_io_threads = 8 # this is only for embedded server [embedded] # This group is only read by MariaDB servers, not by MySQL. # If you use the same .cnf file for MySQL and MariaDB, # you can put MariaDB-only options here [mariadb] # This group is only read by MariaDB-10.11 servers. # If you use the same .cnf file for MariaDB of different versions, # use this group for options that older servers don't understand [mariadb-10.11]
