MySQL实践积累

Author Archive

Find out the difference between Com_Select and InnoDB_rows_read

2012年05月16日, 7:57 下午

This morning one MySQL cluster ( 4 Master ,4 Slave) , one mysql server ‘s response time raises up.

The raid config of the server

Adapter 0 -- Virtual Drive Information:
Virtual Drive: 0 (Target Id: 0)
Name                :
RAID Level          : Primary-1, Secondary-0, RAID Level Qualifier-0
Size                : 99.999 GB
State               : Optimal
Strip Size          : 64 KB
Number Of Drives per span:2
Span Depth          : 6
Default Cache Policy: WriteBack, ReadAheadNone, Direct, Write Cache OK if Bad BBU
Current Cache Policy: WriteBack, ReadAheadNone, Direct, Write Cache OK if Bad BBU
Access Policy       : Read/Write
Disk Cache Policy   : Disabled
Encryption Type     : None

Virtual Drive: 1 (Target Id: 1)
Name                :
RAID Level          : Primary-1, Secondary-0, RAID Level Qualifier-0
Size                : 1.533 TB
State               : Optimal
Strip Size          : 64 KB
Number Of Drives per span:2
Span Depth          : 6
Default Cache Policy: WriteBack, ReadAheadNone, Direct, Write Cache OK if Bad BBU
Current Cache Policy: WriteBack, ReadAheadNone, Direct, Write Cache OK if Bad BBU
Access Policy       : Read/Write
Disk Cache Policy   : Disabled
Encryption Type     : None

The pressure tends of this server

The first trend is MySQL Com_select , the second trend picture is MySQL InnoDB_rows_read. You can find out that the InnoDB_rows_read is larger than the counter of Com_Select.

At the beginng , it makes me confused that why the result of innodb_rows_read is bigger than com_select.

And what’s more , iostat tells me that disk is very busy and the avg response time is over 5ms. Yes, Response time is not ideal.

Finally , i observe the running sql in MySQL.

I found that a large number of this sql is running

SELECT id, user_id, subscribe_id, twoway, group_ids, gmt_create, gmt_modified
FROM table_0485
WHERE user_id =376297445
ORDER BY gmt_create DESC
LIMIT 0 , 2000

What’s the problem of this sql?

1. It is query 2000 rows from InnoDB, if we have 100 sql like this is running, the innodb_rows_read = 100*2000;

2. the column user_id is not necessary to select once again, because we have post the user_id . We can redure the traffic after remove user_id,just like this “SELECT id, subscribe_id, twoway, group_ids, gmt_create, gmt_modified ”

So , return to our topic “Find out the difference between Com_Select and InnoDB_rows_read”

Right, if we have some query that need over thounds row, although the com_select is little, but the InnoDB_rows_read may is large.

标签：MySQL InnoDB
Category: MySQL | 评论

[InnoDB]Truncate table difference between InnoDB build-in and InnoDB Plugin-in

2012年05月16日, 1:26 上午

MySQL(5.1.37) InnoDB Build-in

root@(none) 04:55:17> SHOW PLUGINS;
+------------+--------+----------------+---------+---------+
| Name       | Status | Type           | Library | License |
+------------+--------+----------------+---------+---------+
| binlog     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| partition  | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| CSV        | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| MEMORY     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| InnoDB     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| MyISAM     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
+------------+--------+----------------+---------+---------+
7 rows in set (0.00 sec)

How Can we certify we using InnoDB Plugin, you just need run “show plugins” ,if you see the result like above, it is.

When we truncate one table .

[MM-Writable@mysqldb /u01/mysql/data/db_02]
$du -sh db.ibd
773M    db.ibd

root@db_02 05:08:55&gt;select count(*) from db;
+----------+
| count(*) |
+----------+
|  1410879 |
+----------+
1 row in set (0.38 sec) 

root@db_02 05:08:59&gt;truncate table db;
Query OK, 0 rows affected (0.08 sec) 

root@db_02 05:09:05&gt;select count(*) from db;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

After we truncate the table “db”, we found that the total number of the table is zero. But the file is still use the space

$du -sh * | grep db

12K     db.frm
773M    db.ibd

Finally , how can we withdraw the space??? It is easily we just run “ alter table db engine=innodb;”;

root@snsfa_02 05:13:10&gt;alter table sns_universal_relations_0064 engine=innodb;
Query OK, 0 rows affected (0.92 sec)
Records: 0  Duplicates: 0  Warnings: 0 

root@snsfa_02 05:13:17&gt;exit
Bye 

[MM-Writable@my130.cm4 /u01/mysql/data/snsfa_02]
$du -sh * | grep sns_universal_relations_0064
12K     sns_universal_relations_0064.frm
132K    sns_universal_relations_0064.ibd

InnoDB Plugin-in

root@(none) 04:55:17&gt; SHOW PLUGINS; 

| InnoDB                         | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |
| INNODB_RSEG                    | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_TRX                     | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_LOCKS                   | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_LOCK_WAITS              | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_CMP                     | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_CMP_RESET               | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_CMPMEM                  | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_CMPMEM_RESET            | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_SYS_TABLES              | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_SYS_TABLESTATS          | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_SYS_INDEXES             | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_SYS_COLUMNS             | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_SYS_FIELDS              | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_SYS_FOREIGN             | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_SYS_FOREIGN_COLS        | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_SYS_STATS               | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_TABLE_STATS             | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_INDEX_STATS             | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_BUFFER_POOL_PAGES       | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_BUFFER_POOL_PAGES_INDEX | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_BUFFER_POOL_PAGES_BLOB  | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| XTRADB_ADMIN_COMMAND           | ACTIVE | INFORMATION SCHEMA | NULL             | GPL

When you are using InnoDB Plugin, it is actually Effective for your work.

When you truncate table, InnoDB Plugin would help you immediately empty the space.!

Reference http://dev.mysql.com/doc/refman/5.1/en/replacing-builtin-innodb.html

标签：InnoDB-Plugin
Category: MySQL | 评论

shell脚本批量操作多个表

2012年05月14日, 6:02 上午

分布式情况下很多分表，比如user_0000到 user_0511 。需要一个简便的批量操作方法。

例如 truncate table . alter table engine=innodb; drop tale等

脚本非常简单

list=`echo "show tables like ‘%user%’ " | mysql -uroot test -N `
for i in $list;do
echo "alter table test.$i engine=innodb;" | mysql -uroot test
done

标签：shell
Category: MySQL | 评论

遭遇MySQL server has gone away

2012年02月7日, 5:40 下午

一个开发MM的项目(PHP+MySQL)的PHP项目今天出现了MySQL server has gone away的错误，MySQL服务器端表示毫无压力。

而通过mysql客户端的连接方式mysql -hxx -uxx -pxx databasename -e xxx则毫无问题

而App服务器的netstat -a | grep 3306 | grep TIME_WAIT个数非常小，只有2个。

MySQL在连接超时这块的参数如下

root@(none) 09:24:51>show variables like '%out%';

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

| Variable_name                | Value    |

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

| connect_timeout              | 10       |

| delayed_insert_timeout       | 300      |

| handlersocket_timeout        | 300      |

| handlersocket_wrlock_timeout | 12       |

| innodb_lock_wait_timeout     | 100      |

| innodb_rollback_on_timeout   | OFF      |

| interactive_timeout          | 28800    |

| lock_wait_timeout            | 31536000 |

| log_output                   | FILE     |

| net_read_timeout             | 30       |

| net_write_timeout            | 60       |

| slave_net_timeout            | 3600     |

| wait_timeout                 | 28800    |

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

最终MM把PHP连接MySQL的连接方式从长连接改成了短连接，就再没出现了。

后来查了下资料，总结如下

1.MySQL服务器的wait_timeout参数为默认的28800秒，即8小时。该参数的意思是MySQL等待睡眠的连接的持续时间，如果超过8小时不活动，MySQL就会关闭这个连接。这个参数可以动态修改，但这个参数是session级别的，需要应用重新建立连接获取这个变量值才会生效。

2.PHP的默认php.ini里面，default_socket_timeout参数是默认60s.default_socket_timeout参数的意思是socket超时为60s。官方解释

3.在MySQL的官方文档里面，有专门的章节介绍MySQL Server has gone away。但并没有提及到客户端本身也会有超时机制，以前曾经试过把wait_timeout设置为1年，但App还是继续出现MySQL server has gone away的错误。

4.在PHP程序里面，如果php.ini修改起来不方便，可以以下代码来尝试解决。

ini_set('mysql.connect_timeout', 300);

ini_set('default_socket_timeout', 300);

在ini_set后，可以用ini_get来验证参数设置适合符合预期。

参考资料

1. MySQL server has gone away – in exactly 60 seconds

2.MySQL server has gone away

标签：mysql, php
Category: MySQL | 一条评论

MySQL group by with rollup的用法

2012年01月31日, 11:48 下午

有一位同学通过askdba来询问with rollup的用法

(2012-02-01 15:08:22):

 mysql中有这种用法select ... from table_name  group by a with rollup

"with rollup"是什么意思呢？

GROUP BY Modifiers 官方手册里面对这个rollup有一个专门的页面介绍地址在这里，说得非常详细，我这里做一个简单的例子重现

建一个简单的表并插入几条简单的数据

CREATE TABLE `t` (

  `id` int(11) DEFAULT NULL,

  `id2` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=gbk

insert into t valeu(11,11),(12,12),(13,13);

先来做一个查询

root@test 03:44:32>select id,sum(id2),avg(id2) from t group by id with rollup;

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

| id   | sum(id2) | avg(id2) |

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

|   11 |       11 |  11.0000 |

|   12 |       12 |  12.0000 |

|   13 |       13 |  13.0000 |

| NULL |       36 |  12.0000 |

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

4 rows in set (0.00 sec)

我们可以看到，对于group by的列，with rollup将不会做任何的操作，而是返回一个NULL，而没有group by的列，则根据前面的avg函数和sum函数做了处理。

再来看另外一个语句，只对一个列做avg

root@test 03:44:36>select id,avg(id2) from t group by id with rollup;

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

| id   | avg(id2) |

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

|   11 |  11.0000 |

|   12 |  12.0000 |

|   13 |  13.0000 |

| NULL |  12.0000 |

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

4 rows in set (0.00 sec)

以前从没留意到有这种用法，这次长见识了。

标签：SQL
Category: MySQL | 一条评论

redhat5 和redhat6 root用户不同的ulimits

2012年01月31日, 1:27 上午

以前一直都是用redhat5，redhat6也处于测试阶段，当然也遇到了很多奇奇怪怪的问题，比如之前写的一篇博客,当时候是用root启动了mysqld_unsafe，在mysql的QPS到1W以上后，会出现ERROR 1135 (HY000): Can’t create a new thread (errno 11); 当时候的解决办法是用mysql用户来启动就解决了问题。但因为在系统重启后，如果用sudo -u mysql来启动的话，脚本会被卡主。

这个问题今天得到了一个稍微深入一点的结论。

首先我们看看这个错误

ERROR 1135 (HY000): Can’t create a new thread (errno 11);

if you are not out of available memory,

you can consult the manual for a possible OS-dependent bug

google类似Can’t create a new thread的错误后，得到的结论是文件描述符不够用，检查了vim /etc/security/limits.conf 的设置，是正常的

vim /etc/security/limits.conf

得到的结果是

root    soft    nofile  65535

root    hard    nofile  65535

admin   soft    nofile  65535

admin   hard    nofile  65535

# End of file

mysql   soft    nproc   65536

mysql   hard    nproc   65536

mysql   soft    nofile  65535

mysql   hard    nofile  65535

但观察了sudo -u root bash -c " ulimit -a " 后，得到 max user processes (-u) 1024

core file size          (blocks, -c) 0


data seg size           (kbytes, -d) unlimited


scheduling priority             (-e) 0


file size               (blocks, -f) unlimited


pending signals                 (-i) 385957


max locked memory       (kbytes, -l) 64


max memory size         (kbytes, -m) unlimited


open files                      (-n) 65535


pipe size            (512 bytes, -p) 8


POSIX message queues     (bytes, -q) 819200


real-time priority              (-r) 0


stack size              (kbytes, -s) 10240


cpu time               (seconds, -t) unlimited


max user processes              (-u) 1024


virtual memory          (kbytes, -v) unlimited


file locks                      (-x) unlimited

max user processes (-u) 1024 和 sudo -u root bash -c " ulimit -u " 一样，都是得到1024的结果

sudo -u root bash -c " ulimit -u "

1024

而在redhat5里面，只要在/etc/security/limits.conf 设置了root soft nofile 65535 和root hard nofile 65535，对应的uilmit -u 就会是65535.

和@维西v @tb天羽搞了几个小时，依然没法成功修改root用户的 max user processes到65535 。后来发现了一篇文章 Know your limits (ulimits) ，提及到redhat6新增了/etc/security/limits.d/90-nproc.conf，里面的内容是

# Default limit for number of user's processes to prevent


# accidental fork bombs.


# See rhbz #432903 for reasoning.


*          soft    nproc     1024

redhat6下面，root用户使用ulimit -u没法修改

* soft nproc 1024的意思是任何用户的最大max user processes为1024个，其他用户可以通过ulimit -u来修改，但root用户则修改不成功，我们这里看一个例子

[yingyuan.ydh@my031226 ~]$ cat /etc/security/limits.d/90-nproc.conf


# Default limit for number of user's processes to prevent


# accidental fork bombs.


# See rhbz #432903 for reasoning.



*          soft    nproc     1024


[yingyuan.ydh@my031226 ~]$ ulimit -u


1024


[yingyuan.ydh@my031226 ~]$ ulimit -u 65535


[yingyuan.ydh@my031226 ~]$ ulimit -u


65535


[yingyuan.ydh@my031226 ~]$ sudo -uroot bash -c " ulimit -u 65535"


[yingyuan.ydh@my031226 ~]$ sudo -uroot bash -c " ulimit -u "


1024

很明显，在redhat6的/etc/security/limits.d/90-nproc.conf限制下，个人用户可以修改ulimit-u,但root用户没法修改。 解下来，我们把etc/security/limits.d/90-nproc.conf改掉，会看到root的ulimit -u 可以修改成功。

[yingyuan.ydh@my031226 ~]$ sudo -uroot bash -c " ulimit -u 65535"


[yingyuan.ydh@my031226 ~]$ sudo -uroot bash -c " ulimit -u "


65535


[yingyuan.ydh@my031226 ~]$ cat /etc/security/limits.d/90-nproc.conf


# Default limit for number of user's processes to prevent


# accidental fork bombs.


# See rhbz #432903 for reasoning.



*          soft    nproc     65535

结果

在成功修改了root用户的max user processes后，继续使用root用户启动mysqld_safe脚本，稳定运行了一个下午，一切正常。

思考

为什么redhat6要做新增一个文件的限制，而不是继续沿用redhat5的方式来管理？在微博上面发了一条简单的描述，引起很多人的讨论。

http://weibo.com/1642466057/y3jM4cz3q

标签：Linux, redhat6
Category: Linux | 评论

遭遇Linux进程状态D

2012年01月19日, 6:37 上午

在一台flashcache的机器上面跑stap脚本

global some_count
probe process(@1).function("*")
{
some_count[tid()] = backtrace()
}

function print_top()
{
foreach (tid+ in some_count)
{
print_stack(some_count[tid])
}
}

probe timer.s(5)
{
print_top()
printf("—————————————————-\n")
}

跑了这个脚本，跑了一会就ctrl+c abort掉，但后台还是有一个D进程的程序，用了好几次kill -9也杀不掉

1 15466 15252 14863 ? -1 D 0 0:00 /usr/libexec/systemtap/stapio -u /tmp/stapzHoiGc/stap_b2dc831605d82ca90db5b550e7dfd16a_24607.ko

在Linux里面，进程状态分为task_running,task_interruptiable,task_uninterruptiable，_task_traced _task_stopped

之前一直对task_uninterruptiable不是很理解，这次亲身经历后，对它的认识更加深入。

进程状态为D的进程，一直滞留在CPU run_queue里面，搞得我的其他进程都不能正常运行，尝试了kill –9 ，没办法杀掉。最后只能reboot解决

之所以命名为D，往往是因为I/O资源得不到满足而引发等待。

我们的备库依赖nas服务器来作为备份盘，今年遇到过好几次因为nas的问题，导致mysql的监控一直告警(监控程序需要连接到MySQL,但由于备份脚本因为在写nas的时候，nas在中途卸载掉了，导致脚本一直在等待nas就绪,必须得重新挂载nas才能解决)

标签：Linux
Category: Linux | 2 条评论

CPU软中断实践一

2012年01月17日, 10:03 上午

最近在对一个MySQL项目进行性能测试 QPS在1.1W到1.5W之间波动，但通过tcprstat观察到，响应时间不是非常稳定，会从0.3ms波动到1.9ms

响应时间监控，avg代表的是平均响应时间，单位为微秒，这里可以看到，平均响应时间为0.3ms 到1.7ms之间波动

@淘宝褚霸在帮忙分析的时候，给了三点建议 1.CPU 2.IO 3.内存这里先从CPU使用优化来总结一下

1.首先定位系统的CPU占用是否正常，可以使用命令 mpstat –P ALL 1

我们可以看到第四颗CPU的idle百分比明显比其他CPU要低好多。那这颗CPU到底在忙什么事情？ perf top 工具可以帮我们查看，这颗CPU上面跑的进程的百分比

2. sudo perf top –cpu=4

输出的结果和oprofile相似，但perf top可以实时来做cpu采样，这点比oprofile要好使得多。

另外一个工具是 taskset ,例如 taskset -p 03 700 的意思是把pid为700的进程绑定到第四颗CPU上面

Category: Linux | 一条评论

ERROR 1135 (HY000): Can’t create a new thread (errno 11)

2012年01月9日, 5:33 上午

在一台MySQL测试服务器上面，今天遇到了

ERROR 1135 (HY000): Can’t create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

首先使用perror工具看一下错误代码11代表的意思

perror 11
OS error code 11: Resource temporarily unavailable
资源暂时不可用
google搜索了一下，很多文章说和当前用户的文件描述符ulimit -n有关系
ulimit -n
65535
当时候mysql的服务器load 为0，自然不可能是文件描述符不够导致的问题
第二个检查操作 cat /etc/security/limits.conf
得到的结果是
root soft nofile 65535
root hard nofile 65535
admin soft nofile 65535
admin hard nofile 65535
# End of file
mysql soft nproc 65536
mysql hard nproc 65536
mysql soft nofile 65535
第三个检查操作查看mysql的错误日志
有一个很奇怪的现象，我的mysql是一直跑着的，但错误日志只有前两天的

这个时候，在ps aux | grep mysql的输出中发现 -log-error=/var/log/mysqld.log ，错误日志输出到/var目录下面是不正常的
mysql 2086 35.1 37.1 51184004 18388068 ? Sl 20:25 9:01
/u01/mysql/bin/mysqld –basedir=/u01/mysql –datadir=/u01/mysql/data –plugin-dir=/u01/mysql/lib/plugin
–user=mysql –log-error=/var/log/mysqld.log –open-files-limit=65535 –pid-file=/u01/mysql/run/mysqld.pid
–socket=/u01/mysql/run/mysql.sock –port=3306

直觉告诉我mysql用了/etc/my.cnf的内容
/etc/my.cnf的内容
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

最终把mysqld_safe干掉后，用mysqld_safe –defaults-file=xx.cnf 重新指定my.cnf的位置，mysql就正常跑了
最终原因，@维西v 启动mysql的时候没有指定my.cnf的位置，所以导致mysqld_safe使用了/etc/my.cnf这个默认文件
但依然有问题不是很理解
1.为什么读了/etc/my.cnf的时候，mysqld还会继续使用my.cnf配置文件里面的/u01/mysql/data
2.为什么还会继续使用plugin-dir=/u01/mysql/lib/plugin

Category: Linux | 一条评论

编译Percona MySQL 5.5 +Handler Socket

2011年11月2日, 6:32 上午

安装准备 Cmake wget http://service-spi.web.cern.ch/service-spi/external/tarFiles/cmake-2.6.4.tar.gz

Percona-Server-5.5.16-rel22.0 get http://www.percona.com/redir/downloads/Percona-Server-5.5/Percona-Server-5.5.16-22.0/source/Percona-Server-5.5.16-rel22.0.tar.gz

$tar zxf cmake-2.6.4.tar.gz
$cd cmake-2.6.4
$./bootstrap
$make
$sudo make install

tar zxvf Percona-Server-5.5.15-rel22.0.tar.gz

cd Percona-Server-5.5.15-rel22.0

执行 CFLAGS=”-O3″ CXX=gcc CXXFLAGS=”-O3 -felide-constructors -fno-exceptions -fno-rtti”

cmake . \
  -DCMAKE_BUILD_TYPE:STRING=Release             \
  -DSYSCONFDIR:PATH=/u01/mysql            \
  -DCMAKE_INSTALL_PREFIX:PATH=/u01/mysql  \
  -DENABLED_PROFILING:BOOL=ON                   \
  -DENABLE_DEBUG_SYNC:BOOL=OFF                  \
  -DMYSQL_DATADIR:PATH=/u01/mysql/data    \
  -DMYSQL_MAINTAINER_MODE:BOOL=OFF              \
  -DWITH_EXTRA_CHARSETS:STRING=utf8,gbk,gb2312  \
  -DWITH_BIG_TABLES:BOOL=ON \
  -DWITH_FAST_MUTEXES:BOOL=ON \
  -DENABLE-PROFILING:BOOL=ON \
  -DWITH_SSL:STRING=bundled                     \
  -DWITH_UNIT_TESTS:BOOL=OFF                    \
  -DWITH_ZLIB:STRING=bundled                    \
  -DWITH_PARTITION_STORAGE_ENGINE:BOOL=ON       \
  -DWITH_SERVER_SUFFIX=hxsw                        \
  -DWITH_PLUGINS=heap,csv,partition,innodb_plugin,myisam \
  -DDEFAULT_CHARSET=gbk -DDEFAULT_COLLATION=gbk_chinese_ci -DWITH_EXTRA_CHARSETS=ALL \
  -DENABLED_ASSEMBLER:BOOL=ON                   \
  -DENABLED_LOCAL_INFILE:BOOL=ON                \
  -DENABLED_THREAD_SAFE_CLIENT:BOOL=ON          \
  -DENABLED_EMBEDDED_SERVER:BOOL=OFF             \
  -DWITH_CLIENT_LDFLAGS:STRING=all-static                 \
  -DINSTALL_LAYOUT:STRING=STANDALONE            \
  -DCOMMUNITY_BUILD:BOOL=ON;

 make -j `cat /proc/cpuinfo  | grep processor | wc -l`

make install

执行/u01/mysql/scripts/mysql_install_db –basedir=/u01/mysql/

(安装mysql,test数据库，初始化权限)

启动MySQL /u01/mysql/bin/mysqld_safe –defaults-file=my.cnf &

(my.cnf 请参考/u01/pmysql/support-files 下面的 my-innodb-heavy-4G.cnf文件）

启动MySQL后，登录后

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.16-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

恭喜你，已经安装成功了！

编译Handler Socket

cd Percona-Server-5.5.16-rel22.0/storage/HandlerSocket-Plugin-for-MySQL

./configure –with-mysql-source=/home/xxx/Percona-Server-5.5.15-rel21.0 \

–with-mysql-bindir=/u01/mysql/bin/ –with-mysql-plugindir=/u01/mysql/lib/plugin

make -j `cat /proc/cpuinfo | grep processor | wc -l`

make install

登录到MySQL

mysql> INSTALL PLUGIN handlersocket SONAME ‘handlersocket.so’;

mysql> show plugins;

+--------------------------------+----------+--------------------+------------------+---------+
| Name                           | Status   | Type               | Library          | License |
+--------------------------------+----------+--------------------+------------------+---------+
| binlog                         | ACTIVE   | STORAGE ENGINE     | NULL             | GPL     |
| mysql_native_password          | ACTIVE   | AUTHENTICATION     | NULL             | GPL     |
| mysql_old_password             | ACTIVE   | AUTHENTICATION     | NULL             | GPL     |
| MRG_MYISAM                     | ACTIVE   | STORAGE ENGINE     | NULL             | GPL     |
| MEMORY                         | ACTIVE   | STORAGE ENGINE     | NULL             | GPL     |
| MyISAM                         | ACTIVE   | STORAGE ENGINE     | NULL             | GPL     |
| CSV                            | ACTIVE   | STORAGE ENGINE     | NULL             | GPL     |
| BLACKHOLE                      | ACTIVE   | STORAGE ENGINE     | NULL             | GPL     |
| InnoDB                         | ACTIVE   | STORAGE ENGINE     | NULL             | GPL     |
| INNODB_RSEG                    | ACTIVE   | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_TRX                     | ACTIVE   | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_LOCKS                   | ACTIVE   | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_LOCK_WAITS              | ACTIVE   | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_CMP                     | ACTIVE   | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_CMP_RESET               | ACTIVE   | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_CMPMEM                  | ACTIVE   | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_CMPMEM_RESET            | ACTIVE   | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_SYS_TABLES              | ACTIVE   | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_SYS_TABLESTATS          | ACTIVE   | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_SYS_INDEXES             | ACTIVE   | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_SYS_COLUMNS             | ACTIVE   | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_SYS_FIELDS              | ACTIVE   | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_SYS_FOREIGN             | ACTIVE   | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_SYS_FOREIGN_COLS        | ACTIVE   | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_SYS_STATS               | ACTIVE   | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_TABLE_STATS             | ACTIVE   | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_INDEX_STATS             | ACTIVE   | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_BUFFER_POOL_PAGES       | ACTIVE   | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_BUFFER_POOL_PAGES_INDEX | ACTIVE   | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_BUFFER_POOL_PAGES_BLOB  | ACTIVE   | INFORMATION SCHEMA | NULL             | GPL     |
| XTRADB_ADMIN_COMMAND           | ACTIVE   | INFORMATION SCHEMA | NULL             | GPL     |
| FEDERATED                      | DISABLED | STORAGE ENGINE     | NULL             | GPL     |
| ARCHIVE                        | ACTIVE   | STORAGE ENGINE     | NULL             | GPL     |
| PERFORMANCE_SCHEMA             | ACTIVE   | STORAGE ENGINE     | NULL             | GPL     |
| partition                      | ACTIVE   | STORAGE ENGINE     | NULL             | GPL     |
| handlersocket                  | ACTIVE   | DAEMON             | handlersocket.so | BSD     |
+--------------------------------+----------+--------------------+------------------+---------+
36 rows in set (0.00 sec)

参考资料 Cmake使用 http://forge.mysql.com/wiki/Autotools_to_CMake_Transition_Guide

Category: Linux | 评论