Postgres፡ እብጠት፣ pg_repack እና የዘገዩ ገደቦች

የሆድ እብጠት ጠረጴዛዎች እና ኢንዴክሶች (ብሎት) ተጽእኖ በሰፊው የሚታወቅ እና በ Postgres ውስጥ ብቻ አይደለም. እንደ VACUUM FULL ወይም CLUSTER ያሉ “ከሳጥኑ ውጪ”ን ለመቋቋም መንገዶች አሉ፣ ነገር ግን በሚሰሩበት ጊዜ ጠረጴዛዎችን ይቆልፋሉ እና ስለዚህ ሁልጊዜ ጥቅም ላይ መዋል አይችሉም።

ጽሑፉ እብጠት እንዴት እንደሚከሰት፣ እሱን እንዴት መቋቋም እንደሚችሉ፣ ስለተዘገዩ ገደቦች እና የpg_repack ቅጥያውን በመጠቀም ስለሚያስከትሏቸው ችግሮች አንዳንድ ንድፈ ሃሳቦች ይኖረዋል።

ይህ ጽሑፍ የተመሰረተው በ ንግግሬ በ PgConf.Russia 2020።

ለምን እብጠት አለ

Postgres በበርካታ ስሪት ሞዴል ላይ የተመሰረተ ነው (ኤም.ቪ.ሲ.ሲ). ዋናው ነገር በሰንጠረዡ ውስጥ ያለው እያንዳንዱ ረድፍ ብዙ ስሪቶች ሊኖረው ይችላል, ግብይቶች ግን ከእነዚህ ስሪቶች ውስጥ ከአንድ በላይ አይታዩም, ግን የግድ አንድ አይነት አይደለም. ይህ ብዙ ግብይቶች በአንድ ጊዜ እንዲሰሩ እና አንዳቸው በሌላው ላይ ምንም አይነት ተጽእኖ አይኖራቸውም.

በግልጽ ለማየት እንደሚቻለው, እነዚህ ሁሉ ስሪቶች መቀመጥ አለባቸው. Postgres የሚሠራው ከማህደረ ትውስታ ገጽ በገጽ ሲሆን ገጽ ደግሞ ከዲስክ ሊነበብ ወይም ሊጻፍ የሚችል አነስተኛው የውሂብ መጠን ነው። ይህ እንዴት እንደሚከሰት ለመረዳት አንድ ትንሽ ምሳሌ እንመልከት።

ብዙ መዝገቦችን የጨመርንበት ጠረጴዛ አለን እንበል። ጠረጴዛው የተከማቸበት የፋይሉ የመጀመሪያ ገጽ አዲስ ውሂብ አለው። እነዚህ ከቁርጠኝነት በኋላ ለሌሎች ግብይቶች የሚገኙ የቀጥታ የረድፎች ስሪቶች ናቸው (ለቀላልነት፣ የመነጠል ደረጃው የተነበበ ነው ብለን እንገምታለን።

ከዚያ አንዱን ግቤቶች አዘምነን እና የድሮውን ስሪት ጊዜው ያለፈበት መሆኑን ምልክት አድርገናል።

የረድፍ ስሪቶችን ደረጃ በደረጃ በማዘመን እና በመሰረዝ ከውሂቡ ውስጥ ግማሽ ያህሉ "ቆሻሻ" የሆነበት ገጽ አግኝተናል። ይህ ውሂብ ለማንኛውም ግብይት አይታይም።

Postgres ዘዴ አለው። VACUUMያረጁ ስሪቶችን የሚያጸዳ እና ለአዲስ ውሂብ ቦታ የሚሰጥ። ነገር ግን በጠንካራ ሁኔታ ካልተዋቀረ ወይም በሌሎች ጠረጴዛዎች ውስጥ በመስራት ከተጠመደ “የቆሻሻ መረጃ” ይቀራል እና ለአዲስ መረጃ ተጨማሪ ገጾችን መጠቀም አለብን።

ስለዚህ በእኛ ምሳሌ, በተወሰነ ጊዜ ውስጥ, ሠንጠረዡ አራት ገጾችን ይይዛል, ነገር ግን በውስጡ ያለው የቀጥታ መረጃ ግማሽ ብቻ ይሆናል. በውጤቱም, ሰንጠረዡን ሲደርሱ, ከሚያስፈልገው በላይ ብዙ መረጃዎችን እናነባለን.

ምንም እንኳን VACUUM አሁን ሁሉንም ተዛማጅነት የሌላቸው የረድፍ ስሪቶችን ቢያጠፋም, ሁኔታው ​​በከፍተኛ ሁኔታ አይሻሻልም. ለአዲስ መስመሮች በገጾች ወይም ሙሉ ገፆች ነፃ ቦታ ይኖረናል፣ ነገር ግን አሁንም ከምንፈልገው በላይ ብዙ መረጃዎችን እናነባለን።
በነገራችን ላይ, ሙሉ በሙሉ ባዶ ገጽ (በእኛ ምሳሌ ውስጥ ሁለተኛው) በፋይሉ መጨረሻ ላይ ከሆነ, VACUUM ሊያቋርጠው ይችላል. አሁን ግን መሀል ላይ ስለምትገኝ ምንም ማድረግ አይቻልም።

እንደዚህ ያሉ ባዶ ወይም በጣም ትንሽ ያልሆኑ ገጾች ቁጥር ትልቅ ሲሆን ይህም እብጠት ይባላል, በአፈፃፀም ላይ ተጽዕኖ ማሳደር ይጀምራል.

ከላይ የተገለፀው ሁሉም ነገር በጠረጴዛዎች ውስጥ የሆድ እብጠት መከሰት ሜካኒክስ ነው. በመረጃ ጠቋሚዎች ውስጥ, ይህ በተመሳሳይ መንገድ ይከሰታል.

እብጠት አለብኝ?

የሆድ እብጠት እንዳለብዎ ለማወቅ ብዙ መንገዶች አሉ። የመጀመሪያው ሀሳብ በጠረጴዛዎች ውስጥ ስላሉት ረድፎች ብዛት ፣ ስለ “ቀጥታ” ረድፎች ብዛት ፣ ወዘተ ግምታዊ መረጃ የያዘውን የ Postgres ውስጣዊ ስታቲስቲክስን መጠቀም ነው ። በበይነመረብ ላይ ብዙ ዝግጁ-የተዘጋጁ ስክሪፕቶች አሉ። መሰረት አድርገን ነው የወሰድነው ስክሪፕት ከPostgreSQL ኤክስፐርቶች፣ የጠረጴዛ እብጠትን ከቶስት እና እብጠት የbtree ኢንዴክሶች ጋር መገምገም ይችላል። በእኛ ልምድ, ስህተቱ ከ10-20% ነው.

ሌላው መንገድ ቅጥያውን መጠቀም ነው pgstattuple, ይህም በገጾቹ ውስጥ እንዲመለከቱ እና ሁለቱንም የተገመተውን እና ትክክለኛውን የሆድ እብጠት እንዲያገኙ ያስችልዎታል. ነገር ግን በሁለተኛው ጉዳይ ላይ ሙሉውን ጠረጴዛ መቃኘት አለብዎት.

አነስተኛ መጠን ያለው እብጠት, እስከ 20% ድረስ, ተቀባይነት አለው. ለ fillfactor እንደ አናሎግ ተደርጎ ሊወሰድ ይችላል ጠረጴዛዎች и ኢንዴክሶች. በ 50% እና ከዚያ በላይ, የአፈፃፀም ችግሮች ሊጀምሩ ይችላሉ.

እብጠትን ለመቋቋም መንገዶች

በፖስትግሬስ ውስጥ እብጠትን ለመቋቋም ከሳጥን ውጭ ያሉ ብዙ መንገዶች አሉ ነገር ግን እነሱ ሁልጊዜ በጣም የራቁ ናቸው እና ለሁሉም ሰው ላይስማሙ ይችላሉ።

እብጠት እንዳይከሰት AUTOVACUM ን ያዋቅሩ. እና የበለጠ ትክክለኛ ለመሆን ፣ ለእርስዎ ተቀባይነት ባለው ደረጃ ለማቆየት። ይህ እንደ "ካፒቴን" ምክር ይመስላል, ነገር ግን በእውነቱ ይህ ለመድረስ ሁልጊዜ ቀላል አይደለም. ለምሳሌ፣ በመረጃ ንድፍ ላይ መደበኛ ለውጥ ያለው ንቁ እድገት አለህ፣ ወይም የሆነ አይነት የውሂብ ፍልሰት እየተካሄደ ነው። በውጤቱም, የጭነት መገለጫዎ በተደጋጋሚ ሊለወጥ እና ለተለያዩ ጠረጴዛዎች የተለየ ሊሆን ይችላል. ይህ ማለት ያለማቋረጥ ከጠመዝማዛው ትንሽ ቀድመህ መሆን አለብህ እና AUTOVACUUMን በየጠረጴዛው ተለዋዋጭ መገለጫ ላይ ማስተካከል አለብህ ማለት ነው። ግን ይህን ማድረግ ቀላል እንዳልሆነ ግልጽ ነው.

AUTOVACUUM ሰንጠረዦችን መስራት ያልቻለበት ሌላው የተለመደ ምክንያት በእነዚህ ግብይቶች ውስጥ በመገኘቱ መረጃን ከማጽዳት የሚከለክሉት ረጅም ጊዜ የሚፈጁ ግብይቶች መኖራቸው ነው። እዚህ ያለው ምክር ግልጽ ነው - "የተንጠለጠሉ" ግብይቶችን ያስወግዱ እና የንቁ ግብይቶችን ጊዜ ይቀንሱ. ነገር ግን በመተግበሪያዎ ላይ ያለው ጭነት የ OLAP እና OLTP ድብልቅ ከሆነ በተመሳሳይ ጊዜ ብዙ ተደጋጋሚ ዝመናዎችን እና አጭር መጠይቆችን እንዲሁም ረጅም ስራዎችን ለምሳሌ ሪፖርት መገንባት ይችላሉ። በእንደዚህ ዓይነት ሁኔታ, ሸክሙን በተለያዩ መሠረቶች ላይ ለማሰራጨት ማሰብ አለብዎት, ይህም እያንዳንዳቸውን በደንብ እንዲያስተካክሉ ያስችልዎታል.

ሌላ ምሳሌ - ምንም እንኳን መገለጫው ተመሳሳይነት ያለው ቢሆንም የውሂብ ጎታ በጣም ከፍተኛ በሆነ ጭነት ውስጥ ቢሆንም በጣም ኃይለኛው AUTOVACUUM እንኳን መቋቋም አይችልም, እና እብጠት ይከሰታል. ስኬል (አቀባዊ ወይም አግድም) ብቸኛው መፍትሄ ነው.

AUTOVACUUM ን ሲያዋቅሩ እንዴት በአንድ ሁኔታ ውስጥ እንደሚገኙ፣ ነገር ግን እብጠት ማደጉን ይቀጥላል።

ቡድን ቫኩም ሙሉ የሰንጠረዦችን እና ኢንዴክሶችን ይዘቶች እንደገና ይገነባል እና በውስጣቸው ወቅታዊ መረጃዎችን ብቻ ያስቀምጣል። እብጠትን ለማስወገድ በትክክል ይሠራል ፣ ግን በሚተገበርበት ጊዜ በጠረጴዛው ላይ ልዩ መቆለፊያ (AccessExclusiveLock) ተይዟል ፣ ይህም ወደዚህ ጠረጴዛ ላይ ጥያቄዎችን አይፈቅድም ፣ ይመርጣል። ለተወሰነ ጊዜ አገልግሎትዎን ወይም ከፊሉን ለማቆም አቅም ካሎት (ከአስር ደቂቃዎች እስከ ብዙ ሰአታት እንደ ዳታቤዙ መጠን እና እንደ ሃርድዌርዎ መጠን) ይህ አማራጭ በጣም ጥሩው ነው። እኛ, በሚያሳዝን ሁኔታ, በታቀደለት ጥገና ወቅት VACUUM FULLን ለማስኬድ ጊዜ የለንም, ስለዚህ ይህ ዘዴ አይስማማንም.

ቡድን ክላስተር የሠንጠረዡን ይዘቶች ልክ እንደ VACUUM FULL በተመሳሳይ መልኩ ይገነባል፣ መረጃው በአካል በዲስክ ላይ የሚታዘዝበትን ኢንዴክስ እንዲገልጹ ያስችልዎታል (ነገር ግን ትዕዛዙ ለወደፊቱ አዲስ ረድፎች ዋስትና አይሰጥም)። በተወሰኑ ሁኔታዎች ውስጥ, ይህ ለብዙ መጠይቆች ጥሩ ማመቻቸት ነው - በመረጃ ጠቋሚ ውስጥ ብዙ መዝገቦችን በማንበብ. የትዕዛዙ ጉዳቱ ከ VACUUM FULL ጋር ተመሳሳይ ነው - በሚሠራበት ጊዜ ጠረጴዛውን ይቆልፋል.

ቡድን REINDEX ከቀደሙት ሁለቱ ጋር ተመሳሳይ ነው ፣ ግን አንድ የተወሰነ ኢንዴክስ ወይም ሁሉንም ኢንዴክሶች በጠረጴዛ ላይ እንደገና ይገነባል። መቆለፊያዎች በትንሹ ደካማ ናቸው፡ ShareLock በጠረጴዛ ላይ (ማሻሻያዎችን ይከለክላል፣ ግን ምርጫዎችን ይፈቅዳል) እና AccessExclusiveLock በእንደገና ሊገነባ በሚችል ኢንዴክስ ላይ (ይህንን ኢንዴክስ በመጠቀም መጠይቆችን ያግዳል። ሆኖም ፖስትግሬስ 12 አማራጩን አስተዋውቋል በተመሳሳይበአንድ ጊዜ መደመር፣ ማሻሻያ እና መዝገቦችን መሰረዝ ሳያግዱ ኢንዴክስን እንደገና እንዲገነቡ ያስችልዎታል።

በቀደሙት የፖስትግሬስ ስሪቶች ከ REINDEX ጋር ተመሳሳይ የሆነ ውጤት ማግኘት ይችላሉ። ኢንዴክስን በተመሳሳይ ጊዜ ፍጠር. ያለ ጠንካራ መቆለፊያ (ShareUpdateExclusiveLock, በትይዩ መጠይቆች ላይ ጣልቃ የማይገባ) ኢንዴክስ እንዲፈጥሩ ይፈቅድልዎታል, ከዚያም የድሮውን ኢንዴክስ በአዲስ ይቀይሩ እና የድሮውን ኢንዴክስ ይሰርዙ. ይህ በማመልከቻዎ ላይ ጣልቃ ሳይገቡ የመረጃ ጠቋሚ እብጠትን ለማስወገድ ያስችልዎታል. ኢንዴክሶችን እንደገና በሚገነቡበት ጊዜ በዲስክ ንዑስ ስርዓት ላይ ተጨማሪ ጭነት እንደሚኖር ግምት ውስጥ ማስገባት አስፈላጊ ነው.

ስለዚህ, ኢንዴክሶች ትኩስ እብጠትን ለማስወገድ መንገዶች ካሉ, ለጠረጴዛዎች ምንም የለም. ውጫዊ ማራዘሚያዎች የሚጫወቱት እዚህ ነው፡ pg_repack (የቀድሞው pg_reorg)፣ pgcompact, pgcompacttable እና ሌሎችም። በዚህ ጽሑፍ ማዕቀፍ ውስጥ እነሱን አላነፃፅራቸውም እና ስለ pg_repack ብቻ እናገራለሁ ፣ ይህም ከአንዳንድ ማሻሻያዎች በኋላ በቤት ውስጥ እንጠቀማለን።

pg_repack እንዴት እንደሚሰራ

በጣም ተራ የሆነ ጠረጴዛ አለን እንበል - በመረጃ ጠቋሚዎች ፣ ገደቦች እና ፣ በሚያሳዝን ሁኔታ ፣ ከ እብጠት ጋር። እንደ መጀመሪያ ደረጃ፣ pg_repack እየሄደ እያለ ሁሉንም ለውጦች ለመከታተል የምዝግብ ማስታወሻ ሠንጠረዥ ይፈጥራል። ቀስቅሴው እነዚህን ለውጦች በእያንዳንዱ ማስገባት፣ ማዘመን እና መሰረዝ ላይ ይደግማል። ከዚያም አወቃቀሩ ከመጀመሪያው ጋር ተመሳሳይነት ያለው ሠንጠረዥ ይፈጠራል, ነገር ግን ያለ ኢንዴክሶች እና ገደቦች, መረጃን የማስገባት ሂደት እንዳይዘገይ.

በመቀጠል pg_repack መረጃን ከአሮጌው ሠንጠረዥ ወደ አዲሱ ሰንጠረዥ ያስተላልፋል ፣ ሁሉንም ተዛማጅ ያልሆኑ ረድፎችን በራስ-ሰር በማጣራት እና ከዚያ ለአዲሱ ሰንጠረዥ ኢንዴክሶችን ይፈጥራል። እነዚህ ሁሉ ስራዎች በሚከናወኑበት ጊዜ ለውጦች በሎግ ሠንጠረዥ ውስጥ ይከማቻሉ.

ቀጣዩ ደረጃ ለውጦቹን ወደ አዲሱ ሰንጠረዥ ማስተላለፍ ነው. ፍልሰት በበርካታ ድግግሞሾች ውስጥ ይከናወናል, እና በሎግ ሠንጠረዥ ውስጥ ከ 20 ያነሱ ግቤቶች ሲቀሩ, pg_repack ጠንካራ መቆለፊያ ያገኛል, የቅርብ ጊዜውን ውሂብ ያፈልሳል እና የድሮውን ሰንጠረዥ በፖስትግሬስ ስርዓት ሰንጠረዦች ውስጥ በአዲሱ ይተካዋል. ይህ ከጠረጴዛው ጋር መስራት የማይችሉበት ብቸኛው እና በጣም አጭር ጊዜ ነው. ከዚያ በኋላ, አሮጌው ጠረጴዛ እና ምዝግብ ማስታወሻ ያለው ጠረጴዛ ይሰረዛሉ እና በፋይል ስርዓቱ ውስጥ ቦታ ይለቀቃሉ. ሂደት ተጠናቅቋል።

በንድፈ ሀሳብ, ሁሉም ነገር በጣም ጥሩ ይመስላል, ግን በተግባርስ? pg_repackን ያለጭነት እና በጭነት ሞከርን እና ያለጊዜው ማቆሚያ (በሌላ አነጋገር በ Ctrl + C) አሰራሩን አረጋግጠናል ። ሁሉም ሙከራዎች አዎንታዊ ነበሩ።

ወደ ፕሮዱም ሄድን - እና ከዚያ እንደጠበቅነው ሁሉም ነገር ተሳስቷል።

የመጀመሪያው ፓንኬክ በሽያጭ ላይ

በመጀመሪያው ክላስተር ላይ፣ ልዩ የሆነ የእገዳ ጥሰት በተመለከተ ስህተት ገጥሞናል፡-

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

ይህ ገደብ በpg_repack የተፈጠረ በራስ-የመነጨ ስም index_16508 ነበረው። በአጻጻፍ ውስጥ በተካተቱት ባህሪያት, ከእሱ ጋር የሚስማማውን "የእኛን" ገደብ ወስነናል. ችግሩ የሆነው ይህ ተራ ገደብ ሳይሆን የዘገየ ነው (የዘገየ ገደብ), ማለትም እ.ኤ.አ. የእሱ ማረጋገጫ የሚከናወነው ከ sql ትዕዛዝ በኋላ ነው, ይህም ወደ ያልተጠበቁ ውጤቶች ይመራል.

የዘገዩ ገደቦች፡ ለምን እንደሚፈለጉ እና እንዴት እንደሚሰሩ

ስለ ዘገዩ ገደቦች ትንሽ ንድፈ ሃሳብ።
አንድ ቀላል ምሳሌ አስብበት፡ ሁለት ባህሪያት ያሉት የመኪና ማውጫ ጠረጴዛ አለን - በማውጫው ውስጥ የመኪናው ስም እና ቅደም ተከተል።

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique
);

የመጀመሪያዎቹን እና የሁለተኛውን መኪናዎች በቦታዎች መለዋወጥ አስፈለገን እንበል። ዋናው መፍትሄ የመጀመሪያውን እሴት ወደ ሁለተኛው እና ሁለተኛውን ወደ መጀመሪያው ማዘመን ነው-

begin;
  update cars set ord = 2 where name = 'audi';
  update cars set ord = 1 where name = 'bmw';
commit;

ነገር ግን ይህን ኮድ ስናሄድ፣ በሠንጠረዡ ውስጥ ያሉት የእሴቶቹ ቅደም ተከተል ልዩ ስለሆነ የእገዳ ጥሰት ይጠብቀናል፡

[23305] ERROR: duplicate key value violates unique constraint “uk_cars”
Detail: Key (ord)=(2) already exists.

በተለየ መንገድ እንዴት ማድረግ ይቻላል? አማራጭ አንድ: በሠንጠረዡ ውስጥ እንደማይኖር ዋስትና ባለው ትዕዛዝ የእሴቱን ተጨማሪ ምትክ ይጨምሩ, ለምሳሌ "-1". በፕሮግራም አወጣጥ ውስጥ, ይህ "የሁለት ተለዋዋጮችን እሴት በሶስተኛ በኩል መለዋወጥ" ይባላል. የዚህ ዘዴ ብቸኛው ጉዳት ተጨማሪ ማሻሻያ ነው.

አማራጭ ሁለት፡ ሰንጠረዡን በአዲስ መልክ ንድፍ ኢንቲጀር ከመሆን ይልቅ ተንሳፋፊ ነጥብ ዳታ አይነትን ለአርቢ እሴት ለመጠቀም። ከዚያም እሴቱን ከ 1 ለምሳሌ ወደ 2.5 ሲያዘምን, የመጀመሪያው ግቤት በራስ-ሰር በሁለተኛው እና በሦስተኛው መካከል "ይቆማል". ይህ መፍትሔ ይሰራል, ግን ሁለት ገደቦች አሉ. በመጀመሪያ ፣ እሴቱ በይነገጹ ውስጥ የሆነ ቦታ ጥቅም ላይ ከዋለ ለእርስዎ አይሰራም። በሁለተኛ ደረጃ ፣ እንደ የውሂብ አይነት ትክክለኛነት ፣ የሁሉንም መዛግብት እሴቶች እንደገና ከማስላትዎ በፊት የተወሰነ ቁጥር ሊኖርዎት ይችላል ።

አማራጭ ሶስት፡ እገዳው በተፈጸመበት ጊዜ ብቻ እንዲረጋገጥ ገደብ እንዲዘገይ ያድርጉ፡

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique deferrable initially deferred
);

የመጀመሪያ ጥያቄያችን አመክንዮ ሁሉም እሴቶች ልዩ መሆናቸውን የሚያረጋግጥ በመሆኑ ቁርጠኝነት ይሳካል።

ከላይ የተብራራው ምሳሌ, በእርግጥ, በጣም የተዋሃደ ነው, ግን ሀሳቡን ያሳያል. በእኛ መተግበሪያ ውስጥ ተጠቃሚዎች በተመሳሳይ ጊዜ በቦርዱ ውስጥ ካሉ የጋራ መግብር ዕቃዎች ጋር ሲገናኙ ግጭቶችን የመፍታት ኃላፊነት ያለበትን አመክንዮ ለመተግበር የዘገዩ ገደቦችን እንጠቀማለን። እንደነዚህ ያሉ ገደቦችን መጠቀም የመተግበሪያውን ኮድ ትንሽ ቀላል ለማድረግ ያስችለናል.

በአጠቃላይ፣ በ Postgres ውስጥ ባለው የእገዳ አይነት ላይ በመመስረት፣ የማረጋገጫቸው ሶስት የጥራጥሬነት ደረጃዎች አሉ፡ ረድፍ፣ ግብይት እና አገላለጽ።

ምንጭ: ብስጭት

ቼክ እና NULL ሁልጊዜ በረድፍ ደረጃ ይፈተሻሉ, ለሌሎች ገደቦች, ከጠረጴዛው ላይ እንደሚታየው, የተለያዩ አማራጮች አሉ. የበለጠ ማንበብ ትችላለህ እዚህ.

ባጭሩ ለማጠቃለል፣ በተለያዩ ሁኔታዎች ውስጥ የዘገዩ ገደቦች የበለጠ ሊነበብ የሚችል ኮድ እና ጥቂት ትዕዛዞችን ያስከትላሉ። ነገር ግን ስህተቱ በተከሰተበት ቅጽበት እና ስለሱ የተማሩበት ቅጽበት በጊዜ ስለሚለያዩ የማረም ሂደቱን በማወሳሰብ መክፈል አለቦት። ሌላው ችግር ሊሆን የሚችለው በጥያቄው ውስጥ የዘገየ ገደብ ካለ መርሐግብር አውጪው ሁልጊዜ ጥሩ እቅድ መገንባት ላይችል ይችላል።

የpg_repack መሻሻል

የተዘገዩ ገደቦች ምን እንደሆኑ ሸፍነናል፣ ግን ከችግራችን ጋር እንዴት ይዛመዳሉ? ቀደም ሲል ያገኘነውን ስህተት አስታውስ፡-

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

ውሂብ ከሎግ ሰንጠረዥ ወደ አዲስ ሰንጠረዥ ሲገለበጥ ይከሰታል. ይህ እንግዳ ይመስላል, ምክንያቱም በምዝግብ ማስታወሻው ውስጥ ያለው መረጃ ከመጀመሪያው ሠንጠረዥ ውስጥ ካለው መረጃ ጋር ተወስኗል። የመጀመሪያውን የጠረጴዛ ገደቦች ካሟሉ በአዲሱ ውስጥ ተመሳሳይ ገደቦችን እንዴት መጣስ ይችላሉ?

እንደ ተለወጠ ፣ የችግሩ ምንጭ በ pg_repack ቀዳሚው ደረጃ ላይ ነው ፣ ይህም ኢንዴክሶችን ብቻ ይፈጥራል ፣ ግን ገደቦችን አይፈጥርም-የአሮጌው ጠረጴዛ ልዩ ገደቦች ነበረው ፣ እና አዲሱ በምትኩ ልዩ ኢንዴክስ ፈጠረ።

እዚህ ላይ ማስተዋሉ አስፈላጊ ነው እገዳው የተለመደ ከሆነ እና የማይዘገይ ከሆነ, በእሱ ምትክ የተፈጠረው ልዩ ኢንዴክስ ከዚህ ገደብ ጋር እኩል ነው, ምክንያቱም በ Postgres ውስጥ ልዩ ገደቦች ልዩ ኢንዴክስ በመፍጠር ይተገበራሉ። ነገር ግን የዘገየ እገዳን በተመለከተ, ባህሪው ተመሳሳይ አይደለም, ምክንያቱም ጠቋሚው ሊዘገይ ስለማይችል እና ሁልጊዜ የ sql ትዕዛዝ በሚፈፀምበት ጊዜ ይጣራል.

ስለዚህ, የችግሩ ዋናው ነገር በ "የዘገየ" ቼክ ውስጥ ነው-በመጀመሪያው ሠንጠረዥ ውስጥ, በተፈፀመበት ጊዜ እና በአዲሱ ውስጥ, የ sql ትዕዛዝ በሚፈፀምበት ጊዜ ይከሰታል. ስለዚህ ቼኮች በሁለቱም ሁኔታዎች በተመሳሳይ መንገድ መከናወናቸውን ማረጋገጥ አለብን-ሁልጊዜ ዘግይተዋል, ወይም ሁልጊዜም ወዲያውኑ.

ስለዚህ ምን ሀሳቦች ነበሩን.

ከዘገየ ጋር የሚመሳሰል መረጃ ጠቋሚ ይፍጠሩ

የመጀመሪያው ሃሳብ ሁለቱንም ቼኮች በአስቸኳይ ሁነታ ማከናወን ነው. ይህ እገዳው በርካታ የውሸት አወንታዊ ውጤቶችን ሊያስከትል ይችላል, ነገር ግን ጥቂቶቹ ካሉ, ይህ በተጠቃሚዎች ስራ ላይ ተጽዕኖ ሊያሳድር አይገባም, ምክንያቱም እንዲህ ያሉ ግጭቶች ለእነሱ የተለመደ ሁኔታ ናቸው. ይከሰታሉ, ለምሳሌ, ሁለት ተጠቃሚዎች አንድ አይነት መግብርን በአንድ ጊዜ ማረም ሲጀምሩ, እና የሁለተኛው ተጠቃሚ ደንበኛ መግብር በመጀመሪያ ተጠቃሚ ለማረም አስቀድሞ እንደታገደ መረጃ ለመቀበል ጊዜ የለውም. በእንደዚህ አይነት ሁኔታ አገልጋዩ ለሁለተኛው ተጠቃሚ እምቢተኛ ምላሽ ይሰጣል እና ደንበኛው ለውጦቹን መልሶ ያሽከረክራል እና መግብርን ይቆልፋል። ትንሽ ቆይቶ የመጀመሪያው ተጠቃሚ አርትዖትን ሲያጠናቅቅ ሁለተኛው መግብር እንዳልታገደ መረጃ ይደርሰዋል እና ድርጊቱን መድገም ይችላል።

ቼኮች ሁል ጊዜ በማይተላለፉ ሁነታ ላይ መሆናቸውን ለማረጋገጥ ከመጀመሪያው የዘገየ ገደብ ጋር ተመሳሳይ የሆነ አዲስ መረጃ ጠቋሚ ፈጠርን፦

CREATE UNIQUE INDEX CONCURRENTLY uk_tablename__immediate ON tablename (id, index);
-- run pg_repack
DROP INDEX CONCURRENTLY uk_tablename__immediate;

በሙከራው አካባቢ, የተጠበቁትን ጥቂት ስህተቶች ብቻ ተቀብለናል. ስኬት! pg_repackን በፕሮድ ላይ እንደገና አስሮጠን እና በመጀመሪያው ክላስተር ላይ በአንድ ሰአት ስራ 5 ስህተቶችን አግኝተናል። ይህ ተቀባይነት ያለው ውጤት ነው. ነገር ግን፣ ቀድሞውኑ በሁለተኛው ክላስተር ላይ፣ የስህተቶቹ ብዛት በከፍተኛ ሁኔታ ጨምሯል እና pg_repackን ማቆም ነበረብን።

ለምን ሆነ? ስህተት የመከሰቱ ዕድል ምን ያህል ተጠቃሚዎች ከተመሳሳዩ መግብሮች ጋር በአንድ ጊዜ እንደሚሰሩ ይወሰናል. በግልጽ ለማየት እንደሚቻለው፣ በዚያን ጊዜ፣ በመጀመሪያው ክላስተር ላይ በተከማቸው ውሂብ ከቀሪው ይልቅ በጣም ያነሱ የውድድር ለውጦች ነበሩ፣ ማለትም። እኛ "እድለኛ" ነን።

ሀሳቡ አልሰራም። በዚያን ጊዜ፣ ሌሎች ሁለት መፍትሄዎችን አይተናል፡ የተላለፉ ገደቦችን ለመተው የእኛን መተግበሪያ ኮድ እንደገና ጻፍ፣ ወይም ከእነሱ ጋር ለመስራት pg_repackን “ማስተማር”። ሁለተኛውን መርጠናል.

በአዲስ ሠንጠረዥ ውስጥ ኢንዴክሶችን ከመጀመሪያው ሰንጠረዥ በተዘገዩ ገደቦች ይተኩ

የማሻሻያው ዓላማ ግልጽ ነበር - የመጀመሪያው ሠንጠረዥ የዘገየ ገደብ ካለው, ለአዲሱ እንዲህ አይነት ገደብ መፍጠር አስፈላጊ ነው, እና ኢንዴክስ አይደለም.

ለውጦቻችንን ለመፈተሽ ቀላል ፈተና ጽፈናል፡-

• የዘገየ ገደብ ያለው ጠረጴዛ እና አንድ መዝገብ;
• አሁን ካለው መዝገብ ጋር የሚጋጭ መረጃን በ loop ውስጥ እናስገባለን ፤
• ማሻሻያ ያድርጉ - ውሂቡ ከአሁን በኋላ አይጋጭም;
• ለውጦችን ማድረግ.

create table test_table
(
  id serial,
  val int,
  constraint uk_test_table__val unique (val) deferrable initially deferred 
);

INSERT INTO test_table (val) VALUES (0);
FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (0) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    COMMIT;
  END;
END LOOP;

የመጀመሪያው የpg_repack ስሪት ሁልጊዜ በመጀመሪያው አስገባ ላይ ተሰናክሏል፣ የተሻሻለው እትም ያለምንም ስህተት ይሰራል። በጣም ጥሩ.

ወደ ፕሮድ እንሄዳለን እና በተመሳሳይ ጊዜ ከሎግ ሠንጠረዥ ወደ አዲስ በመቅዳት ላይ ስህተት ደርሰናል፡

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

የሚታወቅ ሁኔታ: ሁሉም ነገር በሙከራ አካባቢዎች ላይ ይሰራል, ነገር ግን በምርት ላይ አይደለም?!

APPLY_COUNT እና የሁለት ባች መጋጠሚያ

ኮዱን በጥሬው በመስመር መተንተን ጀመርን እና አንድ አስፈላጊ ነጥብ አገኘን-መረጃ ከሎግ ጠረጴዛው ወደ አዲሱ በቡድን ይተላለፋል ፣ APPLY_COUNT ቋሚ የምድብ መጠኑን ያሳያል።

for (;;)
{
num = apply_log(connection, table, APPLY_COUNT);

if (num > MIN_TUPLES_BEFORE_SWITCH)
     continue;  /* there might be still some tuples, repeat. */
...
}

ችግሩ ብዙ ክንዋኔዎች ገደብ ሊጥሱ የሚችሉበት የዋናው ግብይት መረጃ በማስተላለፍ ጊዜ በሁለት ቡድኖች መጋጠሚያ ላይ ሊጠናቀቅ ይችላል - ከትእዛዞቹ ውስጥ ግማሹ በመጀመሪያ ደረጃ ይከናወናል ፣ ሌላኛው ግማሽ በሁለተኛው ውስጥ. እና እዚህ ፣ ምን ያህል እድለኛ ነው-በመጀመሪያው ቡድን ውስጥ ያሉት ቡድኖች ምንም ነገር ካልጣሱ ፣ ከዚያ ሁሉም ነገር ጥሩ ነው ፣ ግን ካደረጉ ስህተት ይከሰታል።

APPLY_COUNT ከ1000 መዛግብት ጋር እኩል ነው፣ ይህም የእኛ ፈተናዎች ለምን እንደተሳካላቸው ያብራራል - የ"ባች መስቀለኛ መንገድ" ጉዳይን አልሸፈኑም። ሁለት ትዕዛዞችን ተጠቀምን - አስገባ እና አዘምን ፣ ስለዚህ በትክክል 500 የሁለት ትዕዛዞች ግብይቶች ሁል ጊዜ በቡድን ውስጥ ይቀመጡ ነበር እና ችግር አላጋጠመንም። ሁለተኛውን ማሻሻያ ካከልን በኋላ፣ የእኛ አርትዖት መስራት አቁሟል፡-

FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (1) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    UPDATE test_table set val = i where id = v_id; -- one more update
    COMMIT;
  END;
END LOOP;

ስለዚህ የሚቀጥለው ተግባር በአንድ ግብይት ውስጥ የተቀየረው ከመጀመሪያው ሰንጠረዥ የተገኘው መረጃ በአንድ ግብይት ውስጥ ወደ አዲሱ ሰንጠረዥ መግባቱን ማረጋገጥ ነው።

የመደብደብ አለመቀበል

እና እንደገና ሁለት መፍትሄዎች ነበሩን. አንደኛ፡ በጥቅሉ ለመከፋፈል እንቢ እና የውሂብ ዝውውሩን አንድ ግብይት እናድርገው። ለዚህ ውሳኔ ቀላልነቱ ነበር - የሚፈለጉት የኮድ ለውጦች በጣም አናሳ ናቸው (በነገራችን ላይ፣ በድሮ ስሪቶች በዚያን ጊዜ pg_reorg በዚያ መንገድ ሰርቷል)። ግን አንድ ችግር አለ - እኛ የረጅም ጊዜ ግብይት እየፈጠርን ነው ፣ እና ይህ ቀደም ሲል እንደተጠቀሰው ፣ ለአዲስ እብጠት መከሰት ስጋት ነው።

ሁለተኛው መፍትሔ የበለጠ የተወሳሰበ ነው, ነገር ግን ምናልባት የበለጠ ትክክል ነው: ወደ ጠረጴዛው ላይ መረጃን ከጨመረው የግብይቱን መለያ ጋር በማስታወሻ ሠንጠረዥ ውስጥ አምድ ይፍጠሩ. ከዚያ መረጃን ስንገለብጥ በዚህ ባህሪ ልንከፋፍለው እና ተዛማጅ ለውጦች አንድ ላይ መተላለፉን ማረጋገጥ እንችላለን። ቡድኑ ከበርካታ ግብይቶች (ወይም አንድ ትልቅ) ይመሰረታል እና መጠኑ በእነዚህ ግብይቶች ውስጥ ምን ያህል ውሂብ እንደተቀየረ ይለያያል። የተለያዩ ግብይቶች መረጃ በዘፈቀደ ቅደም ተከተል ወደ ሎግ ጠረጴዛው ስለሚገባ እንደበፊቱ በቅደም ተከተል ማንበብ እንደማይቻል ልብ ሊባል ይገባል። በእያንዳንዱ ጥያቄ ላይ seqscan በ tx_id ተጣርቶ በጣም ውድ ነው፣ ኢንዴክስ ያስፈልገዎታል፣ ነገር ግን እሱን በማዘመን ከፍተኛ ወጪ የተነሳ ስልቱን ይቀንሳል። በአጠቃላይ, እንደ ሁልጊዜ, አንድ ነገር መስዋዕት ማድረግ ያስፈልግዎታል.

ስለዚህ, እንደ ቀለል ያለ, የመጀመሪያውን አማራጭ ለመጀመር ወሰንን. በመጀመሪያ, ረጅም ግብይት እውነተኛ ችግር መሆን አለመሆኑን መረዳት አስፈላጊ ነበር. ከአሮጌው ሠንጠረዥ ወደ አዲሱ ዋናው የመረጃ ልውውጥ በአንድ ረጅም ግብይት ውስጥ ስለሚከሰት ጥያቄው "ይህን ግብይት ምን ያህል እንጨምርበታለን?" የመጀመሪያው ግብይት የሚቆይበት ጊዜ በዋናነት በሠንጠረዡ መጠን ላይ የተመሰረተ ነው. የአዲሱ የቆይታ ጊዜ የሚወሰነው በውሂብ ዝውውሩ ወቅት በሰንጠረዡ ውስጥ ምን ያህል ለውጦች እንደሚከማቹ ነው, ማለትም. በጭነቱ ጥንካሬ ላይ. የpg_repack አሂድ የተከናወነው አነስተኛ የአገልግሎት ጭነት በነበረበት ጊዜ ነው፣ እና የለውጡ መጠን ከመጀመሪያው የሰንጠረዥ መጠን ጋር ሲነጻጸር አነስተኛ ነበር። አዲስ የግብይት ጊዜን ችላ ማለት እንደምንችል ወስነናል (ለማነፃፀር በአማካይ 1 ሰዓት ከ2-3 ደቂቃ ነው)።

ሙከራዎቹ አዎንታዊ ነበሩ። በሽያጭ ላይም ይጀምሩ። ግልፅ ለማድረግ፣ ከሩጫው በኋላ የአንዱ መሰረቶቹ መጠን ያለው ምስል እዚህ አለ፡-

ይህ መፍትሔ ሙሉ በሙሉ ስለሚስማማን, ሁለተኛውን ለመተግበር አልሞከርንም, ነገር ግን ከቅጥያው ገንቢዎች ጋር የመወያየት እድልን እያሰብን ነው. የኛ ወቅታዊ ክለሳ፣ በሚያሳዝን ሁኔታ፣ እስካሁን ለህትመት ዝግጁ አይደለም፣ ምክንያቱም ችግሩን የፈታነው በልዩ የዘገዩ ገደቦች ብቻ ነው፣ እና ለተሟላ ፕላስተር ለሌሎች አይነቶች ድጋፍ መደረግ አለበት። ወደፊትም ይህን ማድረግ እንደምንችል ተስፋ እናደርጋለን።

ምናልባት አንድ ጥያቄ ሊኖርህ ይችላል፣ ለምንድነው በpg_repack ማጣራት በዚህ ታሪክ ውስጥ የተሳተፍነው እና ለምሳሌ አናሎግዎቹን ያልተጠቀምንበት? በአንድ ወቅት, እኛ ደግሞ ስለእሱ አስበን ነበር, ነገር ግን ቀደም ሲል ጥቅም ላይ የዋለው አዎንታዊ ተሞክሮ, ያለዘገዩ ገደቦች በጠረጴዛዎች ላይ, የችግሩን ምንነት ለመረዳት እና ለማስተካከል እንድንሞክር አነሳስቶናል. በተጨማሪም ሌሎች መፍትሄዎችን መጠቀም ፈተናዎችን ለማካሄድ ጊዜን ይጠይቃል, ስለዚህ በመጀመሪያ ችግሩን ለማስተካከል እንሞክራለን, እና በተመጣጣኝ ጊዜ ማድረግ እንደማንችል ከተገነዘብን, አናሎግዎችን ግምት ውስጥ ማስገባት እንጀምራለን.

ግኝቶች

ከራሳችን ልምድ በመነሳት ልንመክረው እንችላለን፡-

1. እብጠትዎን ይቆጣጠሩ። በክትትል መረጃው ላይ በመመስረት አውቶቫክዩም ምን ያህል እንደተቀናበረ መረዳት ይችላሉ።
2. እብጠት ተቀባይነት ባለው ደረጃ ለማቆየት AUTOVACUUM ያዘጋጁ።
3. እብጠቱ አሁንም እያደገ ከሆነ እና ከሳጥኑ ውጭ ባሉ መሳሪያዎች ላይ መቋቋም ካልቻሉ ውጫዊ ቅጥያዎችን ለመጠቀም አይፍሩ. ዋናው ነገር ሁሉንም ነገር በደንብ መሞከር ነው.
4. የእርስዎን ፍላጎት ለማሟላት ውጫዊ መፍትሄዎችን ለመቀየር አይፍሩ - አንዳንድ ጊዜ የራስዎን ኮድ ከመቀየር የበለጠ ቀልጣፋ እና እንዲያውም ቀላል ሊሆን ይችላል.

ምንጭ: hab.com