Proxysql: Physical Memory Increasing continuously on Proxysql Server .

[rishimittal]

There was a noticeable behaviour in the continuous increase in the physical memory consumptions, and then there was a sudden drop in the memory consumption and same pattern is followed again. Please find the attached screenshot.

This is on the time frame of 24 hours, any explanation for this??

[renecannao]

This seems to be an issue since version 1.2 , but becames even more noticeable in 1.3 as it has more threads.
Although memory usage grows, no memory leaks were detected and I am investigating possible ways to tune memory usage.
Branch 1.2.4-lowmem has some tweaks, and they are mostly applied to 1.3.1 too.

Which version are you using?
How much is the total amount of RAM? I see the percentage, but I am also interested in knowing the total.
Finally, can you add this at line 19 in /etc/init.d/proxysql , restart proxysql, and see if the memory increase has a different pattern?

export MALLOC_CONF="xmalloc:true,lg_chunk:18,lg_tcache_max:12,purge:ratio"

Thanks.

[renecannao]

Updated the previous comment from MALLOC_CONF ... to export MALLOC_CONF ...

[rishimittal]

The version I am using is ProxySQL version 1.3.0e-0-g9fc5438, codename Truls, also can you please explain what the suggested change by you does, Is it documented somewhere??

RAM(on server) : 8GB
Mysql-threads : 16

[renecannao]

Thank you for the info.
The options are related to jemalloc (used by ProxySQL) and they attempt to reduce the size of thread cache and chunk size, and also change the purging algorithm. Details here:
http://jemalloc.net/jemalloc.3.html

From my testings this doesn't completely solve the problem, but help. I am curious if it helps also in your case.

Thanks

[rishimittal]

@renecannao : I am currently using ProxySQL version 1.3.0e-0-g9fc5438, codename Truls which version can i now download to use above fixes . Release page https://github.com/sysown/proxysql/releases does not shows that patch any related patch on 1.3.0e.

[renecannao]

@rishimittal : I am running few more tests before releasing 1.3.0g . Just a bit more of patience and will be released 😉
Thanks

[renecannao]

Version v1.3.0g is now released.
Thank you

[rishimittal]

@renecannao Thank you for the support . I have updated my proxysql version to ProxySQL version 1.3.0g-0-g244a052, codename Truls but the memory leaks are still happening. I can see the continuous increase in the memory consumptions.

Also just for Info in the logs(/var/lib/proxysql/proxysql.log), I am having lot of warnings which are caused due to duplicate entries, etc.(basically bad legacy code and improper table structure), But ideally that should have impact on continuous memory consumption.

[renecannao]

@rishimittal : I am very sorry to hear this didn't fix your issue.
Is the memory growth rate the same, or it has slowed down?

I will shortly write a wiki page on how to perform memory profiling, so that you can enable it and help me identify the root cause of the memory growth.
When the wiki page is ready I will update this issue.
Thank you for your patience.

[rishimittal]

@renecannao Thank you for the support and Memory growth is quite similar as before.

[renecannao]

@rishimittal:
please let me know if these instructions are clear: https://github.com/sysown/proxysql/wiki/Memory-leak-detection

Thanks

[rishimittal]

@renecannao Yes, the instructions are quite clear. Please find the attached tar.gz file.
jeprof_heap.tar.gz

[renecannao]

@rishimittal : thanks. Quick question: which binary did you installed exactly? I mean, which version, on which OS, and if debug or not. With this information I can better process the files. Thanks

[rishimittal]

@renecannao
Binary details:
https://github.com/sysown/proxysql/releases/download/1.3.0g/proxysql_1.3.0g-ubuntu14_amd64.deb

OS details :
Distributor ID: Ubuntu
Description: Ubuntu 14.04.4 LTS
Release: 14.04
Codename: trusty

Proxysql version
ProxySQL version 1.3.0g-0-g244a052, codename Truls

Please tell me if anything else is required.

[renecannao]

Thank you very much!

[renecannao]

Here are some reports, starting from different dump file but all ending on the last one (jeprof.3205.63.i63.heap) .
There is clearly a steady increase of memory allocated in the function responsible from reading data sent from the client.

I believe this is somehow related to prepared statements.
While I continue my debugging, can you please attach the output of this command executed on Admin? :

SHOW MYSQL STATUS;

Thanks you

[rishimittal]

@renecannao Here is the output required by you.

mysql> SHOW MYSQL STATUS;
+------------------------------+--------------+
| Variable_name | Value |
+------------------------------+--------------+
| Active_Transactions | 0 |
| Backend_query_time_nsec | 185603012163 |
| Client_Connections_aborted | 0 |
| Client_Connections_connected | 19 |
| Client_Connections_created | 1188182 |
| Client_Connections_non_idle | 3 |
| Com_autocommit | 102 |
| Com_autocommit_filtered | 102 |
| Com_commit | 0 |
| Com_commit_filtered | 0 |
| Com_rollback | 0 |
| Com_rollback_filtered | 0 |
| Com_stmt_close | 5970106 |
| Com_stmt_execute | 5975098 |
| Com_stmt_prepare | 5971300 |
| ConnPool_get_conn_failure | 0 |
| ConnPool_get_conn_immediate | 23807 |
| ConnPool_get_conn_success | 6434290 |
| ConnPool_memory_bytes | 641232 |
| MySQL_Monitor_Workers | 16 |
| MySQL_Thread_Workers | 16 |
| Queries_backends_bytes_recv | 3623159839 |
| Queries_backends_bytes_sent | 1726314037 |
| 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 | 253333110 |
| Questions | 19378677 |
| SQLite3_memory_bytes | 843072 |
| Server_Connections_aborted | 0 |
| Server_Connections_connected | 12 |
| Server_Connections_created | 136620 |
| Servers_table_version | 1 |
| Slow_queries | 222 |
| Stmt_Active_Total | 7 |
| Stmt_Active_Unique | 4 |
| Stmt_Max_Stmt_id | 21344 |
| mysql_backend_buffers_bytes | 212736 |
| mysql_frontend_buffers_bytes | 1245184 |
| mysql_session_internal_bytes | 94608 |
+------------------------------+--------------+
45 rows in set (0.00 sec)

[renecannao]

@rishimittal , the default value for mysql-max_stmts_cache is 10000, that means that ProxySQL will cache up to 10000 prepared statements.
Your value of Stmt_Max_Stmt_id is beyond mysql-max_stmts_cache , that means:

1. ProxySQL is caching a lot of PS, so it is OK to use a lot of memory
2. memory usage should grow up until Stmt_Max_Stmt_id reaches mysql-max_stmts_cache : as the trend doesn't stop, this sounds like a bug.
   I will continue investigating

[rishimittal]

@renecannao Got it, Opened a new issue to document these variables mentioned above. https://github.com/sysown/proxysql/issues/798

[rishimittal]

@renecannao Any update on this??

[renecannao]

The root cause has been identified in a line of code commented. I am running multiple tests to identify if re-enabling it had some other side effect.

[rishimittal]

@renecannao Thanks for the update :) , Can you tell the release date/version which I can use after this issue is fixed.

[renecannao]

Building it right now

[rishimittal]

@renecannao thats awesome ...waiting for new release.

[renecannao]

@rishimittal , v1.3.0h is available
Thank you

[rishimittal]

@renecannao As we have discussed earlier, regarding the value of Stmt_Max_Stmt_id , it is still having the same issue.
mysql> show mysql status;
+------------------------------+---------------+
| Variable_name | Value |
+------------------------------+---------------+
| Active_Transactions | 0 |
| Backend_query_time_nsec | 3025786465259 |
| Client_Connections_aborted | 3 |
| Client_Connections_connected | 447 |
| Client_Connections_created | 12054642 |
| Client_Connections_non_idle | 49 |
| Com_autocommit | 1555 |
| Com_autocommit_filtered | 1555 |
| Com_commit | 0 |
| Com_commit_filtered | 0 |
| Com_rollback | 4 |
| Com_rollback_filtered | 0 |
| Com_stmt_close | 79007047 |
| Com_stmt_execute | 79162950 |
| Com_stmt_prepare | 79152154 |
| ConnPool_get_conn_failure | 0 |
| ConnPool_get_conn_immediate | 2717607 |
| ConnPool_get_conn_success | 83386362 |
| ConnPool_memory_bytes | 926584 |
| MySQL_Monitor_Workers | 16 |
| MySQL_Thread_Workers | 16 |
| Queries_backends_bytes_recv | 44003351944 |
| Queries_backends_bytes_sent | 28021442008 |
| 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 | 2677019918 |
| Questions | 251509152 |
| SQLite3_memory_bytes | 843080 |
| Server_Connections_aborted | 0 |
| Server_Connections_connected | 48 |
| Server_Connections_created | 2252557 |
| Servers_table_version | 1 |
| Slow_queries | 11260 |
| Stmt_Active_Total | 1351 |
| Stmt_Active_Unique | 28 |
| Stmt_Max_Stmt_id | 302030 |
| mysql_backend_buffers_bytes | 2481920 |
| mysql_frontend_buffers_bytes | 29229056 |
| mysql_session_internal_bytes | 316024 |
+------------------------------+---------------+
45 rows in set (0.00 sec)

Also there is a steady increase in the memory w.r.t time.

[renecannao]

@rishimittal:
about the value of Stmt_Max_Stmt_id , this is not necessarily a big issue and it may be reasonable ok considering your workload.
To be more specific:

• your application created 79152154 prepared statements (Com_stmt_prepare)
• currently there are 28 unique prepared statements in cache (Stmt_Active_Unique) with some degree of duplicates (Stmt_Active_Total) across multiple connections (Client_Connections_connected)
• The auto-increment for statement id, Stmt_Max_Stmt_id , is reasonable low compared to Com_stmt_prepare : this seems to be ok.
  (I must document all this).

With regards to memory increase: how severe is that?
Could you try to enable profiling again?

Thanks

[rishimittal]

@renecannao Considering the rate of memory increase, and it is pretty severe. In 12 hours memory increased from 200MB to 2.5GB and as trend shows it is consistently increasing. I will share the profiling results soon.

[renecannao]

@rishimittal : did you manage to get some profiling results from this?
Thanks.

[renecannao]

@rishimittal : did you manage to get some profiling results?
Thanks

[taylor840326]

@renecannao I found memory leaks in my test environment too.
In my environment,if proxysql print a large number of warnings or errors to proxysql.log,the memory useage will increase. at last, os will kill the proxysql process.

1.My test environment is :

1.proxysql = 1.3.0h
2.os = CentOS 7 64bit
3.benchmark toosl = tpcc-mysql
4.mysql = 5.6.x

172.18.6.195 is mysql node IP address.

172.18.6.194 is proxysql node IP address.

172.18.6.193 is benchmark tools node IP address.

2.test case:

2.1.download and compile tpcc-mysql

$ git clone https://github.com/Percona-Lab/tpcc-mysql.git
$ cd tpcc-mysql/src ; make

2.2.download and install proxysql 1.3.0h

# yum install https://github.com/sysown/proxysql/releases/download/v1.3.0h/proxysql-1.3.0h-1-centos7.x86_64.rpm

2.3.download and start mysql 5.6

# docker pull mysql:5.6
# docker run --network host --name m56 -m 4096m -e MYSQL_ROOT_PASSWORD=111111 -d mysql:5.6
# docker exec -it m56 /usr/bin/mysql -p111111
mysql> create database tpcc;
mysql> grant all privileges on *.* to 'dev'@'%' identified by 'dev';
mysql> create user 'monitor'@'%' identified by 'monitor';
mysql> flush privileges;

2.4.setup mysql variables in proxysql

mysql> insert into mysql_users(username,password,default_hostgroup) values('dev','dev',0);
mysql> insert into mysql_servers(hostgroup_id,hostname,port,status) vlaues(0,'172.18.6.195',3306,'ONLINE'),(1,'172.18.6.195',3306,'ONLINE');
mysql> insert into mysql_query_rules(active,apply,username,match_digest,destination_hostgroup) values(1,1,'dev','^SELECT',1),(1,1,'dev','^INSERT',0),(1,1,'dev','^UPDATE',0),(1,1,'dev','^DELETE',0);
mysql> load mysql users to runtime; save mysql users to disk;
mysql> load mysql servers to runtime;save mysql servers to disk;
mysql> load mysql query rules to runtime;save mysql query rules to disk;

2.5.create table schema on mysql node

$ cd tpcc-mysql
$ cat create_table.sql |mysql -h 172.18.6.194 -udev -pdev -P6033 tpcc
$ cat add_fkey_idx.sql | mysql -h 172.18.6.194 -udev -pdev -P6033 tpcc

2.6.generate data

$ vim tpcc-mysql/load.sh
change HOST=172.18.6.194 ,-u dev , -p dev ,-P 6033
$ ./load.sh tpcc 1000

2.7.use top inspect proxysql process memory usage

proxysql print a large number WARNING logs to proxysql.log

SAMPLE:

2016-12-07 05:49:52 MySQL_Session.cpp:2225:handler(): [WARNING] Error during query on (0,172.18.6.195,3306): 1452, Cannot add or update a child row: a foreign key constraint fails (tpcc.customer, CONSTRAINT fkey_customer_1 FOREIGN KEY (c_w_id, c_d_id) REFERENCES district (d_w_id, d_id))

proxysql process memory usage increase.

[taylor840326]

@rishimittal are there a lot of error or warning logs in your proxysql.log?

[taylor840326]

@renecannao I found proxysql memory usage increase when proxysql print ERROR/WARNING/INFORMATION logs to proxysql.log.
the memory usage not increase when proxysql not print any ERROR/WARNING/INFORMATION logs to proxysql.log .

[taylor840326]

@renecannao libdaemon memory leak? I'm testing.....

[renecannao]

@taylor840326 : thank you for the test case!
I will try to reproduce it now.

[renecannao]

@taylor840326 What you reported is caused by an incorrect handling of errors during STMT_EXECUTE, and it is now fixed.
I will try to investigate also the FK errors before building a new release

[rishimittal]

@taylor840326 : Yes there are lot of warnings in the proxysql.log.

[renecannao]

@taylor840326 :
TPCC parallel load doesn't work with ProxySQL because TPCC uses SET FOREIGN_KEY_CHECKS=0 and ProxySQL doesn't track it.
I created a new issue for this: #835 , and work on it as high priority.

Meanwhile, you can add foreign keys after the data is loaded:

$ cat create_table.sql |mysql -h 172.18.6.194 -udev -pdev -P6033 tpcc
$ ./load.sh tpcc 1000
$ cat add_fkey_idx.sql | mysql -h 172.18.6.194 -udev -pdev -P6033 tpcc

[taylor840326]

@renecannao ok:)

[renecannao]

@taylor840326 : #835 is already fixed and v1.3.1 is available :)

[geotro]

I'm seeing a similar trend, after say 8 hours of running under our full query load proxysql takes up about 1.5GB and I think it will grow further if I let it run for longer, I'll test and report. In the mean time:

MySQL [(none)]> show mysql status;
+------------------------------+----------------+
| Variable_name | Value |
+------------------------------+----------------+
| Active_Transactions | 0 |
| Backend_query_time_nsec | 58396118755230 |
| Client_Connections_aborted | 0 |
| Client_Connections_connected | 410 |
| Client_Connections_created | 5481199 |
| Client_Connections_non_idle | 408 |
| Com_autocommit | 9789917 |
| Com_autocommit_filtered | 9789917 |
| Com_commit | 4894958 |
| Com_commit_filtered | 0 |
| Com_rollback | 0 |
| Com_rollback_filtered | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_prepare | 0 |
| ConnPool_get_conn_failure | 0 |
| ConnPool_get_conn_immediate | 1109 |
| ConnPool_get_conn_success | 5605011 |
| ConnPool_memory_bytes | 1232224 |
| MySQL_Monitor_Workers | 16 |
| MySQL_Thread_Workers | 8 |
| Queries_backends_bytes_recv | 747867260254 |
| Queries_backends_bytes_sent | 310995993817 |
| 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 | 2482683437956 |
| Questions | 584614949 |
| SQLite3_memory_bytes | 851152 |
| Server_Connections_aborted | 15 |
| Server_Connections_connected | 406 |
| Server_Connections_created | 5344513 |
| Servers_table_version | 2462 |
| Slow_queries | 41880 |
| Stmt_Active_Total | 0 |
| Stmt_Active_Unique | 0 |
| Stmt_Max_Stmt_id | 0 |
| mysql_backend_buffers_bytes | 921982976 |
| mysql_frontend_buffers_bytes | 26804224 |
| mysql_session_internal_bytes | 868232 |
+------------------------------+----------------+
45 rows in set (0.00 sec)

I have about an hour or so of jemalloc logging data if you wish, but the archive is too large to post here. Let me know if you want it.

[renecannao]

@geotro : how big is the archive? I am surely interested in it.
Thanks

[taylor840326]

@geotro mysql_backend_buffers_bytes = 921982976 ? O_0
please attach the output of this command executed on Admin?

show processlist;
show mysql variables;

[sodabrew]

Has this memory leak issue been resolved in recent 1.3.x releases, or is it blocked until 1.4.0?

[renecannao]

This is fixed. Closing

[sodabrew]

Awesome! But, is it fixed in the recent 1.3.x releases, or only in the master branch pending 1.4.0 release?

[renecannao]

@sodabrew : sorry, I didn't reply your answer.
It is fixed since 1.3.1 : https://github.com/sysown/proxysql/releases/tag/v1.3.1
Thanks

[zsrinivas]

@rishimittal , the default value for mysql-max_stmts_cache is 10000, that means that ProxySQL will cache up to 10000 prepared statements.
Your value of Stmt_Max_Stmt_id is beyond mysql-max_stmts_cache , that means:

1. ProxySQL is caching a lot of PS, so it is OK to use a lot of memory
2. memory usage should grow up until Stmt_Max_Stmt_id reaches mysql-max_stmts_cache : as the trend doesn't stop, this sounds like a bug.
   I will continue investigating

Hi, were you able to identify why Stmt_Max_Stmt_id is going beyond mysql-max_stmts_cache ?