3

I want to offer SQL shared hosting for customers.

Ideally I'd like:

  1. access isolation (one user shouldn't see information about other user's databases)
  2. SQL disk space quota per SQL user
  3. ability to limit or deprioritize CPU usage of intensive queries so everyone gets its fair share
  4. other things that I haven't thought about but you might have insight.

What engine do you recommend, PostgreSQL or MySQL? Why?

I understand PostgreSQL has better support in case of a server crash or loss of failure, but it fails to provide things like 1) (since pg_database is viewable by anyone). I don't know if MySQL supports 2) but it's done in CPanel somehow so it must be possible, I'm clueless if PostgreSQL supports that...

user55488
  • 31
  • 1
  • What the problem about access isolation ? The users won't be able to access data on the others databases, just the name of the databases if the user don't have the CONNECT privilege) – Kedare Dec 23 '10 at 10:41

1 Answers1

1

If your users are going to be sharing a database server instance, I'm not sure you can keep them from seeing what databases are on the server. (mysql has "show databases"). If this is a critical feature for you, you may want to consider having a server instance per customer. This allows you to tune the server instance (cache size, sort size, that type of thing) for their application.

NOTE: this will require that they either utilize a specific port that you assign them, or you can assign them ip addresses to use. (If you utilize rfc 1918 ip addresses (the "private" ip addresses) you have a virtually unlimited number of these, and this would work fine as long as these servers are being accessed by machines at your datacenter.)

Another option is to utilize virtual servers such as xen or vmware. This will give you even more control over the OS configuration for each customer, and protects them from being resource starved by a piggy user (if you configure your xen hosts with reasonable limits).

I think that the virtual hosting solution meets all of your needs, even though it goes beyond the specific scope of your question. IMO, it's the easiest and fastest way to accomplish your goal.

As to what engine I recommend: Postgresql, every day, all day long. However, you need to provide what your customers want. Hmm, if you use virtual machines, you can provide both!

unixguy
  • 366
  • 1
  • 4