I've got a community site, which some days delivers to 35K visits. Each pageview results in several DB queries (all within one DB connection) when i did not implement any caching function yet. About every 3rd pageview cases a DB write (mostly for logging reasons).

I presently use MySQL using the MyISAM engine. The DB server software was on a single hardware because the Web server. Once the traffic began to develop and was striking about 12K visits daily the SQL server started to eat 100% from the CPU and was killing the server. I Quickly separated the MySQL server to some separate Amazon . com AWS RDS "small" instance. It had been killing it too. I Quickly moved the database to some "large" RDS instance in which the DB was eating no more than 70-80% from the CPU time. So, now it kinda works however the "large" RDS instance from Amazon . com AWS is extremely effective and incredibly costly. Something is certainly going terribly wrong here and my first guess would be that the DB configuration or storage engine isn't optimal.

The net server is running on nginx &lifier php-fpm. The MySQL now around the separate Amazon . com AWS RDS instance (large) and it has MyISAM as storage engine. The quantity of concurrent customers going to the website differs from 200 to 500 with respect to the time during the day.

If you want anymore info tell me and I'll combine it with the publish.

EDIT 1: DB does not have saved methods.

I'd recommend while using database software that you simplyOryour team is most acquainted with. Begin with query optimisation. Look at your slow query log (http://dev.mysql.com/doc/refman/5./en/slow-query-log.html), and focus on optimizing individuals queries first. Then evaluate your bottlenecks. Possibly you have to increase RAM/buffer usage, or disks are extremely slow. The "iostat" utility let you know just how much disk utilization you are at. "free" will explain just how much RAM you are using.

The MySQL versus PostgreSQL could be debated for a long time on finish. Let us not enter into that :)

It's also wise to be utilising InnoDB like a default, unless of course you'll need MyISAM features.

HTH

All major databases are designed for high volume, plus they use almost entirely exactly the same concepts. Keeping them work involves diving in to the particulars of the particular platform and developing some specific dba-level understanding of this platform.

I recommend starting with the woking platform you're already preferred with and drilling to the general concepts: normalization, indexing, etc., as well as in to the platform-specific tools like how you can monitor disk activity, see query plans, and so on.