MySQL Performance Question

[wirehopper]

Imagine you have a large collection of data which can be segregated in an orderly manner and distributed into several segments.From a performance standpoint, is it better to use a single large database or multiple smaller ones.The bulk of the operations on the data will be indexed read access.Assume a default or minimally modified configuration, on CentOS.Thanks in advance.

[End User]

Generally you'd be better off with a single, well-optimized database, especially for mostly read-only requests.There are exceptions, such as if you have a *really* large dataset and massive incoming requests (think Google). In that case doing triage on the incoming requests and routing them to the correct DB for lookup would be more efficient, but you'd have to have an enormous amount of data and a high level of unstructured requests coming in to make it worthwhile. How much data are we talking about...a TB? 100 TB? And how many requests are you expecting? Hundreds per minute or hundreds per second, or...?

[wirehopper]

I really don't know the size of the database, this is more an theoretical, architectural question. It's not really a transaction system. It may process 1000 accesses a day - but each access may include multiple database requests.Other considerations:Some of the accesses are near-real-time - think routing of signalsSegmenting the database would make it much easier to distribute data sets to different servers to support additional systems, or distribute loadBreaking the database into sections may improve security by making it more difficult to get to all the data

[justsomeguy]

If you really need a large high-performance system, most people use database server clusters. MySQL has a cluster server and all of the commercial ones have some variation on it also. I'm not sure of the details of how the data is physically saved, but it usually gets exposed to the application as a single database.

[wirehopper]

I guess the part I don't understand is how searching a large dataset would be faster than searching a smaller one.It seems if the data was partitioned, each dataset would be smaller, so a search would be quicker.If the answer is "trust MySQL" I can live with that.

[boen_robot]

If the large data set is in memory, it would be faster than a small data set on an HDD, that's for sure. If reaching the new partion of data requires a second disk I/O, then things slow down.But those are various complicated details... so... "trust MySQL" is a good philosophy, but if it's not enough for you, then you need to train (yourself?) somehow to become what people call "database administrator". And you though that means anyone who knows how to create MySQL tables, didn't you? (I once did...)

[wirehopper]

I do know there's a lot more to databases than just creating tables. I've written some truly horrible queries, found them in the mysqld.slow.log and then reworked them. Every now and then I tinker with indexes and changing from CHAR to VARCHAR (CHAR seems better if the data is being INSERTed and UPDATEd, VARCHAR seems faster for SELECTs). I'm not the database administrator - so I don't usually worry about database architecture and design issues. This was just a question I've had for a couple years.I'm going to take boen_robots advice and get a book about MySQL. It's about time I learned more.Thanks.

[Synook]

Well, you could say there's a lot more to MySQL than just queries...