Closed rohitkeshwani07 closed 4 years ago
MySQL [(none)]> select * from stats_mysql_global;
+---------------------------------------------+----------------+
| Variable_Name | Variable_Value |
+---------------------------------------------+----------------+
| ProxySQL_Uptime | 1164 |
| Active_Transactions | 5 |
| Client_Connections_aborted | 0 |
| Client_Connections_connected | 60 |
| Client_Connections_created | 16412 |
| Server_Connections_aborted | 0 |
| Server_Connections_connected | 102 |
| Server_Connections_created | 103 |
| Server_Connections_delayed | 0 |
MySQL [(none)]> select * from stats_mysql_connection_pool;
+-----------+-------------------------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
| hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+-------------------------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
| 0 | qa-perf-mysql-new.cfq3jvutzavw.ap-south-1.rds.amazonaws.com | 3306 | ONLINE | 20 | 82 | 103 | 0 | 102 | 816096 | 0 | 70432638 | 70603959 | 138 |
| 1 | qa-perf-mysql-new.cfq3jvutzavw.ap-south-1.rds.amazonaws.com | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 138 |
+-----------+-------------------------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
2 rows in set (0.077 sec)
this verifies that backend connections are not getting disconnected at all... neither by reset_connection_algorithm
nor by connection_max_age_ms
MySQL [(none)]> select * from global_variables;
+--------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+
| variable_name | variable_value |
+--------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+
| mysql-query_retries_on_failure | 1 |
| mysql-client_multi_statements | true |
| mysql-connect_retries_delay | 1 |
| mysql-connection_delay_multiplex_ms | 0 |
| mysql-eventslog_filename | |
| mysql-eventslog_filesize | 104857600 |
| mysql-eventslog_default_log | 0 |
| mysql-eventslog_format | 1 |
| mysql-auditlog_filename | |
| mysql-auditlog_filesize | 104857600 |
| mysql-default_charset | utf8 |
| mysql-handle_unknown_charset | 1 |
| mysql-session_idle_ms | 1000 |
| mysql-have_ssl | false |
| mysql-client_found_rows | true |
| mysql-monitor_enabled | true |
| mysql-monitor_connect_timeout | 600 |
| mysql-monitor_read_only_max_timeout_count | 3 |
| mysql-monitor_replication_lag_interval | 10000 |
| mysql-monitor_replication_lag_timeout | 1000 |
| mysql-monitor_groupreplication_healthcheck_interval | 5000 |
| mysql-monitor_groupreplication_healthcheck_timeout | 800 |
| mysql-monitor_groupreplication_healthcheck_max_timeout_count | 3 |
| mysql-monitor_groupreplication_max_transactions_behind_count | 3 |
| mysql-monitor_galera_healthcheck_interval | 5000 |
| mysql-monitor_galera_healthcheck_timeout | 800 |
| mysql-monitor_galera_healthcheck_max_timeout_count | 3 |
| mysql-monitor_replication_lag_use_percona_heartbeat | |
| mysql-monitor_query_interval | 60000 |
| mysql-monitor_query_timeout | 100 |
| mysql-monitor_slave_lag_when_null | 60 |
| mysql-monitor_threads_min | 8 |
| mysql-monitor_threads_max | 128 |
| mysql-monitor_threads_queue_maxsize | 128 |
| mysql-monitor_wait_timeout | true |
| mysql-monitor_writer_is_also_reader | true |
| mysql-max_allowed_packet | 67108864 |
| mysql-tcp_keepalive_time | 0 |
| mysql-use_tcp_keepalive | 0 |
| mysql-automatic_detect_sqli | 0 |
| mysql-firewall_whitelist_enabled | 0 |
| mysql-firewall_whitelist_errormsg | Firewall blocked this query |
| mysql-throttle_connections_per_sec_to_hostgroup | 1000000 |
| mysql-max_transaction_time | 14400000 |
| mysql-multiplexing | true |
| mysql-log_unhealthy_connections | true |
| mysql-forward_autocommit | false |
| mysql-autocommit_false_not_reusable | false |
| mysql-verbose_query_error | false |
| mysql-hostgroup_manager_verbose | 1 |
| mysql-binlog_reader_connect_retry_msec | 3000 |
| mysql-threshold_query_length | 524288 |
| mysql-threshold_resultset_size | 4194304 |
| mysql-query_digests_max_digest_length | 2048 |
| mysql-query_digests_max_query_length | 65000 |
| mysql-throttle_max_bytes_per_second_to_client | 0 |
| mysql-throttle_ratio_server_to_client | 0 |
| mysql-max_stmts_per_connection | 1024 |
| mysql-max_stmts_cache | 10000 |
| mysql-mirror_max_concurrency | 16 |
| mysql-mirror_max_queue_length | 32000 |
| mysql-query_processor_iterations | 0 |
| mysql-query_processor_regex | 1 |
| mysql-set_query_lock_on_hostgroup | 1 |
| mysql-reset_connection_algorithm | 1 |
| mysql-auto_increment_delay_multiplex | 5 |
| mysql-long_query_time | 1000 |
| mysql-query_cache_size_MB | 256 |
| mysql-default_schema | information_schema |
| mysql-poll_timeout_on_failure | 100 |
| mysql-keep_multiplexing_variables | tx_isolation,version |
| mysql-kill_backend_connection_when_disconnect | true |
| mysql-client_session_track_gtid | true |
| mysql-session_idle_show_processlist | true |
| mysql-show_processlist_extended | 0 |
| mysql-query_digests | true |
| mysql-query_digests_lowercase | false |
| mysql-query_digests_replace_null | false |
| mysql-query_digests_no_digits | false |
| mysql-query_digests_normalize_digest_text | false |
| mysql-query_digests_track_hostname | false |
| mysql-servers_stats | true |
| mysql-default_reconnect | true |
| mysql-ssl_p2s_ca | |
| mysql-ssl_p2s_cert | |
| mysql-ssl_p2s_key | |
| mysql-ssl_p2s_cipher | |
| mysql-init_connect | |
| mysql-ldap_user_variable | |
| mysql-add_ldap_user_comment | |
| mysql-default_time_zone | SYSTEM |
| mysql-default_isolation_level | READ COMMITTED |
| mysql-default_transaction_read | WRITE |
| mysql-default_tx_isolation | READ-COMMITTED |
| mysql-default_character_set_results | NULL |
| mysql-default_session_track_gtids | OFF |
| mysql-default_sql_auto_is_null | OFF |
| mysql-default_sql_select_limit | DEFAULT |
| mysql-default_sql_safe_updates | OFF |
| mysql-default_collation_connection | |
| mysql-default_net_write_timeout | 60 |
| mysql-default_max_join_size | 18446744073709551615 |
| mysql-connpoll_reset_queue_length | 50 |
| mysql-min_num_servers_lantency_awareness | 1000 |
| mysql-aurora_max_lag_ms_only_read_from_replicas | 2 |
| mysql-stats_time_backend_query | false |
| mysql-stats_time_query_processor | false |
| mysql-query_cache_stores_empty_result | true |
| admin-stats_credentials | stats:stats |
| admin-stats_mysql_connections | 60 |
| admin-stats_mysql_connection_pool | 60 |
| admin-stats_mysql_query_cache | 60 |
| admin-stats_mysql_query_digest_to_disk | 0 |
| admin-stats_system_cpu | 60 |
| admin-stats_system_memory | 60 |
| admin-telnet_admin_ifaces | (null) |
| admin-telnet_stats_ifaces | (null) |
| admin-read_only | false |
| admin-vacuum_stats | true |
| admin-version | 2.0.10-27-g5b319972 |
| admin-cluster_username | |
| admin-cluster_password | |
| admin-cluster_check_interval_ms | 1000 |
| admin-cluster_check_status_frequency | 10 |
| admin-cluster_mysql_query_rules_diffs_before_sync | 3 |
| admin-cluster_mysql_servers_diffs_before_sync | 3 |
| admin-cluster_mysql_users_diffs_before_sync | 3 |
| admin-cluster_proxysql_servers_diffs_before_sync | 3 |
| admin-cluster_mysql_query_rules_save_to_disk | true |
| admin-cluster_mysql_servers_save_to_disk | true |
| admin-cluster_mysql_users_save_to_disk | true |
| admin-cluster_proxysql_servers_save_to_disk | true |
| admin-checksum_mysql_query_rules | true |
| admin-checksum_mysql_servers | true |
| admin-checksum_mysql_users | true |
| admin-restapi_enabled | false |
| admin-restapi_port | 6070 |
| admin-web_enabled | false |
| admin-web_port | 6080 |
| admin-admin_credentials | admin:admin |
| admin-hash_passwords | 1 |
| admin-mysql_ifaces | 0.0.0.0:6032;/container-share/proxysql_admin.sock |
| admin-refresh_interval | 2000 |
| mysql-autocommit_false_is_transaction | true |
| mysql-commands_stats | true |
| mysql-connect_retries_on_failure | 10 |
| mysql-connect_timeout_server | 10000 |
| mysql-connect_timeout_server_max | 100000 |
| mysql-default_max_latency_ms | 10000 |
| mysql-default_query_delay | 0 |
| mysql-default_query_timeout | 36000000 |
| mysql-enforce_autocommit_on_reads | true |
| mysql-free_connections_pct | 60 |
| mysql-have_compress | true |
| mysql-interfaces | 0.0.0.0:3306;/container-share/proxysql.sock |
| mysql-max_connections | 200 |
| mysql-monitor_connect_interval | 200000 |
| mysql-monitor_history | 60000 |
| mysql-monitor_password | QHftzsDJm57722ue |
| mysql-monitor_ping_interval | 200000 |
| mysql-monitor_read_only_interval | 1500 |
| mysql-monitor_read_only_timeout | 500 |
| mysql-monitor_ping_max_failures | 30 |
| mysql-monitor_ping_timeout | 5000 |
| mysql-monitor_username | perf_api |
| mysql-ping_interval_server_msec | 60000 |
| mysql-ping_timeout_server | 1000 |
| mysql-poll_timeout | 2000 |
| mysql-server_version | 8.0.16 |
| mysql-sessions_sort | true |
| mysql-shun_on_failures | 10000000 |
| mysql-shun_recovery_time_sec | 0 |
| mysql-default_sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
| mysql-stacksize | 1048576 |
| mysql-threads | 32 |
| mysql-wait_timeout | 85000 |
| mysql-connection_max_age_ms | 50000 |
| mysql-server_capabilities | 569899 |
+--------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+
@val214 , @JavierJF , @pondix @renecannao... any help, where i should be looking?
do i need to run with auxilary threads? will that help?
Doesn't seem like it will help... but i am looking at c code after along time.
for me adding
reset_connection_algorithm=1
connpoll_reset_queue_length=0
solved it.
I have done a benchmark test with
mysql-free_connections_pct
as a high value of 100(not good for production, just testing) andmax_connections
as 200. Now as we increase traffic, mysql connections reach to around 90/100. But when we decrease traffic these idle connections didn't go down, which was expected as we were doing ping to server every 60 seconds when timeout at server was 90seconds. We wanted to decrease these idle connections after a point when traffic is not so much. So we decided to addmysql-connection_max_age_ms
as 100seconds. As this should kill the connections and we should again create connections as when needed. So that should bring idle connections down. This worked when we sent the high traffic and then stopped. But when we decrease the traffic rather to keep running some connections, this doesn't bring the connections down.Looking at the code i thought this may be because
mysql-max_stmts_per_connection
is 20 and proxySQL might be reseting the connections when putting them in connection pool as that will give it new created_at. So we decided to domysql-reset_connection_algorithm
as 1 which does not reset the connection rather just destroy it. But that didn't work either.