mysql and php, too heavy traffic to the mysql cause it up and down?

searain

I have mysql running on FreeBSD to support several web sites (all php sites).

This set up is running fine for 1 year or so.

And in the recent a few days, I didn't do any changes to these sites and mysql database.

Suddenly, I have the mysql connection problems.

It seems mysql only accept very few connections, and if more connection requests come after that, mysql will not create the connection for the request.

so all these php pages with mysql connection request will create error and hung in there.

I have to restart the apache to kill these php process, but still, the problem will come again, a few runs later.

these connections could be php calling from web site, or could be my desktop client connect to mysql server.

it all creats the same error, only a few connections allowed, the connection after that, will be rejected.

What is the problem?

Does MySQL needs some kind house cleanning work after a few months?

searain

I shut down the mysql and restart the mysql.

It seems solved the problem.

But I need to know what is the problem. Becasue right now, I only run mysql slightly on my server.

I have quite a few heavy load InterBase/FireBird sites to transfer to mysql sites in the near future. And a few more crucial sites i will build up on mysql.

And these sites downtime should be controlled to the min.

My Questions will be

1) What is the cause of the problem?

2) How can I prevent it happen again?

3) Or this kind problem is unavoidable, and I have to personally monitor the mysql running status, and detect this kind case, and shutdown the database and restart it again?

4) I have run interbase/firebird for years without running to this kind problem. I am thinking about switching to mysql because that mysql has more popularity. And even the interbase/firebird is a good database server, not so many hosting companies are supporting it. and not so many open source system are using interbase/firebird. But if mysql will give me this kind problem like running for a while and suddenly stop working that i have manually shut it down and restart it to get it back to work. That will make me think again if I should transfer from interbase to mysql. any opinions?

Thanks!

Sxooter

Unfortunately, under enough load, MySQL will usually go non-responsive and eventually fail.

PostgreSQL generally requires a far greater load to become unresponsive, and in all situations I've seen it in, once the load is removed the database becomes performant again.

searain

Originally posted by Sxooter
Unfortunately, under enough load, MySQL will usually go non-responsive and eventually fail.

PostgreSQL generally requires a far greater load to become unresponsive, and in all situations I've seen it in, once the load is removed the database becomes performant again.

Actually, later on, I found out the traffic is not heavy at all. And MySQL projects in the world already handles traffic much bigger than my project. But still, after run the mysql (light traffic) for 1 year, I run into the problems that mysql got stuck there and only open few connections. After I restart it, it is running fine.

Could it be some other problems rather than traffic? Is it because MySQL needs some house cleanning work once a while?

Could firebird handles heavy load better? I was using InterBase/Firebird, the the load is pretty heavy. I had no problems in that.

But due to the lack of hosting support, I have to switch from FireBird to MySQL or PostregSQL.

Now I just did some projects in MySQL.

And my next project (may have heavy load traffic) will be on MySQL. I may not have time now to learn the PostregSQL and use it for the next project. But if it is possible, I would keep the option open that transfer that project from MySQL to PostregSQL later on if necessary (such as if mysql cannot handle that traffic etc.)

So my questions now is

1) Will it be difficult to transfer MySQL/php to PostregSQL/php?

2) What issues I should be careful if I want to transfer mysql to postregsql later on? (Transfer from FireBird to MySQL, I knew such as date format is the code I need to re-write, something like that what is the difference part between mysql and postregsql in the coding.)

Sxooter

I would assign load handling a rank of Postgresql, then firebird, then mysql. That's parallel load. For fast batch processing, mysql is a fine database.

Anyway, if you avoid MySQLisms, the transfer over from it to either firebird or postgresql should be pretty simple.

Avoide things like enum, assuming text searches are case insensitive, using the merge command, and doing a lot of table locking and such. Also, you'll have to learn to think a little differently in postgresql. I'd subscribe to the pgsql-general list, and look up the general bits archive on the web.

Avoid using a lot of count(*) or other things that demand a seq scan in postgresql. Get used to sequences (aliased under the serial type in create table)

and lastly, learn the \ psql commands. They are much easier to type once you get to know them, because they're so short, but a bit less intuitive.

Sxooter

P.s., in a lot of those "real world" big applications, MySQL is heavily cached. Almost nothing you view on slashdot, for instance, is actually coming live out of a database. It's all generated every X seconds and stashed in a cache.

Which is typical for most high traffic, mostly READ access sites, not just slashdot and / or MySQL.