High traffic MYSQL serverLower Maximum Possible Memory UsageMySQL keeps crashing OS server.. Please help adjust my.ini!How do I allow mysqld to use more than 24.9% of my cpu?my battle against web-server monitoring - slow serverImprove server performance by configuring MySQLMySQL becomes unresponsive a few times per hourmax_used_connections in mysql keeps on increasing on ubuntu 11.10Mysql server high trafic makes websites really slow or unable to loadmysqld_safe Can't log to error log and syslog at the same time. Remove all --log-error configuration options for --syslog to take effectMySQL Tuning — High Memory Usage
Has there been any indication at all that further negotiation between the UK and EU is possible?
What is "industrial ethernet"?
"How can you guarantee that you won't change/quit job after just couple of months?" How to respond?
What did River say when she woke from her proto-comatose state?
Hot coffee brewing solutions for deep woods camping
Why do even high-end cameras often still include normal (non-cross-type) AF sensors?
When to remove insignificant variables?
How is hair tissue mineral analysis performed?
How would modern naval warfare have to have developed differently for battleships to still be relevant in the 21st century?
Is there a way, while dragging, to "snap" to the nearest guide?
What size of powerbank will I need to power a phone and DSLR for 2 weeks?
Should developer taking test phones home or put in office?
Non-flat partitions of a set
Is it damaging to turn off a small fridge for two days every week?
Count All Possible Unique Combinations of Letters in a Word
Is it illegal to withhold someone's passport and green card in California?
Why does Linux list NVMe drives as /dev/nvme0 instead of /dev/sda?
Do I have to explain the mechanical superiority of the player-character within the fiction of the game?
Find the C-factor of a vote
What could exist inside and between the walls of a Dyson Sphere?
Can Ogre clerics use Purify Food and Drink on humanoid characters?
Heavily limited premature compiler translates text into excecutable python code
Is a single radon-daughter atom in air a solid?
Why tighten down in a criss-cross pattern?
High traffic MYSQL server
Lower Maximum Possible Memory UsageMySQL keeps crashing OS server.. Please help adjust my.ini!How do I allow mysqld to use more than 24.9% of my cpu?my battle against web-server monitoring - slow serverImprove server performance by configuring MySQLMySQL becomes unresponsive a few times per hourmax_used_connections in mysql keeps on increasing on ubuntu 11.10Mysql server high trafic makes websites really slow or unable to loadmysqld_safe Can't log to error log and syslog at the same time. Remove all --log-error configuration options for --syslog to take effectMySQL Tuning — High Memory Usage
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I am looking for some help to twich my MYSQL server.
I am running a 5.7 MYSQL server on a 64 GB high end processor server (nothing else on it) and I just am unable to set the config for it to use all available ressouces.
So first off, I know I should soon replicate the server and have Slave/master Mysql - ill do it as soon as I exploit my first server fully. I also spent weeks in optimizing the queries and different caching (apache side) to limit the amount of queries. Now I need to make some config for it to be nice.
The situation; I have a huge amount of traffic and a huge amount of queries - and mysql seems not to be able to handle it anymore. I currently run at around 700 Selects/second (only 20 Updates and 30 Inserts) (My website and apache runs at 3k simultaneous connections).
My first thought was MaxClients that I took up to 250; I however noticed that I never have over 7 simultaneous "Connections" (says MysqlWorkbench). I tried understanding things the "Performance Report" tells me but I do not find any wierd numbers. This is why I need your help.
This is the current configurations I did for MYSQL :
max_connections = 250
query_cache_type=1
key_buffer_size=128M
query_cache_size=64M
back_log=100
query_prealloc_size=32K
sort_buffer_size=16M
read_buffer_size=2M
read_rnd_buffer_size=4M
key_cache_age_threshold=3000
key_cache_division_limit=50
memlock=1
max_connect_errors=999999
max_allowed_packet=1M
I also changed the somaxcon config of my Ubuntu.
Can someone please help me to hit a higher limit for my MYSQL server ? Because currently my Processors are only at 10% and my RAM at 6GB out of 64 and MYSQL is slowing my website/api down (too much traffic). What is the "perfect" config (I know it doesn't exist).
Oh and I am sure the problem is on that server ; everything runs smoothly if I cut all MYSQL connections and only use my Mongo/Redis ones (each service runs on a different server).
Please help, I am tearing my hair out. Please tell me if you need any screens from MysqlWorkbench.
mysql configuration
edited Jun 16 '18 at 6:33
jrtapsell
321314
asked May 29 '18 at 16:17
Bastian JakobsenBastian Jakobsen
165
|
show 1 more comment
I am looking for some help to twich my MYSQL server.
I am running a 5.7 MYSQL server on a 64 GB high end processor server (nothing else on it) and I just am unable to set the config for it to use all available ressouces.
So first off, I know I should soon replicate the server and have Slave/master Mysql - ill do it as soon as I exploit my first server fully. I also spent weeks in optimizing the queries and different caching (apache side) to limit the amount of queries. Now I need to make some config for it to be nice.
The situation; I have a huge amount of traffic and a huge amount of queries - and mysql seems not to be able to handle it anymore. I currently run at around 700 Selects/second (only 20 Updates and 30 Inserts) (My website and apache runs at 3k simultaneous connections).
My first thought was MaxClients that I took up to 250; I however noticed that I never have over 7 simultaneous "Connections" (says MysqlWorkbench). I tried understanding things the "Performance Report" tells me but I do not find any wierd numbers. This is why I need your help.
This is the current configurations I did for MYSQL :
max_connections = 250
query_cache_type=1
key_buffer_size=128M
query_cache_size=64M
back_log=100
query_prealloc_size=32K
sort_buffer_size=16M
read_buffer_size=2M
read_rnd_buffer_size=4M
key_cache_age_threshold=3000
key_cache_division_limit=50
memlock=1
max_connect_errors=999999
max_allowed_packet=1M
I also changed the somaxcon config of my Ubuntu.
Can someone please help me to hit a higher limit for my MYSQL server ? Because currently my Processors are only at 10% and my RAM at 6GB out of 64 and MYSQL is slowing my website/api down (too much traffic). What is the "perfect" config (I know it doesn't exist).
Oh and I am sure the problem is on that server ; everything runs smoothly if I cut all MYSQL connections and only use my Mongo/Redis ones (each service runs on a different server).
Please help, I am tearing my hair out. Please tell me if you need any screens from MysqlWorkbench.
mysql configuration
edited Jun 16 '18 at 6:33
jrtapsell
321314
asked May 29 '18 at 16:17
Bastian JakobsenBastian Jakobsen
165
I'll currently take your word for the performance of the queries. What can put a serious brake on things: connections. Does your application reuse existing connections, ie. connection pooling?
– Gerard H. Pille
May 29 '18 at 17:37
What does your storage look like? Does the system start fast and get slower over time? Do you have benchmarks etc?
– TheFiddlerWins
May 29 '18 at 18:52
1
Thank you for your answers: @GerardH.Pille : Yes and No, we only open one connection for every API call (having multiple queries) but we do not pool (is it possible?) by opening a connection for all the API queries. Note that one user will call our API about once every 10 seconds. This means that a user creates a connection every 10 seconds. Is there a way to maintain the connection open in PHP (in this case with SLIM) - i can't find anything about it ?
– Bastian Jakobsen
May 30 '18 at 7:04
1
@TheFiddlerWins : So our benchmarks are a bit sloppy but this is what we can confirm : 1. Senario A (200 Selects per second) : No problem at all 2. Senario B (500 Selects per second) : The first 10 seconds are not an issue but then it slows down. If we maintain the 500 selects per second for 10 minutes, we start getting timeouts 3. Senario C (1000 Selects per second) : It takes a few seconds to crash (actually Apache will go into 503 because of un-ability to handle the MYSQL connections)
– Bastian Jakobsen
May 30 '18 at 7:07
1
Yes sorry I made a shortcut ; I use slim that uses Laravel, that uses Eloquent ORM to query Mysql. Ill have a look at the p:
– Bastian Jakobsen
May 30 '18 at 8:23
|
show 1 more comment
I am looking for some help to twich my MYSQL server.
I am running a 5.7 MYSQL server on a 64 GB high end processor server (nothing else on it) and I just am unable to set the config for it to use all available ressouces.
So first off, I know I should soon replicate the server and have Slave/master Mysql - ill do it as soon as I exploit my first server fully. I also spent weeks in optimizing the queries and different caching (apache side) to limit the amount of queries. Now I need to make some config for it to be nice.
The situation; I have a huge amount of traffic and a huge amount of queries - and mysql seems not to be able to handle it anymore. I currently run at around 700 Selects/second (only 20 Updates and 30 Inserts) (My website and apache runs at 3k simultaneous connections).
My first thought was MaxClients that I took up to 250; I however noticed that I never have over 7 simultaneous "Connections" (says MysqlWorkbench). I tried understanding things the "Performance Report" tells me but I do not find any wierd numbers. This is why I need your help.
This is the current configurations I did for MYSQL :
max_connections = 250
query_cache_type=1
key_buffer_size=128M
query_cache_size=64M
back_log=100
query_prealloc_size=32K
sort_buffer_size=16M
read_buffer_size=2M
read_rnd_buffer_size=4M
key_cache_age_threshold=3000
key_cache_division_limit=50
memlock=1
max_connect_errors=999999
max_allowed_packet=1M
I also changed the somaxcon config of my Ubuntu.
Can someone please help me to hit a higher limit for my MYSQL server ? Because currently my Processors are only at 10% and my RAM at 6GB out of 64 and MYSQL is slowing my website/api down (too much traffic). What is the "perfect" config (I know it doesn't exist).
Oh and I am sure the problem is on that server ; everything runs smoothly if I cut all MYSQL connections and only use my Mongo/Redis ones (each service runs on a different server).
Please help, I am tearing my hair out. Please tell me if you need any screens from MysqlWorkbench.
mysql configuration
edited Jun 16 '18 at 6:33
jrtapsell
321314
asked May 29 '18 at 16:17
Bastian JakobsenBastian Jakobsen
165
I am looking for some help to twich my MYSQL server.
I am running a 5.7 MYSQL server on a 64 GB high end processor server (nothing else on it) and I just am unable to set the config for it to use all available ressouces.
So first off, I know I should soon replicate the server and have Slave/master Mysql - ill do it as soon as I exploit my first server fully. I also spent weeks in optimizing the queries and different caching (apache side) to limit the amount of queries. Now I need to make some config for it to be nice.
The situation; I have a huge amount of traffic and a huge amount of queries - and mysql seems not to be able to handle it anymore. I currently run at around 700 Selects/second (only 20 Updates and 30 Inserts) (My website and apache runs at 3k simultaneous connections).
My first thought was MaxClients that I took up to 250; I however noticed that I never have over 7 simultaneous "Connections" (says MysqlWorkbench). I tried understanding things the "Performance Report" tells me but I do not find any wierd numbers. This is why I need your help.
This is the current configurations I did for MYSQL :
max_connections = 250
query_cache_type=1
key_buffer_size=128M
query_cache_size=64M
back_log=100
query_prealloc_size=32K
sort_buffer_size=16M
read_buffer_size=2M
read_rnd_buffer_size=4M
key_cache_age_threshold=3000
key_cache_division_limit=50
memlock=1
max_connect_errors=999999
max_allowed_packet=1M
I also changed the somaxcon config of my Ubuntu.
Can someone please help me to hit a higher limit for my MYSQL server ? Because currently my Processors are only at 10% and my RAM at 6GB out of 64 and MYSQL is slowing my website/api down (too much traffic). What is the "perfect" config (I know it doesn't exist).
Oh and I am sure the problem is on that server ; everything runs smoothly if I cut all MYSQL connections and only use my Mongo/Redis ones (each service runs on a different server).
Please help, I am tearing my hair out. Please tell me if you need any screens from MysqlWorkbench.
mysql configuration
mysql configuration
edited Jun 16 '18 at 6:33
jrtapsell
321314
asked May 29 '18 at 16:17
Bastian JakobsenBastian Jakobsen
165
edited Jun 16 '18 at 6:33
jrtapsell
321314
asked May 29 '18 at 16:17
Bastian JakobsenBastian Jakobsen
165
edited Jun 16 '18 at 6:33
jrtapsell
321314
edited Jun 16 '18 at 6:33
jrtapsell
321314
edited Jun 16 '18 at 6:33
jrtapsell
321314
321314
asked May 29 '18 at 16:17
Bastian JakobsenBastian Jakobsen
165
asked May 29 '18 at 16:17
Bastian JakobsenBastian Jakobsen
165
asked May 29 '18 at 16:17
Bastian JakobsenBastian Jakobsen
165
165
I'll currently take your word for the performance of the queries. What can put a serious brake on things: connections. Does your application reuse existing connections, ie. connection pooling?
– Gerard H. Pille
May 29 '18 at 17:37
What does your storage look like? Does the system start fast and get slower over time? Do you have benchmarks etc?
– TheFiddlerWins
May 29 '18 at 18:52
1
Thank you for your answers: @GerardH.Pille : Yes and No, we only open one connection for every API call (having multiple queries) but we do not pool (is it possible?) by opening a connection for all the API queries. Note that one user will call our API about once every 10 seconds. This means that a user creates a connection every 10 seconds. Is there a way to maintain the connection open in PHP (in this case with SLIM) - i can't find anything about it ?
– Bastian Jakobsen
May 30 '18 at 7:04
1
@TheFiddlerWins : So our benchmarks are a bit sloppy but this is what we can confirm : 1. Senario A (200 Selects per second) : No problem at all 2. Senario B (500 Selects per second) : The first 10 seconds are not an issue but then it slows down. If we maintain the 500 selects per second for 10 minutes, we start getting timeouts 3. Senario C (1000 Selects per second) : It takes a few seconds to crash (actually Apache will go into 503 because of un-ability to handle the MYSQL connections)
– Bastian Jakobsen
May 30 '18 at 7:07
1
Yes sorry I made a shortcut ; I use slim that uses Laravel, that uses Eloquent ORM to query Mysql. Ill have a look at the p:
– Bastian Jakobsen
May 30 '18 at 8:23
|
show 1 more comment
I'll currently take your word for the performance of the queries. What can put a serious brake on things: connections. Does your application reuse existing connections, ie. connection pooling?
– Gerard H. Pille
May 29 '18 at 17:37
What does your storage look like? Does the system start fast and get slower over time? Do you have benchmarks etc?
– TheFiddlerWins
May 29 '18 at 18:52
1
Thank you for your answers: @GerardH.Pille : Yes and No, we only open one connection for every API call (having multiple queries) but we do not pool (is it possible?) by opening a connection for all the API queries. Note that one user will call our API about once every 10 seconds. This means that a user creates a connection every 10 seconds. Is there a way to maintain the connection open in PHP (in this case with SLIM) - i can't find anything about it ?
– Bastian Jakobsen
May 30 '18 at 7:04
1
@TheFiddlerWins : So our benchmarks are a bit sloppy but this is what we can confirm : 1. Senario A (200 Selects per second) : No problem at all 2. Senario B (500 Selects per second) : The first 10 seconds are not an issue but then it slows down. If we maintain the 500 selects per second for 10 minutes, we start getting timeouts 3. Senario C (1000 Selects per second) : It takes a few seconds to crash (actually Apache will go into 503 because of un-ability to handle the MYSQL connections)
– Bastian Jakobsen
May 30 '18 at 7:07
1
Yes sorry I made a shortcut ; I use slim that uses Laravel, that uses Eloquent ORM to query Mysql. Ill have a look at the p:
– Bastian Jakobsen
May 30 '18 at 8:23
I'll currently take your word for the performance of the queries. What can put a serious brake on things: connections. Does your application reuse existing connections, ie. connection pooling?
– Gerard H. Pille
May 29 '18 at 17:37
I'll currently take your word for the performance of the queries. What can put a serious brake on things: connections. Does your application reuse existing connections, ie. connection pooling?
– Gerard H. Pille
May 29 '18 at 17:37
What does your storage look like? Does the system start fast and get slower over time? Do you have benchmarks etc?
– TheFiddlerWins
May 29 '18 at 18:52
What does your storage look like? Does the system start fast and get slower over time? Do you have benchmarks etc?
– TheFiddlerWins
May 29 '18 at 18:52
1
1
Thank you for your answers: @GerardH.Pille : Yes and No, we only open one connection for every API call (having multiple queries) but we do not pool (is it possible?) by opening a connection for all the API queries. Note that one user will call our API about once every 10 seconds. This means that a user creates a connection every 10 seconds. Is there a way to maintain the connection open in PHP (in this case with SLIM) - i can't find anything about it ?
– Bastian Jakobsen
May 30 '18 at 7:04
Thank you for your answers: @GerardH.Pille : Yes and No, we only open one connection for every API call (having multiple queries) but we do not pool (is it possible?) by opening a connection for all the API queries. Note that one user will call our API about once every 10 seconds. This means that a user creates a connection every 10 seconds. Is there a way to maintain the connection open in PHP (in this case with SLIM) - i can't find anything about it ?
– Bastian Jakobsen
May 30 '18 at 7:04
1
1
@TheFiddlerWins : So our benchmarks are a bit sloppy but this is what we can confirm : 1. Senario A (200 Selects per second) : No problem at all 2. Senario B (500 Selects per second) : The first 10 seconds are not an issue but then it slows down. If we maintain the 500 selects per second for 10 minutes, we start getting timeouts 3. Senario C (1000 Selects per second) : It takes a few seconds to crash (actually Apache will go into 503 because of un-ability to handle the MYSQL connections)
– Bastian Jakobsen
May 30 '18 at 7:07
@TheFiddlerWins : So our benchmarks are a bit sloppy but this is what we can confirm : 1. Senario A (200 Selects per second) : No problem at all 2. Senario B (500 Selects per second) : The first 10 seconds are not an issue but then it slows down. If we maintain the 500 selects per second for 10 minutes, we start getting timeouts 3. Senario C (1000 Selects per second) : It takes a few seconds to crash (actually Apache will go into 503 because of un-ability to handle the MYSQL connections)
– Bastian Jakobsen
May 30 '18 at 7:07
1
1
Yes sorry I made a shortcut ; I use slim that uses Laravel, that uses Eloquent ORM to query Mysql. Ill have a look at the p:
– Bastian Jakobsen
May 30 '18 at 8:23
Yes sorry I made a shortcut ; I use slim that uses Laravel, that uses Eloquent ORM to query Mysql. Ill have a look at the p:
– Bastian Jakobsen
May 30 '18 at 8:23
|
show 1 more comment
3 Answers
3
active
oldest
votes
There are a bunch of things to do to improve your DB performance. I first go to a few things:
- You could try to increase your query_cache_size.
- Create indexes on popular tables
answered May 29 '18 at 19:09
Joe MJoe M
27114
1
Thanks for your answer, we just increased the query_cache_size to 128M and it doesnt seem to do much. If you mean creating indexes on the searched columns of our tables, thats already done.
– Bastian Jakobsen
May 30 '18 at 7:09
add a comment |
See here https://secure.php.net/manual/en/mysqli.persistconns.php
Unlike the mysql extension, mysqli does not provide a separate
function for opening persistent connections. To open a persistent
connection you must prepend p: to the hostname when connecting.
I'm curious what your benchmarks will show now.
answered May 30 '18 at 7:59
Gerard H. PilleGerard H. Pille
70648
1
Hey, I switched to persistant connections but I can't really see any change. I wont really be able to hit my limit before 5pm GMT and therefor wont be able to benchmark before then. However I thought that my mysql connections would roof as soon as I went in persistant mode ; it doesnt seem as it changes anything though, is it normal ? (I currently had 750 concurrent connections on my website when thaking this screenshot) See pic : imgur.com/a/ukgsaSJ
– Bastian Jakobsen
May 30 '18 at 8:41
If it stays like that, you're OK?
– Gerard H. Pille
May 30 '18 at 9:27
BTW, "mysql seems not to be able to handle it anymore". I've re-read your OP. What is it that gives you that impression? Is your website slow?
– Gerard H. Pille
May 30 '18 at 9:31
1
Well I am happy if it stays at 13-15 but shouldn't be much more? How come it's so low? Well the response time of the API (apache) is slow. But when I remove all MYSQL connections/queries, the response time is very fast. And even when MYSQL queriying directly, the queries take more time to execute.
– Bastian Jakobsen
May 30 '18 at 11:16
No, it will only grown if the existing connections cannot handle the running queries fast enough. Unless there is a limit on the number of connections somewhere. How many apache sessions are W (waiting)? BTW. you have redis, so MySQL isn't bothered with queries for static content, is it?
– Gerard H. Pille
May 30 '18 at 11:24
|
show 1 more comment
Suggestions to consider for your my.cnf-ini [mysqld] section
Lead with # to disable or REMOVE the following
to allow defaults to work for you.
. sort_buffer_size
. read_buffer_size
. read_rnd_buffer_size
additional lines to change or add to my.cnf-ini
max_connect_errors=10 # from 999999 to make Hackers/Crackers life miserable
. why give them more than 10 chances to guess a password?
These will help.
edited Jun 22 '18 at 1:16
womble♦
86.6k18149208
answered Jun 15 '18 at 20:38
Wilson HauckWilson Hauck
36718
@BastianJakobsen Have you disabled the bad boys in your my.cnf-ini to improve system performance and limited your hacker exposure to 10 attempts to guess a password? Thanks
– Wilson Hauck
Jun 20 '18 at 17:20
@BastianJakobsen What is your situation at this time? If any suggestions helped, upvote/accept if you have enough reputation. If you are still troubled, describe your current difficulty, please.
– Wilson Hauck
Jul 2 '18 at 0:43
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "2"
;
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%2fserverfault.com%2fquestions%2f914296%2fhigh-traffic-mysql-server%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
There are a bunch of things to do to improve your DB performance. I first go to a few things:
- You could try to increase your query_cache_size.
- Create indexes on popular tables
answered May 29 '18 at 19:09
Joe MJoe M
27114
1
Thanks for your answer, we just increased the query_cache_size to 128M and it doesnt seem to do much. If you mean creating indexes on the searched columns of our tables, thats already done.
– Bastian Jakobsen
May 30 '18 at 7:09
add a comment |
There are a bunch of things to do to improve your DB performance. I first go to a few things:
- You could try to increase your query_cache_size.
- Create indexes on popular tables
answered May 29 '18 at 19:09
Joe MJoe M
27114
1
Thanks for your answer, we just increased the query_cache_size to 128M and it doesnt seem to do much. If you mean creating indexes on the searched columns of our tables, thats already done.
– Bastian Jakobsen
May 30 '18 at 7:09
add a comment |
There are a bunch of things to do to improve your DB performance. I first go to a few things:
- You could try to increase your query_cache_size.
- Create indexes on popular tables
answered May 29 '18 at 19:09
Joe MJoe M
27114
There are a bunch of things to do to improve your DB performance. I first go to a few things:
- You could try to increase your query_cache_size.
- Create indexes on popular tables
answered May 29 '18 at 19:09
Joe MJoe M
27114
answered May 29 '18 at 19:09
Joe MJoe M
27114
answered May 29 '18 at 19:09
Joe MJoe M
27114
answered May 29 '18 at 19:09
Joe MJoe M
27114
27114
1
Thanks for your answer, we just increased the query_cache_size to 128M and it doesnt seem to do much. If you mean creating indexes on the searched columns of our tables, thats already done.
– Bastian Jakobsen
May 30 '18 at 7:09
add a comment |
1
Thanks for your answer, we just increased the query_cache_size to 128M and it doesnt seem to do much. If you mean creating indexes on the searched columns of our tables, thats already done.
– Bastian Jakobsen
May 30 '18 at 7:09
1
1
Thanks for your answer, we just increased the query_cache_size to 128M and it doesnt seem to do much. If you mean creating indexes on the searched columns of our tables, thats already done.
– Bastian Jakobsen
May 30 '18 at 7:09
Thanks for your answer, we just increased the query_cache_size to 128M and it doesnt seem to do much. If you mean creating indexes on the searched columns of our tables, thats already done.
– Bastian Jakobsen
May 30 '18 at 7:09
add a comment |
See here https://secure.php.net/manual/en/mysqli.persistconns.php
Unlike the mysql extension, mysqli does not provide a separate
function for opening persistent connections. To open a persistent
connection you must prepend p: to the hostname when connecting.
I'm curious what your benchmarks will show now.
answered May 30 '18 at 7:59
Gerard H. PilleGerard H. Pille
70648
1
Hey, I switched to persistant connections but I can't really see any change. I wont really be able to hit my limit before 5pm GMT and therefor wont be able to benchmark before then. However I thought that my mysql connections would roof as soon as I went in persistant mode ; it doesnt seem as it changes anything though, is it normal ? (I currently had 750 concurrent connections on my website when thaking this screenshot) See pic : imgur.com/a/ukgsaSJ
– Bastian Jakobsen
May 30 '18 at 8:41
If it stays like that, you're OK?
– Gerard H. Pille
May 30 '18 at 9:27
BTW, "mysql seems not to be able to handle it anymore". I've re-read your OP. What is it that gives you that impression? Is your website slow?
– Gerard H. Pille
May 30 '18 at 9:31
1
Well I am happy if it stays at 13-15 but shouldn't be much more? How come it's so low? Well the response time of the API (apache) is slow. But when I remove all MYSQL connections/queries, the response time is very fast. And even when MYSQL queriying directly, the queries take more time to execute.
– Bastian Jakobsen
May 30 '18 at 11:16
No, it will only grown if the existing connections cannot handle the running queries fast enough. Unless there is a limit on the number of connections somewhere. How many apache sessions are W (waiting)? BTW. you have redis, so MySQL isn't bothered with queries for static content, is it?
– Gerard H. Pille
May 30 '18 at 11:24
|
show 1 more comment
See here https://secure.php.net/manual/en/mysqli.persistconns.php
Unlike the mysql extension, mysqli does not provide a separate
function for opening persistent connections. To open a persistent
connection you must prepend p: to the hostname when connecting.
I'm curious what your benchmarks will show now.
answered May 30 '18 at 7:59
Gerard H. PilleGerard H. Pille
70648
1
Hey, I switched to persistant connections but I can't really see any change. I wont really be able to hit my limit before 5pm GMT and therefor wont be able to benchmark before then. However I thought that my mysql connections would roof as soon as I went in persistant mode ; it doesnt seem as it changes anything though, is it normal ? (I currently had 750 concurrent connections on my website when thaking this screenshot) See pic : imgur.com/a/ukgsaSJ
– Bastian Jakobsen
May 30 '18 at 8:41
If it stays like that, you're OK?
– Gerard H. Pille
May 30 '18 at 9:27
BTW, "mysql seems not to be able to handle it anymore". I've re-read your OP. What is it that gives you that impression? Is your website slow?
– Gerard H. Pille
May 30 '18 at 9:31
1
Well I am happy if it stays at 13-15 but shouldn't be much more? How come it's so low? Well the response time of the API (apache) is slow. But when I remove all MYSQL connections/queries, the response time is very fast. And even when MYSQL queriying directly, the queries take more time to execute.
– Bastian Jakobsen
May 30 '18 at 11:16
No, it will only grown if the existing connections cannot handle the running queries fast enough. Unless there is a limit on the number of connections somewhere. How many apache sessions are W (waiting)? BTW. you have redis, so MySQL isn't bothered with queries for static content, is it?
– Gerard H. Pille
May 30 '18 at 11:24
|
show 1 more comment
See here https://secure.php.net/manual/en/mysqli.persistconns.php
Unlike the mysql extension, mysqli does not provide a separate
function for opening persistent connections. To open a persistent
connection you must prepend p: to the hostname when connecting.
I'm curious what your benchmarks will show now.
answered May 30 '18 at 7:59
Gerard H. PilleGerard H. Pille
70648
See here https://secure.php.net/manual/en/mysqli.persistconns.php
Unlike the mysql extension, mysqli does not provide a separate
function for opening persistent connections. To open a persistent
connection you must prepend p: to the hostname when connecting.
I'm curious what your benchmarks will show now.
answered May 30 '18 at 7:59
Gerard H. PilleGerard H. Pille
70648
answered May 30 '18 at 7:59
Gerard H. PilleGerard H. Pille
70648
answered May 30 '18 at 7:59
Gerard H. PilleGerard H. Pille
70648
answered May 30 '18 at 7:59
Gerard H. PilleGerard H. Pille
70648
70648
1
Hey, I switched to persistant connections but I can't really see any change. I wont really be able to hit my limit before 5pm GMT and therefor wont be able to benchmark before then. However I thought that my mysql connections would roof as soon as I went in persistant mode ; it doesnt seem as it changes anything though, is it normal ? (I currently had 750 concurrent connections on my website when thaking this screenshot) See pic : imgur.com/a/ukgsaSJ
– Bastian Jakobsen
May 30 '18 at 8:41
If it stays like that, you're OK?
– Gerard H. Pille
May 30 '18 at 9:27
BTW, "mysql seems not to be able to handle it anymore". I've re-read your OP. What is it that gives you that impression? Is your website slow?
– Gerard H. Pille
May 30 '18 at 9:31
1
Well I am happy if it stays at 13-15 but shouldn't be much more? How come it's so low? Well the response time of the API (apache) is slow. But when I remove all MYSQL connections/queries, the response time is very fast. And even when MYSQL queriying directly, the queries take more time to execute.
– Bastian Jakobsen
May 30 '18 at 11:16
No, it will only grown if the existing connections cannot handle the running queries fast enough. Unless there is a limit on the number of connections somewhere. How many apache sessions are W (waiting)? BTW. you have redis, so MySQL isn't bothered with queries for static content, is it?
– Gerard H. Pille
May 30 '18 at 11:24
|
show 1 more comment
1
Hey, I switched to persistant connections but I can't really see any change. I wont really be able to hit my limit before 5pm GMT and therefor wont be able to benchmark before then. However I thought that my mysql connections would roof as soon as I went in persistant mode ; it doesnt seem as it changes anything though, is it normal ? (I currently had 750 concurrent connections on my website when thaking this screenshot) See pic : imgur.com/a/ukgsaSJ
– Bastian Jakobsen
May 30 '18 at 8:41
If it stays like that, you're OK?
– Gerard H. Pille
May 30 '18 at 9:27
BTW, "mysql seems not to be able to handle it anymore". I've re-read your OP. What is it that gives you that impression? Is your website slow?
– Gerard H. Pille
May 30 '18 at 9:31
1
Well I am happy if it stays at 13-15 but shouldn't be much more? How come it's so low? Well the response time of the API (apache) is slow. But when I remove all MYSQL connections/queries, the response time is very fast. And even when MYSQL queriying directly, the queries take more time to execute.
– Bastian Jakobsen
May 30 '18 at 11:16
No, it will only grown if the existing connections cannot handle the running queries fast enough. Unless there is a limit on the number of connections somewhere. How many apache sessions are W (waiting)? BTW. you have redis, so MySQL isn't bothered with queries for static content, is it?
– Gerard H. Pille
May 30 '18 at 11:24
1
1
Hey, I switched to persistant connections but I can't really see any change. I wont really be able to hit my limit before 5pm GMT and therefor wont be able to benchmark before then. However I thought that my mysql connections would roof as soon as I went in persistant mode ; it doesnt seem as it changes anything though, is it normal ? (I currently had 750 concurrent connections on my website when thaking this screenshot) See pic : imgur.com/a/ukgsaSJ
– Bastian Jakobsen
May 30 '18 at 8:41
Hey, I switched to persistant connections but I can't really see any change. I wont really be able to hit my limit before 5pm GMT and therefor wont be able to benchmark before then. However I thought that my mysql connections would roof as soon as I went in persistant mode ; it doesnt seem as it changes anything though, is it normal ? (I currently had 750 concurrent connections on my website when thaking this screenshot) See pic : imgur.com/a/ukgsaSJ
– Bastian Jakobsen
May 30 '18 at 8:41
If it stays like that, you're OK?
– Gerard H. Pille
May 30 '18 at 9:27
If it stays like that, you're OK?
– Gerard H. Pille
May 30 '18 at 9:27
BTW, "mysql seems not to be able to handle it anymore". I've re-read your OP. What is it that gives you that impression? Is your website slow?
– Gerard H. Pille
May 30 '18 at 9:31
BTW, "mysql seems not to be able to handle it anymore". I've re-read your OP. What is it that gives you that impression? Is your website slow?
– Gerard H. Pille
May 30 '18 at 9:31
1
1
Well I am happy if it stays at 13-15 but shouldn't be much more? How come it's so low? Well the response time of the API (apache) is slow. But when I remove all MYSQL connections/queries, the response time is very fast. And even when MYSQL queriying directly, the queries take more time to execute.
– Bastian Jakobsen
May 30 '18 at 11:16
Well I am happy if it stays at 13-15 but shouldn't be much more? How come it's so low? Well the response time of the API (apache) is slow. But when I remove all MYSQL connections/queries, the response time is very fast. And even when MYSQL queriying directly, the queries take more time to execute.
– Bastian Jakobsen
May 30 '18 at 11:16
No, it will only grown if the existing connections cannot handle the running queries fast enough. Unless there is a limit on the number of connections somewhere. How many apache sessions are W (waiting)? BTW. you have redis, so MySQL isn't bothered with queries for static content, is it?
– Gerard H. Pille
May 30 '18 at 11:24
No, it will only grown if the existing connections cannot handle the running queries fast enough. Unless there is a limit on the number of connections somewhere. How many apache sessions are W (waiting)? BTW. you have redis, so MySQL isn't bothered with queries for static content, is it?
– Gerard H. Pille
May 30 '18 at 11:24
|
show 1 more comment
Suggestions to consider for your my.cnf-ini [mysqld] section
Lead with # to disable or REMOVE the following
to allow defaults to work for you.
. sort_buffer_size
. read_buffer_size
. read_rnd_buffer_size
additional lines to change or add to my.cnf-ini
max_connect_errors=10 # from 999999 to make Hackers/Crackers life miserable
. why give them more than 10 chances to guess a password?
These will help.
edited Jun 22 '18 at 1:16
womble♦
86.6k18149208
answered Jun 15 '18 at 20:38
Wilson HauckWilson Hauck
36718
@BastianJakobsen Have you disabled the bad boys in your my.cnf-ini to improve system performance and limited your hacker exposure to 10 attempts to guess a password? Thanks
– Wilson Hauck
Jun 20 '18 at 17:20
@BastianJakobsen What is your situation at this time? If any suggestions helped, upvote/accept if you have enough reputation. If you are still troubled, describe your current difficulty, please.
– Wilson Hauck
Jul 2 '18 at 0:43
add a comment |
Suggestions to consider for your my.cnf-ini [mysqld] section
Lead with # to disable or REMOVE the following
to allow defaults to work for you.
. sort_buffer_size
. read_buffer_size
. read_rnd_buffer_size
additional lines to change or add to my.cnf-ini
max_connect_errors=10 # from 999999 to make Hackers/Crackers life miserable
. why give them more than 10 chances to guess a password?
These will help.
edited Jun 22 '18 at 1:16
womble♦
86.6k18149208
answered Jun 15 '18 at 20:38
Wilson HauckWilson Hauck
36718
@BastianJakobsen Have you disabled the bad boys in your my.cnf-ini to improve system performance and limited your hacker exposure to 10 attempts to guess a password? Thanks
– Wilson Hauck
Jun 20 '18 at 17:20
@BastianJakobsen What is your situation at this time? If any suggestions helped, upvote/accept if you have enough reputation. If you are still troubled, describe your current difficulty, please.
– Wilson Hauck
Jul 2 '18 at 0:43
add a comment |
Suggestions to consider for your my.cnf-ini [mysqld] section
Lead with # to disable or REMOVE the following
to allow defaults to work for you.
. sort_buffer_size
. read_buffer_size
. read_rnd_buffer_size
additional lines to change or add to my.cnf-ini
max_connect_errors=10 # from 999999 to make Hackers/Crackers life miserable
. why give them more than 10 chances to guess a password?
These will help.
edited Jun 22 '18 at 1:16
womble♦
86.6k18149208
answered Jun 15 '18 at 20:38
Wilson HauckWilson Hauck
36718
Suggestions to consider for your my.cnf-ini [mysqld] section
Lead with # to disable or REMOVE the following
to allow defaults to work for you.
. sort_buffer_size
. read_buffer_size
. read_rnd_buffer_size
additional lines to change or add to my.cnf-ini
max_connect_errors=10 # from 999999 to make Hackers/Crackers life miserable
. why give them more than 10 chances to guess a password?
These will help.
edited Jun 22 '18 at 1:16
womble♦
86.6k18149208
answered Jun 15 '18 at 20:38
Wilson HauckWilson Hauck
36718
edited Jun 22 '18 at 1:16
womble♦
86.6k18149208
edited Jun 22 '18 at 1:16
womble♦
86.6k18149208
edited Jun 22 '18 at 1:16
womble♦
86.6k18149208
86.6k18149208
answered Jun 15 '18 at 20:38
Wilson HauckWilson Hauck
36718
answered Jun 15 '18 at 20:38
Wilson HauckWilson Hauck
36718
answered Jun 15 '18 at 20:38
Wilson HauckWilson Hauck
36718
36718
@BastianJakobsen Have you disabled the bad boys in your my.cnf-ini to improve system performance and limited your hacker exposure to 10 attempts to guess a password? Thanks
– Wilson Hauck
Jun 20 '18 at 17:20
@BastianJakobsen What is your situation at this time? If any suggestions helped, upvote/accept if you have enough reputation. If you are still troubled, describe your current difficulty, please.
– Wilson Hauck
Jul 2 '18 at 0:43
add a comment |
@BastianJakobsen Have you disabled the bad boys in your my.cnf-ini to improve system performance and limited your hacker exposure to 10 attempts to guess a password? Thanks
– Wilson Hauck
Jun 20 '18 at 17:20
@BastianJakobsen What is your situation at this time? If any suggestions helped, upvote/accept if you have enough reputation. If you are still troubled, describe your current difficulty, please.
– Wilson Hauck
Jul 2 '18 at 0:43
@BastianJakobsen Have you disabled the bad boys in your my.cnf-ini to improve system performance and limited your hacker exposure to 10 attempts to guess a password? Thanks
– Wilson Hauck
Jun 20 '18 at 17:20
@BastianJakobsen Have you disabled the bad boys in your my.cnf-ini to improve system performance and limited your hacker exposure to 10 attempts to guess a password? Thanks
– Wilson Hauck
Jun 20 '18 at 17:20
@BastianJakobsen What is your situation at this time? If any suggestions helped, upvote/accept if you have enough reputation. If you are still troubled, describe your current difficulty, please.
– Wilson Hauck
Jul 2 '18 at 0:43
@BastianJakobsen What is your situation at this time? If any suggestions helped, upvote/accept if you have enough reputation. If you are still troubled, describe your current difficulty, please.
– Wilson Hauck
Jul 2 '18 at 0:43
add a comment |
Thanks for contributing an answer to Server Fault!
- 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%2fserverfault.com%2fquestions%2f914296%2fhigh-traffic-mysql-server%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
I'll currently take your word for the performance of the queries. What can put a serious brake on things: connections. Does your application reuse existing connections, ie. connection pooling?
– Gerard H. Pille
May 29 '18 at 17:37
What does your storage look like? Does the system start fast and get slower over time? Do you have benchmarks etc?
– TheFiddlerWins
May 29 '18 at 18:52
1
Thank you for your answers: @GerardH.Pille : Yes and No, we only open one connection for every API call (having multiple queries) but we do not pool (is it possible?) by opening a connection for all the API queries. Note that one user will call our API about once every 10 seconds. This means that a user creates a connection every 10 seconds. Is there a way to maintain the connection open in PHP (in this case with SLIM) - i can't find anything about it ?
– Bastian Jakobsen
May 30 '18 at 7:04
1
@TheFiddlerWins : So our benchmarks are a bit sloppy but this is what we can confirm : 1. Senario A (200 Selects per second) : No problem at all 2. Senario B (500 Selects per second) : The first 10 seconds are not an issue but then it slows down. If we maintain the 500 selects per second for 10 minutes, we start getting timeouts 3. Senario C (1000 Selects per second) : It takes a few seconds to crash (actually Apache will go into 503 because of un-ability to handle the MYSQL connections)
– Bastian Jakobsen
May 30 '18 at 7:07
1
Yes sorry I made a shortcut ; I use slim that uses Laravel, that uses Eloquent ORM to query Mysql. Ill have a look at the p:
– Bastian Jakobsen
May 30 '18 at 8:23