is MySql good for large databases? [closed]_问答_开发者

Closed. Thi开发者_如何转开发s question needs details or clarity. It is not currently accepting answers.

Want to improve this question? Add details and clarify the problem by editing this post.

Closed 1 year ago.

Improve this question

I work for a company and we are always accessing an external site for information. The site was developed by an antiquated software development company who doesn't even have a website. They pretty much have a monopoly in my state since the content provider for the database only uses this extremely dysfunctional site to upload their data. The problem with this website is that it is so slow it's not even functional.

Having controlled for things like Connection speed and Browser type, it is clear that the problem lies within the website itself. So, I am thinking about redoing the site and then offering it to the content provider as a means for uploading their data. Basically, this project requires a very large database to store hundreds of thousands of names, addresses, and other types of data.

My only experience with databases is MySql, and really my only experience with dynamic content is PHP. So, yeah, I'm trying to figure out if the old PHP + MySQL combination is suitable for storing and representing large amounts of data. I have only done this on small projects, but I think the whole HTML templates with placeholders for the dynamic content would work fine.

Of course, I truly don't know why this website is so slow. Maybe it's not the DB at all. Maybe it's the server or something else. But the key thing I am trying to accomplish is to improve upon the speed and functionality of this site. I have no experience with other types of databases, so any tips / advice you can offer for doing a project like this would be greatly appreciated. Also, any tips regarding how to generally make a fast and functional site that would need to represent dynamic data from an extremely large database would also be helpful.

*Edit: I am learning python so if you think this would be a better side-scripting language then I can certainly try to implement something different than the initial plan above.

If you do a good design, you can have very large databases in MySQL (this other question may help you). Define properly the keys and indexes, optimize your queries (EXPLAIN is your friend here). Select a good database engine.

There are a lot of things to do to get the best from MySQL.

EDIT: some more ideas...

It's also quite important the way you structure your data and the tables to make easy to write them, recover them or finding a compromise between both (depending on the use case).

Also if it grows you can use a cluster, partition your data between several MySQL databases in several machines (using a Master-slave schema for instance) it's another option.

To improve the performance you should also think on using some kind of cache for repetitive queries.

There is also tools like Vitess that help to scale a MySQL database in a similar way as the NoSQL databases.

If PHP/MySQL can scale to Facebook's usage, it can scale to yours. It's not necessarily the best solution for all situations, nor is scaling always a simple task.

Yes, You can create large-scale applications using PHP and MySQL. You need to use some other helper tools as well, which will help scaling your app, for example load balancers.

Now the thing is you can't do that with default installation of MySQL itself. You need to understand how to balance the loads upon multiple servers. And then how to use clusters or shards. So you can do everything as long as your system is developed on top of a scalable architecture.

And don't start worrying about millions of records when you are just deploying your first version. Scaling happens gradually. You can't deploy an application which scales in just one day. You have to optimize it day by day. Remember Donald Knuth's quote "Premature optimization is the root of all evil"

Site's like HighScalability can help you understand designing better architectures on top of LAMP stack :)

=H=

You only really start to hit mysql's limit in the millions (and can still push it farther if really needed). You might take a look at mysql's spin offs, or if you are using billions, look at something like Cassandra.

Performance wise, php isn't the fasted, but should be fast enough. Try using background tasks/cron jobs for the time consuming jobs, and a lot of caching.

There are a few points to bring up with your question.

The first item that I notice is that you mentioned storing "hundreds of thousands" of rows. This isn't very big. My 386 server with 512M ram can handle that many rows with only a small delay. If you engineer your database correctly, then your real issue is going to be about the bandwidth (requests per second) that are going to be coming to your server.

MySQL can scale, but it is not great at it. Facebook does use PHP/MySQL for some of their services, but for the faster and more bandwidth intensive tasks they use hadoop. They also use PHP, but for their faster applications they use hip-hop to convert it to c++ and compile it.

MySQL can scale, but if you don't configure it correctly then it will fail miserably when the tables get too large. PostgreSQL scales better out of the box, but either does fine if configured correctly.

It looks like your application is bigger than you are used to, but really isn't "that" big. I would worry more about response time than database scaling in your particular situation.

Personally I've used MySQL as a large scale database mixed in with a PHP based reporting based website. We have a good couple many thousands of rows with about 20+ columns of data that come and go every day with constant multiple connections to the server. MySQL should be easily capable of performing most of the same functionality as a normal MSSQL, PostgreSQL, Oracle, etc... The key is to your queries and to make sure they are the best you can possibly write them to reduce the load on the server itself. Good luck with your project!

MySQL can handle a lot, you just need to make sure you're using the right database engine that suits your needs. I use InnoDB for mostly performance reasons, but you can use anything that's not MyISAM.

Of your course PHP and MySQL can handle a lot of data, if you have a good structured (well normalized) then you don't have nothing to worry about.

MySQL is used for a very very large projects, some of them are: