??xml version="1.0" encoding="utf-8" standalone="yes"?>սƴ3:C++博客 - սƵ2019|սع//www.pppqb.icu/markqian86/practice makes perfectQ阅诅R分析、练习、ȝ?/description>zh-cnMon, 23 Sep 2019 15:13:19 GMTMon, 23 Sep 2019 15:13:19 GMT60Linux tcpdump命o详解 - սƵ2019|սع//www.pppqb.icu/markqian86/archive/2019/09/19/216843.html长戟十三?/dc:creator>长戟十三?/author>Thu, 19 Sep 2019 07:21:00 GMT//www.pppqb.icu/markqian86/archive/2019/09/19/216843.html//www.pppqb.icu/markqian86/comments/216843.html//www.pppqb.icu/markqian86/archive/2019/09/19/216843.html#Feedback0//www.pppqb.icu/markqian86/comments/commentRss/216843.html//www.pppqb.icu/markqian86/services/trackbacks/216843.html阅读全文

]]>
Linux中找到占用cpu最高的U程 - սƵ2019|սع//www.pppqb.icu/markqian86/archive/2019/09/03/216762.html长戟十三?/dc:creator>长戟十三?/author>Tue, 03 Sep 2019 08:12:00 GMT//www.pppqb.icu/markqian86/archive/2019/09/03/216762.html//www.pppqb.icu/markqian86/comments/216762.html//www.pppqb.icu/markqian86/archive/2019/09/03/216762.html#Feedback0//www.pppqb.icu/markqian86/comments/commentRss/216762.html//www.pppqb.icu/markqian86/services/trackbacks/216762.html在工作中Q经怼到CPU占用100%的情况,那如何找到是那个U程占用了cpu呢? 

1. top命oQ找到cpu占用最高的q程

2. 查看该进E的U程Q?top  -p <pid>

3. H 切换到线E模式,扑ֈ占用cpu最高的U程。ƈ把线E号转化为十六进Ӟprintf "%x\n" <U程ID>

4. pstack <q程?gt;Q把U程栈打印出来。找到对应的U程号就可以分析Z么线E会占用那么高的cpu了?/p>

]]>
Socket/Epoll主要程对socket错误码正处?/title><link>//www.pppqb.icu/markqian86/archive/2019/08/14/216690.html</link><dc:creator>长戟十三?/dc:creator><author>长戟十三?/author><pubDate>Wed, 14 Aug 2019 11:11:00 GMT</pubDate><guid>//www.pppqb.icu/markqian86/archive/2019/08/14/216690.html</guid><wfw:comment>//www.pppqb.icu/markqian86/comments/216690.html</wfw:comment><comments>//www.pppqb.icu/markqian86/archive/2019/08/14/216690.html#Feedback</comments><slash:comments>0</slash:comments><wfw:commentRss>//www.pppqb.icu/markqian86/comments/commentRss/216690.html</wfw:commentRss><trackback:ping>//www.pppqb.icu/markqian86/services/trackbacks/216690.html</trackback:ping><description><![CDATA[     摘要: 以下是对相关程和socket错误码正处理的结。一. Socket/Epoll主要遇到的问?(1) 非阻塞socket?接收程(recv/recvfrom)寚w?EINTR/EAGAIN/EWOULDBLOCK)当成Fatal错误处理,产生频繁断连.(2)EPOLLERR/EPOLLHUP事g?直接调用socket异常处理,产生频繁断连.(3)udp socket接收到size??..  <a href='//www.pppqb.icu/markqian86/archive/2019/08/14/216690.html'>阅读全文</a><img src ="//www.pppqb.icu/markqian86/aggbug/216690.html" width = "1" height = "1" /><br><br><div align=right><a style="text-decoration:none;" href="//www.pppqb.icu/markqian86/" target="_blank">长戟十三?/a> 2019-08-14 19:11 <a href="//www.pppqb.icu/markqian86/archive/2019/08/14/216690.html#Feedback" target="_blank" style="text-decoration:none;">发表评论</a></div>]]></description></item><item><title>epoll两种cdET和LT区别(l合实际例子) - սƵ2019|սع//www.pppqb.icu/markqian86/archive/2019/08/14/216689.html长戟十三?/dc:creator>长戟十三?/author>Wed, 14 Aug 2019 11:01:00 GMT//www.pppqb.icu/markqian86/archive/2019/08/14/216689.html//www.pppqb.icu/markqian86/comments/216689.html//www.pppqb.icu/markqian86/archive/2019/08/14/216689.html#Feedback0//www.pppqb.icu/markqian86/comments/commentRss/216689.html//www.pppqb.icu/markqian86/services/trackbacks/216689.html阅读全文

]]>
linux下epoll如何实现高效处理 - սƵ2019|սع//www.pppqb.icu/markqian86/archive/2019/08/14/216688.html长戟十三?/dc:creator>长戟十三?/author>Wed, 14 Aug 2019 10:41:00 GMT//www.pppqb.icu/markqian86/archive/2019/08/14/216688.html//www.pppqb.icu/markqian86/comments/216688.html//www.pppqb.icu/markqian86/archive/2019/08/14/216688.html#Feedback0//www.pppqb.icu/markqian86/comments/commentRss/216688.html//www.pppqb.icu/markqian86/services/trackbacks/216688.html开发高性能|络E序Ӟwindows开发者们a必称iocpQlinux开发者们则言必称epoll?/p>

大家都明白epoll是一UIO多\复用技术,可以非常高效的处理数以百万计的socket句柄Q比起以前的select和poll效率高大发了?/p>

我们用vepoll来都感觉挺爽Q确实快Q那么,它到底ؓ什么可以高速处理这么多q发q接呢?

原理介绍

先简单回下如何使用C库封装的3个epollpȝ调用吧?/p>

1 int epoll_create(int size);     2 int epoll_ctl(int epfd, int op, int fd, struct epoll_event *event);     3 int epoll_wait(int epfd, struct epoll_event *events,int maxevents, int timeout);   

使用h很清晎ͼ

1 首先要调用epoll_create建立一个epoll对象。参数size是内怿证能够正处理的最大句柄数Q多于这个最大数时内核可不保证效果?/p>

2 epoll_ctl可以操作上面建立的epollQ例如,刚建立的socket加入到epoll中让其监控,或者把 epoll正在监控的某个socket句柄UdepollQ不再监控它{等?/p>

3 epoll_wait在调用时Q在l定的timeout旉内,当在监控的所有句柄中有事件发生时Q就q回用户态的q程?/p>

从上面的调用方式可以看到epoll比select/poll的优之处:

因ؓ后者每ơ调用时都要传递你所要监控的所有socketlselect/pollpȝ调用Q这意味着需要将用户态的socket列表copy到内核态,如果以万计的句柄会导致每ơ都要copy几十几百KB的内存到内核态,非常低效?/p>

而我们调用epoll_wait时就相当于以往调用select/pollQ但是这时却不用传递socket句柄l内核,因ؓ内核已经在epoll_ctl中拿C要监控的句柄列表?/p>

所以,实际上在你调用epoll_create后,内核已l在内核态开始准备帮你存储要监控的句柄了Q每ơ调用epoll_ctl只是在往内核的数据结构里塞入新的socket句柄?/p>

在内栔RQ一切皆文g。所以,epoll向内核注册了一个文件系l,用于存储上述的被监控socket。当你调用epoll_createӞ׃在这个虚拟的epoll文gpȝ里创Z个filel点。当然这个file不是普通文Ӟ它只服务于epoll?/p>

epoll在被内核初始化时Q操作系l启动)Q同时会开辟出epoll自己的内栔R速cache区,用于安置每一个我们想监控的socketQ这些socket会以U黑树的形式保存在内核cache里,以支持快速的查找、插入、删除?/p>

q个内核高速cache区,是建立q箋的物理内存页Q然后在之上建立slab层,单的_是物理上分配好你想要的size的内存对象,每次使用旉是用空闲的已分配好的对象?/p>

 1 static int __init eventpoll_init(void)      2 {      3     ... ...      4       5     /* Allocates slab cache used to allocate "struct epitem" items */      6     epi_cache = kmem_cache_create("eventpoll_epi", sizeof(struct epitem),      7             0, SLAB_HWCACHE_ALIGN|EPI_SLAB_DEBUG|SLAB_PANIC,      8             NULL, NULL);      9      10     /* Allocates slab cache used to allocate "struct eppoll_entry" */     11     pwq_cache = kmem_cache_create("eventpoll_pwq",     12             sizeof(struct eppoll_entry), 0,     13             EPI_SLAB_DEBUG|SLAB_PANIC, NULL, NULL);     14      15  ... ...     

epoll的高效在?/h3>

当我们调用epoll_ctl往里塞入百万个句柄Ӟepoll_wait仍然可以飞快的返回,q有效的发生事件的句柄l我们用戗?/p>

q是׃我们在调用epoll_createӞ内核除了帮我们在epoll文gpȝ里徏了个filel点Q在内核cache里徏了个U黑树用于存储以后epoll_ctl传来的socket外,q会再徏立一个list链表Q用于存储准备就l的事gQ当epoll_wait调用Ӟ仅仅观察q个list链表里有没有数据卛_?/p>

有数据就q回Q没有数据就sleepQ等到timeout旉到后即链表没数据也q回。所以,epoll_wait非常高效?/p>

而且Q通常情况下即使我们要监控百万计的句柄Q大多一ơ也只返回很量的准备就l句柄而已Q所以,epoll_wait仅需要从内核态copy量的句柄到用户态而已Q如何能不高效?Q?/p>

那么Q这个准备就llist链表是怎么l护的呢Q?/p>

当我们执行epoll_ctlӞ除了把socket攑ֈepoll文gpȝ里file对象对应的红黑树上之外,q会l内怸断处理程序注册一个回调函敎ͼ告诉内核Q如果这个句柄的中断CQ就把它攑ֈ准备qAlist链表里?/p>

所以,当一个socket上有数据CQ内核在把网卡上的数据copy到内怸后就来把socket插入到准备就l链表里了?/p>

如此Q一颗红黑树Q一张准备就l句柄链表,量的内核cacheQ就帮我们解决了大ƈ发下的socket处理问题?/span>

执行epoll_createӞ创徏了红黑树和就l链表,执行epoll_ctlӞ如果增加socket句柄Q则查在U黑树中是否存在Q存在立卌回,不存在则d到树q上Q然后向内核注册回调函数Q用于当中断事g来时向准备qA链表中插入数据。执行epoll_wait时立刻返回准备就l链表里的数据即可?/p>

最后看看epoll独有的两U模式LT和ET。无论是LT和ET模式Q都适用于以上所说的程。区别是QLT模式下,只要一个句柄上的事件一ơ没有处理完Q会在以后调用epoll_wait时次ơ返回这个句柄,而ET模式仅在W一ơ返回?/p>

qg事怎么做到的呢Q?/p>

当一个socket句柄上有事gӞ内核会把该句柄插入上面所说的准备qAlist链表Q这时我们调用epoll_waitQ会把准备就l的socket拯到用h内存,然后清空准备qAlist链表Q?/p>

最后,epoll_waitq了件事Q就是检查这些socketQ如果不是ET模式Q就是LT模式的句柄了Q,q且q些socket上确实有未处理的事gӞ又把该句柄放回到刚刚清空的准备就l链表了?/p>

所以,非ET的句柄,只要它上面还有事Ӟepoll_wait每次都会q回。而ET模式的句柄,除非有新中断刎ͼ即socket上的事g没有处理完,也是不会ơ次从epoll_waitq回的?/p>

  1 /*     2  * Each file descriptor added to the eventpoll interface will     3  * have an entry of this type linked to the hash.     4  */     5 struct epitem {     6         /* RB-Tree node used to link this structure to the eventpoll rb-tree */     7         struct rb_node rbn;     8         //U黑树,用来保存eventpoll     9             10         /* List header used to link this structure to the eventpoll ready list */    11         struct list_head rdllink;    12         //双向链表Q用来保存已l完成的eventpoll    13             14         /* The file descriptor information this item refers to */    15         struct epoll_filefd ffd;    16         //q个l构体对应的被监听的文g描述W信?   17             18         /* Number of active wait queue attached to poll operations */    19         int nwait;    20         //poll操作中事件的个数    21             22         /* List containing poll wait queues */    23         struct list_head pwqlist;    24         //双向链表Q保存着被监视文件的{待队列Q功能类gselect/poll中的poll_table    25             26         /* The "container" of this item */    27         struct eventpoll *ep;    28         //指向eventpollQ多个epitem对应一个eventpoll    29             30         /* The structure that describe the interested events and the source fd */    31         struct epoll_event event;    32         //记录发生的事件和对应的fd    33            34         /*    35          * Used to keep track of the usage count of the structure. This avoids    36          * that the structure will desappear from underneath our processing.    37 */    38         atomic_t usecnt;    39         //引用计数    40             41         /* List header used to link this item to the "struct file" items list */    42         struct list_head fllink;    43         双向链表Q用来链接被监视的文件描q符对应?span style="box-sizing: border-box; color: #f92672;">struct file。因为file里有f_ep_link,用来保存所有监视这个文件的epoll节点    44             45         /* List header used to link the item to the transfer list */    46         struct list_head txlink;    47         双向链表Q用来保存传输队?   48             49         /*    50          * This is used during the collection/transfer of events to userspace    51          * to pin items empty events set.    52 */         53         unsigned int revents;    54         //文g描述W的状态,在收集和传输时用来锁住空的事仉?   55 };    56         57 //该结构体用来保存与epoll节点兌的多个文件描q符Q保存的方式是用红黑树实现的hash?    58 //至于Z么要保存Q下文有详细解释。它与被监听的文件描q符一一对应.    59 struct eventpoll {    60         /* Protect the this structure access */    61         rwlock_t lock;    62         //d?   63            64         /*    65          * This semaphore is used to ensure that files are not removed    66          * while epoll is using them. This is read-held during the event    67          * collection loop and it is write-held during the file cleanup    68          * path, the epoll file exit code and the ctl operations.    69 */    70         struct rw_semaphore sem;    71         //d信号?   72             73         /* Wait queue used by sys_epoll_wait() */    74         wait_queue_head_t wq;    75         /* Wait queue used by file->poll() */    76             77         wait_queue_head_t poll_wait;    78         /* List of ready file descriptors */    79             80         struct list_head rdllist;    81         //已经完成的操作事件的队列?   82             83         /* RB-Tree root used to store monitored fd structs */    84         struct rb_root rbr;    85         //保存epoll监视的文件描q符    86 };    87     88 //q个l构体保存了epoll文g描述W的扩展信息Q它被保存在filel构体的private_data    89 //中。它与epoll文g节点一一对应。通常一个epoll文g节点对应多个被监视的文g描述W?   90 //所以一个eventpolll构体会对应多个epiteml构体。那么,epoll中的{待事g攑֜哪里呢?见下?   91 /* Wait structure used by the poll hooks */    92 struct eppoll_entry {    93         /* List header used to link this structure to the "struct epitem" */    94         struct list_head llink;    95         /* The "base" pointer is set to the container "struct epitem" */    96         void *base;    97         /*    98          * Wait queue item that will be linked to the target file wait    99          * queue head.   100 */   101         wait_queue_t wait;   102         /* The wait queue head that linked the "wait" wait queue item */   103         wait_queue_head_t *whead;   104 };   105    106 //与select/poll的struct poll_table_entry相比Qepoll的表C等待队列节点的l?  107 //构体只是E有不同Q与struct poll_table_entry比较一下?  108 struct poll_table_entry {   109         struct file * filp;   110         wait_queue_t wait;   111         wait_queue_head_t * wait_address;   112 };  


]]>MySQL row_format引发的案例一?/title><link>//www.pppqb.icu/markqian86/archive/2019/08/14/216685.html</link><dc:creator>长戟十三?/dc:creator><author>长戟十三?/author><pubDate>Wed, 14 Aug 2019 07:38:00 GMT</pubDate><guid>//www.pppqb.icu/markqian86/archive/2019/08/14/216685.html</guid><wfw:comment>//www.pppqb.icu/markqian86/comments/216685.html</wfw:comment><comments>//www.pppqb.icu/markqian86/archive/2019/08/14/216685.html#Feedback</comments><slash:comments>0</slash:comments><wfw:commentRss>//www.pppqb.icu/markqian86/comments/commentRss/216685.html</wfw:commentRss><trackback:ping>//www.pppqb.icu/markqian86/services/trackbacks/216685.html</trackback:ping><description><![CDATA[<pre style="-webkit-font-smoothing: antialiased; margin-top: 0px; margin-bottom: 10px; white-space: pre-line; font-variant-numeric: normal; font-variant-east-asian: normal; font-stretch: normal; font-size: 0.8rem; line-height: 1.45; font-family: Consolas, "Liberation Mono", Menlo, Courier, monospace; color: #567482; word-wrap: normal; padding: 1rem 1.5rem; overflow: auto; background: #f2f5f9; max-width: 100%;"><code cs"="" style="-webkit-font-smoothing: antialiased; font-family: Consolas, "Liberation Mono", Menlo, Courier, monospace; font-size: 0.9rem; color: #333333; word-break: normal; white-space: pre; background: transparent; display: inline; max-width: initial; padding: 0px; margin: 0px; overflow: initial; line-height: inherit; word-wrap: normal; border: 0px;">?span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; color: #008080; max-width: 100%;">768</span>字节Q?span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; font-weight: bold; max-width: 100%;">dynamic</span>格式下,溢出的列只存储前<span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; color: #008080; max-width: 100%;">20</span>字节Q一旦发生了行溢出, <span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; font-weight: bold; max-width: 100%;">dynamic</span>其实存储一个指针,数据都放在溢出页里,<span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; font-weight: bold; max-width: 100%;">dynamic</span>代表长字段(发生行溢?完全off-page存储?/code></pre><p style="-webkit-font-smoothing: antialiased; margin: 0px 0px 0.7rem; padding: 0px; color: #3d464d; line-height: 1.8; word-wrap: break-word; font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; font-size: 16px; max-width: 100%;"><strong style="-webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; max-width: 100%;">Row_format 引发异常的一个案?</strong><br style="-webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; max-width: 100%;" />前几天生产MYSQL遇到的一个问题,在录入数据时Q整行数据完全录不进去,报以下错Q?/p><pre style="-webkit-font-smoothing: antialiased; margin-top: 0px; margin-bottom: 10px; white-space: pre-line; font-variant-numeric: normal; font-variant-east-asian: normal; font-stretch: normal; font-size: 0.8rem; line-height: 1.45; font-family: Consolas, "Liberation Mono", Menlo, Courier, monospace; color: #567482; word-wrap: normal; padding: 1rem 1.5rem; overflow: auto; background: #f2f5f9; max-width: 100%;"><code php"="" style="-webkit-font-smoothing: antialiased; font-family: Consolas, "Liberation Mono", Menlo, Courier, monospace; font-size: 0.9rem; color: #333333; word-break: normal; white-space: pre; background: transparent; display: inline; max-width: initial; padding: 0px; margin: 0px; overflow: initial; line-height: inherit; word-wrap: normal; border: 0px;">Cause:java.sql.SQLException: com.taobao.tddl.common.<span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; font-weight: bold; max-width: 100%;">exception</span>.TddlException:java.sql.SQLException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:Row size too large (> <span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; color: #008080; max-width: 100%;">8126</span>). Changing some columns to TEXT <span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; font-weight: bold; max-width: 100%;">or</span> BLOB <span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; font-weight: bold; max-width: 100%;">or</span> usingROW_FORMAT=DYNAMIC <span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; font-weight: bold; max-width: 100%;">or</span> ROW_FORMAT=COMPRESSED may help. In current row format,BLOB prefix of <span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; color: #008080; max-width: 100%;">768</span> bytes is stored inline.; nested <span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; font-weight: bold; max-width: 100%;">exception</span> iscom.ibatis.common.jdbc.<span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; font-weight: bold; max-width: 100%;">exception</span>.NestedSQLException: </code></pre><p style="-webkit-font-smoothing: antialiased; margin: 0px 0px 0.7rem; padding: 0px; color: #3d464d; line-height: 1.8; word-wrap: break-word; font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; font-size: 16px; max-width: 100%;">该表是一个品介l详情表Q有20多个TEXT 字段Q刚好碰C一个品,每个字段录入的数据都很长Q?br style="-webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; max-width: 100%;" />而mysql 中有了个限制Q一个页Q这里pagesize ?6KQ必至存2行,也就是说每行的存储长度必d于等?192Q而这么多 TEXT 字段Q一行肯定是存不下来Q也是会发生溢出,而即例发生溢出,每个列仍然会存储?68字节Q该表的row_formart 是compactQ,字段一多还是超q了8192Q于是就报错Q插不进了?br style="-webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; max-width: 100%;" />最后将表的row_format 改ؓ dynamic 得以解决。alter table … row_format=dynamic;</p><p style="-webkit-font-smoothing: antialiased; margin: 0px 0px 0.7rem; padding: 0px; color: #3d464d; line-height: 1.8; word-wrap: break-word; font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; font-size: 16px; max-width: 100%;">所以,如果大家遇到一些表TEXT ?VARCHAR 大字D很多,又不好拆解时Q可能需要考虑下溢出后列的长度了,如果溢出后列的长度还是太大,则要看一下表?row_format Q?/p><pre style="-webkit-font-smoothing: antialiased; margin-top: 0px; margin-bottom: 10px; white-space: pre-line; font-variant-numeric: normal; font-variant-east-asian: normal; font-stretch: normal; font-size: 0.8rem; line-height: 1.45; font-family: Consolas, "Liberation Mono", Menlo, Courier, monospace; color: #567482; word-wrap: normal; padding: 1rem 1.5rem; overflow: auto; background: #f2f5f9; max-width: 100%;"><code sql"="" style="-webkit-font-smoothing: antialiased; font-family: Consolas, "Liberation Mono", Menlo, Courier, monospace; font-size: 0.9rem; color: #333333; word-break: normal; white-space: pre; background: transparent; display: inline; max-width: initial; padding: 0px; margin: 0px; overflow: initial; line-height: inherit; word-wrap: normal; border: 0px;"><span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; font-weight: bold; max-width: 100%;">show</span> <span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; font-weight: bold; max-width: 100%;">table</span> <span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; font-weight: bold; max-width: 100%;">status</span> <span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; font-weight: bold; max-width: 100%;">like</span> <span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; color: #dd1144; max-width: 100%;">'%xxx%'</span>\G </code></pre><p style="-webkit-font-smoothing: antialiased; margin: 0px 0px 0.7rem; padding: 0px; color: #3d464d; line-height: 1.8; word-wrap: break-word; font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; font-size: 16px; max-width: 100%;">必要旉要将其实讄?dynamic 如:</p><pre style="-webkit-font-smoothing: antialiased; margin-top: 0px; margin-bottom: 10px; white-space: pre-line; font-variant-numeric: normal; font-variant-east-asian: normal; font-stretch: normal; font-size: 0.8rem; line-height: 1.45; font-family: Consolas, "Liberation Mono", Menlo, Courier, monospace; color: #567482; word-wrap: normal; padding: 1rem 1.5rem; overflow: auto; background: #f2f5f9; max-width: 100%;"><code sql"="" style="-webkit-font-smoothing: antialiased; font-family: Consolas, "Liberation Mono", Menlo, Courier, monospace; font-size: 0.9rem; color: #333333; word-break: normal; white-space: pre; background: transparent; display: inline; max-width: initial; padding: 0px; margin: 0px; overflow: initial; line-height: inherit; word-wrap: normal; border: 0px;"><span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; font-weight: bold; max-width: 100%;">create</span> <span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; font-weight: bold; max-width: 100%;">table</span> <span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; font-weight: bold; max-width: 100%;">test</span>(<span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; font-weight: bold; max-width: 100%;">id</span> <span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; color: #0086b3; max-width: 100%;">int</span>,<span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; font-weight: bold; max-width: 100%;">name</span> <span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; color: #0086b3; max-width: 100%;">text</span>,...... ) row_format=dynamic; ?<span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; font-weight: bold; max-width: 100%;">alter</span> <span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; font-weight: bold; max-width: 100%;">table</span> <span style="font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, "PingFang SC", "Hiragino Sans GB", "WenQuanYi Micro Hei", "Microsoft Yahei", sans-serif; -webkit-font-smoothing: antialiased; margin: 0px; padding: 0px; font-weight: bold; max-width: 100%;">test</span> row_format=dynamic;</code></pre><img src ="//www.pppqb.icu/markqian86/aggbug/216685.html" width = "1" height = "1" /><br><br><div align=right><a style="text-decoration:none;" href="//www.pppqb.icu/markqian86/" target="_blank">长戟十三?/a> 2019-08-14 15:38 <a href="//www.pppqb.icu/markqian86/archive/2019/08/14/216685.html#Feedback" target="_blank" style="text-decoration:none;">发表评论</a></div>]]></description></item><item><title>mysql表数据压~?/title><link>//www.pppqb.icu/markqian86/archive/2019/08/14/216684.html</link><dc:creator>长戟十三?/dc:creator><author>长戟十三?/author><pubDate>Wed, 14 Aug 2019 07:31:00 GMT</pubDate><guid>//www.pppqb.icu/markqian86/archive/2019/08/14/216684.html</guid><wfw:comment>//www.pppqb.icu/markqian86/comments/216684.html</wfw:comment><comments>//www.pppqb.icu/markqian86/archive/2019/08/14/216684.html#Feedback</comments><slash:comments>0</slash:comments><wfw:commentRss>//www.pppqb.icu/markqian86/comments/commentRss/216684.html</wfw:commentRss><trackback:ping>//www.pppqb.icu/markqian86/services/trackbacks/216684.html</trackback:ping><description><![CDATA[<p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 15px;">记得一ơ面试中Q面试官问我是否知道表的压羃Q这个时候我才知道mysql有个表压~这么个功能Q今天试用下看看表的压羃率怎么栗?/span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 15px;">q里分两个部分说明,W一部分Q官Ҏ?说明Q第二部分:具体实例试?/span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 18px;"><strong style="margin: 0px; padding: 0px;">【第一部分?/strong></span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 16px;"><strong style="margin: 0px; padding: 0px;">一、表压羃概述Q?/strong></span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 15px;">表压~可以在创徏表时开启,压羃表能够表中的数据以压羃格式存储Q压~能够显著提高原生性能和可伸羃性。压~意味着在硬盘和内存之间传输的数据更且占用相对的内存及硬盘,对于辅助索引Q这U压~带来更加明昄好处Q因为烦引数据也被压~了。压~对于硬盘是SSD的存储设备尤为重要,因ؓ它们相对普通的HDD盘比较贵且定w有限?/span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 15px;">我们都知道,CPU和内存的速度q远大于盘Q因为对于数据库服务器,盘IO可能会成为紧要资源或者瓶颈。数据压~能够让数据库变得更,从而减磁盘的I/O,q能提高pȝ吞吐量,以很的成本Q耗费较多的CPU资源Q。对于读比重比较多的应用Q压~是特别有用。压~能够让pȝ拥有_的内存来存储热数据?/span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 15px;">在创建innodb表时带上ROW_FORMAT=COMPRESSED参数能够使用比默认的16K更小的页。这样在d旉要更的I/OQ对于SSD盘更有价倹{?/span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 15px;">늚大小通过<code style="margin: 0px; padding: 0px;">KEY_BLOCK_SIZE</code>参数指定。不同大的|味着需要用独立表I间Q不能用系l共享表I间Q可以通过<code style="margin: 0px; padding: 0px;">innodb_file_per_table</code>指定?span style="margin: 0px; padding: 0px;"><code style="margin: 0px; padding: 0px;">KEY_BLOCK_SIZE</code></span>的D,你获得I/O好处p多,但是如果因ؓ你指定的值太,当数据被压羃C_满每页多行数据记录Ӟ会生额外的开销来重l页。对于一个表Q?span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px;"><code style="margin: 0px; padding: 0px;">KEY_BLOCK_SIZE</code></span></span>的值有多小是有严格的限制的Q一般是Z每个索引键的长度。有时指定D,当create table或者alter table会失败?/span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 15px;">在缓冲池中,被压~的数据是存储在页中的Q这个小늚实际大小是<span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px;"><code style="margin: 0px; padding: 0px;">KEY_BLOCK_SIZE</code></span></span>的倹{ؓ了提取和更新列|mysql也会在缓冲池中创Z个未压羃?6kcQ何更新到未压~的也需要重新写入到压羃的页Q这时你需要估计缓冲池的大以满压羃和未压羃的页Q尽当~冲I间不Ӟ未压~的会被挤出缓冲池。在下次讉KӞ不压~的还会被创徏?/span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 16px;"><strong style="margin: 0px; padding: 0px;">二、用表的压~?/strong></span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 15px;">在创Z个压~表之前Q需要启用独立表I间参数<span style="margin: 0px; padding: 0px;"><code style="margin: 0px; padding: 0px;">innodb_file_per_table=1Q也需要设|?span style="margin: 0px; padding: 0px;"><code style="margin: 0px; padding: 0px;">innodb_file_format</code></span>=<code style="margin: 0px; padding: 0px;">BarracudaQ你可以写到my.cnf文g中不需要重启mysql服务?/code></code></span></span></p><div style="margin: 5px 0px; font-size: 12px !important;"><pre style="margin-top: 0px; margin-bottom: 0px; padding: 0px; word-wrap: break-word; white-space: pre-wrap; font-family: "Courier New" !important;"><span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">SET</span> GLOBAL innodb_file_per_table<span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">=</span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">1</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;">; </span><span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">SET</span> GLOBAL innodb_file_format<span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">=</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;">Barracuda; </span><span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">CREATE</span> <span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">TABLE</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> t1 (c1 </span><span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">INT</span> <span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">PRIMARY</span> <span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">KEY</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;">) <strong style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;"> ROW_FORMAT</span></strong></span><strong style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">=COMPRESSED KEY_BLOCK_SIZE=8;</span></strong></pre></div><ul style="margin: 0px 0px 0px 30px; padding: 0px; word-break: break-all; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><li style="margin: 0px 0px 1em; padding: 0px; list-style: disc;">如果你指?code style="margin: 0px; padding: 0px;">ROW_FORMAT=COMPRESSED</code>Q那么可以忽?code style="margin: 0px; padding: 0px;">KEY_BLOCK_SIZE</code>的|q时使用默认innodb늚一半,?kbQ?/li><li style="margin: 0px 0px 1em; padding: 0px; list-style: disc;">如果你指定了<code style="margin: 0px; padding: 0px;">KEY_BLOCK_SIZE</code>的|那么你可以忽?code style="margin: 0px; padding: 0px;">ROW_FORMAT=COMPRESSED</code>Q因时会自动启用压羃Q?/li><li style="margin: 0px 0px 1em; padding: 0px; list-style: disc;">Z指定最合?code style="margin: 0px; padding: 0px;">KEY_BLOCK_SIZE</code>的|你可以创的多个副本,使用不同的D行测试,比较他们?ibd文g的大;</li><li style="margin: 0px 0px 1em; padding: 0px; list-style: disc;"><code style="margin: 0px; padding: 0px;">KEY_BLOCK_SIZE</code>的gZU提C,如必要,Innodb也可以用一个不同的倹{?代表默认压羃늚|Innodb늚一半?code style="margin: 0px; padding: 0px;">KEY_BLOCK_SIZE</code>的值只能小于等于innodb page size。如果你指定了一个大于innodb page size的|mysql会忽略这个值然后生一个警告,q时<code style="margin: 0px; padding: 0px;">KEY_BLOCK_SIZE</code>的值是Innodb늚一半。如果设|了<span style="margin: 0px; padding: 0px;"><code style="margin: 0px; padding: 0px;">innodb_strict_mode=ONQ那么指定一个不合法?code style="margin: 0px; padding: 0px;">KEY_BLOCK_SIZE</code>的值是q回报错?/code></span></li></ul><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 15px;">InnoDB未压~的数据|16KQ根据选项l合|mysql为每个表?ibd文g使用1kb,2kb,4kb,8kb,16kb大,实际的压~算法ƈ不会?code style="margin: 0px; padding: 0px;">KEY_BLOCK_SIZE</code>值媄响,q个值只是决定每个压~块有多大,从而媄响多行被压~到每个c设|?span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px;"><code style="margin: 0px; padding: 0px;">KEY_BLOCK_SIZE</code>?/span></span>{于16kq不能有效的q行压羃Q因为默认的innodb就?6kQ但是对于拥有很多BLOB,TEXT,VARCHARcd字段的表可能会有效果的?/span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 16px;"><strong style="margin: 0px; padding: 0px;">三、InnoDB表的压羃优化</strong></span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 15px;"> 在进行表压羃旉要考虑影响压羃性能的因素,如:</span></p><ul style="margin: 0px 0px 0px 30px; padding: 0px; word-break: break-all; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><li style="margin: 0px 0px 1em; padding: 0px; list-style: disc;"><span style="margin: 0px; padding: 0px; font-size: 15px;">哪些表需要压~?br style="margin: 0px; padding: 0px;" /></span></li><li style="margin: 0px 0px 1em; padding: 0px; list-style: disc;">如何选择压羃表的大?/li><li style="margin: 0px 0px 1em; padding: 0px; list-style: disc;">Zq行时性能特征是否需要调整buffer pool大小Q如pȝ在压~和解压~数据所p的时间量Q系l负载更像一个数据仓库还是OLTP事务性系l?/li><li style="margin: 0px 0px 1em; padding: 0px; list-style: disc;">如果在压~表上执行DML操作Q由于数据分布的方式Q可能导致压~失败,q时你可能需要配|额外的更高U的配置选项</li></ul><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><strong style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px; font-size: 16px;">1、何时用压羃?/span></strong></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 15px;">一般而言Q对于读q远大于写的应用以及拥有合理数量的字W串列的表,使用压羃效果会更好?/span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 16px;"><strong style="margin: 0px; padding: 0px;">2、数据特性及压羃?/strong></span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 15px;">影响数据文g压羃效率的一个关键因素是数据本n的结构,在块数据中,压羃是通过识别重复字符q行压羃的,对于完全随机的数据是一个糟p的情况Q一般而言Q有重复数据的压~更好。对于字W串的列压羃׃错,无论是stringq是blob、text{类型的。另一斚wQ如果表中的数据是二q制cdQ如整Ş、Q点型{或者之前别压羃q的如jpg、pngcd的,压羃效果一般不好,但也不是l对的?/span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 15px;">Z军_是否Ҏ个表q行压羃Q你需要进行试验,可以Ҏ未压~与压羃后的数据文g的大,以及监控pȝ对于压羃表的工作负蝲q行军_。具体试验请查看W二部分?/span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 15px;">查看监控压羃表的负蝲Q如下:</span></p><ul style="margin: 0px 0px 0px 30px; padding: 0px; word-break: break-all; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><li style="margin: 0px 0px 1em; padding: 0px; list-style: disc;"><span style="margin: 0px; padding: 0px; font-size: 15px;">对于单的试Q如一个mysql实例上没有其他的压羃表了Q直接查?span style="margin: 0px; padding: 0px;"><code style="margin: 0px; padding: 0px;">INFORMATION_SCHEMA.INNODB_CMP表数据即可,该表存一些压~表的数据状态,l构如下Q?/code></span></span></li></ul><table summary="Columns of INNODB_CMP and INNODB_CMP_RESET" border="1" style="margin: 0px; padding: 0px; border-spacing: 0px; border-style: solid; border-color: silver; border-collapse: collapse; word-break: break-word; color: #000000; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 14px; background-color: #ffffff; width: 1589px; height: 165px;"><thead style="margin: 0px; padding: 0px;"><tr style="margin: 0px; padding: 0px;"><th scope="col" style="margin: 0px; background-color: #fafafa; border: 1px solid silver; border-collapse: collapse; padding: 8px 14px;">Column name</th><th scope="col" style="margin: 0px; background-color: #fafafa; border: 1px solid silver; border-collapse: collapse; padding: 8px 14px;">Description</th></tr></thead><tbody style="margin: 0px; padding: 0px;"><tr style="margin: 0px; padding: 0px;"><td scope="row" style="margin: 0px; border-style: solid; border-color: silver; border-collapse: collapse; padding: 8px 14px;"><code style="margin: 0px; padding: 0px;">PAGE_SIZE</code></td><td style="margin: 0px; border-style: solid; border-color: silver; border-collapse: collapse; padding: 8px 14px;">采用压羃大(字节敎ͼ.</td></tr><tr style="margin: 0px; padding: 0px;"><td scope="row" style="margin: 0px; border-style: solid; border-color: silver; border-collapse: collapse; padding: 8px 14px;"><code style="margin: 0px; padding: 0px;">COMPRESS_OPS</code></td><td style="margin: 0px; border-style: solid; border-color: silver; border-collapse: collapse; padding: 8px 14px;">Number of times a B-tree page of the size <code style="margin: 0px; padding: 0px;">PAGE_SIZE</code> has been compressed. Pages are compressed whenever an empty page is created or the space for the uncompressed modification log runs out.</td></tr><tr style="margin: 0px; padding: 0px;"><td scope="row" style="margin: 0px; border-style: solid; border-color: silver; border-collapse: collapse; padding: 8px 14px;"><code style="margin: 0px; padding: 0px;">COMPRESS_OPS_OK</code></td><td style="margin: 0px; border-style: solid; border-color: silver; border-collapse: collapse; padding: 8px 14px;">Number of times a B-tree page of the size <code style="margin: 0px; padding: 0px;">PAGE_SIZE</code> has been successfully compressed. This count should never exceed <code style="margin: 0px; padding: 0px;">COMPRESS_OPS</code>.</td></tr><tr style="margin: 0px; padding: 0px;"><td scope="row" style="margin: 0px; border-style: solid; border-color: silver; border-collapse: collapse; padding: 8px 14px;"><code style="margin: 0px; padding: 0px;">COMPRESS_TIME</code></td><td style="margin: 0px; border-style: solid; border-color: silver; border-collapse: collapse; padding: 8px 14px;">Total time in seconds spent in attempts to compress B-tree pages of the size <code style="margin: 0px; padding: 0px;">PAGE_SIZE</code>.</td></tr><tr style="margin: 0px; padding: 0px;"><td scope="row" style="margin: 0px; border-style: solid; border-color: silver; border-collapse: collapse; padding: 8px 14px;"><code style="margin: 0px; padding: 0px;">UNCOMPRESS_OPS</code></td><td style="margin: 0px; border-style: solid; border-color: silver; border-collapse: collapse; padding: 8px 14px;">Number of times a B-tree page of the size <code style="margin: 0px; padding: 0px;">PAGE_SIZE</code> has been uncompressed. B-tree pages are uncompressed whenever compression fails or at first access when the uncompressed page does not exist in the buffer pool.</td></tr><tr style="margin: 0px; padding: 0px;"><td scope="row" style="margin: 0px; border-style: solid; border-color: silver; border-collapse: collapse; padding: 8px 14px;"><code style="margin: 0px; padding: 0px;">UNCOMPRESS_TIME</code></td><td style="margin: 0px; border-style: solid; border-color: silver; border-collapse: collapse; padding: 8px 14px;">Total time in seconds spent in uncompressing B-tree pages of the size <code style="margin: 0px; padding: 0px;">PAGE_SIZE</code>.</td></tr></tbody></table><ul style="margin: 0px 0px 0px 30px; padding: 0px; word-break: break-all; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><li style="margin: 0px 0px 1em; padding: 0px; list-style: disc;"><span style="margin: 0px; padding: 0px; font-size: 15px;">对于_的测试,如多个压~表Q查?span style="margin: 0px; padding: 0px;"><code style="margin: 0px; padding: 0px;">INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX表数据,׃该表攉数据需要付出昂贵得代hQ所以必d?span style="margin: 0px; padding: 0px;"><code style="margin: 0px; padding: 0px;">innodb_cmp_per_index_enabled选项才能查询。一般不要在生环境下开启该选项?/code></span></code></span></span></li><li style="margin: 0px 0px 1em; padding: 0px; list-style: disc;"><span style="margin: 0px; padding: 0px; font-size: 15px;">q可以针对压~运行一些测试SQL看看效率如何?/span></li><li style="margin: 0px 0px 1em; padding: 0px; list-style: disc;"><span style="margin: 0px; padding: 0px; font-size: 15px;">如果发现很多压羃p|Q那么你可以调整<span style="margin: 0px; padding: 0px;"><code style="margin: 0px; padding: 0px;">innodb_compression_level</code></span>, <span style="margin: 0px; padding: 0px;"><code style="margin: 0px; padding: 0px;">innodb_compression_failure_threshold_pct</code></span>, ?span style="margin: 0px; padding: 0px;"><code style="margin: 0px; padding: 0px;">innodb_compression_pad_pct_max</code></span>参数?/span></li></ul><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 16px;"><strong style="margin: 0px; padding: 0px;">3、数据库压羃和应用程序压~?/strong></span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 15px;">不需要在应用端和数据库同时压~相同的数据Q那h果ƈ不明显而且q消耗很多CPU资源。对于数据库压羃Q是在server端进行的。如果你在插入数据前通过代码q行数据压羃Q然后插入数据库Q这栯费很多CPU资源Q当然如果你的CPU有大量结余。你也可以结合两者,对于某些表进行应用程序压~,而对其他数据采用数据库压~?/span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 16px;"><strong style="margin: 0px; padding: 0px;">4、工作负载特性和压羃?/strong></span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 15px;">Z选择哪些表可以用压~,工作负蝲是另一个决定因素,一般而言Q如果你的系l是I/O瓉Q那么可以用CPUq行压羃与解压羃Q以CPU换取I/O?/span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 15px;"><span style="margin: 0px; padding: 0px; font-size: 16px;"><strong style="margin: 0px; padding: 0px;">四、INNODB表是如何压羃的?</strong></span></span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><strong style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px; font-size: 15px;">1、压~算?/span></strong></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 15px;">mysqlq行压羃是借助于zlib库,采用L777压羃法Q这U算法在减少数据大小、CPU利用斚w是成熟的、健壮的、高效的。同时这U算法是无失真的Q因此原生的未压~的数据L能够从压~文件中重构QLZ777实现原理是查N复数据的序列L后进行压~,所以数据模式决定了压羃效率Q一般而言Q用L数据能够被压~?0%以上?/span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 15px;">不同于应用程序压~或者其他数据库pȝ的压~,InnoDB压羃是同时对数据和烦引进行压~,很多情况下,索引能够占数据库d的40%-50%。如果压~效果很好,一般innodb文g会减?5%-50%或者更多,而且减少I/O增加pȝ吞吐量,但是会增加CPU的占用,你可通过讄<span style="margin: 0px; padding: 0px;"><code style="margin: 0px; padding: 0px;">innodb_compression_level参数来^衡压~别和CPU占用?/code></span></span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><strong style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px; font-size: 15px;">2、InnoDB数据存储及压~?/span></strong></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 15px;">所有数据和b-tree索引都是按页q行存储的,每行包含主键和表的其他列。辅助烦引也是b-treel构的,包含对|索引值及指向每行记录的指针,q个指针实际上就是表的主键倹{?/span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;">在innodb压羃表中Q每个压~页Q?,2,4,8Q都对应一个未压羃的页16KQؓ了访问压~页中的数据Q如果该在buffer pool中不存在Q那么就从硬盘上dq个压羃,然后q行解压到原来的数据l构。ؓ了最化I/O和减解压页的次敎ͼ有时Qbuffer pool中包括压~和未压~的,为给其他腾出地方,buffer pool会驱逐未压羃,仅仅留下压羃在内存中。或者如果一个页一D|间没有被讉KQ那么会被写到硬盘上。这样一来,M时候,buffer pool中都可以包含压羃和未压~页Q或者只有压~页或者两者都没有?/p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;">Mysql采用LRU法来保证哪些页应该在内存中q是被驱逐。因此热数据一般都会在内存中?/p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 15px;"><span style="margin: 0px; padding: 0px; font-size: 16px;"><strong style="margin: 0px; padding: 0px;">五、OLTPpȝ压羃负蝲优化</strong></span></span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 15px;">一般而言Qinnodb压羃对于只读或者读比重比较多的应用效果更好QSSD的出玎ͼ使得压羃更加吸引我们Q尤其对于OLTPpȝ。对于经常update、delete、insert的应用,通过压羃表能够减他们的存储需求和每秒I/O操作?/span></p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 15px;">下面是针对写密集的应用,讄压羃表的一些有用参敎ͼ</span></p><ul style="margin: 0px 0px 0px 30px; padding: 0px; word-break: break-all; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><li style="margin: 0px 0px 1em; padding: 0px; list-style: disc;"><span style="margin: 0px; padding: 0px;"><code style="margin: 0px; padding: 0px;">innodb_compression_levelQ决定压~程度的参数Q如果你讄比较大,那么压羃比较多,耗费的CPU资源也较多;相反Q如果设|较的|那么CPU占用。默认?Q可以设|?-9</code></span></li><li style="margin: 0px 0px 1em; padding: 0px; list-style: disc;"><span style="margin: 0px; padding: 0px; font-size: 15px;"><span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px;"><code style="margin: 0px; padding: 0px;">innodb_compression_failure_threshold_pctQ默认?Q范??00.讄中断炚w免高昂的压羃p|率?/code></span></span></span></li><li style="margin: 0px 0px 1em; padding: 0px; list-style: disc;"><span style="margin: 0px; padding: 0px; font-size: 15px;"><span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px;"><code style="margin: 0px; padding: 0px;">innodb_compression_pad_pct_maxQ?/code></span></span></span>指定在每个压~页面可以作为空闲空间的最大比例,该参C仅应用在讄了i</span><span style="margin: 0px; padding: 0px; font-size: 15px;"><span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px;"><code style="margin: 0px; padding: 0px;">nnodb_compression_failure_threshold_pct不ؓ零情况下Qƈ且压~失败率通过了中断点。默认?0Q可以设|范围是0?5.</code></span></span></span></li></ul><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 18px;"><strong style="margin: 0px; padding: 0px;">【第二部分】实验:</strong></span></p><div style="margin: 5px 0px; font-size: 12px !important;"><div style="margin: 5px 0px 0px;"><span style="margin: 0px; padding: 0px 5px 0px 0px; line-height: 1.5 !important;"><a title="复制代码" style="margin: 0px; padding: 0px; text-decoration-line: underline; border: none !important;"><img src="https://common.cnblogs.com/images/copycode.gif" alt="复制代码" style="margin: 0px; padding: 0px; height: auto; max-width: 820px; border: none !important;" /></a></span></div><pre style="margin-top: 0px; margin-bottom: 0px; padding: 0px; word-wrap: break-word; white-space: pre-wrap; font-family: "Courier New" !important;"><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;">#没有讄压羃前的数据大小 </span><span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">-</span>rw<span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">-</span>rw<span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">--</span><span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">--. 1 mysql mysql 368M 12?29 11:05 test.ibd</span> #讄KEY_BLOCK_SIZE<span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">=</span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">1</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> (product)root</span><span style="margin: 0px; padding: 0px; color: #008000; line-height: 1.5 !important;">@localhost</span> <span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">[</span><span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">sakila</span><span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">]</span><span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">></span> <span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">alter</span> <span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">table</span> test KEY_BLOCK_SIZE<span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">=</span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">1</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;">; Query OK, </span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span> rows affected (<span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">14</span> <span style="margin: 0px; padding: 0px; color: #ff00ff; line-height: 1.5 !important;">min</span> <span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">49.30</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> sec) Records: </span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span> Duplicates: <span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span> Warnings: <span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">-</span>rw<span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">-</span>rw<span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">--</span><span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">--. 1 mysql mysql 204M 1? 11 21:43 test.ibd <strong style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">#####压羃?4.5%</span></strong><br style="margin: 0px; padding: 0px;" /></span> #讄KEY_BLOCK_SIZE<span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">=</span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">2</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> (product)root</span><span style="margin: 0px; padding: 0px; color: #008000; line-height: 1.5 !important;">@localhost</span> <span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">[</span><span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">sakila</span><span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">]</span><span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">></span> <span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">alter</span> <span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">table</span> test KEY_BLOCK_SIZE<span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">=</span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">2</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;">; Query OK, </span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span> rows affected (<span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">9</span> <span style="margin: 0px; padding: 0px; color: #ff00ff; line-height: 1.5 !important;">min</span> <span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">55.60</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> sec) Records: </span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span> Duplicates: <span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span> Warnings: <span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">-</span>rw<span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">-</span>rw<span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">--</span><span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">--. 1 mysql mysql 180M 1? 12 13:40 test.ibd <span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"><strong style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">#####压羃?1%</span></strong></span></span> <span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> #讄KEY_BLOCK_SIZE</span><span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">=</span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">4</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> (product)root</span><span style="margin: 0px; padding: 0px; color: #008000; line-height: 1.5 !important;">@localhost</span> <span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">[</span><span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">sakila</span><span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">]</span><span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">></span> <span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">alter</span> <span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">table</span> test KEY_BLOCK_SIZE<span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">=</span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">4</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;">; Query OK, </span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span> rows affected (<span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">7</span> <span style="margin: 0px; padding: 0px; color: #ff00ff; line-height: 1.5 !important;">min</span> <span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">24.52</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> sec) Records: </span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span> Duplicates: <span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span> Warnings: <span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">-</span>rw<span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">-</span>rw<span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">--</span><span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">--. 1 mysql mysql 172M 1? 11 21:09 test.ibd <span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"><strong style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">#####压羃?3.2%</span></strong></span></span></span> <span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> #讄KEY_BLOCK_SIZE</span><span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">=</span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">8</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> (product)root</span><span style="margin: 0px; padding: 0px; color: #008000; line-height: 1.5 !important;">@localhost</span> <span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">[</span><span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">sakila</span><span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">]</span><span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">></span> <span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">alter</span> <span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">table</span> test KEY_BLOCK_SIZE<span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">=</span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">8</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;">; Query OK, </span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span> rows affected (<span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">5</span> <span style="margin: 0px; padding: 0px; color: #ff00ff; line-height: 1.5 !important;">min</span> <span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">16.34</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> sec) Records: </span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span> Duplicates: <span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span> Warnings: <span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">-</span>rw<span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">-</span>rw<span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">--</span><span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">--. 1 mysql mysql 172M 1? 11 21:00 test.ibd <span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"><strong style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">#####压羃?3.2%</span></strong></span></span></span></span> <span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> #讄KEY_BLOCK_SIZE</span><span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">=</span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">16</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> (product)root</span><span style="margin: 0px; padding: 0px; color: #008000; line-height: 1.5 !important;">@localhost</span> <span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">[</span><span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">sakila</span><span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">]</span><span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">></span> <span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">alter</span> <span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">table</span> test KEY_BLOCK_SIZE<span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">=</span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">16</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;">; Query OK, </span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span> rows affected (<span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">2</span> <span style="margin: 0px; padding: 0px; color: #ff00ff; line-height: 1.5 !important;">min</span> <span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">47.48</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> sec) Records: </span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span> Duplicates: <span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span> Warnings: <span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">-</span>rw<span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">-</span>rw<span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">--</span><span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">--. 1 mysql mysql 336M 1? 12 13:54 test.ibd <span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"><strong style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">#####压羃?.6%</span></strong></span></span></span></span> </span> </pre><div style="margin: 5px 0px 0px;"><span style="margin: 0px; padding: 0px 5px 0px 0px; line-height: 1.5 !important;"><a title="复制代码" style="margin: 0px; padding: 0px; text-decoration-line: underline; border: none !important;"><img src="https://common.cnblogs.com/images/copycode.gif" alt="复制代码" style="margin: 0px; padding: 0px; height: auto; max-width: 820px; border: none !important;" /></a></span></div></div><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; background-color: #ffffff;"><strong style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px; color: #ff0000;">【ȝ】:通过以上试可知Q当KEY_BLOCK_SIZE的D|ؓ4或?Ӟ压羃效果最好,讄?6效果最差,因ؓ늚默认?6K。通常我是讄??/span></strong></p><img src ="//www.pppqb.icu/markqian86/aggbug/216684.html" width = "1" height = "1" /><br><br><div align=right><a style="text-decoration:none;" href="//www.pppqb.icu/markqian86/" target="_blank">长戟十三?/a> 2019-08-14 15:31 <a href="//www.pppqb.icu/markqian86/archive/2019/08/14/216684.html#Feedback" target="_blank" style="text-decoration:none;">发表评论</a></div>]]></description></item><item><title>Innodb表压~过E中遇到的坑Qinnodb_file_formatQ?/title><link>//www.pppqb.icu/markqian86/archive/2019/08/14/216683.html</link><dc:creator>长戟十三?/dc:creator><author>长戟十三?/author><pubDate>Wed, 14 Aug 2019 07:24:00 GMT</pubDate><guid>//www.pppqb.icu/markqian86/archive/2019/08/14/216683.html</guid><wfw:comment>//www.pppqb.icu/markqian86/comments/216683.html</wfw:comment><comments>//www.pppqb.icu/markqian86/archive/2019/08/14/216683.html#Feedback</comments><slash:comments>0</slash:comments><wfw:commentRss>//www.pppqb.icu/markqian86/comments/commentRss/216683.html</wfw:commentRss><trackback:ping>//www.pppqb.icu/markqian86/services/trackbacks/216683.html</trackback:ping><description><![CDATA[<div class="dpun"><div style="display: inline-block;"></div></div> <div style="margin: 5px 0px; font-size: 12px !important;"><pre style="margin-top: 0px; margin-bottom: 0px; padding: 0px; word-wrap: break-word; white-space: pre-wrap; font-family: "Courier New" !important;"><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> Row_format: <span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;"><strong style="margin: 0px; padding: 0px;">Compressed</strong></span> Rows: </span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> Avg_row_length: </span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> Data_length: </span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">8192</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> Max_data_length: </span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> Index_length: </span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> Data_free: </span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> Auto_increment: </span><span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">NULL</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> Create_time: </span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">2013</span><span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">-</span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">09</span><span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">-</span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">27</span> <span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">16</span>:<span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">09</span>:<span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">51</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> Update_time: </span><span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">NULL</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> Check_time: </span><span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">NULL</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> Collation: utf8_general_ci Checksum: </span><span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">NULL</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> Create_options: row_format</span><span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">=</span>COMPRESSED KEY_BLOCK_SIZE<span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">=</span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">8</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> Comment: </span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">1</span> row <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">in</span> <span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">set</span> (<span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0.00</span> sec)</pre><div style="margin: 5px 0px 0px;"><span style="margin: 0px; padding: 0px 5px 0px 0px; line-height: 1.5 !important;"><a title="复制代码" style="margin: 0px; color: green; padding: 1px 3px; border: none !important;"><img src="https://common.cnblogs.com/images/copycode.gif" alt="复制代码" style="margin: 0px; padding: 0px; height: auto; max-width: 800px; border: none !important;" /></a></span></div></div><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13.92px; background-color: #ffffff;"> </p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13.92px; background-color: #ffffff;">No3:</p><hr style="margin: 0px; padding: 0px; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13.92px; background-color: #ffffff;" /><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13.92px; background-color: #ffffff;"> </p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13.92px; background-color: #ffffff;">发现和innodb_file_format相关?个参?</p><div style="margin: 5px 0px; font-size: 12px !important;"><div style="margin: 5px 0px 0px;"><span style="margin: 0px; padding: 0px 5px 0px 0px; line-height: 1.5 !important;"><a title="复制代码" style="margin: 0px; color: green; padding: 1px 3px; border: none !important;"><img src="https://common.cnblogs.com/images/copycode.gif" alt="复制代码" style="margin: 0px; padding: 0px; height: auto; max-width: 800px; border: none !important;" /></a></span></div><pre style="margin-top: 0px; margin-bottom: 0px; padding: 0px; word-wrap: break-word; white-space: pre-wrap; font-family: "Courier New" !important;"><span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">+</span><span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">--</span><span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">------------------------+-----------+</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> Variable_name <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> Value <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">+</span><span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">--</span><span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">------------------------+-----------+</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> innodb_file_format <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> Barracuda <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> innodb_file_format_check <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> <span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">ON</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> innodb_file_format_max <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> Barracuda <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">+</span><span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">--</span><span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">------------------------+-----------+</span> <span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">3</span> rows <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">in</span> <span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">set</span> (<span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0.00</span> sec)</pre><div style="margin: 5px 0px 0px;"><span style="margin: 0px; padding: 0px 5px 0px 0px; line-height: 1.5 !important;"><a title="复制代码" style="margin: 0px; color: green; padding: 1px 3px; border: none !important;"><img src="https://common.cnblogs.com/images/copycode.gif" alt="复制代码" style="margin: 0px; padding: 0px; height: auto; max-width: 800px; border: none !important;" /></a></span></div></div><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13.92px; background-color: #ffffff;">官方的解释可以参考如下的链接Q//dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_format</p><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13.92px; background-color: #ffffff;">试q程中发玎ͼ如果是innodb_file_format=barracuda而innodb_file_format_max=antelopQ那么在建立压羃表的时候,max会自动变成barracuda?/p><div style="margin: 5px 0px; font-size: 12px !important;"><div style="margin: 5px 0px 0px;"><span style="margin: 0px; padding: 0px 5px 0px 0px; line-height: 1.5 !important;"><a title="复制代码" style="margin: 0px; color: green; padding: 1px 3px; border: none !important;"><img src="https://common.cnblogs.com/images/copycode.gif" alt="复制代码" style="margin: 0px; padding: 0px; height: auto; max-width: 800px; border: none !important;" /></a></span></div><pre style="margin-top: 0px; margin-bottom: 0px; padding: 0px; word-wrap: break-word; white-space: pre-wrap; font-family: "Courier New" !important;">localhost.test<span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">></span>show global variables <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">like</span> <span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">'</span><span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">innodb_file_format%</span><span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">'</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;">; </span><span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">+</span><span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">--</span><span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">------------------------+-----------+</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> Variable_name <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> Value <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">+</span><span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">--</span><span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">------------------------+-----------+</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> innodb_file_format <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> Barracuda <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> innodb_file_format_check <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> <span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">ON</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> innodb_file_format_max <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> <strong style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">Antelope</span></strong> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">+</span><span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">--</span><span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">------------------------+-----------+</span> <span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">3</span> rows <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">in</span> <span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">set</span> (<span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0.00</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> sec) localhost.test</span><span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">></span><span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">create</span> <span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">table</span> test_4(x <span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">int</span>) ROW_FORMAT<span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">=</span>COMPRESSED KEY_BLOCK_SIZE<span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">=</span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">8</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;">;</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> Query OK, </span><span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0</span> rows affected (<span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0.01</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;"> sec) localhost.test</span><span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">></span>show global variables <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">like</span> <span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">'</span><span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">innodb_file_format%</span><span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;">'</span><span style="margin: 0px; padding: 0px; line-height: 1.5 !important;">; </span><span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">+</span><span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">--</span><span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">------------------------+-----------+</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> Variable_name <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> Value <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">+</span><span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">--</span><span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">------------------------+-----------+</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> innodb_file_format <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> Barracuda <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> innodb_file_format_check <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> <span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">ON</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> innodb_file_format_max <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> <span style="margin: 0px; padding: 0px; color: #ff0000; line-height: 1.5 !important;"><strong style="margin: 0px; padding: 0px;">Barracuda</strong></span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">|</span> <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">+</span><span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">--</span><span style="margin: 0px; padding: 0px; color: #008080; line-height: 1.5 !important;">------------------------+-----------+</span> <span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">3</span> rows <span style="margin: 0px; padding: 0px; color: #808080; line-height: 1.5 !important;">in</span> <span style="margin: 0px; padding: 0px; color: #0000ff; line-height: 1.5 !important;">set</span> (<span style="margin: 0px; padding: 0px; color: #800000; font-weight: bold; line-height: 1.5 !important;">0.00</span> sec)</pre><div style="margin: 5px 0px 0px;"><span style="margin: 0px; padding: 0px 5px 0px 0px; line-height: 1.5 !important;"><a title="复制代码" style="margin: 0px; color: green; padding: 1px 3px; border: none !important;"><img src="https://common.cnblogs.com/images/copycode.gif" alt="复制代码" style="margin: 0px; padding: 0px; height: auto; max-width: 800px; border: none !important;" /></a></span></div></div><p style="padding: 0px; margin-top: 10px; margin-bottom: 10px; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13.92px; background-color: #ffffff;">如果innodb_file_format_checkq参数解释的Q决定innodb是否会检查共享表I间中的表格式的tagQ如果检查开启,那么当标记的表格式的tag高于innodb可以支撑的表格式Q那么innodb会报错,q停止启动。如果支持,那么会将innodb_file_format_max的值改个tag的倹{?/p><img src ="//www.pppqb.icu/markqian86/aggbug/216683.html" width = "1" height = "1" /><br><br><div align=right><a style="text-decoration:none;" href="//www.pppqb.icu/markqian86/" target="_blank">长戟十三?/a> 2019-08-14 15:24 <a href="//www.pppqb.icu/markqian86/archive/2019/08/14/216683.html#Feedback" target="_blank" style="text-decoration:none;">发表评论</a></div>]]></description></item><item><title>MySQL: Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB - սƵ2019|սع//www.pppqb.icu/markqian86/archive/2019/08/08/216653.html长戟十三?/dc:creator>长戟十三?/author>Thu, 08 Aug 2019 10:11:00 GMT//www.pppqb.icu/markqian86/archive/2019/08/08/216653.html//www.pppqb.icu/markqian86/comments/216653.html//www.pppqb.icu/markqian86/archive/2019/08/08/216653.html#Feedback0//www.pppqb.icu/markqian86/comments/commentRss/216653.html//www.pppqb.icu/markqian86/services/trackbacks/216653.html 解决办法Q?br />vim /etc/my.cnfQ根据实际情况进行参数调_
[mysqld]
max_allowed_packet = 1G
innodb_log_file_size = 30M
innodb_log_buffer_size = 512M
innodb_file_format='Barracuda'


alter table 'role' row_format=dynamic
修改之后Q重启mysql服务?nbsp;

mysql文
https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-20.html
In MySQL 5.6.22, the redo log BLOB write limit is relaxed to 10% of the total redo log size (innodb_log_file_size * innodb_log_files_in_group).



# For advice on how to change settings please see
# //dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
#server_id = 1
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
port=3306
basedir=/data/mysql
datadir=/data/mysql/mysqldata
log-error=/data/mysql/log
socket=/tmp/mysqld.sock
server_id=1
back_log=2048
log-bin=mysql-bin 
bind-address=0.0.0.0
character-set-server=utf8
collation-server=utf8_general_ci
skip-external-locking
skip-name-resolve
query_cache_type=0
max_connections=2000
max_connect_errors=1000000
default-storage-engine=INNODB
innodb_buffer_pool_size=12884901888
innodb_concurrency_tickets=5000
innodb_flush_method=O_DIRECT
innodb_io_capacity=2000
innodb_io_capacity_max=4000
innodb_log_buffer_size=1048576
innodb_log_file_size=1048576000
innodb_log_files_in_group=2
innodb_file_format=Barracuda
innodb_file_per_table=ON
innodb_file_format_max=Antelope
innodb_max_dirty_pages_pct=75
innodb_open_files=3000
innodb_additional_mem_pool_size=20M
innodb_sort_buffer_size=8388608
join_buffer_size=262144
key_buffer_size=16777216
preload_buffer_size=32768
read_buffer_size=262144
read_rnd_buffer_size=262144
sort_buffer_size=262144
max_allowed_packet=1073741824
binlog_stmt_cache_size=32768
bulk_insert_buffer_size=4194304
key_buffer=64M
slow_query_log=1
slow_query_log_file=/data/mysql/log
long_query_time=5
sync_frm=ON
max_binlog_size=524288000
sync_binlog=1000
binlog_format=ROW
binlog_cache_size=2097152
expire_logs_days=3
tmpdir=/data/mysql/tmpdir
max_tmp_tables=32
tmp_table_size=262144
open_files_limit=65535
table_definition_cache=512
table_open_cache=100
default_storage_engine=InnoDB
default_tmp_storage_engine=InnoDB
log_queries_not_using_indexes=ON
interactive_timeout=7200
lock_wait_timeout=31536000
wait_timeout=86400
connect_timeout=120
pid_file=/data/mysql/mysqldata/mysqld.pid
slave-skip-errors=1062,1053,1146,1061

[mysql]
port=3306
socket=/tmp/mysqld.sock
default-character-set=utf8


]]>
__attribute__ ȝ - սƵ2019|սع//www.pppqb.icu/markqian86/archive/2019/01/26/216222.html长戟十三?/dc:creator>长戟十三?/author>Sat, 26 Jan 2019 07:28:00 GMT//www.pppqb.icu/markqian86/archive/2019/01/26/216222.html//www.pppqb.icu/markqian86/comments/216222.html//www.pppqb.icu/markqian86/archive/2019/01/26/216222.html#Feedback0//www.pppqb.icu/markqian86/comments/commentRss/216222.html//www.pppqb.icu/markqian86/services/trackbacks/216222.html阅读全文

]]>