I understand that this is very broad so let me give you the setting and be specific about my focus points.
Setting:
I am working with an existing PHP application using MYSQL. Tables almost all use the MYISAM engine and contain millions of rows for the most part. One of the largest tables uses an EAV design which is necessary but impacts on performance. The application was written to best leverage MYSQL cache. It requests a fair amount of requests per page load (partialy because of this) and is complex enough to have to go through most tables of the whole DB on each page load.
Pros:
- it's free
- MYISAM tables support full text indexes which are important to the application
Cons:
- With the way things are set up, MYSQL is limited to one CPU for the whole of the application. If one very demanding query is run (or server is under a lot of load) it will queue all others making the site unresponsive
- MYSQL caching and lack of "WITH" or "INTERSECT" means we have to break our queries down to better use cache. Thus multiplying the number of queries made. For instance, using subqueries over multiple tables with millions of rows (even with decent indexing) turns out to be a big issue with the current/upcomming load and the constraint layed out in the point above (CPU usage)
Feeling the need to scale up in the upcomming year, but not necessarily ready to pay for licensing right away, I've been thinking about rewriting the application and switching DBs.
The three options being considered are to either continue using mysql but with the INNODB engine, this way we can leverage more CPU power. Adapt to Oracle XE and get a license when we need to scale upwards of 4Gb database, 1Gb RAM or the 1 CPU limit (all of which we haven't hit yet). Or adapt to PostgreSQL
So the questions are :
- How would losing full text indexing impa