Professional Web Applications Themes

Max. no of database in a server? - MySQL

How may databases are allowed to host in a given MySQL server? e.g. 4 or 5 Will performance suffer If I install too many DB in a single server? Thanks....

  1. #1

    Default Max. no of database in a server?

    How may databases are allowed to host in a given MySQL server? e.g. 4
    or 5

    Will performance suffer If I install too many DB in a single server?

    Thanks.

    howa Guest

  2. #2

    Default Re: Max. no of database in a server?

    howa wrote: 

    That depends how many your database administrator has limited you to. I
    can't find the exact limit in the manual, but it will handle far more
    than you will ever need. Probably way more than thousands.

    Cheers,
    Nicholas Sherlock

    --
    http://www.sherlocksoftware.org
    Nicholas Guest

  3. #3

    Default Re: Max. no of database in a server?


    Nicholas Sherlock 寫道:
     
    >
    > That depends how many your database administrator has limited you to. I
    > can't find the exact limit in the manual, but it will handle far more
    > than you will ever need. Probably way more than thousands.
    >
    > Cheers,
    > Nicholas Sherlock
    >
    > --
    > http://www.sherlocksoftware.org[/ref]

    how to limit?

    howa Guest

  4. #4

    Default Re: Max. no of database in a server?


    Nicholas Sherlock wrote:
     
    >
    > That depends how many your database administrator has limited you to. I
    > can't find the exact limit in the manual, but it will handle far more
    > than you will ever need. Probably way more than thousands.
    >
    > Cheers,
    > Nicholas Sherlock
    >
    > --
    > http://www.sherlocksoftware.org[/ref]
    But that would be a limit to how many that user could create. Other
    users could create more in the same server.

    Captain Guest

  5. #5

    Default Re: Max. no of database in a server?


    howa 寫道:
     

    some background information...

    we have a limited set of user, say. 500, they have many rows (e.g. avg.
    more than 10000 rows stored on a table, so num of row = 5000000, and
    the number still increasing, what i am think is stored the data in
    different database per user, so each user owned a set of tables, the
    scablity of the system can be greatly increased.

    but i want to know the exact limit of the number of DB.

    howa Guest

  6. #6

    Default Re: Max. no of database in a server?

    howa wrote: 
    >
    >
    > some background information...
    >
    > we have a limited set of user, say. 500, they have many rows (e.g. avg.
    > more than 10000 rows stored on a table, so num of row = 5000000, and
    > the number still increasing, what i am think is stored the data in
    > different database per user, so each user owned a set of tables, the
    > scablity of the system can be greatly increased.
    >
    > but i want to know the exact limit of the number of DB.
    >[/ref]

    I don't know the limit in MySQL, but you'll run out of system resources
    long before you'll run out of databases. 500 is piddly.

    As to performance suffering - insufficient information. There are too
    many variables involved. Your best bet is to test it yourself.

    Whether you *should* break up your database into 500 separate ones is
    another question (which you didn't ask).

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  7. #7

    Default Re: Max. no of database in a server?


    "howa" <com> wrote in message
    news:googlegroups.com... 


    As you will have gathered by other replies, there doesn't seem to be a
    "fixed" answer and in fact why should there be a fixed answer. It's more
    likely that the limitation would be the specification of the machine you're
    using for the server.

    Consider ISP's that provide MySQL databases as part of the hosting packages.
    My ISP only provides me with two databases, but multiply that by the number
    of clients they have and you will soon realise that they must have thousands
    per server.

    SA



    Sean Guest

  8. #8

    Default Re: Max. no of database in a server?


    Sean 寫道:
     
    >
    >
    > As you will have gathered by other replies, there doesn't seem to be a
    > "fixed" answer and in fact why should there be a fixed answer. It's more
    > likely that the limitation would be the specification of the machine you're
    > using for the server.
    >
    > Consider ISP's that provide MySQL databases as part of the hosting packages.
    > My ISP only provides me with two databases, but multiply that by the number
    > of clients they have and you will soon realise that they must have thousands
    > per server.
    >
    > SA[/ref]

    What I think is:

    MySQL should have a limit anyway, just as max data length, max row
    size, etc.

    but no one know and can't find in the doc.

    howa Guest

  9. #9

    Default Re: Max. no of database in a server?

    try this:

    $i=0;
    $n=20;
    mysql_connect();
    for($n=0;$n>20;)
    {
    $i++;
    $res=mysql_query('create database databse'+$i);
    if(!$res)$n=21;
    }



    if program runs sucessfully u ll get u r answer but be careful i m not
    responsible for nay problem(;-))

    piyush Guest

  10. #10

    Default Re: Max. no of database in a server?



    What I think is:

    MySQL should have a limit anyway, just as max data length, max row
    size, etc.

    but no one know and can't find in the doc.

    --------

    There are some limitations published for MySQL, as follows:

    Database size: described only as multiple gigabytes or single terabytes

    Number of columns in one table: 3398

    Size (data) of a table row (excluding BLOB and TEXT): 65534

    Number of tables: "unlimited" - up to 32 indexes per table with 256 bytes
    maximum key length and there is an equation which calculates that there is a
    maximum (implied by other limitations) somewhere in the region of 4 billion

    Given the bit about unlimited number of tables, would 'guess' that it's
    probably the same for databases per server.

    SA



    Sean Guest

  11. #11

    Default Re: Max. no of database in a server?

    "howa" <com> wrote:
     

    You're asking the wrong questions.

    MySQL's "databases" (now "schema" is the preferred notion) are
    containers for other database objects like tables, views or
    procedures.

    Using databases brings some benefits:

    - you avoid naming conflicts
    - permissions are easier to handle
    - you can easily see what belongs together, making it easier to
    * backup
    * restore
    * migrate
    database content

    So your first question should be: "What specific benefit do I get if I
    use another database?"
    Then the next question could be: "What is the price for using another
    database and does the benefit justify this price?"

    Usually the price for another database is quite low. Technically a
    database is just a subdirectory in MySQLs $DATADIR. If storage engines
    InnoDB or Falcon are involved (those are managing their tablespaces
    separately) there may be additional (but still low) overhead involved.
    I.e. there are invisible system tables to track database objects.


    XL
    --
    Axel Schwenke, Support Engineer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  12. #12

    Default Re: Max. no of database in a server?


    howa wrote:
     
    > >
    > >
    > > As you will have gathered by other replies, there doesn't seem to be a
    > > "fixed" answer and in fact why should there be a fixed answer. It's more
    > > likely that the limitation would be the specification of the machine you're
    > > using for the server.
    > >
    > > Consider ISP's that provide MySQL databases as part of the hosting packages.
    > > My ISP only provides me with two databases, but multiply that by the number
    > > of clients they have and you will soon realise that they must have thousands
    > > per server.
    > >
    > > SA[/ref]
    >
    > What I think is:
    >
    > MySQL should have a limit anyway, just as max data length, max row
    > size, etc.
    >
    > but no one know and can't find in the doc.[/ref]

    What you have written doees not make any sense. I think what you meant
    to write was:

    "MySQL does have a limit anyway, just as max data length, max row size,
    etc. but no one know and can't find in the doc."

    (I haven't corrected all the English, just enough to give it meaning).

    If you really think it "should" have a limit, then WHY should it. If
    there does not need to be one, why introduce an arbitrary one?

    If you think it does have a limit, then "someone" knows. But why do you
    think it has one. If it has no limit on the number of tables, why do
    you think it has one for the number of databases?

    Captain Guest

  13. #13

    Default Re: Max. no of database in a server?


    Captain Paralytic 寫道:
     
    > >
    > > What I think is:
    > >
    > > MySQL should have a limit anyway, just as max data length, max row
    > > size, etc.
    > >
    > > but no one know and can't find in the doc.[/ref]
    >
    > What you have written doees not make any sense. I think what you meant
    > to write was:
    >
    > "MySQL does have a limit anyway, just as max data length, max row size,
    > etc. but no one know and can't find in the doc."
    >
    > (I haven't corrected all the English, just enough to give it meaning).
    >
    > If you really think it "should" have a limit, then WHY should it. If
    > there does not need to be one, why introduce an arbitrary one?[/ref]

    it should be part of the specification...

    howa Guest

  14. #14

    Default Re: Max. no of database in a server?

    howa wrote: 
    >>
    >>What you have written doees not make any sense. I think what you meant
    >>to write was:
    >>
    >>"MySQL does have a limit anyway, just as max data length, max row size,
    >>etc. but no one know and can't find in the doc."
    >>
    >>(I haven't corrected all the English, just enough to give it meaning).
    >>
    >>If you really think it "should" have a limit, then WHY should it. If
    >>there does not need to be one, why introduce an arbitrary one?[/ref]
    >
    >
    > it should be part of the specification...
    >[/ref]

    What part of NO LIMIT don't you understand?

    Any limitations will be imposed by your OS, disk, etc., not MySQL.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  15. #15

    Default Re: Max. no of database in a server?


    Jerry Stuckle 寫道:
     
    > >
    > >
    > > it should be part of the specification...
    > >[/ref]
    >
    > What part of NO LIMIT don't you understand?
    >
    > Any limitations will be imposed by your OS, disk, etc., not MySQL.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    what i think is :

    if there is NO LIMIT, I think the manual should state clearly there is
    really NO LIMIT, just as the others

    howa Guest

  16. #16

    Default Re: Max. no of database in a server?

    >what i think is : 

    If there is NO LIMIT on the number of 'a' characters appearing in
    table names (all tables in all databases combined), then the manual
    should state clearly there is really NO LIMIT. (But we all know
    this can't easily exceed 256TB because of limits on disk size).

    If there is NO LIMIT on the number of ALTER TABLE statements you
    can execute on a given server in your lifetime, then the manual
    should state clearly that there is really NO LIMIT. (But as a
    practical matter, your life expectancy expressed in microseconds
    is likely to be way over what you can actually do.

    If there is NO LIMIT on the number of people that you can safely
    carry in an elevator, how come elevators can get overloaded?
    (The limit isn't on the number, it's on the weight.)

    For MySQL, the question of "how many databases can you have on a
    server?" roughly comes down to "how many subdirectories can you
    have in a directory?", which is an OS issue. For FreeBSD, I think
    that limit is 65533, assuming you don't use symlinks. If you do
    use symlinks (to directories elsewhere), it may come down to "how
    many files (symlinks) can you have in a filesystem?" For FreeBSD,
    this is about 2**32 minus a half a dozen or so.

    Now, how many databases can you have on a server before looking up
    the database name in the directory kills performance? (I recall a
    version of Xenix where UUCP having more than 100,000 spool files
    in the spool directory (no UUCP subdirectories in this version)
    started causing communication timeouts. This was on mid-1980's
    hardware, and CPUs and disks were pretty slow, and memory scarce.
    I think you could have 65,533 subdirectories (databases) without
    too much degradation.

    And how much are you going to USE these databases? At one query a
    minute for all databases combined, you could probably handle a
    billion databases with no problem. You might run into troubles
    with the number of SIMULTANEOUSLY OPEN tables allowed, as many OS
    don't allow a process (MySQL server) to have more than a few thousand
    open files simultaneously. You can run into this problem with ONE
    database and a few thousand tables, also.

    MySQL may try to cache the user tables in memory, and if you've got
    complex permission tables with a different user for each database of
    a couple million of them, you might start feeling the limits of a 4G
    address space on x86 hardware.

    Gordon Guest

Similar Threads

  1. Read Point of Sale database and update server database
    By turtle in forum Macromedia ColdFusion
    Replies: 4
    Last Post: July 20th, 09:41 PM
  2. When the database server is down...
    By frankjshin in forum Coldfusion - Advanced Techniques
    Replies: 5
    Last Post: July 11th, 05:53 PM
  3. Replies: 3
    Last Post: July 13th, 02:58 AM
  4. connecting to a database (with no web server)
    By dominant in forum PERL Miscellaneous
    Replies: 14
    Last Post: August 12th, 11:10 AM
  5. Cache Database Server
    By Sam Poikail in forum Macromedia Dreamweaver
    Replies: 0
    Last Post: July 8th, 05:27 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139