|
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)]
|
|
my.cnf
mysqld_process_memory.png