cPanel and Percona – Should you replace MySQL? Here’s all your questions answered
From WordPress to Magento, almost all popular web apps use MySQL as their default database. While MySQL is easy to use and administer, it causes frequent performance issues in web hosting servers. Many website owners and web hosting providers now use high performance MySQL alternatives such as MariaDB or Percona.
Percona server started gaining popularity in 2013, as a high performance, high availability alternative to MySQL that has features comparable to the MySQL Enterprise version. Though cPanel does not officially support Percona, there are ways to make it work flawlessly in cPanel/WHM servers.
Today, we’ll see how to decide if you need Percona, and if so, how to go about it.
Is Percona needed in your cPanel server?
cPanel is the default server management tool provided by many hosting companies. If you have a business website hosted in a VPS or a Dedicated server, chances are you’d be using cPanel and MySQL (if you haven’t switched it already). As a rule of thumb, if you see frequent server load issues due to MySQL, your server might perform better with Percona.
[ Use your time to build your business. We’ll take care of your customers. Hire our tech support specialists at affordable pricing. ]
What kind of website needs Percona?
Here are a few kinds of websites where we’ve seen Percona perform better than MySQL.
Businesses that require high uptime (eCommerce, hotels, online services, etc.)
High uptime can be ensured only with redundancy, that is, even if one server goes down, another should be able to take its place. MySQL supports master-master replication for this purpose, but they are known to produce inconsistent data (data that’s different in each server) when the cluster nodes are unable to talk to each other.
So, we recommend Percona’s XtraDB Clusters (PXC) to our customers. PXC uses Galera’s replication technology where if a database node is unable to talk to others, it won’t accept any new updates, and will remove itself from the cluster. This gives our customers a highly available database system which is 100% reliable.
Websites with traffic spikes or high traffic websites
For servers with websites with around 50k hits per month, or that see over 20-30 visitors at a time, we’ve seen that Percona delivers 40% better performance. This is possible because of two pieces of technology in Percona called “row level locking” and “Binary log Group commit”.
MySQL locks an entire database table to commit a change, while Percona locks only a single row – avoiding log query “wait times”. This is called “row level locking” or “fine grained locking”. Another technology helping fast transactions in Percona is “Binary log Group commit”. It allows multiple write transactions to happen in parallel, where as in MySQL it happens only one after another.
Businesses that handle large data volume
MySQL is a memory hog – so much so that even for moderately large databases (eg. 50 GB), it requires very high memory allocation (greater than 32 GB) to work well. Since there’s a limit on the amount of memory that can be allocated in a server, supporting very large databases is pretty difficult with MySQL.
Percona uses a technology called Dynamic Row Format (DRF) to reduce memory usage by as much as 45%. In comparison, MySQL uses a fixed-size memory approach, where memory is allocated in pre-defined blocks which leads to high memory usage. With DRF, Percona assigns only just enough memory for each piece of data, minimizing required memory, and making it possible to support larger databases in a given server infrastructure.
[ You don’t have to lose your sleep to keep your customers happy. Get the best tech support specialists to care for your customers 24/7. ]
Is Percona useful for your cPanel/WHM hosting service?
If you are a web hosting service provider, and are facing frequent MySQL related issues, you might find Percona to be a good replacement. Here are a couple of hosting scenarios where we found Percona to be better than MySQL.
How Percona can help shared hosting
In shared hosting environment, where the resource requirements are limited, the common issue with SQL server is the server management aspect and resource constraints.
Low resource usage
MySQL uses fixed size data blocks to store data. Even if the data is small, it’ll take the whole allocated space for that data type. This results in high memory usage when the number of transactions increase.
On the other hand, Percona uses an Improved memory storage engine which enables dynamic allocation of memory to data fields. Unlike the fixed implementation, each column value only uses as much space as required. With this technology, up to 45% reduction in memory usage can be achieved in shared hosting servers.
There are many reasons such as slow queries, unoptimized tables, etc. that can cause MySQL to become slow, or even crash. To troubleshoot these issues, many external tools such as mytop, mysqladmin, etc. are needed. These tools do not provide historic performance data, and a lot of time is lost waiting for a performance bottleneck to crop up again.
Percona records performance statistics on a table called INFORMATION_SCHEMA. So, when a performance issue is noticed, the erring query, table, user or visitor can be quickly identified with a few queries on this table. We support several cPanel servers that use Percona. In these servers, we’ve been able to troubleshoot performance issues in as little as 2 minutes using performance statistics.
How Percona is useful in managed application hosting
For managed application hosting services such as WordPress hosting or Magento hosting, the onus of delivering high performance is on the hosting provider. A key challenge in such a hosting is the ability to handle traffic spikes and maintain consistently fast page load speeds. Here are a few situations where we found Percona to be better than MySQL.
Handle traffic spikes better than MySQL
MySQL stores all query results in memory, and periodically writes it into the disk (called “flushing”). This causes a dip in performance. When the number of queries are high, the performance dip due to flushing can cause load load delays.
Percona uses a technology called “adaptive flushing” which do not induce a performance bottleneck. When combined with another piece of technology called “Flashcache”, where disk I/O is made faster, Percona avoids memory or I/O bottlenecks even during a sudden surge in traffic.
Faster transactions than MySQL which improves responsiveness
When updating a database, MySQL locks a whole database table, forcing other queries to wait in a “queue”. This causes page response delays for high traffic websites.
Percona uses two features called “fine grained locks” and “binary log group commit” that enables transactions to be done in parallel. By avoiding waiting, Percona achieves up to 40% better responsiveness than MySQL in high traffic websites.
High uptime through Percona XtraDB Clusters (PXC)
MySQL achieves high availability through master-slave replication and master-master replication. However, MySQL is known to produce inconsistent data during network errors.
Percona fixes this problem by using Galera replication technology, where a node that’s unable to talk to others will shut down and exclude itself from the cluster. So, we recommend managed application hosts to use PXC as a high availability database solution to ensure reliable data.
Better scalability through multi-CPU support and clustering
Percona helps to scale up SQL servers easily using its flash memory support. With the help of Flash storage devices, Percona is able to perform ten thousands of I/O operations per second, which is around 5 times improvement over the traditional MySQL server.
As it can scale up to 48 CPU cores, Percona helps to support large and heavily loaded websites that handles thousands of transactions per second, while MySQL scalability is limited to around 4 CPU cores and about 100 transactions per second.
Thus, with adequate hardware support, Percona can scale up better and support higher number of transactions than MySQL.
How to setup Percona in cPanel/WHM servers
You can achieve better performance, scalability and availability with Percona, but since it’s not supported by cPanel, there are several things to take care of before MySQL is replaced.
Steps for setting up Percona in cPanel/WHM servers
We’ve helped many cPanel/WHM server owners smoothly transition their servers from MySQL to Percona. Here’s a big picture overview of the different steps that we’ve found to be useful.
1. Feasibility audit – A well maintained cPanel server should be able to support Percona, but there are servers with old cPanel or OS versions that may not be compatible. It is even possible that older versions of Percona will not work in latest versions to cPanel. For eg. Percona 5.6 does not work with WHM 54.0. So, check for software compatibility to make sure there’ll be no road blocks.
2. Performance audit – To optimize the Percona server, it is important to accurately determine the database load on the MySQL server. For this, collect the MySQL performance data such as the number of transactions, the complexity of transactions, etc. from MySQL logs and MySQL status information.
3. Take backups of databases and config files – Before any change is made to the production server, take backups of all databases and MySQL configuration files. So, even if something goes wrong, you can quickly restore the old MySQL service.
4. Removing MySQL – Percona is a drop-in replacement for MySQL, which means it uses the same file paths and libraries as MySQL. So the next step is to remove all MySQL RPMs and binaries.
5. Preventing auto-updates – It won’t be nice if the next OS or cPanel update removes all Percona files. To prevent that, disable all auto-updates to cPanel and the operating system.
6. Percona install – Now the server is ready for Percona. Install the following packages using yum.
# yum install Percona-Server-client-57 Percona-Server-server-57 Percona-Server-devel-57 Percona-Server-shared-57 Percona-Server-shared-compat.i686
7. PHP update – After restarting the SQL server, PHP needs to be recompiled using EasyApache to ensure that Apache is linked to the right database libraries.
8. Performance tuning – As mentioned earlier, the Percona server needs to be customized for the query load on the server. Some of the commonly adjusted Percona settings are:
a. innodb_buffer_pool_size – Depending on the server’s memory capacity, this parameter should be given the maximum value possible to reduce the I/O overhead. For eg. In a database server with 32 GB memory, up to 25 GB can be used for innodb_buffer_pool_size.
b. innodb_log_buffer_size – This setting denotes the buffer memory size used to store transactions that is not written to the disk yet. Based on the volume of transactions, it should be given a value that’ll help avoid using swap memory. For eg. in a website that hit up to 3000 visits per hour, we’ve set this value was set at 128 MB. This can be optimized further by observing the “wait” time of database queries over a few days.
c. innodb-file-per-table – This setting is useful in shared server environments. It stores each table in a separate InnoDB file. It helps in easy management, storing, backup and restoration of databases. But for dedicated servers with a single application, this option is best left disabled, as it would cause a lag in performance.
d. innodb_log_file_size – This setting determines how many database changes are logged before a new log is started. For very busy write-intensive websites (such as forums) we’ve seen 120 MB to be adequate. It’s best to assign only the minimum size required, as large log size increases database recovery time in case of a server crash.
How to maintain Percona in cPanel/WHM servers
Like any other service Percona needs to be maintained to deliver high quality of service. Here are a few things that needs to be done on a regular basis to make sure everything works fine in a Percona server.
Avoiding software conflicts in cPanel upgrades
During cPanel updates or yum package updates, there are chances that the Percona server binary gets updated and it fails to be compatible with the cPanel version in the server. So all auto-updates should be switched off. Whenever an update was released for cPanel or Percona, it’s best to run these updates manually after confirming there are no conflicts.
Maintaining error free Percona backups
Traditional backup tools such as mysqldump or hot backups have an uptime penalty (to read more about MySQL backup issues, click here). So, use a non-blocking backup tool such as Percona XtraBackup to take seamless backups without impacting performance.
Many issues like server load, network errors or disk errors can cause backups to fail. It is important to closely monitor the backup process and re-run any failed backups. One failed incremental backup can cause all subsequent backups to be corrupted.
[ Focus on your core business without interruptions. Our tech support experts are here to manage your customers 24/7. ]
Monitoring and fixing performance bottlenecks or errors
Even with a high performance alternative such as Percona, new performance issues can crop up as the database grows. To maintain high up-time, keep a close eye on the database metrics, and take quick action to prevent performance issues. A few useful metrics are:
a. Percona server status – The status of the server can be checked using data from the “INFORMATION_SCHEMA” table. Resource intensive queries, busy databases, and database engine uptime can be identified using this table.
b. Memory usage – To ensure that the memory usage is within limits, the database memory status need to be monitored. The information can be obtained from /proc/meminfo and the process list can be used to detect the process that leads to high memory usage.
c. No. of connections – Using SHOW STATUS and SHOW VARIABLES MySQL queries, the number of database threads and connections to the SQL server can be identified. This information is useful to identify database abusers in a multi-user environment.
d. Deleted files and tables – It is possible that a new update can drop a table inadvertently. Deletion of files or tables can be monitored using a diagnostic tool called “pmp-check-mysql-deleted-files”.
e. Queries that take a long time to execute – Some queries can get a long time to execute because of complex join operations, poor query structure, etc. The slow-query-log can be used to identify and fix such queries.
Periodic server optimization through audit and tuning
A vast majority of the performance issues can be found out through 24/7 database monitoring. However, nothing beats a top-down audit in finding out those hard to find performance bottlenecks.
It is best to audit the database performance by checking the actual usage of database memory, the I/O usage, and the errors reported by Percona. This information can be collected from the PERFORMANCE_SCHEMA table, INFORMATION_SCHEMA table and a host of other tools.
Information such as number of dead-locks (aborted database changes), disk read errors, disk write errors, etc. can be verified to see if there are any bottlenecks. Based on these inputs, the database configuration (or sometimes the architecture) need to be modified to fix the detected bottlenecks.