欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

PostgreSQL長(zhǎng)事務(wù)與失效的索引查詢淺析介紹

 更新時(shí)間:2022年09月16日 10:35:38   作者:foucus、  
pg中的長(zhǎng)事務(wù)會(huì)影響表中垃圾回收,導(dǎo)致表的年齡增長(zhǎng)無(wú)法freeze。能消耗事務(wù)的只有當(dāng)執(zhí)行了一些DML或者DDL操作后才能算是我們通常說(shuō)的長(zhǎng)事務(wù)。否則只能算是我們常說(shuō)的長(zhǎng)連接,當(dāng)然長(zhǎng)連接也有很多弊端,例如占用內(nèi)存、cpu等資源

最近剛寫(xiě)了一篇文章介紹了下長(zhǎng)事務(wù),以及一些長(zhǎng)事務(wù)常見(jiàn)的危害,如無(wú)法及時(shí)的垃圾回收導(dǎo)致表膨脹之類(lèi)的問(wèn)題,最近剛好又碰到一個(gè)問(wèn)題也是長(zhǎng)事務(wù)所導(dǎo)致的。

上周六早上接到同事電話,說(shuō)某個(gè)庫(kù)CPU一直很高,看了下全是某張大表的全表掃描導(dǎo)致,但是奇怪的是相關(guān)的查詢都有用到索引列,不知道為啥查詢?nèi)慷紱](méi)走索引。

當(dāng)我連上去查看時(shí)發(fā)現(xiàn)確實(shí)如此,如果只是某個(gè)查詢不走索引那可能是SQL本身寫(xiě)的有問(wèn)題,但是這張表相關(guān)的所有SQL都不走索引,那自然會(huì)想到是索引本身的原因了。那是不是索引失效了呢?經(jīng)過(guò)檢查發(fā)現(xiàn)這張表上的索引狀態(tài)均正常,并且我還將索引重建了,可仍然沒(méi)起作用。

正當(dāng)我迷茫的時(shí)候,偶然間再去執(zhí)行相關(guān)SQL的時(shí)候發(fā)現(xiàn)竟然又都走索引了,這又是啥情況。。

我啥都沒(méi)做你就自己恢復(fù)了,那不是顯得我很呆?不行,必須得搞清楚啥原因。

當(dāng)我再去查看相關(guān)索引的時(shí)候發(fā)現(xiàn),該索引的pg_index中的indcheckxmin列均為true,這個(gè)字段我之前有寫(xiě)過(guò)一篇索引失效的文章里介紹過(guò)。那么什么情況下索引的該屬性會(huì)被設(shè)置為true呢??jī)煞N情況:

  1. 當(dāng)前事務(wù)中表上存在broken HOT chains;
  2. 當(dāng)old_snapshot_threshold被設(shè)置時(shí)。

之前我們也介紹過(guò),如果索引的該屬性為true那么在創(chuàng)建索引的事務(wù)中該索引是不可用的,不過(guò)這種場(chǎng)景我們基本不太會(huì)遇到,因?yàn)樵趯?shí)際應(yīng)用中我們基本不會(huì)在事務(wù)中創(chuàng)建完索引然后不提交該事務(wù)直接去使用。

而關(guān)于indcheckxmin的詳細(xì)解釋是:直到此pg_index行的xmin低于查詢的TransactionXmin之前,查詢都不能使用此索引。那么什么情況下會(huì)出現(xiàn)這種問(wèn)題呢?長(zhǎng)事務(wù)!

當(dāng)我們創(chuàng)建索引的時(shí)候如果索引的indcheckxmin被設(shè)置為true,且數(shù)據(jù)庫(kù)中此時(shí)存在長(zhǎng)事務(wù),那么直到該長(zhǎng)事務(wù)提交前,該索引會(huì)一直不可用。

下面我們來(lái)模擬這種情況:

--會(huì)話一:打開(kāi)一個(gè)長(zhǎng)事務(wù)

bill=# begin;
BEGIN
bill=*# delete from t;
DELETE 1000
bill=*#

--會(huì)話二:創(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)行查詢:

索引的確無(wú)法使用。

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)

提交該長(zhǎng)事務(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)

果然是長(zhǎng)事務(wù)的坑?。?/p>

由于我們的庫(kù)中基本都打開(kāi)了old_snapshot_threshold參數(shù),導(dǎo)致創(chuàng)建的索引的indcheckxmin一定是true。但這其實(shí)并不會(huì)有什么太大影響,問(wèn)題在于在創(chuàng)建索引的同時(shí)數(shù)據(jù)庫(kù)中存在長(zhǎng)事務(wù),這就導(dǎo)致了索引在創(chuàng)建完之后第一時(shí)間變得不可用了,需要直到該長(zhǎng)事務(wù)被提交后才可用。

后來(lái)和同事求證發(fā)現(xiàn)他們之前也經(jīng)常碰到這種CPU變高然后又自己降下來(lái)的情況,之前并沒(méi)有注意是這張表的全表掃描導(dǎo)致的。由于該表是由pg_pathman創(chuàng)建的自動(dòng)分區(qū),每天都會(huì)自己去創(chuàng)建一個(gè)新的分區(qū),因此如果每天自動(dòng)創(chuàng)建分區(qū)的時(shí)候存在長(zhǎng)事務(wù),那么創(chuàng)建完之后相關(guān)的分區(qū)上的索引均是不可用的,這也是為什么查詢不走索引然后又自己恢復(fù)的原因了。

這個(gè)案例其實(shí)我們?cè)趯?shí)際生產(chǎn)中遇到的可能性還是很大的,如果你的數(shù)據(jù)庫(kù)打開(kāi)了old_snapshot_threshold參數(shù),同時(shí)沒(méi)有做好長(zhǎng)事務(wù)的監(jiān)控,那么創(chuàng)建的索引就會(huì)出現(xiàn)這種不可用的情況。長(zhǎng)事務(wù)危害不淺啊!

到此這篇關(guān)于PostgreSQL長(zhǎng)事務(wù)與失效的索引查詢淺析介紹的文章就介紹到這了,更多相關(guān)PostgreSQL長(zhǎng)事務(wù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!

相關(guān)文章

  • postgres之jsonb屬性的使用操作

    postgres之jsonb屬性的使用操作

    這篇文章主要介紹了postgres之jsonb屬性的使用操作,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教
    2021-06-06
  • Postgresql 如何選擇正確的關(guān)閉模式

    Postgresql 如何選擇正確的關(guān)閉模式

    這篇文章主要介紹了Postgresl 如何選擇正確的關(guān)閉模式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧
    2021-01-01
  • PostgreSQL教程(九):事物隔離介紹

    PostgreSQL教程(九):事物隔離介紹

    這篇文章主要介紹了PostgreSQL教程(九):事物隔離介紹,本文主要針對(duì)讀已提交和可串行化事物隔離級(jí)別進(jìn)行說(shuō)明和比較,需要的朋友可以參考下
    2015-05-05
  • PostgreSQL中數(shù)據(jù)批量導(dǎo)入導(dǎo)出的錯(cuò)誤處理

    PostgreSQL中數(shù)據(jù)批量導(dǎo)入導(dǎo)出的錯(cuò)誤處理

    在 PostgreSQL 中進(jìn)行數(shù)據(jù)的批量導(dǎo)入導(dǎo)出是常見(jiàn)的操作,但有時(shí)可能會(huì)遇到各種錯(cuò)誤,下面將詳細(xì)探討可能出現(xiàn)的錯(cuò)誤類(lèi)型、原因及相應(yīng)的解決方案,并提供具體的示例來(lái)幫助您更好地理解和處理這些問(wèn)題,需要的朋友可以參考下
    2024-07-07
  • Postgresql 通過(guò)出生日期獲取年齡的操作

    Postgresql 通過(guò)出生日期獲取年齡的操作

    這篇文章主要介紹了Postgresql 通過(guò)出生日期獲取年齡的操作,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧
    2020-12-12
  • 使用PostgreSQL數(shù)據(jù)庫(kù)建立用戶畫(huà)像系統(tǒng)的方法

    使用PostgreSQL數(shù)據(jù)庫(kù)建立用戶畫(huà)像系統(tǒng)的方法

    這篇文章主要介紹了使用PostgreSQL數(shù)據(jù)庫(kù)建立用戶畫(huà)像系統(tǒng),下面使用一個(gè)具體的例子來(lái)說(shuō)明如何使用PostgreSQL的json數(shù)據(jù)類(lèi)型來(lái)建立用戶標(biāo)簽數(shù)據(jù),需要的朋友可以參考下
    2022-10-10
  • PostgreSQL如何查看事務(wù)所占有的鎖實(shí)操指南

    PostgreSQL如何查看事務(wù)所占有的鎖實(shí)操指南

    這篇文章主要給大家介紹了關(guān)于PostgreSQL如何查看事務(wù)所占有鎖的相關(guān)資料,文中通過(guò)代碼以及圖文介紹的非常詳細(xì),對(duì)大家學(xué)習(xí)或者使用PostgreSQL具有一定的參考借鑒價(jià)值,需要的朋友可以參考下
    2023-10-10
  • postgresql 實(shí)現(xiàn)獲取所有表名,字段名,字段類(lèi)型,注釋

    postgresql 實(shí)現(xiàn)獲取所有表名,字段名,字段類(lèi)型,注釋

    這篇文章主要介紹了postgresql 實(shí)現(xiàn)獲取所有表名,字段名,字段類(lèi)型,注釋操作,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧
    2021-01-01
  • postgresql 如何查看pg_wal目錄下xlog文件總大小

    postgresql 如何查看pg_wal目錄下xlog文件總大小

    這篇文章主要介紹了postgresql 如何查看pg_wal目錄下xlog文件總大小的操作,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧
    2021-01-01
  • PostgreSQL存儲(chǔ)過(guò)程循環(huán)調(diào)用方式

    PostgreSQL存儲(chǔ)過(guò)程循環(huán)調(diào)用方式

    這篇文章主要介紹了PostgreSQL存儲(chǔ)過(guò)程循環(huán)調(diào)用方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧
    2021-01-01

最新評(píng)論