enterprise_refresh_index Sleeping MySQL Connection Planned maintenance scheduled April 23,...
What did Turing mean when saying that "machines cannot give rise to surprises" is due to a fallacy?
Is there a verb for listening stealthily?
Does the main washing effect of soap come from foam?
In musical terms, what properties are varied by the human voice to produce different words / syllables?
What can we say about Classical Nahuatl <z>?
How to ask rejected full-time candidates to apply to teach individual courses?
Does the universe have a fixed centre of mass?
Can gravitational waves pass through a black hole?
Russian equivalents of おしゃれは足元から (Every good outfit starts with the shoes)
.bashrc alias for a command with fixed second parameter
Noise in Eigenvalues plot
Why is there so little support for joining EFTA in the British parliament?
Is the Mordenkainen's Sword spell underpowered?
How can I prevent/balance waiting and turtling as a response to cooldown mechanics
Is there a spell that can create a permanent fire?
Is a copyright notice with a non-existent name be invalid?
How to achieve cat-like agility?
How do Java 8 default methods hеlp with lambdas?
Derived column in a data extension
Understanding piped commands in GNU/Linux
How to make an animal which can only breed for a certain number of generations?
Is this Kuo-toa homebrew race balanced?
What is a more techy Technical Writer job title that isn't cutesy or confusing?
What is "Lambda" in Heston's original paper on stochastic volatility models?
enterprise_refresh_index Sleeping MySQL Connection
Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern)
Announcing the arrival of Valued Associate #679: Cesar Manara
Unicorn Meta Zoo #1: Why another podcast?Problems with reindexing productsAdd to cart / checkout performance issuesSet Magento MySQL Connection to use SSLMagento 1.9.1 with MySQL 5.5Magento Enterprise - Ongoing Redis Connection ErrorsMySQL error when targetrule indexer is executedMysql Query to Create a custom reportMySQL server has gone away when maual reindex catalog_product_flatMagento 2.0 with upgraded to php 7.0 Indexer Stuck at processingRogue “enterprise_refresh_index” causing excessive Merchandising failures
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
There are times that the indexer that is ran by cron seems to get stuck at the MySQL as Sleeping connections can be seen. Indexer runs for several hours and this actually not running at all. I've searched for this but I couldn't find any related. Anyone who can shed a light? Possibly server configuration or Magento bug?
magento-enterprise mysql reindex indexer
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
There are times that the indexer that is ran by cron seems to get stuck at the MySQL as Sleeping connections can be seen. Indexer runs for several hours and this actually not running at all. I've searched for this but I couldn't find any related. Anyone who can shed a light? Possibly server configuration or Magento bug?
magento-enterprise mysql reindex indexer
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
There are times that the indexer that is ran by cron seems to get stuck at the MySQL as Sleeping connections can be seen. Indexer runs for several hours and this actually not running at all. I've searched for this but I couldn't find any related. Anyone who can shed a light? Possibly server configuration or Magento bug?
magento-enterprise mysql reindex indexer
There are times that the indexer that is ran by cron seems to get stuck at the MySQL as Sleeping connections can be seen. Indexer runs for several hours and this actually not running at all. I've searched for this but I couldn't find any related. Anyone who can shed a light? Possibly server configuration or Magento bug?
magento-enterprise mysql reindex indexer
magento-enterprise mysql reindex indexer
edited Jun 12 '15 at 7:23
Marius♦
168k28324692
168k28324692
asked Jun 12 '15 at 4:10
user1240207user1240207
77421031
77421031
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Are you using persistent connections? How many sleep connections do you have? What is the max amount of connections your DB Server is configured to allow?
To check how many sleep connections you have just run:
show full processlist;
To see the max_connections run:
show variables like 'max_connections';
I think that the sleeping connections are not the problem, mysqld will timeout sleep connections based on 2 values:
interactive_timeout
wait_timetout
Both are 28800 seconds (8 hours) by default.
You can set these options in my.cnf (location of this file is different in different OS and DBs, percona, mysql, etc)
Also see this answer from Database Administrators: https://dba.stackexchange.com/a/1559 and if you would like to know more on how to debug the origin of the sleeping connections check this excellent article: https://www.percona.com/blog/2007/02/08/debugging-sleeping-connections-with-mysql/
"If your connections are persistent (opened via mysql_pconnect) you could lower these numbers to something reasonable like 600 (10 min) or even 60 (1 min). Or, if your app works just fine, you can leave the default. This is up to you."
Try to run the indexer from the console to see if it outputs some error:
php shell/indexer info # this will output the list of indexes then
php shell/indexer --reindex {index_name}
Unfortunately, we're still getting this issue. I run the show full process command and it displayed a very long SQL statements with entity IDs listed.
– user1240207
Jul 3 '15 at 3:25
add a comment |
configure your mysql-server by setting a shorter timeout on wait_timeout
and interactive_timeout
mysql> show variables like "%timeout%"
;
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| connect_timeout | 5 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| interactive_timeout | 28800 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 |
| wait_timeout | 28800 |
+--------------------------+-------+
9 rows in set (0.00 sec)
Set with:
set global wait_timeout=3;
set global interactive_timeout=3;
(and also set in your configuration file, for when your server restarts)
But you're treating the symptoms instead of the underlying cause - why are the connections open? If the PHP script finished, shouldn't they close? Make sure your webserver is not using connection pooling...
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "479"
};
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%2fmagento.stackexchange.com%2fquestions%2f70812%2fenterprise-refresh-index-sleeping-mysql-connection%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
Are you using persistent connections? How many sleep connections do you have? What is the max amount of connections your DB Server is configured to allow?
To check how many sleep connections you have just run:
show full processlist;
To see the max_connections run:
show variables like 'max_connections';
I think that the sleeping connections are not the problem, mysqld will timeout sleep connections based on 2 values:
interactive_timeout
wait_timetout
Both are 28800 seconds (8 hours) by default.
You can set these options in my.cnf (location of this file is different in different OS and DBs, percona, mysql, etc)
Also see this answer from Database Administrators: https://dba.stackexchange.com/a/1559 and if you would like to know more on how to debug the origin of the sleeping connections check this excellent article: https://www.percona.com/blog/2007/02/08/debugging-sleeping-connections-with-mysql/
"If your connections are persistent (opened via mysql_pconnect) you could lower these numbers to something reasonable like 600 (10 min) or even 60 (1 min). Or, if your app works just fine, you can leave the default. This is up to you."
Try to run the indexer from the console to see if it outputs some error:
php shell/indexer info # this will output the list of indexes then
php shell/indexer --reindex {index_name}
Unfortunately, we're still getting this issue. I run the show full process command and it displayed a very long SQL statements with entity IDs listed.
– user1240207
Jul 3 '15 at 3:25
add a comment |
Are you using persistent connections? How many sleep connections do you have? What is the max amount of connections your DB Server is configured to allow?
To check how many sleep connections you have just run:
show full processlist;
To see the max_connections run:
show variables like 'max_connections';
I think that the sleeping connections are not the problem, mysqld will timeout sleep connections based on 2 values:
interactive_timeout
wait_timetout
Both are 28800 seconds (8 hours) by default.
You can set these options in my.cnf (location of this file is different in different OS and DBs, percona, mysql, etc)
Also see this answer from Database Administrators: https://dba.stackexchange.com/a/1559 and if you would like to know more on how to debug the origin of the sleeping connections check this excellent article: https://www.percona.com/blog/2007/02/08/debugging-sleeping-connections-with-mysql/
"If your connections are persistent (opened via mysql_pconnect) you could lower these numbers to something reasonable like 600 (10 min) or even 60 (1 min). Or, if your app works just fine, you can leave the default. This is up to you."
Try to run the indexer from the console to see if it outputs some error:
php shell/indexer info # this will output the list of indexes then
php shell/indexer --reindex {index_name}
Unfortunately, we're still getting this issue. I run the show full process command and it displayed a very long SQL statements with entity IDs listed.
– user1240207
Jul 3 '15 at 3:25
add a comment |
Are you using persistent connections? How many sleep connections do you have? What is the max amount of connections your DB Server is configured to allow?
To check how many sleep connections you have just run:
show full processlist;
To see the max_connections run:
show variables like 'max_connections';
I think that the sleeping connections are not the problem, mysqld will timeout sleep connections based on 2 values:
interactive_timeout
wait_timetout
Both are 28800 seconds (8 hours) by default.
You can set these options in my.cnf (location of this file is different in different OS and DBs, percona, mysql, etc)
Also see this answer from Database Administrators: https://dba.stackexchange.com/a/1559 and if you would like to know more on how to debug the origin of the sleeping connections check this excellent article: https://www.percona.com/blog/2007/02/08/debugging-sleeping-connections-with-mysql/
"If your connections are persistent (opened via mysql_pconnect) you could lower these numbers to something reasonable like 600 (10 min) or even 60 (1 min). Or, if your app works just fine, you can leave the default. This is up to you."
Try to run the indexer from the console to see if it outputs some error:
php shell/indexer info # this will output the list of indexes then
php shell/indexer --reindex {index_name}
Are you using persistent connections? How many sleep connections do you have? What is the max amount of connections your DB Server is configured to allow?
To check how many sleep connections you have just run:
show full processlist;
To see the max_connections run:
show variables like 'max_connections';
I think that the sleeping connections are not the problem, mysqld will timeout sleep connections based on 2 values:
interactive_timeout
wait_timetout
Both are 28800 seconds (8 hours) by default.
You can set these options in my.cnf (location of this file is different in different OS and DBs, percona, mysql, etc)
Also see this answer from Database Administrators: https://dba.stackexchange.com/a/1559 and if you would like to know more on how to debug the origin of the sleeping connections check this excellent article: https://www.percona.com/blog/2007/02/08/debugging-sleeping-connections-with-mysql/
"If your connections are persistent (opened via mysql_pconnect) you could lower these numbers to something reasonable like 600 (10 min) or even 60 (1 min). Or, if your app works just fine, you can leave the default. This is up to you."
Try to run the indexer from the console to see if it outputs some error:
php shell/indexer info # this will output the list of indexes then
php shell/indexer --reindex {index_name}
edited Apr 13 '17 at 12:43
Community♦
1
1
answered Jun 12 '15 at 6:41
lloiaconolloiacono
2,9761338
2,9761338
Unfortunately, we're still getting this issue. I run the show full process command and it displayed a very long SQL statements with entity IDs listed.
– user1240207
Jul 3 '15 at 3:25
add a comment |
Unfortunately, we're still getting this issue. I run the show full process command and it displayed a very long SQL statements with entity IDs listed.
– user1240207
Jul 3 '15 at 3:25
Unfortunately, we're still getting this issue. I run the show full process command and it displayed a very long SQL statements with entity IDs listed.
– user1240207
Jul 3 '15 at 3:25
Unfortunately, we're still getting this issue. I run the show full process command and it displayed a very long SQL statements with entity IDs listed.
– user1240207
Jul 3 '15 at 3:25
add a comment |
configure your mysql-server by setting a shorter timeout on wait_timeout
and interactive_timeout
mysql> show variables like "%timeout%"
;
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| connect_timeout | 5 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| interactive_timeout | 28800 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 |
| wait_timeout | 28800 |
+--------------------------+-------+
9 rows in set (0.00 sec)
Set with:
set global wait_timeout=3;
set global interactive_timeout=3;
(and also set in your configuration file, for when your server restarts)
But you're treating the symptoms instead of the underlying cause - why are the connections open? If the PHP script finished, shouldn't they close? Make sure your webserver is not using connection pooling...
add a comment |
configure your mysql-server by setting a shorter timeout on wait_timeout
and interactive_timeout
mysql> show variables like "%timeout%"
;
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| connect_timeout | 5 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| interactive_timeout | 28800 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 |
| wait_timeout | 28800 |
+--------------------------+-------+
9 rows in set (0.00 sec)
Set with:
set global wait_timeout=3;
set global interactive_timeout=3;
(and also set in your configuration file, for when your server restarts)
But you're treating the symptoms instead of the underlying cause - why are the connections open? If the PHP script finished, shouldn't they close? Make sure your webserver is not using connection pooling...
add a comment |
configure your mysql-server by setting a shorter timeout on wait_timeout
and interactive_timeout
mysql> show variables like "%timeout%"
;
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| connect_timeout | 5 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| interactive_timeout | 28800 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 |
| wait_timeout | 28800 |
+--------------------------+-------+
9 rows in set (0.00 sec)
Set with:
set global wait_timeout=3;
set global interactive_timeout=3;
(and also set in your configuration file, for when your server restarts)
But you're treating the symptoms instead of the underlying cause - why are the connections open? If the PHP script finished, shouldn't they close? Make sure your webserver is not using connection pooling...
configure your mysql-server by setting a shorter timeout on wait_timeout
and interactive_timeout
mysql> show variables like "%timeout%"
;
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| connect_timeout | 5 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| interactive_timeout | 28800 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 |
| wait_timeout | 28800 |
+--------------------------+-------+
9 rows in set (0.00 sec)
Set with:
set global wait_timeout=3;
set global interactive_timeout=3;
(and also set in your configuration file, for when your server restarts)
But you're treating the symptoms instead of the underlying cause - why are the connections open? If the PHP script finished, shouldn't they close? Make sure your webserver is not using connection pooling...
answered Nov 14 '16 at 5:03
CharlieCharlie
1,90652755
1,90652755
add a comment |
add a comment |
Thanks for contributing an answer to Magento 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%2fmagento.stackexchange.com%2fquestions%2f70812%2fenterprise-refresh-index-sleeping-mysql-connection%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