RE: ~ How to install 3 instances of mysql~
I just did this last week on a 5.0.18 machine. It's supported by the
mysqlmanager out of the box. Here are a copy of my notes, and worked
well on a Suse machine. The locations of your files may not be the
same.
# stop the server, if running
/etc/init.d/mysql stop
# edit /etc/my.cnf to set up the instances and
# and also tell the startup script to use mysqlmanager
[mysql.server]
use-manager
[mysqld07]
port= 3307
socket = /srv/mysql/lx07sock
pid-file= /srv/mysql/lx07/lx09.pid07
datadir = /srv/mysql/lx07/data
log-error = /srv/mysql/lx07/mysql.error.log
[mysqld20]
port= 3320
socket = /srv/mysql/lx20sock
pid-file= /srv/mysql/lx20/lx09.pid20
datadir = /srv/mysql/lx20/data
log-error = /srv/mysql/lx20/mysql.error.log
# run commands as user mysql
su mysql
# go to the main mysql directory
cd /srv/mysql
# make a directory for each instance
mkdir lx07
mkdir lx20
mysql_install_db --datadir=/srv/mysql/lx07/data --user=mysql --verbose
mysql_install_db --datadir=/srv/mysql/lx20/data --user=mysql --verbose
# start the server
/etc/init.d/mysql start
# connect to first instance and configure so root can log in
# from anywhere. You may or may not want to do this.
mysql --socket=/srv/mysql/lx07sock
create user 'root'@'%' identified by 'password'
grant all on *.* to 'root'@'%' identified by 'password;
use mysql;
update user set password=password('password') where user='root';
flush privileges
# same for second instance
mysql --socket=/srv/mysql/lx20sock
create user 'root'@'%' identified by 'password'
grant all on *.* to 'root'@'%' identified by 'password';
use mysql;
update user set password=password('password') where user='root';
flush privileges
Regards,
Rich
-Original Message-
From: Mohammed Abdul Azeem [mailto:[EMAIL PROTECTED]
Sent: Saturday, April 15, 2006 12:53 AM
To: mysql@lists.mysql.com
Subject: ~ How to install 3 instances of mysql~
Hi,
I need to install 3 instances of mysqld server on a single
machine. Can anyone let me know how this can be acheived ?
It would be helpful if someone can send me some links and
suggestions regarding the same. Also pls lemme know what kind
of a configuration file i need to have in order to acheive the same.
Thanks in advance,
Abdul.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ~ How to install 3 instances of mysql~
Thanks Rich.
On Mon, 2006-04-17 at 08:28 -0500, Duzenbury, Rich wrote:
I just did this last week on a 5.0.18 machine. It's supported by the
mysqlmanager out of the box. Here are a copy of my notes, and worked
well on a Suse machine. The locations of your files may not be the
same.
# stop the server, if running
/etc/init.d/mysql stop
# edit /etc/my.cnf to set up the instances and
# and also tell the startup script to use mysqlmanager
[mysql.server]
use-manager
[mysqld07]
port= 3307
socket = /srv/mysql/lx07sock
pid-file= /srv/mysql/lx07/lx09.pid07
datadir = /srv/mysql/lx07/data
log-error = /srv/mysql/lx07/mysql.error.log
[mysqld20]
port= 3320
socket = /srv/mysql/lx20sock
pid-file= /srv/mysql/lx20/lx09.pid20
datadir = /srv/mysql/lx20/data
log-error = /srv/mysql/lx20/mysql.error.log
# run commands as user mysql
su mysql
# go to the main mysql directory
cd /srv/mysql
# make a directory for each instance
mkdir lx07
mkdir lx20
mysql_install_db --datadir=/srv/mysql/lx07/data --user=mysql --verbose
mysql_install_db --datadir=/srv/mysql/lx20/data --user=mysql --verbose
# start the server
/etc/init.d/mysql start
# connect to first instance and configure so root can log in
# from anywhere. You may or may not want to do this.
mysql --socket=/srv/mysql/lx07sock
create user 'root'@'%' identified by 'password'
grant all on *.* to 'root'@'%' identified by 'password;
use mysql;
update user set password=password('password') where user='root';
flush privileges
# same for second instance
mysql --socket=/srv/mysql/lx20sock
create user 'root'@'%' identified by 'password'
grant all on *.* to 'root'@'%' identified by 'password';
use mysql;
update user set password=password('password') where user='root';
flush privileges
Regards,
Rich
-Original Message-
From: Mohammed Abdul Azeem [mailto:[EMAIL PROTECTED]
Sent: Saturday, April 15, 2006 12:53 AM
To: mysql@lists.mysql.com
Subject: ~ How to install 3 instances of mysql~
Hi,
I need to install 3 instances of mysqld server on a single
machine. Can anyone let me know how this can be acheived ?
It would be helpful if someone can send me some links and
suggestions regarding the same. Also pls lemme know what kind
of a configuration file i need to have in order to acheive the same.
Thanks in advance,
Abdul.
This email has been Scanned for Viruses!
www.newbreak.com
This email has been Scanned for Viruses!
www.newbreak.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ~ How to install 3 instances of mysql~
Hi Logan,
Thanks for the links. But how am i suppose to allocate my physical
memory for the 3 mysql instances. Pls guide me on the innodb memory
settings. My total RAM memory is 1 GB.
Thanks in advance,
Abdul.
On Sat, 2006-04-15 at 23:35 +1000, Logan, David (SST - Adelaide) wrote:
Hi,
Also a read of
http://dev.mysql.com/doc/refman/5.0/en/multiple-servers.html and
http://dev.mysql.com/doc/refman/5.0/en/mysqld-multi.html would probably
be quite helpful. The second gives you a good idea on how to setup the
my.cnf file for multiple servers. The concepts are the same for version
4.1
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Saturday, 15 April 2006 10:45 PM
To: mysql@lists.mysql.com
Subject: Re: ~ How to install 3 instances of mysql~
On Sat, 15 Apr 2006, Mohammed Abdul Azeem wrote:
To: [EMAIL PROTECTED]
From: Mohammed Abdul Azeem [EMAIL PROTECTED]
Subject: Re: ~ How to install 3 instances of mysql~
Hello Keith,
Thank you very much for your guidence.
This is my existing my.cnf file
[mysqld]
port= 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
thread_cache = 8
query_cache_size = 32M
log_bin_trust_routine_creators = 1
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
#specify the storage engine
default-storage-engine = InnoDB
#specify the table type
default-table-type = InnoDB
#enable the full query log
log
#Print warnings to error log file
log_warnings
#specify max connections
max_connections = 30
#specify max user connections
max_user_connections = 12
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /mysql-system/mysql/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /mysql-system/mysql/data/
innodb_log_arch_dir = /mysql-system/mysql/data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5242880
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 100
The variables key_buffer = 384M, innodb_buffer_pool_size = 384M,
innodb_additional_mem_pool_size = 20M, innodb_log_buffer_size = 8M
are set for 1 instance.
My RAM memory is 1GB. Can i have the same values for the above
variables
set for all the 3 instances ? if yes, will that not exceed the
available
RAM memory. Please help me set the correct values for these variables
for each instance.
Well, if you are running multiple instances of mysqld
concurrently, then I guess you will have to share the system
resorces as well, such as memory and file handles.
Note that if you are using qps to monitor processes, for
each mysqld you have running, the memory usage will increase
appropriately.
I only use MyISAM tables the moment, so I cannot help you
with your InnoDB table settings.
Kind Regards
Keith Roberts
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
This email has been Scanned for Viruses!
www.newbreak.com
This email has been Scanned for Viruses!
www.newbreak.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ~ How to install 3 instances of mysql~
Hi Abdul,
As I only use MyISAM, I can only reinforce Keith Roberts mentioned,
these are three separate instances and will require three separate
memory spaces.
Have you looked here?
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html and
http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html
I could also recommend the book by Jeremy Zawodny, High Performance
MySQL. This has a fair bit of information which would be helpful to you.
Chapter 6, Server Tuning, is on the mysql website here
http://dev.mysql.com/books/hpmysql-excerpts/ch06.html
Sorry, that is about all the help I can be as I am no InnoDB expert.
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Mohammed Abdul Azeem [mailto:[EMAIL PROTECTED]
Sent: Monday, 17 April 2006 12:35 PM
To: Logan, David (SST - Adelaide)
Cc: mysql@lists.mysql.com; [EMAIL PROTECTED]
Subject: RE: ~ How to install 3 instances of mysql~
Hi Logan,
Thanks for the links. But how am i suppose to allocate my physical
memory for the 3 mysql instances. Pls guide me on the innodb memory
settings. My total RAM memory is 1 GB.
Thanks in advance,
Abdul.
On Sat, 2006-04-15 at 23:35 +1000, Logan, David (SST - Adelaide) wrote:
Hi,
Also a read of
http://dev.mysql.com/doc/refman/5.0/en/multiple-servers.html and
http://dev.mysql.com/doc/refman/5.0/en/mysqld-multi.html would
probably
be quite helpful. The second gives you a good idea on how to setup the
my.cnf file for multiple servers. The concepts are the same for
version
4.1
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Saturday, 15 April 2006 10:45 PM
To: mysql@lists.mysql.com
Subject: Re: ~ How to install 3 instances of mysql~
On Sat, 15 Apr 2006, Mohammed Abdul Azeem wrote:
To: [EMAIL PROTECTED]
From: Mohammed Abdul Azeem [EMAIL PROTECTED]
Subject: Re: ~ How to install 3 instances of mysql~
Hello Keith,
Thank you very much for your guidence.
This is my existing my.cnf file
[mysqld]
port= 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
thread_cache = 8
query_cache_size = 32M
log_bin_trust_routine_creators = 1
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
#specify the storage engine
default-storage-engine = InnoDB
#specify the table type
default-table-type = InnoDB
#enable the full query log
log
#Print warnings to error log file
log_warnings
#specify max connections
max_connections = 30
#specify max user connections
max_user_connections = 12
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /mysql-system/mysql/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /mysql-system/mysql/data/
innodb_log_arch_dir = /mysql-system/mysql/data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5242880
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 100
The variables key_buffer = 384M, innodb_buffer_pool_size = 384M,
innodb_additional_mem_pool_size = 20M, innodb_log_buffer_size = 8M
are set for 1 instance.
My RAM memory is 1GB. Can i have the same values for the above
Re: ~ How to install 3 instances of mysql~
Hi Mohammed - yes it is possible to install multiple instances of mysqld on one machine. Under SuSE Linux 9.2 I have had 3 instances of mysqld running. I would suggest the following. Use the generic static pre-compiled distibution. Linux (non RPM package) downloads (platform notes) Linux (x86, glibc-2.2, standard is static, gcc) Standard5.0.20 30.3M Pick a mirror MD5: 5b0471380db88b03267bbabde500b7e0 | Signature For each server you want to run, install a copy of the above distribution into a seperate base directory. eg. /usr/local/mysql-5.0.20-srv1 /usr/local/mysql-5.0.20-srv2 /usr/local/mysql-5.0.20-srv3 You will need to install a seperate data directory for each server, as it could corrupt the database if multiple mysqld's are writing to the same database. Each server can be started using a shell script that lives in it's own server directory, in this case; /usr/local/mysql-5.0.20-srv1/bin/start-mysql-5.0.20 contents of start-mysql-5.0.20-srv1 would be something like: #! /bin/sh # # start the MySQL database server srv1 instance /usr/local/mysql-5.0.20-srv1/bin/mysqld \ --defaults-file=/usr/local/mysql-5.0.20-srv1/my.cnf \ --port=7000 \ --socket=/usr/local/mysql-5.0.20-srv1/data/mysql.sock \ --pid=/usr/local/mysql-5.0.20-srv1/data/srv1.pid \ --user=mysql \ --datadir=/usr/local/mysql-5.0.20-srv1 Each server instance can have it's own my.conf file, such as **you will need to change /usr/local/mysql-5.0.18 to match your own installations** # /usr/local/mysql-version/my.cnf # MySQL server configuration file # last updated 2006-03-08 # mysql client program configuration file lives in /etc/my.cnf # # mysqld server configuration options # [mysqld] basedir=/usr/local/mysql-5.0.18 ## use for testing multiple instances of mysqld ## these parameters are normally passed to mysqld ## from the start-mysql-5.0.18 script ## ##basedir=/usr/local/mysql-5.0.18 ##port=7005 ##socket=/usr/local/mysql-5.0.18/data/mysql.sock ##pid-file=/usr/local/mysql-5.0.18/data/laptop.pid ##datadir=/usr/local/mysql-5.0.18/data ##user=mysql server-id=1 #skip-networking skip-name-resolve skip-locking set-variable = key_buffer=16M set-variable = max_allowed_packet=1M set-variable = table_cache=64 set-variable = sort_buffer=512K set-variable = net_buffer_length=8K set-variable = myisam_sort_buffer_size=8M # logging options log=5-0-18.log log-bin=laptop-bin log-error=5-0-18.error-log log-queries-not-using-indexes log-slow-admin-statements log-slow-queries=5-0-18.slow-log log-warnings # # end of mysqld server configuration file # /usr/local/mysql-version/my.cnf The /etc/my.cnf can be used to set parameters for all the mysql clients. # /etc/my.cnf # MySQL client program configuration file # last updated 2006-03-05 # mysqld server configuration file lives in # /usr/local/mysql-version/my.cnf #--- # mysql client program configuration options #--- [mysql] no-auto-rehash # needed for security - to stops multiple deletes/updates # without a where clause safe-updates # # The following options will be passed to all MySQL clients [client] socket = /var/lib/mysql/mysql.sock port = # [myisamchk] set-variable = key_buffer=20M set-variable = sort_buffer=20M set-variable = read_buffer=2M set-variable = write_buffer=2M # [mysqldump] quick set-variable = max_allowed_packet=16M # # available programs/scripts are: #my_print_defaults mysqladmin #myisamchk mysqlbinlog #myisamlog mysqlbug - n/a #myisampack mysqlcheck #mysql mysqld #mysql_convert_table_format - .plmysqld_multi #mysql_find_rows mysqldump #mysql_fix_privilege_tables n/a mysqlhotcopy - .pl #mysql_install_dbmysqlimport #mysql_setpermission - .pl mysqlshow #mysql_zap mysqltest #mysqlaccess - .pl safe_mysqld # # end of mysql client program configurations # /etc/my.cnf To monitor the mysql instances and the parameters passed to each one, you could use a process monitoring tool like: http://www.student.nada.kth.se/~f91-men/qps/ To cleanly shut down the
Re: ~ How to install 3 instances of mysql~
Create 3 my.cnf files with different ports and sockets: [mysqld] port = 3306 socket=/var/lib/mysql/mysql1.sock . Launch server with: bin/safe_mysqld --defaults-file=/usr/local/mysql/bin/my1.cnf client: /usr/local/mysql/bin/mysql -S /var/lib/mysql/mysql1.sock stop: mysqladmin -S /var/lib/mysql/mysql1.sock shutdown I run MySql 4 5 in the same CPU. (different databases). Santino Cusimano At 11:23 +0530 15-04-2006, Mohammed Abdul Azeem wrote: Hi, I need to install 3 instances of mysqld server on a single machine. Can anyone let me know how this can be acheived ? It would be helpful if someone can send me some links and suggestions regarding the same. Also pls lemme know what kind of a configuration file i need to have in order to acheive the same. Thanks in advance, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~ How to install 3 instances of mysql~
Hello Keith, Thank you very much for your guidence. This is my existing my.cnf file [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M thread_cache = 8 query_cache_size = 32M log_bin_trust_routine_creators = 1 # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 #specify the storage engine default-storage-engine = InnoDB #specify the table type default-table-type = InnoDB #enable the full query log log #Print warnings to error log file log_warnings #specify max connections max_connections = 30 #specify max user connections max_user_connections = 12 # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /mysql-system/mysql/data/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /mysql-system/mysql/data/ innodb_log_arch_dir = /mysql-system/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 5242880 innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 100 The variables key_buffer = 384M, innodb_buffer_pool_size = 384M, innodb_additional_mem_pool_size = 20M, innodb_log_buffer_size = 8M are set for 1 instance. My RAM memory is 1GB. Can i have the same values for the above variables set for all the 3 instances ? if yes, will that not exceed the available RAM memory. Please help me set the correct values for these variables for each instance. Thanks in advance, Abdul. On Sat, 2006-04-15 at 09:11 +0100, [EMAIL PROTECTED] wrote: Hi Mohammed - yes it is possible to install multiple instances of mysqld on one machine. Under SuSE Linux 9.2 I have had 3 instances of mysqld running. I would suggest the following. Use the generic static pre-compiled distibution. Linux (non RPM package) downloads (platform notes) Linux (x86, glibc-2.2, standard is static, gcc) Standard 5.0.20 30.3M Pick a mirror MD5: 5b0471380db88b03267bbabde500b7e0 | Signature For each server you want to run, install a copy of the above distribution into a seperate base directory. eg. /usr/local/mysql-5.0.20-srv1 /usr/local/mysql-5.0.20-srv2 /usr/local/mysql-5.0.20-srv3 You will need to install a seperate data directory for each server, as it could corrupt the database if multiple mysqld's are writing to the same database. Each server can be started using a shell script that lives in it's own server directory, in this case; /usr/local/mysql-5.0.20-srv1/bin/start-mysql-5.0.20 contents of start-mysql-5.0.20-srv1 would be something like: #! /bin/sh # # start the MySQL database server srv1 instance /usr/local/mysql-5.0.20-srv1/bin/mysqld \ --defaults-file=/usr/local/mysql-5.0.20-srv1/my.cnf \ --port=7000 \ --socket=/usr/local/mysql-5.0.20-srv1/data/mysql.sock \ --pid=/usr/local/mysql-5.0.20-srv1/data/srv1.pid \ --user=mysql \ --datadir=/usr/local/mysql-5.0.20-srv1 Each server instance can have it's own my.conf file, such as **you will need to change /usr/local/mysql-5.0.18 to match your own installations** # /usr/local/mysql-version/my.cnf # MySQL server configuration file # last updated 2006-03-08 # mysql client program configuration file lives in /etc/my.cnf # # mysqld server configuration options # [mysqld] basedir=/usr/local/mysql-5.0.18 ## use for testing multiple instances of mysqld ## these parameters are normally passed to mysqld ## from the start-mysql-5.0.18 script ## ##basedir=/usr/local/mysql-5.0.18 ##port=7005 ##socket=/usr/local/mysql-5.0.18/data/mysql.sock ##pid-file=/usr/local/mysql-5.0.18/data/laptop.pid ##datadir=/usr/local/mysql-5.0.18/data ##user=mysql server-id=1 #skip-networking skip-name-resolve skip-locking set-variable = key_buffer=16M set-variable = max_allowed_packet=1M set-variable = table_cache=64 set-variable = sort_buffer=512K set-variable = net_buffer_length=8K set-variable = myisam_sort_buffer_size=8M # logging options log=5-0-18.log log-bin=laptop-bin log-error=5-0-18.error-log log-queries-not-using-indexes log-slow-admin-statements log-slow-queries=5-0-18.slow-log log-warnings # # end of mysqld server configuration file # /usr/local/mysql-version/my.cnf The /etc/my.cnf can be used to set parameters for all the mysql clients. # /etc/my.cnf # MySQL client program configuration file # last updated 2006-03-05 # mysqld server configuration file lives in #
Re: ~ How to install 3 instances of mysql~
On Sat, 15 Apr 2006, Mohammed Abdul Azeem wrote: To: [EMAIL PROTECTED] From: Mohammed Abdul Azeem [EMAIL PROTECTED] Subject: Re: ~ How to install 3 instances of mysql~ Hello Keith, Thank you very much for your guidence. This is my existing my.cnf file [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M thread_cache = 8 query_cache_size = 32M log_bin_trust_routine_creators = 1 # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 #specify the storage engine default-storage-engine = InnoDB #specify the table type default-table-type = InnoDB #enable the full query log log #Print warnings to error log file log_warnings #specify max connections max_connections = 30 #specify max user connections max_user_connections = 12 # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /mysql-system/mysql/data/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /mysql-system/mysql/data/ innodb_log_arch_dir = /mysql-system/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 5242880 innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 100 The variables key_buffer = 384M, innodb_buffer_pool_size = 384M, innodb_additional_mem_pool_size = 20M, innodb_log_buffer_size = 8M are set for 1 instance. My RAM memory is 1GB. Can i have the same values for the above variables set for all the 3 instances ? if yes, will that not exceed the available RAM memory. Please help me set the correct values for these variables for each instance. Well, if you are running multiple instances of mysqld concurrently, then I guess you will have to share the system resorces as well, such as memory and file handles. Note that if you are using qps to monitor processes, for each mysqld you have running, the memory usage will increase appropriately. I only use MyISAM tables the moment, so I cannot help you with your InnoDB table settings. Kind Regards Keith Roberts -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ~ How to install 3 instances of mysql~
Hi, Also a read of http://dev.mysql.com/doc/refman/5.0/en/multiple-servers.html and http://dev.mysql.com/doc/refman/5.0/en/mysqld-multi.html would probably be quite helpful. The second gives you a good idea on how to setup the my.cnf file for multiple servers. The concepts are the same for version 4.1 Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Saturday, 15 April 2006 10:45 PM To: mysql@lists.mysql.com Subject: Re: ~ How to install 3 instances of mysql~ On Sat, 15 Apr 2006, Mohammed Abdul Azeem wrote: To: [EMAIL PROTECTED] From: Mohammed Abdul Azeem [EMAIL PROTECTED] Subject: Re: ~ How to install 3 instances of mysql~ Hello Keith, Thank you very much for your guidence. This is my existing my.cnf file [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M thread_cache = 8 query_cache_size = 32M log_bin_trust_routine_creators = 1 # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 #specify the storage engine default-storage-engine = InnoDB #specify the table type default-table-type = InnoDB #enable the full query log log #Print warnings to error log file log_warnings #specify max connections max_connections = 30 #specify max user connections max_user_connections = 12 # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /mysql-system/mysql/data/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /mysql-system/mysql/data/ innodb_log_arch_dir = /mysql-system/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 5242880 innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 100 The variables key_buffer = 384M, innodb_buffer_pool_size = 384M, innodb_additional_mem_pool_size = 20M, innodb_log_buffer_size = 8M are set for 1 instance. My RAM memory is 1GB. Can i have the same values for the above variables set for all the 3 instances ? if yes, will that not exceed the available RAM memory. Please help me set the correct values for these variables for each instance. Well, if you are running multiple instances of mysqld concurrently, then I guess you will have to share the system resorces as well, such as memory and file handles. Note that if you are using qps to monitor processes, for each mysqld you have running, the memory usage will increase appropriately. I only use MyISAM tables the moment, so I cannot help you with your InnoDB table settings. Kind Regards Keith Roberts -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
