Can MySQL handle very high traffic? I'm talking about at least 1000 connections to a database per minute. I heard there are some drawbacks with MySQL, and this might be one of them. I love MySQL, but if its not good for enterprise level work, then I have to use another databasing method. Thanks.
I suspect that most databases could handle 1000 connections (concurrent / new) connections / minute as long as they are idle. If it can be handled under load well that depends on your Algorithms / Database design / Queries / Indexes (or caches) / amount of data transferred or records stored / networkconnection (between www and db server) / user usage patterns / Hardware used. In short the only answer to this question can only given by you, observe usage patterns for your application then program these usage patterns (including pauses) in an loadtesting tool like Apache JMeter and run the tests on different levels of conncurrency. Before you run the test choose the worst acceptable performance / response en use this to get an indicator of the level of scalability of your enviroment. P.S. You might want to eleborate on the enterprise level work statement because this term is loosly defined and can include depending whom you ask : - Complex multitable joins (Mysql might not be the best contender in this area) - GIS (Mysql might not be the best contender in this area) - Google uses it so huge amount of connections is enterprise class (Mysql might not be the best contender in this area) - OLTP (Mysql might not be the best contender in this area) Most databaseservers can do any of the above, some just do them better
I mean 1000 different active connections to the database per minute. This is a high amount, but I can't undercut at all. I'll try this... I'm not really familiar with all of these terms. To be more accurate: think in terms of websites/services like Amazon, Adsense, Ebay, Newegg etc. A lot of users connecting to their stored information. What do you recommend?
If you have the right hardware and you don't end up being bandwidth limited, then you could probably design a DB to handle load like this. Ideally you would use a cluster to handle load like this, which would still be far cheaper (10's of thousands of dollars) than a comparable SQL Server, DB2 or Oracle server. You will definitely want a lot of RAM and as many CPU's / Cores as possible, and a 64bit OS. 4 Quad core processors with about 64Gb RAM would be on the right track. Also, I would use google or percona's patches as they could make this usage a lot more manageable. Google's patches - http://code.google.com/p/google-mysql-tools/ Percona - http://www.mysqlperformanceblog.com/mysql-patches/ How many transactions per second are you looking at? We've got a single CPU 4 core processor up to about 1500 / second with some tuning. The faster you can make a transaction and kill the connection the better, which is why more cores is better. With 16 cores you can probably get into the 3,000 to 4,000 per second range, which is very fast, and unless each connection is making several hundred queries, this should work without problem for 1000 connections per minute.
Sounds good. So you would do this in MySQL then? If it just comes down to servers, then thats fine. I can always limit traffic and then allow more once I can afford more/better servers. My only worry was having to recode everything to accommodate for another database plan.
We got away from Microsoft SQL and we are definitely not looking back. The sheer licensing costs were enough for us to simply find something else that works. You can build from the ground up an incredible MySQL server (or two) for much less than the cost of a single CPU license for Microsoft SQL which is far less than DB2 or Oracle. As for migrating, it can definitely be difficult and most certainly shouldn't be taken lightly. Once we determined that we could migrate with minimal pain, then our choice was easy. I don't think that your requirements are too much for a solid server, but you should definitely look into every aspect of switching, because you need to have high-availability, a stable system, and the ability to manage and recover the DB in case something goes wrong. If you can't cover these, then you probably shouldn't switch. As far as recovery, it is one of the areas where MySQL is way behind the other guys. We use Navicat for hourly backups, but R1Soft and some others have very good backup systems at a higher price.
You're starting out with fairly steep requirements. I don't see why mysql can't handle this, given the right hardware and optimization. I'm assuming you've got mostly reads with just some inserts. Look into using InnoDB rather than MyISAM tables. they're a little bit slower, but you get row level locking instead of table level locking. Also, Look into a product called Mysql Cluster.
Use the EXPLAIN / ANALYZE statements and keep queries as simple as possible - Make your queries more efficient / faster Don't fetch more records then necessary, don't fetch more columns then necessary. - Maximizes cache efficiency - Maximizes network efficiency - More efficient on your IO system Cache in your webserver if possible / permissable - Less load on you database when the webserver has the information in cache (trade memory for performance) Pregenerate complex queries in a cache table if possible / permissable - More efficient queries at the expense of disk usage Just some things that pop into my mind to ensure the system can keep up with your requirements. Also take into account if you might need 2000 connections in the future and how you can achieve this (Clustering / Replication / etc etc) with minimal impact on your current system. Going from single server -> multi server (app and/or DB) is more difficult then tuning a single server enviroment for performance.
I believe that MySQL can fit your expectations, but the key here in my opinion, only slightly less relevant than having good hardware/bandwidth, is optimization of your database. Make sure each table is setup perfectly for the most efficient work; if you are not very proficient with MySQL, you should try researching it elsewhere.