Almost every open source project uses MySQL as its database. It is supported by all hosting providers, is easy to administer, and free. However, MySQL servers often face performance issues, leading to many websites looking for alternate high performance databases.

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. Today, we’ll see how Percona differs from MySQL, and if you should choose Percona for your website.

We support several websites and web hosting providers on whose systems we’ve replaced MySQL with Percona. Here are the top reasons why we used Percona in those servers.

MySQL needs a lot of memory – Percona doesn’t

MySQL with MyISAM engine can be a memory hog. The reason for this lies in the way MySQL stores data. Even for small data size, MySQL assigns a fixed storage size for memory. As the queries on a database increase, the required memory increases.  With high memory usage, more disk-based swap is used, leading to high I/O wait.

Percona, on the other hand use something called “Dynamic row format”, where data fields are given just enough memory they need. This reduces the overall memory usage, which translates to lower I/O bottlenecks, and lower instances of high server load.

Percona executes queries in parallel – MySQL doesn’t

When MySQL uses MyISAM storage engine and executes a query, it locks all the tables needed by that query, so that data is not modified by other queries. This leads to other queries waiting in a queue for the lock to be released, and causes significant delays when the query volume is high.

Percona avoid this issue by locking only a single row (aka fine-grained locking) when executing queries. Further, it uses a technology called “Binary log Group commit” where multiple transactions can be written at the same time. Taken together, these two features allow fast execution of database transactions in multi-user environments.

Unlike MyISAM engine, things become really different when MySQL uses InnoDB. Here, it supports binary log group commit feature too.

Percona has diagnostics metrics for fast troubleshooting

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.

MySQL doesn’t scale without sharding – Percona helps avoid sharding

Many websites such as eCommerce stores, market analytics apps, online publishers, etc. use large amounts of data. When these sites grow, their databases can become quite large (50 GB or more).

MySQL relies only on server memory to execute queries. When the database is large, MySQL needs proportionately large memory to function well. When the practical memory limit is exhausted, many websites split the database into multiple servers to avoid memory bottlenecks. Such splitting (aka sharding) requires extensive re-write of application code to correctly route database queries.

In contrast, Percona keeps part of the data on disk, thereby minimizing server memory usage. This helps avoid database splitting, and associated code rewrite.

[ Seeing high server loads in your MySQL server? It is possible that your server might be un-optimized. Here’s how you can fix it. ]

Should you replace MySQL with Percona?

As a rule of thumb, if you constantly experience performance or stability issues with MySQL, your site might perform better with Percona. Here are a few kinds of websites in which we’ve seen performance gains.

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. We use Percona’s XtraDB Clusters for this purpose, because it uses a database redundancy method called “master-master replication” – that is, each database in a cluster is able to act as a full database.

So, with Percona, even if one server crashes, others can take on the traffic without affecting read or write performance.

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”.

Businesses that handle large data volume

For websites that handle and store large amounts of data, Percona is a good choice as it supports large databases with its improved memory storage engine. Since 45% memory reduction is possible in Percona with its dynamic row format (DRF) for data fields, it supports larger databases compared to MySQL with the same memory.

[ Looking for a high availability, high performance database solution for your website? Here’s how we implemented PXC on a WordPress website. ]

Website visitors expect websites to load within 2 seconds. So, online businesses need a website infrastructure that is very fast. In this solution we explained how we’ve used Percona as a high performance alternative to MySQL in business websites. By choosing an open source alternative to MySQL, we were able to keep the infrastructure costs low, while delivering high uptime and performance.