Possible MySQL memory leak issue

[gvard]

Hello,

We face a very strange issue and need your help on this one. We tried contacting CloudLinux before because there seems to be a MySQL memory leak, however they told us "As for huge MySQL memory allocation, we are not very experienced in troubleshooting these kinds of issues" (CloudLinux ticket 70971). I thought that since a CL license was bought the OS would be supported too, please let me know if I am wrong and if you can shed a light on this issue.

We have CL7 with MySQL Governor installed, in order not to have any abusive sites using up all MySQL. Last Friday (04/Oct) the server used up all the memory + swap file and froze. I rebooted the server, however I see through munin that after a few hours the memory ended again and slowly the swap file started to get used again. Please see prnt.sc/pf6m0z .

FACT 1:
After communication with CL we were advised to remove the swap file which we did (the server has 32GB of RAM) and set the following:

innodb_buffer_pool_size = 5G
max_allowed_packet = 128M

...however MySQL again uses more than 20GB of RAM each time and we manually restart it to prevent from using up all memory. One time we didn't restart it (last Sunday 23:30) and it used up all memory, making the server unresponsive.

FACT 2:
After each mysql restart we performed, in .err file the following gets recorded:

2019-10-05T00:39:28.422650+02:00 0 [Note] InnoDB: FTS optimize thread exiting.
2019-10-05T00:39:28.422801+02:00 0 [Note] InnoDB: Starting shutdown...
2019-10-05T00:39:28.522999+02:00 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
2019-10-05T00:39:28.523949+02:00 0 [Note] InnoDB: Buffer pool(s) dump completed at 191005 0:39:28
2019-10-05T00:39:29.023601+02:00 0 [ERROR] [FATAL] InnoDB: Page [page id: space=109603, page number=488] still fixed or dirty
2019-10-05 00:39:29 0x7f04eb259780 InnoDB: Assertion failure in thread 139659101706112 in file ut0ut.cc line 910
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
21:39:29 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=28
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68201 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xf0930b]
/usr/sbin/mysqld(handle_fatal_signal+0x461)[0x7bacb1]
/lib64/libpthread.so.0(+0xf5f0)[0x7f04eae3f5f0]
/lib64/libc.so.6(gsignal+0x37)[0x7f04e9828337]
/lib64/libc.so.6(abort+0x148)[0x7f04e9829a28]
/usr/sbin/mysqld[0x78abb8]
/usr/sbin/mysqld(_ZN2ib5fatalD1Ev+0xfd)[0x11940fd]
/usr/sbin/mysqld[0x11d4018]
/usr/sbin/mysqld(_Z13buf_all_freedv+0x4c)[0x11d408c]
/usr/sbin/mysqld(_Z37logs_empty_and_mark_files_at_shutdownv+0x184f)[0x105a26f]
/usr/sbin/mysqld(_Z27innobase_shutdown_for_mysqlv+0x8df)[0x113ad8f]
/usr/sbin/mysqld[0xfedb65]
/usr/sbin/mysqld(_Z22ha_finalize_handlertonP13st_plugin_int+0x2c)[0x80710c]
/usr/sbin/mysqld[0xcf5d77]
/usr/sbin/mysqld(_Z15plugin_shutdownv+0x209)[0xcf8e69]
/usr/sbin/mysqld[0x7afe18]
/usr/sbin/mysqld(_Z11mysqld_mainiPPc+0x20f2)[0x7b6692]
/lib64/libc.so.6(__libc_start_main+0xf5)[0x7f04e9814505]
/usr/sbin/mysqld[0x7aa5e3]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2019-10-04T21:39:30.686484Z 0 [Warning] Could not increase number of max_open_files to more than 50000 (request: 65697)
2019-10-04T21:39:30.686722Z 0 [Warning] Changed limits: table_open_cache: 24919 (requested 32768)
2019-10-04T21:39:30.884907Z 0 [Note] libgovernor.so found
2019-10-04T21:39:30.884943Z 0 [Note] All governors functions found too
2019-10-04T21:39:30.885005Z 0 [Note] Governor connected
2019-10-04T21:39:30.885012Z 0 [Note] All governors lve functions found too
2019-10-05T00:39:30.885718+02:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-10-05T00:39:30.885734+02:00 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2019-10-05T00:39:30.887664+02:00 0 [Note] /usr/sbin/mysqld (mysqld 5.7.27-cll-lve) starting as process 699636 ...

FACT 3:
This is a new server. The problems started after 10 days of migrating sites from Plesk. It had around 170 accounts on it without an issue, until last Thursday when it started using the swap partition ( prnt.sc/pgbugn ) . Does this munin graph looks good? I'm trying to find out if something we migrated has an issue or if the issue existed since day 1.

FACT 4:
MySQL .err entries mentioned above started on September 23rd, which is actually the first time MySQL was restarted in the lifespan of this OS. The OS was set up on September 19th. Also we keep restarting MySQL every 5-6 hours in order not to use up all the server memory.

FACT 5:
MySQLTuner reports:

[OK] Maximum reached memory usage: 5.6G (17.87% of installed RAM) 
[OK] Maximum possible memory usage: 5.8G (18.63% of installed RAM)

... so there seems to be nothing wrong with our my.cnf file, which is as follows:

[mysqld]
performance-schema = 0
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/lib/mysql/host.eshoped.gr.err
pid-file = /var/run/mysqld/mysqld.pid
innodb_buffer_pool_size = 5G
max_allowed_packet=268435456
open_files_limit=50000
default-storage-engine = MyISAM
innodb_file_per_table = 1
sql_mode="NO_ENGINE_SUBSTITUTION"
log_timestamps = SYSTEM
max_user_connections=50

#tmpdir=/tmpsql


query_cache_size=512M
query_cache_type=1
query_cache_limit=512M
join_buffer_size=1M

table_open_cache=32K
performance_schema=1

Do you have any advice which might help?

 

[GOT]

Something in that is not linking up. You said mysqltuner reports max possible memory usage as 950M but your innodb buffer pool is set to 5g. That does not line up.

Definitely do not leave swap off that was not good advice.

 

[gvard]

Hello,

You are right, the output was before CloudLinux's recommendations. Here is the correct output, I'm putting it on the 1st post too:

[OK] Maximum reached memory usage: 5.6G (17.87% of installed RAM) 
[OK] Maximum possible memory usage: 5.8G (18.63% of installed RAM)

 

[InterServed]

Hello,

Had same problems on quite a few servers , MariaDB or MySQL. What did the trick for me was to change the memory allocator library. Also based on the log you posted i would make sure you have increased the openfiles limit. If you want to give it a try , here's a little help ,use at own risk.

echo "@mysql    soft    nofile    65697" >>/etc/security/limits.conf
echo "@mysql    hard    nofile    65697" >>/etc/security/limits.conf
yum install jemalloc -y
cp -a /etc/sysconfig/mysql /etc/sysconfig/mysql.bkp
echo -e "LD_PRELOAD=/usr/lib64/libjemalloc.so.1" > /etc/sysconfig/mysql
/scripts/restartsrv_mysql

Then verify with the next command if mysql processes are using the new jemalloc , if yes , then wait and see how the memory usage will be.

pmap `pidof mysqld` | grep jemalloc

It should return something like this:

00007f6455a20000    196K r-x-- libjemalloc.so.1
00007f6455a51000   2044K ----- libjemalloc.so.1
00007f6455c50000      8K r---- libjemalloc.so.1
00007f6455c52000      4K rw--- libjemalloc.so.1

Maybe it's a good idea to also perform a server reboot to apply the openfiles limits in /etc/security/limits.conf
Sharing also my view on how i would configure mysql based on your existing config:

[mysqld]

tmpdir="/dev/shm"
sql_mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=300
max_user_connections=50

log-error = /var/lib/mysql/host.eshoped.gr.err

open_files_limit=50000

slow-query-log=1
long-query-time=5
performance_schema=0
wait_timeout=300
interactive_timeout=1800

innodb_strict_mode=0
innodb_file_per_table=1
innodb_buffer_pool_size=5G
innodb_buffer_pool_instances=5
innodb_log_file_size=640M
innodb_log_buffer_size=16M
innodb_buffer_pool_dump_at_shutdown=0
innodb_buffer_pool_load_at_startup=0
innodb_flush_method            = O_DIRECT

tmp_table_size                 = 256M
max_heap_table_size            = 256M
max_allowed_packet             = 256M

query_cache_type               = 0
query_cache_size               = 0

key_buffer_size                = 128M
join_buffer_size               = 512K
sort_buffer_size               = 512K
read_buffer_size               = 256K
read_rnd_buffer_size           = 256K
table_open_cache               = 32000
table_definition_cache         = 32000

default-storage-engine=MyISAM

[mysqld_safe]
open-files-limit = 50000

Best regards

 

[cPanelLauren]

Hi @gvard

Beyond what's been suggested here, if you continue to have these issues I'd suggest opening a ticket with us, which you can do using the link in my signature. If you do open a ticket please update this thread with the Ticket ID so that we can follow it and provide an update here when the issue is resolved.

 

[gvard]

Hi Lauren,

A ticket to cPanel was opened prior opening this thread, however in the ticket I was asked to talk to a server administrator. A funny story, we opened the ticket because we are afraid that there is a possible memory leak which is relevant to MySQL binaries and not to something we can do to solve ourselves, since we can't install our own MySQL binaries to a CloudLinux server running cPanel.

cPanel ticket was 13503483, feel free to take a look at what had been said there.

 

[cPanelLauren]

Hi @gvard

Thanks for providing the ticket ID, I do see that a detailed investigation was done on the server, up to our Lvl 3 analysts, in fact the last response indicated some suggestions on configuration that might help you to move forward, I do not see that the Lvl 3 analyst deferred you to an administrator (though I do see it was suggested by another analyst)

For reference here's the last response from the analyst:

=======================================================================

Hello, Thank you for your patience. There are actually many kinds of memory leaks but they are rather rare in MySQL. Most suspected memory leaks end up being some run-away resource usage.

Some memory leaks might happen per connection and they will be gone when the connection is closed. Yet others correspond to global memory allocation and will result in increased memory allocation until the server is restarted.
I would suspect memory leak when you see memory usage growing which can’t be connected to any known resource use. For example, for global memory leaks, you would see memory usage continue to grow even after you close the connections and tables regularly. If memory is allocated and forgotten about, it should just be swapped out and swapped back in whenever it is needed again.

This can be determined with swap space. If you see swap space being used gradually and growing and there are “swap outs” but a lot less “swap ins”, chances are it is caused by a memory leak. Having swap disabled now makes that much more difficult to determine.

However, starting with MySQL 5.7 there is now memory allocation checks within the performance_schema.

Here's how that can be checked:

mysql> SELECT event_name, current_alloc, high_alloc FROM sys.memory_global_by_current_bytes WHERE current_count > 0;
+--------------------------------------------------------------------------------+---------------+-------------+
| event_name                                                                     | current_alloc | high_alloc  |
+--------------------------------------------------------------------------------+---------------+-------------+
| memory/performance_schema/table_handles                                        | 226.56 MiB    | 226.56 MiB  |
| memory/performance_schema/file_instances                                       | 82.50 MiB     | 82.50 MiB   |
| memory/performance_schema/table_shares                                         | 60.00 MiB     | 60.00 MiB   |
| memory/performance_schema/rwlock_instances                                     | 53.00 MiB     | 53.00 MiB   |
| memory/performance_schema/table_io_waits_summary_by_index_usage                | 41.25 MiB     | 41.25 MiB   |
| memory/performance_schema/events_statements_history_long                       | 13.66 MiB     | 13.66 MiB   |
| memory/performance_schema/events_statements_summary_by_digest.tokens           | 9.77 MiB      | 9.77 MiB    |
| memory/performance_schema/events_statements_history_long.tokens                | 9.77 MiB      | 9.77 MiB    |
| memory/performance_schema/events_statements_history_long.sqltext               | 9.77 MiB      | 9.77 MiB    |
| memory/performance_schema/events_statements_summary_by_user_by_event_name      | 8.85 MiB      | 8.85 MiB    |
| memory/performance_schema/events_statements_summary_by_account_by_event_name   | 8.85 MiB      | 8.85 MiB    |
| memory/performance_schema/events_statements_summary_by_thread_by_event_name    | 8.85 MiB      | 8.85 MiB    |
| memory/performance_schema/table_lock_waits_summary_by_table                    | 6.72 MiB      | 6.72 MiB    |
| memory/performance_schema/mutex_instances                                      | 6.12 MiB      | 6.12 MiB    |
| memory/performance_schema/memory_summary_by_account_by_event_name              | 5.62 MiB      | 5.62 MiB    |
| memory/performance_schema/memory_summary_by_thread_by_event_name               | 5.62 MiB      | 5.62 MiB    |
| memory/performance_schema/memory_summary_by_user_by_event_name                 | 5.62 MiB      | 5.62 MiB    |
| memory/performance_schema/events_statements_summary_by_digest                  | 4.88 MiB      | 4.88 MiB    |
| memory/performance_schema/events_statements_summary_by_host_by_event_name      | 4.42 MiB      | 4.42 MiB    |
| memory/performance_schema/events_statements_current                            | 3.50 MiB      | 3.50 MiB    |
| memory/performance_schema/events_statements_history                            | 3.50 MiB      | 3.50 MiB    |
| memory/performance_schema/events_waits_summary_by_user_by_event_name           | 3.39 MiB      | 3.39 MiB    |
| memory/performance_schema/events_waits_summary_by_thread_by_event_name         | 3.39 MiB      | 3.39 MiB    |
| memory/performance_schema/events_waits_summary_by_account_by_event_name        | 3.39 MiB      | 3.39 MiB    |
| memory/performance_schema/events_transactions_history_long                     | 3.28 MiB      | 3.28 MiB    |
| memory/performance_schema/memory_summary_by_host_by_event_name                 | 2.81 MiB      | 2.81 MiB    |
| memory/performance_schema/events_statements_current.sqltext                    | 2.50 MiB      | 2.50 MiB    |
| memory/performance_schema/events_statements_current.tokens                     | 2.50 MiB      | 2.50 MiB    |
| memory/performance_schema/events_statements_history.sqltext                    | 2.50 MiB      | 2.50 MiB    |
| memory/performance_schema/events_statements_history.tokens                     | 2.50 MiB      | 2.50 MiB    |
| memory/performance_schema/events_waits_summary_by_host_by_event_name           | 1.70 MiB      | 1.70 MiB    |
| memory/performance_schema/events_waits_history_long                            | 1.68 MiB      | 1.68 MiB    |
| memory/performance_schema/prepared_statements_instances                        | 1.62 MiB      | 1.62 MiB    |
| memory/performance_schema/events_stages_summary_by_account_by_event_name       | 1.17 MiB      | 1.17 MiB    |
| memory/performance_schema/events_stages_summary_by_thread_by_event_name        | 1.17 MiB      | 1.17 MiB    |
| memory/performance_schema/events_stages_summary_by_user_by_event_name          | 1.17 MiB      | 1.17 MiB    |
| memory/performance_schema/events_stages_history_long                           | 1015.62 KiB   | 1015.62 KiB |
| memory/performance_schema/threads                                              | 928.00 KiB    | 928.00 KiB  |
| memory/performance_schema/events_transactions_history                          | 860.00 KiB    | 860.00 KiB  |
| memory/performance_schema/events_stages_summary_by_host_by_event_name          | 600.00 KiB    | 600.00 KiB  |
| memory/performance_schema/events_statements_summary_by_program                 | 448.00 KiB    | 448.00 KiB  |
| memory/performance_schema/events_waits_history                                 | 440.00 KiB    | 440.00 KiB  |
| memory/performance_schema/events_stages_history                                | 260.00 KiB    | 260.00 KiB  |
| memory/performance_schema/file_handle                                          | 256.00 KiB    | 256.00 KiB  |
| memory/performance_schema/accounts                                             | 176.00 KiB    | 176.00 KiB  |
| memory/performance_schema/users                                                | 160.00 KiB    | 160.00 KiB  |
| memory/performance_schema/session_connect_attrs                                | 128.00 KiB    | 128.00 KiB  |
| memory/performance_schema/socket_instances                                     | 80.00 KiB     | 80.00 KiB   |
| memory/performance_schema/hosts                                                | 72.00 KiB     | 72.00 KiB   |
| memory/performance_schema/memory_class                                         | 60.00 KiB     | 60.00 KiB   |
| memory/performance_schema/setup_objects                                        | 56.00 KiB     | 56.00 KiB   |
| memory/performance_schema/scalable_buffer                                      | 54.22 KiB     | 54.22 KiB   |
| memory/performance_schema/mutex_class                                          | 52.50 KiB     | 52.50 KiB   |
| memory/performance_schema/setup_actors                                         | 40.00 KiB     | 40.00 KiB   |
| memory/performance_schema/stage_class                                          | 37.50 KiB     | 37.50 KiB   |
| memory/performance_schema/statement_class                                      | 36.94 KiB     | 36.94 KiB   |
| memory/performance_schema/events_statements_summary_global_by_event_name       | 35.40 KiB     | 35.40 KiB   |
| memory/performance_schema/file_class                                           | 25.00 KiB     | 25.00 KiB   |
| memory/performance_schema/memory_summary_global_by_event_name                  | 22.50 KiB     | 22.50 KiB   |
| memory/performance_schema/events_transactions_summary_by_account_by_event_name | 22.00 KiB     | 22.00 KiB   |
| memory/performance_schema/events_transactions_summary_by_user_by_event_name    | 22.00 KiB     | 22.00 KiB   |
| memory/performance_schema/events_transactions_summary_by_thread_by_event_name  | 22.00 KiB     | 22.00 KiB   |
| memory/performance_schema/cond_class                                           | 20.00 KiB     | 20.00 KiB   |
| memory/performance_schema/cond_instances                                       | 16.00 KiB     | 16.00 KiB   |
| memory/performance_schema/rwlock_class                                         | 12.50 KiB     | 12.50 KiB   |
| memory/performance_schema/events_transactions_summary_by_host_by_event_name    | 11.00 KiB     | 11.00 KiB   |
| memory/performance_schema/thread_class                                         | 9.38 KiB      | 9.38 KiB    |
| memory/performance_schema/events_stages_summary_global_by_event_name           | 4.69 KiB      | 4.69 KiB    |
| memory/performance_schema/socket_class                                         | 3.12 KiB      | 3.12 KiB    |
+--------------------------------------------------------------------------------+---------------+-------------+
69 rows in set (0.01 sec)

The largest chunk of RAM is usually the buffer pool but I don't see memory/innodb/buf_buf_pool here, which means it may not have been enabled.

You can do that with the following command: mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';
You'll then need to wait about 24 hours and run the above command again and see if it can help determine if there's a memory leak.

The following post may also be beneficial to you: https://www.percona.com/blog/2012/03/21/troubleshooting-mysql-memory-usage/

In the long run, this simply requires some MySQL tuning.

These are the variables that may need adjusting until the following formula yields 80% of installed RAM or less.
sort_buffer_size
read_buffer_size
read_rnd_buffer_size
join_buffer_size
max_connections

Formula: Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) X max_connections)

CloudLinux's MySQL Governor will only address CPU and I/O usage. It does not prevent against memory abuse, which again, is rare, but not impossible.

Please let me know if we can be of further assistance.

=======================================================================

Did you make any of the suggested changes here? I also noticed that the ticket auto closed - if you did make the changes and it didn't work for you, would you like for the ticket to be reopened?

 

[gvard]

We tried your suggestions with no results.

You seem to imply that the problem is settings related, but if you did the math yourself you would see that this is not the case. Even with the default my.cnf that cpanel ships with the memory leak remains. MySQL will full up all available ram unless it gets restarted in time. We even added another 32GB or RAM on the server, and MSQL usage kept growing beyond 40GB, see munin graph: Screenshot

The formula result is: 5.2GB
mysqltuner reports almost the same number (5.8GB)
And at the moment of this writing (with ~36h uptime) MySQL actually uses: ~40GB

So, this is not an issue of bad configuration in my.cnf.
Nor it is an issue with connections left open. The mysql threads (connections) are low as you can see here Screenshot
And in SHOW PROCESSLIST there are no connections running for over a few seconds at a time.
Also issuing a FLUSH TABLES; command to close all open tables did not change anything in memory usage.

Regarding the swap, when we had it active, it would gradually fill up completely in a matter of few hours. Restaring mysql would empty the swap, until it became full again.
Since swaping caused the server to become unresponsive we disabled it. CloudLinux support also suggested the same. But to answer your question, yes, it was almost only swap-outs.

So, all evindence points to a memory leak, I would appreciate a little more help here.

 

[cPanelLauren]

I see you've reopened the ticket and our L3 analysts are continuing to work on this, investigating this issue up to the level you're suggesting isn't something that would be feasible in the forums, I am glad to see you are getting assistance and I'll follow along the ticket to update the thread here with the outcome.

 

[gvard]

Hello,

We ended replacing MySQL 5.7 with MariaDB 10.3. On you can see on the red rectangular the memory allocation with MySQL 5.7 (which went to the roof even though we added 32GB more RAM) and then you can see memory allocation with MariaDB.

It's been 2,5 days and MariaDB is under 10GB or RAM, as it's supposed to be. Unfortunately I don't know what rare condition caused this memory leak, but it was a memory leak.

 

[ghazanfar]

i'm experiencing issue on gmetrix while accessng the [Moderator Notice: This URL was removed. Please avoid including third-party links in the thread]
An error occurred fetching the page: HTTPS error: SSL connect attempt failed error:14077438:SSL routines:SSL23_GET_SERVER_HELLO:tlsv1 alert internal error
There may be a connectivity issue between your server and the GTmetrix test server. Please login to try testing from another test location or try again later.
any suggestion?

 

[SamuelM]

Hello @ghazanfar,

The issue you are reporting seems unrelated to the MySQL memory leak which was the original topic of this thread. I would encourage you to submit a new forum thread regarding the SSL error you receive when using the GTMetrix site, or submit a support ticket using the link in my signature.

Best regards

 

[eimix]

Hello,

We ended replacing MySQL 5.7 with MariaDB 10.3. On http://prntscr.com/pn6luu you can see on the red rectangular the memory allocation with MySQL 5.7 (which went to the roof even though we added 32GB more RAM) and then you can see memory allocation with MariaDB.

It's been 2,5 days and MariaDB is under 10GB or RAM, as it's supposed to be. Unfortunately I don't know what rare condition caused this memory leak, but it was a memory leak.

I have similar problem on few projects - RAM usage goes beond expected limits.
Moved from MySQL versions 5.6 -> 5.7 -> 8.0
Got same memory issues, and swapping finaly

Noticed this behaviour with databases that have MyISAM tables (most of them with before/after update triggers and many Procedures/Functions called by those triggers)

Currently i suspect that it is related to
config
table_open_cache = 16K
table_open_cache_instances = 16

and after running server some time i get same table opened multiple times:
select * from performance_schema.table_handles;

During tests reduced:
table_open_cache = 1024
table_open_cache_instances = 1

Memory usage stabilized, but slowed down also :/

What config parameters do you use with MariaDB?

 

[gvard]

Hello,

For table_* variables, we have only set the following:

table_open_cache = 32K
table_definition_cache = 64K

 

[eimix]

Tried to move to MariaDB - memory usage came down, but not much as expected.
Finaly found that both MySQL and MariaDB can use high levels of memory for stored procedures/functions and trigger, and could not find way to controll it.

Created DB structrue test scripts, reported to
MySQL - answer: "expected behaviour"
MariaDB - still waiting.

[MDEV-21447] Uncontrollable memory allocation with TRIGGERS, PROCEDURES and FUNCTIONS - Jira

jira.mariadb.org

first script uses 2Gb memory

https://jira.mariadb.org/secure/attachment/50229/dSTAR16_MariaDB.sql

second script use UNKNOWN ammount of memory

https://jira.mariadb.org/secure/attachment/50228/dSTAR64_MariaDB.sql

Easily takes down any shared hosting DB server
tested on 4 random different hosting servers - all DB servers restarted stalled.

If more shared hosting servers encountered this DB structure - maybe problem would be solved faster? :D


every time somebody visits https://sqldstar.example.com/db.php
this DB uptime - decreases: 000webhost status

^^^^^^^^^^^^^
2 rows above edited by moderators, could not show deployed DB :/