I regularly troubleshoot slow MySQL queries for my awesome clients on Codeable. Luckily hosts like Kinsta and Cloudways provide New Relic integration which can help me pin down these slow queries and improve them. Hosts will be able to enable the raw MySQL query data for you if you share this guide with them as well!
This guide is two parts, first we are going to change the obfuscated MySQL query data to the raw query data so we can do some testing on a development server. In the second part I will show you how to disable the MySQL query cache so that when you are running your optimization tests, they will actually be accurate. There’s nothing worse than thinking you optimized a query only to learn it was fast the second time you tested because the query was cached!
If you have root access to the server you can also enable MySQL slow query logging
How to Get Raw MySQL Query Data in New Relic
Open your newrelic.ini which is in this path below, you may need to change the 7.0 to 7.2 or 7.3 if you are using one of those PHP versions
nano /etc/php/7.0/mods-available/newrelic.iniYou will see the basic New Relic template
; This file contains the various settings for the New Relic PHP agent. There
; are many options, all of which are described in detail at the following URL:
; https://docs.newrelic.com/docs/agents/php-agent/configuration/php-agent-configuration
;
; If you use a full path to the extension you insulate yourself from the
; extension directory changing if you change PHP installations or versions.
; If you do not use an absolute path then the file must be installed in the
; active configuration's extension directory.
extension = "newrelic.so"
[newrelic]
; Setting: newrelic.enabled
; Type : boolean
; Scope : per-directory
; Default: true
; Info : Enable or disable the agent. Please note that you cannot globally
; disable the agent and then selectively enable it on a per-directory
; basis. If you disable the agent in the global INI file then the
; agent will not initialize at all. However, you can selectively
; disable the agent on a per-directory basis.
;
;newrelic.enabled = true
; Setting: newrelic.license
; Type : string
; Scope : per-directory
; Default: none
; Info : Sets the New Relic license key to use. This can vary from directory
; to directory if you are running a multi-tenant system. By special
; dispensation if you upgraded from a previous version of the agent
; where the license key was set in the daemon, the installation and
; upgrade script will have preserved your license key from the file
; /etc/newrelic/newrelic.cfg, but ONLY if you installed via rpm/yum
; or dpkg. The key is saved in /etc/newrelic/upgrade_please.key
; and the agent will look for that file if you do not specify a valid
; license here.
; It is *STRONGLY* recommended that you set the license key in your
; INI file(s) and do not rely on the key file being present. Also
; please note that even if you are not letting the agent start the
; daemon and are still using newrelic.cfg (see below) the license
; keyword in that file is no longer obeyed. Instead the agent will
; use the preserved value of that license from the key file.
; Once you have updated your INI files to contain the license we
; urge you to remove /etc/newrelic/upgrade_please.key in order to
; eliminate the potential for confusion about exactly where the key
; is coming from.
;
newrelic.license = "d112bdda535908e2b356602ab3e2faa1ae72bc47"
; Setting: newrelic.logfile
; Type : string
; Scope : system
; Default: none
; Info : Sets the name of the file to send log messages to.
;
newrelic.logfile = "/var/log/newrelic/php_agent.log"
If you are using nano you can use Ctrl+V a few times to get to the bottom of the configuration file.
Add this line underneath or use Ctrl+W to search for the string newrelic.transaction_tracer.record_sql and change the value from obfuscated to raw.
newrelic.transaction_tracer.record_sql = "raw"Hit Ctrl+X, Y and Enter to Save and exit nano.
Restart php-fpm or Apache depending on what you use for PHP handling
service php7.0-fpm restart
service apache2 reloadYou can also do this with .htaccess if you are using Apache or LiteSpeed (but not OpenLiteSpeed) by adding this line to your .htaccess file
php_value newrelic.transaction_tracer.record_sql = "raw"You can also use a drop-in .user.ini file and add this to it
newrelic.transaction_tracer.record_sql="raw";Now let’s move on to disabling the MySQL query cache so that when you are running tests you don’t get inaccurate raw query time data!
How to Test MySQL Query Improvements
First you want to disable query cache or you will just be spinning your wheels during your testing phase.
Once query cache is disabled you can try different indexes, schema changes or whatever you can think of to try and improve the query time.
Disable MySQL Query Cache
Open your MySQL configuration, usually it is /etc/mysql/my.conf and find the section that looks like this
#
# * Query Cache Configuration
#
query_cache_type = 1
query_cache_limit = 16M
query_cache_size = 256M
The query_cache_type parameter is a boolean value so 1 means enabled and 0 means disabled so changing query_cache_type = 1 to query_cache_type = 0 will disable the MySQL query cache so you can run your tests.
#
# * Query Cache Configuration
#
query_cache_type = 0
query_cache_limit = 16M
query_cache_size = 256M
Make sure to restart MySQL after making these changes
sudo service mysql restartNow you can test all of your optimizations without the query cache confounding the results! This way you will be testing real and raw improvements rather than cached ones 🙂