Tracking SQL statistics with ExtSQL
Keeping Score

© Rick Sargeant, 123RF
ExtSQL provides detailed usage statistics for MySQL and PostgreSQL database systems.
MySQL and PostgreSQL have become standard open source databases on the basis of their active developer communities, solid performance, and reliable operation.
Despite the power and flexibility of these SQL variants, the options for obtaining historical data on database usage are relatively limited. SQL monitoring tools typically focus on gross monitoring at the server level. The Extended Usage Statistics for SQL (ExtSQL) project is an effort to make a much wider range of statistical information available to database admins through simple commands at the SQL command line. In fact, ExtSQL provides advanced admin features for MySQL and PostgreSQL that are not even available in the "big brother" SQL vendors. Database admins can now use ExtSQL to monitor and account for server usage down to the individual database, user, or even connecting client.
Almost any variable available through the SHOW STATUS command can be tracked. For instance, To see a display of all user activity in hourly increments for the last 24 hours showing the number of queries of each type, enter:
SHOW STATISTICS Com_select, Com_update, Com_insert FROM user WHERE Com_select > 100 HISTORY LIMIT 24
My company, Software Workshop Inc., developed ExtSQL and currently makes it available as open source software under the GPL. Our hope is that the features implemented through ExtSQL will eventually be implemented by other databases such as Oracle and DB2 and will become part of the SQL standard. In the meantime, ExtSQL takes the form of drop-in replacements for the server daemons used with MySQL and PostgreSQL.
In this article, I describe how to install and configure ExtSQL for a typical MySQL installation. The process for setting up ExtSQL for PostgreSQL is similar.
Building a New mysqld
The ExtSQL website [1] has compiled binaries for various OS/hardware platforms available for evaluation and licensing. The most current instructions for installing and using ExtSQL are also available at the ExtSQL website.
First, download the ExtSQL patches for your version of the MySQL server and put the .gz file at the top of your MySQL distribution. Type make distclean at the top of your distribution tree to remove all prior build products then enter
tar -zxvf extsql-my-src-5.0.45-rhel4-x86-2-1a-2.0b.tar.gz
to unpack. The result should be files similar to these four:
patch-5.0.2.0b compat-5.0.1a do_conf-5.2 README.source_extsql
The first two files are patch files. The patch- file contains the bulk of the changes that make up ExtSQL. The second file (compat-) contains unique fixes for the specific version of MySQL.
The do_conf- file contains the exact configure commands used for the source build, so merge this list with any selections you've made. As always, view the README for the latest information.
The next step is to apply the patches, but apply the compat- file first
patch -V t -p0 -lNu < compat-5.0.1a patch -V t -p0 -lNu < patch-5.0.2.0b
then run the same configure command you used to create your current build of MySQL with the options you desire.
For ExtSQL 5.0.x, you must add the following option to the current list of CXXFLAGS used in the definition for your configure (e.g., -DEXTSQL_50=1):
'CXXFLAGS=-O2 -DEXTSQL_50=1 -pipe -m32 -march=i386 -mtune=pentium4'.
Then type make as usual to build mysqld. A new mysqld binary in the build directory sql should be the result.
Install the ExtSQL Server
Instead of make install, you manually replace mysqld. I recommend the following procedure. (I assume the base install directory for MySQL is /usr/local/mysql.)
Start by backing up your databases and making a backup of your current mysqld:
cp -a mysqld mysqld.sav
ExtSQL prints status information to the MySQL error file. In another window, monitor the server with the following (use the correct path for your system):
tail -f /usr/local/mysql/var/your-hostname.err &
Now you are ready to install and activate the new server with the basic commands available in MySQL to stop and start the server. If your installation uses other wrapper programs, use them as well:
/usr/local/mysql/bin/mysqladmin -uroot -p"password" shutdown 060612 07:58:08 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
Note: The path in the logfile shutdown command should be the same path to which you previously backed up mysqld.
Copy the new mysqld into place
cp -a /path/to/your/source-installation/sql/mysqld mysqld
and start the new server:
/usr/local/mysql/bin/mysqld_safe --user=$mysql_user $other_args & 071121 7:42:47 [Note] ExtSQL build: ExtSQL version: extsql-my-bin-5.0.45-rhel4-x86-2-1a-2.0b 060612 7:58:38 [Note] ExtSQL disabled by user, no extsql_class_list defined
Now take a breather. The preceding lines confirm the server was built with the ExtSQL subsystem, and it is currently disabled because no statistics were requested. We recommend you run the server for at least a day and convince yourself things are operating normally. Server performance should not be affected.
If an error occurs, it could indicate a problem with the build. To get back to a known good configuration, just restore your version of mysqld and restart the server. To start recording statistical data, open /etc/my.cnf and add the following line, which must be in the [mysqld] section of the config file:
extsql_class_list="user, max-100, time-120, units-h, (Com_insert, Com_select, Com_update, Com_delete, Com_replace, Qcache_hits, Questions, Slow_queries)"
The command above should be on one physical line in the file. If you change /etc/my.cnf, be sure to remove this line if you revert to a standard mysqld.
On restart of your server, you should see the following entries in the error log:
060612 8:26:12 [Note] ExtSQL build: ExtSQL version: extsql-my-bin-5.0.45-rhel4-x86-2-5a-3.0b 060612 8:26:12 [Note] ExtSQL ACTIVE tracking memory: 435600 bytes for 1 class
Errors during initialization or operation will be noted in the error log, and the subsystem will disable itself, leaving MySQL operations intact.
Terminology
In ExtSQL, the entities that interact with the server are called classes (Table 1). Within a class, specific instantiations are called instances. An instance of a user could be domenic or christina, for example. The server class is unique in that the only instance is the server itself.

For each instance, you can track individual data items called vars. Vars include almost all of the values displayed in the present SHOW STATUS command. Use the name of the var as displayed by SHOW STATUS; for example, Com_select tracks select statements.
Buy this article as PDF
(incl. VAT)
Buy Linux Magazine
Subscribe to our Linux Newsletters
Find Linux and Open Source Jobs
Subscribe to our ADMIN Newsletters
Support Our Work
Linux Magazine content is made possible with support from readers like you. Please consider contributing when you've found an article to be beneficial.
News
-
2024 Open Source Professionals Job Survey Now Open
Share your expectations regarding open source jobs.
-
Arch Linux 2023.12.01 Released with a Much-Improved Installer
If you've ever wanted to install Arch Linux, now is your time. With the latest release, the archinstall script vastly simplifies the process.
-
Zorin OS 17 Beta Available for Testing
The upcoming version of Zorin OS includes plenty of improvements to take your PC to a whole new level of user-friendliness.
-
Red Hat Migrates RHEL from Xorg to Wayland
If you've been wondering when Xorg will finally be a thing of the past, wonder no more, as Red Hat has made it clear.
-
PipeWire 1.0 Officially Released
PipeWire was created to take the place of the oft-troubled PulseAudio and has finally reached the 1.0 status as a major update with plenty of improvements and the usual bug fixes.
-
Rocky Linux 9.3 Available for Download
The latest version of the RHEL alternative is now available and brings back cloud and container images for ppc64le along with plenty of new features and fixes.
-
Ubuntu Budgie Shifts How to Tackle Wayland
Ubuntu Budgie has yet to make the switch to Wayland but with a change in approaches, they're finally on track to making it happen.
-
TUXEDO's New Ultraportable Linux Workstation Released
The TUXEDO Pulse 14 blends portability with power, thanks to the AMD Ryzen 7 7840HS CPU.
-
AlmaLinux Will No Longer Be "Just Another RHEL Clone"
With the release of AlmaLinux 9.3, the distribution will be built entirely from upstream sources.
-
elementary OS 8 Has a Big Surprise in Store
When elementary OS 8 finally arrives, it will not only be based on Ubuntu 24.04 but it will also default to Wayland for better performance and security.