AniDB

Forgive my nosiness, but what database do you have running in the backend? Thinking about some of the tables, the number of rows and the sizes of the table indexes gave me a real headache, and I'm wondering if MySQL or Postgres can still handle that or if something big (read: expensive) is required...

Dunno what anidb uses but mysql can handle millions of rows without any problems, mostly ram that matters anyway once you start getting a large database. (If you have optimized queries and indexes of course.)
Problem with mysql without transaction support is that the tables break very easy though.

IIRC it is using PostgreSQL.

Nah, MySQL's lack of transaction support doesn't break the tables themselves. The worst thing that could happen is that a foreign key becomes meaningless (i.e. an entry for a file is deleted but the references to that file aren't removed from everyone's mylists). However, that is a problem that can be solved on the application level, and even if that happens, the tables are still not corrupt per se.

Anyhow, after wasting some more thinking time on this, you're probably right. It's unlikely that any given table would contain more then a couple million rows. But now I wonder how much disk space the indices actually consume...

Actually what I meant is that mysql tables get corrupted pretty easy, those of my friends running larger sites on mysql all have corruption issues now and then. (Not common but also too common to be regarded as safe compared to a transaction database.)
Guess I just wouldn't feel comfortable with a big site running on MyISAM tables.

AniDB is running on PostgreSQL and (not counting certain cronjobs which run over the entire db now and then) there are no performance issues.
All queries issued by the cgi are handled without any real delay.

About the size, anidb together with all indices takes about 400MB of hdd space atm. And it's growing rapidly )

Btw. i think mysql is a nice little proggy to play around with, but if you need a real database (and want an open source one), it's better to stick with postgresql.

BYe!
EXP

Actually what I meant is that mysql tables get corrupted pretty easy, those of my friends running larger sites on mysql all have corruption issues now and then.

i've encountered this problem myself, probably every 4-5 months or so, with my database (mysql). it randomly will either not be able to access a field, all the data will magically disappear, or something else of the like. after corruption, the database itself couldn't even be deleted. took about a year to discover that in fact, if the mysql server was simply restarted, all the data was back :/ i still have no idea why.

Sounds like your mysqld version leaks memory and either eventually uses up all system memory or runs into a memory limit. But that's just a wild guess...

so you guys use postgresql to create this site and cron jobs to control the site automaticly?

MeqA wrote:so you guys use postgresql to create this site and cron jobs to control the site automaticly?

yes

btw. status update, db size is now nearly 2 GB

exp wrote:btw. status update, db size ist now nearly 2 GB

Quite small... I thought it would be bigger.

hmm, I hope it's highly compressible and you're doing backups regulary...

Getting 2 GB right off the server and burning it to CD daily would be a pain in the a..

MySQL InnoDB does support transactions, I have never used it myself as I only found this out about 2 weeks ago, I am going to use it in my new projects though

You guys are funny MySQL is one of the biggest DB's in the market. In case you didn't know, Apache + PHP + MySQL is a standard server setup for many many sites.

I've been using MySQL for over 4 years now, and for more than 2 years now I didn't hear of any corruption going on (some weird bug wasted a lot of my time a few years ago randomly corrupting one table). Seems like you need to update your version

Biggest doesnt mean better. There are lots of features, which lacks in MySQL (or are implemented not very good or only partially) but are present in PostgresSQL and other DB, like:
- views
- sequences
- constraints
- subqueries
- triggers
- stored procedures
- prepared queries
- cursors
- transactions