Debugging a Communications Link failure issue for a long MySQL procedure

A long procedure executed from JBoss on MySQL instance fails every 2 hours in production environment. Find below the reason for the same.

There are three TCP related parameters in Linux OS which played role in this.

cat /proc/sys/net/ipv4/tcp_keepalive_time = 7200 seconds
cat /proc/sys/net/ipv4/tcp_keepalive_intvl = 75 seconds
cat /proc/sys/net/ipv4/tcp_keepalive_probes = 9

Description - The first two parameters are expressed in seconds, and the last is the pure number. This means that the keepalive routines wait for two hours (7200 secs) before sending the first keepalive probe, and then resend it every 75 seconds. If no ACK response is received for nine consecutive times, the connection is marked as broken.


Conclusion - We need to know default settings in our environment and of course the procedure requires tuning :)

MySQL Query Profiling

Profiling is enabled per session. When a session ends, its profiling information is lost.

mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE T1 (id INT);
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW PROFILES;
+----------+----------+--------------------------+
| Query_ID | Duration | Query                    |
+----------+----------+--------------------------+
|        0 | 0.000088 | SET PROFILING = 1        |
|        1 | 0.000136 | DROP TABLE IF EXISTS t1  |
|        2 | 0.011947 | CREATE TABLE t1 (id INT) |
+----------+----------+--------------------------+
3 rows in set (0.00 sec)

mysql> SHOW PROFILE;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| checking permissions | 0.000040 |
| creating table       | 0.000056 |
| After create         | 0.011363 |
| query end            | 0.000375 |
| freeing items        | 0.000089 |
| logging slow query   | 0.000019 |
| cleaning up          | 0.000005 |
+----------------------+----------+
7 rows in set (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 1;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| query end          | 0.000107 |
| freeing items      | 0.000008 |
| logging slow query | 0.000015 |
| cleaning up        | 0.000006 |
+--------------------+----------+
4 rows in set (0.00 sec)

Refer - http://dev.mysql.com/doc/refman/5.0/en/show-profile.html

Linux - Drop caches.

Writing to this will cause the kernel to drop clean caches, dentries and inodes from memory, causing that memory to become free.
To free pagecache:

• echo 1 > /proc/sys/vm/drop_caches

To free dentries and inodes:

• echo 2 > /proc/sys/vm/drop_caches

To free pagecache, dentries and inodes:

• echo 3 > /proc/sys/vm/drop_caches

As this is a non-destructive operation, and dirty objects are not freeable, the user should run "sync" first in order to make sure all cached objects are freed.

This tunable was added in 2.6.16.

Referred - http://www.linuxinsight.com/proc_sys_vm_drop_caches.html