Benchmarking Joomla

This post recently caught my attention on Planet MySQL.

If you haven’t read it yet, I suggest that you go and do so, and also read the comments.

I think Matthew’s request for the queries so that others can run comparative benchmarks is very interesting, and while I don’t have access to the queries used to produce those results, I am making some queries of my own available for others to test. You can get your own queries if you want, just enable slow query logging with long_query_time set to 0 on the database for a Joomla instalation (being unexperienced in Joomla I used this project, which is great) and then run these steps on the resulting slow query log file:


csplit -z joomlatools-slow.log '/^#/' '{*}'
for f in x*; do grep -i select $f >/dev/null || rm -f $f; done
for f in x*; do grep -v '^SET' $f > $$ && mv -f $$ $f; done
for f in x*; do grep -v '^#' $f > $$ && mv -f $$ $f; done
for f in x*; do tr '\n' ' ' <$f>$$ && echo >> $$ && mv -f $$ $f; done
cat x* > queries.txt

That should give you a file similar to this one.
The EXPLAIN statements are not mine, everything you see on that file was generated by me visiting a sample Joomla site.

I do not have the time to run a big benchmark now, but a mysqlslap using the exact same settings as those from the original post, and my list of queries, seems simple enough.

The vagrant box for Joomla has MySQL 5.5 so I used that for my tests. Here are the results:

MySQL 5.5 with the default configuration (default as installed by the debian package):

root@joomlatools:~# mysqlslap --concurrency=100 --iterations=10 --query=queries.txt --create-schema=myuser_joomla -umyuser_joom -p7654e684c4d14ab544261568101cc9c4
Benchmark
Average number of seconds to run all queries: 1.911 seconds
Minimum number of seconds to run all queries: 1.786 seconds
Maximum number of seconds to run all queries: 2.202 seconds
Number of clients running queries: 100
Average number of queries per client: 265

MariaDB 10.1, with the config as installed by the MariaDB package:

root@joomlatools:~# mysqlslap --concurrency=100 --iterations=10 --query=queries.txt --create-schema=myuser_joomla -umyuser_joom -p7654e684c4d14ab544261568101cc9c4
Benchmark
Average number of seconds to run all queries: 1.743 seconds
Minimum number of seconds to run all queries: 1.694 seconds
Maximum number of seconds to run all queries: 1.847 seconds
Number of clients running queries: 100
Average number of queries per client: 265

MySQL 5.5 with MariaDB’s configuration (minus incompatible options)

root@joomlatools:~# mysqlslap --concurrency=100 --iterations=10 --query=queries.txt --create-schema=myuser_joomla -umyuser_joom -p7654e684c4d14ab544261568101cc9c4
Benchmark
Average number of seconds to run all queries: 1.803 seconds
Minimum number of seconds to run all queries: 1.548 seconds
Maximum number of seconds to run all queries: 2.398 seconds
Number of clients running queries: 100
Average number of queries per client: 265

Interesting. MariaDB still performs better than 5.5, but performance improved for this old version anyway.
I took a quick look at the configuration file and among the changes, use of the query cache caught my attention. I think it’s strange to have it enabled by default since it can cause so much contention problems at heavy concurrency workloads, but I’m not familiar with the MariaDB improvements and perhaps the query cache implementation on it is better at scaling.

Just to check, I tried MariaDB 10.1 with the query cache disabled:

mariadb 10.1 without the query cache:
root@joomlatools:~# mysqlslap --concurrency=100 --iterations=10 --query=queries.txt --create-schema=myuser_joomla -umyuser_joom -p7654e684c4d14ab544261568101cc9c4
Benchmark
Average number of seconds to run all queries: 4.113 seconds
Minimum number of seconds to run all queries: 3.735 seconds
Maximum number of seconds to run all queries: 5.446 seconds
Number of clients running queries: 100
Average number of queries per client: 265

Interesting. It seems without the query cache, MariaDB 10.1 performs worse than MySQL 5.5. Could be a concurrency thing though, as I know more recent MySQL versions (and I assume the same is true for MariaDB) have worked on optimising for high concurrency use cases, at the expense of low concurrency or single threaded workloads. And while the mysqlslap invocation runs 100 threads, don’t be fooled. I ran this on a VM on a notebook with 2 cores with hyperthreading. It’s great as a workstation but it wouldn’t cut it as a server :)

I’d like to see what experience others have. You know exactly the VM and workload I used for this tests, so let’s see what numbers you get.

Recovering MySQL access

Ever found yourself working on a MySQL server where root’s password is unavailable? It has happened to me a few times, always because the person who set up the DB left the place long ago, and this information was not documented anywhere.

If you have root access to the OS, MySQL lets you restart the server bypassing access checks, using the skip-grant-tables option, which requires a service restart.

However, if you need to regain root access and want to minimize service impact, you can take advantage of the way the server responds to SIGHUP signals and the fact that access credentials are stored on a MyISAM table.

MySQL uses a few tables to store credentials and privileges for users (you can find more about this here), but for this procedure, we only need to work with the mysql.user table.

Specifically, we will work with the columns ‘user’, ‘host’ and ‘password’ from this table.

Here’s an example of how this can look on a server:

mysql> select user,host,password from mysql.user;
+-----------+-----------+-------------------------------------------+
| user      | host      | password                                  |
+-----------+-----------+-------------------------------------------+
| root      | localhost | *1BD9C328233CF457571A4BB5DB8D32892AB8EDBF |
| root      | mysql     | *1BD9C328233CF457571A4BB5DB8D32892AB8EDBF |
| root      | 127.0.0.1 | *1BD9C328233CF457571A4BB5DB8D32892AB8EDBF |
| root      | ::1       | *1BD9C328233CF457571A4BB5DB8D32892AB8EDBF |
|           | localhost |                                           |
|           | mysql     |                                           |
| dba       | %         | *4FC8D8270BEC4364C78799065996F5306139B412 |
| readwrite | localhost | *202273E75BD11D06FBE2F057BFA1B1BB2B26549C |
| readonly  | localhost | *FC69E042CE30D92E2952335F690CF2345C812E36 |
+-----------+-----------+-------------------------------------------+
9 rows in set (0.00 sec)

To start, we’ll need to make a copy of this table to a database where we can change it. On this example server, this means the ‘test’ schema, as the ‘readwrite’ user has write privileges on it. Even if root’s password was lost, you can typically get a less privileged MySQL account by checking the applications that connects to this database. If for some reason this is not the case, you can achieve the same results by copying this table to another server, and copying it back after the necessary changes have been made.

The following command happen on the datadir:

[root@mysql mysql]# cp mysql/user.* test/; chown mysql.mysql test/user.*

Please don’t overwrite an existing table when doing this! Rename the copied files as needed instead …

Now you should be able to access (and write) to this table:

[root@mysql mysql]# mysql -ureadwrite -p test
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 34
Server version: 5.6.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user,host,password from user;
+-----------+-----------+-------------------------------------------+
| user      | host      | password                                  |
+-----------+-----------+-------------------------------------------+
| root      | localhost | *1BD9C328233CF457571A4BB5DB8D32892AB8EDBF |
| root      | mysql     | *1BD9C328233CF457571A4BB5DB8D32892AB8EDBF |
| root      | 127.0.0.1 | *1BD9C328233CF457571A4BB5DB8D32892AB8EDBF |
| root      | ::1       | *1BD9C328233CF457571A4BB5DB8D32892AB8EDBF |
|           | localhost |                                           |
|           | mysql     |                                           |
| dba       | %         | *4FC8D8270BEC4364C78799065996F5306139B412 |
| readonly  | %         | *FC69E042CE30D92E2952335F690CF2345C812E36 |
| readwrite | %         | *202273E75BD11D06FBE2F057BFA1B1BB2B26549C |
+-----------+-----------+-------------------------------------------+
9 rows in set (0.00 sec)

By now you’ve probably figured out what I’ll do: update test.user, changing the password column for user ‘root’ and host ‘localhost’ to the result of running the PASSWORD() function with some string of my choice, then copying this table back, and then sending SIGHUP to the server.

A couple of caveats:

  • Either make a copy of the original table file, (and?) or write down the original hash for root (the one you will replace)
  • Even if nobody on the customer’s current team knows how to get you MySQL’s root password, that does not mean they don’t have some old app someone has forgotten about that uses the root account to connect. If this is the case, access will break for this app. You can follow the same steps outlined here, but instead of permanently changing root’s password, use your regained access to create a new super user account, and then replace root’s hash with the one you saved (and flush privileges!)

For completion, here’s the rest of the process:

mysql> update test.user set password=password('newpass but this is insecure so dont use') where user = 'root' and host = 'localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select user,host,password from test.user where user='root';
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *0A131BF1166FB756A61317A40F272D6FFDD281E9 |
| root | mysql     | *1BD9C328233CF457571A4BB5DB8D32892AB8EDBF |
| root | 127.0.0.1 | *1BD9C328233CF457571A4BB5DB8D32892AB8EDBF |
| root | ::1       | *1BD9C328233CF457571A4BB5DB8D32892AB8EDBF |
+------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)

mysql>

Time to copy the table back and reload the grant tables:

[root@mysql mysql]# 'cp' test/user.MY* mysql/
[root@mysql mysql]# kill -SIGHUP $(pidof mysqld)

And now you should be able to get back in:

[root@mysql mysql]# mysql -p'newpass but this is insecure so dont use'
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.6.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*0A131BF1166FB756A61317A40F272D6FFDD281E9' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

There you go. We’ve regained root access to MySQL without restarting the service!

I hope you find this useful, and I’ll leave opinions on MySQL’s security as an exercise to the reader …