Query rules memory usage monitoring

[mitaka]

We have enabled caching for query rules and we are unable to monitor each query rule how much memory uses for cache. Probably adding a column such as memory_usage to mysql_query_rules or stats_mysql_query_rules will be enough. If there is another way to monitor memory usage please provide us with instructions.

Please advice us how much more ram should proxysql use when cache for query rules is enabled. We have enabled cache for around 14 rules and the usage increased up to around 5GB ram.

Looking forward to hearing from you,
Dimitar Ianakiev

[renecannao]

Right now it is not possible to monitor memory usage related to caching.
I like the idea of an extra column in stats_mysql_query_rules , and I think this is the way I will implement it.

Since memory usage is a concern, I think another option I will be looking into is the possibility of compressing resultsets in memory.

[dkanchev]

@renecannao Supporting compression is a great idea. I am also part of the SiteGround team and I would like to mention that I just noticed on one of our servers that once the ProxySQL starts consuming a lot of memory its CPU usage also increases. Here is an example:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
875 root 20 0 5185m 4.0g 2832 S 203.2 33.3 145:50.84 proxysql

Here are the details for all threads from htop:

PID USER PRI NI VIRT RES SHR S CPU% MEM% TIME+ Command
883 root 20 0 5199M 4100M 2832 S 0.0 33.4 0:01.47 proxysql -f
884 root 20 0 5199M 4100M 2832 R 98.6 33.4 1h10:44 proxysql -f
885 root 20 0 5199M 4100M 2832 S 0.0 33.4 5:31.12 proxysql -f
886 root 20 0 5199M 4100M 2832 R 99.1 33.4 1h10:22 proxysql -f
887 root 20 0 5199M 4100M 2832 S 0.0 33.4 5:28.99 proxysql -f
888 root 20 0 5199M 4100M 2832 S 0.0 33.4 0:10.43 proxysql -f
954 root 20 0 5199M 4100M 2832 S 0.0 33.4 0:02.31 proxysql -f
956 root 20 0 5199M 4100M 2832 S 0.9 33.4 1:11.35 proxysql -f
961 root 20 0 5199M 4100M 2832 S 0.0 33.4 0:59.48 proxysql -f
962 root 20 0 5199M 4100M 2832 S 0.0 33.4 0:52.30 proxysql -f
963 root 20 0 5199M 4100M 2832 S 0.0 33.4 0:03.93 proxysql -f
28467 root 20 0 5199M 4100M 2832 S 0.0 33.4 0:19.19 proxysql -f
875 root 20 0 5199M 4100M 2832 S 198. 33.4 2h35:51 proxysql -f

I am not sure if this behavior is expected but it would be good to know how to debug such CPU/RAM intensive ProxySQL processes.

[renecannao]

@dkanchev , I can surely try to reproduce it and see if there is any increased CPU usage caused by a lot of entries in memory.
Meanwhile, can you check with perf which are the most time consuming call?
Also, which version is this one?

[dkanchev]

Here is the data taken from "perf stat" for 60 seconds:

Performance counter stats for process id 875:

     117571.449093      task-clock (msec)         #    1.959 CPUs utilized           [100.00%]
            34,106      context-switches          #    0.290 K/sec                   [100.00%]
             4,899      cpu-migrations            #    0.042 K/sec                   [100.00%]
            55,228      page-faults               #    0.470 K/sec
   305,400,836,439      cycles                    #    2.598 GHz                     [100.00%]
   <not supported>      stalled-cycles-frontend
   <not supported>      stalled-cycles-backend
   175,658,581,499      instructions              #    0.58  insns per cycle         [100.00%]
    31,570,344,549      branches                  #  268.521 M/sec                   [100.00%]
        79,374,532      branch-misses             #    0.25% of all branches

      60.000956429 seconds time elapsed

As for the version of proxysql - it should be 1.2.0-1. However, @mitaka should confirm the exact version (probably he compiled it on his end).

[renecannao]

Thank you @dkanchev . Sorry for not being specific, I was looking for the output of :

perf record -p `pidof proxysql`

leave it running for some time, then ctrl+c it. Followed by:

perf report | head -n 50

[hackman]

[root@machine ~]# perf4 record -p 12567
^C[ perf record: Woken up 11 times to write data ]
[ perf record: Captured and wrote 8.763 MB perf.data (228655 samples) ]

[root@machine ~]# perf4 report | head -n 50

To display the perf.data header info, please use --header/--header-only options.

Total Lost Samples: 0

Samples: 228K of event 'cycles:pp'

Event count (approx.): 144729089400

Overhead Command Shared Object Symbol

19.14% proxysql [kernel.kallsyms] [k] _raw_spin_lock_irqsave
15.90% proxysql [kernel.kallsyms] [k] fget_light
11.81% proxysql [kernel.kallsyms] [k] fput
7.03% proxysql proxysql [.] MySQL_Thread::run
5.71% proxysql [kernel.kallsyms] [k] _raw_spin_unlock_irqrestore
5.23% proxysql [kernel.kallsyms] [k] tcp_poll
4.29% proxysql [kernel.kallsyms] [k] unix_poll
4.17% proxysql [kernel.kallsyms] [k] do_sys_poll
2.45% proxysql [kernel.kallsyms] [k] __pollwait
2.26% proxysql [kernel.kallsyms] [k] sock_poll
1.90% proxysql [kernel.kallsyms] [k] free_poll_entry
1.49% proxysql [kernel.kallsyms] [k] add_wait_queue
1.46% proxysql [kernel.kallsyms] [k] remove_wait_queue
1.42% proxysql [kernel.kallsyms] [k] trace_hardirqs_off
1.12% proxysql proxysql [.] MySQL_Logger::wrlock
1.09% proxysql [kernel.kallsyms] [k] select_estimate_accuracy
0.84% proxysql [kernel.kallsyms] [k] trace_hardirqs_on
0.73% proxysql [kernel.kallsyms] [k] ktime_get_ts
0.68% proxysql [kernel.kallsyms] [.] sysret_check
0.67% proxysql libc-2.12.so [.] __libc_disable_asynccancel
0.60% proxysql proxysql [.] MySQL_Data_Stream::array2buffer
0.59% proxysql [vdso] [.] __vdso_clock_gettime
0.58% proxysql proxysql [.] MySQL_Session::handler
0.53% proxysql proxysql [.] MySQL_Session::writeout
0.52% proxysql proxysql [.] MySQL_Data_Stream::set_pollout
0.50% proxysql [kernel.kallsyms] [k] copy_user_enhanced_fast_string
0.47% proxysql proxysql [.] MySQL_Thread::process_all_sessions
0.45% proxysql [unknown] [k] 0x00002acdd349d111
0.42% proxysql [kernel.kallsyms] [k] system_call_after_swapgs
0.42% proxysql proxysql [.] MySQL_Data_Stream::write_to_net_poll
0.32% proxysql [kernel.kallsyms] [k] pipe_poll
0.31% proxysql [kernel.kallsyms] [k] sys_poll
0.29% proxysql libc-2.12.so [.] __libc_enable_asynccancel
0.26% proxysql [kernel.kallsyms] [k] read_tsc
0.22% proxysql [kernel.kallsyms] [k] poll_freewait
0.21% proxysql [kernel.kallsyms] [k] trace_hardirqs_on_caller
0.19% proxysql [kernel.kallsyms] [k] poll_select_set_timeout
0.19% proxysql proxysql [.] MySQL_Thread::process_data_on_data_stream

[mitaka]

The exact version is proxysql-1.2.0-1-centos67.x86_64.rpm taken from https://github.com/sysown/proxysql/releases/ v1.2.0f section.

[renecannao]

Virtual memory utilization should be drastically dropped since version v1.2.0k after adding --nummap to jemalloc : #612 .
This change is now in stable v1.2.1.
Branch v1.2.2 starts having additional metrics, tracked in #140 .

Back to the original request of adding an extra column in stats_mysql_query_rules, we need to keep in mind that multiple rules can modify cache_ttl of a single query. Yet, I can track the last rule_id that mas modified cache_ttl.
Another think to keep in mind is that cache_ttl can be changed "inline" (see #166) : in that case, a "special" rule_id should be tracked, for example "0".