mysql的處理能力問題(2)

這兩個禮拜一直困擾我的一個問題是mysql的處理能力問題，不知道是有些配置沒有弄好，還是真的mysql在多cpu、大記憶體下不能充分利用資源，使得我想通過多個例項來充分利用系統資源。每天上班都是一堆爛事，週末抽空試了一下：

 

 

1   利用mysql_install_db生成資料庫（這裡也可以拷貝一份原來的資料庫）

[root@testdb2 mysql]# mysql_install_db --datadir=/var/lib/mysql2 --user=mysql

Installing MySQL system tables...

110903 10:34:51 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.

OK

Filling help tables...

 

2   編輯mysqld_multi配置檔案

可以用mysqld_multi –example來生成一個樣例

[root@testdb2 mysql2]# mysqld_multi --example

# This is an example of a my.cnf file for mysqld_multi.

# Usually this file is located in home dir ~/.my.cnf or /etc/my.cnf

#

# SOME IMPORTANT NOTES FOLLOW:

#

# 1.COMMON USER

#

#   Make sure that the MySQL user, who is stopping the mysqld services, has

#   the same password to all MySQL servers being accessed by mysqld_multi.

#   This user needs to have the 'Shutdown_priv' -privilege, but for security

#   reasons should have no other privileges. It is advised that you create a

#   common 'multi_admin' user for all MySQL servers being controlled by

#   mysqld_multi. Here is an example how to do it:

#

#   GRANT SHUTDOWN ON *.* TO multi_admin@localhost IDENTIFIED BY 'password'

#

#   You will need to apply the above to all MySQL servers that are being

#   controlled by mysqld_multi. 'multi_admin' will shutdown the servers

#   using 'mysqladmin' -binary, when 'mysqld_multi stop' is being called.

#

# 2.PID-FILE

#

#   If you are using mysqld_safe to start mysqld, make sure that every

#   MySQL server has a separate pid-file. In order to use mysqld_safe

#   via mysqld_multi, you need to use two options:

#

#   mysqld=/path/to/mysqld_safe

#   ledir=/path/to/mysqld-binary/

#

#   ledir (library executable directory), is an option that only mysqld_safe

#   accepts, so you will get an error if you try to pass it to mysqld directly.

#   For this reason you might want to use the above options within [mysqld#]

#   group directly.

#

# 3.DATA DIRECTORY

#

#   It is NOT advised to run many MySQL servers within the same data directory.

#   You can do so, but please make sure to understand and deal with the

#   underlying caveats. In short they are:

#   - Speed penalty

#   - Risk of table/data corruption

#   - Data synchronising problems between the running servers

#   - Heavily media (disk) bound

#   - Relies on the system (external) file locking

#   - Is not applicable with all table types. (Such as InnoDB)

#     Trying so will end up with undesirable results.

#

# 4.TCP/IP Port

#

#   Every server requires one and it must be unique.

#

# 5.[mysqld#] Groups

#

#   In the example below the first and the fifth mysqld group was

#   intentionally left out. You may have 'gaps' in the config file. This

#   gives you more flexibility.

#

# 6.MySQL Server User

#

#   You can pass the user=... option inside [mysqld#] groups. This

#   can be very handy in some cases, but then you need to run mysqld_multi

#   as UNIX root.

#

# 7.A Start-up Manage Script. for mysqld_multi

#

#   In the recent MySQL distributions you can find a file called

#   mysqld_multi.server.sh. It is a wrapper for mysqld_multi. This can

#   be used to start and stop multiple servers during boot and shutdown.

#

#   You can place the file in /etc/init.d/mysqld_multi.server.sh and

#   make the needed symbolic links to it from various run levels

#   (as per Linux/Unix standard). You may even replace the

#   /etc/init.d/mysql.server script. with it.

#

#   Before using, you must create a my.cnf file either in /etc/my.cnf

#   or /root/.my.cnf and add the [mysqld_multi] and [mysqld#] groups.

#

#   The script. can be found from support-files/mysqld_multi.server.sh

#   in MySQL distribution. (Verify the script. before using)

 

下面是我的：

 

[oracle@testdb2 ~]$ cat /etc/mysqld_multi.cnf

[mysqld_multi]

mysqld     = /usr/bin/mysqld_safe

mysqladmin = /usr/bin/mysqladmin

#user       = multi_admin

#password   = my_password

 

 

 

[client]

#password       = your_password

#port            = 3306

#socket          = /var/lib/mysql/mysql.sock

 

# Here follows entries for some specific programs

 

# The MySQL server

[mysqld1]

port            = 3306

socket          = /var/lib/mysql/mysql.sock

pid-file   = /var/lib/mysql/hostname.pid

datadir    = /var/lib/mysql

 

 

skip-locking

key_buffer_size = 256M

max_allowed_packet = 1M

table_open_cache = 512

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size = 32M

# Try number of CPU's*2 for thread_concurrency

thread_concurrency = 8

 

#log-bin=/var/lib/mysql/mysql-bin

 

server-id       = 1

 

wait_timeout=28800000

 

innodb_data_file_path = ibdata1:66M;ibdata2:2000M;ibdata3:10M:autoextend

innodb_log_file_size = 200M

innodb_log_files_in_group = 4

 

innodb_buffer_pool_size = 512M

innodb_additional_mem_pool_size = 120M

innodb_log_buffer_size = 16M

 

 

[mysqld2]

port            = 3307

socket          = /var/lib/mysql2/mysql2.sock

pid-file   = /var/lib/mysql2/hostname2.pid

datadir    = /var/lib/mysql2

skip-locking

key_buffer_size = 128M

max_allowed_packet = 1M

table_open_cache = 512

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size = 32M

# Try number of CPU's*2 for thread_concurrency

thread_concurrency = 8

 

log-bin=/var/lib/mysql2/mysql-bin

 

server-id       = 2

 

wait_timeout=28800000

 

innodb_log_file_size = 100M

innodb_log_files_in_group = 3

 

innodb_buffer_pool_size = 256M

innodb_additional_mem_pool_size = 120M

innodb_log_buffer_size = 16M

 

[mysqldump]

quick

max_allowed_packet = 16M

 

[mysql]

no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

#safe-updates

 

[myisamchk]

key_buffer_size = 256M

sort_buffer_size = 256M

read_buffer = 2M

write_buffer = 2M

 

[mysqlhotcopy]

interactive-timeout

 

3    用mysqld_multi來控制：

[root@testdb2 ~]# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf report

Reporting MySQL servers

MySQL server from group: mysqld1 is not running

MySQL server from group: mysqld2 is not running

[root@testdb2 ~]# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf start

[root@testdb2 ~]# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf report

Reporting MySQL servers

MySQL server from group: mysqld1 is running

MySQL server from group: mysqld2 is running

[root@testdb2 ~]# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf stop

 

start後面跟具體的數字，比如start  1，則單獨開啟mysqld1這個例項。

[root@testdb2 ~]# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf report

Reporting MySQL servers

MySQL server from group: mysqld1 is not running

MySQL server from group: mysqld2 is running

[root@testdb2 ~]#

[root@testdb2 ~]# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf start 1

[root@testdb2 ~]# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf report

Reporting MySQL servers

MySQL server from group: mysqld1 is running

MySQL server from group: mysqld2 is running

 

4    操作，通過mysql連線注意要制定埠和socket：

[root@testdb2 mysql]# mysql -u root   -P3307 -S/var/lib/mysql2/mysql2.sock

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.1.51-log MySQL Community Server (GPL)

 

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL v2 license

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql> \s

--------------

mysql  Ver 14.14 Distrib 5.1.51, for unknown-linux-gnu (x86_64) using readline 5.1

 

Connection id:          2

Current database:

Current user:           root@localhost

SSL:                    Not in use

Current pager:          stdout

Using outfile:          ''

Using delimiter:        ;

Server version:         5.1.51-log MySQL Community Server (GPL)

Protocol version:       10

Connection:             Localhost via UNIX socket

Server characterset:    latin1

Db     characterset:    latin1

Client characterset:    latin1

Conn.  characterset:    latin1

UNIX socket:            /var/lib/mysql2/mysql2.sock

Uptime:                 1 min 27 sec

 

Threads: 1  Questions: 5  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.57

--------------

 

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| test               |

+--------------------+

3 rows in set (0.00 sec)

 

5    作業系統可看到兩個mysqld程式：

root     17474     1  0 10:54 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe --port=3306 --socket=/var/lib/mysql/mysql.sock

root     17480     1  0 10:54 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe --port=3307 --socket=/var/lib/mysql2/mysql.sock

mysql    17952 17474  0 10:54 pts/0    00:00:00 /usr/sbin/mysqld --basedir=/

mysql    17957 17480  0 10:54 pts/0    00:00:00 /usr/sbin/mysqld --basedir=/