On Wednesday 31 October 2001 21:42, Gary Huntress wrote:
> I'm up to over 10000 databases in one data directory and that is
> becoming a performance problem as users make their connections. I would
> like to begin running multiple servers on the same box. I made my
> first attempt tonight and was confused. Before I screw up a production
> box perhaps someone can tell me what I'm doing wrong.
Wow. That's a lot!
> I always start my server with this script:
>
> safe_mysqld --log --datadir=/mnt/db -O wait_timeout=1000 -O
> max_connections=1000 &
We make totally separate directories:
/database/instance1
/database/instance2
Within each tree, we have everything MySQL needs except the
binaries.
I.e.
/database/instance1/etc config including my.cnf
/database/instance1/run pid file, Unix socket
/database/instance1/log log files
/database/instance1/bin tuned scripts to run _this_ instance only
/database/instance1/var database table directories for this instance
/database/instance1/tmp temp dir for this instance.
We leave the mysqld etc. binaries wherever they normally live.
We start the whole thing with a slightly hacked version of safe_mysqld
safe_mysqld --defaults-file=/database/instance1/etc/my.cnf
That my.cnf file defines the port, socket, log files, data directory, temp directory
etc.
The scripts in .../instance1/bin are very important for what we do. They have
hardcoded data in them to make them only work on that instance of the
database. Thus, we can start and stop and instance by going into the
instance-specific bin directory and running a script:
db_ctrl stop
We do _not_ have to pass the instance path. The reason for this is that it helps
prevent a whole class of errors that the system admins might make accidentally
by typing the wrong instance. They need to actually type the full path to the
instance bin dir. That has helped reduce errors a lot.
> I still have an old data directory in /usr/local/mysql/var so I thought
> I could just change the pid, socket, and data directory via:
>
> safe_mysqld --pid-file=/usr/local/mysql/var/kettlebrook.pid
> --socket=/tmp/ mysql3307.sock --port=3307 --datadir=/usr/local/mysql/var
> -O
> wait_timeout=1000 -O max_connections=1000 &
>
> to run it on port 3307.
But, your Unix socket is in /tmp without any differentiation from the other
instance.
> The primary server starts fine. When I run the second one, I have
> gotten address already in use errors ("is there already a server
> running?" or something to that effect) I thought I had successfully
> created a second *independent* server that nobody else should be using
> so I did:
The address in use might be from the other socket, not the IP address.
I would run mysqld directly on the command line and see what errors it tells you.
I've had better luck debugging my my.cnf files that way than trying to use
safe_mysqld.
> mysql -pmypassword -P3307
This will connect to the Unix port on localhost. If you want to connect to
the IP side, you'll have to specify a host that is not localhost (the default
above).
> connected ok , and did a processlist. It showed numerous web
> connections, and it apppeared that I was connected to my production
> server on 3306 rather than 3307.
Use "SHOW VARIABLES" to make sure that the data dir is where you think
it is supposed to be.
> Then, I issued "mysqladmin -pmypassword -P3307 shutdown" hoping to shut
> down the second test server. It shut down the main server on 3306, as
> evidenced by my webserver indicating connection problems. I was able to
> repeat this several times.
Note above. You are not connecting to the port that you think you are.
If you do not pass a hostname other than localhost, you'll connect
through the Unix socket.
> My question is basically this....did I somehow (probably in my first
> lame attempts) get some of my socket files mixed up and/or pid files and
> they were simply out of sync with the proper server? 2) Should I be
> able to properly control two servers using the two safe_mysqld command
> lines above?
We do it all the time. Just make sure that your sockets are differentiated and
that you are using a FQDN if you are trying to connect via IP instead of
a Unix socket.
Best,
Kyle
bot fodder: sql, mysql, query
--
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
MicroTelco Services saves money on every Fax:
- Fax to email (FREE)
- Fax to PSTN based Fax (Up to 95% Savings)
- Fax Broadcasting: Send 100s of faxes to fax machines
and email addresses in the time it takes to send just one!
===========================================================
So send a fax today and let us know what you think!
For more info. visit: www.internetfaxjack.com
===========================================================
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
