• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 596
  • Last Modified:

MySQL peformance, capacity and high traffic

We are looking at developing an online business.  Like everyone setting up, we would hope it would become large in usage and storage.  We are trying to think of the long term of what would be required if this goal was achieved.

I have read database comparisons, and of course these differ depending on who has done them and their familiarity with the databases, so I was really hoping to get some real world experience from people.  MySQL is claimed to be only for small to medium size companies in many reviews, however you often hear of companies that have used mySQL for quite large capacity.  

I have just started to read about clustering and am wondering how far you could really push mySQL.  I believe ver 5 did focus more on Enterprise grade and I would welcome input from people that have really pushed this or know of others who have.    We can't aford Oracle and I realise there is a free express one, but this has limitations.  It also seems that with Oracle we would always have our hand in our pockets, and I think it is beyond our means to be honest.  I know Posgres is another favoured one.  I am asking about MySQL because it's free, fast, the one I know, am able to get access to programmers at reasonable rates, but I am concerned that MySQL may not last the distance if the business was successful.  

I am thinking there must be many successful web businesses which have started from nothing, and not likely to have been able to afford an Oracle, or do they all move over to Oracle at some point?

For example does anyone know what database Amazon, Ebay, PayPal etc. use?  I am interested in high traffic sites, with large storage capacity that still perform well.  

I have put 500 points on this, and hope that I will get a few responses and split the points.  I hope that is acceptable.  

Thank you

Debbie
0
debbieau1
Asked:
debbieau1
  • 5
  • 5
2 Solutions
 
Kim RyanIT ConsultantCommented:
Have you looked at MySQL Cluster?  http://www.mysql.com/products/database/cluster/

MySQL also has a list of reference sites from large users http://www.mysql.com/why-mysql/mysql_power.html . Also some white papers here
0
 
debbieau1Author Commented:
Yes I was looking at MySql Cluster.  Thanks.  I hadn't seen the reference sites, which is interesting reading as are the whitepapers.  I will leave the question open a little longer, as I would hoping to get a few responses on this as mentioned earlier.  I will allocate some points to you when I close the question.  Thank you very much.
0
 
patspamCommented:
MySQL is great for your average web app. I use it at home and work on loads of websites/webapps.

But... to be honest, if you want to write a serious app you should really be looking at PostgreSQL. Until I actually used it I stayed away thinking MySQL was the greatest, but the truth is PostgreSQL is the most advanced open source db server in existence. I hope I don't start a flame war but you won't find many serious coders who will debate that. I recommend you spend a bit of time reading the postgreSQL website (http://www.postgresql.org/) and if nothing else have a look who else uses it and you'll see what I mean.

The other great thing about postgreSQL is that it's released under a BSD license, which means that you can use it for a commercial application with minimal restrictions.

Good luck,

Patrick
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
debbieau1Author Commented:
Thanks Patrick.  I must admit that I have it thought it likely we will need to go with Postgre, but was hoping that MySQL would still be a solution, only because of the reasons I explained earlier.  I have read a bit about Posgres.  One thing that seems to come up a lot on a downside though is that it is quite a bit slower than MySQL.  I would be interested in your comments on this and how easy/difficult you found it is as a MySQL user to make the transition to Posgre.  Also did you import mysql data into Postgre, or start from scratch?

I don't suppose you know what the Amazon's, Ebay, etc. are developed in?  

Thanks again

Debbie
0
 
patspamCommented:
According to this link Amazon uses Oracle: http://www.dba-oracle.com/oracle_news/2004_2_17_conway.htm which I'm sure you know is commercial software.
Not sure about ebay. Did some googling but couldn't find it quickly.

MySQL may well be faster because of its lighter feature set. On the flipside I'd say PostgreSQL wins on features and stability. Then again, part of it comes down to what connector you are using. I'm writing my current project in C# and I'm using the psqlODBC driver which is sitll in development (hence some usage issues). You might want to look at what's available for your particular development/deployment environment.

Starting out as a MySQL person, all I really had to do was spend a few hours learning how to perform the common sysadmin operations (create databases, grant permissions, etc..) and get my head around a few new concepts (nothing an afternoon can't fix). After that, well it depends what sort of advanced features you want to use. For simple SQL you won't find many differences. Once you start getting more advanced and using things like stored procedures you'll need to check out the (really well detailed) documentation on PostgreSQLs internal programming language.

I found the opensource windows graphical admin tool PgAdmin (www.pgadmin.org/) fantastic for getting things up and running, and I ended up using it to design/create all my tables, relationships and views.

My main reason for choosing PostgreSQL was licensing but the more I tap into the advanced features the more I'm glad I took the plunge.

Hope that helps,

Patrick
0
 
patspamCommented:
> Also did you import mysql data into Postgre, or start from scratch?
I started from scratch. You could probably bring across a lot of your tables, relationships, views etc.. by doing a mysqldump to a text file and then a pg_restore (although you might need to 'translate' some of the more specific syntax. I haven't tried this, but it probably comes down to how complicated your sql is.
0
 
debbieau1Author Commented:
Thanks Patrick.    It think Ebay uses Oracle too.    I think for now we may need to go with Postgre for larger projects.

One last question.  I am interested in what was the actual attraction to you with the licence side of Postgre you mentioned.  Would you mind elaborating on this for me.

Debbie
0
 
patspamCommented:
Sure. I am writing a commercial application that uses PostgreSQL as a backend. Part of the service offering will be to install PostgreSQL locally on a dedicated server inside the client's LAN. PostgreSQL's BSD license lets me do that. My application is a windows app, using the psqlodbc connector which is released under the LGPL license. That's more restrictive than a BSD license but I will still be able to make use of the connector (although I will have to keep notices intact).

The bottom line is that other db licenses wouldn't let me use their db as a backend as part of a commercial product.

Hope that helps.

Patrick
0
 
debbieau1Author Commented:
Thanks Teraplane and Patrick for all your help.
0
 
patspamCommented:
No worries. I was hoping someone would jump in and give you a few alternative viewpoints since I'm pretty PostgreSQL biased. Maybe do a bit of "MySQL vs PostgreSQL" google searches to get a more balanced argument.

Cheers,

Patrick
0
 
debbieau1Author Commented:
Hi Patrick I think the google searches can be more confusing as in these there does seem a bias in these depending on who does them.   It sounds like you have been a fan of mysql and continue to be, but for the right jobs, so I do take seriously what you say.  

Also, I  had suspected after doing some research that Postgre would be the way to go for us and it was really useful to me to get your comments.

thanks again.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now