LOAD DATA CONCURRENT LOCAL INFILE from another machine
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.
mysql-5.7
asked 6 mins ago
udhayan dharmalingamudhayan dharmalingam
11
add a comment |
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.
mysql-5.7
asked 6 mins ago
udhayan dharmalingamudhayan dharmalingam
11
add a comment |
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.
mysql-5.7
asked 6 mins ago
udhayan dharmalingamudhayan dharmalingam
11
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.
mysql-5.7
mysql-5.7
asked 6 mins ago
udhayan dharmalingamudhayan dharmalingam
11
asked 6 mins ago
udhayan dharmalingamudhayan dharmalingam
11
edited 54 secs ago
udhayan dharmalingam
asked 6 mins ago
udhayan dharmalingamudhayan dharmalingam
11
asked 6 mins ago
udhayan dharmalingamudhayan dharmalingam
11
asked 6 mins ago
udhayan dharmalingamudhayan dharmalingam
11
11
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f229703%2fload-data-concurrent-local-infile-from-another-machine%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f229703%2fload-data-concurrent-local-infile-from-another-machine%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
