How much can MySQL take?

Syket

Hi all,

I'm new to the forum and would like to start off by saying hi to everyone. And now to my question, how much can a MySQL database take? I have read some on the offical MySQL page and according to them, they think that their database is capable of handling almost anything. I have been using the database for about a year for various smaller projects and I realy must say that its very good on handling small applications quickly (I havent used it on any bigger projects).

Now, I know for a fact that the highprice databases like Oracle and such are great to work with if you are planing on using it for very large databases. But as I mentioned before, I have never deared to try MySQL for the job, but it would sure make the projects cheaper.

Now I hear that some people are using Postgre SQL and saying that it is far more better than MySQL, but cant really give me a good explenation why.

So here comes my questions:

Anyone who have any experience of making a larger project with about 20-100 tables and with about 5000-10000 records, and dealing with about 5000 querys daily, using a MySQL database or PostgreSQL database?

How much can MySQL or PostgreSQL take without crashing all the time, querys and so on..?

Or any other information regarding these databases like security or flexibility..

I hope that someone out there can awnser any of my questions.

Thanks in advance.

fire_cracker

I, personaly, have just started using mySQL on my own projects, but I used to work for a large telemarketing firm in eastern nebraska. We used a box that ran nothing by unix and mySQL. The database worked fine for everything it got used for. Keeping stats, timesheets, sales reports, and talk time for about 4,000 employees, 1,000 of them all working at one time. It kept records of all our customers, leads, and cancelations. Over 5 million phone numbers from around the country. The database was huge, but it seemed to keep a hold on things pretty good.

tha_mink

Speed and reliability are not really the issue.

The issue is more about functionality. When working with complex data designs, you (may) need functionality that MySQL doesn't support.(yet?) For example, MySQL has no support for things like views, stored procedures, triggers and other valuable things that you need on large complex database work. It's great for simple stuff but when you need more complex storage and extraction, time to look to more versitile systems. (PostgreSQL and oracle included)

Syket

Originally posted by tha_mink
Speed and reliability are not really the issue.

The issue is more about functionality. When working with complex data designs, you (may) need functionality that MySQL doesn't support.(yet?) For example, MySQL has no support for things like views, stored procedures, triggers and other valuable things that you need on large complex database work. It's great for simple stuff but when you need more complex storage and extraction, time to look to more versitile systems. (PostgreSQL and oracle included)

I hear you. But the functionality in MySQL is suficent for the most of the projects that I have worked with, the main issue for me is if the database really can perform good when containing very much data, meaning speed and reliability.

Sure, you could use abstract layers, to have as a backup plan if the mysql db cant handle the job, but again, that would be a waste of dev time if the mysql can handle it. I have seen some horror examples in some forum posts where people say that their querys take as much as 10 seconds, but without seeing any code I dont know if its because the code is very poor or if the database can't handle larger amounts of traffic/data.

So if anyone else have experience of MySQL and bigger apps/dbs, I would like to hear them. Thanks.

tha_mink

It's like I said. Speed and reliability are not really an issue. MySQL compares with most other SQL DBs in speed, response time, etc. I have used MYSQL on large projects with good results.

Here are some benchmarks if you like that kinda thing.

http://www.mysql.com/information/benchmarks.html

BUT....

Now I hear that some people are using Postgre SQL and saying that it is far more better than MySQL, but cant really give me a good explenation why.

Again, the reason is the extended functionality. If you will be working with data like you explained (20-200 tables with 20000+ records each), you will most likely need things like views, stored procs, and triggers to make getting to your data quicker and easier. Those tools save queries, query time and load. It's kinda rough to realize this after you already have the data in MYSQL and want to switch. (actually it's not really that hard but still...) When you know you are going to be ABLE to use the functionality provided by more robust dbs then you can design your data storage more efficiently and thus speed everything up.

Syket

Thank you for your response. The benchmark was very usefull aswell as the PostgreSQL comperation. I now understand why people are using PostgreSQL instead of MySQL. I will look in to it to see what I can gain from it using it in my projects. If someone still have some input or suggestions, please feel free to share them, since I think that many people can gain alot from this thread. Thanks again.