Hi,
After I create a memory partition table, I excute the below command to load data into a memsql table:
memsql> load data infile ‘/data1/boray_poc_data/data/pur_tcn_datareport_daily/correct_r_PUR_TCN_DATAREPORT_DAILY.csv’ into table PUR_TCN_DATAREPORT_DAILY columns terminated by ‘,’;
Query OK, 212009169 rows affected (33 min 48.00 sec)
I find that during load data procedure, the cache of memory-usage on master node increases very fast. Almost all memory is used as cache.
In this way, when excuting a complicated SQL query which contains multi-table “left join”, memsql will return a “Not enough memory available” error.
So are there any methods to decrease master node’s memory cache, or in another way, make a balance between master node and leaf node?
The table structure is as below:
create table PUR_TCN_DATAREPORT_DAILY
(
ticket_dt DATE,
flt_dpt_dt DATE,
flt_nbr VARCHAR(10),
carrier_cd VARCHAR(2),
orig_airport_cd VARCHAR(3),
dest_airport_cd VARCHAR(3),
nation_flag VARCHAR(10),
sub_class_cd VARCHAR(1),
class_cd VARCHAR(1),
ticket_agent_nbr VARCHAR(10),
agent_name VARCHAR(100),
agent_area_name VARCHAR(30),
agent_branch_name VARCHAR(60),
agent_branch_name_airport VARCHAR(60),
manager_name VARCHAR(60),
channel_name VARCHAR(30),
pax_qty INTEGER,
pax_income DECIMAL,
agent_commi_amt DECIMAL,
yq_amt DECIMAL,
z_commi_amt DECIMAL,
ka_qty INTEGER,
ka_amt DECIMAL,
ka_agent_commi_amt DECIMAL,
ka_z_commi_amt DECIMAL,
ka_yq_amt DECIMAL,
refund_pax_qty INTEGER,
refund_pax_income DECIMAL,
refund_commi_amt DECIMAL,
refund_yq_amt DECIMAL,
refund_no_pax_qty INTEGER,
refund_no_pax_income DECIMAL,
refund_no_commi_amt DECIMAL,
refund_no_yq_amt DECIMAL,
data_source VARCHAR(100),
src_sysname VARCHAR(10),
src_table VARCHAR(30),
etl_job VARCHAR(50),
etl_tx_dt DATE,
etl_first_dt DATE,
etl_proc_dt DATE,
key(TICKET_AGENT_NBR,SUB_CLASS_CD,PAX_INCOME,DATA_SOURCE,FLT_DPT_DT,FLT_NBR,CARRIER_CD,ORIG_AIRPORT_CD,DEST_AIRPORT_CD,TICKET_DT,CLASS_CD)
);