Personal computing discussed

Moderators: renee, SecretSquirrel, just brew it!

 
RonaRosen
Gerbil In Training
Topic Author
Posts: 6
Joined: Thu Apr 19, 2012 6:30 am
Location: Beer Sheva, Israel

Splt: Asking for system build suggestions

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
 
just brew it!
Administrator
Posts: 54500
Joined: Tue Aug 20, 2002 10:51 pm
Location: Somewhere, having a beer

Database partitioning/sharding (split from SBA)

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.
Nostalgia isn't what it used to be.
 
just brew it!
Administrator
Posts: 54500
Joined: Tue Aug 20, 2002 10:51 pm
Location: Somewhere, having a beer

Re: Asking for system build suggestions

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
Nostalgia isn't what it used to be.
 
morphine
TR Staff
Posts: 11600
Joined: Fri Dec 27, 2002 8:51 pm
Location: Portugal (that's next to Spain)

Re: Database partitioning/sharding (split from SBA)

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 :(
 
just brew it!
Administrator
Posts: 54500
Joined: Tue Aug 20, 2002 10:51 pm
Location: Somewhere, having a beer

Re: Database partitioning/sharding (split from SBA)

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?
Nostalgia isn't what it used to be.
 
morphine
TR Staff
Posts: 11600
Joined: Fri Dec 27, 2002 8:51 pm
Location: Portugal (that's next to Spain)

Re: Database partitioning/sharding (split from SBA)

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 :(
 
just brew it!
Administrator
Posts: 54500
Joined: Tue Aug 20, 2002 10:51 pm
Location: Somewhere, having a beer

Re: Database partitioning/sharding (split from SBA)

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!
Nostalgia isn't what it used to be.
 
notfred
Maximum Gerbil
Posts: 4610
Joined: Tue Aug 10, 2004 10:10 am
Location: Ottawa, Canada

Re: Database partitioning/sharding (split from SBA)

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.
 
JustAnEngineer
Gerbil God
Posts: 19673
Joined: Sat Jan 26, 2002 7:00 pm
Location: The Heart of Dixie

Re: Splt: Asking for system build suggestions

Fri May 04, 2012 5:37 am

I've split the off-topic post from the original thread.

Who is online

Users browsing this forum: No registered users and 1 guest
GZIP: On