Russian Version

Part 4. Techniques, used for debugging of Production applications.

Chapter 11. Techniques, used for debugging of Production applications.

Unfortunately is not always possible to find error while testing. Often they only happen when high load.

How do you know about such problems?

One of most important sources of information about problems is error log file. There you can find information such as server crashes, connection errors (if option log-warnings=2 turned on), about options which were specified in the configuration file, but had not turned on because error and some others. There is a rule for working with error log file: if something unexpected happens check error log first. Error log file also contains information about server errors which are not accessible for clients. Therefore is better to have it always turned on even if you do logging at application level.

Method #25: if something unexpected happens check error log first.

You can also turn InnoDB Monitor on which will write all information about InnoDB transactions into error log file.

Method #26: turn InnoDB Monitor to on to have information about all InnoDB transactions in the error log file.

Other important source of information is slow query log file. It contains all queries which were running more than long_query_time seconds. Default value for long_query_time is 10 seconds, but you can change it. Use slow query log for finding slow queries. Also you can tune it in such a way so it will write all queries which don't use indexes.

Method #27: use slow query log to find all slow queries.

After you found error you can want to check it in mysql command line client. But this not always possible to do on production server. For example, if you found a query which crashes server. Or query which runs slow and requires a lot of resources: you can want to make such query easier or try if several simple queries will work faster than original complicated one.

In this case you need to create environment which works most like real, but on separate testing box.

In this case you need to run on separate box MySQL server of same version which production box runs. You also need to copy options from configuration file and load data. More easy to do it with help of mysqldump command, but this is not always convenient, because can take a lot of time especially having large amoutn of data. Fortunately MySQL supports binary compatibility of data between different platforms, therefore you can just copy files of needed tables. See Appendix about methods of backup and moving data between MySQL servers.

After you have a copy of production server you can test without fearing of your application.

Sometimes is needed to test a query using different versions of MySQL server. This can be needed if you meet bug in MySQL code which were fixed later and you want to check if your other queries will work with this new version. This do not have to be a bug, but new feature.

Sometimes is needed to test several minor versions to find more suitable for you.

With MySQL Sandbox you can do it in most easy way. MySQL Sandbox is cross-platform application writing using Perl. You can download it from

Download *tar.gz package which contains required MySQL version, then install MySQL Sandbox and run command:

$make_sandbox mysql-5.4.2-beta-linux-x86_64-glibc23.tar.gz
unpacking /users/ssmirnova/blade12/mysql-5.4.2-beta-linux-x86_64-glibc23.tar.gz
Executing low_level_make_sandbox --basedir=/users/ssmirnova/blade12/5.4.2 \
        --sandbox_directory=msb_5_4_2 \
        --install_version=5.4 \
        --sandbox_port=5420 \
        --no_ver_after_name \
    The MySQL Sandbox,  version 3.0.05
    (C) 2006,2007,2008,2009 Giuseppe Maxia
installing with the following parameters:
upper_directory                = /users/ssmirnova/sandboxes
sandbox_directory              = msb_5_4_2
sandbox_port                   = 5420
check_port                     = 0
no_check_port                  = 0
datadir_from                   = script
install_version                = 5.4
basedir                        = /users/ssmirnova/blade12/5.4.2
my_file                        = 
operating_system_user          = ssmirnova
db_user                        = msandbox
db_password                    = msandbox
my_clause                      = log-error=msandbox.err
prompt_prefix                  = mysql
prompt_body                    =  [\h] {\u} (\d) > '
force                          = 0
no_ver_after_name              = 1
verbose                        = 0
load_grants                    = 1
no_load_grants                 = 0
no_run                         = 0
no_show                        = 0
do you agree? ([Y],n) Y
091101 11:47:44 [Warning] Forcing shutdown of 2 plugins
091101 11:47:44 [Warning] Forcing shutdown of 2 plugins
loading grants
........ sandbox server started
Your sandbox server was installed in $HOME/sandboxes/msb_5_4_2

Replace mysql-5.4.2-beta-linux-x86_64-glibc23.tar.gz with package you are going to use.

You can see above sandbox server was installed in directory $HOME/sandboxes/msb_5_4_2. It also had been started:

$cd $HOME/sandboxes/msb_5_4_2

syntax my sql{dump|binlog|admin} arguments

$./my sql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.4.2-beta MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} ((none)) > select version();
| version()  |
| 5.4.2-beta |
1 row in set (0.00 sec)

mysql [localhost] {msandbox} ((none)) > \q

Stop server with help of a command


Add necessary modifications into configuration file, copy data, then start server again:

. sandbox server started

Sandbox is ready! You can start testing.

Method #28: use MySQL Sandbox for fast and convenient testing of your application using several versions of MySQL server.

Not always convenient to find error using all data. For example, you got wrong result using few rows from a table with millions of rows. If such a query runs slow for some reason you will be waiting result of your queries a lot of time.

Most ofter wrong results happen if you use WHERE clause with other clauses like LIMIT, ORDER BY, GROUP BY, HAVING or if you have several conditions in WHERE.

You can reduce test case using only part of data.

Create a table with same structure like original one:

CREATE TABLE test_problem LIKE problem;

Then load into this new table part of data:

INSERT INTO test_problem SELECT FROM problem WHERE [condition which exists in the original query, but executed properly]

Lately work with table test_problem until you find a reason of wrong behavior. Fix original query.

Same method you can do for queries which use more than one table.

Method #29: use part of data when work with queries which return wrong results from huge tables.

Back Content Forward

Author 2010 Sveta Smirnova
COPYRIGHT © 2010 S.Smirnova and S. Lasunov