php, mysql get stuck, memory problem?

[searain]

I have a FreeBSD unix box installed with mysql and InterBase database servers.

I don't have too many traffic or too many sites on the server when this problem happens (both happened in the evening, off traffic peak time). Many other time, the traffic would be much bigger.

The problem

1) the web sites using mysql database get stuck there and then after a long time, shows the error, no mysql connection.

2) the web sites using interbase database are working fine except, the php execution become slow.

The following are the steps I took to solve the problem.

1) run "apachectl restart" to restart the httpd.

It solves the problems, only temporary. the sites using mysql database are working again. But only after a few runs, these sites get stuck again, and shows the mysql no connection error again.

I shut down the hung in httpd process, but mysql server seems still stuck, all my mysql reqeusts are from httpd, I already killed the httpd process by restart apache, why the mysql server still stuck?

2) I then try to use "msqladmin shutdown" to shutdown the mysql server. It hung in there. I cannot shut down the mysql server. A guide says, "if the memory exhaust, you cannot shut down the server by using mysqladmin shutdown."

How can I have memory exhaust problem in a less traffic time, and the server only host a few active sites now, the other same server host hundreds sites and not have the memory problem?

3) So I run the "apachectl restart" to restart the httpd again to kill hung in httpd process. And then I run "msqladmin shutdown" again to shutdown the mysql server. This time, due to the hung in httpd process has been killed, the mysql is shut down.

It seems I have to kill both stuck httpd and mysql progress to free the mysql server, any expert explainations?

4) Then I run the "/usr/local/mysql/bin/mysqld_safe &", it restart the mysql server fresh.

Now it seems solve the problem. I open dozens windows to run the biggest traffic php pages which calls huge work from mysql at the same time. It seems never exhaust the memory and the php and mysql not get stuck again.

My questions:

1) Is this a server memory problem?

2) if it is a memory problem, it happens twice in a year, and both of them happens in the less traffic time, how could it be a memory problem?

3) is it a php problem or mysql problem? php has the reputation using a lot of memory at an instant second, right?

4) what expert explainations you can give?

5) it might be jsut my imageination, it seems after I "apachectl restart", "mysqladmin shutdown", "/usr/local/mysql/bin/mysqld_safe &", it doesn't just solve the problem, the execution become much faster than the regular speed.

is it because "never dead" php process stuck on the server exhuasting the memory or "never dead" mysql process stuck in the server exhuasting the memory?

Is it becase these never dead but stock process sitting there which slow the the server first and then until enough these kind never dead but stock process accumulating to the limit which exhuast the server memory?

Do I have to do "apachectl restart", "mysqladmin shutdown", "/usr/local/mysql/bin/mysqld_safe &" once a while to do some mysql house clean up works as a routine even before I run into the same problems again?

Thanks!

[DoctorWho]

Twice in a year??? That's not really that bad at all.

If that's the case I would explore work-arounds at this time.

Like re-starting the services during off-peak times.
If it's twice a year, you can do this Once a month, or Once a week.

I don't run your OS, but on 1 Windows machine of mine I have an issue where almost every couple of days the whole system would freeze up and a re-boot was in order, a cold/hard reboot at that.

I have simply filed this as some sort of memory-leak and I re-boot the server every night during off-peak hours using an automatic reboot tool.

sorry, that's about all I can suggest

[searain]

OK, I found out the traffic cause the trouble. My co-worker was running a e-mail tool.

I have 4 newsletter subscribers list. In each list, people sign up and make the select of the newsletter categories they want.

Once a while, I will send out the newsletter to subscribers based on what they sign up for.

Instead of sending mass e-mail out to all the newsletter subscribers, I run the loop to send individual e-mail to individual client, with the personal addressing such as Mr. Smith or Ms. Smith and with the contents they signed up for.

That is no problem, I grab the newseltter subscribers from the database and loop around to send e-mail one by one.

No huge traffic for MySQL so far.

Here comes the MySQL traffic, every time, looped once, an e-mail send out, I also record the email sent out date/time, the email receiver, content category etc. (becasue the newsletter subscribers may change their category later on, so I recorded the category of contents they selected at this time) in the database for the record.

The newsletter subscribers list could be over 1000, so I may run the loop 1000 times, every time a e-mail will be sent out and an record will be inserted to the database. All these happen in a few minutes.

Does this (loop 1000 times email out and 1000 times insert data) exhaust the memory of mysql server use? It is a simple mysql insert.

But my co-worker may make things worse by running two newsletter subscribers at the same time, meaning two php pages are looping 1000 times to send out e-mail and to insert the record at the same time. That would be a problem?

MySQL can handle much big database with hundreds of thousdands records and with much complicated sql statements. Why it cannot handle my simple insert one record work?

Or it is because running php mail() function 1000 times at the same time, this combine together exhaust the memory of mysql needed?

Even that is the case, should the memory be released after the loop done?

Or there are something I can do in terms of php coding or mysql calling to avoid the huge traffic which stuck the mysql in the same cases?