MySQL Proxy is a proxy server written in C. It sits between the application and the MySQL Server and can be used to monitor, profile, log, and affect queries / responses going to the MySQL database. In this article, I am interested in logging and profiling queries as I navigate to various pages of a WordPress website. This will give me information necessary used to improve the performance of a website.
Download and install a copy of MySQL Proxy.
Next, I will explain the 3 command line arguments used to run MySQL Proxy.
bin/mysql-proxy-0.8.4/bin/mysql-proxy --proxy-lua-script=share/doc/mysql-proxy/tutorial-basic.lua --proxy-backend-addresses=mysql.example.com:3306 --proxy-address=www.example.com:4040
The –proxy-lua-script command line argument is used to specify the lua script to trigger when a request comes in to the MySQL Proxy server. Here is where I will put my special processing to log the queries to a file so that I can analyze it later. I modified the existing tutorial-basic.lua script which comes with MySQL Proxy and made it write to a log file.
function read_query( packet )
if string.byte(packet) == proxy.COM_QUERY then
local msg = string.sub(packet, 2);
local f = assert(io.open("/home/USERNAME/tmp/mysql-proxy-logs/" .. os.date("%Y_%m_%d") .. ".log", "a"));
f:write(os.date("%c") .. ":SQL>>:" .. msg .. "\n");
f:close();
end
end
The –proxy-backend-addresses command line argument is used to specify the endpoint of the MySQL database server and port.
The –proxy-address command line argument is used to specify the endpoint where the MySQL Proxy will listen. This is also where you will point the application to.
Now, I am ready to run MySQL Proxy. Run the shell script bin/mysql-proxy to start the proxy.
bin/mysql-proxy-0.8.4/bin/mysql-proxy --proxy-lua-script=share/doc/mysql-proxy/tutorial-basic.lua --proxy-backend-addresses=mysql.example.com:3306 --proxy-address=www.example.com:4040
With the proxy server configured and started, you will see this:
2014-08-07 03:50:07: (critical) plugin proxy 0.8.4 started
The next step is to point the application to the running instance of MySQL Proxy. To do this, navigate to the root of your WordPress installation and modify wp-config.php. Look for the database host line and change it to match the same as what was entered for –proxy-address above.
E.g.,
/** MySQL hostname */
define('DB_HOST', 'dbhost.example.com:4040');
At this point, all the configurations are in place and you can test the setup. Navigate to a page in your WordPress website and view the log file that is generated in the location specified in the lua script. Using the example above, I would look here for a file that looks similar to this:
/home/USERNAME/tmp/mysql-proxy-logs/2014_08_07.log
The contents of the file may look like something like this,
Wed Jul 9 23:54:25 2014:SQL>>:SET NAMES utf8 Wed Jul 9 23:54:25 2014:SQL>>:SELECT @SESSION.sql_mode Wed Jul 9 23:54:25 2014:SQL>>:SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes' Wed Jul 9 23:54:26 2014:SQL>>:SELECT option_value FROM wp_options WHERE option_name = 'ossdl_https' LIMIT 1 Wed Jul 9 23:54:26 2014:SQL>>:SELECT option_value FROM wp_options WHERE option_name = 'uninstall_plugins' LIMIT 1
For my purposes, I discovered an unnecessary test query that was called for every page. This was left behind by the previous development team who forgot to clean it up. I was able to count the number of queries called for the homepage. In my debugging, I also discovered that for each missing graphic link, the number of queries on that page doubled. E.g., if there were 100 queries on the homepage without any broken graphic links. There would be 200 queries for the same page with 1 broken graphic link.
Notes:
1. The instructions here are specific to a web application on the WordPress platform. For other platforms or frameworks, you will need to point the application to the proxy server in the applicable way.
2. The name of the output log file will reflect the current date. E.g., If you ran the setup above on August 6, 2014, the log file will be named 2014_08_06.log.