I am managing a write heavy Mysql DB and the bulk load is the predominant workload that is happening in my system.
Monitoring Logs are extracted as a raw file(each file 100MB) in the application server and below LOAD data command is loading to different tables and all are partitioned tables
LOAD DATA CONCURRENT LOCAL INFILE 'data' INTO TABLE tablename FIELDS TERMINATED BY ',' LINES TERMINATED BY ''
1) How does bulk load from remote server works, because i don't see the raw files being copied to my DB server to my mysql data directory and get loaded from local as like below statement
LOAD DATA LOCAL INFILE 'path/file.csv' INTO TABLE table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n
2)I suspect the above bulk load is not utilizing the capability of my storage system.
I loaded 46GB of data into same mysql instance with sysbench which completes in 70 mins which translates to 672.914 MB per minute
sysbench oltp_write_only.lua --threads=4 --db-driver=mysql --mysql-host=127.0.0.1 --mysql-user=sbtest --mysql-password=Pass --mysql-port=3306 --tables=20 --table-size=10000000 prepare
pt-diskstats at the same time when sysbench was loading data shows much more activity with available disks
pt-diskstats output during my normal workload while only Load data is running shows very less IO activity.
At its peak,per minute my application generates 5* 100 MB files. So even if the Load data workload is able to utilize available IO what sysbench workload is able to (672 MB per minute), i wont see queue pile up in my application.
When load data happens from remote server, anything that needs to be done on network side to speed up the loading
Thanks in advance.