| Subcribe via RSS
mysql tuner-primer 是一个比较简单好用的检查mysql运行状态的shell脚本，它主要通过查询mysql中的variables设定和status 并结合当前系统情况给出mysql运行的状态和建议，主要包括以下内容的检查
slow query慢速查询
binlog 二进制日志
max connections 最大连接数
threads 线程数
key buffer size 关键字缓冲
query cache 查询缓存
sort operations 排序
join operations 多表联接
tmp tables 临时表
open files 打开文件数
table cache 表缓存
table locking 锁表
table scans 表扫描
innodb status
total memory used 内存使用
文件下载：tuning-primer
阅读内文 Tags: MySQL, performance tuning primethread_concurrency
数量设置为CPU核心数量的两倍.
thread_cache_size
按照内存大小来设置, 1G=8, 2G=16, 3G=32, >3G=64
wait_timeout
超时时间,如果连接数比较大,可以减少此参数的值,我使用的是10
max_connections
最大连接数,mysql实际允许连接数的值是max_connections+1,按照系统库不同而有不同性能.一般是500~1000,MySQL AB提供的linux静态库可以达到4000.
query_cache_size
查询缓冲,默认是0,所以必须打开以提高mysql性能,其本身需要40K来保存结构数据.所以不能设置的太小,初期可以设置成32M,然后根据实际运行情况另行调整
query_cache_type
指定查询缓冲的类型,0是关闭,1是缓冲除了使用SELECT SQL_NO_CACHE语句指明了不需要缓冲的数据意外的所有查询,2是只缓冲SELECT SQL_CACHE指定的查询.一般设置为1.
query_cache_limit
允许进入查询缓冲区的最小数据大小,默认值是1MB,可以修改的小一点以满足更多查询的需求.但是如果设置的过于小,则会导致很多新的小查询的结果将原有的查询结果交换出去.增加系统的颠簸.
相关命令
查询mysql服务器相关状态数据
>SHOW STATUS;
查询mysql服务器相关配置选项
>SHOW VARIABLES;
整理查询缓冲区里的碎片
>flush query cache;
删除查询缓冲区里的所有内容
>reset query cache;
设置mysql参数
>SET GLOBAL;
查询mysql当前执行的sql语句
>show processlist;
变量 含义
Qcache_queries_in_cache
在缓存中已注册的查询数目
Qcache_inserts
被加入到缓存中的查询数目
Qcache_hits
缓存采样数数目
Qcache_lowmem_prunes
因为缺少内存而被从缓存中删除的查询数目
Qcache_not_cached
没有被缓存的查询数目 (不能被缓存的，或由于 QUERY_CACHE_TYPE)
Qcache_free_memory
查询缓存的空闲内存总数
Qcache_free_blocks
查询缓存中的空闲内存块的数目
Qcache_total_blocks
查询缓存中的块的总数目
MySQL查询优化
>SHOW STATUS LIKE ‘Qcache%’;
查询出Cache状态
如果Qcache_lowmem_prunes非常大,说明因为内存不足而被交换出cache的数据很多.如果增加内存.可以保证较小的交换次数以及较高的命中率
例如现在我们查询的结果如下
设置为64M cache内存后
>set global query_cache_size=67108864;
自由内存块看起来变小了
是因为现在自由内存块.是一个整块.而以前的内存块都是分散的小块
而因为重建了cache区
Qcache_queries_in_cache变量变小了.因为此操作重新建立了cache内存区.所有数据重新缓存
在运行一两天后我们再看此数据.如果变大了.说明增大cache内存区域是有效的.如果和以前数据差不多
说明增加的内存并没有实际起到多大的作用.
有人会觉得如果我将cache内存设置的非常大
然后将cache_limit设置成0
那么所有查询都会被缓存了
理论上是这样.但是一台数据库服务器的查询非常多.
如果连查询单条数据都要缓存.
那么内存再大也会不够的.到时候老的内容就会被交换出去
当cache内存使用满的时候,就会不停的有新查询进来将老查询替换出去.
这样导致两个结果.一个是内存颠簸.效率反而下降.
第二个是cache内存的小碎块增多,内存利用率降低
如果是只有内容很少的小库,并且查询率不高.是可以使用这种方法提高响应速度
但是如果是实际生产环境,数据量会比较大.还是需要按照最佳比例来配置.
而不同的应用不同的数据量会有不同的搭配,这点大家不要看网上的优化配置随便的填写
还是要时时的查看mysql的状态进行调整.即便是这个月调整好的优化参数
到了下个月业务不同,数据量增加,也会需要调整的.
问题描述：
我们要访问的表是一个非常大的表，四千万条记录，id是主键，program_id上建了索引。
执行一条SQL:
这条SQL非常慢。
我们原以为处理记录太多的原因，所以加了id限制，一次只读五十万条记录
但是这条SQL仍然很慢，速度比上面一条几乎没有提升。
Mysql处理50万条记录的表，条件字段还建了索引，这条语句应该是瞬间完成的。
问题分析：
这张表大约容量30G，数据库服务器内存16G，无法一次载入。就是这个造成了问题。
这条SQL有两个条件，ID一到五十万和Program_id一到四千，因为program_id范围小得多，mysql选择它做为主要索引。
先通过索引文件找出了所有program_id在1到4000范围里所有的id，这个过程非常快。
接下来要通过这些id找出表里的记录，由于这些id是离散的，所以mysql对这个表的访问不是顺序读取。
而这个表又非常大，无法一次装入内存，所以每访问一条记录mysql都要重新在磁盘上定位并把附近的记录都载入内存，大量的IO操作导致了速度的下降。
问题解决方案：
1. 以program_id为条件对表进行分区
2. 分表处理，每张表的大小不超过内存的大小
然而，服务器用的是mysql5.0，不支持分区，而且这个表是公共表，无法在不影响其它项目的条件下修改表的结构。
所以我们采取了第三种办法：
现在program_id的范围远大于id的范围，id被当做主要索引进行查找，由于id是主键，所以查找的是连续50万条记录，速度和访问一个50万条记录的表基本一样
总结：
这是一个在千万笔记录表中由于使用了索引导致了数据查找变慢的问题，有一定的典型性和大家交流下！
前段时间和网友jabincn探讨关于mysql proxy部署了读写分离后，无法用ems等工具连接到slave db的问题；
测试的结果：
必须先键入指令，use dbname；后才能正常进行数据库操作；
发现问题：
发生的原因和rw-splitting.lua脚本中无法定义到slave DB库名称有关；
日志表现为：
server default db: cjhjd
client default db:
syncronizing
client default db未正确定义。
询问mysql官方得到解决方案，在rw-splitting.lua的脚本中增加代码如下：
修改完后，根据测试结果一切顺利。
感谢jabincn对以上问题的不断追踪和到最后圆满的解决，并提供该方案给我们，使我们共同学习和提高。
先使用一台作为读写,一台作为只读
现在使用一台作为读写.两台作为只读
总结
1.同一个IP不同的session的情况下,Proxy将算作一个client,也就是每个IP是一个CLIENT.而不是实际连接数.
2.控制触发读写分离的参数位于 /usr/local/share/mysql-proxy/rw-splitting.lua 脚本min_idle_connections.当客户端的连接数超过此值时,Proxy的读写分离开始执行.(max_idle_connections参数暂时实验出来没有特殊作用)
3.–proxy-read-only-backend-addresses可以增加多条.但是测试始终是连接到第一条增加的服务器上.不会产生自动分发请求的效果.
完整mysql-proxy 0.6.1 二进制包下载:（包含lua5.1、glib2.7.7、libevent1.4、check0.9.3）
请直接解压在根目录下：
tar zxvf mysqlproxy-0.6.1-fullbin-centos4-lua-x86-scripts-32bit.tar.gz -C /
其中/root/mysql-proxy 为启动脚本，请自行修改其中的IP地址。
以下内容是该二进制档的编译过程，供参考：
mysql-proxy install relation tar ball in the CentOS4.4 system:
glib >= 2.6, libevent, libcheck, lua
if the system already has those pkg, no need to install them any more, but on my?CentOS4/RHEL4 box I have to install them all.
1
glib >= 2.7.7
get it from http://www.gtk.org/ (Note: newer version need newer pkg-config 0.15)
here is a short instuction to install glib 2.7.7
./configure –prefix=/opt/glib27
make
make install
2
libevent 1.x or higher (1.4.1 or later is preferred)
you can get lib event from http://monkey.org/~provos/libevent/
./configure –prefix=/opt/libevent
make
make install
3 check
get the lib from http://check.sourceforge.net/
check 0.9.3 successed in CentOS 4.4
./configure –prefix=/opt/check
make
make install
4
lua 5.1.x or higher
“lua” can be found @ http://www.lua.org/
and the latest version of lua is Lua 5.1.3
here is a short instruction to install lua
a) update the Make file change line “INSTALL_TOP= /usr/local” to “INSTALL_TOP= /opt/lua”
b) then “make linux” (you can just type make to get a list of supported platform)
c) “make install”
5
mysql 5.1.23 client version is enough
get the?source ball?from http://www.mysql.com/
tar zxvf mysql-5.1.23-rc-linux-i686-glibc23.tar.gz
ln -s mysql-5.1.23-rc-linux-i686-glibc23 mysql
cd mysql
groupadd mysql
useradd -g mysql mysql -s /sbin/nologin
chown mysql:root . -R
chown mysql:mysql data -R
scripts/mysql_install_db –user=mysql
6
mysql-proxy 0.6.1 the latest version
export LUA_CFLAGS=”-I/usr/local/include”
export LUA_LIBS=”-L/usr/local/lib -llua -ldl”
export LDFLAGS=”-lm”
export GLIB_CFLAGS=”-I/opt/glib27/include/glib-2.0 -I/opt/glib27/lib/glib-2.0/include”
export GLIB_LIBS=”-L/opt/glib27/lib -lglib-2.0″
export CPPFLAGS=”$CPPFLAGS -I/opt/libevent/include”
export CFLAGS=”$CFLAGS -I/opt/libevent/include”
export LDFLAGS=”$LDFLAGS -L/opt/libevent/lib -lm”
./configure –prefix=/usr/local/mysql-proxy –with-mysql=/usr/local/mysql –with-check=/opt/check
7
running
cp /usr/local/mysql-proxy/sbin/mysql-proxy /usr/sbin/
help command:
mysql-proxy –help-all
configure run:
mysql-proxy \
–proxy-backend-addresses=192.168.3.91:3306 \
–proxy-backend-addresses=192.168.3.92:3306
Note that the default port number of the proxy server is 4040
so you can connect to the proxy server the port 4040
8
testing
mysql -uroot -h 192.168.3.99 -P 4040 -p123456
9
For example, mysql Write/Read splitting configure running:
LUA_PATH=”/usr/local/mysql-proxy/share/mysql-proxy/?.lua” \
/usr/sbin/mysql-proxy \
–proxy-address=127.0.0.1:3306 \
–proxy-backend-addresses=192.168.3.91:3306 \
–proxy-read-only-backend-addresses=192.168.3.92:3306 \
–proxy-lua-script=/usr/local/mysql-proxy/share/mysql-proxy/rw-splitting.lua > /var/log/mysql-proxy.log &
具体实施过程请参看blog内其他文档。
相关技术交流请发邮件或msn
阅读内文MySQL Proxy项目已经嵌入了LUA语言
当初希望得到的功能.
都已经实现了
包括
负载均衡
故障切换
SQL语句变形等等
有了SQL变形
则很多功能
例如数据库的垂直以及水平分割
都可以移出应用程序层
这样开发人员的负担就大大降低了.
环境:
CentOS 5.1
MySQL Proxy 0.6.1
只选择Development Tools其他组件未安装
路径:
lua执行程序: /usr/local/bin/lua
mysql-proxy执行程序: /usr/local/sbin/mysql-proxy
mysql-proxy配置文件: /usr/local/share/mysql-proxy
下面是MySQL Proxy的安装步骤
首先安装lua.由于MySQL Proxy 用到lua作为内嵌配置脚本语言
所以make的时候需要用到lua的文件
下载,解压并进入lua目录
安装glib包
#yum install glib2-devel
安装libevent
#yum install libevent-devel
安装check
#yum install check
安装libtool
#yum install libtool
安装SVN
#yum install subversion
安装mysql开发包
#yum install mysql-devel(如果是手动安装,则只要保证mysql.h能够被系统找到即可)
正式安装MySQL Proxy
从svn上获取最新代码
测试mysql proxy是否正常运行
#mysql-proxy -V
执行读写分离
如果 MySQL 正在运行，首先杀之： killall -TERM mysqld。
启动 MySQL ：bin/safe_mysqld –skip-grant-tables &
就可以不需要密码就进入 MySQL 了。
然后就是
>use mysql
>update user set password=password(”new_pass”) where user=”root”;
>flush privileges;
重新杀 MySQL ，用正常方法启动 MySQL 。
网上所有讲述关于MySQL Cluster的文章
要不就是跳过了某些步骤没说
要不就是搞得太复杂.把配置文件能写的都加上了
这对于按需自定义很不方便
甚至MySQL官网的HELP
对于某些内容的描述也是有些模糊和遗漏的
只能参考N多文档进行N多试验.
最后得出正确以及最简化的配置方法
然后大家可以按照最简化的配置.
自行加上所需的配置内容.
网络分布
MySQL Server1 : 192.168.210.131
MySQL Server2 : 192.168.210.132
NDB MGM : 192.168.210.133
NDB1 : 192.168.210.134
NDB2 : 192.168.210.135
NDB节点
先从MYSQL官网下载NDB Storage Engine
http://mirrors.24-7-solutions.net/pub/mysql/Downloads/MySQL-5.0/MySQL-ndb-storage-5.0.45-0.glibc23.i386.rpm
安装后配置my.cnf
NDB节点的配置文件只需要一句话用来指定MGM的地址即可
# vi /etc/my.cnf
[MYSQL_CLUSTER]
ndb-connectstring=192.168.210.133
初始化ndbd
# ndbd –initial
启动ndbd
# ndbd
MGM节点
安装PERL模块
# yum install perl
# wget http://dag.wieers.com/rpm/packages/perl-HTML-Template/perl-HTML-Template-2.9-1.el5.rf.noarch.rpm
# rpm -ivh perl-HTML-Template-2.9-1.el5.rf.noarch.rpm
下载MGM服务以及管理工具
http://mirrors.24-7-solutions.net/pub/mysql/Downloads/MySQL-5.0/MySQL-ndb-management-5.0.45-0.glibc23.i386.rpm
http://mirrors.24-7-solutions.net/pub/mysql/Downloads/MySQL-5.0/MySQL-ndb-tools-5.0.45-0.glibc23.i386.rpm
安装之后配置MGM配置文件
#vi /etc/ndb.ini
# Example Ndbcluster storage engine config file.
#
[ndbd default]
NoOfReplicas= 2
MaxNoOfConcurrentOperations= 10000
DataMemory= 80M
IndexMemory= 24M
TimeBetweenWatchDogCheck= 30000
DataDir= /var/lib/mysql-cluster
MaxNoOfOrderedIndexes= 512
[ndb_mgmd default]
DataDir= /var/lib/mysql-cluster
[ndb_mgmd]
Id=1
HostName=192.168.210.133
[ndbd]
Id= 2
HostName=192.168.210.134
[ndbd]
Id= 3
HostName=192.168.210.135
[mysqld]
Id= 4
hostname=192.168.210.131
[mysqld]
Id= 5
hostname=192.168.210.132
# choose an unused port number
# in this configuration 63132, 63133, and 63134
# will be used
[tcp default]
PortNumber= 63132
启动MGM
# ndb_mgmd -f /etc/ndb.ini
MySQL节点
安装MySQL服务端,Redhat自带的5.0.22版本似乎不支持mysql cluster
http://mirrors.24-7-solutions.net/pub/mysql/Downloads/MySQL-5.0/MySQL-devel-5.0.45-0.glibc23.i386.rpm
http://mirrors.24-7-solutions.net/pub/mysql/Downloads/MySQL-5.0/MySQL-server-5.0.45-0.glibc23.i386.rpm
安装MySQL客户端
http://mirrors.24-7-solutions.net/pub/mysql/Downloads/MySQL-5.0/MySQL-shared-5.0.45-0.glibc23.i386.rpm
http://mirrors.24-7-solutions.net/pub/mysql/Downloads/MySQL-5.0/MySQL-client-5.0.45-0.glibc23.i386.rpm
MySQL配置文件
# vi /etc/my.cnf
[MYSQLD]
ndbcluster
ndb-connectstring=192.168.210.133
#将默认存储方式改成NDB,对于生产环境下的MySQL Cluster会方便很多.
default-storage-engine=ndbcluster
启动MySQL
# /etc/rc.d/init.d/mysql restart
然后我们回到MGM节点服务器192.168.210.133
查看节点情况
# ndb_mgm
> show
ndb_mgm> show
Cluster Configuration
———————
[ndbd(NDB)] 2 node(s)
id=2 @192.168.210.134 (Version: 5.0.45, Nodegroup: 0)
id=3 @192.168.210.135 (Version: 5.0.45, Nodegroup: 0, Master)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.210.133 (Version: 5.0.45)
[mysqld(API)] 4 node(s)
id=4 @192.168.210.131 (Version: 5.0.45)
id=5 @192.168.210.132 (Version: 5.0.45)
所有节点都已正常启动
安装MySQL Cluster有几点要注意
1.不能使用RedHat或者CentOS自带的mysql服务.此服务甚至不支持NDB的参数
2.似乎NDB只有二进制代码下载.至今没有找到源代码包下载
基本就是在上篇
MySQL Cluster安装配置
基础上加入LVS构成集群
经过测试.LVS能够正常将数据库申请分发到服务器上
MySQL也能够正常处理申请
select,insert以及update都可以正常执行
在互斥条件下也能够正确报错
由于LVS的搭建和MySQL Cluster的搭建都已经有文档了
就不在此将步骤重述了.
这篇主要后期要更新的是性能压力测试的结果
由于现在跑在xen上.
最终测试的性能可以预期比单机mysql还会低
所以暂时没有做压力测试
结构
前段LVS一台
两台MySQL服务器
一台MGM中心管理节点
两台NDB存储节点
传统的read/write分离模式
只能解决读缓存问题.
而面对用户可能有的写操作
便无法进行加速.
在MYSQL CLUSTER释出之后
具体数据层的读写可以独立出来成为cluster.
这样我们可以在mysql服务器前段加上LVS
对读/写操作进行分发.
由于数据冲突等将有CLUSTER的MGM进行管理
所以无需担心两次有着相同的写操作被分发到不用MYSQL服务器上的情况.
目前没有测试过具体MGM管理节点的负载
不知道是否MGM节点会成为新的瓶颈.
ORACLE达成Select TOP 效果
Seletct * from temp where ROWNUM > 10 Order By ROWNUM
修改序列的增加幅度
ALTER SEQUENCE [user.]sequence_name
[INCREMENT BY n] (n可以为负数)
SQLLDR用法
–使用分隔符和引用符来分割各个字段,此用法比较适合包含多字段数据的CSV文件
Load data
infile ‘data.txt’
badfile ‘mytemp.bad’
Append into Table mytemp
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
(字段1,字段2,字段3…)
字符串转换成日期
to_date(selltime,’yyyy-mm-dd’)