PostgreSQL vs MySQL

Anon

Hello all,

My site receives about 200,000 page views a day, and stores about 10,000 BLOB fields.
Currently, I am running MySQL, but I am starting to run into some problems (high CPU usage, server crashing)
I was wondering if MySQL was built to support this many users, and whether I should migrate to PostgreSQL to handle more users (my site will continue to expand in both the # of Blob entries and concurrent users)

Thanks,
Jeremy Ferwerda

Anon

I can't give you a strait yes/no on whether postgresql can help or not, but I would recommend evaluating whether you really need that many blobs (I'm assuming you ment blob records and not fields).

While that is a very big number, and may be harder to manage if it is not in a db, it may be what's causing your problems. I personally go for a table of paths to files so I don't need to grab blobs from the db and can instead just store the path to the element as it relates to documentroot and handle it as a normal http retrieve.

I think you'll find you need to change all of your queries if you switch db's because although pgsql supports blob types, it uses special functions to do so.

Anon

My site is an archive of sheet music for various instruments. I need to store them as blob fields, as I get about 30 new submissions each day that I must approve before I put them into the archive. Also, the sheet music needs to have the ability to be edited from a browser.

Doesnt the PostgreSQL text field correspond to blobs?

Anon

That's not my understanding. It could probably be used for small blobs, but there are other true blob'ish things available that are unlimited size.

As for a web interface, to change your site to use a database of paths, just create an admin script that would check for orphaned files or db records by loading all the files and comparing them against the db.

Once you get rid of all the orphans, you can write code for deleting the file from the file system or updating it, etc. It's just a much faster way to display the data at the cost of more complex admin scirpts.

Anon

Good Idea, I'll get started on that right away. Should I create a temporary table to hold unauthorized (pending approval) sheet music, or just use files for those as well?

Anon

Yea, good idea on the pending table. Because queries to that table are very low, just blob them there first then copy them to the file system after approval.

Anon

Forget the blobs AND the copying. Just have them go to a temporary directory first. Then, when you approve them, just rename them to the correct location. Bingo, problem solved, very low cpu overhead compared to copying all those bytes (and the copy still has to create a directory entry for the new file, so there's no overhead being saved there.)