Lately everyone tries its shot at replacing MySQL as the first choice for web databases in the small and medium sized website/application category. There is MariaDB, there is the new and shiny Amazon Aurora, there’s WebscaleSQL and there is also Percona Server. What they all have in common is that they are drop-in replacements for MySQL, in short you don’t need to modify your application, you don’t need extra drivers. The only thing you need is to configure the server and connect your website / app to it.
In the past half of a year I’ve been doing all kinds of research on databases I’ve tried out lots of options for high performance and availability databases. I will try to present some of them in a series of blog posts and today I’ll start with an article about Percona Server installation.
The Percona Server is an enhanced, drop-in replacement for MySQL Server, it is advertised by the company behind it as having all the features of MySQL 5.6 Community Edition but also having the scalability, availability, backup and security features that are available only in the MySQL 5.6 Enterprise Edition. While MySQL Enterprise Edition requires support from Oracle the Percona Server is a free and open source software.
I’ve started playing around with Percona for a while and i must say I’m pretty happy with it. Among other things this very blog runs with Percona Server as a database backend. I just installed it and changed the credentials in the WordPress config to the new server and it just worked. There is no need to do anything else because it’s fully compatible with MySQL.
But what other benefits does Percona Server brings us compared to a standard MySQL?
- XtraDB – this is a backwards compatible fork of the InnoDB engine with several important improvements in performance and efficiency. This allows us to get better results from our current hardware. Actually with Percona Server the number of concurrent queries that can be executed on the same hardware is significantly higher then what we would get from a standard MySQL. And because XtraDB is built based on InnoDB the InnoDB tables will be loaded without any migration or transformation.
- Compatibility – as i said above Percona is a drop-in replacement for MySQL which means that any application that works with MySQL will work with Percona Server
- Stability – Percona Server handles high loads more easily and will require much more load before it slows down compared to MySQL
- PAM Authentication – this is a feature from MySQL Enterprise Edition and allows you to tie in a custom authentication scheme into database access
- Metrics – there are a number of additional performance metrics built into Percona Server which allows the administrator to see which tables or queries are slowing down he server.
But how do you install it ?
Actually its pretty easy.
You start with getting the signing key for the packages. This is needed because Percona packages are signed
apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
After this is done you should have at hand the distribution code for your Ubuntu. This is something like lucid, precise, saucy, trusty. If you don’t know your distribution code you can look it up using :
lsb_release -c
After you have this at hand you proceed with the addition of the repo to the /etc/apt/sources.list file. Edit this file and add the following lines to it
deb http://repo.percona.com/apt ###DistributionCode### main deb-src http://repo.percona.com/apt ###DistributionCode### main
Make sure to replace ###DistributionCode### with your distribution code learned at the previous step. In my case this was “trusty” as i use Ubuntu 14.04
When this is done we need to set up prioritization of the repos. This means that the system should prioritize Percona repositories versus default repositories.
To do this create the following file: /etc/apt/preferences.d/00percona.pref , you can do this with touch or the editor of your choice
After you created the file you should open it and add the following lines to it:
Package: * Pin: release o=Percona Development Team Pin-Priority: 1001
Ok. Now you’re almost good to go. You just need to perform an
apt-get update
and then the install command:
apt-get install percona-server-server
As you can see there is no magic in this. It’s just a regular apt-get install. The installer will ask you to set a root password if it is your first installation. After Percona is installed it’s advised to run the command:
/usr/bin/mysql_secure_installation
This will perform a security check and secure your installation.
Then you should have a working Percona installation which you can use instead of MySQL. Yes, instead of it because there is no reason to stick to MySQL or run it in parallel. What I did was that i migrated my whole server to Percona and all the websites (TYPO3, Flow, WordPress, TikiWiki, Custom) run now on Percona. So far (for the last 7 months) i didn’t see any issues with it. Actually I was suprised to see that one of the TYPO3 sites which had a 17 GB table which gets hit pretty often performed quite well (previously when i was on MySQL i always had issues with this one).
I plan to stick with Percona for a while until i figure out if what Amazon says about its new Aurora RDS is true. Then i might go for that one. Either way I’ll probably write about that one too pretty soon.
