Next time you want to build a fast, well-optimized website, don’t forget the database system. Many content management systems (CMS) let you choose a relational database management system (RDBMS) to use on the back end. MySQL, famous for its stability and security, is a popular choice; in addition to its numerous features, it has a large community, many contributors, and good documentation. However, MySQL is now owned by Oracle, and its open-source status is not guaranteed, which makes MariaDB, an application compatible database system, an excellent alternative.
Joomla, a popular CMS, is written in PHP and by default uses MySQL as its database system. Since MariaDB can provide improved functionality, performance, and stability, you might want to use MariaDB instead of MySQL with Joomla. Although MariaDB is not listed in Joomla’s technical requirements, it is safe to migrate your Joomla site’s database to it.
I’ll walk through the process on a CentOS server, since it is a popular distribution for hosting web servers. While the syntax might differ slightly for other Linux distributions, the algorithm is the same.
Before you migrate, you might want to do some benchmarks, so you can see whether your efforts have led to improvements. You can turn on the Joomla debugging option through the admin’s back end or by executing the command
# sed -i "s@$debug = '0';@$debug = '1';@" configuration.php. After that, you can load the front page of your website and see the full list of the queries that are run. Navigate to the bottom of the page, click on the Database Queries link and the queries will be listed. Copy them and turn off the debugging if you are benchmarking your live website; if you don’t, your visitors will see its output after each page’s footer. Then use a text editor like vim or nano to paste them in your test .sql file, put each query on a single line, removing unnecessary blank spaces in them – for example, the format should be as follows:
SELECT folder AS type, element AS name, params FROM gpa_extensions WHERE enabled >= 1 AND type ='plugin' AND state >= 0 AND access IN (1,1) ORDER BY ordering
– and save the SQL file. To run a benchmark, use a database server load emulator called mysqlslap, like this:
# mysqlslap --concurrency=100 --iterations=10 --query=several_joomla_queries.sql --create-schema=myuser_joomla -umyuser_joom -p7654e684c4d14ab544261568101cc9c4 Benchmark Average number of seconds to run all queries: 0.161 seconds Minimum number of seconds to run all queries: 0.120 seconds Maximum number of seconds to run all queries: 0.279 seconds Number of clients running queries: 100 Average number of queries per client: 3
That command simulates 100 MySQL clients that concurrently run several sample SELECT queries grabbed from the debugging database queries output and saved in the
several_joomla_queries.sql file on your Joomla database. The connection to the database is established with the database name, the associated database username and the password specified in the Joomla’s configuration.php file. The queries in my test are a small excerpt from the Joomla debugging tool database queries list, which I specified should iterate 10 times. Once the migration to MariaDB is completed, you can perform the same test and compare the results, since the load emulator is replicated in MariaDB.
Now you can proceed with the migration. Create a full backup of the database files and your MySQL configuration file, just in case something goes wrong, then stop the MySQL server:
# cp /var/lib/mysql/ mysql_backup -r # mysqldump -umyuser_joom -p7654e684c4d14ab544261568101cc9c4 myuser_joomla > myuser_joomla.sql # cp /etc/my.cnf /etc/my.cnf_backup # /etc/init.d/mysql stop Shutting down MySQL.. SUCCESS!
Next, remove the existing MySQL 5.6 installation. First, list the packages that you plan to remove (
rpm -qa | grep -i mysql-). Then complete the actual process; use the following command with caution:
# for i in `rpm -qa | grep -i mysql-`; do rpm -e --nodeps $i; done.
Next, generate a MariaDB repository file for your architecture and place it under the /etc/yum.repos.d/ folder:
# MariaDB 10.1 CentOS repository list - created 2014-10-26 21:13 UTC # http://mariadb.org/mariadb/repositories/ [mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.1/centos6-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1
Install the MariaDB 10.1 server and client with the command
# yum makecache && yum install MariaDB-server MariaDB-client. Since MariaDB is compatible with MySQL, the Joomla website should now work without further modifications. Clear your web browser’s cache and test it. If Joomla works, you can run the benchmark again. When I did that, I saw better results with my benchmark test queries:
# mysqlslap --concurrency=100 --iterations=10 --query=several_joomla_queries.sql --create-schema=myuser_joomla -umyuser_joom -p7654e684c4d14ab544261568101cc9c4 Benchmark Average number of seconds to run all queries: 0.153 seconds Minimum number of seconds to run all queries: 0.103 seconds Maximum number of seconds to run all queries: 0.202 seconds Number of clients running queries: 100 Average number of queries per client: 3
For the purpose of this test case I have compared MySQL 5.6.21 with MariaDB 10.1.0. The default database engine in both cases is InnoDB:
# mysql -e "show engines" | grep -i default | cut -f 1 InnoDB
As you can see, in a very short time, replacing MySQL with a fast and reliable database system like MariaDB can improve a database application’s user experience.
This blog post was revised 2014-10-27.