Hello,

I have recently hosted a site which is obviously developed its scripts very badly.

I have about 150 websites on this server , some of them are very big but this one alone has sometimes more than 40 sleeping connections to database.

I am going to look inside the script and help the owner in optimizing his code.

Anyone know what should I expect to find? (which is wrong? i.e what type of instruction etc.)

Thank you
Mac


| 4190577 | se_1 | localhost | se_1 | Sleep | 491 | | |
| 4191833 | se_1 | localhost | se_1 | Sleep | 253 | | |
| 4192337 | se_1 | localhost | se_1 | Sleep | 50 | | |
| 4192358 | se_1 | localhost | se_1 | Sleep | 136 | | |
| 4192363 | se_1 | localhost | se_1 | Sleep | 141 | | |
| 4192364 | se_1 | localhost | se_1 | Sleep | 4 | | |
| 4192371 | im_1 | localhost | im_1 | Sleep | 140 | | |
| 4192377 | se_1 | localhost | se_1 | Sleep | 104 | | |
| 4192405 | se_1 | localhost | se_1 | Sleep | 135 | | |
| 4192411 | se_1 | localhost | se_1 | Sleep | 124 | | |
| 4192448 | se_1 | localhost | se_1 | Sleep | 14 | | |
| 4192459 | se_1 | localhost | se_1 | Sleep | 125 | | |
| 4192463 | se_1 | localhost | se_1 | Sleep | 16 | | |
| 4192552 | se_1 | localhost | se_1 | Sleep | 105 | | |
| 4192587 | se_1 | localhost | se_1 | Sleep | 100 | | |
| 4192607 | se_1 | localhost | se_1 | Sleep | 95 | | |
| 4192618 | se_1 | localhost | se_1 | Sleep | 94 | | |
| 4192622 | se_1 | localhost | se_1 | Sleep | 94 | | |
| 4192671 | se_1 | localhost | se_1 | Sleep | 84 | | |
| 4192688 | ty_2 | localhost | ty_2 | Sleep | 80 | | |
| 4192698 | se_1 | localhost | se_1 | Sleep | 70 | | |
| 4192719 | se_1 | localhost | se_1 | Sleep | 75 | | |
| 4192725 | se_1 | localhost | se_1 | Sleep | 74 | | |
| 4192745 | se_1 | localhost | se_1 | Sleep | 70 | | |
| 4192749 | se_1 | localhost | se_1 | Sleep | 69 | | |
| 4192793 | se_1 | localhost | se_1 | Sleep | 64 | | |
| 4192801 | se_1 | localhost | se_1 | Sleep | 62 | | |
| 4192816 | se_1 | localhost | se_1 | Sleep | 60 | | |
| 4192845 | ty_2 | localhost | ty_2 | Sleep | 56 | | |
| 4192853 | se_1 | localhost | se_1 | Sleep | 54 | | |
| 4192891 | se_1 | localhost | se_1 | Sleep | 45 | | |
| 4192895 | se_1 | localhost | se_1 | Sleep | 44 | | |
| 4192911 | se_1 | localhost | se_1 | Sleep | 38 | | |
| 4192940 | se_1 | localhost | se_1 | Sleep | 6 | | |
| 4192944 | se_1 | localhost | se_1 | Sleep | 23 | | |
| 4192975 | se_1 | localhost | se_1 | Sleep | 27 | | |
| 4192983 | se_1 | localhost | se_1 | Sleep | 26 | | |
| 4192987 | se_1 | localhost | se_1 | Sleep | 25 | | |
| 4192998 | se_1 | localhost | se_1 | Sleep | 23 | | |
| 4193008 | se_1 | localhost | se_1 | Sleep | 20 | | |
| 4193009 | se_1 | localhost | se_1 | Sleep | 20 | | |
| 4193036 | se_1 | localhost | se_1 | Sleep | 15 | | |
| 4193039 | se_1 | localhost | se_1 | Sleep | 14 | | |
| 4193076 | se_1 | localhost | se_1 | Sleep | 7 | | |

You need to analyze where the scripts are dying without closing. Perhaps theres no error trap routine? or just lazy programming.

If the website in question uses PHP, you might want to check whether the PHP scripts connect to the database via mysql_pconnect() (http://php.net/mysql_pconnect) or mysql_connect (http://php.net/mysql_connect). The first one is for persistent connections. Those connections cannot be closed with mysql_close() (http://php.net/mysql_close). They just lie there after the script has finished. That's an advantage if you're on a dedicated server with one large website which connects to the same database over and over again. However, in a shared hosting environment, persistent connections can be a huge problem.

Another possible situation: The user uses mysql_connect() (non-persistent connections) but forgets to close the connection (mysql_close()) after he's done using it.

--
Jonas Koch Bentzen

http://findhim.com/

Another point, search engines crawl sites, and if they dont do it correctly, it can lead to the above or much worse. You need to define a robots.txt to exclude them from folders with a lot of php files.

Anyway, the sleep command in php and perl usually ends up taking more cpu than any other command, either get a process monitoring system to kill them after a certain time, or ask your customer to refrain from using sleep - it consumes too much cpu - see my TOS.

You can usually avoid sleep by using cron or some other time-triggering software.

how can I see the processes on my server??

what *nix command?

thanks

ps aux
top

Thank you everyone,

It seems his scripts have used normal mysql_connect but he has not closed his connections.

It's strange that I have many other sites which have not closed musql connections either but never make such problems.

Again thank you for your help.

Mac