从show innodb status看XtraDB的增强特性

XtraDB存储引擎相对于标准的InnoDB引擎做了很多的改进,从show innodb status的结果可以初步看到部分增强的特性。下面是一个XtraDB测试环境(Linux)的结果:

root@(none) 11:25:18>show innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
091106 11:26:11 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 53 seconds
----------
BACKGROUND THREAD
----------
srv_master_thread loops: 8 1_second, 8 sleeps, 0 10_second, 15 background, 15 flush
srv_master_thread log flush and writes: 8  log writes only: 39
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 9, signal count 9
Mutex spin waits 2, rounds 60, OS waits 0
RW-shared spins 9, OS waits 9; RW-excl spins 0, OS waits 0
Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 0.00 RW-excl
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
25 OS file reads, 48 OS file writes, 32 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.09 writes/s, 0.08 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 553193, node heap has 1 buffer(s)
0.00 hash searches/s, 0.02 non-hash searches/s
---
LOG
---
Log sequence number 48613
Log flushed up to   48613
Last checkpoint at  48613
Max checkpoint age  7782360
Modified age        0
Checkpoint age      0
0 pending log writes, 0 pending chkp writes
24 log i/o's done, 0.04 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 136806400; in additional pool allocated 0
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 2233316         (2212772 + 20544)
    Page hash           69556
    Dictionary cache    576800  (553912 + 22888)
    File system         41904   (41336 + 568)
    Lock system         167076  (166436 + 640)
    Recovery system     0       (0 + 0)
    Threads             41524   (41348 + 176)
Dictionary memory allocated 22888
Buffer pool size        8191
Buffer pool size, bytes 134201344
Free buffers            8170
Database pages          20
Modified db pages       0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 19, created 2, written 45
0.00 reads/s, 0.00 creates/s, 0.04 writes/s
Buffer pool hit rate 1000 / 1000
LRU len: 20, unzip_LRU len: 0
I/O sum[2]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 16905, id 2788809632, state: waiting for server activity
Number of rows inserted 4, updated 0, deleted 0, read 0
0.02 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
------------
TRANSACTIONS
------------
Trx id counter 509
Purge done for trx's n:o < 506 undo n:o < 0
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started, process no 16905, OS thread id 2777967520
MySQL thread id 6, query id 196 dxmonitor 172.19.68.250 root
show innodb status
---TRANSACTION 508, ACTIVE 2 sec, process no 16905, OS thread id 2777766816
1 lock struct(s), heap size 320, 0 row lock(s), undo log entries 1
MySQL thread id 7, query id 195 dxmonitor 172.19.68.250 root
TABLE LOCK table `test`.`test` trx id 508 lock mode IX
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (0.00 sec)

从上面的结果可以看到:

  • 增加了BACKGROUND THREAD部分,显示一些后台线程的统计信息
  • SEMAPHORES部分,最后增加了一个关于spin的统计信息。引入了一个新的innodb_spin_wait_delay的参数对spin的行为进行控制
  • FILE I/O部分,innodb默认开启4个IO线程,由参数innodb_file_io_threads控制,但在unix/linux平台该参数无效,只能是默认的4个。而XtraDB/innodb plugin默认开启8个IO线程,并且读线程和写线程可以分别由参数innodb_read_io_threadsinnodb_write_io_threads控制。在多核CPU环境中,如果CPU和磁盘本身没有成为瓶颈,开启更多线程应该对提升IO性能有很好的作用。而读写线程的单独控制,则可以针对不同应用环境下的要求进行灵活配置。
  • LOG部分,增加了max checkpoint age/Modified age/Checkpoint age信息,这些信息可用于调整一些IO相关的参数,具体可参考这里
  • BUFFER POOL AND MEMORY部分,XtraDB更加详细的列出buffer pool中不同部分的详细信息,还有buffer pool的命中率和LRU的信息等,对内存的使用情况一目了然,拒绝黑箱操作。

关于XtraDB的show innodb status的更多改进,请参考这里



无觅相关文章插件,快速提升流量

155条评论

  • At 2014.10.21 17:41, futuristic Jay Z remixes said:

    futuristic Jay Z remixes…

    » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

    • At 2014.10.22 00:31, hotels said:

      hotels…

      » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

      • At 2014.10.22 01:01, guangchaofayin.org said:

        guangchaofayin.org…

        » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

        • At 2014.10.22 03:05, nazwa.pl opinie 2014 said:

          nazwa.pl opinie 2014…

          » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

          • At 2014.10.22 05:24, alex and ani coupon said:

            alex and ani coupon…

            » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

            • At 2014.10.22 08:16, pir said:

              pir…

              » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

              • At 2014.10.22 09:24, DWG to PDF said:

                DWG to PDF…

                » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                • At 2014.10.22 09:34, original prada lenses said:

                  Dans sa d茅claration d’ouverture au jury le 13 janvier Huneault dit que la preuve soit pr茅sent茅e mettrait l’accent sur trois personnes Hunda, MacIsaac et Leggat.

                  • At 2014.10.22 10:23, farmhouse saga app said:

                    farmhouse saga app…

                    » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                    • At 2014.10.22 12:03, site said:

                      site…

                      » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                      • At 2014.10.22 13:39, Morley Tutoring said:

                        Morley Tutoring…

                        » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                        • At 2014.10.22 13:44, barnett crossbow accessories said:

                          barnett crossbow accessories…

                          » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                          • At 2014.10.22 14:08, vk.com said:

                            vk.com…

                            » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                            • At 2014.10.23 13:03, PDF Utilities said:

                              PDF Utilities…

                              » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                              • At 2014.10.24 04:41, web site said:

                                web site…

                                » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                                • купить анальные стимуляторы…

                                  » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                                  • At 2014.10.24 06:12, proper dental said:

                                    proper dental…

                                    » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                                    • At 2014.10.24 08:04, home based business tips said:

                                      home based business tips…

                                      » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                                      • At 2014.10.25 00:31, how to get rid of stretch marks for men said:

                                        how to get rid of stretch marks for men…

                                        » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                                        • comment pirater un compte facebook gratuitement…

                                          » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                                          • At 2014.10.31 04:01, more information said:

                                            more information…

                                            » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                                            • At 2014.11.01 11:37, online guitar lessons said:

                                              online guitar lessons…

                                              » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                                              • At 2014.11.04 20:29, Business Analyst internship said:

                                                Business Analyst internship…

                                                Barefoot BBS…

                                                • At 2014.11.05 05:52, คริปโป้ๆ said:

                                                  คริปโป้ๆ…

                                                  » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                                                  • At 2014.11.05 15:12, Free-Video-Joiner said:

                                                    Free-Video-Joiner…

                                                    » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                                                    • At 2014.11.06 05:29, cure back pain said:

                                                      cure back pain…

                                                      » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                                                      • At 2014.11.06 10:26, knock off michael kors said:

                                                        knock off michael kors…

                                                        » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                                                        • At 2014.11.06 13:43, compound bow hunting said:

                                                          compound bow hunting…

                                                          » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                                                          • At 2014.11.07 09:24, carsalejapan.com said:

                                                            carsalejapan.com…

                                                            » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                                                            • cheap-tickets-tour.com/steve-harvey-tickets.html…

                                                              » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                                                              • At 2014.11.08 05:47, frameless shower enclosure said:

                                                                frameless shower enclosure…

                                                                » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                                                                • At 2014.11.08 06:21, advice here said:

                                                                  advice here…

                                                                  » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                                                                  • At 2014.11.09 08:31, marega salaa full movie said:

                                                                    marega salaa full movie…

                                                                    » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                                                                    • A Background In Straightforward Loans With Bad Credit Plans…

                                                                      » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                                                                      • trajes de novia r7bT How to educate yourself regar…

                                                                        » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                                                                        • At 2014.11.12 20:45, learn more said:

                                                                          learn more…

                                                                          » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                                                                          • At 2014.11.13 00:18, how to kill bed bugs said:

                                                                            how to kill bed bugs…

                                                                            » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                                                                            • At 2014.11.13 03:53, robertwalter924.skyrock.com said:

                                                                              robertwalter924.skyrock.com…

                                                                              » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                                                                              • At 2014.11.14 15:47, facebook of sex.com said:

                                                                                facebook of sex.com…

                                                                                » 从show innodb status看XtraDB的增强特性 江边潮未尽,枫红一季秋 — NinGoo’s blog…

                                                                                • At 2017.04.02 17:07, Adeline said:

                                                                                  Thank you for the good writeup. It in treuth used to be a enjoyment
                                                                                  account it. Glance advanced to morfe introduced agreeable from you!
                                                                                  However, how could we keep in touch?

                                                                                  • At 2017.04.18 10:03, hats said:

                                                                                    Thanks John for the excellent tip, actually non-developers just like my family can take that off: )This is actually essential some sort of multi-international website on the subject of WEB OPTIMIZATION.

                                                                                    • At 2017.04.18 10:04, capsclub said:

                                                                                      Robert/Nick: Appreciate your suggestions. Outlined on our site have an interest to listen involving just about any gains towards your SEARCH ENGINE MARKETING campaigns after this can be put in place.

                                                                                      • Pretty great post. I simply stumbled upon your weblog and wanted to say that I
                                                                                        have truly enjoyed surfing around your blog posts.

                                                                                        In any case I will be subscribing on your feed and I am hoping you
                                                                                        write once more very soon!

                                                                                        • At 2018.07.27 00:47, Nadine said:

                                                                                          I am really loving the theme/design of yoyr blog.
                                                                                          Do you ever ruun into any internet browser compatibility problems?
                                                                                          A small number of my blog readers have complained about my blog not operating correctly
                                                                                          in Explorer but looks grea in Chrome. Do you have any tips tto help fix this issue?

                                                                                          • At 2018.08.05 13:32, Sendvid.com said:

                                                                                            You are offering a very important information. I’ll be your regular visitor.

                                                                                            • At 2018.10.21 03:33, Weheartit.com said:

                                                                                              Fantastic posts from you, man. I’ve understand your stuff and you are way too wonderful.

                                                                                              I actually like what you have here, especially like what
                                                                                              you’re saying and the way in which you say it. You make it
                                                                                              enjoyable. I can’t wait to find out more of your articles.
                                                                                              This is actually an awesome site.

                                                                                              • At 2018.10.21 16:35, granite countertops said:

                                                                                                You’re so great! I don’t suppose I can learn something like this before.
                                                                                                So good to find somebody with some unique ideas about this
                                                                                                subject. really thanks for bringing this up. this website is something that’s needed on the
                                                                                                net, somebody with somewhat creativity. excellent job for bringing something new online!

                                                                                                • At 2018.10.21 16:35, granite countertops said:

                                                                                                  You’re so great! I don’t suppose I can learn something like this before.
                                                                                                  So good to find somebody with some unique ideas
                                                                                                  about this subject. really thanks for bringing this up.
                                                                                                  this website is something that’s needed on the net, somebody with somewhat creativity.
                                                                                                  excellent job for bringing something new online!

                                                                                                  • At 2018.10.24 00:43, Mose said:

                                                                                                    Wow! This could be one of the most helpful blogs we’ve ever arrive across about
                                                                                                    this subject.Excellent. I’m also an expert in this
                                                                                                    topic therefore I can understand your effort.

                                                                                                    • At 2018.11.13 19:10, home cleaning franklin said:

                                                                                                      visit the following website page

                                                                                                      • 105 old comments are not displayed. Click to display all comments

                                                                                                      (Required)
                                                                                                      (Required, will not be published)