mysql, number of TCP connections does not match on server / client machines
Posted by: gigi kent
Date: March 12, 2013 10:39AM

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!

Options: ReplyQuote


Subject
Written By
Posted
mysql, number of TCP connections does not match on server / client machines
March 12, 2013 10:39AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.