New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Connection pool seems not working when disabling multiplexing #1107

Open
seanlook opened this Issue Jul 25, 2017 · 13 comments

Comments

Projects
None yet
2 participants
@seanlook

seanlook commented Jul 25, 2017

mysql> select * from stats_mysql_global where Variable_Name like '%Connections_created%';
+----------------------------+----------------+
| Variable_Name              | Variable_Value |
+----------------------------+----------------+
| Client_Connections_created | 24944822       |
| Server_Connections_created | 42210856       |
+----------------------------+----------------+

mysql> show variables like "%multi%";
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| mysql-multiplexing | false |
+--------------------+-------+

Version: 1.3.6
I set mysql-multiplexing disabled and find out server side connections created is larger than client side. A little confused about that.

I show processlist from db instance directly and no long connection is kept alive. It create threads for client(proxysql) for every connection. So disabling multiplexing leads to this?

I tried set mysql-max_stmts_per_connection=1000 ( 20 is too small for me), but nothing changed. By the way setting mysql-max_stmts_per_connection larger than 2000 do not give me any hints but set failed.

Thanks.

@renecannao

This comment has been minimized.

Show comment
Hide comment
@renecannao

renecannao Jul 26, 2017

Contributor

Are you using read/write split or other query routing?
If yes, a single client connection can result in multiple backend connections, depending from where queries are routed.

I show processlist from db instance directly and no long connection is kept alive.

Maybe you have a small wait_timeout on DB server?

I tried set mysql-max_stmts_per_connection=1000 ( 20 is too small for me), but nothing changed.

If you have mysql-multiplexing=false, changing mysql-max_stmts_per_connection will make no difference. In fact, mysql-max_stmts_per_connection is used to determine what to do with the connection when it is sent back to the connection pool: if you have multiplex disabled, the connection will never go back to the connection pool.

By the way setting mysql-max_stmts_per_connection larger than 2000 do not give me any hints but set failed.

There is no input validation during the SET orUPDATE command.
Input validation is performed when you issue a LOAD command. You should see an error in the error log.

Contributor

renecannao commented Jul 26, 2017

Are you using read/write split or other query routing?
If yes, a single client connection can result in multiple backend connections, depending from where queries are routed.

I show processlist from db instance directly and no long connection is kept alive.

Maybe you have a small wait_timeout on DB server?

I tried set mysql-max_stmts_per_connection=1000 ( 20 is too small for me), but nothing changed.

If you have mysql-multiplexing=false, changing mysql-max_stmts_per_connection will make no difference. In fact, mysql-max_stmts_per_connection is used to determine what to do with the connection when it is sent back to the connection pool: if you have multiplex disabled, the connection will never go back to the connection pool.

By the way setting mysql-max_stmts_per_connection larger than 2000 do not give me any hints but set failed.

There is no input validation during the SET orUPDATE command.
Input validation is performed when you issue a LOAD command. You should see an error in the error log.

@seanlook

This comment has been minimized.

Show comment
Hide comment
@seanlook

seanlook Jul 27, 2017

mysql> show variables like "wait_timeout";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 7200  |
+---------------+-------+
1 row in set

I don't think it's a small one. In fact the connection to server looks closed instantly after the query is done. I see many sessions in sleep state just for 0 or 1 seconds, when I look for the session the second time, it gone(closed):
20170727091630

Seems connection pool never worked!

Sorry for misunderstanding the mysql-max_stmts_per_connection, I thought it controls the max statements of the backend server before the connection is closed, to achieve the connection pool goal :(
Error log:

2017-07-25 15:09:35 ProxySQL_Admin.cpp:2906:flush_mysql_variables___database_to_runtime(): [WARNING] Impossible to set variable max_stmts_per_connection with value "2000". Resetting to current "1000".

Thanks for your reply.

seanlook commented Jul 27, 2017

mysql> show variables like "wait_timeout";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 7200  |
+---------------+-------+
1 row in set

I don't think it's a small one. In fact the connection to server looks closed instantly after the query is done. I see many sessions in sleep state just for 0 or 1 seconds, when I look for the session the second time, it gone(closed):
20170727091630

Seems connection pool never worked!

Sorry for misunderstanding the mysql-max_stmts_per_connection, I thought it controls the max statements of the backend server before the connection is closed, to achieve the connection pool goal :(
Error log:

2017-07-25 15:09:35 ProxySQL_Admin.cpp:2906:flush_mysql_variables___database_to_runtime(): [WARNING] Impossible to set variable max_stmts_per_connection with value "2000". Resetting to current "1000".

Thanks for your reply.

@renecannao

This comment has been minimized.

Show comment
Hide comment
@renecannao

renecannao Jul 27, 2017

Contributor

This needs further troubleshooting.

Please attach the output of SHOW MYSQL STATUS and SELECT * FROM stats_mysql_connection_pool .
Other metrics may be needed, but let's start from these.
Thanks

Contributor

renecannao commented Jul 27, 2017

This needs further troubleshooting.

Please attach the output of SHOW MYSQL STATUS and SELECT * FROM stats_mysql_connection_pool .
Other metrics may be needed, but let's start from these.
Thanks

@seanlook

This comment has been minimized.

Show comment
Hide comment
@seanlook

seanlook Jul 27, 2017

show mysql status

mysql> SHOW MYSQL STATUS;
+------------------------------+---------------+
| Variable_name                | Value         |
+------------------------------+---------------+
| Active_Transactions          | 0             |
| Backend_query_time_nsec      | 8034008154580 |
| Client_Connections_aborted   | 0             |
| Client_Connections_connected | 46            |
| Client_Connections_created   | 42976680      |
| Client_Connections_non_idle  | 43            |
| Com_autocommit               | 0             |
| Com_autocommit_filtered      | 0             |
| Com_commit                   | 1131          |
| Com_commit_filtered          | 0             |
| Com_rollback                 | 2             |
| Com_rollback_filtered        | 0             |
| Com_stmt_close               | 1523499       |
| Com_stmt_execute             | 1523499       |
| Com_stmt_prepare             | 1523499       |
| ConnPool_get_conn_failure    | 0             |
| ConnPool_get_conn_immediate  | 0             |
| ConnPool_get_conn_success    | 73935169      |
| ConnPool_memory_bytes        | 1712          |
| MySQL_Monitor_Workers        | 8             |
| MySQL_Thread_Workers         | 4             |
| ProxySQL_Uptime              | 757611        |
| Queries_backends_bytes_recv  | 14920851328   |
| Queries_backends_bytes_sent  | 32813702749   |
| Query_Cache_Entries          | 0             |
| Query_Cache_Memory_bytes     | 0             |
| Query_Cache_Purged           | 0             |
| Query_Cache_bytes_IN         | 0             |
| Query_Cache_bytes_OUT        | 0             |
| Query_Cache_count_GET        | 0             |
| Query_Cache_count_GET_OK     | 0             |
| Query_Cache_count_SET        | 0             |
| Query_Processor_time_nsec    | 2524961125231 |
| Questions                    | 275841450     |
| SQLite3_memory_bytes         | 4484816       |
| Server_Connections_aborted   | 0             |
| Server_Connections_connected | 73            |
| Server_Connections_created   | 73935169      |
| Servers_table_version        | 1             |
| Slow_queries                 | 1305          |
| Stmt_Active_Total            | 0             |
| Stmt_Active_Unique           | 0             |
| Stmt_Max_Stmt_id             | 900           |
| mysql_backend_buffers_bytes  | 5176576       |
| mysql_frontend_buffers_bytes | 3014656       |
| mysql_session_internal_bytes | 137792        |
+------------------------------+---------------+
46 rows in set (0.00 sec)

**stats_mysql_connection_pool **:
20170727142512

show mysql variabes
-> gist

seanlook commented Jul 27, 2017

show mysql status

mysql> SHOW MYSQL STATUS;
+------------------------------+---------------+
| Variable_name                | Value         |
+------------------------------+---------------+
| Active_Transactions          | 0             |
| Backend_query_time_nsec      | 8034008154580 |
| Client_Connections_aborted   | 0             |
| Client_Connections_connected | 46            |
| Client_Connections_created   | 42976680      |
| Client_Connections_non_idle  | 43            |
| Com_autocommit               | 0             |
| Com_autocommit_filtered      | 0             |
| Com_commit                   | 1131          |
| Com_commit_filtered          | 0             |
| Com_rollback                 | 2             |
| Com_rollback_filtered        | 0             |
| Com_stmt_close               | 1523499       |
| Com_stmt_execute             | 1523499       |
| Com_stmt_prepare             | 1523499       |
| ConnPool_get_conn_failure    | 0             |
| ConnPool_get_conn_immediate  | 0             |
| ConnPool_get_conn_success    | 73935169      |
| ConnPool_memory_bytes        | 1712          |
| MySQL_Monitor_Workers        | 8             |
| MySQL_Thread_Workers         | 4             |
| ProxySQL_Uptime              | 757611        |
| Queries_backends_bytes_recv  | 14920851328   |
| Queries_backends_bytes_sent  | 32813702749   |
| Query_Cache_Entries          | 0             |
| Query_Cache_Memory_bytes     | 0             |
| Query_Cache_Purged           | 0             |
| Query_Cache_bytes_IN         | 0             |
| Query_Cache_bytes_OUT        | 0             |
| Query_Cache_count_GET        | 0             |
| Query_Cache_count_GET_OK     | 0             |
| Query_Cache_count_SET        | 0             |
| Query_Processor_time_nsec    | 2524961125231 |
| Questions                    | 275841450     |
| SQLite3_memory_bytes         | 4484816       |
| Server_Connections_aborted   | 0             |
| Server_Connections_connected | 73            |
| Server_Connections_created   | 73935169      |
| Servers_table_version        | 1             |
| Slow_queries                 | 1305          |
| Stmt_Active_Total            | 0             |
| Stmt_Active_Unique           | 0             |
| Stmt_Max_Stmt_id             | 900           |
| mysql_backend_buffers_bytes  | 5176576       |
| mysql_frontend_buffers_bytes | 3014656       |
| mysql_session_internal_bytes | 137792        |
+------------------------------+---------------+
46 rows in set (0.00 sec)

**stats_mysql_connection_pool **:
20170727142512

show mysql variabes
-> gist

@renecannao

This comment has been minimized.

Show comment
Hide comment
@renecannao

renecannao Jul 27, 2017

Contributor

Hi Sean,

Thank you for the output. I think the reason of high backend connections is my initial hypothesis:

Are you using read/write split or other query routing?
If yes, a single client connection can result in multiple backend connections, depending from where queries are routed.

You do not have a single hostgroup where ConnOK is greater than Client_Connections_created . So it seems like most of the client connections have a connection to HG 110 and a connection to HG 1010 . Remember that disabling connection multiplexing doesn't disable query routing.
Makes sense?

Contributor

renecannao commented Jul 27, 2017

Hi Sean,

Thank you for the output. I think the reason of high backend connections is my initial hypothesis:

Are you using read/write split or other query routing?
If yes, a single client connection can result in multiple backend connections, depending from where queries are routed.

You do not have a single hostgroup where ConnOK is greater than Client_Connections_created . So it seems like most of the client connections have a connection to HG 110 and a connection to HG 1010 . Remember that disabling connection multiplexing doesn't disable query routing.
Makes sense?

@seanlook

This comment has been minimized.

Show comment
Hide comment
@seanlook

seanlook Jul 27, 2017

Sorry for not get you.

You mean any queries that don't match rule:44 will try to match the next one, but the previous rule have connection created though not use it ?

+---------+--------+----------+--------+-------------------------------+---------------+----------------------+------+-------+--------------+
| rule_id | active | username | digest | substr(match_digest,30,-30)   | match_pattern | negate_match_pattern | HG   | apply | comment      |
+---------+--------+----------+--------+-------------------------------+---------------+----------------------+------+-------+--------------+
| 44      | 1      | NULL     | NULL   | (XXXXXXXXXXXXX|ZZZZZZZZZZZZZ) | NULL          | 0                    | 110  | 1     | force master |
| 50      | 1      | NULL     | NULL   | ^select                       | NULL          | 0                    | 1010 | 1     | NULL         |
| 53      | 1      | NULL     | NULL   | set.*read write               | NULL          | 0                    | 110  | 1     | NULL         |
| 55      | 1      | NULL     | NULL   | ^select                       | NULL          | 1                    | 110  | 1     | NULL         |
+---------+--------+----------+--------+-------------------------------+---------------+----------------------+------+-------+--------------+

And this doesn't explain why connection pool doesn't work:

if you have multiplex disabled, the connection will never go back to the connection pool.

If I have multiplex disable and my frontend close the connection, what will happen to the backend connections? Close it or keep it alive?

seanlook commented Jul 27, 2017

Sorry for not get you.

You mean any queries that don't match rule:44 will try to match the next one, but the previous rule have connection created though not use it ?

+---------+--------+----------+--------+-------------------------------+---------------+----------------------+------+-------+--------------+
| rule_id | active | username | digest | substr(match_digest,30,-30)   | match_pattern | negate_match_pattern | HG   | apply | comment      |
+---------+--------+----------+--------+-------------------------------+---------------+----------------------+------+-------+--------------+
| 44      | 1      | NULL     | NULL   | (XXXXXXXXXXXXX|ZZZZZZZZZZZZZ) | NULL          | 0                    | 110  | 1     | force master |
| 50      | 1      | NULL     | NULL   | ^select                       | NULL          | 0                    | 1010 | 1     | NULL         |
| 53      | 1      | NULL     | NULL   | set.*read write               | NULL          | 0                    | 110  | 1     | NULL         |
| 55      | 1      | NULL     | NULL   | ^select                       | NULL          | 1                    | 110  | 1     | NULL         |
+---------+--------+----------+--------+-------------------------------+---------------+----------------------+------+-------+--------------+

And this doesn't explain why connection pool doesn't work:

if you have multiplex disabled, the connection will never go back to the connection pool.

If I have multiplex disable and my frontend close the connection, what will happen to the backend connections? Close it or keep it alive?

@renecannao

This comment has been minimized.

Show comment
Hide comment
@renecannao

renecannao Jul 27, 2017

Contributor

You mean any queries that don't match rule:44 will try to match the next one, but the previous rule have connection created though not use it ?

Not exactly. A connection is established when a query needs to be executed.
Let me make an example.

  • client connects to proxysql : no connections are established to backend yet
  • client sends a query that matched rule 53 : a connection is created to HG 110
  • client sends a query that matched rule 55 : the same connection to HG 110 is used
  • client sends a query that matched rule 50 : a connection is created to HG 1010.

Now you have 1 client connection and 2 backend connections.
This is expected, because disabling multiplexing doesn't disable query routing, so a single client connection can lead to multiple connections on different hostgroups.

If I have multiplex disable and my frontend close the connection, what will happen to the backend connections? Close it or keep it alive?

In ProxySQL 1.3.x , a connection with multiplexing disabled cannot return to the connection pool. So if the client closes the connection to proxysql, all its connections to the backends are closed. This happens because if multiplexing is disabled it means that the connection is in some state that makes it not sharable, therefore cannot be re-used by another client. Also note that this happens also when multiplexing is enabled globally but some event caused multiplexing to be disabled in a specific connection, for example if a temporary table is created.

ProxySQL 1.4.x works differently. When a backend connection needs to be closed because multiplexing is disabled, instead of being closed immediately it is placed into a queue: a background thread will reset the connection and place it back to the connection pool.

Contributor

renecannao commented Jul 27, 2017

You mean any queries that don't match rule:44 will try to match the next one, but the previous rule have connection created though not use it ?

Not exactly. A connection is established when a query needs to be executed.
Let me make an example.

  • client connects to proxysql : no connections are established to backend yet
  • client sends a query that matched rule 53 : a connection is created to HG 110
  • client sends a query that matched rule 55 : the same connection to HG 110 is used
  • client sends a query that matched rule 50 : a connection is created to HG 1010.

Now you have 1 client connection and 2 backend connections.
This is expected, because disabling multiplexing doesn't disable query routing, so a single client connection can lead to multiple connections on different hostgroups.

If I have multiplex disable and my frontend close the connection, what will happen to the backend connections? Close it or keep it alive?

In ProxySQL 1.3.x , a connection with multiplexing disabled cannot return to the connection pool. So if the client closes the connection to proxysql, all its connections to the backends are closed. This happens because if multiplexing is disabled it means that the connection is in some state that makes it not sharable, therefore cannot be re-used by another client. Also note that this happens also when multiplexing is enabled globally but some event caused multiplexing to be disabled in a specific connection, for example if a temporary table is created.

ProxySQL 1.4.x works differently. When a backend connection needs to be closed because multiplexing is disabled, instead of being closed immediately it is placed into a queue: a background thread will reset the connection and place it back to the connection pool.

@seanlook

This comment has been minimized.

Show comment
Hide comment
@seanlook

seanlook Jul 27, 2017

Got you the first point.

So in PrxoySQL 1.3.x, disabling multiplexing means no any connection pool feature, and enabling multiplexing means both multiplex and connection pool work, right?

Quote from your article:

When a client connects to ProxySQL no connection to any backend is established. When a client issues a request ProxySQL determines if the request needs a connection to the backend or not, and only if required it forwards the request to a backend. As soon as the request is completed, ProxySQL determines if the connection to the backend is still required, and if not it returns it to a connection pool.
That is: the connection to the backend is returned to the connection pool not when the client disconnects, but when the request is completed, assuming that it can be reused by other clients (for example if there are no active transactions).
Similarly, when a backend connection is linked to a client connection, ProxySQL will ensure that the environment is set correctly : schema, charset, autocommit, etc .
In other words, ProxySQL doesn't just implement Persistent Connection, but also Connection Multiplexing. In fact, ProxySQL can handle hundreds of thousands of clients, yet forward all their traffic to few connections to the backend.

seanlook commented Jul 27, 2017

Got you the first point.

So in PrxoySQL 1.3.x, disabling multiplexing means no any connection pool feature, and enabling multiplexing means both multiplex and connection pool work, right?

Quote from your article:

When a client connects to ProxySQL no connection to any backend is established. When a client issues a request ProxySQL determines if the request needs a connection to the backend or not, and only if required it forwards the request to a backend. As soon as the request is completed, ProxySQL determines if the connection to the backend is still required, and if not it returns it to a connection pool.
That is: the connection to the backend is returned to the connection pool not when the client disconnects, but when the request is completed, assuming that it can be reused by other clients (for example if there are no active transactions).
Similarly, when a backend connection is linked to a client connection, ProxySQL will ensure that the environment is set correctly : schema, charset, autocommit, etc .
In other words, ProxySQL doesn't just implement Persistent Connection, but also Connection Multiplexing. In fact, ProxySQL can handle hundreds of thousands of clients, yet forward all their traffic to few connections to the backend.

@renecannao

This comment has been minimized.

Show comment
Hide comment
@renecannao

renecannao Jul 27, 2017

Contributor

That's correct.
ProxySQL's connection pool is more advanced than other normal connection pool.
In normal connection pool, a connection is assigned to a client until the client disconnects and then the connection returns back to the connection pool.
In ProxySQL a connection is returned to the connection pool as soon as possible, if the connection is sharable. If the connection isn't sharable when the client disconnects, the connection is dropped.

Contributor

renecannao commented Jul 27, 2017

That's correct.
ProxySQL's connection pool is more advanced than other normal connection pool.
In normal connection pool, a connection is assigned to a client until the client disconnects and then the connection returns back to the connection pool.
In ProxySQL a connection is returned to the connection pool as soon as possible, if the connection is sharable. If the connection isn't sharable when the client disconnects, the connection is dropped.

@seanlook

This comment has been minimized.

Show comment
Hide comment
@seanlook

seanlook Jul 27, 2017

You're so nice. Thank you so much.
By the way, would you consider to let proxysql have normal connection pool feature when disabling multiplexing?

seanlook commented Jul 27, 2017

You're so nice. Thank you so much.
By the way, would you consider to let proxysql have normal connection pool feature when disabling multiplexing?

@renecannao

This comment has been minimized.

Show comment
Hide comment
@renecannao

renecannao Jul 27, 2017

Contributor

That is basically what ProxySQL 1.4.x does :)
But the state of the connection needs to be reset, and this operation is performed in a background thread to not interfere with regular traffic

Contributor

renecannao commented Jul 27, 2017

That is basically what ProxySQL 1.4.x does :)
But the state of the connection needs to be reset, and this operation is performed in a background thread to not interfere with regular traffic

@seanlook

This comment has been minimized.

Show comment
Hide comment
@seanlook

seanlook Jul 27, 2017

When version 1.4.x is stable enough, I'll upgrade to it.

What about setting mysql-max_stmts_per_connection failed ?

seanlook commented Jul 27, 2017

When version 1.4.x is stable enough, I'll upgrade to it.

What about setting mysql-max_stmts_per_connection failed ?

@renecannao

This comment has been minimized.

Show comment
Hide comment
@renecannao

renecannao Jul 27, 2017

Contributor

The current branch 1.4.1 is being tested in production, successfully.
I am waiting a bit longer before releasing it.

mysql-max_stmts_per_connection "fails" because the value is out of range.
Allowed range is between 1 and 1024 : https://github.com/sysown/proxysql/blob/v1.3.6/lib/MySQL_Thread.cpp#L1194

Contributor

renecannao commented Jul 27, 2017

The current branch 1.4.1 is being tested in production, successfully.
I am waiting a bit longer before releasing it.

mysql-max_stmts_per_connection "fails" because the value is out of range.
Allowed range is between 1 and 1024 : https://github.com/sysown/proxysql/blob/v1.3.6/lib/MySQL_Thread.cpp#L1194

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment