HI
I have a issue with the timetrex 5.2.2 using mysql 5.0.95
the database becomes not available and investigations showed that the process list is filled with many sleeping connections.
is this a known issue?
I would like to stay with mysql
I know that you do like postgre better.
mysql processes not cleanedup.
-
- Posts: 43
- Joined: Mon Jan 23, 2012 9:40 am
mysql processes not cleanedup.
Andrew Bauman
Re: mysql processes not cleanedup.
How many connections are you talking about?
-
- Posts: 43
- Joined: Mon Jan 23, 2012 9:40 am
Re: mysql processes not cleanedup.
well the first time I tested with Debug set to true I got my whole server froze
so I made a separate user for TimeTrex and set the max connections to 12
but the database down for maint message came up.
so I made a separate user for TimeTrex and set the max connections to 12
but the database down for maint message came up.
Andrew Bauman
-
- Posts: 43
- Joined: Mon Jan 23, 2012 9:40 am
Re: mysql processes not cleanedup.
My Current Process list.
mysql> SHOW PROCESSLIST;
+------+----------+-------------------+------------------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+----------+-------------------+------------------+---------+------+-------+------------------+
| 1211 | andrew | 10.8.0.6:4630 | NULL | Sleep | 952 | | NULL |
| 1538 | timetrex | localhost | timetrextest | Sleep | 7138 | | NULL |
| 1556 | timetrex | localhost | timetrextest | Sleep | 6690 | | NULL |
| 1557 | timetrex | localhost | timetrextest | Sleep | 6687 | | NULL |
| 1558 | timetrex | localhost | timetrextest | Sleep | 6657 | | NULL |
| 1559 | timetrex | localhost | timetrextest | Sleep | 6657 | | NULL |
| 1569 | timetrex | localhost | timetrextest | Sleep | 6438 | | NULL |
| 1570 | timetrex | localhost | timetrextest | Sleep | 6437 | | NULL |
| 1571 | timetrex | localhost | timetrextest | Sleep | 6431 | | NULL |
| 1572 | timetrex | localhost | timetrextest | Sleep | 6430 | | NULL |
| 1601 | timetrex | localhost | timetrextest | Sleep | 5482 | | NULL |
| 1602 | timetrex | localhost | timetrextest | Sleep | 5476 | | NULL |
| 1603 | timetrex | localhost | timetrextest | Sleep | 5468 | | NULL |
| 1604 | timetrex | localhost | timetrextest | Sleep | 5467 | | NULL |
| 1607 | timetrex | localhost | timetrextest | Sleep | 5404 | | NULL |
| 1608 | timetrex | localhost | timetrextest | Sleep | 5403 | | NULL |
| 1609 | timetrex | localhost | timetrextest | Sleep | 5398 | | NULL |
| 1610 | timetrex | localhost | timetrextest | Sleep | 5397 | | NULL |
| 1613 | timetrex | localhost | timetrextest | Sleep | 5377 | | NULL |
| 1614 | timetrex | localhost | timetrextest | Sleep | 5376 | | NULL |
| 1615 | timetrex | localhost | timetrextest | Sleep | 5376 | | NULL |
| 1617 | timetrex | localhost | timetrextest | Sleep | 5341 | | NULL |
| 1618 | timetrex | localhost | timetrextest | Sleep | 5341 | | NULL |
| 1619 | timetrex | localhost | timetrextest | Sleep | 5339 | | NULL |
| 1620 | timetrex | localhost | timetrextest | Sleep | 5339 | | NULL |
| 1621 | timetrex | localhost | timetrextest | Sleep | 5338 | | NULL |
| 1622 | timetrex | localhost | timetrextest | Sleep | 5338 | | NULL |
| 1623 | timetrex | localhost | timetrextest | Sleep | 5336 | | NULL |
| 1624 | timetrex | localhost | timetrextest | Sleep | 5336 | | NULL |
| 1627 | timetrex | localhost | timetrextest | Sleep | 5330 | | NULL |
| 1628 | timetrex | localhost | timetrextest | Sleep | 5329 | | NULL |
| 1629 | timetrex | localhost | timetrextest | Sleep | 5328 | | NULL |
| 1630 | timetrex | localhost | timetrextest | Sleep | 5328 | | NULL |
| 1631 | timetrex | localhost | timetrextest | Sleep | 5327 | | NULL |
| 1632 | timetrex | localhost | timetrextest | Sleep | 5324 | | NULL |
| 1633 | timetrex | localhost | timetrextest | Sleep | 5324 | | NULL |
| 1634 | timetrex | localhost | timetrextest | Sleep | 5323 | | NULL |
| 1635 | timetrex | localhost | timetrextest | Sleep | 5322 | | NULL |
| 1636 | timetrex | localhost | timetrextest | Sleep | 5313 | | NULL |
| 1637 | timetrex | localhost | timetrextest | Sleep | 5313 | | NULL |
| 1638 | timetrex | localhost | timetrextest | Sleep | 5311 | | NULL |
| 1639 | timetrex | localhost | timetrextest | Sleep | 5311 | | NULL |
| 1640 | timetrex | localhost | timetrextest | Sleep | 5311 | | NULL |
| 1641 | timetrex | localhost | timetrextest | Sleep | 5305 | | NULL |
| 1642 | timetrex | localhost | timetrextest | Sleep | 5299 | | NULL |
| 1643 | timetrex | localhost | timetrextest | Sleep | 5298 | | NULL |
| 1644 | timetrex | localhost | timetrextest | Sleep | 5297 | | NULL |
| 1645 | timetrex | localhost | timetrextest | Sleep | 5287 | | NULL |
| 1646 | timetrex | localhost | timetrextest | Sleep | 5287 | | NULL |
| 1649 | timetrex | localhost | timetrextest | Sleep | 5248 | | NULL |
| 1650 | timetrex | localhost | timetrextest | Sleep | 5247 | | NULL |
| 1651 | timetrex | localhost | timetrextest | Sleep | 5246 | | NULL |
| 1652 | timetrex | localhost | timetrextest | Sleep | 5243 | | NULL |
| 1653 | timetrex | localhost | timetrextest | Sleep | 5241 | | NULL |
| 1654 | timetrex | localhost | timetrextest | Sleep | 5228 | | NULL |
| 1655 | timetrex | localhost | timetrextest | Sleep | 5225 | | NULL |
| 1656 | timetrex | localhost | timetrextest | Sleep | 5225 | | NULL |
| 1657 | timetrex | localhost | timetrextest | Sleep | 5224 | | NULL |
| 1658 | timetrex | localhost | timetrextest | Sleep | 5223 | | NULL |
| 1661 | timetrex | localhost | timetrextest | Sleep | 5178 | | NULL |
| 1664 | timetrex | localhost | timetrextest | Sleep | 5176 | | NULL |
| 1665 | timetrex | localhost | timetrextest | Sleep | 5170 | | NULL |
| 1666 | timetrex | localhost | timetrextest | Sleep | 5170 | | NULL |
| 1690 | andrew | 10.8.0.6:1676 | timetrextest | Sleep | 4634 | | NULL |
| 1714 | andrew | 10.8.0.6:1986 | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
| 1738 | timetrex | localhost | timetrextest | Sleep | 3674 | | NULL |
| 1739 | timetrex | localhost | timetrextest | Sleep | 3673 | | NULL |
| 1740 | timetrex | localhost | timetrextest | Sleep | 3672 | | NULL |
| 1741 | timetrex | localhost | timetrextest | Sleep | 3672 | | NULL |
| 1742 | timetrex | localhost | timetrextest | Sleep | 3667 | | NULL |
| 1743 | timetrex | localhost | timetrextest | Sleep | 3664 | | NULL |
| 1917 | timetrex | localhost | timetrextest | Sleep | 2964 | | NULL |
| 1918 | timetrex | localhost | timetrextest | Sleep | 2963 | | NULL |
| 1940 | root | 192.168.0.12:1161 | dbprojectmanager | Sleep | 0 | | NULL |
| 2001 | timetrex | localhost | timetrextest | Sleep | 945 | | NULL |
| 2002 | timetrex | localhost | timetrextest | Sleep | 926 | | NULL |
| 2003 | timetrex | localhost | timetrextest | Sleep | 924 | | NULL |
| 2004 | timetrex | localhost | timetrextest | Sleep | 924 | | NULL |
| 2005 | timetrex | localhost | timetrextest | Sleep | 924 | | NULL |
| 2006 | timetrex | localhost | timetrextest | Sleep | 919 | | NULL |
| 2007 | timetrex | localhost | timetrextest | Sleep | 908 | | NULL |
| 2008 | timetrex | localhost | timetrextest | Sleep | 907 | | NULL |
| 2009 | timetrex | localhost | timetrextest | Sleep | 906 | | NULL |
| 2010 | timetrex | localhost | timetrextest | Sleep | 905 | | NULL |
| 2011 | timetrex | localhost | timetrextest | Sleep | 903 | | NULL |
| 2012 | timetrex | localhost | timetrextest | Sleep | 901 | | NULL |
+------+----------+-------------------+------------------+---------+------+-------+------------------+
86 rows in set
mysql> SHOW PROCESSLIST;
+------+----------+-------------------+------------------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+----------+-------------------+------------------+---------+------+-------+------------------+
| 1211 | andrew | 10.8.0.6:4630 | NULL | Sleep | 952 | | NULL |
| 1538 | timetrex | localhost | timetrextest | Sleep | 7138 | | NULL |
| 1556 | timetrex | localhost | timetrextest | Sleep | 6690 | | NULL |
| 1557 | timetrex | localhost | timetrextest | Sleep | 6687 | | NULL |
| 1558 | timetrex | localhost | timetrextest | Sleep | 6657 | | NULL |
| 1559 | timetrex | localhost | timetrextest | Sleep | 6657 | | NULL |
| 1569 | timetrex | localhost | timetrextest | Sleep | 6438 | | NULL |
| 1570 | timetrex | localhost | timetrextest | Sleep | 6437 | | NULL |
| 1571 | timetrex | localhost | timetrextest | Sleep | 6431 | | NULL |
| 1572 | timetrex | localhost | timetrextest | Sleep | 6430 | | NULL |
| 1601 | timetrex | localhost | timetrextest | Sleep | 5482 | | NULL |
| 1602 | timetrex | localhost | timetrextest | Sleep | 5476 | | NULL |
| 1603 | timetrex | localhost | timetrextest | Sleep | 5468 | | NULL |
| 1604 | timetrex | localhost | timetrextest | Sleep | 5467 | | NULL |
| 1607 | timetrex | localhost | timetrextest | Sleep | 5404 | | NULL |
| 1608 | timetrex | localhost | timetrextest | Sleep | 5403 | | NULL |
| 1609 | timetrex | localhost | timetrextest | Sleep | 5398 | | NULL |
| 1610 | timetrex | localhost | timetrextest | Sleep | 5397 | | NULL |
| 1613 | timetrex | localhost | timetrextest | Sleep | 5377 | | NULL |
| 1614 | timetrex | localhost | timetrextest | Sleep | 5376 | | NULL |
| 1615 | timetrex | localhost | timetrextest | Sleep | 5376 | | NULL |
| 1617 | timetrex | localhost | timetrextest | Sleep | 5341 | | NULL |
| 1618 | timetrex | localhost | timetrextest | Sleep | 5341 | | NULL |
| 1619 | timetrex | localhost | timetrextest | Sleep | 5339 | | NULL |
| 1620 | timetrex | localhost | timetrextest | Sleep | 5339 | | NULL |
| 1621 | timetrex | localhost | timetrextest | Sleep | 5338 | | NULL |
| 1622 | timetrex | localhost | timetrextest | Sleep | 5338 | | NULL |
| 1623 | timetrex | localhost | timetrextest | Sleep | 5336 | | NULL |
| 1624 | timetrex | localhost | timetrextest | Sleep | 5336 | | NULL |
| 1627 | timetrex | localhost | timetrextest | Sleep | 5330 | | NULL |
| 1628 | timetrex | localhost | timetrextest | Sleep | 5329 | | NULL |
| 1629 | timetrex | localhost | timetrextest | Sleep | 5328 | | NULL |
| 1630 | timetrex | localhost | timetrextest | Sleep | 5328 | | NULL |
| 1631 | timetrex | localhost | timetrextest | Sleep | 5327 | | NULL |
| 1632 | timetrex | localhost | timetrextest | Sleep | 5324 | | NULL |
| 1633 | timetrex | localhost | timetrextest | Sleep | 5324 | | NULL |
| 1634 | timetrex | localhost | timetrextest | Sleep | 5323 | | NULL |
| 1635 | timetrex | localhost | timetrextest | Sleep | 5322 | | NULL |
| 1636 | timetrex | localhost | timetrextest | Sleep | 5313 | | NULL |
| 1637 | timetrex | localhost | timetrextest | Sleep | 5313 | | NULL |
| 1638 | timetrex | localhost | timetrextest | Sleep | 5311 | | NULL |
| 1639 | timetrex | localhost | timetrextest | Sleep | 5311 | | NULL |
| 1640 | timetrex | localhost | timetrextest | Sleep | 5311 | | NULL |
| 1641 | timetrex | localhost | timetrextest | Sleep | 5305 | | NULL |
| 1642 | timetrex | localhost | timetrextest | Sleep | 5299 | | NULL |
| 1643 | timetrex | localhost | timetrextest | Sleep | 5298 | | NULL |
| 1644 | timetrex | localhost | timetrextest | Sleep | 5297 | | NULL |
| 1645 | timetrex | localhost | timetrextest | Sleep | 5287 | | NULL |
| 1646 | timetrex | localhost | timetrextest | Sleep | 5287 | | NULL |
| 1649 | timetrex | localhost | timetrextest | Sleep | 5248 | | NULL |
| 1650 | timetrex | localhost | timetrextest | Sleep | 5247 | | NULL |
| 1651 | timetrex | localhost | timetrextest | Sleep | 5246 | | NULL |
| 1652 | timetrex | localhost | timetrextest | Sleep | 5243 | | NULL |
| 1653 | timetrex | localhost | timetrextest | Sleep | 5241 | | NULL |
| 1654 | timetrex | localhost | timetrextest | Sleep | 5228 | | NULL |
| 1655 | timetrex | localhost | timetrextest | Sleep | 5225 | | NULL |
| 1656 | timetrex | localhost | timetrextest | Sleep | 5225 | | NULL |
| 1657 | timetrex | localhost | timetrextest | Sleep | 5224 | | NULL |
| 1658 | timetrex | localhost | timetrextest | Sleep | 5223 | | NULL |
| 1661 | timetrex | localhost | timetrextest | Sleep | 5178 | | NULL |
| 1664 | timetrex | localhost | timetrextest | Sleep | 5176 | | NULL |
| 1665 | timetrex | localhost | timetrextest | Sleep | 5170 | | NULL |
| 1666 | timetrex | localhost | timetrextest | Sleep | 5170 | | NULL |
| 1690 | andrew | 10.8.0.6:1676 | timetrextest | Sleep | 4634 | | NULL |
| 1714 | andrew | 10.8.0.6:1986 | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
| 1738 | timetrex | localhost | timetrextest | Sleep | 3674 | | NULL |
| 1739 | timetrex | localhost | timetrextest | Sleep | 3673 | | NULL |
| 1740 | timetrex | localhost | timetrextest | Sleep | 3672 | | NULL |
| 1741 | timetrex | localhost | timetrextest | Sleep | 3672 | | NULL |
| 1742 | timetrex | localhost | timetrextest | Sleep | 3667 | | NULL |
| 1743 | timetrex | localhost | timetrextest | Sleep | 3664 | | NULL |
| 1917 | timetrex | localhost | timetrextest | Sleep | 2964 | | NULL |
| 1918 | timetrex | localhost | timetrextest | Sleep | 2963 | | NULL |
| 1940 | root | 192.168.0.12:1161 | dbprojectmanager | Sleep | 0 | | NULL |
| 2001 | timetrex | localhost | timetrextest | Sleep | 945 | | NULL |
| 2002 | timetrex | localhost | timetrextest | Sleep | 926 | | NULL |
| 2003 | timetrex | localhost | timetrextest | Sleep | 924 | | NULL |
| 2004 | timetrex | localhost | timetrextest | Sleep | 924 | | NULL |
| 2005 | timetrex | localhost | timetrextest | Sleep | 924 | | NULL |
| 2006 | timetrex | localhost | timetrextest | Sleep | 919 | | NULL |
| 2007 | timetrex | localhost | timetrextest | Sleep | 908 | | NULL |
| 2008 | timetrex | localhost | timetrextest | Sleep | 907 | | NULL |
| 2009 | timetrex | localhost | timetrextest | Sleep | 906 | | NULL |
| 2010 | timetrex | localhost | timetrextest | Sleep | 905 | | NULL |
| 2011 | timetrex | localhost | timetrextest | Sleep | 903 | | NULL |
| 2012 | timetrex | localhost | timetrextest | Sleep | 901 | | NULL |
+------+----------+-------------------+------------------+---------+------+-------+------------------+
86 rows in set
Andrew Bauman
Re: mysql processes not cleanedup.
It likely has more to do with Apache than anything else. How many apache processes/threads do you allow to be running at any time?
-
- Posts: 43
- Joined: Mon Jan 23, 2012 9:40 am
Re: mysql processes not cleanedup.
I have done some searching.
those connections are all sleeping
but timetrex opens new connections on every page.
Is it possible that there is a missing close connection command?
in the database connector?
those connections are all sleeping
but timetrex opens new connections on every page.
Is it possible that there is a missing close connection command?
in the database connector?
Andrew Bauman
Re: mysql processes not cleanedup.
Please see this topic:
http://forums.timetrex.com/viewtopic.ph ... tion#p1889
http://forums.timetrex.com/viewtopic.ph ... tion#p1889
-
- Posts: 43
- Joined: Mon Jan 23, 2012 9:40 am
Re: mysql processes not cleanedup.
Thanks very much.
I am confused though.
on that thread you state that persistent connections are explicit in timetrex.ini.php
But I am not finding any lines to that effect.
did you perhaps mean php.ini ?
I am confused though.
on that thread you state that persistent connections are explicit in timetrex.ini.php
But I am not finding any lines to that effect.
did you perhaps mean php.ini ?
Andrew Bauman
Re: mysql processes not cleanedup.
In timetrex.ini.php, you can put this line under the [database] section:
Though that doesn't guarantee anything will actually change in your case.
The real problem is likely the number of threads/processes you allow Apache to keep open at any given time. PHP/Apache handle the database connection pooling itself, if you tell Apache to allow 500 processes and let them sit idle indefinitely, you need to configure MySQL to allow 500 connections as well. If you are site isn't busy its a huge waste of resources. Optimizing Apache to better handle the number of connections you require is probably the better approach and would result in an overall performance improvement too.
Code: Select all
persistent_connections = 0
The real problem is likely the number of threads/processes you allow Apache to keep open at any given time. PHP/Apache handle the database connection pooling itself, if you tell Apache to allow 500 processes and let them sit idle indefinitely, you need to configure MySQL to allow 500 connections as well. If you are site isn't busy its a huge waste of resources. Optimizing Apache to better handle the number of connections you require is probably the better approach and would result in an overall performance improvement too.
-
- Posts: 43
- Joined: Mon Jan 23, 2012 9:40 am
Re: mysql processes not cleanedup.
in httpd.conf
the persistent connection is set to off.
I have threads per child at 25
still get the issue of many sleeping processes in mysql.
the persistent connection is set to off.
I have threads per child at 25
still get the issue of many sleeping processes in mysql.
Andrew Bauman
-
- Posts: 43
- Joined: Mon Jan 23, 2012 9:40 am
Re: mysql processes not cleanedup.
Hi to All
When all is said and done then
switching to a RHEL 6.xx server package solved the issue.
When all is said and done then
switching to a RHEL 6.xx server package solved the issue.
Andrew Bauman