Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 419
Server version: 5.1.67-0ubuntu0.11.10.1 (Ubuntu)
jdbc-mysql.jar version: unknown - how do I check this ?
root@XMPP:/opt/tigase# java -version
java version "1.6.0_24"
OpenJDK Runtime Environment (IcedTea6 1.11.5) (6b24-1.11.5-0ubuntu1~11.10.1)
OpenJDK 64-Bit Server VM (build 20.0-b12, mixed mode)
As reported by netstat (client side) "show processlist" mysql command and netstat (server side). TCP state is "ESTABLISHED" on boths sides.
Server side (xx.xx.xx.241)
mysql> show processlist;
+-----+-------------+--------------------+----------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-------------+--------------------+----------+---------+------+-------+------------------+
| 100 | tigase_user | xx.xx.xx.25:23820 | tigasedb | Sleep | 921 | | NULL |
| 101 | tigase_user | xx.xx.xx.25:23821 | tigasedb | Sleep | 6664 | | NULL |
| 102 | tigase_user | xx.xx.xx.25:23822 | tigasedb | Sleep | 6664 | | NULL |
| 103 | tigase_user | xx.xx.xx.25:23823 | tigasedb | Sleep | 6664 | | NULL |
| 104 | tigase_user | xx.xx.xx.25:23824 | tigasedb | Sleep | 6664 | | NULL |
| 105 | tigase_user | xx.xx.xx.25:23825 | tigasedb | Sleep | 6664 | | NULL |
| 106 | tigase_user | xx.xx.xx.25:23826 | tigasedb | Sleep | 6664 | | NULL |
| 107 | tigase_user | xx.xx.xx.25:23827 | tigasedb | Sleep | 6654 | | NULL |
| 108 | tigase_user | xx.xx.xx.25:23828 | tigasedb | Sleep | 921 | | NULL |
| 115 | tigase_user | xx.xx.xx.25:23835 | tigasedb | Sleep | 6667 | | NULL |
| 116 | tigase_user | xx.xx.xx.25:23836 | tigasedb | Sleep | 6667 | | NULL |
| 117 | tigase_user | xx.xx.xx.25:23837 | tigasedb | Sleep | 6667 | | NULL |
| 118 | tigase_user | xx.xx.xx.25:23838 | tigasedb | Sleep | 6667 | | NULL |
| 119 | tigase_user | xx.xx.xx.25:23839 | tigasedb | Sleep | 6667 | | NULL |
| 143 | tigase_user | xx.xx.xx.25:23864 | tigasedb | Sleep | 921 | | NULL |
| 144 | tigase_user | xx.xx.xx.25:23866 | tigasedb | Sleep | 921 | | NULL |
| 145 | tigase_user | xx.xx.xx.25:23867 | tigasedb | Sleep | 654 | | NULL |
| 153 | tigase_user | xx.xx.xx.25:23875 | tigasedb | Sleep | 921 | | NULL |
| 166 | tigase_user | xx.xx.xx.39:45214 | NULL | Query | 0 | NULL | show processlist |
+-----+-------------+--------------------+----------+---------+------+-------+------------------+
19 rows in set (0.00 sec)
Client side (xx.xx.xx.25):
root@XMPP:/opt/tigase# netstat -antop | grep "xx.xx.xx.241:3306" | wc -l
20
root@XMPP:/opt/tigase# netstat -antop | grep "xx.xx.xx.241:3306"
tcp 0 74 xx.xx.xx.25:23846 xx.xx.xx.241:3306 ESTABLISHED 10222/java on (75.60/12/0)
tcp 0 348 xx.xx.xx.25:23863 xx.xx.xx.241:3306 ESTABLISHED 10222/java on (75.06/12/0)
tcp 0 348 xx.xx.xx.25:23829 xx.xx.xx.241:3306 ESTABLISHED 10222/java on (75.06/12/0)
tcp 0 0 xx.xx.xx.25:23866 xx.xx.xx.241:3306 ESTABLISHED 10222/java keepalive (5648.85/0/0)
tcp 0 74 xx.xx.xx.25:23845 xx.xx.xx.241:3306 ESTABLISHED 10222/java on (79.66/12/0)
tcp 0 55 xx.xx.xx.25:23849 xx.xx.xx.241:3306 ESTABLISHED 10222/java on (75.56/12/0)
tcp 0 0 xx.xx.xx.25:23870 xx.xx.xx.241:3306 ESTABLISHED 10222/java keepalive (5648.83/0/0)
tcp 0 45 xx.xx.xx.25:23871 xx.xx.xx.241:3306 ESTABLISHED 10222/java on (75.03/12/0)
tcp 0 0 xx.xx.xx.25:23868 xx.xx.xx.241:3306 ESTABLISHED 10222/java keepalive (5648.82/0/0)
tcp 0 0 xx.xx.xx.25:23869 xx.xx.xx.241:3306 ESTABLISHED 10222/java keepalive (5648.82/0/0)
tcp 0 0 xx.xx.xx.25:23875 xx.xx.xx.241:3306 ESTABLISHED 10222/java keepalive (5665.19/0/0)
tcp 0 0 xx.xx.xx.25:23864 xx.xx.xx.241:3306 ESTABLISHED 10222/java keepalive (5648.80/0/0)
tcp 0 0 xx.xx.xx.25:23872 xx.xx.xx.241:3306 ESTABLISHED 10222/java keepalive (5648.79/0/0)
tcp 0 0 xx.xx.xx.25:23820 xx.xx.xx.241:3306 ESTABLISHED 10222/java keepalive (962.67/0/0)
tcp 0 74 xx.xx.xx.25:23848 xx.xx.xx.241:3306 ESTABLISHED 10222/java on (75.51/12/0)
tcp 0 348 xx.xx.xx.25:23874 xx.xx.xx.241:3306 ESTABLISHED 10222/java on (74.99/12/0)
tcp 0 55 xx.xx.xx.25:23850 xx.xx.xx.241:3306 ESTABLISHED 10222/java on (79.59/12/0)
tcp 0 0 xx.xx.xx.25:23828 xx.xx.xx.241:3306 ESTABLISHED 10222/java keepalive (962.65/0/0)
tcp 0 228 xx.xx.xx.25:23873 xx.xx.xx..241:3306 ESTABLISHED 10222/java on (83.17/12/0)
tcp 0 0 xx.xx.xx.25:23867 xx.xx.xx.241:3306 ESTABLISHED 10222/java keepalive (5648.76/0/0)
As it can be seen, client shows 20 connections to the mysql server - which only shows 18 connections. Other time I have spotted a gap of 10 missing connections (10 reported at server vs 20 opened by client, or even a gap of 17: 22 client vs 5 server). The situation persists for some good minutes - not more than 15 I think - and then the number of connections starts to change (sometimes to the desired 20 / 20, but not sure how stable it stays there).
Connections are opened the regular way through Java code:
conn = DriverManager.getConnection(db_conn);
They are not (explicitly) closed from client as they should always remain open. Db connection string:
--user-db-uri = jdbc:mysql://xx.xx.xx.241/tigasedb?user=tigase_user&password=*****&useUnicode=true&characterEncoding=UTF8
my.cnf:
root@TDB:~# egrep "^[^#]." /etc/mysql/my.cnf
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
default-character-SET=utf8
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
skip-external-locking
bind-address = 0.0.0.0
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
query_cache_limit = 1M
query_cache_size = 16M
log_error = /var/log/mysql/error.log
expire_logs_days = 10
max_binlog_size = 100M
init_connect='SET collation_connection = utf8_general_ci; SET NAMES utf8;'
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/
And there are also lots of reports of
Caused by: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
and
Caused by: java.sql.SQLException: Query execution was interrupted
What is going on here ? Thank you!