Hello folks

I need to run multiple instances of MySQL 5.1 server (run as service,
database type is InnoDB) on the same windows computer (Windows Server
2008). I have been using this resource:
http://dev.mysql.com/doc/refman/5.1/...d-cmdline.html

I still have a couple of things I do not understand:
1. What's the deal with ibdata1 file? Is this all right if it is
shared by all the instances? Or shall I point to a separate file for
each instance in the corresponding ini file?

For example sharing the datadir folder between instances is said to be
a bad idea.

2. To be able to have multiple instances of the service I intend to
run bat scripts (containing different values for port, service name,
ini file name, etc) to configure each instance.

Among other things I create a separate ini file for each instance. The
challenge is to find an argument for MySQLInstanceConfig.exe that
will set the "datadir" path in the ini file. It cannot be the case
that one has to edit each ini file manually after
MySQLInstanceConfig.exe has created them, can it?

Thanks in advance for any help,
Anatoly

In article <d75e21c9-8a49-4a79-aee5-9f832792164a (AT) g1g2000vbd (DOT) googlegroups.com>,
anatoly <anatoly71 (AT) gmail (DOT) com> wrote:
Why do you believe you need to run multiple instances? What problem are
you trying to solve, for which you think that is the solution?

A single instance of the server can easily serve multiple databases to
multiple users.

Cheers
Tony
--
Tony Mountifield
Work: tony (AT) softins (DOT) co.uk - http://www.softins.co.uk
Play: tony (AT) mountifield (DOT) org - http://tony.mountifield.org

On 7 Nov, 12:24, t... (AT) mountifield (DOT) org (Tony Mountifield) wrote:
Hello Tony

Well, the amount of clients is expected to be so hight that some
resources might be exhausted. In particular number of connections
(amount of concurrent sessions) might be over 2000. It seems safer to
have the option of having 2 or 3 instances of MySql server and letting
different groups of client programs use different service instances.

In article <4b3eb473-563e-41b6-a03b-e9162f4f6395 (AT) f13g2000vbv (DOT) googlegroups.com>,
anatoly <anatoly71 (AT) gmail (DOT) com> wrote:
OK, I would start by studying and setting the appropriate variables of
a single MySQL instance, and only trying multiple instances if you need
to do so. After all, if you have several instances on the same machine,
you need the clients to know which one to connect to, and will have to
have all but one of them listening on a non-standard port.

The following page gives information on running multiple instances:
http://dev.mysql.com/doc/refman/5.1/...e-servers.html

Since each instance must have a separate data directory, they will each
have their own ibdata1 file which will not be shared. You can't make
multiple instances share databases or tables; they will each have their
own completely separate set.

If the different groups of clients need to access the same tables,
then multiple SQL instances is not the way to go. Instead, study how
to scale a single instance appropriately.

Cheers
Tony
--
Tony Mountifield
Work: tony (AT) softins (DOT) co.uk - http://www.softins.co.uk
Play: tony (AT) mountifield (DOT) org - http://tony.mountifield.org

anatoly <anatoly71 (AT) gmail (DOT) com> wrote:
I too doubt that this will solve your problem, if it is this:

You will probably hit another problem much earlier:

http://bugs.mysql.com/bug.php?id=26590

oh, and also

http://dev.mysql.com/doc/refman/5.1/...ver-on-windows

Not to mention the general performance drop with that many server
threads.


Also:

this will not work if you want (as I guess) those MySQL instances share
the same data.

The ibdata1 file is the global InnoDB table space that holds all system
tables and (in the absence of --innodb-file-per-table) all user tables
too. Certainly each MySQL instance needs it's own copy of this file.

B*llsh*t. You want several my.ini files. Then create multiple Windoze
services, each using it's own ini file. Anything you need to set, can
be set in the ini file.

I suggest you configure one instance with the wizard. Then copy and
edit the resulting ini file. As almost all such wizards - they are for
newbies. Experienced users write the my.ini from scratch.


PS: I severely doubt that you *really* need 2000 or more *active*
connections into the MySQL server. First review your application if it
correctly closes inactive connections. And STAY AWAY from persistent
connections. Or connection pools.

If you really, really need this - then go for 5.5 and the thread pool.
Alternatively look at replication and the scenario called "scaleout".

As always, RTFM for details.


XL

Why do you need to run multiple instances, as distinguished from
multiple computers? If you have a resource problem using multiple
computers will raise resources faster than multiple instances on
the same computer.

You cannot share tables between instances (so you may end up
duplicating tables between instances, which just made your resource
problem worse, not better). If that doesn't kill the idea of
multiple instances, it's only one step further to putting the
instances on separate computers, so you don't have to share things
like CPU power, memory, the system-wide limit on open files, etc.

It's normally in the data directory, so don't share it.

You cannot share tables between instances. Even if the code could
do it without corrupting data, using OS locking rather than internal
MySQL bookkeeping is inefficient and cuts down on the speed of the
instances. In other words, running two instances with shared tables,
if it worked at all without corrupting data, might handle 75% of
what one instance can.

You cannot share tables (or anything writable, like log files, or
the socket file, or .pid file, or master.info, etc.) between
instances, regardless of how you try to do it.

Ok, if you insist on this approach.

I don't know about MySQLInstanceConfig. If you set the data directory
on the command line for invocation of mysqld, that overrides the
value in the .ini file. On Windows you would probably set up a
separate service for each instance.

It appears that the data directory is the "Data" subdirectory of
the base directory. You *CAN* set the base directory with
MySQLInstanceConfig. It seems that MySQLInstanceConfig is expecting
that you will use different MySQL versions for the instances, so
you need separate versions of the code, too.

The main reasons for multiple instances on the same computer are:
- You want to test multiple versions of the MySQL server with your code,
and you don't have money or space for multiple computers.
- You are short on resources and you need more computers but can't afford
or don't have space for them.
- As a security policy, you don't want the data of two different customers
on the same MySQL instance. Separate computers would be better.
- The instances need different administrators.