Mysql vs Oracle XE vs Postgresql . Scalability and performance, which to chose? [closed]

Question

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 impact performance in the three cases (does oracle or postgreSQL have an equivalent?)
• How do oracle and postgreSQL leverage cache on subqueries, WITH, and UNION/INTERSECT statements
• How do Oracle and PostgreSQL leverage multicore/cpu power (if/when we get an oracle license)

I think that's already a lot to answer so I'll stop here. I don't mind simple/incomplete answers if there are links to compliment.

If you need any more information just let me know

Thanks in advance guys, the help is appreciated.

Darrick

InnoDB supports full text in latest version. – Intercellular

It requests a fair amount of requests per page load Indicates that you'd have to do a serious redesign for both the Oracle and the Postgres case. IMHO they are a sign of bad design anyway: round trips to the database are often more expensive than the cost of a typical query. – Brocade

Yes we're planning to do some serious redesigning. The major issue was that in order to leverage cache in mysql as best possible, multiple subqueries over an EAV table with millions of lines just wasn't good enough given the constraints. EAV is already poor design but we will have to stick with it. I'm also not saying no optimization can be done in the current state, whether through views or aggregate type queries I'm sure we can better rewrite some parts. But if we're going to rewrite it all anyway we might as well chose the appropriate db to go with it. – Darrick

If you have an EAV table, do look at PostgreSQL. The hstore datatype (and module) is probably the best solution to the dreaded EAV problem available. Much better than a "real" EAV implementation (and PostgreSQL has a lot more SQL features than MySQL - and it doesn't seem like MySQL is catching up there in the foreseeable future) – Eliseo

@ÁlvaroG.Vicario: 5.6 (which supports fulltext indexing for InnoDB) is still not officially released (GA) yet. – Eliseo

Answer 1

PostgreSQL supports full text search and indexes. Details here.

And it can use any number of CPU cores. It creates separate process for every session + some additional support processes. Details here.

PostgreSQL doesn't have built in query caching, but there are lots of open source utilities for this purpose.

Tennies

well this got closed even though I had limited the scope of answers by asking clear questions. So I'll mark this as the correct answer. Thanks for your time that's already half of what I was looking for. A shame about the lock – Darrick