Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 0 down vote favorite

I've moved the mysql server on different machine in LAN, 16 core, 8GB RAM, and the same dreaaded result. During a load test, all is going well until around 300 seconds. There are 15 persistent connections from DB's client to DB, and after this period I can see it going flat:

Suddenly all queries sent to it start to time out:

mysql> show processlist;
+-----+-------------+--------------------+----------+---------+------+-------+------------------+
| Id  | User        | Host               | db       | Command | Time | State | Info             |
+-----+-------------+--------------------+----------+---------+------+-------+------------------+
| 238 | tigase_user | 58.64.157.39:35595 | NULL     | Query   |    0 | NULL  | show processlist | 
| 244 | tigase_user | 58.64.157.25:22624 | tigasedb | Sleep   |    0 |       | NULL             | 
| 245 | tigase_user | 58.64.157.25:22625 | tigasedb | Sleep   |    0 |       | NULL             | 
| 246 | tigase_user | 58.64.157.25:22626 | tigasedb | Sleep   |    0 |       | NULL             | 
| 247 | tigase_user | 58.64.157.25:22627 | tigasedb | Sleep   |    0 |       | NULL             | 
| 248 | tigase_user | 58.64.157.25:22628 | tigasedb | Sleep   |    0 |       | NULL             | 
| 249 | tigase_user | 58.64.157.25:22629 | tigasedb | Sleep   |    0 |       | NULL             | 
| 250 | tigase_user | 58.64.157.25:22630 | tigasedb | Sleep   |    0 |       | NULL             | 
| 251 | tigase_user | 58.64.157.25:22631 | tigasedb | Sleep   |    0 |       | NULL             | 
| 252 | tigase_user | 58.64.157.25:22632 | tigasedb | Sleep   |    0 |       | NULL             | 
| 253 | tigase_user | 58.64.157.25:22633 | tigasedb | Sleep   |    0 |       | NULL             | 
| 254 | tigase_user | 58.64.157.25:22634 | tigasedb | Sleep   |  619 |       | NULL             | 
| 255 | tigase_user | 58.64.157.25:22635 | tigasedb | Sleep   |  619 |       | NULL             | 
| 256 | tigase_user | 58.64.157.25:22636 | tigasedb | Sleep   |  620 |       | NULL             | 
| 257 | tigase_user | 58.64.157.25:22637 | tigasedb | Sleep   |  619 |       | NULL             | 
| 258 | tigase_user | 58.64.157.25:22638 | tigasedb | Sleep   |  620 |       | NULL             | 
+-----+-------------+--------------------+----------+---------+------+-------+------------------+
16 rows in set (0.00 sec)


[...] after 25 seconds [...]

mysql> show processlist;
+-----+-------------+--------------------+----------+---------+------+-------+------------------+
| Id  | User        | Host               | db       | Command | Time | State | Info             |
+-----+-------------+--------------------+----------+---------+------+-------+------------------+
| 238 | tigase_user | 58.64.157.39:35595 | NULL     | Query   |    0 | NULL  | show processlist | 
| 244 | tigase_user | 58.64.157.25:22624 | tigasedb | Sleep   |   25 |       | NULL             | 
| 245 | tigase_user | 58.64.157.25:22625 | tigasedb | Sleep   |   25 |       | NULL             | 
| 246 | tigase_user | 58.64.157.25:22626 | tigasedb | Sleep   |   25 |       | NULL             | 
| 247 | tigase_user | 58.64.157.25:22627 | tigasedb | Sleep   |   25 |       | NULL             | 
| 248 | tigase_user | 58.64.157.25:22628 | tigasedb | Sleep   |   25 |       | NULL             | 
| 249 | tigase_user | 58.64.157.25:22629 | tigasedb | Sleep   |   25 |       | NULL             | 
| 250 | tigase_user | 58.64.157.25:22630 | tigasedb | Sleep   |   25 |       | NULL             | 
| 251 | tigase_user | 58.64.157.25:22631 | tigasedb | Sleep   |   25 |       | NULL             | 
| 252 | tigase_user | 58.64.157.25:22632 | tigasedb | Sleep   |   25 |       | NULL             | 
| 253 | tigase_user | 58.64.157.25:22633 | tigasedb | Sleep   |   25 |       | NULL             | 
| 254 | tigase_user | 58.64.157.25:22634 | tigasedb | Sleep   |  644 |       | NULL             | 
| 255 | tigase_user | 58.64.157.25:22635 | tigasedb | Sleep   |  644 |       | NULL             | 
| 256 | tigase_user | 58.64.157.25:22636 | tigasedb | Sleep   |  645 |       | NULL             | 
| 257 | tigase_user | 58.64.157.25:22637 | tigasedb | Sleep   |  644 |       | NULL             | 
| 258 | tigase_user | 58.64.157.25:22638 | tigasedb | Sleep   |  645 |       | NULL             | 
+-----+-------------+--------------------+----------+---------+------+-------+------------------+
16 rows in set (0.00 sec)

mysql> show processlist;
+-----+-------------+--------------------+----------+---------+------+---------------+------------------------------------------------------------------------------------------------------+
| Id  | User        | Host               | db       | Command | Time | State         | Info                                                                                                 |
+-----+-------------+--------------------+----------+---------+------+---------------+------------------------------------------------------------------------------------------------------+
| 238 | tigase_user | 58.64.157.39:35595 | NULL     | Query   |    0 | NULL          | show processlist                                                                                     | 
|

     244 | tigase_user | 58.64.157.25:22624 | tigasedb | Query   |    0 | freeing items | select nid as nid3, node as node3 from tig_nodes, (select nid as nid2, node as node2 from tig_nodes, | 
    | 245 | tigase_user | 58.64.157.25:22625 | tigasedb | Sleep   |    0 |               | NULL                                                                                                 | 
    | 246 | tigase_user | 58.64.157.25:22626 | tigasedb | Sleep   |    0 |               | NULL                                                                                                 | 
    | 247 | tigase_user | 58.64.157.25:22627 | tigasedb | Sleep   |    0 |               | NULL                                                                                                 | 
    | 248 | tigase_user | 58.64.157.25:22628 | tigasedb | Sleep   |    0 |               | NULL                                                                                                 | 
    | 249 | tigase_user | 58.64.157.25:22629 | tigasedb | Sleep   |    0 |               | NULL                                                                                                 | 
    | 250 | tigase_user | 58.64.157.25:22630 | tigasedb | Sleep   |    0 |               | NULL                                                                                                 | 
    | 251 | tigase_user | 58.64.157.25:22631 | tigasedb | Sleep   |    0 |               | NULL                                                                                                 | 
    | 252 | tigase_user | 58.64.157.25:22632 | tigasedb | Sleep   |    0 |               | NULL                                                                                                 | 
    | 253 | tigase_user | 58.64.157.25:22633 | tigasedb | Sleep   |    0 |               | NULL                                                                                                 | 
    | 254 | tigase_user | 58.64.157.25:22634 | tigasedb | Sleep   |  645 |               | NULL                                                                                                 | 
    | 255 | tigase_user | 58.64.157.25:22635 | tigasedb | Sleep   |  645 |               | NULL                                                                                                 | 
    | 256 | tigase_user | 58.64.157.25:22636 | tigasedb | Sleep   |  646 |               | NULL                                                                                                 | 
    | 257 | tigase_user | 58.64.157.25:22637 | tigasedb | Sleep   |  645 |               | NULL                                                                                                 | 
    | 258 | tigase_user | 58.64.157.25:22638 | tigasedb | Sleep   |  646 |               | NULL                                                                                                 | 
    +-----+-------------+--------------------+----------+---------+------+---------------+------------------------------------------------------------------------------------------------------+
    16 rows in set (0.01 sec)

This correlates with what I see in my logs at mysql's client:

Caused by: tigase.db.TigaseDBException: Error getting user data for: multi-user-chat/rooms/[email protected]/creation-date; method took 25410 ms, thread: 399

however, the load on mysql machine is almost 0 during all this time (as reported by 'top')

After some more time the connections start to disappear (although DB's client has not died):

    mysql> show processlist;
+-----+-------------+--------------------+----------+---------+------+-------+------------------+
| Id  | User        | Host               | db       | Command | Time | State | Info             |
+-----+-------------+--------------------+----------+---------+------+-------+------------------+
| 238 | tigase_user | 58.64.157.39:35595 | NULL     | Query   |    0 | NULL  | show processlist | 
| 247 | tigase_user | 58.64.157.25:22627 | tigasedb | Sleep   |   14 |       | NULL             | 
| 249 | tigase_user | 58.64.157.25:22629 | tigasedb | Sleep   |   14 |       | NULL             | 
| 250 | tigase_user | 58.64.157.25:22630 | tigasedb | Sleep   |   14 |       | NULL             | 
| 252 | tigase_user | 58.64.157.25:22632 | tigasedb | Sleep   |   14 |       | NULL             | 
| 254 | tigase_user | 58.64.157.25:22634 | tigasedb | Sleep   | 1609 |       | NULL             | 
| 255 | tigase_user | 58.64.157.25:22635 | tigasedb | Sleep   | 1609 |       | NULL             | 
| 256 | tigase_user | 58.64.157.25:22636 | tigasedb | Sleep   | 1610 |       | NULL             | 
| 257 | tigase_user | 58.64.157.25:22637 | tigasedb | Sleep   | 1609 |       | NULL             | 
| 258 | tigase_user | 58.64.157.25:22638 | tigasedb | Sleep   | 1610 |       | NULL             | 
+-----+-------------+--------------------+----------+---------+------+-------+------------------+
10 rows in set (0.00 sec)

The 10 problematic timeout-prone connections have beome 4 as seen above, and those would eventually vanish too, only the last 5 would remain (which are used differently btw)

EDIT: after even some more time, 3 out of those remaining 4 have survived and another 7 new connections have appeared.

EDIT2: after even more hours - 0connections to DB with DB's client still around. wth is going on I wonder...

And as per Daniel's remarks:

mysql> show variables like "max_connections";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    439
Current database: *** NONE ***

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   | 
+-----------------+-------+
1 row in set (0.31 sec)

mysql> SHOW VARIABLES LIKE 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 | 
+---------------+-------+
1 row in set (0.00 sec)

EDIT3:

mysql> show variables like 'max_allowed_packet'
    -> ;
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16777216 | 
+--------------------+----------+
1 row in set (0.01 sec)

Where should Ibe looking further ? Thanks!

share|improve this question
what is unusual is the ERROR 2006 (HY000): MySQL server has gone away you are getting. could you please post the value of max_allowed_packet using show variables like 'max_allowed_packet' – Daniel t. Mar 9 at 4:31
@Danielt. Sure, see EDIT3 – kellogs Mar 9 at 14:02

1 Answer

up vote 0 down vote

A couple of comments -

1. what is the max_connections value in your mysql configuration file? Normally it is set to 100 by default. Check if you have the proper value set - 

    mysql> show variables like "max_connections";

2. what is the wait_timeout value for your mysql setup? It appears it is set above 1600, you might need to lower it, to the point where it doesn't negatively affect your application - 

    mysql> SHOW VARIABLES LIKE 'wait_timeout';

Then in your mysql configuration file, add the value, say wait_timeout=180, under mysqld section.

3. Disable persistent connections - it is generally not recommended to use persistent connections from your client application when connecting to mysql.

If none of this helps, try running mysqltuner - mysql tunning script, and apply the relevant recommendations.

share|improve this answer
Hi, thanks for the answer. Perhaps I was a bit unclear. This really does not look like a MySQL problem to me, but rather a client side problem. So what I was asking for really was about proper ways to establish where my client is blowing up. As for the wait_time value, I'd be thinking the more the better. I definitely don't want my connections slain on MySQL's whims. Same for 3. PS - see EDIT2 and below – kellogs Mar 9 at 4:23
It this is a client issue, I am not sure if I will be of any help. The only thing I can say is make sure you select the DB when you make a connection, and try to increase the max_allowed_packet to 32M or 64M to see if that helps with the MySQL server has gone away error. – Daniel t. Mar 9 at 21:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.