Max. no of database in a server?

"howa" wrote:

> 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 Database in a single server?

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/

howa wrote:

> Sean ¼g¹D¡G
>
> > "howa" wrote in message
> > news:1167818610.165070.102640-at-v33g2000cwv.googlegr oups.com...
> > > 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 Database in a single server?
> > >
> > > Thanks.
> > >
> >
> >
> > 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
>
> 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.

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?

howa wrote:

> Sean ¼g¹D¡G
>
> > "howa" wrote in message
> > news:1167818610.165070.102640-at-v33g2000cwv.googlegr oups.com...
> > > 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 Database in a single server?
> > >
> > > Thanks.
> > >
> >
> >
> > 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
>
> 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.

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 Paralytic ¼g¹D¡G

> howa wrote:
>
> > Sean ¼g¹D¡G
> >
> > > "howa" wrote in message
> > > news:1167818610.165070.102640-at-v33g2000cwv.googlegr oups.com...
> > > > 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 Database in a single server?
> > > >
> > > > Thanks.
> > > >
> > >
> > >
> > > 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
> >
> > 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.
>
> 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?

it should be part of the specification...

Captain Paralytic ¼g¹D¡G

> howa wrote:
>
> > Sean ¼g¹D¡G
> >
> > > "howa" wrote in message
> > > news:1167818610.165070.102640-at-v33g2000cwv.googlegr oups.com...
> > > > 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 Database in a single server?
> > > >
> > > > Thanks.
> > > >
> > >
> > >
> > > 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
> >
> > 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.
>
> 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?

it should be part of the specification...

howa wrote:
> Captain Paralytic ¼g¹D¡G
>
>
>>howa wrote:
>>
>>
>>>Sean ¼g¹D¡G
>>>
>>>
>>>>"howa" wrote in message
>>>>news:1167818610.165070.102640-at-v33g2000cwv.googlegr oups.com...
>>>>
>>>>>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 Database in a single server?
>>>>>
>>>>>Thanks.
>>>>>
>>>>
>>>>
>>>>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
>>>
>>>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.
>>
>>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?
>
>
> it should be part of the specification...
>

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.
jstucklex-at-attglobal.net
==================

howa wrote:
> Captain Paralytic ¼g¹D¡G
>
>
>>howa wrote:
>>
>>
>>>Sean ¼g¹D¡G
>>>
>>>
>>>>"howa" wrote in message
>>>>news:1167818610.165070.102640-at-v33g2000cwv.googlegr oups.com...
>>>>
>>>>>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 Database in a single server?
>>>>>
>>>>>Thanks.
>>>>>
>>>>
>>>>
>>>>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
>>>
>>>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.
>>
>>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?
>
>
> it should be part of the specification...
>

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.
jstucklex-at-attglobal.net
==================

Jerry Stuckle ¼g¹D¡G

> howa wrote:
> > Captain Paralytic ¼g¹D¡G
> >
> >
> >>howa wrote:
> >>
> >>
> >>>Sean ¼g¹D¡G
> >>>
> >>>
> >>>>"howa" wrote in message
> >>>>news:1167818610.165070.102640-at-v33g2000cwv.googlegr oups.com...
> >>>>
> >>>>>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 Database in a single server?
> >>>>>
> >>>>>Thanks.
> >>>>>
> >>>>
> >>>>
> >>>>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
> >>>
> >>>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.
> >>
> >>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?
> >
> >
> > it should be part of the specification...
> >
>
> 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.
> jstucklex-at-attglobal.net
> ==================

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

Jerry Stuckle ¼g¹D¡G

> howa wrote:
> > Captain Paralytic ¼g¹D¡G
> >
> >
> >>howa wrote:
> >>
> >>
> >>>Sean ¼g¹D¡G
> >>>
> >>>
> >>>>"howa" wrote in message
> >>>>news:1167818610.165070.102640-at-v33g2000cwv.googlegr oups.com...
> >>>>
> >>>>>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 Database in a single server?
> >>>>>
> >>>>>Thanks.
> >>>>>
> >>>>
> >>>>
> >>>>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
> >>>
> >>>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.
> >>
> >>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?
> >
> >
> > it should be part of the specification...
> >
>
> 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.
> jstucklex-at-attglobal.net
> ==================

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

>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

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.