Hosting8 min lezen

Enhancing MySQL with user statistics

Door wojtek op woensdag, 8 april, 2009

Enhancing MySQL with user statistics

In dit artikel

The standard MySQL provides quite a lot of performance statistics: CPU usage, number of queries performed rows fetched, etc. However, all of the statistics are available for the whole server. If more users are connect to a server, there is no way to tell how much of the resources each of them used, to also be able to monitor MySQL user statistics we did the following:
Based on the work done by google we enhanced MySQL with the SHOW USER_STATISTICS; command. It allows us to monitor the following server resources per database user (a ‘user’ being an entry in the mysql.User table):

  • CPU time used for queries of a selected user;
  • the number of select/update/insert queries per user;
  • the number of connections, number of rows fetched per user;

Some of the cool things we can do with the results:

  • we can tell which user causes most traffic on our servers, and act immediately in case of a server overload (we have 1 minute updates of the data).
  • we can enforce a ‘fair use policy’, preferably by asking our customers to upgrade their accounts with an extra CPU allowance;
  • we provide our customers with interesting overviews of their resource consumption over time;

We have been using the patched servers in production for almost a year now. We haven’t encountered any crash which could be attributed to the patch. Actually we had almost no crashes at all :).

Results of enhancing MySQL with user statistics:

Using the MySQL patch and a few statistic-collection tools (see below) we are able to get such a nice graph:

statistic-collection tools - MySQL user statistics

Statistic-collection tools – MySQL with user statistics

 

Those are the top 10 users of a database server. Each colour represents a database user (our customer). The horizontal axis is time (24h), the vertical axis presents the ratio “CPU seconds used per wallclock second” for a given user. A  bar 1 unit high between t0 and t1 indicates, that the user’s queries took 1 whole CPU in the interval [t0,t1]. The bars are stacked on top of each other.

$$$ Looking at the graph, we can say that this database server has two heavy users (green and blue). On average, they constantly occupy a whole CPU. They have bought expensive packages, so let’s assume it is O.K. The yellow colour, however, has the cheapest option (Personal Hosting) and still appears as one of the top 10 users. We will send him a bill later this month :).

Munin tool

Help, hackers! There was a peak in activity around 8am. However, the two top users shouldn’t be blamed for that. This was another – dark green – user. Actually, this peak was caused by a website overtaken by hackers and used to distribute spam. We were able to quickly identify the site and take it offline.
The shape of our graph corresponds very well with the load graph obtained with the Munin tool:

Implementation

Some time ago Google published a cool MySQL patch for version 5.0.37. The patch is huge and contains a lot of functionality we don’t actually need (semisync replication for example). We managed to extract the measurement part from their code and trim it a bit. The resulting patch is small and tidy (7KB compared with original 5MB). It is therefore easy to port between different MySQL versions. The command we added provides the following output:

mysql> SHOW USER_STATISTICSG
********** 1. row *************
User: user008285
Total_connections: 2378
Concurrent_connections: 0
Connected_time: 258
Busy_time: 12176
Rows_fetched: 19809
Rows_updated: 2734
Select_commands: 9225
Update_commands: 4952
Other_commands: 2376

Having the patched MySQL servers in operation we also implemented:

  • a harvesting daemon to query all servers every minute and report the results to the collecting machine;
  • a collecting daemon which aggregates the results and stores them in RRD files;
  • a web fronted to display the graphs for selected database users;
  • a reporting daemon which detects potentially misbehaving users (causing too heavy load) and eventually raises an alarm.

Other projects:

  • MySQL tools released by Google – this seems t be used by all other projects.
  • OpenQuery and Percona – they maintain and release MySQL enhanced with the google patch and a lot of additions
  • “OurDelta produces enhanced builds for MySQL, with OurDelta and third-party patches, for common production platforms. All the builds are freely available for download.”

Downloads

MySQL 5.0.45

This is the version we are using on our production servers.
It has been running without a crash for almost a year now.

  • Here are the ready 5.0.45 packages for Debian
  • Here is the source package with the patch applied. To build, simply follow the Debian source package building instructions found here
    mysql-dfsg-5.0_5.0.45-3~bpo40+1byte4: .orig .diff .dsc
  • Here is the original debian package and a dpatch separately:
    mysql-dfsg-5.0_5.0.45-3~bpo40+1: .orig .diff .dsc .dpatch

    To build, unpack the source package, then copy the .dpatch file to debian/patches and add it’s name to debian/patches/00list. Then build the package.

  • Pure source code from Mysql.com and the applicable patches.

    To build, download both files and then do:

    tar zxf mysql-5.0.45.tar.gz
    cd mysql-5.0.45
    patch -p1 < ../mysql-5.0.45-per_user_stats.diff
    ./configure
    make
    enjoy

MySQL 5.0.51a

  • Here are the ready 5.0.51 packages for Debian lenny
  • Here is the source package with the patch applied.
    mysql-dfsg-5.0_5.0.51a-24+lenny1byte4: .orig .diff .dsc

Related tools:

Munin, RRDTool,drraw


[widget id=”magereport_widget-5″ /]

Hi! Mijn naam is Dion, Account Manager at Hypernode

Wil je meer weten over Hypernode's Managed E-commerce Hosting? Plan je online meeting.

plan een een-op-een meeting tel:+31648362102

Visit Hypernode at