<ruby id="bdb3f"></ruby>

    <p id="bdb3f"><cite id="bdb3f"></cite></p>

      <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
        <p id="bdb3f"><cite id="bdb3f"></cite></p>

          <pre id="bdb3f"></pre>
          <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

          <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
          <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

          <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                <ruby id="bdb3f"></ruby>

                ThinkChat2.0新版上線,更智能更精彩,支持會話、畫圖、視頻、閱讀、搜索等,送10W Token,即刻開啟你的AI之旅 廣告
                **背景** 在上一期的月報中,我們在[InnoDB自增列重復值問題](http://mysql.taobao.org/index.php/MySQL%E5%86%85%E6%A0%B8%E6%9C%88%E6%8A%A5_2015.01#MySQL_.C2.B7_.E6.8D.89.E8.99.AB.E5.8A.A8.E6.80.81.C2.B7_InnoDB.E8.87.AA.E5.A2.9E.E5.88.97.E9.87.8D.E5.A4.8D.E5.80.BC.E9.97.AE.E9.A2.98)?中提到,InnoDB 自增列在重啟后會丟失,因為MySQL沒有持久化自增值,平時是存在內存表對象中的。如果實例重啟的話,內存值丟失,其初始化過程是做了一個類似 select max(id) + 1 操作。實際上存在另外一種場景,實例即使不重啟,也會導致自增值丟失。 **問題說明** 實例運行過種中,InnoDB表自增值是存儲在表對象中的,表對象又是放在緩存中的,如果表太多而不能全部放在緩存中的話,老的表就會被置換出來,這種被置換出來的表下次再使用的時候,就要重新打開一遍,對自增列來說,這個過程就和實例重啟類似,需要 select max(id) + 1 算一下自增值。 對InnoDB來說,其數據字典中表對象緩存大小由?[table_definition_cache](http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_table_definition_cache)?系統變量控制,在5.6.8之后,其最小值是400。和表緩存相關的另一個系統變量是[table_open_cache](http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_table_definition_cache),這個控制的是所有線程打開表的緩存大小,這個緩存放在server層。 下面我們用testcase的方式來給出InnoDB表對象對置換出的場景: ~~~ ##把 table_definition_cache 和 table_open_cache 都設為400 SET GLOBAL table_definition_cache = 400; SET GLOBAL table_open_cache = 400; ## 創建500個InnoDB自增表,各插入一條數據,然后把自增改為100 let $i=0; while($i < 500) { --eval CREATE TABLE t$i(id INT NOT NULL AUTO_INCREMENT, name VARCHAR(30), PRIMARY KEY(id)) ENGINE=InnoDB; --eval INSERT INTO t$i(name) VALUES("InnoDB"); --eval ALTER TABLE t$i AUTO_INCREMENT = 100; --inc $i } ## 最后400張表掃一遍 let $i=100; while($i < 500) { --eval SELECT * FROM t$i; --inc $i } ## 稍微sleep下,等mysqld把不用的表(t0..t99)換出 sleep 5; ## 查看t1表自增 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 ... ~~~ 可以看到自增值確實和重啟場景一樣,本應是100,卻變成了 2(select max(id) + 1)了。 **問題分析** 原因就是緩存不夠,導致表對象被換出,下次再用就要重新打開,這里給出調用棧,對代碼感興趣的同學可以看下。 將老的table置換出: ~~~ #0 dict_table_remove_from_cache_low (table=0x2b81d054e278, lru_evict=1) at /path/to/mysql/storage/innobase/dict/dict0dict.cc:1804 #1 0x00000000011cf246 in dict_make_room_in_cache (max_tables=400, pct_check=100) at /path/to/mysql/storage/innobase/dict/dict0dict.cc:1261 #2 0x0000000001083564 in srv_master_evict_from_table_cache (pct_check=100) at /path/to/mysql/storage/innobase/srv/srv0srv.cc:2017 #3 0x0000000001084022 in srv_master_do_idle_tasks () at /path/to/mysql/storage/innobase/srv/srv0srv.cc:2212 #4 0x000000000108484a in srv_master_thread (arg=0x0) at /path/to/mysql/storage/innobase/srv/srv0srv.cc:2360 #5 0x00000030cc007851 in start_thread () from /lib64/libpthread.so.0 #6 0x00000030cbce767d in clone () from /lib64/libc.so.6 ~~~ 嘗試從緩存加載表對象: ~~~ #0 dict_table_check_if_in_cache_low (table_name=0x2adef847db20 "test/t1") at /path/to/mysql/storage/innobase/include/dict0priv.ic:114 #1 0x00000000011cd51a in dict_table_open_on_name (table_name=0x2adef847db20 "test/t1", dict_locked=0, try_drop=1, ignore_err=DICT_ERR_IGNORE_NONE) at /path/to/mysql/storage/innobase/dict/dict0dict.cc:947 #2 0x0000000000e58d8a in ha_innobase::open (this=0x2adef9747010, name=0x2adef7460780 "./test/t1", mode=2, test_if_locked=2) at /path/to/mysql/storage/innobase/handler/ha_innodb.cc:4776 #3 0x000000000068668b in handler::ha_open (this=0x2adef9747010, table_arg=0x2adef742bc00, name=0x2adef7460780 "./test/t1", mode=2, test_if_locked=2) at /path/to/mysql/sql/handler.cc:2525 ... #9 0x00000000009c2a84 in mysqld_show_create (thd=0x2adef47aa000, table_list=0x2adef74200f0) at /path/to/mysql/sql/sql_show.cc:867 #10 0x00000000009553b1 in mysql_execute_command (thd=0x2adef47aa000) at /path/to/mysql/sql/sql_parse.cc:3507 #11 0x0000000000963bbe in mysql_parse (thd=0x2adef47aa000, rawbuf=0x2adef7420010 "show create table t1", length=20, parser_state=0x2adef8480630) at /path/to/mysql/sql/sql_parse.cc:6623 ... ~~~ 緩存加載不到表對象,用select maxt 邏輯初始化自增: ~~~ #0 row_search_max_autoinc (index=0x2b241d8f50f8, col_name=0x2b241d855519 "id", value=0x2b241e87d8a8) at /path/to/mysql/storage/innobase/row/row0sel.cc:5361 #1 0x0000000000e58998 in ha_innobase::innobase_initialize_autoinc (this=0x2b241fbd9010) at /path/to/mysql/storage/innobase/handler/ha_innodb.cc:4663 #2 0x0000000000e59bd9 in ha_innobase::open (this=0x2b241fbd9010, name=0x2b241d853780 "./test/t1", mode=2, test_if_locked=2) at /path/to/mysql/storage/innobase/handler/ha_innodb.cc:5089 #3 0x000000000068668b in handler::ha_open (this=0x2b241fbd9010, table_arg=0x2b241e422000, name=0x2b241d853780 "./test/t1", mode=2, test_if_locked=2) at /path/to/mysql/sql/handler.cc:2525 ... #9 0x00000000009c2a84 in mysqld_show_create (thd=0x2b241abaa000, table_list=0x2b241d8200f0) at /path/to/mysql/sql/sql_show.cc:867 #10 0x00000000009553b1 in mysql_execute_command (thd=0x2b241abaa000) at /path/to/mysql/sql/sql_parse.cc:3507 #11 0x0000000000963bbe in mysql_parse (thd=0x2b241abaa000, rawbuf=0x2b241d820010 "show create table t1", length=20, parser_state=0x2b241e880630) at /path/to/mysql/sql/sql_parse.cc:6623 ... ~~~ **處理建議** 對于這個問題,一種解決方法是從源碼改進,將自增值持久化,可以參考[上期的月報](http://mysql.taobao.org/index.php/MySQL%E5%86%85%E6%A0%B8%E6%9C%88%E6%8A%A5_2015.01#MySQL_.C2.B7_.E6.8D.89.E8.99.AB.E5.8A.A8.E6.80.81.C2.B7_InnoDB.E8.87.AA.E5.A2.9E.E5.88.97.E9.87.8D.E5.A4.8D.E5.80.BC.E9.97.AE.E9.A2.98)給出的思路;如果不想改代碼的話,可以這樣繞過:在設定auto_increment值后,主動插入一行記錄,這樣不論在重啟還是緩存淘汰的情況下,重新打開表仍能得到預期的值。
                  <ruby id="bdb3f"></ruby>

                  <p id="bdb3f"><cite id="bdb3f"></cite></p>

                    <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
                      <p id="bdb3f"><cite id="bdb3f"></cite></p>

                        <pre id="bdb3f"></pre>
                        <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

                        <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
                        <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

                        <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                              <ruby id="bdb3f"></ruby>

                              哎呀哎呀视频在线观看