How can I optimize the performance of mysql vm in a nginx load balancer group?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I built a mysql HA group with 3 ubuntu vms. One is load balancer and the other two are mysql vms. Each mysql vm is with 8 mysql instances which corresponding with 8 ports and there are 8 ports on load balancer which corresponding with the ones in mysql vms. The two mysql vms are master-master replication to each other.
But when I tested the performance of mysql with sysbench, the performance is quite poor in load balancer while it is ok if I tested it in one of the mysql vm.
I used the command
sysbench --test=oltp --mysql-host=192.168.1.11 --mysql-port=3307 --mysql-user=xxx --mysql-password=xxx --mysql-db=test1 --num-threads=32 --max-time=1800 --max-requests=0 --oltp-table-size=10000000 run>> /var/tmp/mysql3307.txt
in load balancer and get the result
OLTP test statistics:
queries performed:
read: 3522974
write: 1258168
other: 503270
total: 5284412
transactions: 251629 (139.78 per sec.)
deadlocks: 12 (0.01 per sec.)
read/write requests: 4781142 (2655.95 per sec.)
other operations: 503270 (279.57 per sec.)
Test execution summary:
total time: 1800.1653s
total number of events: 251629
total time taken by event execution: 57596.9970
per-request statistics:
min: 42.53ms
avg: 228.90ms
max: 3086.55ms
approx. 95 percentile: 407.86ms
Threads fairness:
events (avg/stddev): 7863.4062/381.89
execution time (avg/stddev): 1799.9062/0.05
But when I tested in one mysql vm
sysbench --test=oltp --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=xxx --mysql-password=xxx --mysql-db=test1 --num-threads=32 --max-time=1800 --max-requests=0 --oltp-table-size=10000000 run>> /var/tmp/mysql3307.txt
the result is
OLTP test statistics:
queries performed:
read: 140070
write: 50010
other: 20005
total: 210085
transactions: 10000 (317.15 per sec.)
deadlocks: 5 (0.16 per sec.)
read/write requests: 190080 (6028.38 per sec.)
other operations: 20005 (634.46 per sec.)
Test execution summary:
total time: 31.5308s
total number of events: 10000
total time taken by event execution: 1007.3012
per-request statistics:
min: 13.73ms
avg: 100.73ms
max: 589.49ms
approx. 95 percentile: 184.95ms
Threads fairness:
events (avg/stddev): 312.5000/6.02
execution time (avg/stddev): 31.4782/0.04
The load-balance configuration of the nginx in load balancer is
stream {
server {
listen 3307;
proxy_pass db3307;
}
upstream db3307 {
server 192.168.1.11:3307 weight=1;
server 192.168.1.12:3307 weight=1;
}
server {
listen 3308;
proxy_pass db3308;
}
upstream db3308 {
server 192.168.1.11:3308 weight=1;
server 192.168.1.12:3308 weight=1;
}
//... to port 3314
}
I think the cause of the poor performance is the nginx load balance, but I don't know how to optimize it. Is there any one can help me? Thank you.
ubuntu nginx load-balancing high-availability multiple-instances
edited Jan 4 at 3:01
Iman Marashi
2,4652232
asked Jan 4 at 2:38
InthefeatherInthefeather
63
add a comment |
I built a mysql HA group with 3 ubuntu vms. One is load balancer and the other two are mysql vms. Each mysql vm is with 8 mysql instances which corresponding with 8 ports and there are 8 ports on load balancer which corresponding with the ones in mysql vms. The two mysql vms are master-master replication to each other.
But when I tested the performance of mysql with sysbench, the performance is quite poor in load balancer while it is ok if I tested it in one of the mysql vm.
I used the command
sysbench --test=oltp --mysql-host=192.168.1.11 --mysql-port=3307 --mysql-user=xxx --mysql-password=xxx --mysql-db=test1 --num-threads=32 --max-time=1800 --max-requests=0 --oltp-table-size=10000000 run>> /var/tmp/mysql3307.txt
in load balancer and get the result
OLTP test statistics:
queries performed:
read: 3522974
write: 1258168
other: 503270
total: 5284412
transactions: 251629 (139.78 per sec.)
deadlocks: 12 (0.01 per sec.)
read/write requests: 4781142 (2655.95 per sec.)
other operations: 503270 (279.57 per sec.)
Test execution summary:
total time: 1800.1653s
total number of events: 251629
total time taken by event execution: 57596.9970
per-request statistics:
min: 42.53ms
avg: 228.90ms
max: 3086.55ms
approx. 95 percentile: 407.86ms
Threads fairness:
events (avg/stddev): 7863.4062/381.89
execution time (avg/stddev): 1799.9062/0.05
But when I tested in one mysql vm
sysbench --test=oltp --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=xxx --mysql-password=xxx --mysql-db=test1 --num-threads=32 --max-time=1800 --max-requests=0 --oltp-table-size=10000000 run>> /var/tmp/mysql3307.txt
the result is
OLTP test statistics:
queries performed:
read: 140070
write: 50010
other: 20005
total: 210085
transactions: 10000 (317.15 per sec.)
deadlocks: 5 (0.16 per sec.)
read/write requests: 190080 (6028.38 per sec.)
other operations: 20005 (634.46 per sec.)
Test execution summary:
total time: 31.5308s
total number of events: 10000
total time taken by event execution: 1007.3012
per-request statistics:
min: 13.73ms
avg: 100.73ms
max: 589.49ms
approx. 95 percentile: 184.95ms
Threads fairness:
events (avg/stddev): 312.5000/6.02
execution time (avg/stddev): 31.4782/0.04
The load-balance configuration of the nginx in load balancer is
stream {
server {
listen 3307;
proxy_pass db3307;
}
upstream db3307 {
server 192.168.1.11:3307 weight=1;
server 192.168.1.12:3307 weight=1;
}
server {
listen 3308;
proxy_pass db3308;
}
upstream db3308 {
server 192.168.1.11:3308 weight=1;
server 192.168.1.12:3308 weight=1;
}
//... to port 3314
}
I think the cause of the poor performance is the nginx load balance, but I don't know how to optimize it. Is there any one can help me? Thank you.
ubuntu nginx load-balancing high-availability multiple-instances
edited Jan 4 at 3:01
Iman Marashi
2,4652232
asked Jan 4 at 2:38
InthefeatherInthefeather
63
add a comment |
I built a mysql HA group with 3 ubuntu vms. One is load balancer and the other two are mysql vms. Each mysql vm is with 8 mysql instances which corresponding with 8 ports and there are 8 ports on load balancer which corresponding with the ones in mysql vms. The two mysql vms are master-master replication to each other.
But when I tested the performance of mysql with sysbench, the performance is quite poor in load balancer while it is ok if I tested it in one of the mysql vm.
I used the command
sysbench --test=oltp --mysql-host=192.168.1.11 --mysql-port=3307 --mysql-user=xxx --mysql-password=xxx --mysql-db=test1 --num-threads=32 --max-time=1800 --max-requests=0 --oltp-table-size=10000000 run>> /var/tmp/mysql3307.txt
in load balancer and get the result
OLTP test statistics:
queries performed:
read: 3522974
write: 1258168
other: 503270
total: 5284412
transactions: 251629 (139.78 per sec.)
deadlocks: 12 (0.01 per sec.)
read/write requests: 4781142 (2655.95 per sec.)
other operations: 503270 (279.57 per sec.)
Test execution summary:
total time: 1800.1653s
total number of events: 251629
total time taken by event execution: 57596.9970
per-request statistics:
min: 42.53ms
avg: 228.90ms
max: 3086.55ms
approx. 95 percentile: 407.86ms
Threads fairness:
events (avg/stddev): 7863.4062/381.89
execution time (avg/stddev): 1799.9062/0.05
But when I tested in one mysql vm
sysbench --test=oltp --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=xxx --mysql-password=xxx --mysql-db=test1 --num-threads=32 --max-time=1800 --max-requests=0 --oltp-table-size=10000000 run>> /var/tmp/mysql3307.txt
the result is
OLTP test statistics:
queries performed:
read: 140070
write: 50010
other: 20005
total: 210085
transactions: 10000 (317.15 per sec.)
deadlocks: 5 (0.16 per sec.)
read/write requests: 190080 (6028.38 per sec.)
other operations: 20005 (634.46 per sec.)
Test execution summary:
total time: 31.5308s
total number of events: 10000
total time taken by event execution: 1007.3012
per-request statistics:
min: 13.73ms
avg: 100.73ms
max: 589.49ms
approx. 95 percentile: 184.95ms
Threads fairness:
events (avg/stddev): 312.5000/6.02
execution time (avg/stddev): 31.4782/0.04
The load-balance configuration of the nginx in load balancer is
stream {
server {
listen 3307;
proxy_pass db3307;
}
upstream db3307 {
server 192.168.1.11:3307 weight=1;
server 192.168.1.12:3307 weight=1;
}
server {
listen 3308;
proxy_pass db3308;
}
upstream db3308 {
server 192.168.1.11:3308 weight=1;
server 192.168.1.12:3308 weight=1;
}
//... to port 3314
}
I think the cause of the poor performance is the nginx load balance, but I don't know how to optimize it. Is there any one can help me? Thank you.
ubuntu nginx load-balancing high-availability multiple-instances
edited Jan 4 at 3:01
Iman Marashi
2,4652232
asked Jan 4 at 2:38
InthefeatherInthefeather
63
I built a mysql HA group with 3 ubuntu vms. One is load balancer and the other two are mysql vms. Each mysql vm is with 8 mysql instances which corresponding with 8 ports and there are 8 ports on load balancer which corresponding with the ones in mysql vms. The two mysql vms are master-master replication to each other.
But when I tested the performance of mysql with sysbench, the performance is quite poor in load balancer while it is ok if I tested it in one of the mysql vm.
I used the command
sysbench --test=oltp --mysql-host=192.168.1.11 --mysql-port=3307 --mysql-user=xxx --mysql-password=xxx --mysql-db=test1 --num-threads=32 --max-time=1800 --max-requests=0 --oltp-table-size=10000000 run>> /var/tmp/mysql3307.txt
in load balancer and get the result
OLTP test statistics:
queries performed:
read: 3522974
write: 1258168
other: 503270
total: 5284412
transactions: 251629 (139.78 per sec.)
deadlocks: 12 (0.01 per sec.)
read/write requests: 4781142 (2655.95 per sec.)
other operations: 503270 (279.57 per sec.)
Test execution summary:
total time: 1800.1653s
total number of events: 251629
total time taken by event execution: 57596.9970
per-request statistics:
min: 42.53ms
avg: 228.90ms
max: 3086.55ms
approx. 95 percentile: 407.86ms
Threads fairness:
events (avg/stddev): 7863.4062/381.89
execution time (avg/stddev): 1799.9062/0.05
But when I tested in one mysql vm
sysbench --test=oltp --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=xxx --mysql-password=xxx --mysql-db=test1 --num-threads=32 --max-time=1800 --max-requests=0 --oltp-table-size=10000000 run>> /var/tmp/mysql3307.txt
the result is
OLTP test statistics:
queries performed:
read: 140070
write: 50010
other: 20005
total: 210085
transactions: 10000 (317.15 per sec.)
deadlocks: 5 (0.16 per sec.)
read/write requests: 190080 (6028.38 per sec.)
other operations: 20005 (634.46 per sec.)
Test execution summary:
total time: 31.5308s
total number of events: 10000
total time taken by event execution: 1007.3012
per-request statistics:
min: 13.73ms
avg: 100.73ms
max: 589.49ms
approx. 95 percentile: 184.95ms
Threads fairness:
events (avg/stddev): 312.5000/6.02
execution time (avg/stddev): 31.4782/0.04
The load-balance configuration of the nginx in load balancer is
stream {
server {
listen 3307;
proxy_pass db3307;
}
upstream db3307 {
server 192.168.1.11:3307 weight=1;
server 192.168.1.12:3307 weight=1;
}
server {
listen 3308;
proxy_pass db3308;
}
upstream db3308 {
server 192.168.1.11:3308 weight=1;
server 192.168.1.12:3308 weight=1;
}
//... to port 3314
}
I think the cause of the poor performance is the nginx load balance, but I don't know how to optimize it. Is there any one can help me? Thank you.
ubuntu nginx load-balancing high-availability multiple-instances
ubuntu nginx load-balancing high-availability multiple-instances
edited Jan 4 at 3:01
Iman Marashi
2,4652232
asked Jan 4 at 2:38
InthefeatherInthefeather
63
edited Jan 4 at 3:01
Iman Marashi
2,4652232
asked Jan 4 at 2:38
InthefeatherInthefeather
63
edited Jan 4 at 3:01
Iman Marashi
2,4652232
edited Jan 4 at 3:01
Iman Marashi
2,4652232
edited Jan 4 at 3:01
Iman Marashi
2,4652232
2,4652232
asked Jan 4 at 2:38
InthefeatherInthefeather
63
asked Jan 4 at 2:38
InthefeatherInthefeather
63
asked Jan 4 at 2:38
InthefeatherInthefeather
63
63
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f54032479%2fhow-can-i-optimize-the-performance-of-mysql-vm-in-a-nginx-load-balancer-group%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 Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f54032479%2fhow-can-i-optimize-the-performance-of-mysql-vm-in-a-nginx-load-balancer-group%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
