php & pgsql

[Anon]

Hi guys, I have a dilema that I'm hoping you guys can help me with. OK, I'm in charge of figuring out what OS, DB and Lang. to use for this project that will involve 60k-100k hits per day. I've essentially decided on php, but I need some advice in regards to OS and DB. I would love to use Linux/GNU with PGSQL, but I dont know if it can handle the traffic and such. Does php work well also in a windows environment?

Any help with this would be greatly appreciated.

[[deleted]]

Windows is never a good idea when you plan to get high-traffic. (btw 100k hits/day is not high traffic, there are 86k seconds in a day What's more important is how many hits you get per second during the busiest time)

I'd go for Linux (RH 6.2 or 7.1) and apache/php/pgsql.

[Anon]

PHP works quiet well under Win, if you accept to loose some functionnalities like built-in crypto, easy fs operations ...
Anyway, in your case I would go for MySQL for stability. But, if your hits peak at around 100 per sec, MySQL will be badly strained. So, either use MySQL on a phat comp, or use PG and be prepared to deal with crashes ...
Linux is a good choice for this combo.
HTH & good luck ;-)

[[deleted]]

PgSQL is better prepared to deal with crashes than MySQL, and I don't think either of them crashes more often than the other.

It's certainly nonsense to warn people to be ready for crashes if they use PgSQL.

In fact, under a high load I think PgSQL will be less likely to crash than MySQL, because of MySQL's table-level locking and thus queueing of queries, eating up resources during the lock.

[Anon]

I agree with table locks, I do not agree with crashes. A PG crash is harder to recover than a MSQL crash, partly because of the table lock that prevents index corruption, and partly because MySQL is more 'primitive' than PG, so less things can go wrong.
IMO, if you do not have to use roll back then do not use PG.
PG scales up better than MySQL, that's true, but when both are approaching breaking point, I would trust MySQL to keep my data safe.

[[deleted]]

"but when both are approaching breaking point, I would trust MySQL to keep my data safe. "

I most certainly wouldn't.

Apart from the fact that PgSQL-breakingpoint is way beyond MySQL's breakingpoint, MySQL's lack of transactions means there is no transaction log to use for a recovery.

Once the table is damaged, you can only fix what is there and hope for the best.
With PgSQL you get transaction logs. that means that when the table gets damaged, you can restore the table from your backup, and use the transaction log to update your table to the last query before the crash.

That's much more reliable than fixing a corrupted table.

[Anon]

What you are saying is that because PG has in-built support for transactionnal operations, it is easier to recover for a crash. Maybe, if all goes according to plan.
MySQL has no in-built support for transactionnal operations, so crashes caused by those operations are impossible in the first place (granted that you are only using ISAM tables).
MySQL is less likely to crash than PG. On the other hand, if the system crash (like not just the db), yes, it may be easier to recover PG.

[[deleted]]

"MySQL has no in-built support for transactionnal operations, so crashes caused by those operations are impossible in the first place"

MySQL has about one tenth of the functions of PgSQL, does that make MySQL 10 times more reliable?

I find that a very un-intelligent approach (no offense).

Like I said before, think MySQL and PgSQL are equally stable, but when put to a stress test, I'd always prefer PgSQL's approach.

Row-level locking means that hardly any select-queries will have to be queued during an insert.
MySQL will queue everything while an insert or update is taking place. That means that when the insert or update is completed, all the waiting selects are executed immediately, giving quite a high-load and thus a chance for a crash.

MySQL is a nice and well-performing database engine, but it's simply not something you want to use in a high-load situation.

[smarlowe]

I'm finding myself agreeing with Vincent here.

1: I've only had postgresql crash once or twice in my time using it (since 6.5.0) and all of those crashes occured under the older 6.5.x series.

2: pgsql has a WAL (write ahead log) system that pretty much means crashes, emergency powerdowns, or other bad things do NOT cause the corruption problems they once did.

3: Postgresql, on my Dually PIII at work can handle 100 simo transactions per second for hours on end without a single peep or failure.

The only time I've seen a crash prone postgresql installation, the same machine couldn't compile a kernel more than two or three times without a Sig11.

[kparker]

IMO, if you do not have to use roll back then do not use PG

Not so: even in the absense of transactions, PG supports a higher number of writes per second than does Mysql.