search

sysown / proxysql

High-performance MySQL proxy with a GPL license.
http://www.proxysql.com
GNU General Public License v3.0
5.95k stars 970 forks source link

Proxysql 2.0.4 memory usage #2166

Open MaxFedotov opened 5 years ago

MaxFedotov commented 5 years ago

Hi @renecannao. We are observing problems with Proxysql high memory usage:

PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
64191 proxysql  20   0 28.708g 0.026t   4348 S   0.0 21.0   3807:46 proxysql
proxysql --version
ProxySQL version 2.0.4-116-g7d371cf, codename Truls

These problems started on version 1.4 and we hoped, that with update to 2.0.4 they will be gone, but the issue still stays. This installation of proxysql is clustered, so high memory usage is observed on each of the nodes.

Admin> SELECT * FROM stats.stats_memory_metrics;
+------------------------------+----------------+
| Variable_Name                | Variable_Value |
+------------------------------+----------------+
| SQLite3_memory_bytes         | 4699976        |
| jemalloc_resident            | 21103181824    |
| jemalloc_active              | 20716478464    |
| jemalloc_allocated           | 20703386400    |
| jemalloc_mapped              | 21258756096    |
| jemalloc_metadata            | 381932840      |
| jemalloc_retained            | 9295699968     |
| Auth_memory                  | 1127           |
| query_digest_memory          | 1195050        |
| mysql_query_rules_memory     | 1195           |
| stack_memory_mysql_threads   | 33554432       |
| stack_memory_admin_threads   | 58720256       |
| stack_memory_cluster_threads | 50331648       |
+------------------------------+----------------+
13 rows in set (0.01 sec)
Admin> show mysql status;
+------------------------------------------+--------------+
| Variable_name                            | Value        |
+------------------------------------------+--------------+
| Access_Denied_Max_Connections            | 0            |
| Access_Denied_Max_User_Connections       | 0            |
| Access_Denied_Wrong_Password             | 0            |
| Active_Transactions                      | 0            |
| Backend_query_time_nsec                  | 0            |
| Client_Connections_aborted               | 0            |
| Client_Connections_connected             | 58           |
| Client_Connections_created               | 81976        |
| Client_Connections_non_idle              | 58           |
| Com_autocommit                           | 0            |
| Com_autocommit_filtered                  | 0            |
| Com_backend_change_user                  | 204659       |
| Com_backend_init_db                      | 7            |
| Com_backend_set_names                    | 0            |
| Com_backend_stmt_close                   | 0            |
| Com_backend_stmt_execute                 | 748866646    |
| Com_backend_stmt_prepare                 | 8419138      |
| Com_commit                               | 420028990    |
| Com_commit_filtered                      | 219344263    |
| Com_frontend_init_db                     | 0            |
| Com_frontend_set_names                   | 0            |
| Com_frontend_stmt_close                  | 461620458    |
| Com_frontend_stmt_execute                | 748866713    |
| Com_frontend_stmt_prepare                | 461620479    |
| Com_frontend_use_db                      | 0            |
| Com_rollback                             | 94           |
| Com_rollback_filtered                    | 94           |
| ConnPool_get_conn_failure                | 75046        |
| ConnPool_get_conn_immediate              | 4733         |
| ConnPool_get_conn_latency_awareness      | 0            |
| ConnPool_get_conn_success                | 42040957     |
| ConnPool_memory_bytes                    | 856496       |
| GTID_consistent_queries                  | 0            |
| GTID_session_collected                   | 0            |
| Mirror_concurrency                       | 0            |
| Mirror_queue_length                      | 0            |
| MyHGM_myconnpoll_destroy                 | 726          |
| MyHGM_myconnpoll_get                     | 42111270     |
| MyHGM_myconnpoll_get_ok                  | 42036224     |
| MyHGM_myconnpoll_push                    | 42676645     |
| MyHGM_myconnpoll_reset                   | 1485         |
| MySQL_Monitor_Workers                    | 2            |
| MySQL_Monitor_Workers_Aux                | 0            |
| MySQL_Monitor_Workers_Started            | 2            |
| MySQL_Monitor_connect_check_ERR          | 85           |
| MySQL_Monitor_connect_check_OK           | 78811        |
| MySQL_Monitor_ping_check_ERR             | 14           |
| MySQL_Monitor_ping_check_OK              | 157778       |
| MySQL_Monitor_read_only_check_ERR        | 0            |
| MySQL_Monitor_read_only_check_OK         | 0            |
| MySQL_Monitor_replication_lag_check_ERR  | 0            |
| MySQL_Monitor_replication_lag_check_OK   | 0            |
| MySQL_Thread_Workers                     | 4            |
| ProxySQL_Uptime                          | 4733732      |
| Queries_backends_bytes_recv              | 59047431747  |
| Queries_backends_bytes_sent              | 565587593700 |
| Queries_frontends_bytes_recv             | 915935315379 |
| Queries_frontends_bytes_sent             | 344420918964 |
| 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                | 0            |
| Questions                                | 2292821487   |
| SQLite3_memory_bytes                     | 4704080      |
| Server_Connections_aborted               | 628          |
| Server_Connections_connected             | 60           |
| Server_Connections_created               | 788          |
| Server_Connections_delayed               | 0            |
| Servers_table_version                    | 1            |
| Slow_queries                             | 6280         |
| Stmt_Cached                              | 8913         |
| Stmt_Client_Active_Total                 | 0            |
| Stmt_Client_Active_Unique                | 0            |
| Stmt_Max_Stmt_id                         | 10002        |
| Stmt_Server_Active_Total                 | 1067         |
| Stmt_Server_Active_Unique                | 242          |
| aws_aurora_replicas_skipped_during_query | 0            |
| backend_lagging_during_query             | 0            |
| backend_offline_during_query             | 0            |
| generated_error_packets                  | 43           |
| max_connect_timeouts                     | 33           |
| mysql_backend_buffers_bytes              | 3403392      |
| mysql_frontend_buffers_bytes             | 3801088      |
| mysql_killed_backend_connections         | 0            |
| mysql_killed_backend_queries             | 0            |
| mysql_session_internal_bytes             | 162560       |
| mysql_unexpected_frontend_com_quit       | 0            |
| mysql_unexpected_frontend_packets        | 0            |
| queries_with_max_lag_ms                  | 0            |
+------------------------------------------+--------------+
94 rows in set (0.00 sec)

Info from Admin> PROXYSQL MEMPROFILE START; memprofile.tar.gz

Will be very grateful, if you take a look on it. Thanks a lot!

renecannao commented 5 years ago

Hi. Which proxysql binary are you using? Please provide a link to the package. Thanks

MaxFedotov commented 5 years ago

As github doesn't support uploading .deb files, here is tar with proxysql-2.0.4-1-centos7.x86_64.rpm inside, which is used in our production. proxysql_package.tar.gz

MaxFedotov commented 5 years ago

Hi @renecannao, is there any additional info i can collect?