Database partitioning/sharding (split from SBA)

From Visual Basic to GNU C, this is the place to talk programming.

Moderators: SecretSquirrel, just brew it!

Splt: Asking for system build suggestions

Postposted on Sun Apr 29, 2012 9:07 am

Hi All,
I recently asked around for suggestions for anew system build, I think my requirements are similar to those of @canadiancreed. I also have a very large amount of files, mainly photos, video clips and music, and also a whole bunch of application files I've been playing with, these are currently backed up to my sever file as well. Another similarity is in our coding requirements. I need a system that can handle AMP stacks and manage running multiple VMs/app servers, and who know what I'll need to handle in the future, so I wanted a certain amount of flexibility.
Anyway, I actually ended up ordering the almost exact system. One exception - I invested in Asus Xonar DG audio card - as a music fan, this was important to me.
Still, I have some issue I need some input on - I will probably begin working on a project that will require comprehensive database management capabilities. I want to have the ability to handle MySQL database scalability issues as the database grows, and do this remotely if needed. Since the database in question is pretty big already and will most likely continue growing, and fairly quickly. What I'm not sure about is what method to use to scale the database - partitioning or MySQL sharding. I found this nice comparison between MySQL sharding and partitioning that was a nice read, but has helped me decide. If any of you have any experience, advice, or tips for me, I'd be oh so grateful.
:) Rona
I do not fear computers, I fear the lack of them
Isaac Asimov
RonaRosen
Gerbil In Training
 
Posts: 6
Joined: Thu Apr 19, 2012 6:30 am
Location: Beer Sheva, Israel

Database partitioning/sharding (split from SBA)

Postposted on Sun Apr 29, 2012 10:31 am

(split from this thread)

RonaRosen wrote:...
Still, I have some issue I need some input on - I will probably begin working on a project that will require comprehensive database management capabilities. I want to have the ability to handle MySQL database scalability issues as the database grows, and do this remotely if needed. Since the database in question is pretty big already and will most likely continue growing, and fairly quickly. What I'm not sure about is what method to use to scale the database - partitioning or MySQL sharding. I found this nice comparison between MySQL sharding and partitioning that was a nice read, but has helped me decide. If any of you have any experience, advice, or tips for me, I'd be oh so grateful.
:) Rona

Is the existing database already based on MySQL? If it isn't too disruptive, perhaps you should consider migrating to a more full-featured DBMS, like (say) PostgreSQL.

Sharding can require significant changes to the application layer, so unless you're willing to take that on (or there isn't a lot of existing application code that would need to be rewritten), sharding is probably not a good idea.
(this space intentionally left blank)
just brew it!
Administrator
Gold subscriber
 
 
Posts: 37859
Joined: Tue Aug 20, 2002 10:51 pm
Location: Somewhere, having a beer

Re: Asking for system build suggestions

Postposted on Sun Apr 29, 2012 10:33 am

@RonaRosen - It's generally considered bad form to resurrect old threads to ask questions completely unrelated to the thread topic. I've split your database question off to a new thread in the Developer's Den forum: viewtopic.php?p=1118966#p1118966
(this space intentionally left blank)
just brew it!
Administrator
Gold subscriber
 
 
Posts: 37859
Joined: Tue Aug 20, 2002 10:51 pm
Location: Somewhere, having a beer

Re: Database partitioning/sharding (split from SBA)

Postposted on Sun Apr 29, 2012 12:56 pm

just brew it! wrote:Is the existing database already based on MySQL? If it isn't too disruptive, perhaps you should consider migrating to a more full-featured DBMS, like (say) PostgreSQL.

*cough* *hack*

These days, MySQL has nothing less or more than Postgres or similar DBMS. I understand the line of reasoning as MySQL evolved too fast in too little time so a lot of folks still don't know about the current feature set (and still think that it's fast-but-loose, doesn't have ACID compliance, and so on), but that should be made clear.

Anyway, regardless of the choice of DBMS, depending on the workload in question, sharding and/or partitioning might not be necessary. These are heavy-duty measures that have their place but should only be used with excellent reason. Pehaps the OP would like to shed some more light on what the intended application is?
There is a fixed amount of intelligence on the planet, and the population keeps growing :(
morphine
Grand Admiral Gerbil
Silver subscriber
 
 
Posts: 10029
Joined: Fri Dec 27, 2002 8:51 pm
Location: Portugal (that's next to Spain)

Re: Database partitioning/sharding (split from SBA)

Postposted on Sun Apr 29, 2012 3:30 pm

morphine wrote:*cough* *hack*

These days, MySQL has nothing less or more than Postgres or similar DBMS. I understand the line of reasoning as MySQL evolved too fast in too little time so a lot of folks still don't know about the current feature set (and still think that it's fast-but-loose, doesn't have ACID compliance, and so on), but that should be made clear.

I wasn't aware that they had ever added support for enforcement of referential integrity (e.g. foreign key constraints). Am I mistaken here?
(this space intentionally left blank)
just brew it!
Administrator
Gold subscriber
 
 
Posts: 37859
Joined: Tue Aug 20, 2002 10:51 pm
Location: Somewhere, having a beer

Re: Database partitioning/sharding (split from SBA)

Postposted on Sun Apr 29, 2012 4:18 pm

just brew it! wrote:
morphine wrote:*cough* *hack*

These days, MySQL has nothing less or more than Postgres or similar DBMS. I understand the line of reasoning as MySQL evolved too fast in too little time so a lot of folks still don't know about the current feature set (and still think that it's fast-but-loose, doesn't have ACID compliance, and so on), but that should be made clear.

I wasn't aware that they had ever added support for enforcement of referential integrity (e.g. foreign key constraints). Am I mistaken here?

I've looked at the versioning and can't tell you the exact date, but it's available for at least 7 years now, probably more.

Quick lowdown: MySQL supports several table types with their own storage engines, inner workings, and functionality. The default type, MyISAM, is old and busted. Available since the really early days, it's nowadays nearly irrelevant (IMO) and only still used because it's the default, or for historic reasons. It's pretty fast but lacks any type of transactional functionality, foreign keys, ACID compliance, etc.

The new official table type is InnoDB, which since very early on supported all of the above and more. It was somewhat slow in the early days but has massively improved over the years and is now the default table type in MySQL 5.5. Just about the only thing that it lacks (for general use) is fulltext search, and there are workarounds for that. Anyone with more than a passing knowledge of MySQL will use this table type. Moreover, there are even other storage engines available for more specialized operation modes: Archive, NDB Cluster, Falcon, etc.

The real issue is that IMO, it was a really boneheaded move to keep MyISAM as a default table type for so long. That alone contributed more than anything else to the perception that MySQL didn't support X or Y. Analogy: having this really cool OS that does everything, but is set to 80x25 text mode by default with very few available commands.
Last edited by morphine on Sun Apr 29, 2012 4:32 pm, edited 1 time in total.
There is a fixed amount of intelligence on the planet, and the population keeps growing :(
morphine
Grand Admiral Gerbil
Silver subscriber
 
 
Posts: 10029
Joined: Fri Dec 27, 2002 8:51 pm
Location: Portugal (that's next to Spain)

Re: Database partitioning/sharding (split from SBA)

Postposted on Sun Apr 29, 2012 4:28 pm

I stand corrected. It would appear that most of what I (thought I) knew about MySQL was based on the features of the default storage engine!
(this space intentionally left blank)
just brew it!
Administrator
Gold subscriber
 
 
Posts: 37859
Joined: Tue Aug 20, 2002 10:51 pm
Location: Somewhere, having a beer

Re: Database partitioning/sharding (split from SBA)

Postposted on Sun Apr 29, 2012 9:06 pm

MyISAM was really fast for datasets that were updated very rarely but got a lot of read-only queries on them, that's probably why it lasted so long as the default.

I used it about 10 years ago to load all the Call Detail Records in from Access Servers (i.e. the boxes that answer the modem calls at ISPs). Our application would load in 1 month of data in, and then the user could perform statistical analysis on the sessions to find the bad connections in the phone system.
notfred
Grand Gerbil Poohbah
 
Posts: 3750
Joined: Tue Aug 10, 2004 10:10 am
Location: Ottawa, Canada

Re: Splt: Asking for system build suggestions

Postposted on Fri May 04, 2012 5:37 am

I've split the off-topic post from the original thread.
JustAnEngineer
Gerbil God
Gold subscriber
 
 
Posts: 15489
Joined: Sat Jan 26, 2002 7:00 pm
Location: The Heart of Dixie


Return to Developer's Den

Who is online

Users browsing this forum: No registered users and 0 guests