Archive for the ‘数据库’ Category

Dynamo使用一致性hash来实现partition and replication,从而达到高扩展和高可用。在实现上,对经典一致性hash进行了一些优化,本文尝试予以解释。

partitioning algorithm

One of the key design requirements for Dynamo is that it must scale incrementally. This requires a mechanism to dynamically partition the data over the set of nodes (i.e., storage hosts) in the system. Dynamo’s partitioning scheme relies on consistent hashing to distribute the load across multiple storage hosts. In consistent hashing [10], the output range of a hash function is treated as a fixed circular space or “ring” (i.e. the largest hash value wraps around to the smallest hash value). Each node in the system is assigned a random value within this space which represents its “position” on the ring. Each data item identified by a key is assigned to a node by hashing the data item’s key to yield its position on the ring, and then walking the ring clockwise to find the first node with a position larger than the item’s position.

假设我们认为hash ring的取值范围是pow(2, 32),那么经典一致性hash算法采用随机的方式计算position,例如直接random(0, pow(2,32)-1),将存储节点映射到ring上,两个存储节点的position就圈出了一个region。在读写item时,也采用任意hash函数计算范围是pow(2,32)的position,看它落在哪个region里以决定由哪个存储节点负责。

Thus, each node becomes responsible for the region in the ring between it and its predecessor node on the ring. The principle advantage of consistent hashing is that departure or arrival of a node only affects its immediate neighbors and other nodes remain unaffected.

采用经典一致性hash的好处是,当有存储节点变化时,影响较为平滑,仅邻居节点需要向新加入的节点迁移数据。

The basic consistent hashing algorithm presents some challenges. First, the random position assignment of each node on the ring leads to non-uniform data and load distribution. Second, the basic algorithm is oblivious to the heterogeneity in the performance of nodes. To address these issues, Dynamo uses a variant of consistent hashing (similar to the one used in [10, 20]): instead of mapping a node to a single point in the circle, each node gets assigned to multiple points in the ring. To this end, Dynamo uses the concept of “virtual nodes”. A virtual node looks like a single node in the system, but each node can be responsible for more than one virtual node. Effectively, when a new node is added to the system, it is assigned multiple positions (henceforth, “tokens”) in the ring. The process of fine-tuning Dynamo’s partitioning scheme is discussed in Section 6.

但是经典方法也有一些弊端。随机计算的节点position会导致不均衡,而且也没有考虑节点间的异构性。Dynamo引入virtual nodes的概念予以解决。

Replication

To achieve high availability and durability, Dynamo replicates its data on multiple hosts. Each data item is replicated at N hosts, where N is a parameter configured “per-instance”. Each key, k, is assigned to a coordinator node (described in the previous section). The coordinator is in charge of the replication of the data items that fall within its range. In addition to locally storing each key within its range, the coordinator replicates these keys at the N-1 clockwise successor nodes in the ring. This results in a system where each node is responsible for the region of the ring between it and its Nth predecessor. In Figure 2, node B replicates the key k at nodes C and D in addition to storing it locally. Node D will store the keys that fall in the ranges (A, B], (B, C], and (C, D].

为了获得高可用,Dynamo采用多副本的方式,将数据copy到ring上连续的N个节点。

The list of nodes that is responsible for storing a particular key is called the preference list. The system is designed, as will be explained in Section 4.8, so that every node in the system can determine which nodes should be in this list for any particular key. To account for node failures, preference list contains more than N nodes. Note that with the use of virtual nodes, it is possible that the first N successor positions for a particular key may be owned by less than N distinct physical nodes (i.e. a node may hold more than one of the first N positions). To address this, the preference list for a key is constructed by skipping positions in the ring to ensure that the list contains only distinct physical nodes.

regions与存储节点间的一对多映射关系被称为preference list,这个list需要在Dynamo节点间传播、共享。并保证仅存储distinct物理节点。

更进一步的优化

Dynamo uses consistent hashing to partition its key space across its replicas and to ensure uniform load distribution. A uniform key distribution can help us achieve uniform load distribution assuming the access distribution of keys is not highly skewed. In particular, Dynamo’s design assumes that even where there is a significant skew in the access distribution there are enough keys in the popular end of the distribution so that the load of handling popular keys can be spread across the nodes uniformly through partitioning. This section discusses the load imbalance seen in Dynamo and the impact of different partitioning strategies on load distribution.

关于数据倾斜,这里有几个假设的前提。首先,在keys的流量分布不是严重倾斜时,均匀的keys分布可以获得较为均衡的负载。其次,Dynamo认为当popular keys足够多时,即使流量不均衡,只要把popular keys分散开,也可以获得均衡的负载。但这反过来也就意味着,当流量不太大、且仅集中于少量popular keys的时候,可能有些节点会过载。Dynamo的分片策略也在演进如下。(另外,这里有一个隐含的概念,即负载均衡,需要数据分布均衡、流量分布均衡)

Strategy 1: T random tokens per node and partition by token value: This was the initial strategy deployed in production (and described in Section 4.2). In this scheme, each node is assigned T tokens (chosen uniformly at random from the hash space). The tokens of all nodes are ordered according to their values in the hash space. Every two consecutive tokens define a range. The last token and the first token form a range that “wraps” around from the highest value to the lowest value in the hash space. Because the tokens are chosen randomly, the ranges vary in size. As nodes join and leave the system, the token set changes and consequently the ranges change. Note that the space needed to maintain the membership at each node increases linearly with the number of nodes in the system.

策略1是最原始的方式,每个物理节点虚出T个virtual nodes,每个virtual node随机分配一个token。tokens圈出的range决定其存储数据的范围。由于采用随机的方式,故需要一个完整的映射表,才可以获悉整个Dynamo集群的存储映射关系,而该表又随着节点数的线性增长。

While using this strategy, the following problems were encountered. First, when a new node joins the system, it needs to “steal” its key ranges from other nodes. However, the nodes handing the key ranges off to the new node have to scan their local persistence store to retrieve the appropriate set of data items. Note that performing such a scan operation on a production node is tricky as scans are highly resource intensive operations and they need to be executed in the background without affecting the customer performance. This requires us to run the bootstrapping task at the lowest priority. However, this significantly slows the bootstrapping process and during busy shopping season, when the nodes are handling millions of requests a day, the bootstrapping has taken almost a day to complete. Second, when a node joins/leaves the system, the key ranges handled by many nodes change and the Merkle trees for the new ranges need to be recalculated, which is a non-trivial operation to perform on a production system. Finally, there was no easy way to take a snapshot of the entire key space due to the randomness in key ranges, and this made the process of archival complicated. In this scheme, archiving the entire key space requires us to retrieve the keys from each node separately, which is highly inefficient.

这种方式仍存在弊端。首先,新节点的加入,需要从一些old节点迁移数据,由于数据不是连续存储的,扫描较为耗时,且需后台执行。其次,由于range变化导致数据变化了,所涉及的merkle trees需要重算。最后,snapshot也不好做。(这里由于对Dynamo底层存储细节不太了解,无法细致推导)

The fundamental issue with this strategy is that the schemes for data partitioning and data placement are intertwined. For instance, in some cases, it is preferred to add more nodes to the system in order to handle an increase in request load. However, in this scenario, it is not possible to add nodes without affecting data partitioning. Ideally, it is desirable to use independent schemes for partitioning and placement. To this end, following strategies were evaluated:

根本原因是数据分片与数据分布耦合在一起了!

Strategy 2: T random tokens per node and equal sized partitions: In this strategy, the hash space is divided into Q equally sized partitions/ranges and each node is assigned T random tokens. Q is usually set such that Q >> N and Q >> S*T, where S is the number of nodes in the system. In this strategy, the tokens are only used to build the function that maps values in the hash space to the ordered lists of nodes and not to decide the partitioning. A partition is placed on the first N unique nodes that are encountered while walking the consistent hashing ring clockwise from the end of the partition. Figure 7 illustrates this strategy for N=3. In this example, nodes A, B, C are encountered while walking the ring from the end of the partition that contains key k1. The primary advantages of this strategy are: (i) decoupling of partitioning and partition placement, and (ii) enabling the possibility of changing the placement scheme at runtime.

策略2将data partition和location的概念分开,partition是数据分片,而location是由token圈定的范围决定的。另外partition是等分的,一般取值非常大。在读写item时,hash出posittion,看它落在哪几个tokens圈定的范围里。这时partition的概念感觉意义还不大,从论文里也可以看到,策略2仅是一个过渡方案。真正重要的是策略3。

Strategy 3: Q/S tokens per node, equal-sized partitions: Similar to strategy 2, this strategy divides the hash space into Q equally sized partitions and the placement of partition is decoupled from the partitioning scheme. Moreover, each node is assigned Q/S tokens where S is the number of nodes in the system. When a node leaves the system, its tokens are randomly distributed to the remaining nodes such that these properties are preserved. Similarly, when a node joins the system it “steals” tokens from nodes in the system in a way that preserves these properties.

策略3也是将hash space等分为取值较大的Q个partitions,而且partition与存储位置解耦(映射关系,而不是相等关系)。同时,每个存储节点分配Q/S个tokens(即形成Q/S个virtual nodes)。所以virtual node的存储范围其实与partition大小是一致的了,但由于物理节点与virtual节点间是1对多关系,故可以灵活的以virtual node为单位进行数据迁移。当有存储节点离开时,它的tokens随机分布到其他节点上;有节点加入时,不是随机分配新tokens了,而是从现有tokens里随机获取所需。

另外,由于tokens总量都不变,故映射表也恒定。猜测:在put/get item时,需要通过item hash出的position算出partition,再由partition映射到token(由于两者都是等分且数量一致,可以直接=或算出)、token映射到node。有节点增删时,需要all token list,从中随机获取tokens后,根据token到node的映射关系,迁移数据,并修改token到node的映射关系,这时partition到token的关系是不需要变化的。

RDB源码解读:

RDB功能在/path/to/redis/src/rdb.c里,其入口函数是rdbSave方法:

 int rdbSave(char *filename) ;

该方法最主要的调用者是redis.c的serverCron方法,通过rdbSaveBackground方法间接调用。顾名思义,rdbSaveBackground方法是通过fork出一个后台执行的子进程来进行持久化操作的,执行完毕子进程就退出。这里,主要利用的就是copy-on-write机制,即如果没有数据修改,则主子进程是复用同一块内存的,而一旦发生数据写入,则子进程的内存区域会copy出来,保持原状。这样,能够最大程度保持RDB持久化时,对Redis的正常访问是没有影响的(注意,当数据集非常大时,fork可能会阻塞请求)。

回到rdbSave方法,它的工作就是:

  1. 以覆盖写的方式,打开临时文件(命名中包含子进程的pid,从而保持唯一性)
  2. 写入magic头:REDISxxxx,后四位是以0补齐的Redis_Rdb_version,从而支持版本识别
  3. 遍历所有redis db,并忽略空数据的db:
    1. 以safe方式获取dict iterator
    2. 遍历dict->ht,默认情况先遍历ht[0],在遇到rehash的情况下会继续遍历ht[1]
    3. 获取当前item的key、val、expire time,“写入文件”(其实是缓存)
  4. 扫尾工作包括写EOF结束符,以及按需写入checksum
  5. 为了保证数据真的持久化到磁盘,调用fflush强制刷新用户缓存(可能还在磁盘缓冲区),调用fsync强制写入磁盘
  6. 将临时文件改名,真的成为RDB持久化文件,并更改RDB统计数据

RDB持久方式的问题是,磁盘上的数据可能总是内存数据的子集,故障恢复无法保证不丢数据。但其优点是,对线上服务影响较小(只是fork子进程的消耗 和 主子进程对系统资源的竞争关系)。

数据的持久化,是为了在故障或重启时,能够顺利恢复数据。在Redis的main方法里,会调用loadDataFromDisk方法,根据配置方式分别从AOF或RDB文件中Load数据。其中,rdbLoad是入口方法,这个方法同时也应用于主从同步。该方法执行:

  1. 读入RDB文件,检查magic,如果版本不匹配,则退出加载
  2. 依次读入文件内容,根据不同type进行处理
    1. 在遇到真正的key、val数据时,若是master节点,则需检查其expire时间,若已超时则free内存后丢弃(注意,slave节点不直接丢弃,而是走expire机制。因为是否超时,应该由master来控制,从而保证master-slave的数据尽量一致)
    2. 否则,将其插入dict entry里,并按需设置expire时间
  3. 最后,会按需检查checksum,若失败则直接exit退出
 RDB fork的资源消耗

fork一般被认为是没有什么系统资源消耗的,但redis文档里却提到当数据量非常大时,RDB fork可能会阻塞请求,这是为什么呢?

fork creates a child process that differs from the parent process only in its PID and PPID, and in the fact that resource
utilizations are set to 0. File locks and pending signals are not inherited.

Under Linux, fork is implemented using copy-on-write pages, so the only penalty incurred by fork is the time and memory required to duplicate the parent’s page tables, and to create a unique task structure for the child.

由于在linux下,fork采用了COW(copy on write)机制,且尽量优先保证子进程先执行,所以一般情况下其系统消耗很小。但,COW不是万能的。一种猜测是,如果数据量很大,那么page tables可能也会较大,所以fork时复制的成本增大。另一个猜测是,对于高并发写入的redis,即使子进程先执行了,但如果数据量大,可能一个时间片内是无法执行完的。这样的话,一旦切换到主进程,其随后的写入很快到来,立刻就会导致被修改到的页(page)复制,在一个高写入且数据较分散的redis里,其涉及到page量可能会比较大,这样就可能会对主进程造成影响,使其无法响应正常的读写请求了。

这里还有另外一个坑,就是一旦发生copy,则代表主子无法共享内存了,也就是这时的内存需求是 正常内存 × 2!如果这时开启了swap,则很可能会交换到磁盘,从而导致性能的降低。具体可以参考http://www.iteye.com/topic/808293,在公司的Redis集群里,貌似是在不提供服务的slave节点上开启RDB持久化的,这样相当于用资源来换可靠性和性能。

AOF源码解读

相对于RDB,AOF方式可能略微复杂,在阅读其持久化描述后,首先有以下3个问题:

  1. 什么时候调用AOF写日志?
  2. 什么时候进行fsync操作?
  3. 什么时候进行AOF日志的重写?

在redis的设计中,将其分为:命令传播、缓存追加、文件追加及fsync 三步骤。以下将对照源码分别解读。

1. 命令传播

redis.c的call方法是响应读写请求的核心方法,其执行完成之后会调用propagate方法,将命令变更传递给AOF和replication程序(按需)。其中AOF是触发了feedAppendOnlyFile方法。所以,AOF写日志,是在每次执行完写操作成功之后,被调用的。

2. 缓存追加

feedAppendOnlyFile方法里,其功能之一是将接收到的cmd和arg参数,转换为待写入AOF文件的网络通信协议(这样在写入时,就可以起一个mock的客户端,逐条读入并mock发起对server的调用)。首先按需select对应的db,然后根据cmd类型(expire、setex and psetex、其他)进行格式转换,存入buf字段中。调用sdscatlen方法,将buf里的数据写入aof_buf里。

以上是正常情况下的处理逻辑,但若这时有正在运行的aof rewriting 子进程,则还需将新数据追加给它,故还需调用aofRewriteBufferAppend方法告知子进程。由于仅主进程会写,故没有加锁操作(主子进程间如何保证互斥?

3. 文件追加及fsync

当完成步骤2后,aof数据存在于程序中开辟的aof_buf内存区中,还存在极大丢失的风险。首当其冲是需要将其“写入”文件,但linux的write类方法的调用,并不是真的持久化到磁盘了,而是先放到系统缓冲区,只有在调用fsync等命令或缓冲区满了以后,才会被真正写入磁盘。所以,这就存在何时调用write、何时fsync的两个时间点了。

一个重要的触发函数beforeSleep,这个方法在Redis每次进入sleep/wait去等待监听的端口发生I/O事件之前被调用。在该方法最后,会触发flushAppendOnlyFile,这时aof_buf中可能会有之前几次client请求的写入数据,会被批量处理。

另外一个重要触发是serverCron,这个方法每秒执行hz次,相当于是一个批量处理的定时器方法。在该方法里,有两处调用flushAppendOnlyFile的地方,主要是当flush出现异常时,尽早处理:

  • 每次都条件执行的 if(server.aof_flush_postponed_start),在AOF_FSYNC_EVERYSEC被hang住时,下一个loop里触发
  • run_with_period(1000)即1s左右执行一次的,在flushAppendOnlyFile中write出错时,下一个loop被触发

先来看flushAppendOnlyFile方法,其执行流程如下图:

 

总结起来,针对3种不同的fsync配置,其执行如下:

  • AOF_FSYNC_NO
    • 每次都执行write,不执行fsync
  • AOF_FSYNC_EVERYSEC
    • 若当前有fsync子进程,且推迟执行未超过2s,则继续推迟执行
    • 否则,调用write。是否调用fsync:
      • 若当前没有其他fsync子进程,且1s未fsync了,则fork子进程fsync
      • 若当前有其他fsync子进程,但aof_no_fsync_on_rewrite设置为no,且1s未fsync了,则fork子进程fsync(即未配置指定不允许多个fsync子进程同时存在)
      • 若当前有其他fsync子进程,但aof_no_fsync_on_rewrite设置为yes,则不fsync
  • AOF_FSYNC_ALWAYS
    • 每次都执行write,每次都主进程执行fsync
AOF rewrite

todo

总结

总体来看,不管是RDB还是AOF方案,都对所在服务器的性能有所影响。所以,在redis的实际运维中,会关闭主服务器的持久化选项,而采用从服务器进行持久化。这就又涉及到“replication”,即主从复制了。下篇继续。

参考文档:
  • http://redis.readthedocs.org/en/latest/topic/persistence.html
  • http://blog.nosqlfan.com/html/3813.html
  • http://www.makelinux.net/books/lkd2/ch03lev1sec2
  • http://redisbook.readthedocs.org/en/latest/internal/aof.html   AOF-Redis设计与实现

监控以发现问题

http://redis.readthedocs.org/en/latest/server/info.html

通过redis的info命令,可以查看各种信息,其中包括内存相关。

  • memory : 内存信息,包含以下域:
    • used_memory : 由 Redis 分配器分配的内存总量,以字节(byte)为单位
    • used_memory_human : 以人类可读的格式返回 Redis 分配的内存总量
    • used_memory_rss : 从操作系统的角度,返回 Redis 已分配的内存总量(俗称常驻集大小)。这个值和 top 、 ps等命令的输出一致。
    • used_memory_peak : Redis 的内存消耗峰值(以字节为单位)
    • used_memory_peak_human : 以人类可读的格式返回 Redis 的内存消耗峰值
    • used_memory_lua : Lua 引擎所使用的内存大小(以字节为单位)
    • mem_fragmentation_ratio : used_memory_rss 和 used_memory 之间的比率
    • mem_allocator : 在编译时指定的, Redis 所使用的内存分配器。可以是 libc 、 jemalloc 或者 tcmalloc 。
    在理想情况下, used_memory_rss 的值应该只比 used_memory 稍微高一点儿。
    当 rss > used ,且两者的值相差较大时,表示存在(内部或外部的)内存碎片。
    内存碎片的比率可以通过 mem_fragmentation_ratio 的值看出。
    当 used > rss 时,表示 Redis 的部分内存被操作系统换出到交换空间了,在这种情况下,操作可能会产生明显的延迟。

    Because Redis does not have control over how its allocations are mapped to memory pages, highused_memory_rss is often the result of a spike in memory usage.

    当 Redis 释放内存时,分配器可能会,也可能不会,将内存返还给操作系统。
    如果 Redis 释放了内存,却没有将内存返还给操作系统,那么 used_memory 的值可能和操作系统显示的 Redis 内存占用并不一致。
    查看 used_memory_peak 的值可以验证这种情况是否发生。

调优手段

http://redis.io/topics/memory-optimization

Redis(>=2.2)针对小数据量的hash、list、纯数字的set、sorted set会采用特殊的存储方式(encoding)。当然,在数据量增大时,从优化encoding到正常encoding间的数据迁移,对用户来说是透明的。

  • hash
    • 优化存储:zipmap
    • 正常存储:dict(真正的hash table)
  • list
    • 优化存储:ziplist
    • 正常存储:list
  • set
    • 优化存储:intset
    • 正常存储:dict
  • sorted set
    • 优化存储:
    • 正常存储:dict+skip list(跳跃表)

判断小数据量的标准是entries个数和value的字节数:

  • hash-max-zipmap-entries 64  (hash-max-ziplist-entries for Redis >= 2.6) // hash,一个key下fields的个数
  • hash-max-zipmap-value 512   (hash-max-ziplist-value for Redis >= 2.6) // hash,一个key下各个field对应的value大小
  • list-max-ziplist-entries 512  // list
  • list-max-ziplist-value 64   // list
  • zset-max-ziplist-entries 128 // zset
  • zset-max-ziplist-value 64   // zset
  • set-max-intset-entries 512  // set

数据结构的选择

尽可能使用hash而非大量的小内存的KV结构,因为hash在小数据量的情况下,会使用zipmap这种类似一维数组的存储方式,从而大幅节省空间。执行测试数据结果如下:

saveAsString 13824
saveAsHash 3648
small hash len 23907 mem 28768
bigger hash len 24001 mem 37072

可以看到,存储相同的数据(entry个数和value字节数都小于ziplist阈值),kv string需要13k,而hash只需要3k。当增大entry个数使超过阈值时,虽然原始数据值只增长了几十个字节,而redis的存储多使用了8k+,这时应该是发生了encoding的转变。(run_hash脚本如附件)

 

老土了,第一次在线上使用redis,考虑到数据量相对较大,且响应速度要求高,所以对其内存原理进行了一定学习。

使用场景:存储约百万站点的检索信息,例如每个站点的名称、url、logo等。

数据结构设计:

  1. hash结构,key = site:<id>,field = name|url|logo|…,value = <name>|<url>|<logo>|…
  2. hash结构,key = site,field = <id>,value = 序列化后的 array(‘name’=><name>, ‘url’ =><url>, ‘logo’=><logo>,…)

方案1 是从文档上看到的使用方法,但有同事提出其内存消耗可能较大,且没有table的概念了,可能不利于后续的管理。

原理层面的内存占用分析:

临时抱佛脚,看了看redis的内存数据结构,猜测两种方案下,假定数据量是50w条,每条有10个field。其结构如下:

  • 方案1:公用内存:redisDb -> struct dict -> struct dictht -> dictEntry** table ,这时dictEntry里的key对应的是“site:<id>”;私有内存:其val对应的又是一个struct dict结构体 -> struct dictht -> dictEntry** table,这个dictEntry里的key对应的是 ‘name’ / ‘url’ / ‘logo’这样的field名称,其val对应的是name、url、logo等的数值。粗略估算其消耗为:50w * dictEntry指针 + 50w * dictEntry结构体 + 50w*(10 * dictEntry指针 + 10 * dictEntry结构体 + 10 * 字符串/数字的存储空间),第一和第二个dictEntry都消耗内存。
  • 方案2:公用内存:redisDb -> struct dict -> struct dictht -> dictEntry** table ,这时dictEntry里的key对应的是“site”;私有内存:其val对应的又是一个struct dict结构体(我们的数据较大,超出了hash_max_*的配置,故无法使用小数据时的优化方式)-> struct dictht -> dictEntry** table,这个dictEntry里的key对应的是 一个个的siteid,其val对应序列化后的字符串数据(也是struct结构体)。粗略估算其消耗为:50w * dictEntry指针 + 50w * dictEntry结构体 + 50w * Json串所在的struct空间,主要是第二个dictEntry消耗内存。
乍一看,方案2的内存消耗应该更多, 50w*(10 * dictEntry指针 + 10 * dictEntry结构体)这样都是方案1无需的冗余字段。但考虑到方案1是序列化后的数据,所以其还依赖于序列化的内存和cpu成本。

实例层面的内存占用分析:

利用redis的info方法返回的used_memory信息,对1条、1w条、10w条数据,测试的结果如下:

方案2(json) 方案1(array)
1次 使用内存(Byte) 3856 3456
1w次 使用内存 36,516,800 34,677,568
10w次 使用内存 366,213,056 346,355,232

针对一次写入,细化如下:

方案2(json) 方案1(array)
原始数据大小(Byte) 2432 2432
序列化后数据大小 3093 2432
redis使用内存大小 3824 3488
redis冗余内存大小=redis使用内存-序列化 731 1056
redis+序列化冗余内存大小=redis使用内存-原始数据 1392 1056

从以上可以看出:

  • 内存增长基本线性
  • 仅考虑redis自身使用冗余空间,方案2确实优于方案1
  • 在使用json作为序列化手段时,方案2甚至弱于方案1,因为json本身就引入大量的冗余
执行代码:http://flykobe.com/wp-content/uploads/2014/08/run.rar

其他优劣分析:

方案1,在仅更新部分field时,原子性更好,无须自己再关注锁。且没有序列化、反序列化的cpu开销。在仅需读取部分field时,网络传输量较小(按需get filed)。

方案2,若选择压缩比大的序列化方法,且存储空间确实较小。而且在获取多个field信息时(如name+url+logo),仅读取一次最终存储单元,而方案1需要读取3次。

参考文档:

  • http://www.infoq.com/cn/articles/tq-redis-memory-usage-optimization-storage 《Redis内存使用优化与存储》
  • http://www.searchtb.com/2011/05/redis-storage.html 《Redis内存存储结构分析(淘宝)》
  • https://github.com/nicolasff/phpredis/#readme 《phpredis readme》

关于MongoDB你需要知道的几件事

Henrique Lobo Weissmann是一位来自于巴西的软件开发者,他是itexto公司的联合创始人,这是一家咨询公司。近日,Henrique在博客上撰文谈到了关于MongoDB的一些内容,其中有些观点值得我们,特别是正在和打算使用MongoDB的开发者关注。

到目前为止,MongoDB在巴西是最为流行的NoSQL数据库(至少根据关于MongoDB的博客数量以及文章所判断)。MongoDB是个非常棒的解决方案,不过困扰我们的是很少有人了解过关于它的一些限制。这样的事情正在不断上演:人们看到MongoDB的限制,心里却认为这些是它的Bug。

本文列举了颇让作者困惑的一些MongoDB限制,如果你也打算使用MongoDB,那么至少要提前了解这些限制,以免遇到的时候措手不及。

消耗磁盘空间

这是我的第一个困惑:MongoDB会消耗太多的磁盘空间了。当然了,这与它的编码方式有关,因为MongoDB会通过预分配大文件空间来避免磁盘碎片问题。它的工作方式是这样的:在创建数据库时,系统会创建一个名为[db name].0的文件,当该文件有一半以上被使用时,系统会再次创建一个名为[db name].1的文件,该文件的大小是方才的两倍。这个情况会持续不断的发生,因此256、512、1024、2048大小的文件会被写到磁盘上。最后,再次创建文件时大小都将为2048Mb。如果存储空间是项目的一个限制,那么你必须要考虑这个情况。该问题有个商业解决方案,名字叫做TokuMX,使用后存储消耗将会减少90%。此外,从长远来看,repairDatabase与compact命令也会在一定程度上帮到你。

通过复制集实现的数据复制效果非常棒,不过也有限制

MongoDB中数据复制的复制集策略非常棒,很容易配置并且使用起来确实不错。但如果集群的节点有12个以上,那么你就会遇到问题。MongoDB中的复制集有12个节点的限制,这里是问题的描述,你可以追踪这个问题看看是否已经被解决了。

主从复制不会确保高可用性

尽管已经不建议被使用了,不过MongoDB还是提供了另外一种复制策略,即主从复制。它解决了12个节点限制问题,不过却产生了新的问题:如果需要改变集群的主节点,那么你必须得手工完成,感到惊讶?看看这个链接吧。

不要使用32位版本

MongoDB的32位版本也是不建议被使用的,因为你只能处理2GB大小的数据。还记得第一个限制么?这是MongoDB关于该限制的说明

咨询费非常非常昂贵(至少对于巴西的开发者与公司来说如此)

我不清楚其他国家的情况,不过至少在巴西MongoDB的咨询费是个天价。对于“Lightning Consult”计划来说,每小时的价格是450美金,而你至少需要购买两个小时的,换句话说,对于任何一家公司来说,每次咨询的价格至少是900美金。相比于RedHat和Oracle来说,这个价格太高了。

差劲的管理工具

这对于初学者来说依然是个让人头疼的问题,MongoDB的管理控制台太差劲了。我所知道的最好的工具是RoboMongo,它对于那些初次使用的开发者来说非常趁手。

了解官方的限制

让我感到惊讶的是,很少有人会查询关于他们将要使用的工具的限制。幸好,MongoDB的开发人员发布了一篇MongoDB所有限制的博客,你可以提前了解相关信息,避免在使用过程中难堪。

各位读者,现在使用MongoDB的公司也越来越多了,不妨与大家分享你在使用这个NoSQL数据库时的一些经验与教训。

 

zz from: http://www.infoq.com/cn/news/2013/11/mongodb-things?utm_source=infoq&utm_medium=popular_links_homepage

PHP有3种mysql访问API:mysql、mysqli、pdo。泛泛而言,mysql是最古老的访问方式,只提供面向过程的函数,对安全性支持一般,目前不建议使用了。mysqli和pdo都是官方建议的方式,稳定性、安全性都较好,且都提供面向对象的方式,其中mysqli也支持面向过程方式。性能3者差别不大,而且对于一般业务来说,这三种API本身的损耗都可以忽略不计。

那么,为什么官方会建议用mysqli和pdo,而逐步淘汰mysql呢?

对于这三者,官方给出了功能方面的比较:

ext/mysqli PDO_MySQL ext/mysql
PHP version introduced 5.0 5.1 2.0
Included with PHP 5.x Yes Yes Yes
Development status Active Active Maintenance only
Lifecycle Active Active Long term deprecation announced
Recommended for new projects Yes Yes No
OOP Interface Yes Yes No
Procedural Interface Yes No Yes
API supports non-blocking, asynchronous queries with mysqlnd Yes No No
Persistent Connections Yes Yes Yes
API supports Charsets Yes Yes Yes
API supports server-side Prepared Statements Yes Yes No
API supports client-side Prepared Statements No Yes No
API supports Stored Procedures Yes Yes No
API supports Multiple Statements Yes Most No
API supports Transactions Yes Yes No
Transactions can be controlled with SQL Yes Yes Yes
Supports all MySQL 5.1+ functionality Yes Most No

其中server-side prepared statements有助于安全性和性能的提升。由于sql句子和参数分开解析,降低了sql注入的风险。对于同一模板sql、不同参数多次执行的sql,由于sql句子本身的解析和优化仅需执行一次,所以有助于性能提升。但是,单次执行sql时,反而由于这种机制略微降低性能。并且,根据wiki上的阐述,某些mysql版本还会由于不cache query结果等原因,可能会降低性能。

PDO扩展还支持client-side prepared stat,从而可以对应用层提供一致性的prepare接口,并且由于扩展本身的安全性考虑,也可以有效防止sql注入。client-side的方式相当于在扩展层中拼装sql发送给mysql server端,在单次查询时性能可能还会略好于server-side方式,但是多次查询时会略差。(这里的性能区别都可以忽略)

个人感觉上表中其他区别都是编程习惯方面的,可以无视之。

其实除了API层面的区别,PHP官方还搞了mysql的client端C库mysqlnd。之前不论是mysql、mysqli还是PDO,其实都是在PHP扩展层面对mysql提供的libmysql C库的封装,而mysql被oracle收购后,据传闻是由于License的原因,PHP引入了自己的mysql客户端mysqlnd库,并且也作为PHP扩展存在。

背景

         MySQL中在对某个字段做包含匹配时可以用like。

先看这个结构和结果

 

CREATE TABLE `tb` ( 

`id` int(11) NOT NULL AUTO_INCREMENT,

`user_id` bigint(20) DEFAULT NULL,

`title` varchar(128) NOT NULL,

`memo` varchar(2000) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `title` (`title`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

mysql> explain select * from tb where title like ‘%abcd%’;

+—-+————-+——-+——+—————+——+———+——+——+————-+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+—-+————-+——-+——+—————+——+———+——+——+————-+

|  1 | SIMPLE      | tb    | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |

+—-+————-+——-+——+—————+——+———+——+——+————-+

1 row in set (1.65 sec)

 

由于like用的是 ‘%xx%’, 不符合前缀匹配的规则,因此用不上索引title,只能作全表扫描。

 

问题

以上为官方回答。但是如果是在 InnoDB这种聚集索引组织的表中,假设这个表单行很大,比如后面还有若干个类似memo的字段。

这样聚集索引会很大,导致全表扫描需要读更多的磁盘。而理想情况应该是这个流程

1)       遍历title索引,从中读取和过滤所有title中匹配like条件的id

2)       用id到聚簇索引中读数据。

在单行很大,而like能够过滤掉比较多语句的情况下,上面的流程肯定比全表扫描更快,也更省资源。

 

FORCE INDEX行不行?

         第一个反应是用force index。

mysql> explain select * from tb force index(title) where title like ‘%abcd%’; 

+—-+————-+——-+——+—————+——+———+——+——+————-+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+—-+————-+——-+——+—————+——+———+——+——+————-+

|  1 | SIMPLE      | tb    | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |

+—-+————-+——-+——+—————+——+———+——+——+————-+

1 row in set (0.00 sec)

 

 

显然不行。原因是通常情况下,force index只能从possible_keys中强制选择某一个索引,但是这个查询的possible_keys是NULL, force index 无效。

 

覆盖索引

我们想到覆盖索引,试验这个语句。

mysql> explain select id from tb  where title like ‘%abcd%’; 

+—-+————-+——-+——-+—————+——-+———+——+——+————————–+

| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra                    |

+—-+————-+——-+——-+—————+——-+———+——+——+————————–+

|  1 | SIMPLE      | tb    | index | NULL          | title | 386     | NULL |    1 | Using where; Using index |

+—-+————-+——-+——-+—————+——-+———+——+——+————————–+

1 row in set (0.00 sec)

 

我们看到这个语句用上了title索引,而且Using index表明用上了覆盖索引。

 

有同学可能会疑惑,这里possible_keys是NULL, 为什么key用上了title,应了那句“nothing imposible”?

 

实际上在MySQL优化器里面专门加了这一段,在type= JT_ALL时,会特别扫一下所有能够满足的覆盖索引,并找长度最短的那个。

这么做的考虑就是基于选择小的索引,减少读盘。重要的是,这个优化对于现有的引擎是通用的。

 

因此上面说的“通常情况下”的例外就是:force index可以强制使用覆盖索引。比如常见的 select count(*) from tb. 这时候你force index所有已存在的索引都是可以生效的。

 

权宜之计

了解了覆盖索引的效果,我们可以把查询改写为如下,以满足我们最开始希望的执行流程。

mysql> explain Select * from (select id from tb where title like ‘%a’) t1 join tb  using (id); 

+—-+————-+————-+——–+—————+————+———+——-+——+————————–+

| id | select_type | table       | type   | possible_keys | key        | key_len | ref   | rows | Extra                    |

+—-+————-+————-+——–+—————+————+———+——-+——+————————–+

|  1 | PRIMARY     | <derived2>  | system | NULL          | NULL       | NULL    | NULL  |    1 |                          |

|  1 | PRIMARY     | tb | const  | PRIMARY       | PRIMARY    | 4       | const |    1 |                          |

|  2 | DERIVED     | tb | index  | NULL          | idx_userid | 386     | NULL  |    1 | Using where; Using index |

+—-+————-+————-+——–+—————+————+———+——-+——+————————–+

3 rows in set (0.00 sec)

 

 

从explain结果中看执行流程是按照我们之前描述的那样,但是引入了JOIN。

 

 补充说明

JOIN写法还会引入primary key查询的时候是随机查询,因此最终的效率受like的过滤效果影响。

 

这个改写对性能的提升效果取决于要使用的索引与总数据量的大小比较,需要作应用测试。

 

zz from: http://dinglin.iteye.com/blog/1687358

关于InnoDB索引长度限制的tips

有同学问到InnoDB的索引长度问题,简单说几个tips。

 

关于3072

大家经常碰到InnoDB单列索引长度不能超过767bytes,实际上联合索引还有一个限制是3072。

 

mysql> CREATE TABLE `tb` (
    ->   `a` varchar(255) DEFAULT NULL,
    ->   `b` varchar(255) DEFAULT NULL,
    ->   `c` varchar(255) DEFAULT NULL,
    ->   `d` varchar(255) DEFAULT NULL,
    ->   `e` varchar(255) DEFAULT NULL,
    ->   KEY `a` (`a`,`b`,`c`,`d`,`e`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

可以看到,由于每个字段占用255*3, 因此这个索引的大小是3825>3072,报错。

 

为什么3072

我们知道InnoDB一个page的默认大小是16k。由于是Btree组织,要求叶子节点上一个page至少要包含两条记录(否则就退化链表了)。

所以一个记录最多不能超过8k。
又由于InnoDB的聚簇索引结构,一个二级索引要包含主键索引,因此每个单个索引不能超过4k (极端情况,pk和某个二级索引都达到这个限制)。
由于需要预留和辅助空间,扣掉后不能超过3500,取个“整数”就是(1024*3)。

 

单列索引限制

上面有提到单列索引限制767,起因是256×3-1。这个3是字符最大占用空间(utf8)。但是在5.5以后,开始支持4个字节的uutf8。255×4>767, 于是增加了一个参数叫做 innodb_large_prefix。

这个参数默认值是OFF。当改为ON时,允许列索引最大达到3072。

如下效果(5.5):

 

可以看到默认行为是建表成功,报一个warning,并且将长度阶段为255。

 

注意要生效需要加row_format=compressed或者dynamic  。

zz from: http://www.phpben.com/?post=70

要深入研究mysql那首先对mysql的一些系统/扩展变量有一定的了解,因为这些变量不仅决定mysql一些配置信息,还影响了mysql的性能优化提升,其中包括安全、优化、并发、复制等等。

笔者上网查了一下,这些资料有限,以及官网的一个中文文档介绍的内容简短(不包括值域,作用域,有些变量压根没翻译只是给出值)所以想写篇文章,一来学习巩固一下mysql,二来方便以后查阅。其中内容笔者前后用了14天,尽量查阅大量资料(问人,网上查阅,自己测试)以确保尽可能正确,且有些直接从官网英文文档翻译过来,但难免会出现因为知识结构不全面而有什么纰漏。

PS

一、想知道有那些变量(系统变量、状态变量、集群变量、日志变量…),在mysqladmin 中输入:“mysqladmin –u 用户 –p 密码 variable” 或者在mysql命令端用“show variables”显示

二、以下所有测试的环境:win7、mysql 5.1.49-community-log

三、以下变量中的作用域有全局、会话,值域表示变量值的范围(这些是中文文档锁没有的)

1 log_slow_queries                        | OFF/ON

慢查询记录日志,慢查询是指查询时间超过设定时间(如下面设为2秒)的查询,(还有个指标是第29点的min_examined_row_limit)这个可以记录那些查询语句比较慢,然后通过分析语句而优化数据库或查询语句。具体配置在my.ini加入:

log_show_queries = “日志路径/文件名”   #保存日志的路径和文件名,确保权限可写

long_query_time = 2                   #超过多少秒则保存查询数据

log-queries-not-using-indexs             #不使用使用索引

PS加上代码后重启mysql后log_slow_queries=ON状态(默认OFF)

测试:select  * from zd_ask;

结果:在指定的文件里面记录如下

C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld, Version: 5.1.49-community-log (MySQL Community Server (GPL)). started with:

TCP Port: 3306, Named Pipe: (null)

Time                 Id Command    Argument

# Time: 120425 20:40:49

# User@Host: root[root] @ localhost [127.0.0.1]

# Query_time: 0.452026  Lock_time: 0.187010 Rows_sent: 12408  Rows_examined: 12408

use bus7zd;

SET timestamp=1335357649;

select * from zd_ask;

说明:超过时间的查询语句:select * from zd_ask; 查到的结果数:12408 时间:0.452026 其他就是环境信息。

作用域:全局

————————————————-

2log_warnings                            | 1

默认值为1,表示在错误日志当中添加更多日志,日至格式:

120426 11:55:09 [Worning]内容,上网查了一下,国内这个变量的资料少得可怜,查看官网英文文档,会把一些断开链接的错误写进错误日志里面。

可以在配置文件my.ini 加入skip-log-warnings=1来停止log_warning 的使用, skip-log-warnings=1后在mysqladmin 里面用”mysqladmin –u 用户名 –p 密码 variables”查看,会发现log_warning的值是0而不是默认的1.

作用域:全局

————————————————–

3long_query_time                        | 10.000000

这是和log_slow_queries一起使用的,它是设置慢查询时间,若值是0.2,则查询大于0.2秒的定为慢查询。如果启用了慢查询日志,则会把慢查询的信息写如慢查询日志文件中。具体可以查看

作用域:全局  、会话

本文第一点。慢查询:http://www.phpben.com/?post=67

————————————————-

4 low_priority_updates                    | OFF

这个变量是降低mysql写数据的权限的,mysql默认情况下写操作权限高于读操作。附加一些知识:在mysql MYISAM表中读写是串行,即是select时锁表,insert等待释放再,反之一样。然后为了减少锁存和锁读的频率,则引入了concurrent_insert这个变量,使读写能并行操作(具体根据concurrent_insert值还有mysql版本而定)

作用域:全局  、会话

在mysql5.06版本之后,concurrent_insert=0则读(select)的时候不能执行写(insert)concurrent_insert=1则select时写操作把数据写在文件,concurrent_insert=2和1差不多,不同的是:1在数据没内存碎片(洞)才能写在文件尾,否则还是写在洞里;而2则是在select时并发写入文件尾,当select释放读锁时,数据写入洞里面。—推荐用current_insert=2

官网描述:

Value

Description

0

Disables concurrent inserts

1

(Default) Enables concurrent insert for MyISAM tables that do not have holes

2

Enables concurrent inserts for all MyISAM tables, even those that have holes. For a table with a hole, new rows are inserted at the end of the table if it is in use by another thread. Otherwise, MySQL acquires a normal write lock and inserts the row into the hole.

回正题:

low_priority_updates=1则是比上述方法更极端,直接给读操作优先与写操作。(不推荐)

———————————————————-

5lower_case_file_system                  | ON

这是控制mysql数据库文件名在文件系统中是否对大小写敏感,默认是ON对大小写不敏感,OFF的话就是对大小写敏感。

作用域:全局

———————————————————-

6lower_case_table_names                 | 1

此变量是设置数据库名或表别名是否大小写敏感。

作用域:全局

0:存储时按照指定的表名,比较时对大小写敏感。

1:存储时按照小写(不管表是否有大写),比较时对大小写不敏感。

2:存储时按给定的表名,比较时用小写。

对于windows和Mac OX S 这些文件系统对大小写敏感的系统一般不设0,windows默认设置为1,Mac OX S设置为2。

————————————————————

7max_allowed_packet                      | 1048576

8net_buffer_length                       | 16384

先介绍net_buffer_length,它是每个客户端线程的连接缓存区和结果缓冲区都是通过net_buffer_length来初始化,net_buffer_length默认初始值是16384(16K),但其值最大可以达到max_allowed_packet设定值,max_allowed_packe默认值是1M,最大达到1073741824(1G),其值必须是1024的倍数,否则回落到最靠近1024倍数值(如1025则值是1024),在内存允许的情况下,max_allowed_packe越大越好。

若max_allowed_packe设置的小,当增改(insert/update/load data infile…)数据库时,若出现大字符串或blob类型列且大小大于max_allowed_packe值则会出现以下问题。

My.ini加入配置

net_buffer_length=1024  #默认是16384,这里改小为了测试

max_allowed_packet=1025#默认是1M,这里虽然是1025而事实上值是1024

笔者更新表某列(值大于1024)则出现:

ERROR 1153 (08S01): Got a packet bigger than ‘max_allowed_packet’ bytes

注意:在version()<5.0.84 net_buffer_length可以设置但没有效,version()=5.0.84是只读

————————————————————

9 max_binlog_cache_size                   | 4294963200

这是设置最大二进制日志的缓存区大小的变量。若处理多语句事务时需要的内存大小比设置值大的话就会提示一个error:Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage 。

这个变量最小值是4096(4K),最大值,在32位的系统中是4G,64位的是16P。

作用域:全局

在mysql5.0中,max_binlog_cache_size一修改则所有会话都受影响(可能之前的受延迟,笔者没查证过)

————————————————————–

10 max_binlog_size                         | 1073741824

这是设置每个二进制日志文件内容大小的变量,如果当前文件的数据量大于max_binlog_size的值时,则会关闭此文件,新建下个文件写入数据。

但是,当处理多语句事务(大事务)时,会出现文件数据大小比此值设置值大的情况。这是因为事务的二进制日志是块存储的,也就是说不会分割出来存放在两个日志文件中。

作用域:全局

——————————————————————

11 max_connect_errors                      | 10

设置某客户端链接mysql服务器失败次数,若次数超过此值,则锁定禁止该客户端链接服务器且提示错误,若在未超过此值有一次成功链接,则计数器会清零。

这是一个与性能无关的变量,而是安全方面考虑的,主要防止穷举法破解数据库用户和密码。

作用域:全局

默认值是10,32位系统1~ 4294967295,64位的是1~18446744073709547520

官方说:一旦锁定,要解锁只有方法:在mysql客户端flush hosts ;在mysqladmin中用mysqladmin flush-host

笔者在本机测试过:这个功能用不了 。

——————————————————————

12 max_connections                         | 100

这个设置数据库并发可连接的数量

作用域:全局

值域:

Version()<=5.1.14 :默认100

Version()>=5.1.15 :默认151  1~16384

Version()>=5.1.17 :默认151  1~100000

这是网站成长必要修改的一个变量,允许多少人在网站上并发操作。

——————————————————————

13max_delayed_threads                    | 20

延迟操作Delay_insert最大线程数

作用域:全局  、会话

默认20 值域:0~16384

—————————————————————–

14 max_error_count                         | 64

Show warning 或show error 显示warning或Error显示的最大个数,默认是64,值域0~65535.此值不能改太小,否者若错误提示个数比此值多的话不方便调试。

作用域:全局  、会话

注意:此值不要调太小;对于已经运行系统,可设为0,不会提示错误。

—————————————————————–

15max_heap_table_size                     | 16777216

内存表最大行数。

作用域:全局  、会话

值域:32位操作系统16384~4294967295  64位操作系统16384 ~ 1844674407370954752

默认值都是16777216

更新设置此值对已存在的内存表没影响(重启mysql服务器就有),对create/update/truncate语句有影响。

——————————————————————-

16max_insert_delayed_threads              | 20

是max_delayed_threads的别名,看第13点。

作用域:全局  、会话

———————————————————————–

17 max_join_size                           | 18446744073709551615

18 sql_big_selects                        | ON

max_join_size和sql_big_selects

作用域:全局  、会话

sql_big_selects默认值是1,表示所有select查询都执行(不管时间长短)。

sql_big_selects=0/OFF时,mysql先估算单表查询结果行数或多表查询组合行数的大小,若比max_join_size大时,就会放弃该查询语句。

max_join_size默认是4294967295,值域:1~4294967295

注意:sql_big_selects=0/OF的情况下,max_join_size不齐作用,且当max_join_size设置为非默认值时,sql_big_selects会被重置为0.

————————————————————————

19 max_length_for_sort_data                | 1024 ====

确定使用的filesort算法的索引值大小的限值。

作用域:全局  、会话

=========以下引用网上内容这是地址:http://www.itpub.net/thread-1417429-1-1.html

mysql的filesort算法有两种:

一种是最初的算法,在MySQL 4.1以前只有这种算法,一种是改进的filesort算法,它出现在MySQL 4.1以后(blob和text类型的字段不能采用这种改进算法)

 

“最初的算法”流程如下:

1.读取所有的满足条件的数据,只包含sort key和row pointer两种数据

2.在buffer中执行qsort排序

3.排完序后,再根据row pointer去读取相应的行数据

从中可以看出,每次排序都需要读两次表,而根据row pointer去读表往往都是随机离散读的,所有其开销非常大。

 

改进后的filesort算法是:

1.读取所需要的数据,包含sort key,row pointer和查询所需要访问的字段

2.根据sort key排序

3.按排序后的顺序读取数据,由于sort_buffer_size中包含了所需要的字段,因此不需要再回表了,可以直接返回结果给客户端。

很明显,这种改进的方法对sort_buffer_size的需求也大大增加.

 

所以为了防止性能下降,mysql增加了一个参数max_length_for_sort_data,当第一步中除了sort key以外的字段内容大于max_length_for_sort_data这个参数时,mysql将采用第一种排序算法。

——————————————————————–

20max_prepared_stmt_count                 | 16382

该变量设置预处理语句限制数。这个功能能防止拒绝服务攻击,因为攻击可以通过大量的预处理语句致使服务器内存溢出来攻击。设置此变量就是一道安全屏障,此变量对原有的预处理语句不影响,但若是预处理语句的数量超过该变量的值,则不会新增预处理语句,而是等到预处理语句的值小于max_prepared_stmt_count的值才增加新语句。

作用域:全局

默认值:16382 值域:0~1048576

若设为0则表示不允许预处理语句。

注意:该变量是version()>5.0.21才有

—————————————————————–

21max_relay_log_size                     | 0

中继日志大小,和第10点的max_binlog_size类同,只不过中继日志是保存从服务器的日志,是从主服务器复制过来的二进制日志。

当max_relay_log_size =0则max_relay_log_size=max_binlog_size

当max_relay_log_size >0则中继日志大小是max_relay_log_size设定的值

作用域:全局

默认值:0 值域:0~1073741824

—————————————————————–

22max_seeks_for_key                      | 4294967295

此变量假定索引搜索行数最大值。也就是说这个值是索引搜索最大值。查询优化器会忽略索引基数(用show index from table 可看到基数cardinality),它假定扫描索引匹配的行数不会超过max_seeks_for_key设定值。

官网:可以通过改小这个值来强制mysql使用索引来代替全表扫描。

作用域:全局  、会话

在32位系统:默认4294967295,值域:1~ 4294967295

在64位系统:默认18446744073709547520,值域1~18446744073709547520

——————————————————————–

23 max_sort_length                         | 1024

当排序BLOB或者TEXT类型列数据时用的字节数。当数据长度> max_sort_length设定值,排序就用max_sort_length的长度来排序,后面的数据被忽略。

作用域:全局  、会话

默认是1024 值域:4~ 8388608

———————————————————————-

25max_sp_recursion_depth                  | 0

设定存储过程(sp=>save procedures)最大递归数。

作用域:全局  、会话

默认0,禁止存储过程递归,最大值255

PS此变量在version()=5.0.17引入

———————————————————————-

26、 max_tmp_tables                          | 32

设定客户端同时能打开临时表个数的最大值

作用域:全局  、会话

32位系统:默认32 值域:1~4294967295

64位系统:默认32 值域:1~18446744073709547520

PS: 官网:This variable does not yet do anything(该变量还没生效)

————————————————————

27max_user_connections                   | 0

Mysql每个用户能同时链接服务器最大值。0表示没有限制

默认值:0 值域:1~ 4294967295

作用域:全局  、会话

———————————————————————-

28max_write_lock_count                    | 4294967295

设定数据表写锁定最大数,注意:这是对同一个表来说的。

作用域:全局

值域:

32位系统:默认4294967295 值域:1~ 4294967295

64位系统:默认18446744073709547520 值域:1~18446744073709547520

网上有人说:当对同一个表锁定个数超过设定值的时候,服务器会释放读锁定。

笔者有些怀疑,做了个测试:

max_write_lock_count =4294967295

开启三个客户端,第一个锁定写,第二个select读取,第三个有加一个锁定,顺序1、2、3

当笔者开第一个锁定时,读客户端还在等待。

max_write_lock_count =2

第一锁定表,第二selcet语句,第三锁定表

1、  解开第一个锁,select这边没反应

2、  再加一个锁定(三个,解开一个还有两个),selcct这边还是没反应

3、  加一个客户端insert数据,即时3锁1插入1查找,当解开一个锁时,selcet和插入都有了反应且正常运行。

笔者结论是

应该是:当写锁定超过限制且有其他客户端写操作时,部分读操作被释放。

——————————————————————

29min_examined_row_limit                 | 0

这也是判断一个查询是否是慢查询的一个变量,参考第1点的log_slow_queries

若查询的结果集行数大于min_examined_row_limit值,则查询被当作慢查询写入慢查询日志。

作用域:全局  、会话

值域:

32位系统:默认4294967295值域:1~ 4294967295

64位系统:默认18446744073709547520 值域:1~18446744073709547520

——————————————————————-

30multi_range_count                      | 256

veriosn=5.03加入该变量

设定查询语句中range范围最大个数,如“where id>100 and userId<50”是两个范围。

默认是256

作用域:全局  、会话

值域:1~ 4294967295

一般不改此参数。

———————————————————————

31 myisam_data_pointer_size                | 6

默认指针大小,单位是字节, MAX_ROWS不指定时,CREATE TABLE使用该变量创建MyISAM表。默认值是6。值域:2~7

作用域:全局

——————————————————————–

32 myisam_max_sort_file_size               | 107374182400

当用到REPAIR TABLE, ALTER TABLE, LOAD DATA INFILE的时候,相应的索引会被重建,然后要用到临时文件,此变量就是设置临时文件大小的。如果索引文件大小比此值小,系统则调用速度更慢的键值创建索引。

作用域:全局

默认是2G

官网建议:如果MyISAM索引文件大于2G且硬盘空间允许,增大该值可以提高性能。注意的是增加的空间是包含原来索引文件空间的。

———————————————————————–

33 myisam_mmap_size                        | 4294967295

Mmp:memory mapping 内存映射

设置使用内存映射压缩MyISAM表文件的最大内存量的变量。

如果许多压缩MyISAM表使用,可以通过降低该值来减低内存交换问题出现的可能性。

作用域:全局

值域:

32位系统:默认4294967295值域:1~ 4294967295

64位系统:默认18446744073709547520 值域:1~18446744073709547520

——————————————————————-

34myisam_recover_options                  | OFF

设置MyISAM存储模式,它的值可以是OFF, DEFAULT, BACKUP, FORCE, QUICK任意组合,组合值则用‘,’隔开。

默认是OFF。

若非OFF的话,表示每次打开MyISAM表都检查表是否崩溃或者非正常保存,否则和尝试修复表。

修复按照值选项而定:

OFF:关闭

DEFAULT:修复中没有BACKUP, FORCE, QUICK

BACKUP:如果修复过程中用户修改数据,则把“表名.MYD”文件备份成“表名-时间.BAK”文件

FORCE:继续修复数据即便是丢失大量数据

QUICK:不检查表中的行,如果没有任何删除块。

还有的是,修复之前,mysql会在错误日志中写入note,这个note关于修复的。

官网建议:如果想在不受用户打扰的情况下修复数据,则设置为“BACKUP, FORCE”。这样的话,就强制修复即便有人删除数据,修复完了还可以用备份数据查看发生情况。

作用域:全局

———————————————————————-

35 myisam_repair_threads                   | 1

myisam_repair_threads =1 则在repair by sort时,MyISAM表索引在各自线程中并发创建。

作用域:全局|会话

值域:

32位系统:默认1 值域:1~ 4294967295

64位系统:默认1 值域:1~18446744073709547520

————————————————————————

36 myisam_sort_buffer_size                 | 31457280

REPAIR TABLE或则 CREATE INDEX 和 ALTER TABLE创建索引的时候,.给分配的缓冲区的大小。

作用域:全局|会话

值域:

32位系统:默认8388608 值域:4~ 4294967295

64位系统:默认8388608  值域:4~18446744073709547520

————————————————————————

37myisam_stats_method                     | nulls_unequal

该变量告诉服务器,在服务器收集有关MyISAM表的索引值的分布统计时如何处理NULL值。变量可选三个值:nulls_equal, nulls_unequal, nulls_ignored.

Nulls_equal 时,所有null值都被当成一样,形成一个大小是null值个数的单值组。

nulls_unequal时,null被认为是不一样的(尽管我们看来是一样),每个null形成一个大小为1的不同值组。

nulls_ignored时,值被忽略

PS至于为什么要这样区别对待,这里有讲解http://www.phpben.com/?post=69

———————————————————————–

38myisam_use_mmap                        | OFF

这个变量开启的话,就利用内存映射来读和写myisam表,默认是关闭的OFF,一般内存服饰很大的情况下是不会开启这一项

作用域:全局

—————————————————————————

39named_pipe                              | OFF

该变量指示mysql服务器是否支持管道连接。

只使用windows系统

作用域:全局

—————————————————————————

40 net_read_timeout                        | 30

设置服务器放弃读前多少秒以等到更多的连接数据,当服务器从客户端读取,net_read_timeout是设施多少秒后终止读,而类是的变量net_write_timeout设置终止写。也就是mysql为了保证连接不被浪费在无尽的等待中,mysql 通过net_read_timeout、net_write_timeout来主动终止连接。

作用域:全局、会话

值域:默认30 最小是1

—————————————————————————-

41net_write_timeout                       | 60

看上。

作用域:全局、会话

值域:默认60 最小是1

—————————————————————————-

42net_retry_count                         | 10

如果读或写一个通信端口中断,mysql放弃前尝试连接的次数。在FreeBSD系统中此值应设置很高,因为FreeBSD内部中断被发送到所有线程去。

作用域:全局|会话

值域:

32位系统:默认10 值域:1~ 4294967295

64位系统:默认10  值域:1~18446744073709547520

—————————————————————————–

43 new                                     | OFF

该变量用于mysql4.0启用mysql4.1一些新功能,和为了保持向后兼容性。在mysql5.6中,这个变量的值都是OFF。

作用域:全局|会话

默认是False

—————————————————————————–

44 old                                     | OFF

Old是兼容性变量。默认被禁用,在旧版本的服务器可以通过重启服务器时启用。

目前,old被启用时,它改变使用索引提示的默认范围到MySQL5.1.17之前。也就是说,没有FOR子句仅适用于如何使用索引检索行,而不是解决ORDER BY或GROUP BY子句的索引提示。在复制设置启用这个变量时要注意些,基于语句的二进制日志,主从服务器有不同的模式可能会导致复制错误。

作用域:全局

默认是:OFF

——————————————————————————-

45old_alter_table                        | OFF

当该变量被启用,则mysql服务器不会使用alert table 优化方法。在mysql5.0和更早版本,先用个临时表,复制数据进去,然后重命名到原始的临时表。

作用域:全局

默认值:OFF

———————————————————————————-

46old_passwords                          | OFF

该变量设置是否启用mysql4.1之前的账户密码类型。

作用域:全局

默认值:OFF

———————————————————————————

47 open_files_limit                        | 622

设置操作系统允许mysqld允许打开的文件数。这是系统允许的真正值,可能和你用–open-files-limit选项给mysqld或mysqld_safe设置的值不同。该值是0的系统上,MySQL不能更改打开的文件数

作用域:全局

默认值:0 值域:0-65535

——————————————————————————

48optimizer_prune_level                  | 1

该变量为1,控制启发式应用在查询优化过程中,从优化空间中剪去不太有用的部分计划。

值0禁用启发式优化执行穷举搜索。

值1时让优化器根据被检索的行数通过中间计划来剪去不太有用的计划。

作用域:全局、会话

默认值:1

值域是:0或1

———————————————————————————

49 optimizer_search_depth                  | 62

查询优化器进行的搜索的最大深度。如果值大于查询中的关系数则查询方案比较佳,但生成查询执行方案需要的时间更长。值大于查询中的关系数则返回的执行方案更快,但方案远没有优化。如果设置为0, 系统自动选择合理的值。

作用域:全局、会话

默认值:62

值域是:0-62

ps:上述的关系数即时join连结中的表数。

如果optimizer_search_depth设置过大,那么join时,获取最优执行计划的代价十分巨大。

optimizer_search_depth = join tables的数量,一定能获得最优执行计划(根据mysql的代价估计模型),但是计算代价大。

optimizer_search_depth < join tables的数量,获取的执行计划,是局部最优,但是计算代价小。

optimizer_search_depth参数,对于单表查询无意义。

———————————————————————————-

50 optimizer_switch                        | index_merge=on,index_merge_union=on,

index_merge_sort_union=on,index_merge_intersection=on

优化器选项,有很多选项。

batched_key_access Controls use of BKA join algorithm
block_nested_loop Controls use of BNL join algorithm
engine_condition_pushdown Controls engine condition pushdown
index_condition_pushdown Controls index condition pushdown
index_merge Controls all Index Merge optimizations
index_merge_intersection Controls the Index Merge Intersection Access optimization
index_merge_sort_union Controls the Index Merge Sort-Union Access optimization
index_merge_union Controls the Index Merge Union Access optimization
mrr Controls the Multi-Range Read strategy
mrr_cost_based Controls use of cost-based MRR if mrr=on
semijoin Controls all semi-join strategies
firstmatch Controls the semi-join FirstMatch strategy
loosescan Controls the semi-join LooseScan strategy (not to be confused with LooseScan for GROUP BY)
materialization Controls materialization (including semi-join materialization)

————————————————————————————–

51pid_file                               | C:\ProgramData\MySQL\MySQL Server 5.1\Data\Bsky-PC.pid

进程ID (PID)文件的路径名。可以用–pid-file选项设置该变量

作用域:全局

值域是:文件目录,字符串

————————————————————————————-

52 plugin_dir                             | C:\Program Files\MySQL\MySQL Server

5.1\lib/plugin

插件目录的路径。在MySQL 5.1.2中加入了该变量。

如果插件目录对服务器可写,则有可能给用户通过“SELECT … INTO DUMPFILE”在目录中写可执行代码。可以通过设置插件目录只读来阻止或者给目录设置–secure-file-priv select可写老保持安全 。

类型:目录名

默认是:mysql安装目录/lib/pligin

—————————————————————————————-

53 port                                    | 3306

Mysql监听tcp/ip端口号

默认3306

—————————————————————————————-

54 preload_buffer_size                     | 32768

重载索引时分配的缓冲区大小

作用域:全局、会话

默认值:32768

值域是:1024~1073741824

55 profiling                               | OFF

设置show profile 命令是否可用。

当profiling=0/OFF 则show profile不可用

当profiling=1/ON  则show profile可用

ps: show profile是显示查询一些信息。

———————————————————————————————–

56profiling_history_size                  | 15

设置show profile 命令显示多少条查询的。

默认是15,最大值是100

若此变量的值是0则profiling=0/OFF

——————————————————————————————-

57protocol_version                        | 10

MySQL服务器使用的客户端/服务器协议的版本

作用域:全局

——————————————————————————————-

58 pseudo_thread_id                        | 0

这个变量是内部服务器使用。

——————————————————————————————–

59query_alloc_block_size                 | 8192

语句解析和执行过程中创建的对象分配的内存块分配大小。如果你有内存碎片的问题,它可能有助于提高此参数。

作用域:全局,会话

值域:

32位系统:默认8192 值域:1024~ 4294967295

64位系统:默认8192 值域:1024~18446744073709547520

———————————————————————————————-

60query_cache_limit                      | 1048576

不要缓存大于该值的结果。默认值是1048576(1MB)。

作用域:全局,会话

值域:

32位系统:默认1048576 值域:0~ 4294967295

64位系统:默认1048576 值域:0~18446744073709547520

——————————————————————————————–

61 query_cache_min_res_unit                | 4096

查询缓存分配的最小块的大小(字节)。 默认值是4096(4KB)

作用域:全局

值域:

32位系统:默认4096 值域:512~ 4294967295

64位系统:默认4096 值域:512~18446744073709547520

——————————————————————————————-

62query_cache_size                        | 104857600

为缓存查询结果分配的内存的数量。默认值是0,即禁用查询缓存。

请注意即使query_cache_type设置为0也将分配此数量的内存。

作用域:全局

值域:

32位系统:默认0 值域:0~ 4294967295

64位系统:默认0 值域:0~18446744073709547520

——————————————————————————————–

63query_cache_type                        | ON

设置查询缓存类型。设置GLOBAL值可以设置后面的所有客户端连接的类型。客户端可以设置SESSION值以影响他们自己对查询缓存的使用。下面的表显示了可能的值。

0或OFF

不要缓存或查询结果。请注意这样不会取消分配的查询缓存区。要想取消,你应将query_cache_size设置为0。

1或ON

缓存除了以SELECT SQL_NO_CACHE开头的所有查询结果。

2或DEMAND

只缓存以SELECT SQL_NO_CACHE开头的查询结果。

—————————————————————————————-

64query_cache_wlock_invalidate           | OFF

一般情况,当客户端对MyISAM表进行WRITE锁定时,如果查询结果位于查询缓存中,则其它客户端未被锁定,可以对该表进行查询。将该变量设置为1,则可以对表进行WRITE锁定,使查询缓存内所有对该表进行的查询变得非法。这样当锁定生效时,可以强制其它试图访问表的客户端来等待。

作用域:全局

值域:

默认FALSE

————————————————————————————-

65query_prealloc_size                     | 8192

用于查询分析和执行的固定缓冲区的大小。在查询之间该缓冲区不释放。如果你执行复杂查询,分配更大的query_prealloc_size值可以帮助提高性能,因为它可以降低查询过程中服务器分配内存的需求。

作用域:全局、会话

值域:

32位系统:默认8192 值域:8192~ 4294967295

64位系统:默认8192 值域:8192~18446744073709547520

————————————————————————————

66rand_seed1                             |

67rand_seed2                              |

这两个变量只作用域只是会话层,可以可写不可读,所以用show vriabales是显示不了变量的值。

这两个变量是为了使rand()函数支持被复制。在含有rand()查询中,主服务器必须给从服务器传递两个值,它们用于种子随机数发生器。从服务器使用这些值来设置会话变量rand_seed1和rand_seed2 以至从服务器产生与主服务器相同的值。

————————————————————————————–

68range_alloc_block_size                 | 4096

该变量是设置范围优化时分配的块的大小。

作用域:全局、会话

默认8192

值域:8192~ 4294967295

————————————————————————————-

69 read_buffer_size                        | 2097152

每个线程连续扫描时为扫描的每个表分配的缓冲区的大小(字节)。如果进行多次连续扫描,可能需要增加该值, 默认值为131072。该值应是4K的倍数,如果不是4k的倍数,则回滚到最接近4k倍数的值。

值域:4K~2G

—————————————————————————————

70read_only                               | OFF

当变量对复制从服务器设置为ON时,从服务器不允许更新,除非通过从服务器的线程或用户拥有SUPER权限。可以确保从服务器不接受客户端的更新命令。

READ_ONLY不适用临时表,也防止服务器日志表插入行,这个变量并不妨碍分析TABLE或OPTIMIZE TABLE命令语句的使用,因为其目的是为了防止表的结构或内容的变化。

READ_ONLY只存在一个全局变量,所以需要SUPER权限才能改变其值。在主服务器上的设置为READ_ONLY不会被复制到从服务器。可以在主从服务器上分开、独立的设置READ_ONLY的值。

作用域:全局

默认是:false

——————————————————————————————

71 read_rnd_buffer_size                    | 262144

当排序后按排序后的顺序读取行时,则通过该缓冲区读取行,避免搜索硬盘。将该变量设置为较大的值可以大大改进ORDER BY的性能。但是,这是为每个客户端分配的缓冲区,因此你不应将全局变量设置为较大的值。相反,只为需要运行大查询的客户端更改会话变量。

作用域:全局、会话

默认262144

值域:8200~ 4294967295

——————————————————————————————

72relay_log_index                         |

使用的中继日志索引文件的名称。默认名称是HOST_NAM_relay_bin.index数据目录中,其中host_name是从服务器的名称。

如果指定此选项,指定的值也被用作中继日志的主档名。

作用域:全局、会话

值:文件名

ps笔者这里没有中继日志,所以为空。

——————————————————————————————

73relay_log_info_file                    | relay-log.info

这是从服务器保存中继日志有关信息的文件名。

默认是relay-log.info

——————————————————————————————

74 relay_log_purge                         | ON

当不再需要中继日志时禁用或启用自动清空中继日志。默认值是1(启用)。

这是一个全局变量且可以通过SET GLOBAL relay_log_purge = N.来动态改变。

——————————————————————————————

75 relay_log_space_limit                   | 0

所有中继日志空间大小。

作用域:全局

值域:

32位系统:默认0 值域:0~ 4294967295

64位系统:默认0 值域:0~18446744073709547520

——————————————————————————————

76 report_host                             |

— report_host 选项的值

作用域:全局

字符串类型值

——————————————————————————————

77 report_password                         |

— report_password 选项的值 ,与MySQL的复制用户帐户使用的密码不相同。

作用域:全局

字符串类型值

——————————————————————————————

78 report_port                             | 3306

— report_port 选项的值

作用域:全局

默认是0

Ps:version()>5.6.5

——————————————————————————————

79 report_user                             |

— report_user 选项的值 ,与MySQL的复制用户帐户使用的账户不相同。

作用域:全局

——————————————————————————————

80 rpl_recovery_rank                       | 0

——————————————————————————————

81 secure_auth                             | OFF

如果用–secure-auth选项启动了MySQL服务器,它将阻塞有旧格式(4.1之前)密码的所有账户所发起的连接。在这种情况下,该变量的值为ON,否则为OFF。

 

如果你想要防止使用旧格式的密码(致使网络通信不安全),你应启用该选项。

Version()<=5.6.4默认是OFF

Version()>=5.6.5 默认是ON

——————————————————————————————

82 secure_file_priv                        |

该变量默认为空,若给该变量赋值(目录),则限制函数load_file(),load data,和select … into outfile 语句只有在相应目录下起作用。

作用域:全局

默认为空

——————————————————————————————

83server_id                              | 0

–server-id选项的值。用于主复制服务器和从复制服务器。

默认值:0

值域:0~ 4294967295

——————————————————————————————

84 shared_memory                           | OFF

(只用于Windows)服务器是否允许共享内存连接。

作用域:全局

——————————————————————————————

85shared_memory_base_name                 | MYSQL

(只用于Windows)说明服务器是否允许共享内存连接,并为共享内存设置识别符。当在单台机器上运行多个MySQL实例时很有用。

作用域:全局

——————————————————————————————

86skip_external_locking                  | ON

如果mysqld使用外部锁定,该值为OFF,否则是ON。这个在MYISAM表中有作用

——————————————————————————————

87skip_name_resolve                       | OFF

这是变量是通过选项—skip-name-resolve设置。

若是ON,在检查客户端连接时,mysqld会解析主机名。

若是OFF,mysqld使用唯一的IP号码和所有在授权表的Host列值必须是IP地址或localhost

——————————————————————————————

88skip_networking                        | OFF

如果服务器只允许本地(非TCP/IP)连接,该值为ON。在Unix中,本地连接使用Unix套接字文件。在Windows中,本地连接使用命名管道或共享内存。在NetWare中,只支持TCP/IP连接,因此不要将该变量设置为ON。

——————————————————————————————

89 skip_show_database                      | OFF

防止不具有SHOW DATABASES权限的人们使用SHOW DATABASES语句。如果你担心用户能够看见属于其它用户的数据库,这样设置可以提高安全性。其效果取决于SHOW DATABASES权限:如果变量值为ON,只允许具有SHOW DATABASES权限的人们使用SHOW DATABASES 语句,并且该语句将显示所有数据库名。如果值为OFF,允许所有用户执行SHOW DATABASES,但只显示用户具有SHOW DATABASES或其它权限的数据库的名称。

ps:这个对mysql服务器安全性能提升很有用

——————————————————————————————

90 slave_compressed_protocol               | OFF

如果ON,则在主、从服务器均支持,使用从/主压缩协议。

默认是OFF

——————————————————————————————

91 slave_exec_mode                         | STRICT

控制在复制的冲突解决和错误检查中是使用IDEMPOTENT模式还是STRICT模式。

IDEMPOTENT模式会发生抑制重复键和no-key-found错误,这种模式下,应采用多主复制,循环复制,和其他一些特殊的复制场景。

是默认模式,并适用于其他大多数情况下。

——————————————————————————————

92slave_load_tmpdir                      | C:\Windows\TEMP

这是从服务器复制load data infile语句时在哪里生成临时文件的目录。

默认是是系统的tmp文件夹。

——————————————————————————————

93slave_net_timeout                      | 3600

放弃读操作前等待主/从连接的更多数据的等待秒数。

作用域:全局

默认3600

最小值1

——————————————————————————————

94、 slave_skip_errors                       | OFF

正常的来说,当出现错误的时候,复制就会停止。

该变量为ON的时候,则告诉服务器复制不管在任何错误提示的情况下都继续进行下去。

默认:OFF

值有以下:

[list of error codes]

all

ddl_exist_errors

——————————————————————————————

95slave_transaction_retries              | 10

如果因为InnoDB出现死锁或超过InnoDB的innodb_lock_wait_timeout设置的时间,复制从服务器SQL线程未能执行事务,在提示错误并停止前它自动重复slave_transaction_retries次。 默认值是10。

——————————————————————————————

96 slow_launch_time                        | 2

当创建线程的时间超过该秒数,服务器则增加Slow_launch_threads状态变量。

——————————————————————————————

97slow_query_log                          | OFF

98slow_query_log_file                    | C:\ProgramData\MySQL\MySQL Server 5.

1\Data\Bsky-PC-slow.log

slow_query_log是否开启慢查询日志。slow_query_log_file是慢查询日志的路径。

这个和第一点类似

——————————————————————————————

99 socket                                  | MySQL

在uninx平台上,该变量的值是用于本地客户端连接的socket文件的名字,默认是‘tmp/mysql.socket’(对于一些分布式中,比较特殊,如RPMs 中是/var/lib/mysql)

在windows,则是本地客户端命名管道连接的文件名,默认是mysql(不区分大小写)

——————————————————————————————

100 sort_buffer_size                        | 262144

每个排序线程分配的缓冲区的大小。增加该值可以加快ORDER BY或GROUP BY操作。

当用show global status输出信息后,看到很多Sort_merge_passes输出,则要增加sort_buffer_size 的值来提高order by 、group by 的性能,这种情况通过优化器优化和索引是不起作用。

默认2G 最大4G

作用域:全局、会话

值域:

32位系统:默认2097144 值域:0~ 4294967295

64位系统:默认2097144 值域:0~18446744073709547520

——————————————————————————————

101 sql_auto_is_null                        | ON

如果sql_auto_is_null=1/ON,则会自动的插入给自动增长列插入值。

该值可以通过一下来获取

SELECT * FROM tbl_name WHERE auto_col IS NULL

如果有返回行数据,则插入的数值和last_insert_id()函数返回的值是一致。

默认是0

——————————————————————————————

102 sql_big_tables                          | OFF

保存所有临时值在文件中来启用大型结果集,此选项可防止最“表已满”的错误,而且还减慢查询内存中的表就足够了。自MySQL3.23.2中,服务器能够处理大的结果,小临时表使用内存和切换到磁盘表在必要时自动设置。

——————————————————————————————

103 sql_buffer_result                       | OFF

若是1,sql_buffer_result强迫用select语句返回的结果保存在临时表。这样可以提前解开表的锁定,且当给客户端发送结果集要很久的情况下很有用。

默认值0

作用域:全局,会话

——————————————————————————————

104sql_log_bin                            | ON

该变量控制语句是否写进二进制日志。

默认是开启。通过改变此变量来改变会话值,不过要有super权限

Ps:在version 5.6中没交易或子查询的情况下不可以通过set @@session.sql_log_bin来设置

——————————————————————————————

105sql_log_off                             | OFF

该变量控制是否写进常规日志。默认是OFF表示写,通过改变此变量来改变会话值,不过要有super权限

——————————————————————————————

106 sql_log_update                          | ON

是否开启更新日志。详情可以看http://www.phpben.com/?post=67

作用域:全局

——————————————————————————————

107 sql_low_priority_updates                | OFF

该控制控制读写优先级。

若值为1/ON,则读优先于写。即是所有INSERT, UPDATE, DELETE和 LOCK TABLE WRITE 语句都要等没有 SELECT或LOCK TABLE READ作用于表上

ps:一般情况下写优先于读

——————————————————————————————

108sql_max_join_size                       | 18446744073709551615

不允许可能需要检查多于max_join_size行(为单个表语句)或行组合(为多个表语句)或可能执行大于max_join_size次硬盘查询的SELECT语句。通过设置该值,你可以捕获键使用不正确并可能花很长时间的SELECT语句。如果用户想要执行没有WHERE子句的花较长时间或返回数百万行的联接,则设置它。

将该变量设置为DEFAULT之外的值,将SQL_BIG_SELECTS的值重设为0。如果你重新设置SQL_BIG_SELECTS值,sql_max_join_size变量被忽略。

如果查询结果位于查询缓存中,则不检查结果大小,因为前面已经计算了结果,不会要求服务器将它发送给客户端。

ps: max_join_size等同于sql_max_join_size

——————————————————————————————

109sql_mode       | STRICT_TRANS_TABLES,NO_AUTO_CREATE_U

SER,NO_ENGINE_SUBSTITUTION

——————————————————————————————

110 sql_notes                               | ON

若是1/on,warning_count数会增加且服务器会记录警告内容,否者不会。

mysqldump输出内容到这个变量设置为0,使重载转储文件不会产生警告事件不影响重载操作的完整性。

——————————————————————————————

111sql_quote_show_create                   | ON

若值为1,则会给SHOW CREATE TABLE和SHOW CREATE DATABASE添加服务器引号表示符

否则引号被禁用。

——————————————————————————————

112 sql_safe_updates                        | OFF

值若是1,则mysql则退出在where和limit字句中没关键字的update或delete语句。这有利于捕抓到键使用不当的update或delete语句。

默认值是0

——————————————————————————————

113sql_select_limit                       | 18446744073709551615

这是设置select查询语句返回数据最大行数。一个新的连接的默认值是服务器允许每个表的最大行数。如果你已经改变了极限,默认值可以通过指定一个DEFAULT值恢复。

——————————————————————————————

114sql_slave_skip_counter                 |

从服务器应跳过的从主服务器传来的事件的数量。

——————————————————————————————

115 sql_warnings                            | OFF

该变量操作单行插入数据出现warning错误时是否产生一个warning字符串信息。

默认值是0,1的话就产生信息。

——————————————————————————————

116 ssl_ca                                  |

一个受信任的SSL的CA列表中的文件路径。

作用域:全局

——————————————————————————————

117ssl_capath                             |

包含受信任的SSL PEM格式的CA证书目录的路径。

作用域:全局

——————————————————————————————

118ssl_cert                                |

为建立一个安全的连接使用的SSL证书文件的名称。

作用域:全局

——————————————————————————————

119 ssl_cipher                              |

允许使用SSL加密的密码列表。

作用域:全局

——————————————————————————————

120ssl_key                                |

为建立一个安全的连接使用的SSL密钥文件的名称。

作用域:全局

——————————————————————————————

121storage_engine                          | InnoDB

默认的存储引擎。

——————————————————————————————

122sync_binlog                             | 0

当值>0,则每一个sync_binlog写二进制日志后mysql都同步该日志到磁盘中(fddatasync()).

作用域:全局

值域:

32位系统:默认0 值域:0~ 4294967295

64位系统:默认0 值域:0~18446744073709547520

——————————————————————————————

123sync_frm                                | ON

若值为1,任何非临时表在创建其.frm文件时会同步到磁盘。这会速度变慢但数据奔溃时安全点。

作用域:全局变量。

默认是:true

——————————————————————————————

124system_time_zone                       |

服务器系统时区。当 服务器开始执行时,它继承机器默认时区设置值,可以由运行服务器的账户或在启动脚本中进行修改。该值用来设置system_time_zone。典型情况用TZ环境变量来指定时区。还可以用mysqld_safe脚本的–timez选项来指定。

作用域:全局

——————————————————————————————

125table_definition_cache                  | 256

可以存储在定义缓存的表定义的数目,如果使用大量的表,你可以创建一个大表的定义缓存,加快开放表,他表定义缓存占用较少的空间,并且不使用文件描述符,不像正常的表缓存。最低和默认值均为400。

作用域:全局

默认值:400

值域:0~ 524288

——————————————————————————————

126 table_lock_wait_timeout                 | 50

这个参数已经没用了

——————————————————————————————

127 table_open_cache                        | 256

所有线程一共能打开的表的数量,增加该值增加mysqld要求的文件描述符的数量。

可以通过检查Opened_tables状态变量来检查是否要增加该值。

如果Opened_tables状态变量很大则不需要用flush tables,而是怎么该变量的值。

作用域:全局

默认值:400

值域:400~52488

——————————————————————————————

128table_type                              | InnoDB

默认创建表时用到的引擎

——————————————————————————————

129thread_cache_size                       | 8

该变量设置多少个线程服务器重用缓存。当一个客户端断开连接的时候,该客户端的线程总数还没超过该值数时,则还是保存在缓冲区内。

如果有很多新的连接,则可以通过增加该值来提交性能。

通常情况下,这并不能提供一个显着的性能改进,除非你有一个很好的线程执行。

但是,如果服务器每秒有成千新连接的话,则需要确保该值足够大以至于新的连接的线程可以保存在缓存区内。

默认值:0

值域:0~16384

——————————————————————————————

130thread_handling                        | one-thread-per-connection

服务器用什么线程句柄来控制连接线程。

当值是no-theads,服务器使用单独的线程

当值是one-thread-per-connection,服务器用一个线程控制每一个客户连接。

当值是no-threads,在linux下debug是很有用的。

作用域:全局

——————————————————————————————

131thread_stack                           | 196608

每个线程的栈的大小。crash-me测试检测到的限制,很多都是依赖于这个值。

如果线程的堆栈大小是太小了,它限制了复杂的服务器可以处理的SQL语句,存储过程的递归深度,和其他消耗内存的行动。

作用域:全局

值域:

32位系统:默认196608 值域:131072~ 4294967295

64位系统:默认262144 值域:262144 ~18446744073709547520

——————————————————————————————

132 time_format                             | %H:%i:%s

该变量被弃用

——————————————————————————————

133time_zone                               | SYSTEM

当前的时区。初使值是’SYSTEM'(使用system_time_zone的值),但可以用–default-time-zone选项在服务器启动时显式指定。

作用域:全局、会话

——————————————————————————————

134timed_mutexes                          | OFF

——————————————————————————————

135 timestamp                               | 1335315763

时间戳

 

设置此客户端的时间。这是用来取得原始时间戳,如果您使用的二进制日志恢复行。 timestamp_value应该是一个Unix纪元时间戳,而不是一个MySQL时间戳。

——————————————————————————————

136 tmp_table_size                          | 15728640

如果内存内的临时表超过该值,MySQL自动将它转换为硬盘上的MyISAM表。如果你执行许多高级GROUP BY查询并且有大量内存,则可以增加tmp_table_size的值。

作用域:全局、会话

默认值:0

值域:1024~ 4294967295

——————————————————————————————

137 tmpdir                                  | C:\Windows\TEMP

临时目录的路径

——————————————————————————————

138 transaction_alloc_block_size            | 8192

将保存到二进制日志中的事务的查询而分配的内存块的大小

作用域:全局、会话

值域:

32位系统:默认8192 值域:1024~ 4294967295

64位系统:默认8192值域:1024 ~18446744073709547520

——————————————————————————————

139transaction_prealloc_size              | 4096

为transaction_alloc_blocks分配的固定缓冲区的大小(字节),在两次查询之间不会释放。使该值足够大,将所有查询固定到一个事务中,可以避免多次malloc()调用。

作用域:全局、会话

值域:

32位系统:默认4096 值域:1024~ 4294967295

64位系统:默认4096值域:1024 ~18446744073709547520

——————————————————————————————

140 tx_isolation                            | REPEATABLE-READ

默认事务隔离级别。默认值为REPEATABLE-READ

该变量可以被直接设置(全局),也可以通过set transaction=值 语句来设置。

值可以是:

READ-UNCOMMITTED

READ-COMMITTED

REPEATABLE-READ

SERIALIZABLE

——————————————————————————————

141unique_checks                                 | ON

该变量控制是否在InnoDB表的辅助索引的唯一性进行检查。

当值是0/OFF,则服务器假设输入的数据不存在重复键,储存引擎则跳过运行。(这个是你要储存的数据不用唯一性)

作用域:全局、会话

默认值是1

——————————————————————————————

142updatable_views_with_limit             | YES

该变量控制如果更新包含LIMIT子句,是否可以在当前表中使用不包含主关键字的视图进行更新。(通常用GUI工具生成这类更新)。更新指UPDATE或DELETE语句。这儿主关键字指PRIMARY KEY,或一个UNIQUE索引,其中任何列不可以包含NULL。

该变量有两个值:

值是1或YES:只发出警告(没有错误消息)。这是 默认值。

值是0或NO:禁止更新。

作用域:全局、会话

默认值是1

——————————————————————————————

143version                                 | 5.1.49-community

服务器的版本号

——————————————————————————————

144version_comment                         | MySQL Community Server (GPL)

configure脚本有一个–with-comment选项,当构建MySQL时可以进行注释。该变量包含注释值

作用域:全局

——————————————————————————————

145version_compile_machine                 | ia32

该Mysql程序是用什么工具编译出来的

——————————————————————————————

146version_compile_os                      | Win32

该Mysql程序是编译时的操作系统

——————————————————————————————

147wait_timeout                            | 28800

服务器关闭非交互连接之前等待活动的秒数

作用域:全局、会话

值域:

32位系统:默认28800 值域:1~ 4294967295

64位系统:默认28800 值域:1 ~18446744073709547520

——————————————————————————————

148 warning_count                           | 1

上一个查询语句出现的errors, warnings, notes错误的数目。

 

 

上周我们的数据库瞬间出现了很多locked进程,很快,所有服务器页面都无法打开。我们有两个大的项目部署同时在这几台webserver上,分别访问两台mysql server。

通过查看慢查询log发现,有一条管理后台sql语句执行时间为近800秒,其后有多条locked time占总时间90%以上的慢查询。但是,这条管理后台sql只是select语句,怎么会使其他select语句locked呢?一般思考读锁是相容的啊。经实验发现,该sql句子涉及到多张表,且逻辑比较复杂,需要对一张MyISAM类型的product表加读锁,而该表经常会有写操作,这些写操作尝试加写锁时blocked,但是会wait写锁(之前书上看到过,没有查看mysql资料,大概原理应该相同),这就同时会使后面的读操作加读锁失败。

除了需要对这些sql句子进行优化之外,因为之前进行过一次长时间的mysqldump操作,所以show status like ‘%qcache%’发现该数据的query_cache_type为DEMAND,即只有在显示写成select SQL_CACHE时才会使用到查询缓存。在这种配置下,不存在缓存竞争的问题。

为了解决这种读写锁竞争的问题,做了读写分离。之前两台数据库已经是master-slave备份形式了,但是slave并没有承担线上压力,现在从代码层面上处理。

之前的代码描述如下:

class Core_Mysql{
   function querys{...}
   function query{...}
   function update{...}
}
class Core_Model{
   protected $db;
   function __construct{
       $db = new Core_Mysql();
   }  

   function func1_read{ $sql = '...'; $this->db->querys($sql);}
   function func2_write{ $sql = '...'; $this->db->update($sql);}
}

Core_Model还有多个子类继承并override了__construct方法,但是都没有改变db变量的初始化方式。为了最小限度的重构,代码如下:
class Core_Mysql{
   function querys{...}
   function query{...}
   function update{...}
}
class Core_ModelParent{
   private $db_r;
   private $db_rw;

   function __get($name){ return ('db' == $name)? $this->db_rw: (('db_r' == $name)? $this->db_r: null); }
   function lazy_get_r(){...}
   function lazy_get_rw(){...}
   function set_db_info(){...}
}
class Core_Model{
   protected $db;
   function __construct{
       $db = new Core_Mysql();
   }  

   function func1_read{ $sql = '...'; $this->db_r->querys($sql);}
   function func2_write{ $sql = '...'; $this->db->update($sql);}
}

这样,只需要修改model层里希望访问只读库的方法了。当然,以上只是一个大概的代码,在实际中,还考虑了如果主库挂了,则所有访问转移到从库,但只读(在Core_mysql的update方法里控制即可);或者如果从库挂了,则所有访问指向主库。在故障前后,不需要修改代码。

目前仅将前几天导致故障的那个管理后台sql句子指向了从库,待运行几天稳定后,再将更多的流量导向从库。

也需要考虑到,主从复制是有时延的,所以时间敏感的读操作,还需要保留在主库上。

———————————

这里还有一个问题,由于lazy连接mysql数据库,而在构造sql句子时,我会调用mysql_real_escape_string来封装字符串,在手册里写道:

Note:

A MySQL connection is required before using mysql_real_escape_string() otherwise an error of level E_WARNING is generated, and FALSE is returned. If link_identifier isn’t defined, the last MySQL connection is used.

这就导致会引发如下错误:

PHP Warning:  mysql_real_escape_string() [<a href=’function.mysql-real-escape-string’>function.mysql-real-escape-string</a>]: A link to the server could not be established
PHP Warning:  mysql_real_escape_string() [<a href=’function.mysql-real-escape-string’>function.mysql-real-escape-string</a>]: Access denied for user ‘www-user-name’@’localhost’ (using password: NO)

即,在没有mysql连接时,调用mysql_real_escape_string会触发mysql_connect使用当前用户(webserver运行者)连接本地的数据库。为了解决该问题,可以在Core_model的构造器中显示连接读写库。或者压根不使用该方法,自己写一个替代的方法。