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