Fluctuations in persistent connections in connection pool with backend.

507 views

Skip to first unread message

amit bali

unread,

Dec 29, 2016, 5:23:25 AM12/29/16

to proxysql

Hello Renne,

We were able to successfully deploy our code to use proxysql and it is exceeding our expectations in terms of load capacity that it is handling currently.

It has been two weeks since the proxysql is sustaining our heavy connection traffic.

However from past three days i am noticing that the connection pools that we have maintained (20 on each proxysql machine and 3 machines in all) keeps fluctuating

from 10 (+- 2), hence it keeps on ranging between 8 to 12, what looks to be of concern is that this fluctuation is happening very frequently(around 30+ times per hour).

If we run netstat on the proxysql instance, we find that indeed the proxysql is creating 3 connections and destroying them very frequetly but keeps only one connection alive

persistently.

Also the proxysql instances are NOT throttled for CPU\memory (both being under 5%) on all instances.

Also we are pushing around 1000-4000 connections per minute to each proxy sql instance.

Although we are not seeing any connection failiures or connection errors from client side(also i do not see any errors in proxysql logs). I just wanted to understand if i should be even concerned with this flakiness.

If required i can send the configuration details for the proxysql.

This fluctuation has started recently from past three days and was not there for past two weeks.

We did introduce some changes in application and backend side, but i do not know if they could affect the normal functioning of the proxysql since they do ensure the parameters

like connections/minute, DML are unchanged. However we did add 3 more host groups. Dont know if that should cause this trouble.

amit bali

unread,

Dec 29, 2016, 5:40:46 AM12/29/16

to proxysql

BTW for past two weeks it was a flat line of 30 connections with the backend from all proxysqls.

amit bali

unread,

Dec 29, 2016, 5:51:09 AM12/29/16

to proxysql

Below are the stats that i had captured for a newly launched proxy sql instance running for around 30-45 minutes:

+-----------+--------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+

| 1 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | 3306 | ONLINE | 0 | 2 | 1220 | 0 | 279252 | 176566078 | 2201477 | 605 |

mysql> select * from stats_mysql_global;

+------------------------------+----------------+

| Variable_Name | Variable_Value |

+------------------------------+----------------+

| Active_Transactions | 0 |

| Client_Connections_aborted | 0 |

| Client_Connections_connected | 1 |

| Client_Connections_created | 78424 |

| Server_Connections_aborted | 0 |

| Server_Connections_connected | 2 |

| Server_Connections_created | 1258 |

| Client_Connections_non_idle | 1 |

| Queries_backends_bytes_recv | 2264755 |

| Queries_backends_bytes_sent | 181615676 |

| Query_Processor_time_nsec | 204071673 |

| Backend_query_time_nsec | 5075847235 |

| mysql_backend_buffers_bytes | 0 |

| mysql_frontend_buffers_bytes | 65536 |

| mysql_session_internal_bytes | 22072 |

| Com_autocommit | 78095 |

| Com_autocommit_filtered | 78095 |

| Com_commit | 0 |

| Com_commit_filtered | 0 |

| Com_rollback | 0 |

| Com_rollback_filtered | 0 |

| Com_stmt_prepare | 105155 |

| Com_stmt_execute | 105155 |

| Com_stmt_close | 105155 |

| Questions | 549897 |

| Slow_queries | 1 |

| Servers_table_version | 2 |

| MySQL_Thread_Workers | 4 |

| MySQL_Monitor_Workers | 8 |

| ConnPool_get_conn_immediate | 82 |

| ConnPool_get_conn_success | 261528 |

| ConnPool_get_conn_failure | 0 |

| SQLite3_memory_bytes | 827632 |

| ConnPool_memory_bytes | 142528 |

| Stmt_Active_Total | 0 |

| Stmt_Active_Unique | 0 |

| Stmt_Max_Stmt_id | 36 |

| Query_Cache_Memory_bytes | 0 |

| Query_Cache_count_GET | 0 |

| Query_Cache_count_GET_OK | 0 |

| Query_Cache_count_SET | 0 |

| Query_Cache_bytes_IN | 0 |

| Query_Cache_bytes_OUT | 0 |

| Query_Cache_Purged | 0 |

| Query_Cache_Entries | 0 |

+------------------------------+----------------+

On Thursday, December 29, 2016 at 3:53:25 PM UTC+5:30, amit bali wrote:

René Cannaò

unread,

Dec 29, 2016, 2:39:21 PM12/29/16

to amit bali, proxysql

Hello Amit,

There are several reasons why, in absence of any error, ProxySQL may consider to close a connection instead of reusing it.

It could be because:

a) it is configured with a very low value of max_connections in mysql_servers,please look into: SELECT hostgroup_id, max_connections FROM mysql_servers;

b) it is configured to keep very few free open connections. Check : SELECT hostgroup_id, max_connections FROM mysql_servers;

c) there are conditions for which it is not safe to return the connection in the connection pool, for example if temporary tables exists of user variables were defined (for example, using "SET @a=..." or "SELECT ... INTO @a" ). A look into table stats_mysql_query_digest may help identifying these queries.

Because you mentioned you introduced some change in the last few days, maybe option #c is the most probable one.

If you want to send me directly the content of stats_mysql_query_digest and I can have a look at it.

To answer your question: these fluctuations do not seem concerning, as they are reasonably small fluctuations.
Although, there is an open ticket (https://github.com/sysown/proxysql/issues/778) to try to reset connections instead of dropping them, but this is relevant only when thousands of connections are dropped per second.

Thanks,

René

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Rajat Shukla

unread,

Dec 30, 2016, 6:12:55 AM12/30/16

to proxysql, amitku...@gmail.com

Hi Rene,

I am Amit's colleague, I am providing you with the data you asked.

a) and b) Check : SELECT hostgroup_id, max_connections FROM mysql_servers;

mysql> SELECT hostgroup_id, max_connections FROM mysql_servers;

+--------------+-----------------+

| hostgroup_id | max_connections |

+--------------+-----------------+

| 1 | 20 |

+--------------+-----------------+

1 row in set (0.00 sec)

mysql>

c) there are conditions for which it is not safe to return the connection in the connection pool, for example if temporary tables exists of user variables were defined (for example, using "SET @a=..." or "SELECT ... INTO @a" ). A look into table stats_mysql_query_digest may help identifying these queries.

mysql> select * from stats_mysql_query_digest;

+-----------+--------------------+----------+--------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------+------------+-----------+----------+----------+

| 1 | information_schema | mysqluser | 0xF3200F550B172F62 | select * from TABLES limit ? | 2 | 1483019953 | 1483019972 | 87511 | 14074 | 73437 |

| 1 | information_schema | mysqluser | 0xB06310E983BD5E0B | show variables like ? | 2 | 1483014883 | 1483014892 | 3094 | 1326 | 1768 |

| 1 | information_schema | mysqluser | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 6 | 1483014833 | 1483020339 | 0 | 0 | 0 |

| 1 | information_schema | mysqluser | 0xD75891CF47C66635 | select * from processlist limit ? | 2 | 1483020121 | 1483020129 | 3534 | 1581 | 1953 |

| 1 | data_backup_06 | mysqluser | 0x5A37D48832A2474A | DELETE FROM data_backup_06.data_2_status_app_folder_sync_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_06.data_2_status_app_folder_list_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_06.data_2_status_common where user_id=? and account_id=? and job_id=?; | 17 | 1483010468 | 1483084956 | 21614 | 869 | 2308 |

| 1 | data_backup_01 | mysqluser | 0x8F7D53A97678E536 | DELETE FROM data_backup_01.data_2_status_app_folder_sync_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_01.data_2_status_app_folder_list_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_01.data_2_status_common where user_id=? and account_id=? and job_id=?; | 63 | 1483010271 | 1483092996 | 79817 | 716 | 2655 |

| 1 | data_backup_02 | mysqluser | 0x2F4CEB9F832480C0 | DELETE FROM data_backup_02.data_2_status_app_folder_sync_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_02.data_2_status_app_folder_list_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_02.data_2_status_common where user_id=? and account_id=? and job_id=?; | 69 | 1483007419 | 1483093189 | 85880 | 764 | 2610 |

| 1 | data_backup_05 | mysqluser | 0x024CD09B7E1879C3 | DELETE FROM data_backup_05.data_2_status_app_folder_sync_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_05.data_2_status_app_folder_list_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_05.data_2_status_common where user_id=? and account_id=? and job_id=?; | 84 | 1483007032 | 1483094096 | 108888 | 775 | 5165 |

| 1 | data_backup_04 | mysqluser | 0xF77651AD13F8C607 | DELETE FROM data_backup_04.data_2_status_app_folder_sync_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_04.data_2_status_app_folder_list_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_04.data_2_status_common where user_id=? and account_id=? and job_id=?; | 68 | 1483006844 | 1483095208 | 84235 | 760 | 4937 |

| 1 | data_backup_06 | mysqluser | 0x366DD0E561BAABD6 | DELETE FROM data_backup_06.data_3_status_app_folder_sync_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_06.data_3_status_app_folder_list_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_06.data_3_status_common where user_id=? and account_id=? and job_id=?; | 52 | 1483006201 | 1483086754 | 65802 | 745 | 4300 |

| 1 | data_backup_01 | mysqluser | 0x56E788D4389F4C9B | DELETE FROM data_backup_01.data_3_status_app_folder_sync_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_01.data_3_status_app_folder_list_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_01.data_3_status_common where user_id=? and account_id=? and job_id=?; | 120 | 1483006037 | 1483094687 | 148844 | 747 | 5203 |

| 1 | data_backup_04 | mysqluser | 0x867FFC57B3FD9FB2 | DELETE FROM data_backup_04.data_1_status_app_folder_sync_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_04.data_1_status_app_folder_list_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_04.data_1_status_common where user_id=? and account_id=? and job_id=?; | 212 | 1483005930 | 1483094859 | 266825 | 725 | 9274 |

| 1 | data_backup_02 | mysqluser | 0x871806977E80874C | DELETE FROM data_backup_02.data_3_status_app_folder_sync_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_02.data_3_status_app_folder_list_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_02.data_3_status_common where user_id=? and account_id=? and job_id=?; | 143 | 1483005855 | 1483094869 | 200503 | 718 | 24441 |

| 1 | data_backup_05 | mysqluser | 0xC79C8CCE69242994 | DELETE FROM data_backup_05.data_3_status_app_folder_sync_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_05.data_3_status_app_folder_list_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_05.data_3_status_common where user_id=? and account_id=? and job_id=?; | 161 | 1483005756 | 1483094885 | 212360 | 750 | 6817 |

| 1 | data_backup_06 | mysqluser | 0x0D0EFD1B509F8892 | DELETE FROM data_backup_06.data_1_status_app_folder_sync_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_06.data_1_status_app_folder_list_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_06.data_1_status_common where user_id=? and account_id=? and job_id=?; | 46 | 1483005688 | 1483095809 | 67657 | 767 | 4835 |

| 1 | data_backup_03 | mysqluser | 0x998773B97A72E9D8 | DELETE FROM data_backup_03.data_3_status_app_folder_sync_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_03.data_3_status_app_folder_list_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_03.data_3_status_common where user_id=? and account_id=? and job_id=?; | 93 | 1483006336 | 1483092670 | 117417 | 725 | 4713 |

| 1 | data_backup_02 | mysqluser | 0x9A4A06AF37F3C9EA | DELETE FROM data_backup_02.data_1_status_app_folder_sync_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_02.data_1_status_app_folder_list_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_02.data_1_status_common where user_id=? and account_id=? and job_id=?; | 163 | 1483005487 | 1483093415 | 223878 | 750 | 10583 |

| 1 | data_backup_06 | mysqluser | 0x20D6396B3ECD36D0 | INSERT INTO data_backup_06.data_2_status_app_folder_list_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 336 | 1483005414 | 1483094480 | 657442 | 1031 | 30176 |

| 1 | data_backup_06 | mysqluser | 0x2FB86F938EF30630 | set autocommit=? | 272550 | 1483005214 | 1483095866 | 0 | 0 | 0 |

| 1 | data_backup_02 | mysqluser | 0x991E2B3BC61D076B | INSERT INTO data_backup_02.data_2_status_app_folder_sync_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 63108 | 1483005213 | 1483095866 | 122414878 | 628 | 221808 |

| 1 | information_schema | mysqluser | 0x99531AEFF718C501 | show tables | 3 | 1483015167 | 1483020077 | 3696 | 1063 | 1327 |

| 1 | data_backup_06 | mysqluser | 0x508A5B82C308DD85 | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ | 272550 | 1483005214 | 1483095866 | 485364516 | 381 | 635615 |

| 1 | data_backup_03 | mysqluser | 0xF926C1D6A73EA5A4 | DELETE FROM data_backup_03.data_1_status_app_folder_sync_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_03.data_1_status_app_folder_list_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_03.data_1_status_common where user_id=? and account_id=? and job_id=?; | 132 | 1483005812 | 1483093151 | 173463 | 747 | 8671 |

| 1 | data_backup_01 | mysqluser | 0xE59DC13C82F66FBE | INSERT INTO data_backup_01.data_2_status_app_folder_list_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 606 | 1483005523 | 1483095293 | 1337457 | 534 | 50896 |

| 1 | data_backup_04 | mysqluser | 0x508A5B82C308DD85 | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ | 449807 | 1483005213 | 1483095867 | 771975735 | 385 | 2018220 |

| 1 | data_backup_02 | mysqluser | 0xC8D0DB0FA3D5C98D | INSERT INTO data_backup_02.data_1_status_app_folder_sync_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 591510 | 1483005212 | 1483095867 | 842724005 | 451 | 625540 |

| 1 | data_backup_04 | mysqluser | 0xEC0884B590BA9B73 | INSERT INTO data_backup_04.data_3_status_app_folder_list_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 3526 | 1483005408 | 1483095832 | 4392927 | 527 | 209930 |

| 1 | data_backup_04 | mysqluser | 0x2FB86F938EF30630 | set autocommit=? | 449807 | 1483005213 | 1483095867 | 0 | 0 | 0 |

| 1 | data_backup_05 | mysqluser | 0x19A02489F5616360 | INSERT INTO data_backup_05.data_3_status_app_folder_list_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 3674 | 1483005255 | 1483095793 | 3994323 | 534 | 64135 |

| 1 | data_backup_06 | mysqluser | 0x769A9E1020299132 | INSERT INTO data_backup_06.data_3_status_app_folder_list_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 1466 | 1483005378 | 1483095755 | 1641168 | 617 | 36511 |

| 1 | data_backup_03 | mysqluser | 0x314CB61A3AEAF90C | SHOW SESSION VARIABLES LIKE ? | 328066 | 1483005213 | 1483095867 | 386408491 | 649 | 623755 |

| 1 | data_backup_03 | mysqluser | 0x508A5B82C308DD85 | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ | 328066 | 1483005213 | 1483095867 | 573799678 | 376 | 406495 |

| 1 | data_backup_01 | mysqluser | 0x508A5B82C308DD85 | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ | 379899 | 1483005212 | 1483095867 | 661712363 | 385 | 1019220 |

| 1 | information_schema | mysqluser | 0x7A98D462C087EA42 | show variables | 1 | 1483014853 | 1483014853 | 2433 | 2433 | 2433 |

| 1 | data_backup_02 | mysqluser | 0x2FB86F938EF30630 | set autocommit=? | 413732 | 1483005212 | 1483095867 | 0 | 0 | 0 |

| 1 | information_schema | mysqluser | 0x0D55D6AF09FA1C11 | select * from processlist where INFO like ? limit ? | 1 | 1483020181 | 1483020181 | 1426 | 1426 | 1426 |

| 1 | data_backup_03 | mysqluser | 0x475CA35E9CA92A8A | DELETE FROM data_backup_03.data_2_status_app_folder_sync_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_03.data_2_status_app_folder_list_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_03.data_2_status_common where user_id=? and account_id=? and job_id=?; | 41 | 1483006704 | 1483089710 | 55129 | 729 | 5217 |

| 1 | data_backup_01 | mysqluser | 0x2E9E69CE0F3612C3 | INSERT INTO data_backup_01.data_1_status_app_folder_list_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 270872 | 1483005213 | 1483095866 | 163373385 | 463 | 423038 |

| 1 | data_backup_02 | mysqluser | 0x1E8C0AEB0B0C7A97 | INSERT INTO data_backup_02.data_3_status_app_folder_list_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 2956 | 1483005328 | 1483095811 | 3189761 | 511 | 74470 |

| 1 | data_backup_04 | mysqluser | 0x81B8BF74BF1F31C7 | DELETE FROM data_backup_04.data_3_status_app_folder_sync_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_04.data_3_status_app_folder_list_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_04.data_3_status_common where user_id=? and account_id=? and job_id=?; | 153 | 1483005387 | 1483095072 | 195655 | 779 | 5868 |

| 1 | data_backup_03 | mysqluser | 0x6EF9DDC69B87793B | INSERT INTO data_backup_03.data_1_status_app_folder_list_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 245982 | 1483005226 | 1483095866 | 150674202 | 467 | 214989 |

| 1 | data_backup_02 | mysqluser | 0x314CB61A3AEAF90C | SHOW SESSION VARIABLES LIKE ? | 413732 | 1483005212 | 1483095867 | 481803012 | 648 | 441458 |

| 1 | data_backup_02 | mysqluser | 0x4521992D221E58B6 | INSERT INTO data_backup_02.data_3_status_app_folder_sync_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 169636 | 1483005212 | 1483095867 | 316062042 | 517 | 218854 |

| 1 | information_schema | mysqluser | 0x420334C5F28F20E4 | select * from processlist where HOST like ? | 1 | 1483020290 | 1483020290 | 1570 | 1570 | 1570 |

| 1 | data_backup_01 | mysqluser | 0xCD1FE285134D818D | INSERT INTO data_backup_01.data_3_status_app_folder_sync_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 171656 | 1483005214 | 1483095867 | 323211168 | 509 | 219800 |

| 1 | data_backup_01 | mysqluser | 0x0979F97A27D97358 | DELETE FROM data_backup_01.data_1_status_app_folder_sync_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_01.data_1_status_app_folder_list_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_01.data_1_status_common where user_id=? and account_id=? and job_id=?; | 137 | 1483006290 | 1483094814 | 187606 | 724 | 4719 |

| 1 | data_backup_05 | mysqluser | 0x508A5B82C308DD85 | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ | 392687 | 1483005214 | 1483095867 | 674714029 | 386 | 1030863 |

| 1 | data_backup_06 | mysqluser | 0x5C90352BE5F84553 | INSERT INTO data_backup_06.data_3_status_app_folder_sync_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 112806 | 1483005221 | 1483095866 | 216391666 | 498 | 231634 |

| 1 | data_backup_01 | mysqluser | 0x745390BCC7FF9144 | INSERT INTO data_backup_01.data_2_status_app_folder_sync_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 54586 | 1483005212 | 1483095862 | 106618232 | 467 | 215855 |

| 1 | data_backup_01 | mysqluser | 0x314CB61A3AEAF90C | SHOW SESSION VARIABLES LIKE ? | 379899 | 1483005212 | 1483095867 | 439967656 | 641 | 634579 |

| 1 | information_schema | mysqluser | 0xDAFA2CDB478883C1 | select * from processlist where HOST like ? limit ? | 46 | 1483020285 | 1483020552 | 82617 | 1271 | 3577 |

| 1 | data_backup_02 | mysqluser | 0x66FC3A505415EC4A | INSERT INTO data_backup_02.data_1_status_app_folder_list_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 298494 | 1483005212 | 1483095867 | 177699866 | 452 | 399190 |

| 1 | data_backup_06 | mysqluser | 0x314CB61A3AEAF90C | SHOW SESSION VARIABLES LIKE ? | 272550 | 1483005214 | 1483095866 | 321046091 | 644 | 430940 |

| 1 | data_backup_06 | mysqluser | 0x1076B723010D4C3A | INSERT INTO data_backup_06.data_1_status_app_folder_list_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 205422 | 1483005214 | 1483095866 | 127907912 | 457 | 424046 |

| 1 | data_backup_03 | mysqluser | 0x1561C374029157AE | INSERT INTO data_backup_03.data_2_status_app_folder_list_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 580 | 1483005742 | 1483095313 | 1218202 | 472 | 48120 |

| 1 | data_backup_03 | mysqluser | 0xDBF3D4DC29BF5DBC | INSERT INTO data_backup_03.data_3_status_app_folder_sync_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 135292 | 1483005221 | 1483095865 | 257101279 | 488 | 222387 |

| 1 | data_backup_03 | mysqluser | 0x3ACA8BFF1459FBA2 | INSERT INTO data_backup_03.data_3_status_app_folder_list_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 1857 | 1483005744 | 1483095729 | 2413021 | 546 | 44826 |

| 1 | data_backup_06 | mysqluser | 0xA00D114A9DBAFC57 | INSERT INTO data_backup_06.data_1_status_app_folder_sync_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 389072 | 1483005214 | 1483095866 | 555301663 | 450 | 433924 |

| 1 | data_backup_01 | mysqluser | 0x8DAADE5EDEC89DAC | INSERT INTO data_backup_01.data_1_status_app_folder_sync_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 530818 | 1483005213 | 1483095867 | 760316229 | 447 | 3013173 |

| 1 | data_backup_05 | mysqluser | 0x2FB86F938EF30630 | set autocommit=? | 392687 | 1483005214 | 1483095867 | 0 | 0 | 0 |

| 1 | data_backup_05 | mysqluser | 0xCC4C134D7E9C74CC | INSERT INTO data_backup_05.data_1_status_app_folder_sync_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 557000 | 1483005214 | 1483095867 | 787456626 | 454 | 403577 |

| 1 | data_backup_05 | mysqluser | 0x72EAA82AA71E43FD | DELETE FROM data_backup_05.data_1_status_app_folder_sync_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_05.data_1_status_app_folder_list_info where user_id=? and account_id=? and job_id=?;DELETE FROM data_backup_05.data_1_status_common where user_id=? and account_id=? and job_id=?; | 186 | 1483006713 | 1483094771 | 238795 | 705 | 5175 |

| 1 | data_backup_05 | mysqluser | 0x4D58FB5A40202F18 | INSERT INTO data_backup_05.data_1_status_app_folder_list_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 287756 | 1483005215 | 1483095864 | 171701310 | 458 | 405715 |

| 1 | data_backup_04 | mysqluser | 0x0C1FD941541785EF | INSERT INTO data_backup_04.data_3_status_app_folder_sync_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 182420 | 1483005215 | 1483095867 | 340917355 | 510 | 411456 |

| 1 | data_backup_02 | mysqluser | 0x508A5B82C308DD85 | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ | 413732 | 1483005212 | 1483095867 | 716693053 | 385 | 1003640 |

| 1 | data_backup_05 | mysqluser | 0xF58150AC5A82A702 | INSERT INTO data_backup_05.data_3_status_app_folder_sync_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 167060 | 1483005215 | 1483095867 | 312664458 | 486 | 227550 |

| 1 | data_backup_04 | mysqluser | 0x022B0707CA3B8C17 | INSERT INTO data_backup_04.data_1_status_app_folder_list_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 334206 | 1483005214 | 1483095866 | 197080491 | 461 | 423408 |

| 1 | data_backup_04 | mysqluser | 0x52A1E67EB8D60B87 | INSERT INTO data_backup_04.data_2_status_app_folder_sync_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 68618 | 1483005215 | 1483095865 | 133218458 | 501 | 217959 |

| 1 | data_backup_06 | mysqluser | 0x3DE22B3C22DD25AC | INSERT INTO data_backup_06.data_2_status_app_folder_sync_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 41854 | 1483005217 | 1483095865 | 82762142 | 444 | 217067 |

| 1 | data_backup_04 | mysqluser | 0x4F726F7D73F08219 | INSERT INTO data_backup_04.data_2_status_app_folder_list_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 734 | 1483005587 | 1483095682 | 1583302 | 483 | 108720 |

| 1 | data_backup_03 | mysqluser | 0x01D91D058CAE8ADB | INSERT INTO data_backup_03.data_1_status_app_folder_sync_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 466794 | 1483005213 | 1483095867 | 659121642 | 450 | 218783 |

| 1 | data_backup_01 | mysqluser | 0x2FB86F938EF30630 | set autocommit=? | 379899 | 1483005212 | 1483095867 | 0 | 0 | 0 |

| 1 | data_backup_05 | mysqluser | 0xB4A45FE9FF6D2EF1 | INSERT INTO data_backup_05.data_2_status_app_folder_sync_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 56866 | 1483005217 | 1483095866 | 110953137 | 1635 | 218763 |

| 1 | data_backup_03 | mysqluser | 0x2FB86F938EF30630 | set autocommit=? | 328066 | 1483005213 | 1483095867 | 0 | 0 | 0 |

| 1 | data_backup_03 | mysqluser | 0x938A7974644D6ACC | INSERT INTO data_backup_03.data_2_status_app_folder_sync_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 51642 | 1483005226 | 1483095863 | 100999393 | 496 | 210994 |

| 1 | information_schema | mysqluser | 0x96BC3B4B3117DE2B | select * from runtime_global_variables | 1 | 1483017715 | 1483017715 | 978 | 978 | 978 |

| 1 | data_backup_05 | mysqluser | 0x77E9EF1946A6B264 | INSERT INTO data_backup_05.data_2_status_app_folder_list_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 688 | 1483005688 | 1483095650 | 1372190 | 567 | 48147 |

| 1 | data_backup_05 | mysqluser | 0x314CB61A3AEAF90C | SHOW SESSION VARIABLES LIKE ? | 392687 | 1483005214 | 1483095867 | 454639678 | 643 | 639962 |

| 1 | data_backup_02 | mysqluser | 0x162C7B1690BF1989 | INSERT INTO data_backup_02.data_2_status_app_folder_list_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 714 | 1483005233 | 1483095565 | 1435485 | 943 | 80208 |

| 1 | data_backup_04 | mysqluser | 0x314CB61A3AEAF90C | SHOW SESSION VARIABLES LIKE ? | 449807 | 1483005213 | 1483095867 | 518111255 | 647 | 599183 |

| 1 | data_backup_04 | mysqluser | 0xDCE897E6C53F85EC | INSERT INTO data_backup_04.data_1_status_app_folder_sync_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 645188 | 1483005213 | 1483095866 | 909236002 | 443 | 1003298 |

| 1 | data_backup_01 | mysqluser | 0x0C2327FCC5AF43D6 | INSERT INTO data_backup_01.data_3_status_app_folder_list_info (user_id, account_id, account_type, job_id, uuid, data_info) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE job_id=?, data_info=? | 2572 | 1483005306 | 1483095860 | 2953013 | 568 | 38247 |

82 rows in set (0.00 sec)

mysql>

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.

René Cannaò

unread,

Dec 30, 2016, 6:52:16 AM12/30/16

to Rajat Shukla, proxysql, amit bali

Hi Rajat,

Thank you for the output.

I just realized that in my previous reply I had a copy/paste issue.

#b was supposed to be " SHOW VARIABLES LIKE 'mysql-free_connections_pct'; "

mysql-free_connections_pct by default is 10 , and it is a percentage value.
That means that ProxySQL can keep idle connections up to mysql_servers.max_connections*mysql-free_connections_pct/100 .

In your case, mysql_servers.max_connections is 20 , so the math is: 20*10/100 = 2.

ProxySQL will keep up to 2 idle connections.

That explains why you have fluctuations.

If you want to keep max_connections=20 (quite low) I would suggest to increase mysql-free_connections_pct .

I would start trying mysql-free_connections_pct=20 :

SET mysql-free_connections_pct=20;

LOAD MYSQL VARIABLES TO RUNTIME;

SAVE MYSQL VARIABLES TO DISK;

Thanks,

René

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.

Rajat Shukla

unread,

Dec 30, 2016, 6:58:36 AM12/30/16

to proxysql, rajat....@webyog.com, amitku...@gmail.com

The value of mysql-free_connections_pct is 100 already.

mysql> SHOW VARIABLES LIKE 'mysql-free_connections_pct';

+----------------------------+-------+

| Variable_name | Value |

+----------------------------+-------+

| mysql-free_connections_pct | 100 |

+----------------------------+-------+

1 row in set (0.00 sec)

mysql>

...

René Cannaò

unread,

Dec 30, 2016, 7:18:54 AM12/30/16

to Rajat Shukla, proxysql, amit bali

Ok, so the hypothesis of mysql-free_connections_pct is not valid.

I do not see any query that could make the backend connection not reusable.

Looking your previous output:
Client_Connections_created=78424
Server_Connections_created=1258

Com_stmt_prepare=105155

Com_stmt_execute=105155

Com_stmt_close=105155
Stmt_Max_Stmt_id=36

Questions=549897

I can make the following considerations:

* client/server connections ratio is 62

* on average, each backend connection handles 437 clients' request

* around 57% of the requests are related to prepared statements

* max statement id is 36

My new hypothesis is that the backend connections are being closed because they had reached the maximum number of prepared statements allowed per connection, mysql-max_stmts_per_connection, that is 20 by default.

Try to increase mysql-max_stmts_per_connection to 40 and see if connections stay open for longer.

Note: mysql-max_stmts_per_connection is intentionally a small value by default (only 20) because the total number of prepared statements to mysqld servers across all connections shouldn't exceed max_prepared_stmt_count ( http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_prepared_stmt_count ) .

Because multiple proxies can be connected to the same mysqld server, the default in ProxySQL is conservative.

--

amit bali

unread,

Dec 30, 2016, 8:06:36 AM12/30/16

to René Cannaò, proxysql, Rajat Shukla

Ahh...we were guessing on the same lines. We currently make atmost 3-4 prepared statements under single client and proxysql connection.

We thought that for a single client to proxy connection this was well within the limit.

Hence my doubt is, does this count apply to connection between:

1) proxy and backend . OR

2) client and proxy.

Also does it have a ttl(reset duration)

Thanks and regards,

Amit Kumar Bali.

--
You received this message because you are subscribed to a topic in the Google Groups "proxysql" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/proxysql/Zm7Upcf2O28/unsubscribe.
To unsubscribe from this group and all its topics, send an email to proxysql+unsubscribe@googlegroups.com.

René Cannaò

unread,

Dec 30, 2016, 8:34:41 AM12/30/16

to amit bali, proxysql, Rajat Shukla

There is no explicit limit of number of prepared statements between client and proxy, other than the fact that a statement id is an unsigned 32 bits value.

ProxySQL internally tracks metadata related to prepared statements, and if two clients try to prepare the same statements, proxysql will return the same statement id. The statement id returned to the client is not the same of the statement id returned by the backend: they are distinct, and proxysql internally maps them.

Things become very complex because of multiplexing.

A client could prepare a statement on one backend connection, but then that backend connection will be used for another client. Thus, when the first client tries to execute a prepared statement, the proxy must re-prepare in another backend connection if the first one is busy.

This has the consequence that a single prepared statement is prepared into multiple backend connections.

At the same time, when a client closes a PS the proxy only decrease a reference count and still keep metadata about it: if another client tries to re-prepare the same PS, proxysql will already know everything about it and not re-prepare it.

The implementation is quite complex, and I should document it.

To avoid that a lot of prepared statements are created on backend connections, when a connection has reached mysql-max_stmts_per_connection PS it is automatically dropped.

amit bali

unread,

Jan 3, 2017, 2:53:08 AM1/3/17

to proxysql, amitku...@gmail.com, rajat....@webyog.com

Hello Renne,

I noted something curious:

mysql> show global status like 'com_stmt%';

+-------------------------+----------+

| Variable_name | Value |

+-------------------------+----------+

| Com_stmt_close | 0 |

| Com_stmt_execute | 50727135 |

| Com_stmt_fetch | 0 |

| Com_stmt_prepare | 5725355 |

| Com_stmt_reprepare | 0 |

| Com_stmt_reset | 0 |

| Com_stmt_send_long_data | 0 |

+-------------------------+----------+

7 rows in set (0.01 sec)

Now even if i am closing the prepared statements from my client, i am still not seeing the increment in the com_stmt_close variable(i close both the prepare stament and result statement in code, although i was leaking this at the time when i posted my last request),

I donot know why this value is not increasing even though i am closing the prepare statements explicitly now.

Thanks and Regards,

Amit Kumar Bali.

...

René Cannaò

unread,

Jan 3, 2017, 3:14:27 AM1/3/17

to amit bali, proxysql, Rajat Shukla

Hi Amit,

That is expected.

Many applications have a very odd use of PS that is terrible for performance, that is running 1 PREPARE + 1 EXECUTE + 1 CLOSE.
Also your application does the same according to this previous output:

| Com_stmt_prepare | 105155 |

| Com_stmt_execute | 105155 |

| Com_stmt_close | 105155 |

Why it is bad for performance?

To perform a single query, the client sent 3 requests: extremely inefficient, because it adds a lot of latency due to extra unnecessary network round trip.

ProxySQL tries (quite successfully) to reduce the overhead caching PS metadata.

When the client closes the PS, ProxySQL decreases a local counter, but doesn't really close the PS on the backend.

If the client (or another client) tries to re-prepare the same PS, ProxySQL will return a stmt_id without re-preparing it: it has all the metadata already.

This metadata caching mechanism is aimed to improve performance.

In your specific case, while your application has an EXECUTE/PREPARE ratio of 1:1 , ProxySQL has an EXECUTE/PREPARE ratio of 8.86 (Com_stmt_execute/Com_stmt_prepare).

While your application has a CLOSE/EXECUTE ratio of 1:1 , ProxySQL has a CLOSE/EXECUTE ratio of 0:1 . PS are never closed.

This is good as it improves performance, but it also had the potential drawback of creating too many PS on backends.

This is where mysql-max_stmts_per_connection comes into play: when a connection has reached mysql-max_stmts_per_connection PS it is automatically dropped.

As written in a previous reply, the default value for mysql-max_stmts_per_connection is very conservative (20) : if you increase it just a bit (like 40, do not set anything too high) you will notice:
* an even more efficient Com_stmt_execute/Com_stmt_prepare ratio

* less connections being dropped/recreated

--

amit bali

unread,

Jan 3, 2017, 3:54:22 AM1/3/17

to proxysql, amitku...@gmail.com, rajat....@webyog.com

Hello Renne,

We have increased it to a bigger value(500, yes i know, i will reduce it now, in my defence i did this before your previous reply) but still we are seeing that the connections are being created and destroyed very frequently.

However what is of concern to me is that the number of prepared statement close is 0. Does this mean that all those prepared statements are still kept open? If that is the case then should not the close of connection automatically close the prepares aswell? Or the mysql does not keep the track of prepared closed automatically as part of connection close?

Also is there any way for me to ask proxysql to close these prepares explicitly once the proxysql wants to close the connection?

And Does this mean that the ration of (total prepare statements) / (total connection made by proxy sql to backend) ~ (mysql-max_stmts_per_connection) ?

Thanks and Regards,

Amit Kumar Bali.

...

René Cannaò

unread,

Jan 3, 2017, 4:11:35 AM1/3/17

to amit bali, proxysql, Rajat Shukla

Amit,

Can you clarify on "very frequently" ? Was there any change on the frequency?

A new output from "SHOW MYSQL STATUS" and "SELECT * FROM stats_mysql_connection_pool" is appreciated.

About your concern: when a client closes a connection, the server automatically releases all the prepared statements.

So when ProxySQL closes a backend connections, the PS is automatically closed although Com_stmt_close doesn't increase because a CLOSE command wasn't sent.

Currently there is no way to force ProxySQL to close a PS if a client asks to close it. And I don't think it ever makes sense considering how it is related to multiplexing.

In fact, when a client run PREPARE+EXECUTE , there is no guarantee they will be executed on the same backend connection.

Even more interestingly, if the client run 1 PREPARE + many EXECUTE , each EXECUTE may end on different backend connections: closing them all won't be an option, especially because these connections may be in use by another client.

Does this mean that the ration of (total prepare statements) / (total connection made by proxy sql to backend) ~ (mysql-max_stmts_per_connection) ?

No, the math is not that easy.

It depends from the number of unique prepared statements. If the client prepares always the same PS, and the number of unique PS is less than mysql-max_stmts_per_connection, connections to backend should not be dropped.

--

Message has been deleted

amit bali

unread,

Jan 3, 2017, 5:20:56 AM1/3/17

to proxysql, amitku...@gmail.com, rajat....@webyog.com

Hello,

Please ignore the previous tables of metrics(gave you from test environment, my bad):

mysql> select * from stats_mysql_connection_pool;

| 1 | XXX | 3306 | ONLINE | 0 | 3 | 51289 | 0 | 32870161 | 22169829494 | 271012343 | 641 |

1 row in set (0.00 sec)

mysql> show mysql status;

+------------------------------+--------------+

| Variable_name | Value |

+------------------------------+--------------+

| Active_Transactions | 0 |

| Backend_query_time_nsec | 561513202418 |

| Client_Connections_aborted | 1 |

| Client_Connections_connected | 1 |

| Client_Connections_created | 9375517 |

| Client_Connections_non_idle | 0 |

| Com_autocommit | 9342843 |

| Com_autocommit_filtered | 9342843 |

| Com_commit | 0 |

| Com_commit_filtered | 0 |

| Com_rollback | 0 |

| Com_rollback_filtered | 0 |

| Com_stmt_close | 13185093 |

| Com_stmt_execute | 13185092 |

| Com_stmt_prepare | 13185093 |

| ConnPool_get_conn_failure | 42 |

| ConnPool_get_conn_immediate | 9858 |

| ConnPool_get_conn_success | 31899167 |

| ConnPool_memory_bytes | 213720 |

| MySQL_Monitor_Workers | 8 |

| MySQL_Thread_Workers | 4 |

| Queries_backends_bytes_recv | 271020057 |

| Queries_backends_bytes_sent | 22170484926 |

| 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 | 24093692800 |

| Questions | 67612167 |

| SQLite3_memory_bytes | 875648 |

| Server_Connections_aborted | 0 |

| Server_Connections_connected | 3 |

| Server_Connections_created | 51289 |

| Servers_table_version | 2 |

| Slow_queries | 9 |

| Stmt_Active_Total | 0 |

| Stmt_Active_Unique | 0 |

| Stmt_Max_Stmt_id | 36 |

| mysql_backend_buffers_bytes | 0 |

| mysql_frontend_buffers_bytes | 65536 |

| mysql_session_internal_bytes | 21120 |

+------------------------------+--------------+

45 rows in set (0.01 sec)

...

René Cannaò

unread,

Jan 3, 2017, 5:48:15 AM1/3/17

to amit bali, proxysql, Rajat Shukla

Amit,

In a previous output (5 days ago):
Client_Connections_created/Com_stmt_execute = 1.34
Client_Connections_created/Server_Connections_created = 62.34
Com_stmt_execute/Server_Connections_created = 83.59

Now:
Client_Connections_created/Com_stmt_execute = 1.41
Client_Connections_created/Server_Connections_created = 182.80
Com_stmt_execute/Server_Connections_created = 257.07

The ratio Client_Connections_created/Com_stmt_execute is almost unchanged, so workload is similar.

The other two ratios look a lot better, so I assume raising mysql-max_stmts_per_connection helped, and a lot less new backend connections are created now.

Another option I can think of is that transactions are running when the client disconnects.

On 3 January 2017 at 01:59, amit bali <amitku...@gmail.com> wrote:

Hello,

Please ignore the previous tables of metrics(gave you from test environment, my bad):

mysql> select * from stats_mysql_connection_pool;
+-----------+--------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+----------+-----------------+-----------------+------------+

| hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_ms |

+-----------+--------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+----------+-----------------+-----------------+------------+
| 1 | sandman-1-cluster.cluster-cvymgcdxa1mt.us-east-1.rds.amazonaws.com | 3306 | ONLINE | 0 | 3 | 51289 | 0 | 32870161 | 22169829494 | 271012343 | 641 |
+-----------+--------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+----------+-----------------+-----------------+------------+

1 row in set (0.00 sec)

mysql> show mysql status;
+------------------------------+--------------+
| Variable_name | Value |
+------------------------------+--------------+
| Active_Transactions | 0 |
| Backend_query_time_nsec | 561513202418 |
| Client_Connections_aborted | 1 |
| Client_Connections_connected | 1 |
| Client_Connections_created | 9375517 |
| Client_Connections_non_idle | 0 |
| Com_autocommit | 9342843 |
| Com_autocommit_filtered | 9342843 |

| Com_commit | 0 |
| Com_commit_filtered | 0 |
| Com_rollback | 0 |
| Com_rollback_filtered | 0 |

| Com_stmt_close | 13185093 |
| Com_stmt_execute | 13185092 |
| Com_stmt_prepare | 13185093 |
| ConnPool_get_conn_failure | 42 |
| ConnPool_get_conn_immediate | 9858 |
| ConnPool_get_conn_success | 31899167 |
| ConnPool_memory_bytes | 213720 |
| MySQL_Monitor_Workers | 8 |
| MySQL_Thread_Workers | 4 |
| Queries_backends_bytes_recv | 271020057 |
| Queries_backends_bytes_sent | 22170484926 |

| 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 | 24093692800 |
| Questions | 67612167 |
| SQLite3_memory_bytes | 875648 |
| Server_Connections_aborted | 0 |
| Server_Connections_connected | 3 |
| Server_Connections_created | 51289 |
| Servers_table_version | 2 |
| Slow_queries | 9 |

| Stmt_Active_Total | 0 |
| Stmt_Active_Unique | 0 |
| Stmt_Max_Stmt_id | 36 |

| mysql_backend_buffers_bytes | 0 |
| mysql_frontend_buffers_bytes | 65536 |
| mysql_session_internal_bytes | 21120 |
+------------------------------+--------------+
45 rows in set (0.01 sec)

Thanks and regards,
Amit Kumar Bali.

On Tuesday, January 3, 2017 at 3:25:49 PM UTC+5:30, amit bali wrote:

Hello Renne,
Here are the values:

mysql> select * from stats_mysql_connection_pool;

+-----------+----------------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+

| hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_ms |

+-----------+----------------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| 5 | staging-sandman-1-cluster.cluster-crvjhoauhxjw.us-east-1.rds.amazonaws.com | 3306 | ONLINE | 0 | 2 | 943 | 12 | 573113 | 285951748 | 51980097 | 1025 |
+-----------+----------------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+

mysql> SHOW MYSQL STATUS
-> ;
+------------------------------+-------------+
| Variable_name | Value |
+------------------------------+-------------+
| Active_Transactions | 0 |
| Backend_query_time_nsec | 11750407607 |
| Client_Connections_aborted | 0 |
| Client_Connections_connected | 0 |
| Client_Connections_created | 229162 |
| Client_Connections_non_idle | 0 |
| Com_autocommit | 164089 |
| Com_autocommit_filtered | 164089 |

| Com_commit | 0 |
| Com_commit_filtered | 0 |
| Com_rollback | 0 |
| Com_rollback_filtered | 0 |

| Com_stmt_close | 237339 |
| Com_stmt_execute | 237339 |
| Com_stmt_prepare | 237340 |
| ConnPool_get_conn_failure | 256 |
| ConnPool_get_conn_immediate | 145 |
| ConnPool_get_conn_success | 568080 |
| ConnPool_memory_bytes | 142528 |
| MySQL_Monitor_Workers | 8 |
| MySQL_Thread_Workers | 4 |
| Queries_backends_bytes_recv | 51980880 |
| Queries_backends_bytes_sent | 285969229 |
| 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 | 471753950 |
| Questions | 1210760 |
| SQLite3_memory_bytes | 830528 |
| Server_Connections_aborted | 12 |
| Server_Connections_connected | 2 |
| Server_Connections_created | 949 |
| Servers_table_version | 2 |
| Slow_queries | 4 |
| Stmt_Active_Total | 0 |
| Stmt_Active_Unique | 0 |
| Stmt_Max_Stmt_id | 9 |
| mysql_backend_buffers_bytes | 0 |
| mysql_frontend_buffers_bytes | 0 |
| mysql_session_internal_bytes | 21056 |
+------------------------------+-------------+
45 rows in set (0.00 sec)

Thanks and Regards,
Amit Kumar Bali.

...

René Cannaò

unread,

Jan 3, 2017, 6:00:32 AM1/3/17

to amit bali, proxysql, Rajat Shukla

Created https://github.com/sysown/proxysql/issues/862 : this would have helped troubleshooting this issue

amit bali

unread,

Jan 5, 2017, 1:47:50 AM1/5/17

to proxysql, amitku...@gmail.com, rajat....@webyog.com

Hello Renne,

We were able to fix the fluctuations. The line 'It depends from the number of unique prepared statements' helped us to pinpoint the issue :). We were indeed having prepare with queries hardcoded(sprintfed) with some id. This was probably causing a fault every time in the prepare cache that you talked about and hence each query was counted as a different prepare resulting in maxing out of prepares per connection and hence was causing closure of connections.

It would be great if we can have a metric around the number of prepares currently held in the cache and sum of all prepares in the cache till the last restart of proxysql. If there is already such a metric do let us know.

Thanks for pointing this out.

Regards,

Amit Kumar Bali

...

Reply all

Reply to author

Forward

0 new messages