Source Installation of ExtSQL for Linux/UNIX (MySQL compatible)
Introduction
Obviously, the information stored in a database can well be considered
the 'company jewels' and you don't want it broken by some untested
piece of software!
This document describes how to safely build & install Extended usage
statistics for SQL (ExtSQL) on a system running Linux/Unix. The
ExtSQL subsystem is just a change to the MySQL server daemon (mysqld)
itself. It was designed to be easy to install and also easy to
remove. To use this procedure you need TWO things:
a) A working version of MySQL that you compiled and installed starting
with a SOURCE distribution of MySQL from the MySQL web site,
http://www.mysql.com/.
b) A source download of the ExtSQL patches from our web site,
http://www.extsql.com/. The download version should match your MySQL
version EXACTLY.
The procedures below will NOT work if you already have a binary
distribution or if you're using Windows! In that case you need
different installation instructions, check our documentation at
http://www.extsql.com/
If you don't want to build MySQL there are binary versions available
for download for older versions of MySQL and different combinations of
Operating System and machine architecture. Check http://www.extsql.com/
Building ExtSQL
Please, let me repeat again, make sure you start from a MySQL source
distribution you have built and installed yourself and is operating
normally! If you do not want to use the source, the download web site
has compiled binaries for various OS/hardware platforms available for
evaluation and licensing.
0. There is no easy way to back out the patches to your source tree
once they are applied. Make a backup copy of your current source
distribution build tree. If you do have a problem during the build
process, you will not lose your existing working version.
1. Download the source patches for ExtSQL from http://www.extsql.com/
for your version of the server and put the .gz file in the top of your
MySQL distribution.
1a. Type 'make distclean' at the top of your distribution tree. This
will remove all prior build products.
2. Give the command:
tar -zxvf extsql-my-src-5.0.45-rhel4-x86-2-5a-3.1b.tar.gz
to unpack. You should see three files with names similar to:
patch-5.0.3.1b
compat-5.0.5a
do_conf-5.2
README.source_extsql
The first two are both patch files. The 'patch-' file contains the
bulk of the changes that make up ExtSQL. The second file starting
with 'compat-' MAY be present. It contains unique fixes for that
specific version of MySQL (normally limited to Makefile changes).
The 'do_conf-' file contains the exact configure commands used for the
source build we used for testing. You may need to merge this with any
selections you made. Any special notes we have concerning build
configuration would appear as comments in this file.
As always, please view the README before doing anything! It contains
any last minute release notes and a description of the most current
procedures.
3. Apply the patches. Make sure to apply the 'compat-' file FIRST.
patch -V t -p0 -lNu < compat-5.0.5a
patch -V t -p0 -lNu < patch-5.0.3.0b
You should NOT see any failure messages.
4. Run the same configure command you used to create your current
source build of MySQL. You may use whatever options you desire. It
should not affect your ability to build ExtSql.
For ExtSQL 5.0.x: You must add the following option to the current
list of CXXFLAGS used in the definition for your configure,
-DEXTSQL_50=1
e.g. 'CXXFLAGS=-O2 -DEXTSQL_50=1 -pipe -m32 -march=i386 -mtune=pentium4'
5. Then type 'make' as usual to build mysqld. It should complete
normally and you should see a new mysqld binary in the build directory
sql. As a sanity check the size and date of the file compared with
the backup copy of your last build. This file should have 'todays'
date and be slightly larger in size.
Installing the ExtSQL server
The whole point of this procedure is again safety. DO NOT PERFORM a
'make install.' Instead of doing a 'make install' which normally
copies a lot of files to different destinations -- you will manually
install just the new mysqld. We recommend the following steps (below
we assume the base install directory for MySQL is /usr/local/mysql):
1. If possible, try your first install of the new mysqld on a
non-production server that mimics your primary installation.
2. Backup all your databases.
3. Make a backup copy of your current mysqld:
cd /usr/local/mysql/libexec
cp -a mysqld mysqld.sav
4. ExtSQL prints status information to the MySQL error
file. In another window you can monitor that file by typing
something like (use the correct path for your system):
tail -f /usr/local/mysql/var/your-hostname.err &
5. At this point we are ready to install and activate the new server.
We will use the basic commands available with MySQL to stop and start
the server. If your installation uses other wrapper programs -- you
should probably use those. Stop your MySQL server with:
service mysql stop
OR
/usr/local/mysql/bin/mysqladmin -uroot -p"password" shutdown
060612 07:58:08 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
060612 07:58:08 mysqld ended
IMPORTANT NOTE: The path you see in the log file shutdown command
should be the same path were you installed the new ExtSQL above!
6. Copy the new mysqld into place:
cp -a /path/to/your/source-installation/sql/mysqld mysqld
7. 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-5a-3.0b
060612 7:58:38 [Note] ExtSQL disabled by user,
no extsql_class_list defined
8. Take a breather! The preceding two lines confirm that your server
was built with the ExtSQL subsystem and that it is currently disabled
since no statistics were requested. We recommend you run for at least
a day with this configuration and convince yourself things are
operating normally. You should see NO performance impact on the
server.
If an error occurs during start this indicates a problem with the
build. To get back to a known good configuration all you have to do
is restore your version of the mysqld and restart the server.
9. You are now ready to start recording statistical data. Depending
on local policies and your preference perform either of the following:
Change /etc/my.cnf and add the following line. It 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)"
NOTE - enter it exactly as above and it should be ONE physical line in
the file. If you change the /etc/my.cnf be sure to remove this line
if you revert to a standard mysqld. It will not understand this
additional parameter and will generate an error.
OR
You can restart the server and add it as a command line option (again
as ONE physical line):
mysqld_safe --user=mysql --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)" &
Because of the length of the extsql_class_list it's easy to introduce
an error that can cause the server to not start due to my.cnf syntax.
You can run an easy check of your config file syntax by giving the
command: ./mysqld --help --verbose
If you have a syntax error in your config file, it will be reported.
This command will NOT confirm you have a valid license key or a valid
extsql_class_list -- but your server will restart.
For a more complete listing of currently supported tracking variables
and example configurations go to: http://www.ExtSQL.com/tracking.php
10. When the server restarts you should now see the following
additional entries in the server 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 classes
Any error during server initialization or operation will be noted to
the error log and the subsystem will disable itself.
11. From the SQL command line you can now monitor your server with commands like:
SHOW STATISTICS; # summary information about ExtSQL and usage
SHOW STATISTICS * FROM user; # information on total user activity
SHOW STATISTICS Com_Select, Com_insert FROM user HISTORY; # show historical information
SHOW STATISTICS * FROM user LIKE 'biguser' HISTORY; # just one user
SHOW STATISTICS * FROM user WHERE Com_select > 100 HISTORY; # just heavy users
For ExtSQL 5.0.x: (Supports INFORMATION_SCHEMA. Full SQL syntax available.)
use INFORMATION_SCHEMA; # make it the default, show available tables
SHOW TABLES LIKE 'EXTSTATS%';
DESCRIBE EXTSTATS_user; # show column definitions
SELECT user, hours, Com_select, Com_insert FROM EXTSTATS_user; # shows all times
SELECT user, hours, Com_select, Com_insert FROM EXTSTATS_user
WHERE hours='0000-00-00'; # summary data only
SELECT user, hours, Questions FROM EXTSTATS_user
WHERE Questions > 400 AND user='domenic' ORDER BY hours;
Using ExtSQL
After a successful install, check for the most current usage
and configuration instructions at:
http://www.extsql.com/showPage.php?Page=documentation