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
Why does BitLocker not use RSA?
Why complex landing gears are used instead of simple, reliable and light weight muscle wire or shape memory alloys?
How to resize main filesystem
Why can't fire hurt Daenerys but it did to Jon Snow in season 1?
malloc in main() or malloc in another function: allocating memory for a struct and its members
Why is there so little support for joining EFTA in the British parliament?
How to ask rejected full-time candidates to apply to teach individual courses?
Is this Half dragon Quaggoth Balanced
Russian equivalents of おしゃれは足元から (Every good outfit starts with the shoes)
Why are current probes so expensive?
Did John Wesley plagiarize Matthew Henry...?
By what mechanism was the 2017 UK General Election called?
Why did Bronn offer to be Tyrion Lannister's champion in trial by combat?
What did Turing mean when saying that "machines cannot give rise to surprises" is due to a fallacy?
Vertical ranges of Column Plots in 12
New Order #6: Easter Egg
Should man-made satellites feature an intelligent inverted "cow catcher"?
What is "Lambda" in Heston's original paper on stochastic volatility models?
Is there a spell that can create a permanent fire?
Understanding piped commands in GNU/Linux
Problem with display of presentation
Is a copyright notice with a non-existent name be invalid?
Does the transliteration of 'Dravidian' exist in Hindu scripture? Does 'Dravida' refer to a Geographical area or an ethnic group?
What can we say about Classical Nahuatl <z>?
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♦ 4 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♦ 4 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♦ 4 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♦ 4 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