Slow Queries WordPress Site (500k visitors a month and 150k posts)Mysql high queries per second, with high cpu usageWhen loading a wordpress site, is more CPU or RAM better?Wordpress slow when publishingMy wordpress website is deadly slowHolding F5 on Wordpress site causes 100% CPU (Apache)Wordpress High load average 18 and cpu 81% on a dedicated serverMySQL 5.5 and MariaDB5.5 return different number of columns for the same data and same select query?Occasional slow database queries caused by InnoDBPerformance difference between MySQL and MariaDBMariadb 1000% CPU
Why do I get two different answers when solving for arclength?
When a land becomes a creature, is it untapped?
Is there ever any indication in the MCU as to how Spider-Man got his powers?
CPLD based Pierce oscillator
Where to find every-day healthy food near Heathrow Airport?
Can a tourist shoot a gun in the USA?
What to do if SUS scores contradict qualitative feedback?
What's tha name for when you write multiple voices on same staff? And are there any cons?
Longest Text in Latin
Do Life Drain attacks from wights stack?
Unexpected Netflix account registered to my Gmail address - any way it could be a hack attempt?
Would an 8% reduction in drag outweigh the weight addition from this custom CFD-tested winglet?
Why does my circuit work on a breadboard, but not on a perfboard? I am new to soldering
what does a native speaker say when he wanted to leave his work?
Frame adjustment for engine
Why are solar panels kept tilted?
Can someone explain homicide-related death rates?
In books, how many dragons are there in present time?
How can I answer high-school writing prompts without sounding weird and fake?
Ito`s Lemma problem
Does gravity affect the time evolution of a QM wave function?
Labeling matrices/rectangles and drawing Sigma inside rectangle
German characters on US-International keyboard layout
Extracting sublists that contain similar elements
Slow Queries WordPress Site (500k visitors a month and 150k posts)
Mysql high queries per second, with high cpu usageWhen loading a wordpress site, is more CPU or RAM better?Wordpress slow when publishingMy wordpress website is deadly slowHolding F5 on Wordpress site causes 100% CPU (Apache)Wordpress High load average 18 and cpu 81% on a dedicated serverMySQL 5.5 and MariaDB5.5 return different number of columns for the same data and same select query?Occasional slow database queries caused by InnoDBPerformance difference between MySQL and MariaDBMariadb 1000% CPU
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I'm running a WordPress site with 500k visitors a month and 150k posts with in average 100 pageviews every second. I am trying to figure out if the load on the server is normal or if there is something I can do to fix the performance issues without increasing the server setup and monthly costs.
Here is the server setup i'm running right now:
- 2 Front-end servers, Nginx: 2 CPU & 4GB RAM
- 1 DB server, MariaDB: 8 CPU & 16GB RAM
- 1 Redis server: 2 CPU & 4GB RAM
The WordPress theme is develop from scratch were I have optimize the queries and minimized the use of plugins (5 plugins in total).
I run Nginx with Reverse Proxy Cache where I cache all pages for 5 minutes to be able to handle peaks in traffic (two daily peaks with 3k visitors in 30 min when sending newsletters).
The MariaDB and Redis server is running Debian with out of the box configuration. The only thing I've changed is innodb_buffer_pool_size = 11G and max_connections = 300 in MariaDB.
The DB CPU is running at 50% when having 100 real time visitors and 85-90% with 300-700 real time visitors.
The problem is that the queries take some time to load (3-6 seconds) even with 50% load at the CPU.
My staging environment is running on the exact same servers but with another database table (same amount of posts) and queries time is 0,5-1,5 seconds.
So the only difference is that the production database have more concurrent users.
What can it be that make the queries take this time to load?
nginx mysql wordpress mariadb redis
asked May 2 at 19:36
rccoderccode
1
add a comment |
I'm running a WordPress site with 500k visitors a month and 150k posts with in average 100 pageviews every second. I am trying to figure out if the load on the server is normal or if there is something I can do to fix the performance issues without increasing the server setup and monthly costs.
Here is the server setup i'm running right now:
- 2 Front-end servers, Nginx: 2 CPU & 4GB RAM
- 1 DB server, MariaDB: 8 CPU & 16GB RAM
- 1 Redis server: 2 CPU & 4GB RAM
The WordPress theme is develop from scratch were I have optimize the queries and minimized the use of plugins (5 plugins in total).
I run Nginx with Reverse Proxy Cache where I cache all pages for 5 minutes to be able to handle peaks in traffic (two daily peaks with 3k visitors in 30 min when sending newsletters).
The MariaDB and Redis server is running Debian with out of the box configuration. The only thing I've changed is innodb_buffer_pool_size = 11G and max_connections = 300 in MariaDB.
The DB CPU is running at 50% when having 100 real time visitors and 85-90% with 300-700 real time visitors.
The problem is that the queries take some time to load (3-6 seconds) even with 50% load at the CPU.
My staging environment is running on the exact same servers but with another database table (same amount of posts) and queries time is 0,5-1,5 seconds.
So the only difference is that the production database have more concurrent users.
What can it be that make the queries take this time to load?
nginx mysql wordpress mariadb redis
asked May 2 at 19:36
rccoderccode
1
Do anEXPLAINon the slow queries and look into the execution plan.
– ceejayoz
May 2 at 19:57
This probably should be moved to dba stackexchange. You will need to monitor and see which queries are taking the longest and figure out why. That will give you a start. Considering your site is most likely a read heavy setup, query and data cache tuning will help quite a bit. But you will need to research the top slowest queries, ideally during a stress scenario, to get a starting point.
– Jarrod Christman
May 2 at 19:58
add a comment |
I'm running a WordPress site with 500k visitors a month and 150k posts with in average 100 pageviews every second. I am trying to figure out if the load on the server is normal or if there is something I can do to fix the performance issues without increasing the server setup and monthly costs.
Here is the server setup i'm running right now:
- 2 Front-end servers, Nginx: 2 CPU & 4GB RAM
- 1 DB server, MariaDB: 8 CPU & 16GB RAM
- 1 Redis server: 2 CPU & 4GB RAM
The WordPress theme is develop from scratch were I have optimize the queries and minimized the use of plugins (5 plugins in total).
I run Nginx with Reverse Proxy Cache where I cache all pages for 5 minutes to be able to handle peaks in traffic (two daily peaks with 3k visitors in 30 min when sending newsletters).
The MariaDB and Redis server is running Debian with out of the box configuration. The only thing I've changed is innodb_buffer_pool_size = 11G and max_connections = 300 in MariaDB.
The DB CPU is running at 50% when having 100 real time visitors and 85-90% with 300-700 real time visitors.
The problem is that the queries take some time to load (3-6 seconds) even with 50% load at the CPU.
My staging environment is running on the exact same servers but with another database table (same amount of posts) and queries time is 0,5-1,5 seconds.
So the only difference is that the production database have more concurrent users.
What can it be that make the queries take this time to load?
nginx mysql wordpress mariadb redis
asked May 2 at 19:36
rccoderccode
1
I'm running a WordPress site with 500k visitors a month and 150k posts with in average 100 pageviews every second. I am trying to figure out if the load on the server is normal or if there is something I can do to fix the performance issues without increasing the server setup and monthly costs.
Here is the server setup i'm running right now:
- 2 Front-end servers, Nginx: 2 CPU & 4GB RAM
- 1 DB server, MariaDB: 8 CPU & 16GB RAM
- 1 Redis server: 2 CPU & 4GB RAM
The WordPress theme is develop from scratch were I have optimize the queries and minimized the use of plugins (5 plugins in total).
I run Nginx with Reverse Proxy Cache where I cache all pages for 5 minutes to be able to handle peaks in traffic (two daily peaks with 3k visitors in 30 min when sending newsletters).
The MariaDB and Redis server is running Debian with out of the box configuration. The only thing I've changed is innodb_buffer_pool_size = 11G and max_connections = 300 in MariaDB.
The DB CPU is running at 50% when having 100 real time visitors and 85-90% with 300-700 real time visitors.
The problem is that the queries take some time to load (3-6 seconds) even with 50% load at the CPU.
My staging environment is running on the exact same servers but with another database table (same amount of posts) and queries time is 0,5-1,5 seconds.
So the only difference is that the production database have more concurrent users.
What can it be that make the queries take this time to load?
nginx mysql wordpress mariadb redis
nginx mysql wordpress mariadb redis
asked May 2 at 19:36
rccoderccode
1
asked May 2 at 19:36
rccoderccode
1
asked May 2 at 19:36
rccoderccode
1
asked May 2 at 19:36
rccoderccode
1
asked May 2 at 19:36
rccoderccode
1
1
Do anEXPLAINon the slow queries and look into the execution plan.
– ceejayoz
May 2 at 19:57
This probably should be moved to dba stackexchange. You will need to monitor and see which queries are taking the longest and figure out why. That will give you a start. Considering your site is most likely a read heavy setup, query and data cache tuning will help quite a bit. But you will need to research the top slowest queries, ideally during a stress scenario, to get a starting point.
– Jarrod Christman
May 2 at 19:58
add a comment |
Do anEXPLAINon the slow queries and look into the execution plan.
– ceejayoz
May 2 at 19:57
This probably should be moved to dba stackexchange. You will need to monitor and see which queries are taking the longest and figure out why. That will give you a start. Considering your site is most likely a read heavy setup, query and data cache tuning will help quite a bit. But you will need to research the top slowest queries, ideally during a stress scenario, to get a starting point.
– Jarrod Christman
May 2 at 19:58
Do an
EXPLAIN on the slow queries and look into the execution plan.– ceejayoz
May 2 at 19:57
Do an
EXPLAIN on the slow queries and look into the execution plan.– ceejayoz
May 2 at 19:57
This probably should be moved to dba stackexchange. You will need to monitor and see which queries are taking the longest and figure out why. That will give you a start. Considering your site is most likely a read heavy setup, query and data cache tuning will help quite a bit. But you will need to research the top slowest queries, ideally during a stress scenario, to get a starting point.
– Jarrod Christman
May 2 at 19:58
This probably should be moved to dba stackexchange. You will need to monitor and see which queries are taking the longest and figure out why. That will give you a start. Considering your site is most likely a read heavy setup, query and data cache tuning will help quite a bit. But you will need to research the top slowest queries, ideally during a stress scenario, to get a starting point.
– Jarrod Christman
May 2 at 19:58
add a comment |
2 Answers
2
active
oldest
votes
Firstly, enable slow query logging if you have not already done so:
https://mariadb.com/kb/en/library/slow-query-log-overview/
Next, you can take one of those queries and put EXPLAIN in front of it. This will show you a table that explains what happened when executing the query. It will for instance show if an index was used why executing the query. The following page explains what all the columns mean:
https://mariadb.com/kb/en/library/explain/
Most of the times placing a well though out index on a column with good cardinality will reduce the time needed to execute the query significantly.
answered May 2 at 20:12
Jochem BruijnsJochem Bruijns
111
add a comment |
One of the main slownesses in WP is fixable.
http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
discusses how to change the schema for postmeta (and, optionally, other meta tables) so that the main queries using that table will run faster.
answered May 3 at 2:47
Rick JamesRick James
2366
Thanks! Can I do this on a existing WordPress DB?
– rccode
May 3 at 6:44
@rccode - It should be possible in an existing WP. The downtime may be "minutes" for theALTERs, depending on the table size.
– Rick James
May 3 at 13:09
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%2f965636%2fslow-queries-wordpress-site-500k-visitors-a-month-and-150k-posts%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Firstly, enable slow query logging if you have not already done so:
https://mariadb.com/kb/en/library/slow-query-log-overview/
Next, you can take one of those queries and put EXPLAIN in front of it. This will show you a table that explains what happened when executing the query. It will for instance show if an index was used why executing the query. The following page explains what all the columns mean:
https://mariadb.com/kb/en/library/explain/
Most of the times placing a well though out index on a column with good cardinality will reduce the time needed to execute the query significantly.
answered May 2 at 20:12
Jochem BruijnsJochem Bruijns
111
add a comment |
Firstly, enable slow query logging if you have not already done so:
https://mariadb.com/kb/en/library/slow-query-log-overview/
Next, you can take one of those queries and put EXPLAIN in front of it. This will show you a table that explains what happened when executing the query. It will for instance show if an index was used why executing the query. The following page explains what all the columns mean:
https://mariadb.com/kb/en/library/explain/
Most of the times placing a well though out index on a column with good cardinality will reduce the time needed to execute the query significantly.
answered May 2 at 20:12
Jochem BruijnsJochem Bruijns
111
add a comment |
Firstly, enable slow query logging if you have not already done so:
https://mariadb.com/kb/en/library/slow-query-log-overview/
Next, you can take one of those queries and put EXPLAIN in front of it. This will show you a table that explains what happened when executing the query. It will for instance show if an index was used why executing the query. The following page explains what all the columns mean:
https://mariadb.com/kb/en/library/explain/
Most of the times placing a well though out index on a column with good cardinality will reduce the time needed to execute the query significantly.
answered May 2 at 20:12
Jochem BruijnsJochem Bruijns
111
Firstly, enable slow query logging if you have not already done so:
https://mariadb.com/kb/en/library/slow-query-log-overview/
Next, you can take one of those queries and put EXPLAIN in front of it. This will show you a table that explains what happened when executing the query. It will for instance show if an index was used why executing the query. The following page explains what all the columns mean:
https://mariadb.com/kb/en/library/explain/
Most of the times placing a well though out index on a column with good cardinality will reduce the time needed to execute the query significantly.
answered May 2 at 20:12
Jochem BruijnsJochem Bruijns
111
answered May 2 at 20:12
Jochem BruijnsJochem Bruijns
111
answered May 2 at 20:12
Jochem BruijnsJochem Bruijns
111
answered May 2 at 20:12
Jochem BruijnsJochem Bruijns
111
111
add a comment |
add a comment |
One of the main slownesses in WP is fixable.
http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
discusses how to change the schema for postmeta (and, optionally, other meta tables) so that the main queries using that table will run faster.
answered May 3 at 2:47
Rick JamesRick James
2366
Thanks! Can I do this on a existing WordPress DB?
– rccode
May 3 at 6:44
@rccode - It should be possible in an existing WP. The downtime may be "minutes" for theALTERs, depending on the table size.
– Rick James
May 3 at 13:09
add a comment |
One of the main slownesses in WP is fixable.
http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
discusses how to change the schema for postmeta (and, optionally, other meta tables) so that the main queries using that table will run faster.
answered May 3 at 2:47
Rick JamesRick James
2366
Thanks! Can I do this on a existing WordPress DB?
– rccode
May 3 at 6:44
@rccode - It should be possible in an existing WP. The downtime may be "minutes" for theALTERs, depending on the table size.
– Rick James
May 3 at 13:09
add a comment |
One of the main slownesses in WP is fixable.
http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
discusses how to change the schema for postmeta (and, optionally, other meta tables) so that the main queries using that table will run faster.
answered May 3 at 2:47
Rick JamesRick James
2366
One of the main slownesses in WP is fixable.
http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
discusses how to change the schema for postmeta (and, optionally, other meta tables) so that the main queries using that table will run faster.
answered May 3 at 2:47
Rick JamesRick James
2366
answered May 3 at 2:47
Rick JamesRick James
2366
answered May 3 at 2:47
Rick JamesRick James
2366
answered May 3 at 2:47
Rick JamesRick James
2366
2366
Thanks! Can I do this on a existing WordPress DB?
– rccode
May 3 at 6:44
@rccode - It should be possible in an existing WP. The downtime may be "minutes" for theALTERs, depending on the table size.
– Rick James
May 3 at 13:09
add a comment |
Thanks! Can I do this on a existing WordPress DB?
– rccode
May 3 at 6:44
@rccode - It should be possible in an existing WP. The downtime may be "minutes" for theALTERs, depending on the table size.
– Rick James
May 3 at 13:09
Thanks! Can I do this on a existing WordPress DB?
– rccode
May 3 at 6:44
Thanks! Can I do this on a existing WordPress DB?
– rccode
May 3 at 6:44
@rccode - It should be possible in an existing WP. The downtime may be "minutes" for the
ALTERs, depending on the table size.– Rick James
May 3 at 13:09
@rccode - It should be possible in an existing WP. The downtime may be "minutes" for the
ALTERs, depending on the table size.– Rick James
May 3 at 13:09
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%2f965636%2fslow-queries-wordpress-site-500k-visitors-a-month-and-150k-posts%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
Do an
EXPLAINon the slow queries and look into the execution plan.– ceejayoz
May 2 at 19:57
This probably should be moved to dba stackexchange. You will need to monitor and see which queries are taking the longest and figure out why. That will give you a start. Considering your site is most likely a read heavy setup, query and data cache tuning will help quite a bit. But you will need to research the top slowest queries, ideally during a stress scenario, to get a starting point.
– Jarrod Christman
May 2 at 19:58