Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21447

Uncontrollable memory allocation with TRIGGERS, PROCEDURES and FUNCTIONS

Details

    Description

      I have project with 8 databases, each 500Mb.
      Each has ~800 tables, some of them with triggers.
      Use ~100 stored procedures and functions.

      Notices excessibe memory usage 128GB is not the limit.
      Then HW limit is reached - OOM kills MariaDB

      Prepared simple DB structure examples:
      dSTAR16_MariaDB.sql (with 16 tables) - uses 2GB
      dSTAR64_MariaDB.sql (with 64 tables) - uses 32GB
      It is not linear (8 tables would be ~250Mb)

      In such DB simple (imposible query) is enougth to massivie memory usage:

      UPDATE jj001 SET vj001 = 0 WHERE 'TEJA' = 'FEJA';
      

      Could not find server config variables that could limit such memory usage.

      Tried script on hosting servers with shared MySQL/MariaDB service - all of them failed using this kind of script.

      256 tables script (expected 512GB memory usage) would kill almost any server.

      Attachments

        1. Capture.JPG
          Capture.JPG
          162 kB
        2. dSTAR16_MariaDB.sql
          85 kB
        3. dSTAR64_MariaDB.sql
          951 kB

        Issue Links

          Activity

            Statistic of usage is following:
            5.5:
            1.5GB-1.6GB and 1064-1024 files
            10.1:
            2.8GB-2.9GB and 2368-2304 files

            so 1.1 has even less ration memory per file, the probem is in many files opened by 10.1

            sanja Oleksandr Byelkin added a comment - Statistic of usage is following: 5.5: 1.5GB-1.6GB and 1064-1024 files 10.1: 2.8GB-2.9GB and 2368-2304 files so 1.1 has even less ration memory per file, the probem is in many files opened by 10.1

            according to internal statistics server uses for execution the UPDATE:

            5.5
            Opened_files	2321-352 = 1969
            Opened_table_definitions	16
            Opened_tables	1057
            Opened_views	0
            10.1
            Opened_files	2429-347 = 2082
            Opened_plugin_libraries	0
            Opened_table_definitions	16
            Opened_tables	1025
            Opened_views	0
            

            10.1 statistics is dubious because it needs higher limit by opened files in OS then it reported in statistics

            sanja Oleksandr Byelkin added a comment - according to internal statistics server uses for execution the UPDATE: 5.5 Opened_files 2321-352 = 1969 Opened_table_definitions 16 Opened_tables 1057 Opened_views 0 10.1 Opened_files 2429-347 = 2082 Opened_plugin_libraries 0 Opened_table_definitions 16 Opened_tables 1025 Opened_views 0 10.1 statistics is dubious because it needs higher limit by opened files in OS then it reported in statistics
            eimix Eimantas added a comment -

            Expected this to be related to complexity/size of triggers, procedures and functions.
            Tested adding large amount "dummy" code to them - memory consumtion does not increase (significantly).

            eimix Eimantas added a comment - Expected this to be related to complexity/size of triggers, procedures and functions. Tested adding large amount "dummy" code to them - memory consumtion does not increase (significantly).

            almost 2 times more files opened and incorrect statistics by opened files it is problems caused by WSREP.

            sanja Oleksandr Byelkin added a comment - almost 2 times more files opened and incorrect statistics by opened files it is problems caused by WSREP.
            JIraAutomate JiraAutomate added a comment -

            Automated message:
            ----------------------------
            Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

            JIraAutomate JiraAutomate added a comment - Automated message: ---------------------------- Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

            People

              shulga Dmitry Shulga
              eimix Eimantas
              Votes:
              4 Vote for this issue
              Watchers:
              14 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.