Multiple connections

[sane79]

Hi all,

I have a few questions here.

How do I connect to multiple databases through MySQL?
I need to get connected to a minimum of 2 different databases per connection.
Can anyone tell me if its possible.. and how can it be achieved.

Another question is, what is the max number of users, MYSQL can support at the same time. Do I need to do any form of coding.. in order to specify how many users is allowed to log on to the server at any one time to access the databases/tables?

Thanks!

 

[vanekl]

If all your database connections don't have to be atomic
(i.e., if it's OK for some of the database operations to complete
and others not, for example, in a power outage), then you
connect to N databases exactly the same way you connect to one
database, except of course the DB connection string corresponds
to the different database).

If you _must_ have full transaction support between 2 or more
databases then you will probably want to invest in a transaction
monitor. This gets complicated real fast and I don't want to
get into it. I would recommend you stay away from them unless
it's absolutely necessary.

In most instances, the MySQL database will not be the bottleneck,
unless you have an extremely loaded site. RAM is many times
the first rookie mistake, but that's easy to fix. Forgetting
to tune the MySQL parameters is another rookie mistake that
slows MySQL down to a crawl, but that's easily fixed, too.
Where you will start to run into a wall is with disk speed,
or the lack thereof. If you truely have a heavily loaded site
you will have to distribute your tables over several hard
drives, and your logs and tmp space, too. MySQL is not
capable of parallel operation so under extremely high load
you will top out at whatever your one machine's hardware
can produce for you. If you were using a parallel database
you could always just add more machines. Not with MySQL,
unless you count replication, but replication only gets you
so far.

In general, you do not have to code based on the number of
users using your database. What you DO have to do is design
your database so that it is efficient. That means you have to
make sure your database is normalized (designed correctly),
you have indexes on all of your most important search keys,
you bought hardware that is up to the challenge, gobs of RAM,
lots of fast, SCSI drives, or perhaps a big RAID,
got good, fast networking connections set up, etc.
It's not the code, so much as the proper design and set-up.