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

Connect Engine - Json file to Table leak

    XMLWordPrintable

Details

    Description

      Using Connect storage Engine ( Json type )

      0. install ha_connect.so

      1. Table Create - using Connect Engine ( example)

      CREATE TABLE `JSON_DATA_TYPE1` (
        `WRITEDAY` char(8) DEFAULT NULL `field_format`='WRITEDAY',
        `WRITETIME` char(6) DEFAULT NULL `field_format`='WRITETIME',
        `TYPE` varchar(20) DEFAULT NULL `field_format`='TYPE',
        `TYPE2` char(1) DEFAULT NULL `field_format`='TYPE2',
        `DATA1` varchar(20) DEFAULT NULL `field_format`='DATA1',
        `DATA2` int(10) unsigned zerofill NOT NULL `field_format`='DATA3',
        `DATA3` int(10) unsigned zerofill NOT NULL `field_format`='DATA3'
      ) ENGINE=CONNECT DEFAULT CHARSET=utf8 `table_type`=JSON `File_name`='/data_json/DATA.json'
      

      2. Json Data Sample

      [
      {"WRITEDAY":"20170213","WRITETIME":"001935","TYPE":"TYPEDATA", "TYPE2":"BB", "DATA1":"D1","DATA2":55,"DATA3":1},
      {"WRITEDAY":"20170213","WRITETIME":"001935","TYPE":"TYPEDATA", "TYPE2":"BB", "DATA1":"D2","DATA2":55,"DATA3":2},
      {"WRITEDAY":"20170213","WRITETIME":"001935","TYPE":"TYPEDATA", "TYPE2":"BB", "DATA1":"D3","DATA2":55,"DATA3":3},
      {"WRITEDAY":"20170213","WRITETIME":"001935","TYPE":"TYPEDATA", "TYPE2":"BB", "DATA1":"D4","DATA2":55,"DATA3":4},
      {"WRITEDAY":"20170213","WRITETIME":"001935","TYPE":"TYPEDATA", "TYPE2":"BB", "DATA1":"D5","DATA2":55,"DATA3":5},
      {"WRITEDAY":"20170213","WRITETIME":"001935","TYPE":"TYPEDATA", "TYPE2":"BB", "DATA1":"D6","DATA2":55,"DATA3":6},
      {"WRITEDAY":"20170213","WRITETIME":"001935","TYPE":"TYPEDATA", "TYPE2":"BB", "DATA1":"D7","DATA2":55,"DATA3":7}
      ]
      

      3. Create a procedure for table data copying.

      CREATE PROCEDURE `PROCEDURE_DATA_COPY`(
      	IN `TARGETTB` VARCHAR(20),
      	IN `SOURCETB` VARCHAR(20)
      )
      LANGUAGE SQL
      NOT DETERMINISTIC
      CONTAINS SQL
      SQL SECURITY DEFINER
      COMMENT ''
      BEGIN
         SET @istmt := concat("INSERT INTO ", TARGETTB, "( WRITEDAY, WRITETIME, TYPE, TYPE2, DATA1, DATA2, DATA3 ) select WRITEDAY, WRITETIME, TYPE, TYPE2, DATA1, DATA2, DATA3 from ", SOURCETB);
         PREPARE stmt FROM @istmt;
         execute stmt;
         commit;
         DEALLOCATE PREPARE stmt;
      END
      

      3. Every second, files are created in json format on a specific path
      ex) DATA_201702121830.json ( DATA_YYYYMMDDHH24MISS.json, 10k ~ 1MBbyte size )

      4. Copy DATA_201702121830.json file to /data_json/DATA.json file in a shell script.

      5. Execute the procedure a mysql command in a shell script.
      load_data.sh
      copy jsonfile ... ( step 4 )
      mysql -uMARIA_USERID -pMARIA_USERPW MARIA_DBNAME -e "call PROCEDURE_DATA_COPY('TARGET_TABLE', 'JSON_DATA_TYPE1' );\q"
      exit

      6. Repeat 3-4-5 steps indefinitely.

      problem :
      mysqld process uses all memory resources on the system.
      I do not know which process is causing memory leaks.

      attach valgrid log file ( command )
      [mysql@testserver tmp]$ valgrind --tool=massif --massif-out-file=/tmp/mysql-heap-profile.out /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/sw/testsvr/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/sw/testsvr/mysql/errlog/error.log --open-files-limit=8192 --pid-file=/sw/testsvr/mysql/data/testsvr.pid --socket=/tmp/mysql.sock --port=3306

          GB
      9.029^                                                                     #  
           |                                                              :@@:::@#::
           |                                                       @:@:@@::@@:::@#::
           |                                                      :@:@:@@::@@:::@#::
           |                                             :::::@::::@:@:@@::@@:::@#::
           |                                    @@::::::::::::@::::@:@:@@::@@:::@#::
           |                                   @@ ::: :: :::::@::::@:@:@@::@@:::@#::
           |                              :@@::@@ ::: :: :::::@::::@:@:@@::@@:::@#::
           |                           @@::@ ::@@ ::: :: :::::@::::@:@:@@::@@:::@#::
           |                       ::::@ ::@ ::@@ ::: :: :::::@::::@:@:@@::@@:::@#::
           |                    @:@: ::@ ::@ ::@@ ::: :: :::::@::::@:@:@@::@@:::@#::
           |                 @@:@:@: ::@ ::@ ::@@ ::: :: :::::@::::@:@:@@::@@:::@#::
           |             @@@:@ :@:@: ::@ ::@ ::@@ ::: :: :::::@::::@:@:@@::@@:::@#::
           |            @@ @:@ :@:@: ::@ ::@ ::@@ ::: :: :::::@::::@:@:@@::@@:::@#::
           |        ::::@@ @:@ :@:@: ::@ ::@ ::@@ ::: :: :::::@::::@:@:@@::@@:::@#::
           |     :::::: @@ @:@ :@:@: ::@ ::@ ::@@ ::: :: :::::@::::@:@:@@::@@:::@#::
           |     :::::: @@ @:@ :@:@: ::@ ::@ ::@@ ::: :: :::::@::::@:@:@@::@@:::@#::
           |  ::::::::: @@ @:@ :@:@: ::@ ::@ ::@@ ::: :: :::::@::::@:@:@@::@@:::@#::
           | ::: :::::: @@ @:@ :@:@: ::@ ::@ ::@@ ::: :: :::::@::::@:@:@@::@@:::@#::
           | ::: :::::: @@ @:@ :@:@: ::@ ::@ ::@@ ::: :: :::::@::::@:@:@@::@@:::@#::
         0 +----------------------------------------------------------------------->Gi
           0                                                                   108.9
       
      Number of snapshots: 73
       Detailed snapshots: [10, 12, 13, 15, 17, 19, 23, 26, 29, 30, 41, 46, 49, 51, 54, 58, 60, 67, 69 (peak)]
      

      Attachments

        1. my.cnf
          21 kB
        2. mysqld_process_memory.png
          mysqld_process_memory.png
          16 kB
        3. mysql-heap-profile.out
          130 kB

        Activity

          People

            bertrandop Olivier Bertrand
            playok SeonghanJeong
            Votes:
            1 Vote for this issue
            Watchers:
            2 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.