Part II: Advanced MySQL Administration and Management Up Part II: Advanced MySQL Administration and Management Chapter 6: Data backup, repair, recovery 

5 Tracking down load issues

Overtime as you add more accounts and more users on your system, occasionally you’ll get the few bad apples with poorly-coded web applications that hammer your MySQL server with LEFT JOIN’s with SORT BY’s and GROUP BY’s on one million row tables that completely obliterates your server. If you have a large number of SiteWorx accounts, tracking down which one is responsible for MySQL sucking up your server’s resources may prove difficult. The tool that InterWorx support will typically use is called mytop. Mytop is a Perl application that continuously queries the MySQL server with SHOW PROCESSLIST and produces produces output anolgous to the linux system load monitoring tool, top. This allows you to see a somewhat-in-real-time picture of what’s going on with your server.

5.1 Installing mytop

  1. You can download mytop from the MyTop homepage. You can just download it to your /root/ folder. You can run:
    wget HTTP://jeremy.zawodny.com/mysql/mytop/mytop-1.6.tar.gz
  2. Extract the tarball with:
    tar xvzf mytop-1.6.tar.gz .
  3. Try running it by cd’ing to the directory that was just created and running:
    ./mytop.
  4. If you get a warning about Term::ReadKey not being installed, we will need to try and install it via CPAN (which is like a package manager for perl modules).
  5. Check if you have CPAN by just running cpan on your terminal. If not, try installing it with:
    yum install perl-CPAN
    .
  6. Once you have run cpan, CPAN might ask you if you want it to configure itself. Simply hit enter to have CPAN automatically configure itself.
  7. Once at the prompt, enter install Term::ReadKey in order to have cpan download and install the package. Then enter quit to exit.
  8. At this point you should be good to go. You may be missing other dependencies, most of which should be obtainable via CPAN if needed.
  9. If you get an error when running mytop: ’Error in option spec: "long|!" ’, then you should open the ./mytop file in your preferred text editor, go to the line that looks like:
    "long|!" => \$config{long_nums},
    and comment it out with a # character in front of that text. On our version (1.6), the line was number 159.

5.2 Using mytop and logging into the MySQL database as root

In order to use mytop, you need to provide server and login information. Typically most users don’t know their iworx MySQL password to the localhost database. We recommend instead manually setting the root password in NodeWorx under System Services ▷ MySQL Server ▷ Overview for your localhost MySQL server. If you need to set the root password for a remote server, this can also be accomplished by visiting System Services ▷ MySQL Server ▷ Remote Servers and clicking [Configuration] next to the remote server you wish to set the password for.

5.2.1 Connecting to your database server with mytop

In order to connect with mytop now, you can run the following:
./mytop -S /var/lib/mysql/mysql.sock -u root -p [PASSWORD HERE] -d mysql
The -d mysql is added because a database needs to be selected. As all servers have a mysql database (it stores meta data like users and permissions), that is a safe database to choose. As root user, though, you will see all activity across all databases and users.

5.3 mytop Display (From Official Documentation)

figure images/mytop.png
Figure 5.1 An example mytop display from http://jeremy.zawodny.com/mysql/mytop/
This documentation can be found at http://jeremy.zawodny.com/mysql/mytop/mytop.html. The mytop display screen is really broken into two parts and is shown in figure 5.1↑. The top 4 lines (header) contain summary information about your MySQL server.
  • The first line identified the host name of the server (localhost) and the version of MySQL it is running. The right had side shows the uptime of the MySQL server process in days+hours:minutes:seconds format (much like FreeBSD’s top) as well as the current time.
  • The second line displays the total number of queries the server has processed, the average number of queries per second, the real-time number of queries per second, and the number of slow queries.
  • The third line deals with threads. Versions of MySQL before 3.23.x didn’t give out this information, so you’ll see all zeros.
  • And the fourth line displays key buffer efficiency (how often keys are read from the buffer rather than disk) and the number of bytes that MySQL has sent and received.
You can toggle the header by hitting h when running mytop. The second part of the display lists as many threads as can fit on screen. By default they are sorted according to their idle time (least idle first). As you can see, the thread id, username, host from which the user is connecting, database to which the user is connected, number of seconds of idle time, the command the thread is executing, and the query info are all displayed. Often times the query info is what you are really interested in, so it is good to run mytop in an xterm that is wider than the normal 80 columns if possible. The thread display color-codes the threads if you have installed color support. The current color scheme only works well in a window with a dark (like black) background. The colors are selected according to the Command column of the display:
  • Query - Yellow
  • Sleep - White
  • Connect - Green
Those are purely arbitrary and will be customizable in a future release. If they annoy you just start mytop with the -nocolor flag or adjust your config file appropriately.
Instead of always using bulky command-line parameters, you can also use a config file in your home directory (~/.mytop). If present, mytop will read it automatically. It is read before any of your command-line arguments are processed, so your command-line arguments will override directives in the config file.
Here is a sample config file ~/.mytop which implements the defaults described above:
user=root
pass=
host=localhost
db=test
delay=5
port=3306
socket=
batchmode=0
header=1
color=1
idle=1
Using a config file will help to ensure that your database password isn’t visible to users on the command-line. Just make sure that the permissions on ~/.mytop are such that others cannot read it (unless you want them to, of course). You may have white space on either side of the = in lines of the config file.
Shortcut Keys
The following keys perform various actions while mytop is running. This list has been abridged to those keys that will probably be helpful during trouble-shooting load issues.
  • ? Display help.
  • d Show only threads connected to a particular database.
  • f Given a thread id, display the entire query that thread was (and still may be) running.
  • F Disable all filtering (host, user, and db).
  • i Toggle the display of idle (sleeping) threads. If sleeping threads are filtered, the default sorting order is reversed so that the longest running queries appear at the top of the list.
  • k Kill a thread.
  • m Toggle modes. Currently this switches from ‘top’ mode to ‘qps’ (Queries Per Second Mode). In this mode, mytop will write out one integer per second. The number written reflects the number of queries executed by the server in the previous one second interval. More modes may be added in the future.
  • o Reverse the default sort order.
  • p Pause display.
  • q Quit mytop
  • s Change the sleep time (number of seconds between display refreshes).
  • u Show only threads owned by a giver user.

5.4 General Paradigm for Tracking Load Issues

In general, you want to look for either:
  1. Queries which have been running for an excessively long time (high Time value)
  2. Multiple threads mostly owned by the same user.
The combination of these two can often help sort out the nature of what’s going on with load. If a user has excessive queries, they might be getting a lot of traffic and it might be time to segregate that user on their own MySQL server, or even move them to a less busy server with more resources. If you are seeing long-running queries, you may want to try and work with the host to improve their application’s code base, or alternatively ask them to pay more for their resource use. Again, how you handle situation with single SiteWorx accounts depends on your policy and service agreement with your client. This guide’s purpose is to help you root the cause of high MySQL usage.
 Part II: Advanced MySQL Administration and Management Up Part II: Advanced MySQL Administration and Management Chapter 6: Data backup, repair, recovery 

(C) 2017 by InterWorx LLC