Re: Giant database vs unlimited databases
Russell E Glaue wrote: No one probably wants to go through the trouble to code this solution but it is possible to use MySQL Proxy to filter the SQL statements and results. MySQL Proxy sits in between MySQL Server and the MySQL Client. It can read queries, modify them, send queries to the server or deny them all together, and even read results and modify them as well, or deny the results to be sent back to the client. Perhaps if you can resolve to a less complicated set up, but still lean towards the VPD idea, MySQL Proxy might work for you. I just wanted to throw this solution out in case it was useful. -RG Hi Russel, That sounds like a cool idea and makes sense. That is what made me feel oogy about the idea of trying to do something like this with MySQL privileges. I read tons of things that say the real auth layer should be separate. And that VPD example was a good example of how fine grained and therefore complex auth schemes can get. I would guess that following your idea further, it could end up being more scalable(sorry I hate that word it is so overused) that is easy to change and upgrade. I am interested in the many dbs vs one big db issue because I followed the many db choice at one time. It did make sense because I could answer all three of the questions in my previous post a big YES. But, it was a lot of extra work, esp over time. I also discovered you can do cross DB joins, but that makes some DBAs shriek in horror :)As it should. Thanks, Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Giant database vs unlimited databases
No one probably wants to go through the trouble to code this solution but it
is possible to use MySQL Proxy to filter the SQL statements and results.
MySQL Proxy sits in between MySQL Server and the MySQL Client.
It can read queries, modify them, send queries to the server or deny them all
together, and even read results and modify them as well, or deny the results to
be sent back to the client.
Perhaps if you can resolve to a less complicated set up, but still lean towards
the VPD idea, MySQL Proxy might work for you.
I just wanted to throw this solution out in case it was useful.
-RG
Eric Frazier wrote:
> Mohammad wrk wrote:
>> Hi Eric,
>>
>> In the case of a "yes" answer to the second question below, can't we
>> still use something like VPD (Virtual Private Database) in MySQL?
>>
>> Thanks,
>>
>> Mohammad
>>
>
> Hi,
>
> I don't know much about Oracle, but I looked this up. MySQL can't do it,
> that I know of. Here is the final example of such a setup that I found
> at http://www.oracle-base.com/articles/8i/VirtualPrivateDatabases.php
>
> CONNECT user1/[EMAIL PROTECTED];
> INSERT INTO schemaowner.user_data (column1, user_id)
> VALUES('User1', 1);
> INSERT INTO schemaowner.user_data (column1, user_id)
> VALUES('User2',2);
> COMMIT;
>
> CONNECT user2/[EMAIL PROTECTED]
> INSERT INTO schemaowner.user_data (column1, user_id)
> VALUES ('User 1', 1);
> INSERT INTO schemaowner.user_data (column1, user_id)
> VALUES ('User 2', 2);
> COMMIT;
>
> CONNECT schemaowner/[EMAIL PROTECTED]
> SELECT * FROM schemaowner.user_data;
> CONNECT user1/[EMAIL PROTECTED];
> SELECT * FROM schemaowner.user_data;
> CONNECT user2/[EMAIL PROTECTED]
> SELECT * FROM schemaowner.user_data;
>
> Notice that:
>
> * When connected to USER1, only the first insert will work.
> * When connected to USER2, only the second insert will work.
> * The failing inserts produce the error:
> ORA-28115: policy with check option violation
>
>
> You can setup column level privileges on MySQL, but I wonder if it would
> be buggy considering I have never heard of anyone doing this before.
> Plus from what I understand the above example is a lot more than column
> privileges. user1 can only insert data if the insert statement's data
> sets user_id to 1, for example. Pretty cool, but scary in a way. I find
> this much logic in the DB to be scary(esp if not well documented), but
> then I use MySQL :)
>
> So as to which way you should go is most defiantly a matter of opinion I
> think. But, going back to my opinion(which is not at all informed as to
> all the details), if question 2 is a YES, then I would tend to go with
> separate DBs.
>
> Thanks for the Oracle lesson :)
>
> Thanks,
>
> Eric
>
>
>
>
>
>
>
>
>> - Original Message
>> From: Eric Frazier <[EMAIL PROTECTED]>
>> To: Mohammad wrk <[EMAIL PROTECTED]>
>> Cc: mysql@lists.mysql.com
>> Sent: Monday, November 19, 2007 7:42:13 AM
>> Subject: Re: Giant database vs unlimited databases
>>
>> Mohammad wrk wrote:
>> > Hi,
>> >
>> > I'm working on a web 2.0 project that targeting small to medium
>> size companies for providing business services. Companies simply
>> register to the site and then start their business by loading their
>> data, sharing and discussing them with others.
>> >
>> > The design/architectural decision now we are facing from database
>> perspective is how we should store companies' specific data? One way
>> is to put all of them in a single database and partition them by
>> company-id and the other one is to create, on the fly, a new database
>> per company . The justification for the latter is that MySQL is not
>> powerful enough (compare to Oracle or DB2) to handle large amount of
>> data and concurrent users.
>> >
>> > I'm new to MySQL and don't know that much about it and this is why
>> I'd like to discuss this concern here.
>> >
>>
>> Funny, I thought you asked the question, should I separate my customers
>> into their own databases, or use one big DB? Not MySQL sucks, Oracle is
>> better. :)
>>
>> Issues I would ask about on this:
>>
>> 1. Is there a chance that given their separation, these DBs will ever
>> diverge in design because of differences between customers?
>> 2. Could they ever need to be separated for legal reasons? (like one bad
>> qu
Re: Giant database vs unlimited databases
Mohammad wrk wrote:
Hi Eric,
In the case of a "yes" answer to the second question below, can't we
still use something like VPD (Virtual Private Database) in MySQL?
Thanks,
Mohammad
Hi,
I don't know much about Oracle, but I looked this up. MySQL can't do it,
that I know of. Here is the final example of such a setup that I found
at http://www.oracle-base.com/articles/8i/VirtualPrivateDatabases.php
CONNECT user1/[EMAIL PROTECTED];
INSERT INTO schemaowner.user_data (column1, user_id)
VALUES('User1', 1);
INSERT INTO schemaowner.user_data (column1, user_id)
VALUES('User2',2);
COMMIT;
CONNECT user2/[EMAIL PROTECTED]
INSERT INTO schemaowner.user_data (column1, user_id)
VALUES ('User 1', 1);
INSERT INTO schemaowner.user_data (column1, user_id)
VALUES ('User 2', 2);
COMMIT;
CONNECT schemaowner/[EMAIL PROTECTED]
SELECT * FROM schemaowner.user_data;
CONNECT user1/[EMAIL PROTECTED];
SELECT * FROM schemaowner.user_data;
CONNECT user2/[EMAIL PROTECTED]
SELECT * FROM schemaowner.user_data;
Notice that:
* When connected to USER1, only the first insert will work.
* When connected to USER2, only the second insert will work.
* The failing inserts produce the error:
ORA-28115: policy with check option violation
You can setup column level privileges on MySQL, but I wonder if it would
be buggy considering I have never heard of anyone doing this before.
Plus from what I understand the above example is a lot more than column
privileges. user1 can only insert data if the insert statement's data
sets user_id to 1, for example. Pretty cool, but scary in a way. I find
this much logic in the DB to be scary(esp if not well documented), but
then I use MySQL :)
So as to which way you should go is most defiantly a matter of opinion I
think. But, going back to my opinion(which is not at all informed as to
all the details), if question 2 is a YES, then I would tend to go with
separate DBs.
Thanks for the Oracle lesson :)
Thanks,
Eric
- Original Message
From: Eric Frazier <[EMAIL PROTECTED]>
To: Mohammad wrk <[EMAIL PROTECTED]>
Cc: mysql@lists.mysql.com
Sent: Monday, November 19, 2007 7:42:13 AM
Subject: Re: Giant database vs unlimited databases
Mohammad wrk wrote:
> Hi,
>
> I'm working on a web 2.0 project that targeting small to medium size
companies for providing business services. Companies simply register to
the site and then start their business by loading their data, sharing
and discussing them with others.
>
> The design/architectural decision now we are facing from database
perspective is how we should store companies' specific data? One way is
to put all of them in a single database and partition them by company-id
and the other one is to create, on the fly, a new database per company
. The justification for the latter is that MySQL is not powerful enough
(compare to Oracle or DB2) to handle large amount of data and concurrent
users.
>
> I'm new to MySQL and don't know that much about it and this is why
I'd like to discuss this concern here.
>
Funny, I thought you asked the question, should I separate my customers
into their own databases, or use one big DB? Not MySQL sucks, Oracle is
better. :)
Issues I would ask about on this:
1. Is there a chance that given their separation, these DBs will ever
diverge in design because of differences between customers?
2. Could they ever need to be separated for legal reasons? (like one bad
query causing customer data be compromised)
3. Is there any other reason you may do something vastly different from
one customer to another?
If you answer yes to any of these, then you might be best off separating
dbs. But, if you never want to, or expect for any of these things to
happen, you will just be creating headaches for yourself. Backup,
replication, and the need for cross DB queries, will all be a pain in
comparison to a single DB.
I am sure there is more to consider, but these are the points that come
to my mind right away.
Thanks,
Eric
Instant message from any web browser! Try the new * Yahoo! Canada
Messenger for the Web BETA*
<http://ca.messenger.yahoo.com/webmessengerpromo.php>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Giant database vs unlimited databases
Hi Eric, In the case of a "yes" answer to the second question below, can't we still use something like VPD (Virtual Private Database) in MySQL? Thanks, Mohammad - Original Message From: Eric Frazier <[EMAIL PROTECTED]> To: Mohammad wrk <[EMAIL PROTECTED]> Cc: mysql@lists.mysql.com Sent: Monday, November 19, 2007 7:42:13 AM Subject: Re: Giant database vs unlimited databases Mohammad wrk wrote: > Hi, > > I'm working on a web 2.0 project that targeting small to medium size > companies for providing business services. Companies simply register to the > site and then start their business by loading their data, sharing and > discussing them with others. > > The design/architectural decision now we are facing from database > perspective is how we should store companies' specific data? One way is to > put all of them in a single database and partition them by company-id and > the other one is to create, on the fly, a new database per company . The > justification for the latter is that MySQL is not powerful enough (compare > to Oracle or DB2) to handle large amount of data and concurrent users. > > I'm new to MySQL and don't know that much about it and this is why I'd like > to discuss this concern here. > Funny, I thought you asked the question, should I separate my customers into their own databases, or use one big DB? Not MySQL sucks, Oracle is better. :) Issues I would ask about on this: 1. Is there a chance that given their separation, these DBs will ever diverge in design because of differences between customers? 2. Could they ever need to be separated for legal reasons? (like one bad query causing customer data be compromised) 3. Is there any other reason you may do something vastly different from one customer to another? If you answer yes to any of these, then you might be best off separating dbs. But, if you never want to, or expect for any of these things to happen, you will just be creating headaches for yourself. Backup, replication, and the need for cross DB queries, will all be a pain in comparison to a single DB. I am sure there is more to consider, but these are the points that come to my mind right away. Thanks, Eric Be smarter than spam. See how smart SpamGuard is at giving junk email the boot with the All-new Yahoo! Mail. Click on Options in Mail and switch to New Mail today or register for free at http://mail.yahoo.ca
Re: Giant database vs unlimited databases
Mohammad wrk wrote: Hi, I'm working on a web 2.0 project that targeting small to medium size companies for providing business services. Companies simply register to the site and then start their business by loading their data, sharing and discussing them with others. The design/architectural decision now we are facing from database perspective is how we should store companies' specific data? One way is to put all of them in a single database and partition them by company-id and the other one is to create, on the fly, a new database per company . The justification for the latter is that MySQL is not powerful enough (compare to Oracle or DB2) to handle large amount of data and concurrent users. I'm new to MySQL and don't know that much about it and this is why I'd like to discuss this concern here. Funny, I thought you asked the question, should I separate my customers into their own databases, or use one big DB? Not MySQL sucks, Oracle is better. :) Issues I would ask about on this: 1. Is there a chance that given their separation, these DBs will ever diverge in design because of differences between customers? 2. Could they ever need to be separated for legal reasons? (like one bad query causing customer data be compromised) 3. Is there any other reason you may do something vastly different from one customer to another? If you answer yes to any of these, then you might be best off separating dbs. But, if you never want to, or expect for any of these things to happen, you will just be creating headaches for yourself. Backup, replication, and the need for cross DB queries, will all be a pain in comparison to a single DB. I am sure there is more to consider, but these are the points that come to my mind right away. Thanks, Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Giant database vs unlimited databases
How much power do you want? We migrated from Oracle to MySQL because to get enough power from Oracle 8/9i, we had to buy an extremely powerful machine. We had oracle on a sun solaris 9 box, and got X amount of power out of it. On a similar machine we installed MySQL and we got XX amount of power out of it. We choose MySQL because we can get more power out of it on a simple server than we did with Oracle. And MySQL was more flexible. Additionally, data management is easier for us on MySQL. We house dozens of organizations on MySQL servers. Each org either shares a db server, or they get their own db servers. In fact we are now installing MySQL on xen/linux systems and are able to reallocate memory and CPU to xen servers if they need more, rather than having to upgrade hardware in machines. When CPU amd memory start to top on a machine, we look at the queries and optimize them. That usually fixes anything that slows down the server. Our typical xen linux system is 8GB dual-ranked memory Intel 3GHz 1333FSB Quad-core CPU 4MB-Cache. You want to get memory that matches the CPU FSB, or as close to it as possible. We use the RHEL (Red Hat Enterprise Linux). I think many people find other non-intel systems perform better for Oracle - But that is because those systems are optimized at sale point. If the Intel system hardware is configured well, it will perform just as good with MySQL. And you can always migrate from MySQL to Oracle later if you really find that you need to. Tell your employer, if they want to pad the bottom line, they should use inexpensive MySQL and spend some of the savings optimizing the Web Application. Use Java grid technology, and load balance your read-only SQL queries (look at Free Sequoia - https://forge.continuent.org/projects/sequoia ) -RG Mohammad wrk wrote: > Hi, > > I'm working on a web 2.0 project that targeting small to medium size > companies for providing business services. Companies simply register to the > site and then start their business by loading their data, sharing and > discussing them with others. > > The design/architectural decision now we are facing from database perspective > is how we should store companies' specific data? One way is to put all of > them in a single database and partition them by company-id and the other one > is to create, on the fly, a new database per company . The justification for > the latter is that MySQL is not powerful enough (compare to Oracle or DB2) to > handle large amount of data and concurrent users. > > I'm new to MySQL and don't know that much about it and this is why I'd like > to discuss this concern here. > > Thanks, > > Mohammad > > > > > Ask a question on any topic and get answers from real people. Go to > Yahoo! Answers and share what you know at http://ca.answers.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Giant database vs unlimited databases
On 11/19/07, Mohammad wrk <[EMAIL PROTECTED]> wrote: > > Hi, > > I'm working on a web 2.0 project that targeting small to medium size > companies for providing business services. Companies simply register to the > site and then start their business by loading their data, sharing and > discussing them with others. > > The design/architectural decision now we are facing from database > perspective is how we should store companies' specific data? One way is to > put all of them in a single database and partition them by company-id and > the other one is to create, on the fly, a new database per company . The > justification for the latter is that MySQL is not powerful enough (compare > to Oracle or DB2) to handle large amount of data and concurrent users. > > I'm new to MySQL and don't know that much about it and this is why I'd > like to discuss this concern here. I think the statement that MySQL is not as "powerful" as the other products probably is unfounded in the sense that you mean it. On a given platform, searches are typically going to be O(N) or O(log N) depending on how you arrange the indexes and queries. http://en.wikipedia.org/wiki/Big_O_notation What you are trying to do is design your database so that all the queries are O(log N) rather than O(N). It is possible that Oracle can perform more adeptly than MySQL at certain operations (I don't know this, and the stats could easily go the other way). But the difference probably wouldn't exceed 2:1 in favor of either product, and you'll still be left with the O(N) vs. O(log N) design issue. I'd go with the design that does not create new tables. Dave.
RE: Giant database vs unlimited databases
[snip] The justification for the latter is that MySQL is not powerful enough (compare to Oracle or DB2) to handle large amount of data and concurrent users. [/snip] Not true and it has been proven time and again by the likes of Yahoo and others that size. We routinely use MySQL for large data stores (upwards of half a billion records in a single table) and with proper management we have performance equal to or better than the above mentioned products without the overhead required by either of those. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Giant database vs unlimited databases
Hi, I'm working on a web 2.0 project that targeting small to medium size companies for providing business services. Companies simply register to the site and then start their business by loading their data, sharing and discussing them with others. The design/architectural decision now we are facing from database perspective is how we should store companies' specific data? One way is to put all of them in a single database and partition them by company-id and the other one is to create, on the fly, a new database per company . The justification for the latter is that MySQL is not powerful enough (compare to Oracle or DB2) to handle large amount of data and concurrent users. I'm new to MySQL and don't know that much about it and this is why I'd like to discuss this concern here. Thanks, Mohammad Ask a question on any topic and get answers from real people. Go to Yahoo! Answers and share what you know at http://ca.answers.yahoo.com
