PostgreSQL長事務(wù)與失效的索引查詢淺析介紹
最近剛寫了一篇文章介紹了下長事務(wù),以及一些長事務(wù)常見的危害,如無法及時的垃圾回收導(dǎo)致表膨脹之類的問題,最近剛好又碰到一個問題也是長事務(wù)所導(dǎo)致的。
上周六早上接到同事電話,說某個庫CPU一直很高,看了下全是某張大表的全表掃描導(dǎo)致,但是奇怪的是相關(guān)的查詢都有用到索引列,不知道為啥查詢?nèi)慷紱]走索引。
當(dāng)我連上去查看時發(fā)現(xiàn)確實如此,如果只是某個查詢不走索引那可能是SQL本身寫的有問題,但是這張表相關(guān)的所有SQL都不走索引,那自然會想到是索引本身的原因了。那是不是索引失效了呢?經(jīng)過檢查發(fā)現(xiàn)這張表上的索引狀態(tài)均正常,并且我還將索引重建了,可仍然沒起作用。
正當(dāng)我迷茫的時候,偶然間再去執(zhí)行相關(guān)SQL的時候發(fā)現(xiàn)竟然又都走索引了,這又是啥情況。。
我啥都沒做你就自己恢復(fù)了,那不是顯得我很呆?不行,必須得搞清楚啥原因。
當(dāng)我再去查看相關(guān)索引的時候發(fā)現(xiàn),該索引的pg_index中的indcheckxmin列均為true,這個字段我之前有寫過一篇索引失效的文章里介紹過。那么什么情況下索引的該屬性會被設(shè)置為true呢?兩種情況:
- 當(dāng)前事務(wù)中表上存在broken HOT chains;
- 當(dāng)old_snapshot_threshold被設(shè)置時。
之前我們也介紹過,如果索引的該屬性為true那么在創(chuàng)建索引的事務(wù)中該索引是不可用的,不過這種場景我們基本不太會遇到,因為在實際應(yīng)用中我們基本不會在事務(wù)中創(chuàng)建完索引然后不提交該事務(wù)直接去使用。
而關(guān)于indcheckxmin的詳細(xì)解釋是:直到此pg_index行的xmin低于查詢的TransactionXmin之前,查詢都不能使用此索引。那么什么情況下會出現(xiàn)這種問題呢?長事務(wù)!
當(dāng)我們創(chuàng)建索引的時候如果索引的indcheckxmin被設(shè)置為true,且數(shù)據(jù)庫中此時存在長事務(wù),那么直到該長事務(wù)提交前,該索引會一直不可用。
下面我們來模擬這種情況:
--會話一:打開一個長事務(wù)
bill=# begin; BEGIN bill=*# delete from t; DELETE 1000 bill=*#
--會話二:創(chuàng)建索引
由于old_snapshot_threshold參數(shù)被設(shè)置,所以創(chuàng)建的索引indcheckxmin被設(shè)置為true了。
bill=# show old_snapshot_threshold ; old_snapshot_threshold ------------------------ 1h (1 row) bill=# create index idx_t1 on t1(id); CREATE INDEX bill=# select indisvalid,indcheckxmin,indisready,indislive from pg_index where indexrelid = 'idx_t1'::regclass; indisvalid | indcheckxmin | indisready | indislive ------------+--------------+------------+----------- t | t | t | t (1 row)
使用該索引列進(jìn)行查詢:
索引的確無法使用。
bill=# explain analyze select * from t1 where id = 100; QUERY PLAN ------------------------------------------------------------------------------------------------- Seq Scan on t1 (cost=0.00..209.00 rows=51 width=37) (actual time=0.010..0.692 rows=51 loops=1) Filter: (id = 100) Rows Removed by Filter: 9949 Planning Time: 0.150 ms Execution Time: 0.706 ms (5 rows) bill=# set enable_seqscan = off; SET bill=# explain analyze select * from t1 where id = 100; QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Seq Scan on t1 (cost=10000000000.00..10000000209.00 rows=51 width=37) (actual time=0.063..0.732 rows=51 loops=1) Filter: (id = 100) Rows Removed by Filter: 9949 Planning Time: 0.089 ms Execution Time: 0.796 ms (5 rows)
提交該長事務(wù)后再次查詢:
索引變得可用了。
bill=# explain analyze select * from t1 where id = 100; QUERY PLAN --------------------------------------------------------------------------------------------------------------- Index Scan using idx_t1 on t1 (cost=0.29..54.48 rows=51 width=37) (actual time=0.013..0.052 rows=51 loops=1) Index Cond: (id = 100) Planning Time: 0.061 ms Execution Time: 0.067 ms (4 rows)
果然是長事務(wù)的坑??!
由于我們的庫中基本都打開了old_snapshot_threshold參數(shù),導(dǎo)致創(chuàng)建的索引的indcheckxmin一定是true。但這其實并不會有什么太大影響,問題在于在創(chuàng)建索引的同時數(shù)據(jù)庫中存在長事務(wù),這就導(dǎo)致了索引在創(chuàng)建完之后第一時間變得不可用了,需要直到該長事務(wù)被提交后才可用。
后來和同事求證發(fā)現(xiàn)他們之前也經(jīng)常碰到這種CPU變高然后又自己降下來的情況,之前并沒有注意是這張表的全表掃描導(dǎo)致的。由于該表是由pg_pathman創(chuàng)建的自動分區(qū),每天都會自己去創(chuàng)建一個新的分區(qū),因此如果每天自動創(chuàng)建分區(qū)的時候存在長事務(wù),那么創(chuàng)建完之后相關(guān)的分區(qū)上的索引均是不可用的,這也是為什么查詢不走索引然后又自己恢復(fù)的原因了。
這個案例其實我們在實際生產(chǎn)中遇到的可能性還是很大的,如果你的數(shù)據(jù)庫打開了old_snapshot_threshold參數(shù),同時沒有做好長事務(wù)的監(jiān)控,那么創(chuàng)建的索引就會出現(xiàn)這種不可用的情況。長事務(wù)危害不淺啊!
到此這篇關(guān)于PostgreSQL長事務(wù)與失效的索引查詢淺析介紹的文章就介紹到這了,更多相關(guān)PostgreSQL長事務(wù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
PostgreSQL中數(shù)據(jù)批量導(dǎo)入導(dǎo)出的錯誤處理
在 PostgreSQL 中進(jìn)行數(shù)據(jù)的批量導(dǎo)入導(dǎo)出是常見的操作,但有時可能會遇到各種錯誤,下面將詳細(xì)探討可能出現(xiàn)的錯誤類型、原因及相應(yīng)的解決方案,并提供具體的示例來幫助您更好地理解和處理這些問題,需要的朋友可以參考下2024-07-07使用PostgreSQL數(shù)據(jù)庫建立用戶畫像系統(tǒng)的方法
這篇文章主要介紹了使用PostgreSQL數(shù)據(jù)庫建立用戶畫像系統(tǒng),下面使用一個具體的例子來說明如何使用PostgreSQL的json數(shù)據(jù)類型來建立用戶標(biāo)簽數(shù)據(jù),需要的朋友可以參考下2022-10-10postgresql 實現(xiàn)獲取所有表名,字段名,字段類型,注釋
這篇文章主要介紹了postgresql 實現(xiàn)獲取所有表名,字段名,字段類型,注釋操作,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01postgresql 如何查看pg_wal目錄下xlog文件總大小
這篇文章主要介紹了postgresql 如何查看pg_wal目錄下xlog文件總大小的操作,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01PostgreSQL存儲過程循環(huán)調(diào)用方式
這篇文章主要介紹了PostgreSQL存儲過程循環(huán)調(diào)用方式,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01