Skip to main content

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?

Comments

14 comments

Date Votes
  • 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.
    0
  • 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)
    0
  • 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
    0
  • 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.
    0
  • 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.
    0
  • 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:
    0
  • 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.
    0
  • 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.
    0
  • 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.
    0
  • 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?
    0
  • 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
    0
  • eimix
    Hello, We ended replacing MySQL 5.7 with MariaDB 10.3. On
    0
  • gvard
    Hello, For table_* variables, we have only set the following: table_open_cache = 32K table_definition_cache = 64K
    0
  • 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. second script use UNKNOWN ammount of memory this DB uptime - decreases: 000webhost status ^^^^^^^^^^^^^ 2 rows above edited by moderators, could not show deployed DB :/
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post