mysql processes not cleanedup.

Ask your questions regarding TimeTrex installation here.
Locked
andrewbauman
Posts: 43
Joined: Mon Jan 23, 2012 9:40 am

mysql processes not cleanedup.

Post by andrewbauman »

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.
Andrew Bauman
shaunw
Posts: 7839
Joined: Tue Sep 19, 2006 2:22 pm

Re: mysql processes not cleanedup.

Post by shaunw »

How many connections are you talking about?
andrewbauman
Posts: 43
Joined: Mon Jan 23, 2012 9:40 am

Re: mysql processes not cleanedup.

Post by andrewbauman »

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.
Andrew Bauman
andrewbauman
Posts: 43
Joined: Mon Jan 23, 2012 9:40 am

Re: mysql processes not cleanedup.

Post by andrewbauman »

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
Andrew Bauman
shaunw
Posts: 7839
Joined: Tue Sep 19, 2006 2:22 pm

Re: mysql processes not cleanedup.

Post by shaunw »

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?
andrewbauman
Posts: 43
Joined: Mon Jan 23, 2012 9:40 am

Re: mysql processes not cleanedup.

Post by andrewbauman »

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?
Andrew Bauman
shaunw
Posts: 7839
Joined: Tue Sep 19, 2006 2:22 pm

Re: mysql processes not cleanedup.

Post by shaunw »

andrewbauman
Posts: 43
Joined: Mon Jan 23, 2012 9:40 am

Re: mysql processes not cleanedup.

Post by andrewbauman »

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 ?
Andrew Bauman
shaunw
Posts: 7839
Joined: Tue Sep 19, 2006 2:22 pm

Re: mysql processes not cleanedup.

Post by shaunw »

In timetrex.ini.php, you can put this line under the [database] section:

Code: Select all

persistent_connections = 0
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.
andrewbauman
Posts: 43
Joined: Mon Jan 23, 2012 9:40 am

Re: mysql processes not cleanedup.

Post by andrewbauman »

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.
Andrew Bauman
andrewbauman
Posts: 43
Joined: Mon Jan 23, 2012 9:40 am

Re: mysql processes not cleanedup.

Post by andrewbauman »

Hi to All
When all is said and done then
switching to a RHEL 6.xx server package solved the issue.
Andrew Bauman
Locked