存档

‘MySQL’ 分类的存档

MySQL查询大写小敏感问题

2014年11月24日 没有评论

如果你的MySQL表字符集刚好被设置为xxx_ci,比如utf8_generic_ci,如果刚好你做了一个如下查询,如果刚好你的数据库里面刚好有以下这两条记录,如果你足够细心,那么恭喜你应该发现出乎意料的结果。

table user
id       name
1         hello
2        HELLO

SQL
select * from user where name=”hello”

结果集

id       name
1         hello
2        HELLO

难道不应该是?

id       name
1         hello

原来MySQL某些字符集下面默认是不区分查询大小写的,这主要是由mysql的字符校验规则的设置决定的。

简单介绍一下字符校验规则吧

字符集是一套符号和编码,校对规则是在字符集内用于比较字符的一套规则。任何一个给定的字符集至少有一个校对规则,它可能有几个校对规则。

校对规则一般有这些特征:
两个不同的字符集不能有相同的校对规则。
每个字符集有一个默认校对规则。例如,utf8默认校对规则是utf8_general_ci。
存在校对规则命名约定:它们以其相关的字符集名开始,通常包括一个语言名,并且以_ci(大小写不敏感)、_cs(大小写敏感)或_bin(二元)结束。

所以对于一些比较敏感的数据,应该十分注意字符集。

通常对结尾为『_cs』的字符集为『大小写敏感』型,或者使用『_bin』后续字符集,如utf8_bin.

那么对于已经存在的历史数据应该如何处理呢?

其实针对已经设置为xxx_ci字符集,同时又不得不处理大小写敏感问题的数据我们除了修改字符集以外还可以修改查询语句。

1.在SQL语句中使用collate::select * from user where name collate utf8_bin = ‘hello’;

2.在SQL语句中使用binary操作符:select * from user where binary name = ‘hello’;

分类: MySQL 标签:

MySQL同步参数Seconds_Behind_Master=0 并不能确定主从是否延时

2014年3月5日 没有评论

最近上了几台从库之后经常出现疑似主从同步延时问题,但经过运维排查后给出结论是主从同步正常,不存在比较严重的延时问题,依据是反复实验参数Seconds_Behind_Master=0,但问题真的真的是存在。

事实上问题已经很明确,可能存在的问题就那么几点,主从延时可能性确实很大,后来查了一下资料才恍然,原来 Seconds_Behind_Master参数并不能说明主从同步是否延时问题

官方解释是:

  • When the slave is actively processing updates, this field shows the difference between the current timestamp on the slave and the original timestamp logged on the master for the most event currently being processed on the slave.
准确说Seconds_Behind_Master指的是slave 系统当前时间和当前SQL thread处理的binlog event时间差,这个时间差与binlog event 和 slave 系统时间直接关联。如果master 和 slave时间不同步,就有可能产生 Seconds_Behind_Master 参数显示问题,可以在从库执行 date -s“+1 hour”  看看Seconds_Behind_Master是不是突然变的很大了?
  • When no event is currently being processed on the slave, this value is 0.
当前没有待处理binlog event时,Seconds_Behind_Master也为0
这样问题就已经很明朗了,因为I/O thread并不能保证和master保持实时同步,所以Seconds_Behind_Master=0也不同说明同步没有延时。 阅读全文…

MySQL性能优化参数整理(陆续更新)

2012年2月24日 没有评论

本文就常见MySQL运行参数做详细列举与说明,并对各参数的设置条件做尽可能详尽的解释,由于MySQL配置参数比较多,本文尽可能全面介绍各参数,如有遗漏将陆续更新,欢迎大家点评投稿.

鉴于MySQL配置参数优化与数据库运行环境有密切关联,本文主要对MySQL参数做解释翻译整理,部分设置参数值来源整理自其他博客,考虑到尊重原创,本人尽量标注引用来源,如有遗漏,请及时联系我更正。 阅读全文…

分类: MySQL 标签: , ,

【译】如何防范SQL注入攻击(代码示例)

2012年2月7日 没有评论

本文通过一些简单示例解释基本的SQL注入攻击,并提供方法如何去处理这些攻击问题。

如题所示,黑客能够通过SQL查询语句进行攻击。一些网站的开发者还不太了解黑客是如何通过SQL查询来攻击网站的。如果程序完全相信用户的输入信息,并不做一些适当的过滤处理,SQL注入完全可以实现,SQL注入的思路就是让程序运行一些不被期望的SQL查询。
阅读全文…

分类: MySQL 标签: ,

MySQL备份工具:Percona Xtrabackup

2012年1月15日 没有评论

Percona Xtrabackup 是一个开源的MySQL热备份工具,采用GPL开源协议,支持InnoDB、XtraDB引擎在线热备份、Non-blockingd备份,流式备份,压缩备份,增量备份等特性,是商业备份工具InnoDB HotBackup的一个很好的替代品。目前Percona Xtrabackup支持InnoDB、XtraDB和MyISAM,原生MySQL以及Percona Server上的XtraDB,可运行在Linux和FreeBSD上,Windows平台目前处于测试阶段。
阅读全文…

分类: Linux, MySQL 标签: , , ,

详解MyISAM Key Cache

2012年1月13日 没有评论

“Cache为王”,无所不在。为了最小化磁盘I/O,MyISAM将最频繁访问的索引块(“index block”)都放在内存中,这样的内存缓冲区我们称之为Key Cache,它的大小可以通过参数key_buffer_size来控制。在MyISAM的索引文件中(MYI),连续的单元(contiguous unit)组成一个Block,Index block的大小等于该BTree索引节点的大小。Key Cache就是以Block为单位的。

1. MyISAM如何使用Key Cache

当MySQL请求(读或写)MyISAM索引文件中某个Index Block时,首先会看Key Cache队列中是否已经缓存了对应block。如果有,就直接在Key Cache队列中进行读写了,不再需要请求磁盘。如果是写请求,那么Key Cache中的对应Block就会被标记为Dirty(和磁盘不一致)。在MyISAM在Key Cache成功请求(读写)某个Block后,会将该Block放到Key Cache队列的头部。

如果Key Cache中没有待请求(读或写)的Block,MyISAM会向磁盘请求对应的Block,并将其放到Key Cache的队列头部。队列如果满了,会将队列尾部的Block删除,该Block如果是Dirty的,会将其Flush到磁盘上。我们看到MyISAM维护了一个LRU(Least Recently Used)的Key Cache队列。队列中的Dirty Block会在Block被踢出队列时Flush到磁盘上。
阅读全文…

分类: MySQL 标签: , , ,

MySQL 数据库性能优化之缓存参数优化

2012年1月12日 没有评论

数据库属于 IO 密集型的应用程序,其主要职责就是数据的管理及存储工作。而我们知道,从内存中读取一个数据库的时间是微秒级别,而从一块普通硬盘上读取一个IO是在毫秒级别,二者相差3个数量级。所以,要优化数据库,首先第一步需要优化的就是 IO,尽可能将磁盘IO转化为内存IO。本文先从 MySQL 数据库IO相关参数(缓存参数)的角度来看看可以通过哪些参数进行IO优化阅读全文…

分类: MySQL 标签: , , , , ,

MySQL服务器安装完之后如何调节性能

2011年12月21日 没有评论

看了原文觉得挺实用的,确实对于DB新农来讲,用系统默认配置或者MySQL自带的几个配置模板比较多,顶多会去调几个参数。这编博客列出的点如作者所述,“MySQL可调的参数确实不少,不过真正对系统性能有非常显著的影响就那么几个”。

key_buffer_size
innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_log_file_size
innodb_log_buffer_size
innodb_flush_log_at_trx_commit
table_cache
thread_cache
query_cache_size

阅读全文…

分类: MySQL 标签:

MySQL如何避免使用swap

2011年12月20日 没有评论

Linux有很多很好的内存、IO调度机制,但是并不会适用于所有场景。对于DBA来说Linux比较让人头疼的一个地方是,它不会因为MySQL很重要就避免将分配给MySQL的地址空间映射到swap上。对于频繁进行读写操作的系统而言,数据看似在内存而实际上在磁盘是非常糟糕的,响应时间的增长很可能直接拖垮整个系统。这篇blog主要讲讲我们作为DBA,怎样尽量避免MySQL惨遭swap的毒手。

首先我们要了解点基础的东西,比如说为什么会产生swap。假设我们的物理内存是16G,swap是4G。如果MySQL本身已经占用了12G物理内存,而同时其他程序或者系统模块又需要6G内存,这时候操作系统就可能把MySQL所拥有的一部分地址空间映射到swap上去。

cp一个大文件,或用mysqldump导出一个很大的数据库的时候,文件系统往往会向Linux申请大量的内存作为cache,一不小心就会导致L使用swap。这个情景比较常见,以下是最简单的三个调整方法:
1、/proc/sys/vm/swappiness的内容改成0(临时),/etc/sysctl.conf上添加vm.swappiness=0(永久)
这个参数决定了Linux是倾向于使用swap,还是倾向于释放文件系统cache。在内存紧张的情况下,数值越低越倾向于释放文件系统cache。
当然,这个参数只能减少使用swap的概率,并不能避免Linux使用swap。
2、修改MySQL的配置参数innodb_flush_method,开启O_DIRECT模式。
这种情况下,InnoDB的buffer pool会直接绕过文件系统cache来访问磁盘,但是redo log依旧会使用文件系统cache。值得注意的是,Redo log是覆写模式的,即使使用了文件系统的cache,也不会占用太多。
3、添加MySQL的配置参数memlock
这个参数会强迫mysqld进程的地址空间一直被锁定在物理内存上,对于os来说是非常霸道的一个要求。必须要用root帐号来启动MySQL才能生效。

还有一个比较复杂的方法,指定MySQL使用大页内存(Large Page)。Linux上的大页内存是不会被换出物理内存的,和memlock有异曲同工之妙。
具体的配置方法可以参考:http://harrison-fisk.blogspot.com/2009/01/enabling-innodb-large-pages-on-linux.html

这里需要补充一下上面4种方法原理和实现机制,对于Linux api不感兴趣的同学可以直接跳过。

一、操作系统设置swap的目的
程序运行的一个必要条件就是足够的内存,而内存往往是系统里面比较紧张的一种资源。为了满足更多程序的要求,操作系统虚拟了一部分内存地址,并将之映射到swap上。对于程序来说,它只知道操作系统给自己分配了内存地址,但并不清楚这些内存地址到底映射到物理内存还是swap。
物理内存和swap在功能上是一样的,只是因为物理存储元件的不同(内存和磁盘),性能上有很大的差别。操作系统会根据程序使用内存的特点进行换入和换出,尽可能地把物理内存留给最需要它的程序。但是这种调度是按照预先设定的某种规则的,并不能完全符合程序的需要。一些特殊的程序(比如MySQL)希望自己的数据永远寄存在物理内存里,以便提供更高的性能。于是操作系统就设置了几个api,以便为调用者提供“特殊服务”。

二、Linux提供的几个api
1、mlockall()和munlockall()
这一对函数,可以让调用者的地址空间常驻物理内存,也可以在需要的时候将此特权取消。mlockall()的flag位可以是MCL_CURRENT和MCL_FUTURE的任意组合,分别代表了“保持已分配的地址空间常驻物理内存”和“保持未来分配的地址空间常驻物理内存”。对于Linux来说,这对函数是非常霸道的,只有root用户才有权限调用。

2、shmget()和shmat()
这一对函数,可以向操作系统申请使用大页内存(Large Page)。大页内存的特点是预分配和永驻物理内存,因为使用了共享内存段的方式,page table有可能会比传统的小页分配方式更小。对于多进程共享内存的程序(比如ORACLE),大页内存能够节省很多page table开销;而对于MySQL来说,性能和资源开销都没有显著变化,好处就在于减少了内存地址被映射到swap上的可能。至于为什么是减少,而不是完全避免,之后再讲解。

3、O_DIRECT和posix_memalign()
以上两个方法都不会减少内存的使用量,调用者的本意是获取更高的系统特权,而不是节约系统资源。O_DIRECT是一种更加理想化的方式,通过避免double buffer,节省了文件系统cache的开销,最终减少swap的使用率。O_DIRECT是Linux IO调度相关的标志,在open函数里面调用。通过O_DIRECT标志打开的文件,读写都不会用到文件系统的cache。传统的数据库(ORACLE、MySQL)基本都有O_DIRECT相关的开关,在提高性能的同时,也减少了内存的使用。至于posix_memalign(),是用来申请对齐的内存地址的。只有用posix_memalign()申请的内存地址,才能用来读写O_DIRECT模式下的文件描述符。

4、madvise()和fadvise()
这对函数也是比较温和的,可以将调用者对数据访问模式的预期传递给Linux,以期得到更好的性能。
我们比较感兴趣的是MADV_DONTNEED和FADV_NOREUSE这两个flag。前者会建议Linux释放指定的内存区域,而后者会建议文件系统释放指定文件所占用的cache。

三、MySQL内存使用相关的一些代码
1、memlock
在MySQL的源码目录里面查询memlock,可以知道这个参数的作用是使MySQL调用mlockall()。在源码里面匹配可以得知NDB、MyISAM和mysqld都调用了mlockall()。NDB是可以独立于MySQL而存在的存储引擎,此处按下不表。mysqld调用mlockall()的方式有点出乎意料,在init_server_components()函数里传给mlockall()的flag是MCL_CURRENT,也就是说之后申请的内存一概不用锁住。再看看MyISAM的调用顺序是:mlockall() <- lock_memory() <- mi_repair(),MyISAM只有修复的时候会调用mlockall()函数。

2、large-pages
根据Linux的内核文档,大页内存有两种方法可以用到:一种是创建hugetlb类型的文件,并将它mmap到程序的内存地址里面,然后进行正常的读写操作。另外一种是之前说到的shmget()+shmat(),也正是MySQL采用的方式。在MySQL的源码目录里面匹配shmget,可以发现BDB、NDB、InnoDB、MyISAM都调用了这个函数。接着看一下比较常用的InnoDB和MyISAM引擎。
在InnoDB里面可以找到os_mem_alloc_large()调用了shmget(),而调用os_mem_alloc_large()的函数只有buf_pool_init()――InnoDB Buffer Pool的初始化函数。根据观察得到的结论是,InnoDB会根据配置参数在Buffer Pool里面使用大页内存,Redo log貌似就没有这个待遇了。
对于MyISAM,在storage层级的代码里面找不到对shmget()的直接调用。这是因为MyISAM是MySQL的原生存储引擎,很多函数存放在上一层的mysys目录里面。通过搜索shmget(),我们可以找到MyISAM的调用顺序是这样的:shmget() <- my_large_malloc_int() <- my_large_malloc() <- init_key_cache()。也就是说MyISAM只有索引缓存用到了大页内存,这是很容易理解,因为MyISAM的数据是直接扔给文件系统做缓存的,没法使用大页内存。

3、innodb_flush_method
O_DIRECT是BDB、NDB、InnoDB特有的参数,在这里只讨论InnoDB这个比较常见的引擎。在InnoDB的源码目录里面匹配O_DIRECT,很容易找到一个叫做os_file_set_nocache()的函数,而这个函数作用是将文件的打开方式改为O_DIRECT模式。再跟踪一下,会发现只有os_file_create()函数调用了os_file_set_nocache()。虽然函数名里面还有create,实际上os_file_create()会根据传入参数的不同,选择打开或者新建一个文件。同时os_file_create()还会根据MySQL的配置,来调用os_file_set_nocache()关闭文件系统的相应cache。在os_file_create()函数里面有如下一段代码:
/* We disable OS caching (O_DIRECT) only on data files */
if (type != OS_LOG_FILE &&
srv_unix_file_flush_method == SRV_UNIX_O_DIRECT)
{
os_file_set_nocache(file, name, mode_str);
}
这段代码的意思是,只有InnoDB的数据文件有资格使用O_DIRECT模式,Redo log是不能使用的。

以上的分析基于5.0.85版本的原版MySQL,InnoDB是Innobase。
版本不同情况下可能会有一些出入,欢迎参与讨论。

参考文献:
Virtual memory@wiki
All about Linux swap space
HugeTLB C Large Page Support in the Linux Kernel
Page table@wiki
原文 http://www.taobaodba.com/html/552_mysql_avoid_swap.html

分类: MySQL 标签:

MySQL Query Cache 运行参数及状态检测

2011年12月19日 没有评论

MySQL的Query Cache系统变量都是以“query_cache“作为前后缀的,它们分别是:

have_query_cache
query_cache_limit
query_cache_min_res_unit
query_cache_size
query_cache_type
query_cache_wlock_invalidate

其中”have_query_cache“变量标识是QueryCache是否可用。可通过以下命令查看:

通常这个变量值为YES,即使QueryCache功能不可用的时候:当“query_cache_size”的值为0的时候,即使”have_query_cache=YES”,QueryCache也不可用。

标准版MySQL中“query_cache_size”为0,也即,默认配置下QueryCache是不可用的。

我们可以通过配置文件、启动参数和命令设置相关参数。如通过”SET”命令设置“query_cache_size”:

全局设置:mysql> SET GLOBAL query_cache_size = 1048576;
会话设置:mysql> SET SESSION query_cache_size = 1048576;

      如果你使用的时候标准 MySQL 分发版的话,Query Cache 功能默认都是打开的,我们可以通过调整 MySQL Server 的参数选项打开该功能。它们主要由以下5个参数构成:

  • query_cache_limit:允许 Cache 的单条 Query 结果集的最大容量,默认是1MB,超过此参数设置的 Query 结果集将不会被 Cache
  • query_cache_min_res_unit:设置 Query Cache 中每次分配内存的最小空间大小,也就是每个 Query 的 Cache 最小占用的内存空间大小
  • query_cache_size:设置 Query Cache 所使用的内存大小,默认值为0,大小必须是1024的整数倍,如果不是整数倍,MySQL 会自动调整降低最小量以达到1024的倍数。
    query_cache_size最小值不能小于40KB,否则系统将会产生一个WARNING:Query cache failed to set size 39936;new query cache size is 0.
  • query_cache_type:控制 Query Cache 功能的开关,可以设置为0(OFF),1(ON)和2(DEMAND)三种,意义分别如下:
    • 0(OFF):关闭 Query Cache 功能,任何情况下都不会使用 Query Cache
    • 1(ON):开启 Query Cache 功能,但是当 SELECT 语句中使用的 SQL_NO_CACHE 提示后,将不使用Query Cache
    • 2(DEMAND):开启 Query Cache 功能,但是只有当 SELECT 语句中使用了 SQL_CACHE 提示后,才使用 Query Cache
  • query_cache_wlock_invalidate:控制当有写锁定发生在表上的时刻是否先失效该表相关的 Query Cache,如果设置为 1(TRUE),则在写锁定的同时将失效该表相关的所有 Query Cache,如果设置为0(FALSE)则在锁定时刻仍然允许读取该表相关的 Query Cache。

系统的 Query Cache 的运行状态参数
MySQL 提供了一系列的 Global Status 来记录 Query Cache 的当前状态,具体如下:

  • Qcache_free_blocks:目前还处于空闲状态的 Query Cache 中内存 Block 数目
  • Qcache_free_memory:目前还处于空闲状态的 Query Cache 内存总量
  • Qcache_hits:Query Cache 命中次数
  • Qcache_inserts:向 Query Cache 中插入新的 Query Cache 的次数,也就是没有命中的次数
  • Qcache_lowmem_prunes:当 Query Cache 内存容量不够,需要从中删除老的 Query Cache 以给新的 Cache 对象使用的次数
  • Qcache_not_cached:没有被 Cache 的 SQL 数,包括无法被 Cache 的 SQL 以及由于 query_cache_type 设置的不会被 Cache 的 SQL
  • Qcache_queries_in_cache:目前在 Query Cache 中的 SQL 数量
  • Qcache_total_blocks:Query Cache 中总的 Block 数量

可以根据这几个状态计算出 Cache 命中率,计算出 Query Cache 大小设置是否足够,通常不建议将 Query Cache 的大小设置超过256MB,这也是业界比较常用的做法。

分类: MySQL 标签: