Should i convert to mysql?

Anon

Should i convert the following script to php+mysql, will the performance increase (less cpu, faster script execution).

pseudo-code:
- get environment variables
- read a text file
- search for a spefic record
- add or update (when record exists) record
- close text file

The text file contains a maximum of 20 lines (each line is about 30 characters long).

The scripts can sometimes get 10/20 hits per second.

I'm particular worried about that mysql locks the whole table when i update/delete/add a record, should i be worried about this?, cause with every hit my script does a update/delete/add. I'm also worried that connecting to a database will be slower the opening a small file.

[deleted]

MySQL will be faster.

"I'm particular worried about that mysql locks the whole table when i update/delete/add a record,"

MySQL does do table-level locking, but you conveniently forget that a textfile can only be edited by one person at a time. At 20 hits/second, I think you'll get quite a few problems with that.

And if you're still worried about performance, then use PostgreSQL.

Anon

Sorry, i think i did not explain the situation correctly. The script gets 20 hits per second. The script takes 1 parameter:

script.php?user=username

Then (in the script) i extract the parameter "user" and open the textfile which has the same name as the user parameter. For example:
script.php?user=twan
then i open "/path/to/twan.txt", but when:
script.php?user=vincent
then i open "/path/to/vincent.txt".

But when i convert everything to mysql, all data from all users will be put in the same table. This means that every hit has the same storage (aka table).

In this situation will it be good to convert it to mysql?

[deleted]

There are only a few highly specialized cases where textfiles are faster than a database, so moving to a database will probably be faster for you too.

And don't forget that using a database is much easier than using a flatfile. Databases offer much more functionality than a file can.

gerardg

Vincent-

And if you're still worried about performance, then use PostgreSQL.<

What do you base that on? Do you know of any third-party benchmarks/tests (links please 🙂? I'm just looking for someone else's assessment outside of MySQL, PostgreSQL, and PHP Builder. mysql.com says PostgreSQL's test are very biased; that Tim Purdue did his benchmarks with poor setups; they say they're fastest (if you know how to deal with table locks). PostgreSQL says they perform better. ?

Anon

fastest (if you know how to deal with table locks)

That's a silly assertion: one of the last things I want to do is have to write stuff targeted to one specific database, rather than just writing plain, generic, standard sql. Hmm, tying the user to a product by using nonstandard gimmicks, now who does that remind you of?

At any rate, Postgresql does far better than mysql when lots of updates are being done.

[deleted]

I don't have any thridparty evidence that PostgreSQL is faster than MySQL.

Both MYSQL and postgreSQL will never publish any docs that don't make their own product come out best.

The simple truth is that MySQL is blindingly fast at simple queries like
SELECT * FROM table WHERE this=that;
Maybe even faster than PostgreSQL.

But, try the same on a high-traffic site that uses MySQL as a backend for storing session data. Sessions generate tons of inserts/update/select queries in random order. MySQL doesn't know row-level-locking, so each insert or update locks the entire table and everything slows down to a crawl.

PostgreSQL with it's row-level-locking will keep on going.

MySQL has no functional indexing, so if you do a query like
SELECT * FROM table WHERE UPPER(this)='that';
you will force a sequencial scan.
PostgreSQL does support functional indexes, so if you make the proper index, it will run this query without a sequencial scan, which is faster.

But speed is not just 'how long does my query take', its also 'how much work do I have to do to get my data'.

MySQL has a very limited understanding of the SQL standard, and depends very much on the IQ of it's user. No subqueries means you sometimes have to run several queries and create temp tables to get what you need.

No transactions means that the programmer has to execute extra queries to undo all the changes he made when something goes wrong.

No referencial integrety means that if you want your data to be consistant, you'll have to run extra queries to make sure you are not referencing non-existing data, or creating orphan data.

The only thing that MySQL has got going for it (and the only reason it's still on my servers) is it's simplicity. It's easy to setup, easy to maintain and easy to use.

But I wouldn't trust my websites to depend on it.

gerardg

MySQL has implemented (or plans to) most of the points you mention, but of course it's new and hasn't been hammered as much.

The only thing that MySQL has got going for it (and the only reason it's still on my servers) is it's simplicity. It's easy to setup, easy to maintain and easy to use.<

So PostgreSQL isn't easy to setup, maintain, and use? 🙂 I'm about to try to install postgresql on my development box (W2K) - trying to figure out which route: cygwin, VC++ compile, 7.0 NT binaries, ...? For production I use redhat. Also, any helpful utilities for postgresql? I assume phpPgAdmin is comparable to phpMyAdmin.

[deleted]

"MySQL has implemented (or plans to) most of the points you mention, but of course it's new and hasn't been hammered as much."

So far I only see their 'todo' list grow.
Allmos everything that makes a database 'good' is in their 'things to do in the really near future' list.

"So PostgreSQL isn't easy to setup, maintain, and use?"

Not as simple as MySQL, where you can just untar a tarball and type 'mysqld'. Postgress requires a few more sysadmin operations.

"I'm about to try to install postgresql on my development box (W2K)"

Poor lad.
Why don't you just setup a linux box and do the tests on the same OS that you are developing on?

Anon

I definitely agree: postgresql, while not as totally simple as mysql to set up, is not all that hard. If you've ever installed and set up apache for virtual hosts, it's really no more complicated than that.