Forum Moderators: phranque
So the question is, WHICH database on the market today can handle this kind of throughput without chocking.
Thanks for any advice you can give
Gary
What platform are you going to be running? Windows or Unix/Linux?
SQL Server is my favorite database.
MySQL should handle your needs, I would think. I was a little unclear on exactly how many users you meant simultaneously. Did you mean 500? Is that 500 people on the site, which could translate into 1/x that number of simultaneous database transactions, depending on how often they perform searches?
In any case, MySQL probably can handle it. I have several database tables involving millions of records and there are a few transactions a second without any problem. Properly indexed, they should perform very well. If you were talking about many many gigabytes of data, then I might start getting worried a bit but it sounds like you're talking about a few million relatively simple records (i.e. not full-text searching capability on millions of full-length documents).
As for a learning curve, yes there is one. I personally didn't thing it was very steep at all to learn basic querying and database formation. But you will have to read a book on the subject. What programming language to you plan to use to access the database? My personal feeling is that Perl and PHP are always good but others will do fine as well.
I've worked in the I.T. world for 32 yaers and have never had to work the database side of the fense other than having others do it for my group.
Now that is impressive ;) That is quite a career in IT without having any db involvement other than management level. I think you'll find MySQL easier to learn and use, with a much broader "open" support structure. Microsoft's solution is a viable solution, no doubt, but I think you'll find it more costly considering the needs you are describing.
The biggest difference you will find between the two is going to be price and support, MySQL being the less expensive route.
A single processor license for Microsoft SQL Server is $5000.
Now, considering how much money businesses have to pay for IT staff, the $5000 is probably LESS than it will cost to mess around with any other database. Using a different database will cost more than $5000 in unnecessary labor.
I've been working with databases for 10 years, and I've used Informix, Oracle, SQL Server, and even some weird databases you probably never heard of. Of all that I've used, SQL Server was by far the easiest to deploy and learn.
You can buy the developer's edition of SQL Server at Amazon.com for ony $42.99.
YES, 32 years is a long time to spend on any one career. Most, if not all of my time was in customer field service i.e. desk top support and network services. As most of you know, when working in a large Corporate environment where there are groups of departments for each area it is easy to request a database without knowing how it is actually built.
As for the front end to this database it will be PHP. As to the amount of hits on the database I might explain a little further. This is a military database where military personel are searching for their buddies. A search could be on a name, place (Battle), nick name, or where they attented boot camp. Once this infommation as been located within the database, it then lists all the particulars for than person. Although the email addresses will be in the database, that information will not be shown to the members.
If any of you are former Military you know how important this information is and as such could draw a large number of hits.
With the information you all have provided I'm going to download training information about MYsql and see how fast this old manager can pick it up.
I'd like to thank each of you for your input, it is greatly appreciated.
Gary
Since it was only available on Unix until their last version, it has not been as popular as MySQL, however it has much better features and is more standards-compliant.
[sqlcourse.com...]
GREAT link...can't thank you enough
All this brings me back to the days of BASIC and Pascal....seems like 25 years ago...hell, maybe it was. ha ha
If your going with php I would definitivly recommend mysql or postgres, I tend to use mysql mainly because I am familiar with it, and because it seems more standard for third party apps, then for any particular (read more legitimate) reason.
Particularly given that there will be time / expenses associated with training either way I think that and that php/mysql examples exist en masse, an open source solution will, I think, prove more cost effective.
There are a number of features that the MySQL team swore they would never put in because no one needed them, and now they have come to their senses and added them. Transactions, foreign key constraints... I believe both are now available with MySQL, but why should I trust a db dev team that so obviously doesn't understand my needs as an app developer?
MySQL seems but a toy after working on Oracle, so I guess I'm biased. I can't begin to explain the number of headaches I've had trying to implement non-trivial SQL statements with MySQL versions prior to 4.1 that did not handle subqueries. I've also faced a couple of MySQL gotchas ( [sql-info.de...] ) such as the one for NULL with TIMESTAMP that had me questionning my sanity.
MySQL will likely handle Cpl_Miller's projected load without a problem. But then you're pretty much stuck with MySQL. Going with PostgreSQL will make it easier to migrate to a more standards-compliant db, such as Oracle, MS-SQL, etc... You're also less likely to have devs working on non-trivial features developing ulcers.
Again, thanks all you've been a big help.
Gary
I disagree, there is nothing that MySQL supports that isn't included in MS-SQL or Oracle. I know that your migration theory isn't true for MS-SQL, I've actually worked with that, if you can migrate a piece of cr*p access database to that, I would worry about migrating MySQL to MS-SQL -- if anybody in their right mind would actually do that. Oracle is another crate of bannanas, however. But from what I understand you would just need to "commit" your queries which may even be handled by an API layer or class, and besides there are creative ways to make MySQL scale.
The one that comes to mind for MySQL is the LIMIT clause. Of course, you can achieve similar results with other DBs, but you would need to rewrite the queries.
You can find some feature comparisons that may be useful.
The mysql site has fairly extensive feature matrix vs DB2 [dev.mysql.com]
The postgres people have a page about migrating from MySQL to Postgres [techdocs.postgresql.org] that gives some useful information.
As for MySQL standards compliance and diversions from the ANSI SQL standards, the mysql manual has a subchapter (chapter 1.8) on MySQL Standards Compliance [dev.mysql.com] which I believe is an honest attempt on the part of the MySQL to document where it observes and diverges from the standard.
Thanks, and let me know what you think.
LIMIT is in both MySQL and Postgres, but not in Sybase, Oracle or MS Sql Server, Interbase etc etc etc because it's not standard SQL
Tom
And it has some neat features to let it grow in hardware and performance as you may require it, with master/slave arrangements and round robin load balancing.
SN
My problem is that there are two of us that are paying for the sites and their upkeep out of our own pockets. We will be selling military products to support the new site, but cash flow is limited at this point. This is the reason I thought I might try and do this on my own.
Can anyone tell me with the information I've provided what a simple database engine would cost to produce?
Thanks everyone
Gary
