Higher latency than expected

[Dan Waddell]

Hello,

We have deployed ProxySQL between our applications and our MySQL 5.7 databases (which we intend to update to MySQL 8 as soon as possible). The addition of ProxySQL has introduced a significant amount of additional latency.

We have created a script/test environment to repeatably measure this. This environment eliminates network latency as a potential cause - the script is run from the server running the ProxySQL instance (connecting to ProxySQL on 127.0.0.1), so there is no additional network hop to get to the MySQL 5.7 server.

The script makes 1000 ping connections directly to the MySQL 5.7 server and then runs a basis SELECT query 1000 times directly to the MySQL 5.7 server. The script then runs the same tests against ProxySQL, which is configured to send traffic to the MySQL 5.7 mentioned above. The results are as follows:

Average MySQL 5.7 ping latency: 0.44 ms

Average ProxySQL ping latency: 0.8 ms

Average MySQL 5.7 query latency: 0.56 ms

Average ProxySQL query latency: 0.84 ms

As you can see, there's an 82% increase in latency when pinging the server and a 50% increase for query latency, which is significantly higher than we expected, especially after eliminating the network latency. We have experimented with adjusting the thread count to values between 1 and 16, which doesn't affect the result. Having looked at htop on the server running ProxySQL, none of the CPU cores are close to being saturated. We've tested this on versions 2.7.3 and 3.0.1

Based on the our config below, is there anything glaringly obvious that's been misconfigured that would be a cause for this additional latency?

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

| Variable_name | Value |

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

| admin-admin_credentials | admin:[redacted] |

| admin-checksum_admin_variables | true |

| admin-checksum_ldap_variables | true |

| admin-checksum_mysql_query_rules | true |

| admin-checksum_mysql_servers | true |

| admin-checksum_mysql_users | true |

| admin-checksum_mysql_variables | true |

| admin-cluster_admin_variables_diffs_before_sync | 3 |

| admin-cluster_admin_variables_save_to_disk | true |

| admin-cluster_check_interval_ms | 1000 |

| admin-cluster_check_status_frequency | 10 |

| admin-cluster_ldap_variables_diffs_before_sync | 3 |

| admin-cluster_ldap_variables_save_to_disk | true |

| admin-cluster_mysql_query_rules_diffs_before_sync | 3 |

| admin-cluster_mysql_query_rules_save_to_disk | true |

| admin-cluster_mysql_servers_diffs_before_sync | 3 |

| admin-cluster_mysql_servers_save_to_disk | true |

| admin-cluster_mysql_servers_sync_algorithm | 1 |

| admin-cluster_mysql_users_diffs_before_sync | 3 |

| admin-cluster_mysql_users_save_to_disk | true |

| admin-cluster_mysql_variables_diffs_before_sync | 3 |

| admin-cluster_mysql_variables_save_to_disk | true |

| admin-cluster_password | |

| admin-cluster_proxysql_servers_diffs_before_sync | 3 |

| admin-cluster_proxysql_servers_save_to_disk | true |

| admin-cluster_username | |

| admin-coredump_generation_interval_ms | 30000 |

| admin-coredump_generation_threshold | 10 |

| admin-mysql_ifaces | 0.0.0.0:6032 |

| admin-pgsql_ifaces | 0.0.0.0:6132 |

| admin-prometheus_memory_metrics_interval | 61 |

| admin-read_only | false |

| admin-refresh_interval | 2000 |

| admin-restapi_enabled | true |

| admin-restapi_port | 6070 |

| admin-ssl_keylog_file | |

| admin-stats_credentials | stats:stats |

| admin-stats_mysql_connection_pool | 60 |

| admin-stats_mysql_connections | 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-vacuum_stats | true |

| admin-version | 3.0.1-420-g2c26a42 |

| admin-web_enabled | false |

| admin-web_port | 6080 |

| admin-web_verbosity | 0 |

| mysql-add_ldap_user_comment | |

| mysql-auditlog_filename | |

| mysql-auditlog_filesize | 104857600 |

| mysql-aurora_max_lag_ms_only_read_from_replicas | 2 |

| mysql-auto_increment_delay_multiplex | 5 |

| mysql-auto_increment_delay_multiplex_timeout_ms | 10000 |

| mysql-autocommit_false_is_transaction | false |

| mysql-autocommit_false_not_reusable | false |

| mysql-automatic_detect_sqli | false |

| mysql-binlog_reader_connect_retry_msec | 3000 |

| mysql-client_host_cache_size | 0 |

| mysql-client_host_error_counts | 0 |

| mysql-client_session_track_gtid | true |

| mysql-commands_stats | true |

| mysql-connect_retries_delay | 1 |

| mysql-connect_retries_on_failure | 10 |

| mysql-connect_timeout_client | 10000 |

| mysql-connect_timeout_server | 1000 |

| mysql-connect_timeout_server_max | 10000 |

| mysql-connection_delay_multiplex_ms | 0 |

| mysql-connection_max_age_ms | 600000 |

| mysql-connection_warming | true |

| mysql-connpoll_reset_queue_length | 50 |

| mysql-data_packets_history_size | 0 |

| mysql-default_authentication_plugin | mysql_native_password |

| mysql-default_charset | latin1 |

| mysql-default_collation_connection | latin1_general_ci |

| mysql-default_max_latency_ms | 1000 |

| mysql-default_query_delay | 0 |

| mysql-default_query_timeout | 36000000 |

| mysql-default_reconnect | true |

| mysql-default_schema | information_schema |

| mysql-default_session_track_gtids | OFF |

| mysql-enable_client_deprecate_eof | false |

| mysql-enable_load_data_local_infile | false |

| mysql-enable_server_deprecate_eof | false |

| mysql-enforce_autocommit_on_reads | false |

| mysql-evaluate_replication_lag_on_servers_load | 1 |

| mysql-eventslog_buffer_history_size | 0 |

| mysql-eventslog_buffer_max_query_length | 32768 |

| mysql-eventslog_default_log | 0 |

| mysql-eventslog_filename | |

| mysql-eventslog_filesize | 104857600 |

| mysql-eventslog_format | 1 |

| mysql-eventslog_stmt_parameters | 0 |

| mysql-eventslog_table_memory_size | 10000 |

| mysql-firewall_whitelist_enabled | false |

| mysql-firewall_whitelist_errormsg | Firewall blocked this query |

| mysql-free_connections_pct | 1 |

| mysql-handle_unknown_charset | 1 |

| mysql-handle_warnings | 1 |

| mysql-have_compress | true |

| mysql-have_ssl | false |

| mysql-hostgroup_manager_verbose | 1 |

| mysql-init_connect | |

| mysql-interfaces | 0.0.0.0:6111;0.0.0.0:6120;0.0.0.0:6121; |

| mysql-keep_multiplexing_variables | tx_isolation,transaction_isolation,version |

| mysql-kill_backend_connection_when_disconnect | true |

| mysql-ldap_user_variable | |

| mysql-log_mysql_warnings_enabled | false |

| mysql-log_unhealthy_connections | true |

| mysql-long_query_time | 10000 |

| mysql-max_allowed_packet | 67108864 |

| mysql-max_connections | 6000 |

| mysql-max_stmts_cache | 10000 |

| mysql-max_stmts_per_connection | 20 |

| mysql-max_transaction_idle_time | 14400000 |

| mysql-max_transaction_time | 14400000 |

| mysql-min_num_servers_lantency_awareness | 1000 |

| mysql-mirror_max_concurrency | 16 |

| mysql-mirror_max_queue_length | 32000 |

| mysql-monitor_aws_rds_topology_discovery_interval | 0 |

| mysql-monitor_connect_interval | 120000 |

| mysql-monitor_connect_timeout | 600 |

| mysql-monitor_enabled | true |

| mysql-monitor_galera_healthcheck_interval | 5000 |

| mysql-monitor_galera_healthcheck_max_timeout_count | 3 |

| mysql-monitor_galera_healthcheck_timeout | 800 |

| mysql-monitor_groupreplication_healthcheck_interval | 5000 |

| mysql-monitor_groupreplication_healthcheck_max_timeout_count | 3 |

| mysql-monitor_groupreplication_healthcheck_timeout | 800 |

| mysql-monitor_groupreplication_max_transactions_behind_count | 3 |

| mysql-monitor_groupreplication_max_transactions_behind_for_read_only | 1 |

| mysql-monitor_history | 7200000 |

| mysql-monitor_local_dns_cache_refresh_interval | 60000 |

| mysql-monitor_local_dns_cache_ttl | 300000 |

| mysql-monitor_local_dns_resolver_queue_maxsize | 128 |

| mysql-monitor_password | [redacted] |

| mysql-monitor_ping_interval | 8000 |

| mysql-monitor_ping_max_failures | 3 |

| mysql-monitor_ping_timeout | 1000 |

| mysql-monitor_query_interval | 60000 |

| mysql-monitor_query_timeout | 100 |

| mysql-monitor_read_only_interval | 1000 |

| mysql-monitor_read_only_max_timeout_count | 3 |

| mysql-monitor_read_only_timeout | 800 |

| mysql-monitor_replication_lag_count | 10 |

| mysql-monitor_replication_lag_group_by_host | false |

| mysql-monitor_replication_lag_interval | 30000 |

| mysql-monitor_replication_lag_timeout | 1000 |

| mysql-monitor_replication_lag_use_percona_heartbeat | |

| mysql-monitor_slave_lag_when_null | 60 |

| mysql-monitor_threads_max | 128 |

| mysql-monitor_threads_min | 8 |

| mysql-monitor_threads_queue_maxsize | 128 |

| mysql-monitor_username | pmm |

| mysql-monitor_wait_timeout | true |

| mysql-monitor_writer_is_also_reader | true |

| mysql-multiplexing | true |

| mysql-parse_failure_logs_digest | false |

| mysql-ping_interval_server_msec | 10000 |

| mysql-ping_timeout_server | 200 |

| mysql-poll_timeout | 2000 |

| mysql-poll_timeout_on_failure | 100 |

| mysql-protocol_compression_level | 3 |

| mysql-proxy_protocol_networks | |

| mysql-query_cache_handle_warnings | 0 |

| mysql-query_cache_size_MB | 192 |

| mysql-query_cache_soft_ttl_pct | 0 |

| mysql-query_cache_stores_empty_result | true |

| mysql-query_digests | true |

| mysql-query_digests_grouping_limit | 3 |

| mysql-query_digests_groups_grouping_limit | 10 |

| mysql-query_digests_keep_comment | false |

| mysql-query_digests_lowercase | false |

| mysql-query_digests_max_digest_length | 2048 |

| mysql-query_digests_max_query_length | 65000 |

| mysql-query_digests_no_digits | false |

| mysql-query_digests_normalize_digest_text | false |

| mysql-query_digests_replace_null | false |

| mysql-query_digests_track_hostname | false |

| mysql-query_processor_iterations | 0 |

| mysql-query_processor_regex | 1 |

| mysql-query_retries_on_failure | 1 |

| mysql-query_rules_fast_routing_algorithm | 1 |

| mysql-reset_connection_algorithm | 2 |

| mysql-server_capabilities | 569867 |

| mysql-server_version | 8.0.11 |

| mysql-servers_stats | true |

| mysql-session_idle_ms | 1 |

| mysql-session_idle_show_processlist | true |

| mysql-sessions_sort | true |

| mysql-set_parser_algorithm | 2 |

| mysql-set_query_lock_on_hostgroup | 1 |

| mysql-show_processlist_extended | 0 |

| mysql-shun_on_failures | 5 |

| mysql-shun_recovery_time_sec | 10 |

| mysql-ssl_p2s_ca | |

| mysql-ssl_p2s_capath | |

| mysql-ssl_p2s_cert | |

| mysql-ssl_p2s_cipher | |

| mysql-ssl_p2s_crl | |

| mysql-ssl_p2s_crlpath | |

| mysql-ssl_p2s_key | |

| mysql-stacksize | 1048576 |

| mysql-stats_time_backend_query | false |

| mysql-stats_time_query_processor | false |

| mysql-tcp_keepalive_time | 120 |

| mysql-threads | 4 |

| mysql-threshold_query_length | 524288 |

| mysql-threshold_resultset_size | 4194304 |

| mysql-throttle_connections_per_sec_to_hostgroup | 1000000 |

| mysql-throttle_max_bytes_per_second_to_client | 0 |

| mysql-throttle_ratio_server_to_client | 0 |

| mysql-unshun_algorithm | 0 |

| mysql-use_tcp_keepalive | true |

| mysql-verbose_query_error | false |

| mysql-wait_timeout | 28800000 |

| pgsql-add_ldap_user_comment | |

| pgsql-auditlog_filename | |

| pgsql-auditlog_filesize | 104857600 |

| pgsql-aurora_max_lag_ms_only_read_from_replicas | 2 |

| pgsql-authentication_method | 3 |

| pgsql-auto_increment_delay_multiplex | 5 |

| pgsql-auto_increment_delay_multiplex_timeout_ms | 10000 |

| pgsql-autocommit_false_is_transaction | false |

| pgsql-autocommit_false_not_reusable | false |

| pgsql-automatic_detect_sqli | false |

| pgsql-binlog_reader_connect_retry_msec | 3000 |

| pgsql-client_host_cache_size | 0 |

| pgsql-client_host_error_counts | 0 |

| pgsql-client_session_track_gtid | true |

| pgsql-commands_stats | true |

| pgsql-connect_retries_delay | 1 |

| pgsql-connect_retries_on_failure | 10 |

| pgsql-connect_timeout_client | 10000 |

| pgsql-connect_timeout_server | 1000 |

| pgsql-connect_timeout_server_max | 10000 |

| pgsql-connection_delay_multiplex_ms | 0 |

| pgsql-connection_max_age_ms | 0 |

| pgsql-connection_warming | false |

| pgsql-data_packets_history_size | 0 |

| pgsql-default_allow_in_place_tablespaces | off |

| pgsql-default_bytea_output | hex |

| pgsql-default_client_encoding | UTF8 |

| pgsql-default_client_min_messages | notice |

| pgsql-default_datestyle | ISO, MDY |

| pgsql-default_enable_bitmapscan | on |

| pgsql-default_enable_hashjoin | on |

| pgsql-default_enable_indexscan | on |

| pgsql-default_enable_nestloop | on |

| pgsql-default_enable_seqscan | on |

| pgsql-default_enable_sort | on |

| pgsql-default_escape_string_warning | on |

| pgsql-default_extra_float_digits | 1 |

| pgsql-default_intervalstyle | postgres |

| pgsql-default_maintenance_work_mem | 64MB |

| pgsql-default_max_latency_ms | 1000 |

| pgsql-default_query_delay | 0 |

| pgsql-default_query_timeout | 86400000 |

| pgsql-default_reconnect | true |

| pgsql-default_schema | information_schema |

| pgsql-default_session_track_gtids | OFF |

| pgsql-default_standard_conforming_strings | on |

| pgsql-default_synchronous_commit | on |

| pgsql-default_timezone | GMT |

| pgsql-enable_client_deprecate_eof | true |

| pgsql-enable_load_data_local_infile | false |

| pgsql-enable_server_deprecate_eof | true |

| pgsql-enforce_autocommit_on_reads | false |

| pgsql-eventslog_default_log | 0 |

| pgsql-eventslog_filename | |

| pgsql-eventslog_filesize | 104857600 |

| pgsql-eventslog_format | 1 |

| pgsql-firewall_whitelist_enabled | false |

| pgsql-firewall_whitelist_errormsg | Firewall blocked this query |

| pgsql-free_connections_pct | 10 |

| pgsql-handle_unknown_charset | 1 |

| pgsql-handle_warnings | 1 |

| pgsql-have_compress | true |

| pgsql-have_ssl | true |

| pgsql-hostgroup_manager_verbose | 1 |

| pgsql-init_connect | |

| pgsql-interfaces | |

| pgsql-keep_multiplexing_variables | tx_isolation,transaction_isolation,version |

| pgsql-kill_backend_connection_when_disconnect | true |

| pgsql-ldap_user_variable | |

| pgsql-log_mysql_warnings_enabled | false |

| pgsql-log_unhealthy_connections | true |

| pgsql-long_query_time | 1000 |

| pgsql-max_allowed_packet | 67108864 |

| pgsql-max_connections | 10000 |

| pgsql-max_stmts_cache | 10000 |

| pgsql-max_stmts_per_connection | 20 |

| pgsql-max_transaction_idle_time | 14400000 |

| pgsql-max_transaction_time | 14400000 |

| pgsql-min_num_servers_lantency_awareness | 1000 |

| pgsql-mirror_max_concurrency | 16 |

| pgsql-mirror_max_queue_length | 32000 |

| pgsql-monitor_connect_interval | 120000 |

| pgsql-monitor_connect_interval_window | 50 |

| pgsql-monitor_connect_timeout | 600 |

| pgsql-monitor_dbname | postgres |

| pgsql-monitor_enabled | true |

| pgsql-monitor_history | 7200000 |

| pgsql-monitor_password | monitor |

| pgsql-monitor_ping_interval | 8000 |

| pgsql-monitor_ping_interval_window | 10 |

| pgsql-monitor_ping_max_failures | 3 |

| pgsql-monitor_ping_timeout | 1000 |

| pgsql-monitor_read_only_interval | 1000 |

| pgsql-monitor_read_only_interval_window | 10 |

| pgsql-monitor_read_only_max_timeout_count | 3 |

| pgsql-monitor_read_only_timeout | 800 |

| pgsql-monitor_threads | 2 |

| pgsql-monitor_username | monitor |

| pgsql-monitor_writer_is_also_reader | true |

| pgsql-multiplexing | true |

| pgsql-parse_failure_logs_digest | false |

| pgsql-ping_interval_server_msec | 10000 |

| pgsql-ping_timeout_server | 200 |

| pgsql-poll_timeout | 2000 |

| pgsql-poll_timeout_on_failure | 100 |

| pgsql-query_cache_handle_warnings | 0 |

| pgsql-query_cache_size_MB | 256 |

| pgsql-query_cache_soft_ttl_pct | 0 |

| pgsql-query_cache_stores_empty_result | true |

| pgsql-query_digests | true |

| pgsql-query_digests_grouping_limit | 3 |

| pgsql-query_digests_groups_grouping_limit | 10 |

| pgsql-query_digests_keep_comment | false |

| pgsql-query_digests_lowercase | false |

| pgsql-query_digests_max_digest_length | 2048 |

| pgsql-query_digests_max_query_length | 65000 |

| pgsql-query_digests_no_digits | false |

| pgsql-query_digests_normalize_digest_text | false |

| pgsql-query_digests_replace_null | false |

| pgsql-query_digests_track_hostname | false |

| pgsql-query_processor_iterations | 0 |

| pgsql-query_processor_regex | 1 |

| pgsql-query_retries_on_failure | 1 |

| pgsql-query_rules_fast_routing_algorithm | 1 |

| pgsql-server_capabilities | 569867 |

| pgsql-server_encoding | UTF8 |

| pgsql-server_version | 16.1 |

| pgsql-servers_stats | true |

| pgsql-session_idle_ms | 1 |

| pgsql-session_idle_show_processlist | true |

| pgsql-sessions_sort | true |

| pgsql-set_parser_algorithm | 2 |

| pgsql-set_query_lock_on_hostgroup | 1 |

| pgsql-show_processlist_extended | 0 |

| pgsql-shun_on_failures | 5 |

| pgsql-shun_recovery_time_sec | 10 |

| pgsql-ssl_p2s_ca | |

| pgsql-ssl_p2s_capath | |

| pgsql-ssl_p2s_cert | |

| pgsql-ssl_p2s_cipher | |

| pgsql-ssl_p2s_crl | |

| pgsql-ssl_p2s_crlpath | |

| pgsql-ssl_p2s_key | |

| pgsql-stacksize | 1048576 |

| pgsql-stats_time_backend_query | false |

| pgsql-stats_time_query_processor | false |

| pgsql-tcp_keepalive_time | 120 |

| pgsql-threads | 4 |

| pgsql-threshold_query_length | 524288 |

| pgsql-threshold_resultset_size | 4194304 |

| pgsql-throttle_connections_per_sec_to_hostgroup | 1000000 |

| pgsql-throttle_max_bytes_per_second_to_client | 0 |

| pgsql-throttle_ratio_server_to_client | 0 |

| pgsql-unshun_algorithm | 0 |

| pgsql-use_tcp_keepalive | true |

| pgsql-verbose_query_error | false |

| pgsql-wait_timeout | 28800000 |

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

Thanks in advance for anyone who can shed any light on this!

[René Cannaò]

Hi Dan,

Did you try to check network latency between all the parties involved?

icmp ping can provide useful insights 

--
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+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/proxysql/166d56b6-aa82-4b7f-a32b-ef92a58522edn%40googlegroups.com.

[Dan Waddell]

Hi René,

Thanks for getting back to me. Because we ran the tests from the same server where ProxySQL is running (connecting to ProxySQL on 127.0.0.1), we’ve effectively bypassed the first network hop and ruled out network latency as a cause. There are 2 network hops in this case: test-script —> proxysql (127.0.0.1) —> backend-mysql-db

- The ping latency of the first hop (going to 127.0.0.1) is very low, as you’d expect and averages 0.063ms
- The ping latency of the second hop (going from proxysql to the backend-mysql-db) averages 0.424ms

Thanks,
Dan

[Dan Waddell]

Hi René,

Based on our above config, are there any variables that have been set incorrectly which you think might be adding this latency from within ProxySQL? Or are there any other variables (other than the thread count) which we can experiment which you think could affect latency?

Thanks,

Dan

[René Cannaò]

Hi Dan,

I missed the update on this.

Can you please elaborate on this?

> Average MySQL 5.7 ping latency: 0.44 ms

> Average ProxySQL ping latency: 0.8 ms

How do you measure the ping latency ?

Can you share the script?

Thanks

To view this discussion visit https://groups.google.com/d/msgid/proxysql/4a731fd4-3f52-4121-9b3d-58a0f0d2796an%40googlegroups.com.

[Dan Waddell]

Hi René,

Yes, I've attached the script here, with the sensitive information redacted.

Thanks,

Dan