0

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 ''

Two questions

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_sysbench_workload

pt-diskstats output during my normal workload while only Load data is running shows very less IO activity.

pt-diskstats_usual_workload

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.