Open Wouter0100 opened 2 years ago
I’m experiencing the same issue but with official package
ProxySQL version 2.3.2-10-g8cd66cf
Proxysql without any activity grows to this in a week:
+------------------------------+-----------------+
| Variable_Name | Mem_usage_in_MB |
+------------------------------+-----------------+
| SQLite3_memory_bytes | 5 |
| jemalloc_resident | 1901 |
| jemalloc_active | 1863 |
| jemalloc_allocated | 1852 |
| jemalloc_mapped | 1940 |
| jemalloc_metadata | 38 |
| jemalloc_retained | 636 |
| Auth_memory | 0 |
| query_digest_memory | 1739 |
| mysql_query_rules_memory | 0 |
| mysql_firewall_users_table | 0 |
| mysql_firewall_users_config | 0 |
| mysql_firewall_rules_table | 0 |
| mysql_firewall_rules_config | 0 |
| stack_memory_mysql_threads | 64 |
| stack_memory_admin_threads | 32 |
| stack_memory_cluster_threads | 24 |
+------------------------------+-----------------+
@Wouter0100 : which exact binary of proxysql are you using?
For which distro? I need this information to make sure we run jeprof
against the right binary.
Also, Percona strips symbols from the binaries (in fact our binaries are around 10x bigger), making debugging very difficult.
I recommend to use our binary (you can simply replace the binary without the need to uninstall and reinstall and package) and collect new metrics. In this way jeprof
can tell us even at what exact line of code the memory is being allocated.
@balbaev : there is no evidence you have a similar issue, and in fact it seems completely unrelated.
It also seems you do have activity, and perhaps a lot too.
The in-memory query digest hash table is 1.7GB . Run TRUNCATE TABLE stats_mysql_query_digest
.
Thanks
We have been attempting to find out if we had same memory leak issue. Current proxysql version in use: "2.3.2". Uname: The repository "https://repo.proxysql.com/ProxySQL/proxysql-2.3.x/buster ./ " has been used for installation. Linux lab1 5.10.0-14-amd64 #1 SMP Debian 5.10.113-1 (2022-04-29) x86_64 GNU/Linux
ProxySQL> SELECT * FROM stats.stats_memory_metrics;
+------------------------------+----------------+
| Variable_Name | Variable_Value |
+------------------------------+----------------+
| SQLite3_memory_bytes | 3493840 |
| jemalloc_resident | 77922856960 |
| jemalloc_active | 76263776256 |
| jemalloc_allocated | 76242061080 |
| jemalloc_mapped | 78113628160 |
| jemalloc_metadata | 1624599576 |
| jemalloc_retained | 11602010112 |
| Auth_memory | 1054 |
| query_digest_memory | 59160 |
| mysql_query_rules_memory | 5796 |
| mysql_firewall_users_table | 0 |
| mysql_firewall_users_config | 0 |
| mysql_firewall_rules_table | 0 |
| mysql_firewall_rules_config | 329 |
| stack_memory_mysql_threads | 335544320 |
| stack_memory_admin_threads | 8388608 |
| stack_memory_cluster_threads | 0 |
+------------------------------+----------------+
17 rows in set (0.003 sec)
We used a load balancer (h2load) to simulate proxysql activity and performance.
Performance is satisfactory. The jemalloc_allocated shows 76G consumed by the app.
Can we determine if jemalloc is intelligently keeping the used memory pages for future connectivity?
Also, mysql-threads
is set to 20.
Maybe its the number of backend connections opened and kept alived that are not getting released for inactivity?
@renecannao thanks for your response. Attached the exact binary. proxysql.zip
While replacing the ProxySQL binary with one provided by you, I see this server grew again to 429M.
Admin> select Variable_Name,Variable_Value/1024/1024 as Mem_usage_in_MB from stats_memory_metrics;
+------------------------------+-----------------+
| Variable_Name | Mem_usage_in_MB |
+------------------------------+-----------------+
| SQLite3_memory_bytes | 258 |
| jemalloc_resident | 422 |
| jemalloc_active | 375 |
| jemalloc_allocated | 372 |
| jemalloc_mapped | 442 |
| jemalloc_metadata | 16 |
| jemalloc_retained | 673 |
| Auth_memory | 0 |
| query_digest_memory | 0 |
| mysql_query_rules_memory | 0 |
| mysql_firewall_users_table | 0 |
| mysql_firewall_users_config | 0 |
| mysql_firewall_rules_table | 0 |
| mysql_firewall_rules_config | 0 |
| stack_memory_mysql_threads | 32 |
| stack_memory_admin_threads | 8 |
| stack_memory_cluster_threads | 0 |
+------------------------------+-----------------+
17 rows in set (0.008 sec)
I replaced the binary with the binary from this deb package, as we're running Ubuntu 20.04.3. I enabled memory profiling and will get back to you beginning next week.
Okay, I got another profile with the official ProxySQL binary that has been running for some time. A significant amount of the memory should represent the leak.
Systemd reported 242M memory usage:
root@db1:~# systemctl status proxysql
● proxysql.service - High Performance Advanced Proxy for MySQL
Loaded: loaded (/lib/systemd/system/proxysql.service; enabled; vendor preset: enabled)
Active: active (running) since Tue 2022-07-05 16:39:57 CEST; 6 days ago
Main PID: 3367891 (proxysql)
Tasks: 20 (limit: 4677)
Memory: 241.9M
CGroup: /system.slice/proxysql.service
├─3367891 /usr/bin/proxysql -c /etc/proxysql.cnf
└─3367892 /usr/bin/proxysql -c /etc/proxysql.cnf
ProxySQL reported the following:
Admin> select Variable_Name,Variable_Value/1024/1024 as Mem_usage_in_MB from stats_memory_metrics;
+------------------------------+-----------------+
| Variable_Name | Mem_usage_in_MB |
+------------------------------+-----------------+
| SQLite3_memory_bytes | 145 |
| jemalloc_resident | 231 |
| jemalloc_active | 217 |
| jemalloc_allocated | 214 |
| jemalloc_mapped | 244 |
| jemalloc_metadata | 12 |
| jemalloc_retained | 728 |
| Auth_memory | 0 |
| query_digest_memory | 0 |
| mysql_query_rules_memory | 0 |
| mysql_firewall_users_table | 0 |
| mysql_firewall_users_config | 0 |
| mysql_firewall_rules_table | 0 |
| mysql_firewall_rules_config | 0 |
| stack_memory_mysql_threads | 32 |
| stack_memory_admin_threads | 8 |
| stack_memory_cluster_threads | 0 |
+------------------------------+-----------------+
17 rows in set (0.005 sec)
And.. jeprof output:
All jeprof dumps: jeprof.tar.gz
Thanks @renecannao, if you need anymore information please let me know.
We're running ProxySQL 2.3.2-percona-1.1, packed/distributed by Percona (due to proxysql-admin and some other additional nice-to-haves). Regardless, we're having memory leak issues overal all our ~34 ProxySQL instances and I'm unable to figure out why.
One test server grew from:
to the following in about a week:
Hereby a jeprof.tar.gz. In this case it grew to around 600MB of memory usage over a period of ~2 weeks. On all our servers the growth is seems constant until an OOM or we manually restart it. Some servers have way more RAM then just the 4GB on this test machine, but its never being enough.
The above jeprof results in the following line output by jeprof with the binary that we're using:
jeprof output
``` Total: 108419.5 MB 107541.3 99.2% 99.2% 107541.3 99.2% malloc_usable_size ??:? 548.1 0.5% 99.7% 548.1 0.5% std::_Deque_base::_M_initialize_map ??:? 230.0 0.2% 99.9% 755.2 0.7% MySQL_Connection::reset ??:? 28.0 0.0% 99.9% 2985.8 2.8% MySQL_Thread::create_new_session_and_client_data_stream ??:? 28.0 0.0% 100.0% 49.0 0.0% MySQL_Session::MySQL_Session ??:? 16.0 0.0% 100.0% 39.0 0.0% MySQL_Connection::MySQL_Connection ??:? 12.0 0.0% 100.0% 12.0 0.0% __gnu_cxx::new_allocator::allocate ??:? 5.0 0.0% 100.0% 8.0 0.0% MySQL_Session::init ??:? 2.0 0.0% 100.0% 2.0 0.0% std::__cxx11::basic_string::_M_mutate ??:? 2.0 0.0% 100.0% 2.0 0.0% re2::Regexp::ParseState::PushRepeatOp ??:? 1.0 0.0% 100.0% 1.0 0.0% monitor_galera_thread ??:? 1.0 0.0% 100.0% 1.0 0.0% re2::Compiler::Compiler ??:? 1.0 0.0% 100.0% 3.0 0.0% re2::Regexp::ConcatOrAlternate ??:? 1.0 0.0% 100.0% 1.0 0.0% re2::Regexp::LiteralString ??:? 1.0 0.0% 100.0% 1.0 0.0% re2::Regexp::NewCharClass ??:? 1.0 0.0% 100.0% 1.0 0.0% re2::Regexp::ParseState::DoLeftParen ??:? 1.0 0.0% 100.0% 1.0 0.0% re2::Regexp::ParseState::PushRegexp ??:? 0.0 0.0% 100.0% 2.0 0.0% ConsumerThread::run ??:? 0.0 0.0% 100.0% 13051.4 12.0% MySQL_Connection::async_query ??:? 0.0 0.0% 100.0% 43993.5 40.6% MySQL_Connection::handler ??:? 0.0 0.0% 100.0% 2840.9 2.6% MySQL_Data_Stream::MySQL_Data_Stream ??:? 0.0 0.0% 100.0% 45.1 0.0% MySQL_Data_Stream::buffer2array ??:? 0.0 0.0% 100.0% 6.0 0.0% MySQL_Data_Stream::init@318bb0 ??:? 0.0 0.0% 100.0% 6.0 0.0% MySQL_Data_Stream::init@318d80 ??:? 0.0 0.0% 100.0% 45.1 0.0% MySQL_Data_Stream::read_pkts ??:? 0.0 0.0% 100.0% 754.2 0.7% MySQL_HostGroups_Manager::push_MyConn_to_pool_array ??:? 0.0 0.0% 100.0% 3425.6 3.2% MySQL_Protocol::generate_pkt_row3 ??:? 0.0 0.0% 100.0% 7.0 0.0% MySQL_Protocol::process_pkt_handshake_response ??:? 0.0 0.0% 100.0% 9619.7 8.9% MySQL_ResultSet::add_eof ??:? 0.0 0.0% 100.0% 3425.6 3.2% MySQL_ResultSet::add_row ??:? 0.0 0.0% 100.0% 13045.4 12.0% MySQL_ResultSet::buffer_to_PSarrayOut ??:? 0.0 0.0% 100.0% 25.2 0.0% MySQL_ResultSet::get_resultset ??:? 0.0 0.0% 100.0% 21.1 0.0% MySQL_ResultSet::init ??:? 0.0 0.0% 100.0% 548.1 0.5% MySQL_STMTs_local_v14::MySQL_STMTs_local_v14 ??:? 0.0 0.0% 100.0% 8.0 0.0% MySQL_Session::MySQL_Result_to_MySQL_wire ??:? 0.0 0.0% 100.0% 447.1 0.4% MySQL_Session::RequestEnd ??:? 0.0 0.0% 100.0% 57.2 0.1% MySQL_Session::create_backend ??:? 0.0 0.0% 100.0% 6014.6 5.5% MySQL_Session::get_pkts_from_client ??:? 0.0 0.0% 100.0% 19539.3 18.0% MySQL_Session::handler ??:? 0.0 0.0% 100.0% 7.0 0.0% MySQL_Session::handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE ??:? 0.0 0.0% 100.0% 5903.5 5.4% MySQL_Session::handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY___not_mysql ??:? 0.0 0.0% 100.0% 22.0 0.0% MySQL_Session::handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo ??:? 0.0 0.0% 100.0% 1.0 0.0% MySQL_Session::~MySQL_Session ??:? 0.0 0.0% 100.0% 33980.0 31.3% MySQL_Thread::ProcessAllMyDS_AfterPoll ??:? 0.0 0.0% 100.0% 2992.8 2.8% MySQL_Thread::listener_handle_new_connection ??:? 0.0 0.0% 100.0% 13636.8 12.6% MySQL_Thread::process_all_sessions ??:? 0.0 0.0% 100.0% 30987.2 28.6% MySQL_Thread::process_data_on_data_stream ??:? 0.0 0.0% 100.0% 47646.0 43.9% MySQL_Thread::run ??:? 0.0 0.0% 100.0% 29.2 0.0% MySQL_Thread::run___get_multiple_idle_connections ??:? 0.0 0.0% 100.0% 2.0 0.0% MySQL_Variables::client_set_value ??:? 0.0 0.0% 100.0% 1.0 0.0% MySQL_Variables::server_set_hash_and_value ??:? 0.0 0.0% 100.0% 19945.6 18.4% ProxySQL_Admin::FlushDigestTableToDisk ??:? 0.0 0.0% 100.0% 5903.5 5.4% ProxySQL_Admin::save_mysql_users_runtime_to_database ??:? 0.0 0.0% 100.0% 10933.7 10.1% ProxySQL_Statistics::MySQL_Threads_Handler_sets ??:? 0.0 0.0% 100.0% 10933.7 10.1% ProxySQL_Statistics::MySQL_Threads_Handler_sets_v1 ??:? 0.0 0.0% 100.0% 9012.0 8.3% ProxySQL_Statistics::system_cpu_sets ??:? 0.0 0.0% 100.0% 25.2 0.0% PtrSizeArray::copy_add ??:? 0.0 0.0% 100.0% 807.3 0.7% PtrSizeArray::expand ??:? 0.0 0.0% 100.0% 447.1 0.4% QP_query_digest_stats::QP_query_digest_stats ??:? 0.0 0.0% 100.0% 41.1 0.0% Query_Info::begin ??:? 0.0 0.0% 100.0% 447.1 0.4% Query_Info::end ??:? 0.0 0.0% 100.0% 447.1 0.4% Query_Info::query_parser_update_counters ??:? 0.0 0.0% 100.0% 41.1 0.0% Query_Processor::query_parser_init ??:? 0.0 0.0% 100.0% 447.1 0.4% Query_Processor::query_parser_update_counters ??:? 0.0 0.0% 100.0% 447.1 0.4% Query_Processor::update_query_digest ??:? 0.0 0.0% 100.0% 19945.6 18.4% SQLite3DB::execute ??:? 0.0 0.0% 100.0% 5903.5 5.4% SQLite3DB::prepare_v2 ??:? 0.0 0.0% 100.0% 22.0 0.0% SetParser::parse1[abi:cxx11] ??:? 0.0 0.0% 100.0% 49.0 0.0% __strdup ??:? 0.0 0.0% 100.0% 5903.5 5.4% admin_handler_command_load_or_save ??:? 0.0 0.0% 100.0% 5903.5 5.4% admin_session_handler ??:? 0.0 0.0% 100.0% 22718.5 21.0% calloc ??:? 0.0 0.0% 100.0% 5903.5 5.4% child_mysql ??:? 0.0 0.0% 100.0% 74251.3 68.5% clone ??:? 0.0 0.0% 100.0% 11283.9 10.4% hash_element ??:? 0.0 0.0% 100.0% 112.7 0.1% ma_alloc_root ??:? 0.0 0.0% 100.0% 12.0 0.0% ma_multi_malloc ??:? 0.0 0.0% 100.0% 11283.9 10.4% ma_net_read ??:? 0.0 0.0% 100.0% 11283.9 10.4% ma_net_safe_read ??:? 0.0 0.0% 100.0% 22708.5 20.9% ma_pvio_init ??:? 0.0 0.0% 100.0% 22.0 0.0% ma_read_ok_packet ??:? 0.0 0.0% 100.0% 67631.3 62.4% memalign ??:? 0.0 0.0% 100.0% 1.0 0.0% monitor_ping_thread ??:? 0.0 0.0% 100.0% 112.7 0.1% mthd_my_read_metadata_ex ??:? 0.0 0.0% 100.0% 11283.9 10.4% mthd_my_read_one_row ??:? 0.0 0.0% 100.0% 112.7 0.1% mthd_my_read_query_result ??:? 0.0 0.0% 100.0% 22708.5 20.9% mthd_my_real_connect ??:? 0.0 0.0% 100.0% 30942.1 28.5% my_context_init ??:? 0.0 0.0% 100.0% 34168.2 31.5% my_context_spawn ??:? 0.0 0.0% 100.0% 63.0 0.1% mysql_change_user ??:? 0.0 0.0% 100.0% 11283.9 10.4% mysql_fetch_row ??:? 0.0 0.0% 100.0% 30942.1 28.5% mysql_optionsv ??:? 0.0 0.0% 100.0% 41.1 0.0% mysql_query_digest_and_first_comment ??:? 0.0 0.0% 100.0% 22708.5 20.9% mysql_real_connect ??:? 0.0 0.0% 100.0% 6.0 0.0% mysql_use_result ??:? 0.0 0.0% 100.0% 47646.0 43.9% mysql_worker_thread_func ??:? 0.0 0.0% 100.0% 2.0 0.0% re2::Compiler::AddRuneRange ??:? 0.0 0.0% 100.0% 2.0 0.0% re2::Compiler::AddRuneRangeUTF8 ??:? 0.0 0.0% 100.0% 2.0 0.0% re2::Compiler::AllocInst ??:? 0.0 0.0% 100.0% 2.0 0.0% re2::Compiler::ByteRange ??:? 0.0 0.0% 100.0% 12.0 0.0% re2::Compiler::Compile ??:? 0.0 0.0% 100.0% 9.0 0.0% re2::Compiler::Finish ??:? 0.0 0.0% 100.0% 2.0 0.0% re2::Compiler::PostVisit ??:? 0.0 0.0% 100.0% 2.0 0.0% re2::Compiler::UncachedRuneByteSuffix ??:? 0.0 0.0% 100.0% 1.0 0.0% re2::DFA::CachedState ??:? 0.0 0.0% 100.0% 1.0 0.0% re2::DFA::FastSearchLoop ??:? 0.0 0.0% 100.0% 1.0 0.0% re2::DFA::InlinedSearchLoop ??:? 0.0 0.0% 100.0% 1.0 0.0% re2::DFA::RunStateOnByte ??:? 0.0 0.0% 100.0% 1.0 0.0% re2::DFA::RunStateOnByteUnlocked ??:? 0.0 0.0% 100.0% 1.0 0.0% re2::DFA::Search ??:? 0.0 0.0% 100.0% 1.0 0.0% re2::DFA::SearchFFT ??:? 0.0 0.0% 100.0% 1.0 0.0% re2::DFA::WorkqToCachedState ??:? 0.0 0.0% 100.0% 1.0 0.0% re2::FactorAlternationImpl::Round1 ??:? 0.0 0.0% 100.0% 1.0 0.0% re2::FactorAlternationImpl::Round3 ??:? 0.0 0.0% 100.0% 10.0 0.0% re2::PODArray::PODArray ??:? 0.0 0.0% 100.0% 9.0 0.0% re2::Prog::Flatten ??:? 0.0 0.0% 100.0% 1.0 0.0% re2::Prog::MarkSuccessors ??:? 0.0 0.0% 100.0% 1.0 0.0% re2::Prog::SearchDFA ??:? 0.0 0.0% 100.0% 21.0 0.0% re2::RE2::Init ??:? 0.0 0.0% 100.0% 1.0 0.0% re2::RE2::Match ??:? 0.0 0.0% 100.0% 21.0 0.0% re2::RE2::RE2 ??:? 0.0 0.0% 100.0% 1.0 0.0% re2::RE2::Replace ??:? 0.0 0.0% 100.0% 12.0 0.0% re2::Regexp::CompileToProg ??:? 0.0 0.0% 100.0% 2.0 0.0% re2::Regexp::FactorAlternation ??:? 0.0 0.0% 100.0% 7.0 0.0% re2::Regexp::Parse ??:? 0.0 0.0% 100.0% 3.0 0.0% re2::Regexp::ParseState::DoAlternation ??:? 0.0 0.0% 100.0% 3.0 0.0% re2::Regexp::ParseState::DoCollapse ??:? 0.0 0.0% 100.0% 3.0 0.0% re2::Regexp::ParseState::DoRightParen ??:? 0.0 0.0% 100.0% 1.0 0.0% re2::Regexp::ParseState::PushLiteral ??:? 0.0 0.0% 100.0% 2.0 0.0% re2::Regexp::Walker::WalkExponential ??:? 0.0 0.0% 100.0% 2.0 0.0% re2::Regexp::Walker::WalkInternal ??:? 0.0 0.0% 100.0% 5.0 0.0% re2::SparseArray::SparseArray ??:? 0.0 0.0% 100.0% 2.0 0.0% re2::SparseSetT::SparseSetT ??:? 0.0 0.0% 100.0% 17191.5 15.9% realloc ??:? 0.0 0.0% 100.0% 22.0 0.0% run_plugin_auth ??:? 0.0 0.0% 100.0% 4.0 0.0% sha1_pass_hex ??:? 0.0 0.0% 100.0% 5903.5 5.4% sqlite3_backup_init ??:? 0.0 0.0% 100.0% 25849.1 23.8% sqlite3_db_config ??:? 0.0 0.0% 100.0% 25850.1 23.8% sqlite3_errmsg ??:? 0.0 0.0% 100.0% 25849.1 23.8% sqlite3_exec ??:? 0.0 0.0% 100.0% 5903.5 5.4% sqlite3_prepare_v2 ??:? 0.0 0.0% 100.0% 19946.6 18.4% sqlite3_randomness ??:? 0.0 0.0% 100.0% 5903.5 5.4% sqlite3_realloc64 ??:? 0.0 0.0% 100.0% 19946.6 18.4% sqlite3_release_memory ??:? 0.0 0.0% 100.0% 19946.6 18.4% sqlite3_reset ??:? 0.0 0.0% 100.0% 5903.5 5.4% sqlite3_result_value ??:? 0.0 0.0% 100.0% 19946.6 18.4% sqlite3_step ??:? 0.0 0.0% 100.0% 74251.3 68.5% start_thread ??:? 0.0 0.0% 100.0% 1.0 0.0% std::_Vector_base::_M_allocate ??:? 0.0 0.0% 100.0% 2.0 0.0% std::__cxx11::basic_string::_M_replace ??:? 0.0 0.0% 100.0% 1.0 0.0% std::allocator_traits::allocate ??:? 0.0 0.0% 100.0% 754.2 0.7% std::error_code::default_error_condition ??:? 0.0 0.0% 100.0% 1.0 0.0% std::vector::_M_realloc_insert ??:? 0.0 0.0% 100.0% 1.0 0.0% std::vector::emplace_back ??:? 0.0 0.0% 100.0% 447.1 0.4% strndup ??:? 0.0 0.0% 100.0% 1.0 0.0% verify_set_names ??:? 0.0 0.0% 100.0% 34168.2 31.5% zError ??:? ```I'm not sure how to read the above output though.
proxysql.cnf
``` datadir="/var/lib/proxysql" admin_variables= { admin_credentials="admin:x" mysql_ifaces="127.0.0.1:6032" stats_credentials="stats:x" } mysql_variables= { interfaces="[::]:3306;0.0.0.0:3306" monitor_username="proxysql" monitor_password="x" monitor_galera_healthcheck_interval=2000 # Should preferably be changed to the new value that may be introduced in sysown/proxysql#2597 set_query_lock_on_hostgroup=0 # On MySQL we deploy a timeout of 8 hours, so we make sure ProxySQL terminates first with a 6 hour timeout. wait_timeout=21600000 } mysql_galera_hostgroups = ( { writer_hostgroup=10 backup_writer_hostgroup=20 reader_hostgroup=30 offline_hostgroup=9999 max_writers=1 writer_is_also_reader=0 max_transactions_behind=30 active=1 } ) mysql_servers = ( { address="10.10.2.153" port=13306 hostgroup=10 } ) mysql_query_rules = ( { rule_id=300 active=1 match_pattern=".*" destination_hostgroup=10 apply=1 multiplex=0 } ) ```Thanks.