Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Π Π°ΡΡˆΠΈΡ„Ρ€ΠΎΠ²ΠΊΠ° Π΄ΠΎΠΊΠ»Π°Π΄Π° 2015 Π³ΠΎΠ΄Π° АлСксСя ЛСсовского "Deep dive into PostgreSQL internal statistics"

Disclaimer ΠΎΡ‚ Π°Π²Ρ‚ΠΎΡ€Π° Π΄ΠΎΠΊΠ»Π°Π΄Π°: Π—Π°ΠΌΠ΅Ρ‡Ρƒ Ρ‡Ρ‚ΠΎ Π΄ΠΎΠΊΠ»Π°Π΄ этот Π΄Π°Ρ‚ΠΈΡ€ΠΎΠ²Π°Π½ ноябрСм 2015 Π³ΠΎΠ΄Π° β€” ΠΏΡ€ΠΎΡˆΠ»ΠΎ большС 4 Π»Π΅Ρ‚ ΠΈ ΠΏΡ€ΠΎΡˆΠ»ΠΎ ΠΌΠ½ΠΎΠ³ΠΎ Π²Ρ€Π΅ΠΌΠ΅Π½ΠΈ. РассматриваСмая Π² Π΄ΠΎΠΊΠ»Π°Π΄Π΅ вСрсия 9.4 ΡƒΠΆΠ΅ Π½Π΅ поддСрТиваСтся. Π—Π° ΠΏΡ€ΠΎΡˆΠ΅Π΄ΡˆΠΈΠ΅ 4 Π³ΠΎΠ΄Π° Π²Ρ‹ΡˆΠ»ΠΎ 5 Π½ΠΎΠ²Ρ‹Ρ… Ρ€Π΅Π»ΠΈΠ·ΠΎΠ² Π² ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Ρ… появилась масса Π½ΠΎΠ²ΡˆΠ΅ΡΡ‚Π², ΡƒΠ»ΡƒΡ‡ΡˆΠ΅Π½ΠΈΠΉ ΠΈ ΠΈΠ·ΠΌΠ΅Π½Π΅Π½ΠΈΠΉ ΠΎΡ‚Π½ΠΎΡΠΈΡ‚Π΅Π»ΡŒΠ½ΠΎ статистики ΠΈ Ρ‡Π°ΡΡ‚ΡŒ ΠΌΠ°Ρ‚Π΅Ρ€ΠΈΠ°Π»Π° устарСла ΠΈ Π½Π΅ Π°ΠΊΡ‚ΡƒΠ°Π»ΡŒΠ½Π°. По ΠΌΠ΅Ρ€Π΅ Ρ€Π΅Π²ΡŒΡŽ я постарался ΠΎΡ‚ΠΌΠ΅Ρ‚ΠΈΡ‚ΡŒ эти мСста Ρ‡Ρ‚ΠΎΠ±Ρ‹ Π½Π΅ Π²Π²ΠΎΠ΄ΠΈΡ‚ΡŒ тСбя Ρ‡ΠΈΡ‚Π°Ρ‚Π΅Π»ΡŒ Π² заблуТдСния. ΠŸΠ΅Ρ€Π΅ΠΏΠΈΡΡ‹Π²Π°Ρ‚ΡŒ ΠΆΠ΅ эти мСста я Π½Π΅ стал, ΠΈΡ… ΠΎΡ‡Π΅Π½ΡŒ ΠΌΠ½ΠΎΠ³ΠΎ ΠΈ получится Π² ΠΈΡ‚ΠΎΠ³Π΅ совсСм Π΄Ρ€ΡƒΠ³ΠΎΠΉ Π΄ΠΎΠΊΠ»Π°Π΄.

Π‘Π£Π‘Π” PostgreSQL β€” это ΠΎΠ³Ρ€ΠΎΠΌΠ½Ρ‹ΠΉ ΠΌΠ΅Ρ…Π°Π½ΠΈΠ·ΠΌ, ΠΏΡ€ΠΈ этом состоит этот ΠΌΠ΅Ρ…Π°Π½ΠΈΠ·ΠΌ ΠΈΠ· мноТСства подсистСм, ΠΎΡ‚ слаТСнной Ρ€Π°Π±ΠΎΡ‚Ρ‹ ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Ρ… Π½Π°ΠΏΡ€ΡΠΌΡƒΡŽ зависит ΠΏΡ€ΠΎΠΈΠ·Π²ΠΎΠ΄ΠΈΡ‚Π΅Π»ΡŒΠ½ΠΎΡΡ‚ΡŒ Π‘Π£Π‘Π”. Π’ процСссС эксплуатации обСспСчиваСтся сбор статистики ΠΈ ΠΈΠ½Ρ„ΠΎΡ€ΠΌΠ°Ρ†ΠΈΠΈ ΠΎ Ρ€Π°Π±ΠΎΡ‚Π΅ ΠΊΠΎΠΌΠΏΠΎΠ½Π΅Π½Ρ‚ΠΎΠ², Ρ‡Ρ‚ΠΎ позволяСт ΠΎΡ†Π΅Π½ΠΈΡ‚ΡŒ ΡΡ„Ρ„Π΅ΠΊΡ‚ΠΈΠ²Π½ΠΎΡΡ‚ΡŒ PostgreSQL ΠΈ ΠΏΡ€ΠΈΠ½ΡΡ‚ΡŒ ΠΌΠ΅Ρ€Ρ‹ для ΠΏΠΎΠ²Ρ‹ΡˆΠ΅Π½ΠΈΡ ΠΏΡ€ΠΎΠΈΠ·Π²ΠΎΠ΄ΠΈΡ‚Π΅Π»ΡŒΠ½ΠΎΡΡ‚ΠΈ. Однако, этой ΠΈΠ½Ρ„ΠΎΡ€ΠΌΠ°Ρ†ΠΈΠΈ ΠΎΡ‡Π΅Π½ΡŒ ΠΌΠ½ΠΎΠ³ΠΎ ΠΈ прСдставлСна ΠΎΠ½Π° Π² достаточно ΡƒΠΏΡ€ΠΎΡ‰Π΅Π½Π½ΠΎΠΌ Π²ΠΈΠ΄Π΅. ΠžΠ±Ρ€Π°Π±ΠΎΡ‚ΠΊΠ° этой ΠΈΠ½Ρ„ΠΎΡ€ΠΌΠ°Ρ†ΠΈΠΈ ΠΈ Π΅Π΅ интСрпрСтация ΠΏΠΎΡ€ΠΎΠΉ совсСм Π½Π΅Ρ‚Ρ€ΠΈΠ²ΠΈΠ°Π»ΡŒΠ½Π°Ρ Π·Π°Π΄Π°Ρ‡Π°, Π° "Π·ΠΎΠΎΠΏΠ°Ρ€ΠΊ" инструмСнтов ΠΈ ΡƒΡ‚ΠΈΠ»ΠΈΡ‚ запросто поставит Π² Ρ‚ΡƒΠΏΠΈΠΊ Π΄Π°ΠΆΠ΅ ΠΏΡ€ΠΎΠ΄Π²ΠΈΠ½ΡƒΡ‚ΠΎΠ³ΠΎ DBA.
Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский


Π”ΠΎΠ±Ρ€Ρ‹ΠΉ дСнь! МСня Π·ΠΎΠ²ΡƒΡ‚ АлСксСй. Как Илья сказал, я Π±ΡƒΠ΄Ρƒ Ρ€Π°ΡΡΠΊΠ°Π·Ρ‹Π²Π°Ρ‚ΡŒ ΠΏΡ€ΠΎ статистику PostgreSQL.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Бтатистика активности PostgreSQL. Π£ PostgreSQL Π΅ΡΡ‚ΡŒ Π΄Π²Π΅ статистики. Бтатистика активности, ΠΏΡ€ΠΎ ΠΊΠΎΡ‚ΠΎΡ€ΡƒΡŽ Π±ΡƒΠ΄Π΅Ρ‚ Ρ€Π΅Ρ‡ΡŒ. И статистика ΠΏΠ»Π°Π½ΠΈΡ€ΠΎΠ²Ρ‰ΠΈΠΊΠ° ΠΎ распрСдСлСнии Π΄Π°Π½Π½Ρ‹Ρ…. Π― Π±ΡƒΠ΄Ρƒ Ρ€Π°ΡΡΠΊΠ°Π·Ρ‹Π²Π°Ρ‚ΡŒ ΠΈΠΌΠ΅Π½Π½ΠΎ ΠΎ статистикС активности PostgreSQL, которая позволяСт Π½Π°ΠΌ ΡΡƒΠ΄ΠΈΡ‚ΡŒ ΠΎ ΠΏΡ€ΠΎΠΈΠ·Π²ΠΎΠ΄ΠΈΡ‚Π΅Π»ΡŒΠ½ΠΎΡΡ‚ΠΈ ΠΈ ΠΊΠ°ΠΊ-Ρ‚ΠΎ Π΅Π΅ ΡƒΠ»ΡƒΡ‡ΡˆΠ°Ρ‚ΡŒ.

РасскаТу, ΠΊΠ°ΠΊ эффСктивно ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ статистику для Ρ€Π΅ΡˆΠ΅Π½ΠΈΡ самых Ρ€Π°Π·Π½Ρ‹Ρ… ΠΏΡ€ΠΎΠ±Π»Π΅ΠΌ, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ Ρƒ вас Π²ΠΎΠ·Π½ΠΈΠΊΠ°ΡŽΡ‚ ΠΈΠ»ΠΈ ΠΌΠΎΠ³ΡƒΡ‚ Π²ΠΎΠ·Π½ΠΈΠΊΠ½ΡƒΡ‚ΡŒ.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Π§Π΅Π³ΠΎ Π½Π΅ Π±ΡƒΠ΄Π΅Ρ‚ Π² Π΄ΠΎΠΊΠ»Π°Π΄Π΅? Π’ Π΄ΠΎΠΊΠ»Π°Π΄Π΅ я Π½Π΅ Π±ΡƒΠ΄Ρƒ ΠΊΠ°ΡΠ°Ρ‚ΡŒΡΡ статистики ΠΏΠ»Π°Π½ΠΈΡ€ΠΎΠ²Ρ‰ΠΈΠΊΠ°, Ρ‚.ΠΊ. это ΠΎΡ‚Π΄Π΅Π»ΡŒΠ½Π°Ρ Ρ‚Π΅ΠΌΠ° Π½Π° ΠΎΡ‚Π΄Π΅Π»ΡŒΠ½Ρ‹ΠΉ Π΄ΠΎΠΊΠ»Π°Π΄ ΠΎ Ρ‚ΠΎΠΌ, ΠΊΠ°ΠΊ Π΄Π°Π½Π½Ρ‹Π΅ хранятся Π² Π±Π°Π·Π΅ ΠΈ ΠΎ Ρ‚ΠΎΠΌ ΠΊΠ°ΠΊ ΠΏΠ»Π°Π½ΠΈΡ€ΠΎΠ²Ρ‰ΠΈΠΊ запросов ΠΏΠΎΠ»ΡƒΡ‡Π°Π΅Ρ‚ прСдставлСниС ΠΎ качСствСнных ΠΈ количСствСнных характСриситках этих Π΄Π°Π½Π½Ρ‹Ρ….

И Π½Π΅ Π±ΡƒΠ΄Π΅Ρ‚ ΠΎΠ±Π·ΠΎΡ€ΠΎΠ² инструмСнтов, я Π½Π΅ Π±ΡƒΠ΄Ρƒ ΡΡ€Π°Π²Π½ΠΈΠ²Π°Ρ‚ΡŒ ΠΎΠ΄ΠΈΠ½ ΠΏΡ€ΠΎΠ΄ΡƒΠΊΡ‚ с Π΄Ρ€ΡƒΠ³ΠΈΠΌ. Никакой Ρ€Π΅ΠΊΠ»Π°ΠΌΡ‹ Π½Π΅ Π±ΡƒΠ΄Π΅Ρ‚. ΠžΡ‚Π±Ρ€ΠΎΡΠΈΠΌ это.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Π― Ρ…ΠΎΡ‡Ρƒ Π²Π°ΠΌ ΠΏΠΎΠΊΠ°Π·Π°Ρ‚ΡŒ, Ρ‡Ρ‚ΠΎ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ статистику – это ΠΏΠΎΠ»Π΅Π·Π½ΠΎ. Π­Ρ‚ΠΎ Π½ΡƒΠΆΠ½ΠΎ. Π˜ΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ Π΅Π΅ Π½Π΅ΡΡ‚Ρ€Π°ΡˆΠ½ΠΎ. Нам понадобится всСго лишь ΠΎΠ±Ρ‹Ρ‡Π½Ρ‹ΠΉ SQL ΠΈ Π±Π°Π·ΠΎΠ²Ρ‹Π΅ знания ΠΎ SQL.

И ΠΏΠΎΠ³ΠΎΠ²ΠΎΡ€ΠΈΠΌ, ΠΊΠ°ΠΊΡƒΡŽ статистику Π²Ρ‹Π±ΠΈΡ€Π°Ρ‚ΡŒ для Ρ€Π΅ΡˆΠ΅Π½ΠΈΡ ΠΏΡ€ΠΎΠ±Π»Π΅ΠΌ.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Если ΠΌΡ‹ посмотрим Π½Π° PostgreSQL ΠΈ Π² ΠΎΠΏΠ΅Ρ€Π°Ρ†ΠΈΠΎΠ½Π½ΠΎΠΉ систСмС запустим ΠΊΠΎΠΌΠ°Π½Π΄Ρƒ для просмотра процСссов, Ρ‚ΠΎ ΡƒΠ²ΠΈΠ΄ΠΈΠΌ "Ρ‡Π΅Ρ€Π½Ρ‹ΠΉ ящик". ΠœΡ‹ ΡƒΠ²ΠΈΠ΄ΠΈΠΌ ΠΊΠ°ΠΊΠΈΠ΅-Ρ‚ΠΎ процСссы, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ Ρ‡Ρ‚ΠΎ-Ρ‚ΠΎ Π΄Π΅Π»Π°ΡŽΡ‚, ΠΈ ΠΌΡ‹ ΠΏΠΎ названию ΠΌΠΎΠΆΠ΅ΠΌ ΠΏΡ€ΠΈΠΌΠ΅Ρ€Π½ΠΎ ΠΏΡ€Π΅Π΄ΡΡ‚Π°Π²ΠΈΡ‚ΡŒ, Ρ‡Ρ‚ΠΎ ΠΎΠ½ΠΈ Ρ‚Π°ΠΌ Π΄Π΅Π»Π°ΡŽΡ‚, Ρ‡Π΅ΠΌ Π·Π°Π½ΠΈΠΌΠ°ΡŽΡ‚ΡΡ. Но, ΠΏΠΎ сути, это Ρ‡Π΅Ρ€Π½Ρ‹ΠΉ ящик, Π²ΠΎΠ²Π½ΡƒΡ‚Ρ€ΡŒ ΠΌΡ‹ Π·Π°Π³Π»ΡΠ½ΡƒΡ‚ΡŒ Π½Π΅ ΠΌΠΎΠΆΠ΅ΠΌ.

ΠœΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ Π½Π°Π³Ρ€ΡƒΠ·ΠΊΡƒ Π½Π° процСссор Π² top, ΠΌΠΎΠΆΠ΅ΠΌ ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ ΡƒΡ‚ΠΈΠ»ΠΈΠ·Π°Ρ†ΠΈΡŽ памяти ΠΊΠ°ΠΊΠΈΠΌΠΈ-Ρ‚ΠΎ систСмными ΡƒΡ‚ΠΈΠ»ΠΈΡ‚Π°ΠΌΠΈ, Π½ΠΎ Π·Π°Π³Π»ΡΠ½ΡƒΡ‚ΡŒ Π²ΠΎΠ²Π½ΡƒΡ‚Ρ€ΡŒ PostgreSQL ΠΌΡ‹ Π½Π΅ смоТСм. Для этого Π½Π°ΠΌ Π½ΡƒΠΆΠ½Ρ‹ Π΄Ρ€ΡƒΠ³ΠΈΠ΅ инструмСнты.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

И продолТая дальшС я расскаТу, ΠΊΡƒΠ΄Π° тратится врСмя. Если ΠΌΡ‹ прСдставим PostgreSQL Π² Π²ΠΈΠ΄Π΅ Ρ‚Π°ΠΊΠΎΠΉ схСмы, Ρ‚ΠΎ ΠΌΠΎΠΆΠ½ΠΎ Π±ΡƒΠ΄Π΅Ρ‚ ΠΎΡ‚Π²Π΅Ρ‚ΠΈΡ‚ΡŒ, ΠΊΡƒΠ΄Π° тратится врСмя. Π­Ρ‚ΠΎ Π΄Π²Π΅ Π²Π΅Ρ‰ΠΈ: это ΠΎΠ±Ρ€Π°Π±ΠΎΡ‚ΠΊΠ° клиСнтских запросов ΠΎΡ‚ ΠΏΡ€ΠΈΠ»ΠΎΠΆΠ΅Π½ΠΈΠΉ ΠΈ Ρ„ΠΎΠ½ΠΎΠ²Ρ‹Π΅ Π·Π°Π΄Π°Ρ‡ΠΈ, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ выполняСт PostgreSQL для поддСрТания своСй работоспособности.

Если ΠΌΡ‹ Π½Π°Ρ‡Π½Π΅ΠΌ Ρ€Π°ΡΡΠΌΠ°Ρ‚Ρ€ΠΈΠ²Π°Ρ‚ΡŒ с Π»Π΅Π²ΠΎΠ³ΠΎ Π²Π΅Ρ€Ρ…Π½Π΅Π³ΠΎ ΡƒΠ³Π»Π°, Ρ‚ΠΎ ΠΌΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ ΠΏΡ€ΠΎΡΠ»Π΅Π΄ΠΈΡ‚ΡŒ, ΠΊΠ°ΠΊ обрабатываСтся клиСнтскиС запросы. Запрос ΠΏΡ€ΠΈΡ…ΠΎΠ΄ΠΈΡ‚ ΠΎΡ‚ прилоТСния ΠΈ для дальнСйшСй Ρ€Π°Π±ΠΎΡ‚Ρ‹ открываСтся клиСнтская сСссия. Запрос пСрСдаСтся Π² ΠΏΠ»Π°Π½ΠΈΡ€ΠΎΠ²Ρ‰ΠΈΠΊ. ΠŸΠ»Π°Π½ΠΈΡ€ΠΎΠ²Ρ‰ΠΈΠΊ строит ΠΏΠ»Π°Π½ запроса. ΠžΡ‚ΠΏΡ€Π°Π²Π»ΡΠ΅Ρ‚ Π΅Π³ΠΎ дальшС Π½Π° Π²Ρ‹ΠΏΠΎΠ»Π½Π΅Π½ΠΈΠ΅. ΠŸΡ€ΠΎΠΈΡΡ…ΠΎΠ΄ΠΈΡ‚ ΠΊΠ°ΠΊΠΎΠΉ-Ρ‚ΠΎ Π±Π»ΠΎΡ‡Π½Ρ‹ΠΉ Π²Π²ΠΎΠ΄-Π²Ρ‹Π²ΠΎΠ΄ Π΄Π°Π½Π½Ρ‹Ρ… связанный с Ρ‚Π°Π±Π»ΠΈΡ†Π°ΠΌΠΈ ΠΈ индСксами. НСобходимыС Π΄Π°Π½Π½Ρ‹Π΅ Ρ‡ΠΈΡ‚Π°ΡŽΡ‚ΡΡ с дисков Π² ΠΏΠ°ΠΌΡΡ‚ΡŒ Π² ΡΠΏΠ΅Ρ†ΠΈΠ°Π»ΡŒΠ½ΡƒΡŽ ΠΎΠ±Π»Π°ΡΡ‚ΡŒ "shared buffers". Π Π΅Π·ΡƒΠ»ΡŒΡ‚Π°Ρ‚Ρ‹ запроса, Ссли это updates, deletes, Ρ„ΠΈΠΊΡΠΈΡ€ΡƒΡŽΡ‚ΡΡ Π² ΠΆΡƒΡ€Π½Π°Π»Π΅ Ρ‚Ρ€Π°Π½Π·Π°ΠΊΡ†ΠΈΠΉ Π² WAL. НСкоторая статистичСская информация ΠΏΠΎΠΏΠ°Π΄Π°Π΅Ρ‚ Π² Π»ΠΎΠ³ ΠΈΠ»ΠΈ Π² ΠΊΠΎΠ»Π»Π΅ΠΊΡ‚ΠΎΡ€ статистики. И Ρ€Π΅Π·ΡƒΠ»ΡŒΡ‚Π°Ρ‚ запроса отдаСтся ΡƒΠΆΠ΅ ΠΊΠ»ΠΈΠ΅Π½Ρ‚Ρƒ ΠΎΠ±Ρ€Π°Ρ‚Π½ΠΎ. ПослС Ρ‡Π΅Π³ΠΎ ΠΊΠ»ΠΈΠ΅Π½Ρ‚ ΠΌΠΎΠΆΠ΅Ρ‚ ΠΏΠΎΠ²Ρ‚ΠΎΡ€ΠΈΡ‚ΡŒ всС Π·Π°Π½ΠΎΠ³ΠΎ с Π½ΠΎΠ²Ρ‹ΠΌ запросом.

Π§Ρ‚ΠΎ Ρƒ нас с Ρ„ΠΎΠ½ΠΎΠ²Ρ‹ΠΌΠΈ Π·Π°Π΄Π°Ρ‡Π°ΠΌΠΈ ΠΈ с Ρ„ΠΎΠ½ΠΎΠ²Ρ‹ΠΌΠΈ процСссами? Π£ нас Π΅ΡΡ‚ΡŒ нСсколько процСссов, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ ΠΎΠ±Π΅ΡΠΏΠ΅Ρ‡ΠΈΠ²Π°ΡŽΡ‚ Ρ€Π°Π±ΠΎΡ‚ΠΎΡΠΏΠΎΡΠΎΠ±Π½ΠΎΡΡ‚ΡŒ ΠΈ ΠΏΠΎΠ΄Π΄Π΅Ρ€ΠΆΠΈΠ²Π°ΡŽΡ‚ Π±Π°Π·Ρƒ Π΄Π°Π½Π½Ρ‹Ρ… Π² Π½ΠΎΡ€ΠΌΠ°Π»ΡŒΠ½ΠΎΠΌ Ρ€Π°Π±ΠΎΡ‡Π΅ΠΌ Ρ€Π΅ΠΆΠΈΠΌΠ΅. Π­Ρ‚ΠΈ процСссы Ρ‚Π°ΠΊΠΆΠ΅ Π±ΡƒΠ΄ΡƒΡ‚ Π·Π°Ρ‚Ρ€Π°Π³ΠΈΠ²Π°Ρ‚ΡŒΡΡ Π² Π΄ΠΎΠΊΠ»Π°Π΄Π΅: это autovacuum, checkpointer, процСссы, связанныС с Ρ€Π΅ΠΏΠ»ΠΈΠΊΠ°Ρ†ΠΈΠ΅ΠΉ, background writer. КаТдого ΠΈΠ· Π½ΠΈΡ… я Π±ΡƒΠ΄Ρƒ Π·Π°Ρ‚Ρ€Π°Π³ΠΈΠ²Π°Ρ‚ΡŒ ΠΏΠΎ ΠΌΠ΅Ρ€Π΅ Π΄ΠΎΠΊΠ»Π°Π΄Π°.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

КакиС ΠΏΡ€ΠΎΠ±Π»Π΅ΠΌΡ‹ Π΅ΡΡ‚ΡŒ со статистикой?

  • Π˜Π½Ρ„ΠΎΡ€ΠΌΠ°Ρ†ΠΈΠΈ ΠΌΠ½ΠΎΠ³ΠΎ. PostgreSQL 9.4 прСдоставляСт 109 ΠΌΠ΅Ρ‚Ρ€ΠΈΠΊ для просмотра Π΄Π°Π½Π½Ρ‹Ρ… статистики. Однако, Ссли Π² Π±Π°Π·Π΅ Π΄Π°Π½Π½Ρ‹Ρ… хранятся ΠΌΠ½ΠΎΠ³ΠΎ Ρ‚Π°Π±Π»ΠΈΡ†, схСм, Π±Π°Π·, Ρ‚ΠΎ всС эти ΠΌΠ΅Ρ‚Ρ€ΠΈΠΊΠΈ придСтся ΡƒΠΌΠ½ΠΎΠΆΠΈΡ‚ΡŒ Π½Π° ΡΠΎΠΎΡ‚Π²Π΅Ρ‚ΡΡ‚Π²ΡƒΡŽΡ‰Π΅Π΅ количСство Ρ‚Π°Π±Π»ΠΈΡ†, Π±Π°Π·. Π’. Π΅. ΠΈΠ½Ρ„ΠΎΡ€ΠΌΠ°Ρ†ΠΈΠΈ становится Π΅Ρ‰Π΅ большС. И ΡƒΡ‚ΠΎΠ½ΡƒΡ‚ΡŒ Π² Π½Π΅ΠΉ ΠΎΡ‡Π΅Π½ΡŒ Π»Π΅Π³ΠΊΠΎ.
  • Π‘Π»Π΅Π΄ΡƒΡŽΡ‰Π°Ρ ΠΏΡ€ΠΎΠ±Π»Π΅ΠΌΠ° – это Ρ‚ΠΎ, Ρ‡Ρ‚ΠΎ статистика прСдставлСна счСтчиками. Если ΠΌΡ‹ посмотрим эту статистику, Ρ‚ΠΎ ΠΌΡ‹ ΡƒΠ²ΠΈΠ΄ΠΈΠΌ постоянно ΡƒΠ²Π΅Π»ΠΈΡ‡ΠΈΠ²Π°ΡŽΡ‰ΠΈΠ΅ΡΡ счСтчики. И Ссли с ΠΌΠΎΠΌΠ΅Π½Ρ‚Π° сброса статистики ΠΏΡ€ΠΎΡˆΠ»ΠΎ ΠΎΡ‡Π΅Π½ΡŒ ΠΌΠ½ΠΎΠ³ΠΎ Π²Ρ€Π΅ΠΌΠ΅Π½ΠΈ, ΠΌΡ‹ ΡƒΠ²ΠΈΠ΄ΠΈΠΌ ΠΌΠΈΠ»Π»ΠΈΠ°Ρ€Π΄Π½Ρ‹Π΅ значСния. И ΠΎΠ½ΠΈ Π½Π°ΠΌ Π½ΠΈΡ‡Π΅Π³ΠΎ Π½Π΅ говорят.
  • НСт истории. Если Ρƒ вас ΠΏΡ€ΠΎΠΈΠ·ΠΎΡˆΠ΅Π» ΠΊΠ°ΠΊΠΎΠΉ-Ρ‚ΠΎ сбой, Ρ‡Ρ‚ΠΎ-Ρ‚ΠΎ ΡƒΠΏΠ°Π»ΠΎ 15-30 ΠΌΠΈΠ½ΡƒΡ‚ Π½Π°Π·Π°Π΄, Π½Π΅ получится Π²ΠΎΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒΡΡ статистикой ΠΈ ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ, Ρ‡Ρ‚ΠΎ происходило 15-30 ΠΌΠΈΠ½ΡƒΡ‚ Π½Π°Π·Π°Π΄. Π­Ρ‚ΠΎ ΠΏΡ€ΠΎΠ±Π»Π΅ΠΌΠ°.
  • ΠžΡ‚ΡΡƒΡ‚ΡΡ‚Π²ΠΈΠ΅ встроСнного Π² PostgreSQL инструмСнта – это ΠΏΡ€ΠΎΠ±Π»Π΅ΠΌΠ°. Π Π°Π·Ρ€Π°Π±ΠΎΡ‚Ρ‡ΠΈΠΊΠΈ ядра Π½Π΅ ΠΏΡ€Π΅Π΄ΠΎΡΡ‚Π°Π²Π»ΡΡŽΡ‚ Π½ΠΈΠΊΠ°ΠΊΠΎΠΉ ΡƒΡ‚ΠΈΠ»ΠΈΡ‚Ρ‹. Π£ Π½ΠΈΡ… Π½Π΅Ρ‚ Π½ΠΈΡ‡Π΅Π³ΠΎ Ρ‚Π°ΠΊΠΎΠ³ΠΎ. Они просто Π΄Π°ΡŽΡ‚ статистику Π² Π±Π°Π·Π΅. ΠŸΠΎΠ»ΡŒΠ·ΡƒΠΉΡ‚Π΅ΡΡŒ, Π΄Π΅Π»Π°ΠΉΡ‚Π΅ ΠΊ Π½Π΅ΠΉ запрос, Ρ‡Ρ‚ΠΎ Ρ…ΠΎΡ‚ΠΈΡ‚Π΅, Ρ‚ΠΎ ΠΈ Π΄Π΅Π»Π°ΠΉΡ‚Π΅.
  • Π’Π°ΠΊ ΠΊΠ°ΠΊ встроСнного Π² PostgreSQL инструмСнта Π½Π΅Ρ‚, Ρ‚ΠΎ это являСтся ΠΏΡ€ΠΈΡ‡ΠΈΠ½ΠΎΠΉ Π΄Ρ€ΡƒΠ³ΠΎΠΉ ΠΏΡ€ΠΎΠ±Π»Π΅ΠΌΡ‹. ΠœΠ½ΠΎΠΆΠ΅ΡΡ‚Π²ΠΎ сторонних инструмСнтов. КаТдая компания, Ρƒ ΠΊΠΎΡ‚ΠΎΡ€ΠΎΠΉ Π΅ΡΡ‚ΡŒ Π±ΠΎΠ»Π΅Π΅-ΠΌΠ΅Π½Π΅Π΅ прямыС Ρ€ΡƒΠΊΠΈ, пытаСтся Π½Π°ΠΏΠΈΡΠ°Ρ‚ΡŒ свою ΠΏΡ€ΠΎΠ³Ρ€Π°ΠΌΠΌΡƒ. И Π² ΠΈΡ‚ΠΎΠ³Π΅ Π² community ΠΎΡ‡Π΅Π½ΡŒ ΠΌΠ½ΠΎΠ³ΠΎ инструмСнтов, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹ΠΌΠΈ ΠΌΠΎΠΆΠ½ΠΎ ΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒΡΡ для Ρ€Π°Π±ΠΎΡ‚Ρ‹ со статистикой. И Π² ΠΎΠ΄Π½ΠΈΡ… инструмСнтах Π΅ΡΡ‚ΡŒ ΠΎΠ΄Π½ΠΈ возмоТности, Π² Π΄Ρ€ΡƒΠ³ΠΈΡ… инструмСнтах Π½Π΅Ρ‚ Π΄Ρ€ΡƒΠ³ΠΈΡ… возмоТностСй, Π»ΠΈΠ±ΠΎ Π΅ΡΡ‚ΡŒ ΠΊΠ°ΠΊΠΈΠ΅-Ρ‚ΠΎ Π½ΠΎΠ²Ρ‹Π΅ возмоТности. И Π²ΠΎΠ·Π½ΠΈΠΊΠ°Π΅Ρ‚ ситуация, Ρ‡Ρ‚ΠΎ Π½ΡƒΠΆΠ½ΠΎ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ Π΄Π²Π°-Ρ‚Ρ€ΠΈ-Ρ‡Π΅Ρ‚Ρ‹Ρ€Π΅ инструмСнта, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ Π΄Ρ€ΡƒΠ³ Π΄Ρ€ΡƒΠ³Π° ΠΏΠ΅Ρ€Π΅ΠΊΡ€Ρ‹Π²Π°ΡŽΡ‚ ΠΈ ΠΎΠ±Π»Π°Π΄Π°ΡŽΡ‚ Ρ€Π°Π·Π½Ρ‹ΠΌΠΈ функциями. Π­Ρ‚ΠΎ ΠΎΡ‡Π΅Π½ΡŒ нСприятно.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Π§Ρ‚ΠΎ ΠΈΠ· этого слСдуСт? Π’Π°ΠΆΠ½ΠΎ ΡƒΠΌΠ΅Ρ‚ΡŒ Π±Ρ€Π°Ρ‚ΡŒ статистику Π½Π°ΠΏΡ€ΡΠΌΡƒΡŽ, Ρ‡Ρ‚ΠΎΠ±Ρ‹ Π½Π΅ Π·Π°Π²ΠΈΡΠ΅Ρ‚ΡŒ ΠΎΡ‚ ΠΏΡ€ΠΎΠ³Ρ€Π°ΠΌΠΌ, Π»ΠΈΠ±ΠΎ ΠΊΠ°ΠΊ-Ρ‚ΠΎ самому ΡƒΠ»ΡƒΡ‡ΡˆΠΈΡ‚ΡŒ эти ΠΏΡ€ΠΎΠ³Ρ€Π°ΠΌΠΌΡ‹: Π΄ΠΎΠ±Π°Π²ΠΈΡ‚ΡŒ ΠΊΠ°ΠΊΠΈΠ΅-Ρ‚ΠΎ Ρ„ΡƒΠ½ΠΊΡ†ΠΈΠΈ, Ρ‡Ρ‚ΠΎΠ±Ρ‹ ΠΏΠΎΠ»ΡƒΡ‡ΠΈΡ‚ΡŒ свою Π²Ρ‹Π³ΠΎΠ΄Ρƒ.

И Π½ΡƒΠΆΠ½Ρ‹ Π±Π°Π·ΠΎΠ²Ρ‹Π΅ знания SQL. Π§Ρ‚ΠΎΠ±Ρ‹ ΠΏΠΎΠ»ΡƒΡ‡ΠΈΡ‚ΡŒ ΠΊΠ°ΠΊΠΈΠ΅-Ρ‚ΠΎ Π΄Π°Π½Π½Ρ‹Π΅ ΠΈΠ· статистики, Π½ΡƒΠΆΠ½ΠΎ ΡΠΎΡΡ‚Π°Π²ΠΈΡ‚ΡŒ запросы SQL, Ρ‚. Π΅. Π²Π°ΠΌ Π½ΡƒΠΆΠ½ΠΎ Π·Π½Π°Ρ‚ΡŒ, ΠΊΠ°ΠΊ ΡΠΎΡΡ‚Π°Π²Π»ΡΡŽΡ‚ΡΡ select, join.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Бтатистика ΠΏΡ€Π΅Π΄Π»Π°Π³Π°Π΅Ρ‚ Π½Π°ΠΌ нСсколько Π²Π΅Ρ‰Π΅ΠΉ. Π˜Ρ… ΠΌΠΎΠΆΠ½ΠΎ Ρ€Π°Π·Π΄Π΅Π»ΠΈΡ‚ΡŒ Π½Π° ΠΊΠ°Ρ‚Π΅Π³ΠΎΡ€ΠΈΠΈ.

  • ΠŸΠ΅Ρ€Π²Π°Ρ катСгория – это события, происходящиС Π² Π±Π°Π·Π΅. Π­Ρ‚ΠΎ ΠΊΠΎΠ³Π΄Π° Π² Π±Π°Π·Π΅ происходит ΠΊΠ°ΠΊΠΎΠ΅-Ρ‚ΠΎ событиС: запрос, ΠΎΠ±Ρ€Π°Ρ‰Π΅Π½ΠΈΠ΅ ΠΊ Ρ‚Π°Π±Π»ΠΈΡ†Π΅, Π°Π²Ρ‚ΠΎΠ²Π°ΠΊΡƒΡƒΠΌ, ΠΊΠΎΠΌΠΌΠΈΡ‚Ρ‹, Ρ‚ΠΎ это всС события. Π‘ΠΎΠΎΡ‚Π²Π΅Ρ‚ΡΡ‚Π²ΡƒΡŽΡ‰ΠΈΠ΅ этим события счСтчики ΠΈΠ½ΠΊΡ€Π΅ΠΌΠ΅Π½Ρ‚ΠΈΡ€ΡƒΡŽΡ‚ΡΡ. И ΠΌΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ ΠΎΡ‚ΡΠ»Π΅Π΄ΠΈΡ‚ΡŒ эти события.
  • Вторая катСгория – это свойства ΠΎΠ±ΡŠΠ΅ΠΊΡ‚ΠΎΠ² Ρ‚Π°ΠΊΠΈΠ΅, ΠΊΠ°ΠΊ Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹, Π±Π°Π·Ρ‹. Π£ Π½ΠΈΡ… Π΅ΡΡ‚ΡŒ свойства. Π­Ρ‚ΠΎ Ρ€Π°Π·ΠΌΠ΅Ρ€ Ρ‚Π°Π±Π»ΠΈΡ†. ΠœΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ ΠΎΡ‚ΡΠ»Π΅Π΄ΠΈΡ‚ΡŒ рост Ρ‚Π°Π±Π»ΠΈΡ†, рост индСксов. МоТСм ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ измСнСния Π² Π΄ΠΈΠ½Π°ΠΌΠΈΠΊΠ΅.
  • И Ρ‚Ρ€Π΅Ρ‚ΡŒΡ катСгория – это врСмя, Π·Π°Ρ‚Ρ€Π°Ρ‡Π΅Π½Π½ΠΎΠ΅ Π½Π° событиС. Запрос – это событиС. Π£ Π½Π΅Π³ΠΎ Π΅ΡΡ‚ΡŒ своя конкрСтная ΠΌΠ΅Ρ€Π° Π΄Π»ΠΈΡ‚Π΅Π»ΡŒΠ½ΠΎΡΡ‚ΠΈ. Π—Π΄Π΅ΡΡŒ запустился, Ρ‚ΡƒΡ‚ закончился. ΠœΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ это ΠΎΡ‚ΡΠ»Π΅Π΄ΠΈΡ‚ΡŒ. Π›ΠΈΠ±ΠΎ врСмя чтСния Π±Π»ΠΎΠΊΠ° с диска ΠΈΠ»ΠΈ записи. Π’Π°ΠΊΠΈΠ΅ Π²Π΅Ρ‰ΠΈ Ρ‚ΠΎΠΆΠ΅ ΠΎΡ‚ΡΠ»Π΅ΠΆΠΈΠ²Π°ΡŽΡ‚ΡΡ.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Π˜ΡΡ‚ΠΎΡ‡Π½ΠΈΠΊΠΈ статистики прСдставлСны ΡΠ»Π΅Π΄ΡƒΡŽΡ‰ΠΈΠΌ ΠΎΠ±Ρ€Π°Π·ΠΎΠΌ:

  • Π’ раздСляСмой памяти (shared buffers) Π΅ΡΡ‚ΡŒ сСгмСнт для размСщСния Ρ‚Π°ΠΌ статичтичСских Π΄Π°Π½Π½Ρ‹Ρ…, Ρ‚Π°ΠΌ Π΅ΡΡ‚ΡŒ ΠΈ Ρ‚Π΅ самыС счСтчики, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ постоянно ΠΈΠ½ΠΊΡ€Π΅ΠΌΠ΅Π½Ρ‚ΠΈΡ€ΡƒΡŽΡ‚ΡΡ, ΠΊΠΎΠ³Π΄Π° происходит Ρ‚Π΅ ΠΈΠ»ΠΈ ΠΈΠ½Ρ‹Π΅ события, Π»ΠΈΠ±ΠΎ Π²ΠΎΠ·Π½ΠΈΠΊΠ°ΡŽΡ‚ ΠΊΠ°ΠΊΠΈΠ΅-Ρ‚ΠΎ ΠΌΠΎΠΌΠ΅Π½Ρ‚Ρ‹ Π² Ρ€Π°Π±ΠΎΡ‚Π΅ Π±Π°Π·Ρ‹.
  • ВсС эти счСтчики Π½Π΅ доступны ΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚Π΅Π»ΡŽ ΠΈ Π΄Π°ΠΆΠ΅ Π½Π΅ доступны администратору. Π­Ρ‚ΠΎ Π½ΠΈΠ·ΠΊΠΎΡƒΡ€ΠΎΠ²Π½Π΅Π²Ρ‹Π΅ Π²Π΅Ρ‰ΠΈ. Π§Ρ‚ΠΎΠ±Ρ‹ ΠΊ Π½ΠΈΠΌ ΠΎΠ±Ρ€Π°Ρ‚ΠΈΡ‚ΡŒΡΡ PostgreSQL прСдоставляСт интСрфСйс Π² Π²ΠΈΠ΄Π΅ SQL Ρ„ΡƒΠ½ΠΊΡ†ΠΈΠΉ. ΠœΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ ΡΠ΄Π΅Π»Π°Ρ‚ΡŒ select Π²Ρ‹Π±Ρ€ΠΎΠΊΠΈ с ΠΏΠΎΠΌΠΎΡ‰ΡŒΡŽ этих Ρ„ΡƒΠ½ΠΊΡ†ΠΈΠΉ ΠΈ ΠΏΠΎΠ»ΡƒΡ‡ΠΈΡ‚ΡŒ ΠΊΠ°ΠΊΡƒΡŽ-Ρ‚ΠΎ ΠΌΠ΅Ρ‚Ρ€ΠΈΠΊΡƒ (ΠΈΠ»ΠΈ Π½Π°Π±ΠΎΡ€ ΠΌΠ΅Ρ‚Ρ€ΠΈΠΊ).
  • Однако ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ эти Ρ„ΡƒΠ½ΠΊΡ†ΠΈΠΈ Π½Π΅ всСгда ΡƒΠ΄ΠΎΠ±Π½ΠΎ, поэтому Ρ„ΡƒΠ½ΠΊΡ†ΠΈΠΈ ΡΠ²Π»ΡΡŽΡ‚ΡΡ Π±Π°Π·ΠΎΠΉ для прСдставлСний (VIEWs). Π­Ρ‚ΠΎ Π²ΠΈΡ€Ρ‚ΡƒΠ°Π»ΡŒΠ½Ρ‹Π΅ Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ ΠΏΡ€Π΅Π΄ΠΎΡΡ‚Π°Π²Π»ΡΡŽΡ‚ статистику ΠΏΠΎ ΠΊΠ°ΠΊΠΎΠΉ-Ρ‚ΠΎ ΠΊΠΎΠ½ΠΊΡ€Π΅Ρ‚Π½ΠΎΠΉ подсистСмС, Π»ΠΈΠ±ΠΎ ΠΏΠΎ ΠΊΠ°ΠΊΠΎΠΌΡƒ-Ρ‚ΠΎ Π½Π°Π±ΠΎΡ€Ρƒ событий Π² Π±Π°Π·Π΅ Π΄Π°Π½Π½Ρ‹Ρ….
  • Π­Ρ‚ΠΈ встроСнныС прСдставлСния (VIEWs) ΡΠ²Π»ΡΡŽΡ‚ΡΡ основным интСрфСйсом ΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚Π΅Π»Ρ для Ρ€Π°Π±ΠΎΡ‚Ρ‹ со статистикой. Они доступны ΠΏΠΎ-ΡƒΠΌΠΎΠ»Ρ‡Π°Π½ΠΈΡŽ Π±Π΅Π· ΠΊΠ°ΠΊΠΎΠΉ Π»ΠΈΠ±ΠΎ Π΄ΠΎΠΏΠΎΠ»Π½ΠΈΡ‚Π΅Π»ΡŒΠ½ΠΎΠΉ настройки, ΠΌΠΎΠΆΠ΅Ρ‚Π΅ сразу ΠΈΠΌΠΈ ΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒΡΡ, ΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ, Π±Ρ€Π°Ρ‚ΡŒ ΠΎΡ‚Ρ‚ΡƒΠ΄Π° ΠΈΠ½Ρ„ΠΎΡ€ΠΌΠ°Ρ†ΠΈΡŽ. А Π΅Ρ‰Π΅ Π΅ΡΡ‚ΡŒ contrib’Ρ‹. Contrib’Ρ‹ Π΅ΡΡ‚ΡŒ ΠΎΡ„ΠΈΡ†ΠΈΠ°Π»ΡŒΠ½Ρ‹Π΅. Π’Ρ‹ ΠΌΠΎΠΆΠ΅Ρ‚Π΅ ΡƒΡΡ‚Π°Π½ΠΎΠ²ΠΈΡ‚ΡŒ ΠΏΠ°ΠΊΠ΅Ρ‚ postgresql-contrib (Π½Π°ΠΏΡ€ΠΈΠΌΠ΅Ρ€, postgresql94-contrib), ΠΏΠΎΠ΄Π³Ρ€ΡƒΠ·ΠΈΠ»ΠΈ Π½Π΅ΠΎΠ±Ρ…ΠΎΠ΄ΠΈΠΌΡ‹ΠΉ ΠΌΠΎΠ΄ΡƒΠ»ΡŒ Π² ΠΊΠΎΠ½Ρ„ΠΈΠ³ΡƒΡ€Π°Ρ†ΠΈΠΈ, ΡƒΠΊΠ°Π·Π°Ρ‚ΡŒ для Π½Π΅Π³ΠΎ ΠΏΠ°Ρ€Π°ΠΌΠ΅Ρ‚Ρ€Ρ‹, ΠΏΠ΅Ρ€Π΅Π·Π°ΠΏΡƒΡΡ‚ΠΈΡ‚ΡŒ PostgreSQL ΠΈ ΠΌΠΎΠΆΠ½ΠΎ ΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒΡΡ. (ΠŸΡ€ΠΈΠΌΠ΅Ρ‡Π°Π½ΠΈΠ΅. Π’ зависимости ΠΎΡ‚ дистрибутива, Π² послСдних вСрсиях contrib ΠΏΠ°ΠΊΠ΅Ρ‚ являСтся Ρ‡Π°ΡΡ‚ΡŒΡŽ основного ΠΏΠ°ΠΊΠ΅Ρ‚Π°).
  • И Π΅ΡΡ‚ΡŒ Π½Π΅ΠΎΡ„ΠΈΡ†ΠΈΠ°Π»ΡŒΠ½Ρ‹Π΅ contrib. Они Π½Π΅ ΠΏΠΎΡΡ‚Π°Π²Π»ΡΡŽΡ‚ΡΡ Π² стандартной поставкС PostgreSQL. Π˜Ρ… Π½ΡƒΠΆΠ½ΠΎ Π»ΠΈΠ±ΠΎ ΡΠΊΠΎΠΌΠΏΠΈΠ»ΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ, Π»ΠΈΠ±ΠΎ ΡƒΡΡ‚Π°Π½ΠΎΠ²ΠΈΡ‚ΡŒ ΠΊΠ°ΠΊ Π±ΠΈΠ±Π»ΠΈΠΎΡ‚Π΅ΠΊΡƒ. Π’Π°Ρ€ΠΈΠ°Π½Ρ‚Ρ‹ ΠΌΠΎΠ³ΡƒΡ‚ Π±Ρ‹Ρ‚ΡŒ самыС Ρ€Π°Π·Π½Ρ‹Π΅, Π² зависимости ΠΎΡ‚ Ρ‚ΠΎΠ³ΠΎ, Ρ‡Ρ‚ΠΎ ΠΏΡ€ΠΈΠ΄ΡƒΠΌΠ°Π» Ρ€Π°Π·Ρ€Π°Π±ΠΎΡ‚Ρ‡ΠΈΠΊ этого Π½Π΅ΠΎΡ„ΠΈΡ†ΠΈΠ°Π»ΡŒΠ½ΠΎΠ³ΠΎ contrib’а.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

На этом слайдС прСдставлСны всС Ρ‚Π΅ прСдставлСния (VIEWs) ΠΈ Ρ‡Π°ΡΡ‚ΡŒ Ρ‚Π΅Ρ… Ρ„ΡƒΠ½ΠΊΡ†ΠΈΠΉ, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ доступны Π² PostgreSQL 9.4. Как ΠΌΡ‹ Π²ΠΈΠ΄ΠΈΠΌ, ΠΈΡ… ΠΎΡ‡Π΅Π½ΡŒ ΠΌΠ½ΠΎΠ³ΠΎ. И довольно Π»Π΅Π³ΠΊΠΎ Π·Π°ΠΏΡƒΡ‚Π°Ρ‚ΡŒΡΡ, Ссли Π²Ρ‹ ΡΡ‚ΠΎΠ»ΠΊΠ½ΡƒΠ»ΠΈΡΡŒ с этим Π² ΠΏΠ΅Ρ€Π²Ρ‹ΠΉ Ρ€Π°Π·.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Однако, Ссли ΠΌΡ‹ возьмСм ΠΏΡ€Π΅Π΄Ρ‹Π΄ΡƒΡ‰ΡƒΡŽ ΠΊΠ°Ρ€Ρ‚ΠΈΠ½ΠΊΡƒ Как тратится врСмя Π½Π° PostgreSQL ΠΈ совмСстим с этим списком, Ρ‚ΠΎ ΠΏΠΎΠ»ΡƒΡ‡ΠΈΠΌ Π²ΠΎΡ‚ Ρ‚Π°ΠΊΡƒΡŽ ΠΊΠ°Ρ€Ρ‚ΠΈΠ½ΠΊΡƒ. КаТдоС прСдставлСниС (VIEWs), Π»ΠΈΠ±ΠΎ ΠΊΠ°ΠΆΠ΄ΡƒΡŽ Ρ„ΡƒΠ½ΠΊΡ†ΠΈΡŽ ΠΌΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ Π² Ρ‚Π΅Ρ… ΠΈΠ»ΠΈ ΠΈΠ½Ρ‹Ρ… цСлях для получСния ΡΠΎΠΎΡ‚Π²Π΅Ρ‚ΡΡ‚Π²ΡƒΡŽΡ‰Π΅ΠΉ статистики, ΠΊΠΎΠ³Π΄Π° Ρƒ нас Ρ€Π°Π±ΠΎΡ‚Π°Π΅Ρ‚ PostgreSQL. И ΠΌΠΎΠΆΠ΅ΠΌ ΠΏΠΎΠ»ΡƒΡ‡ΠΈΡ‚ΡŒ ΡƒΠΆΠ΅ ΠΊΠ°ΠΊΡƒΡŽ-Ρ‚ΠΎ ΠΈΠ½Ρ„ΠΎΡ€ΠΌΠ°Ρ†ΠΈΡŽ ΠΎ Ρ€Π°Π±ΠΎΡ‚Π΅ подсистСмы.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

ΠŸΠ΅Ρ€Π²ΠΎΠ΅, Ρ‡Ρ‚ΠΎ ΠΌΡ‹ рассмотрим, это pg_stat_database. Как ΠΌΡ‹ Π²ΠΈΠ΄ΠΈΠΌ, это прСдставлСниС. Π’ Π½Π΅ΠΉ ΠΎΡ‡Π΅Π½ΡŒ ΠΌΠ½ΠΎΠ³ΠΎ ΠΈΠ½Ρ„ΠΎΡ€ΠΌΠ°Ρ†ΠΈΠΈ. Бамая разнообразная информация. И ΠΎΠ½Π° Π΄Π°Π΅Ρ‚ ΠΎΡ‡Π΅Π½ΡŒ ΠΏΠΎΠ»Π΅Π·Π½ΠΎΠ΅ Π·Π½Π°Π½ΠΈΠ΅, Ρ‡Ρ‚ΠΎ Ρƒ нас происходит Π² Π±Π°Π·Π΅ Π΄Π°Π½Π½Ρ‹Ρ….

Π§Ρ‚ΠΎ ΠΌΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ ΠΏΠΎΠ»Π΅Π·Π½ΠΎΠ΅ ΠΎΡ‚Ρ‚ΡƒΠ΄Π° Π²Π·ΡΡ‚ΡŒ? НачнСм c самых простых Π²Π΅Ρ‰Π΅ΠΉ.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;

ΠŸΠ΅Ρ€Π²ΠΎΠ΅, Ρ‡Ρ‚ΠΎ ΠΌΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ – это ΠΏΡ€ΠΎΡ†Π΅Π½Ρ‚ попадания Π² кэш. ΠŸΡ€ΠΎΡ†Π΅Π½Ρ‚ попадания Π² кэш – это полСзная ΠΌΠ΅Ρ‚Ρ€ΠΈΠΊΠ°. Она позволяСт ΠΎΡ†Π΅Π½ΠΈΡ‚ΡŒ, ΠΊΠ°ΠΊΠΎΠΉ объСм Π΄Π°Π½Π½Ρ‹Ρ… бСрСтся ΠΈΠ· кэша shared buffers, Π° ΠΊΠ°ΠΊΠΎΠΉ объСм читаСтся с диска.

ΠŸΠΎΠ½ΡΡ‚Π½ΠΎΠ΅ Π΄Π΅Π»ΠΎ, Ρ‡Ρ‚ΠΎ Ρ‡Π΅ΠΌ большСС Ρƒ нас ΠΏΠΎΠΏΠ°Π΄Π°Π½ΠΈΠ΅ Π² кэш, Ρ‚ΠΎ Ρ‚Π΅ΠΌ Π»ΡƒΡ‡ΡˆΠ΅. ΠœΡ‹ ΠΎΡ†Π΅Π½ΠΈΠ²Π°Π΅ΠΌ эту ΠΌΠ΅Ρ‚Ρ€ΠΈΠΊΡƒ ΠΊΠ°ΠΊ ΠΏΡ€ΠΎΡ†Π΅Π½Ρ‚. И, Π½Π°ΠΏΡ€ΠΈΠΌΠ΅Ρ€, Ссли Ρƒ нас ΠΏΡ€ΠΎΡ†Π΅Π½Ρ‚Π½ΠΎΠ΅ ΠΎΡ‚Π½ΠΎΡˆΠ΅Π½ΠΈΠ΅ этих ΠΏΠΎΠΏΠ°Π΄Π°Π½ΠΈΠΉ Π² кэш большС 90 %, Ρ‚ΠΎ это Ρ…ΠΎΡ€ΠΎΡˆΠΎ. Если ΠΎΠ½ΠΎ опускаСтся Π½ΠΈΠΆΠ΅ 90 %, Π·Π½Π°Ρ‡ΠΈΡ‚, Ρƒ нас памяти нСдостаточно для удСрТания горячСй "Π³ΠΎΠ»ΠΎΠ²Ρ‹" Π΄Π°Π½Π½Ρ‹Ρ… Π² памяти. И Ρ‡Ρ‚ΠΎΠ±Ρ‹ эти Π΄Π°Π½Π½Ρ‹Π΅ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ, PostgreSQL Π²Ρ‹Π½ΡƒΠΆΠ΄Π΅Π½ ΠΎΠ±Ρ€Π°Ρ‰Π°Ρ‚ΡŒΡΡ ΠΊ диску ΠΈ это ΠΌΠ΅Π΄Π»Π΅Π½Π½Π΅Π΅ Ρ‡Π΅ΠΌ Ссли Π±Ρ‹ Π΄Π°Π½Π½Ρ‹Π΅ Ρ‡ΠΈΡ‚Π°Π»ΠΈΡΡŒ ΠΈΠ· памяти. И Π½ΡƒΠΆΠ½ΠΎ ΡƒΠΆΠ΅ Π΄ΡƒΠΌΠ°Ρ‚ΡŒ Π½Π°Π΄ ΡƒΠ²Π΅Π»ΠΈΡ‡Π΅Π½ΠΈΠ΅ΠΌ памяти: Π»ΠΈΠ±ΠΎ shared buffers ΡƒΠ²Π΅Π»ΠΈΡ‡ΠΈΠ²Π°Ρ‚ΡŒ, Π»ΠΈΠ±ΠΎ Π½Π°Ρ€Π°Ρ‰ΠΈΠ²Π°Ρ‚ΡŒ ΠΆΠ΅Π»Π΅Π·Π½ΡƒΡŽ ΠΏΠ°ΠΌΡΡ‚ΡŒ (RAM).

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_file, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;

Π§Ρ‚ΠΎ ΠΌΠΎΠΆΠ½ΠΎ Π΅Ρ‰Π΅ Π²Π·ΡΡ‚ΡŒ ΠΈΠ· этого прСдставлСния? МоТно ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ Π°Π½ΠΎΠΌΠ°Π»ΠΈΠΈ происходящиС Π² Π±Π°Π·Π΅. Π§Ρ‚ΠΎ здСсь ΠΏΠΎΠΊΠ°Π·Π°Π½ΠΎ? Π—Π΄Π΅ΡΡŒ Π΅ΡΡ‚ΡŒ commits, rollbacks, созданиС Π²Ρ€Π΅ΠΌΠ΅Π½Π½Ρ‹Ρ… Ρ„Π°ΠΉΠ»ΠΎΠ², ΠΈΡ… объСм, deadlocks ΠΈ ΠΊΠΎΠ½Ρ„Π»ΠΈΠΊΡ‚Ρ‹.

ΠœΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ Π²ΠΎΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒΡΡ этим запросом. Π­Ρ‚ΠΎΡ‚ SQL довольно простой. И ΠΌΠΎΠΆΠ΅ΠΌ ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ Π²ΠΎΡ‚ эти Π΄Π°Π½Π½Ρ‹Π΅ Ρƒ сСбя.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

И здСсь сразу ΠΏΠΎΡ€ΠΎΠ³ΠΎΠ²Ρ‹Π΅ значСния. ΠœΡ‹ смотрим ΡΠΎΠΎΡ‚Π½ΠΎΡˆΠ΅Π½ΠΈΠ΅ commits ΠΈ rollbacks. Commits – это ΡƒΡΠΏΠ΅ΡˆΠ½ΠΎΠ΅ ΠΏΠΎΠ΄Ρ‚Π²Π΅Ρ€ΠΆΠ΄Π΅Π½ΠΈΠ΅ Ρ‚Ρ€Π°Π½Π·Π°ΠΊΡ†ΠΈΠΈ. Rollbacks – это ΠΎΡ‚ΠΊΠ°Ρ‚, Ρ‚. Π΅. транзакция Π΄Π΅Π»Π°Π»Π° ΠΊΠ°ΠΊΡƒΡŽ-Ρ‚ΠΎ Ρ€Π°Π±ΠΎΡ‚Ρƒ, напрягала Π±Π°Π·Ρƒ, Ρ‡Ρ‚ΠΎ-Ρ‚ΠΎ считала, Π° ΠΏΠΎΡ‚ΠΎΠΌ ΠΏΡ€ΠΎΠΈΠ·ΠΎΡˆΠ΅Π» сбой, ΠΈ Ρ€Π΅Π·ΡƒΠ»ΡŒΡ‚Π°Ρ‚Ρ‹ Ρ‚Ρ€Π°Π½Π·Π°ΠΊΡ†ΠΈΠΈ ΠΎΡ‚Π±Ρ€Π°ΡΡ‹Π²Π°ΡŽΡ‚ΡΡ. Π’. Π΅. количСство rollbacks, постоянно ΡƒΠ²Π΅Π»ΠΈΡ‡ΠΈΠ²Π°ΡŽΡ‰ΠΈΡ…ΡΡ, это ΠΏΠ»ΠΎΡ…ΠΎ. И слСдуСт ΠΊΠ°ΠΊ-Ρ‚ΠΎ ΠΈΠ·Π±Π΅Π³Π°Ρ‚ΡŒ ΠΈΡ…, ΠΈ ΠΏΡ€Π°Π²ΠΈΡ‚ΡŒ ΠΊΠΎΠ΄, Ρ‡Ρ‚ΠΎΠ±Ρ‹ Ρ‚Π°ΠΊΠΎΠ³ΠΎ Π½Π΅ происходило.

ΠšΠΎΠ½Ρ„Π»ΠΈΠΊΡ‚Ρ‹ (conflicts) связаны с Ρ€Π΅ΠΏΠ»ΠΈΠΊΠ°Ρ†ΠΈΠ΅ΠΉ. И ΠΈΡ… Ρ‚ΠΎΠΆΠ΅ слСдуСт ΠΈΠ·Π±Π΅Π³Π°Ρ‚ΡŒ. Если Ρƒ вас ΠΊΠ°ΠΊΠΈΠ΅-Ρ‚ΠΎ запросы, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ Π²Ρ‹ΠΏΠΎΠ»Π½ΡΡŽΡ‚ΡΡ Π½Π° Ρ€Π΅ΠΏΠ»ΠΈΠΊΠ΅ ΠΈ Π²ΠΎΠ·Π½ΠΈΠΊΠ°ΡŽΡ‚ ΠΊΠΎΠ½Ρ„Π»ΠΈΠΊΡ‚Ρ‹, Ρ‚ΠΎ Π½ΡƒΠΆΠ½ΠΎ эти ΠΊΠΎΠ½Ρ„Π»ΠΈΠΊΡ‚Ρ‹ Ρ€Π°Π·Π±ΠΈΡ€Π°Ρ‚ΡŒ, ΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ, Ρ‡Ρ‚ΠΎ происходит. Π”Π΅Ρ‚Π°Π»ΠΈ ΠΌΠΎΠΆΠ½ΠΎ Π½Π°ΠΉΡ‚ΠΈ Π² Π»ΠΎΠ³Π°Ρ…. И ΡƒΡΡ‚Ρ€Π°Π½ΡΡ‚ΡŒ ΠΊΠΎΠ½Ρ„Π»ΠΈΠΊΡ‚Π½Ρ‹Π΅ ситуации, Ρ‡Ρ‚ΠΎΠ±Ρ‹ запросы прилоТСния Ρ€Π°Π±ΠΎΡ‚Π°Π»ΠΈ Π±Π΅Π· ошибок.

Deadlocks – это Ρ‚ΠΎΠΆΠ΅ плохая ситуация. Когда запросы Π±ΠΎΡ€ΡŽΡ‚ΡΡ Π·Π° рСсурсы, ΠΎΠ΄ΠΈΠ½ запрос обратился ΠΊ ΠΎΠ΄Π½ΠΎΠΌΡƒ рСсурсу ΠΈ взял Π±Π»ΠΎΠΊΠΈΡ€ΠΎΠ²ΠΊΡƒ, Π²Ρ‚ΠΎΡ€ΠΎΠΉ запрос обратился ΠΊΠΎ Π²Ρ‚ΠΎΡ€ΠΎΠΌΡƒ рСсурсу ΠΈ Ρ‚Π°ΠΊΠΆΠ΅ взял Π±Π»ΠΎΠΊΠΈΡ€ΠΎΠ²ΠΊΡƒ, Π° ΠΏΠΎΡ‚ΠΎΠΌ ΠΎΠ±Π° запроса ΠΎΠ±Ρ€Π°Ρ‚ΠΈΠ»ΠΈΡΡŒ ΠΊ рСсурсам Π΄Ρ€ΡƒΠ³ Π΄Ρ€ΡƒΠ³Π° ΠΈ Π·Π°Π±Π»ΠΎΠΊΠΈΡ€ΠΎΠ²Π°Π»ΠΈΡΡŒ Π² ΠΎΠΆΠΈΠ΄Π°Π½ΠΈΠΈ ΠΊΠΎΠ³Π΄Π° сосСд отпустит Π±Π»ΠΎΠΊΠΈΡ€ΠΎΠ²ΠΊΡƒ. Π­Ρ‚ΠΎ Ρ‚ΠΎΠΆΠ΅ проблСмная ситуация. Π˜Ρ… Π½ΡƒΠΆΠ½ΠΎ Ρ€Π΅ΡˆΠ°Ρ‚ΡŒ Π½Π° ΡƒΡ€ΠΎΠ²Π½Π΅ пСрСписывания ΠΏΡ€ΠΈΠ»ΠΎΠΆΠ΅Π½ΠΈΠΉ ΠΈ сСриализации доступа ΠΊ рСсурсам. И Ссли Π²Ρ‹ Π²ΠΈΠ΄ΠΈΡ‚Π΅, Ρ‡Ρ‚ΠΎ Ρƒ вас deadlocks ΡƒΠ²Π΅Π»ΠΈΡ‡ΠΈΠ²Π°ΡŽΡ‚ΡΡ постоянно, Π½ΡƒΠΆΠ½ΠΎ ΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ Π΄Π΅Ρ‚Π°Π»ΠΈ Π² Π»ΠΎΠ³Π°Ρ…, Ρ€Π°Π·Π±ΠΈΡ€Π°Ρ‚ΡŒ возникашиС ситуации ΠΈ ΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ Π² Ρ‡Π΅ΠΌ ΠΏΡ€ΠΎΠ±Π»Π΅ΠΌΠ°.

Π’Ρ€Π΅ΠΌΠ΅Π½Π½Ρ‹Π΅ Ρ„Π°ΠΉΠ»Ρ‹ (temp_files) – это Ρ‚ΠΎΠΆΠ΅ ΠΏΠ»ΠΎΡ…ΠΎ. Когда ΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚Π΅Π»ΡŒΡΠΊΠΎΠΌΡƒ запросу Π½Π΅ Ρ…Π²Π°Ρ‚Π°Π΅Ρ‚ памяти для размСщСния ΠΎΠΏΠ΅Ρ€Π°Ρ‚ΠΈΠ²Π½Ρ‹Ρ…, Π²Ρ€Π΅ΠΌΠ΅Π½Π½Ρ‹Ρ… Π΄Π°Π½Π½Ρ‹Ρ…, ΠΎΠ½ создаСт Π½Π° дискС Ρ„Π°ΠΉΠ». И всС ΠΎΠΏΠ΅Ρ€Π°Ρ†ΠΈΠΈ ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ Π±Ρ‹ ΠΎΠ½ ΠΌΠΎΠ³ Π²Ρ‹ΠΏΠΎΠ»Π½ΠΈΡ‚ΡŒ Π²ΠΎ Π²Ρ€Π΅ΠΌΠ΅Π½Π½ΠΎΠΌ Π±ΡƒΡ„Π΅Ρ€Π΅ Π² памяти, Π½Π°Ρ‡ΠΈΠ½Π°Π΅Ρ‚ Π²Ρ‹ΠΏΠΎΠ»Π½ΡΡ‚ΡŒ ΡƒΠΆΠ΅ Π½Π° дискС. Π­Ρ‚ΠΎ ΠΌΠ΅Π΄Π»Π΅Π½Π½ΠΎ. Π­Ρ‚ΠΎ ΡƒΠ²Π΅Π»ΠΈΡ‡ΠΈΠ²Π°Π΅Ρ‚ врСмя выполнСния запроса. И ΠΊΠ»ΠΈΠ΅Π½Ρ‚, ΠΎΡ‚ΠΏΡ€Π°Π²ΠΈΠ²ΡˆΠΈΠΉ запрос ΠΊ PostgreSQL ΠΏΠΎΠ»ΡƒΡ‡ΠΈΡ‚ ΠΎΡ‚Π²Π΅Ρ‚ Ρ‡ΡƒΡ‚ΡŒ ΠΏΠΎΠ·ΠΆΠ΅. Если эти всС ΠΎΠΏΠ΅Ρ€Π°Ρ†ΠΈΠΈ Π±ΡƒΠ΄ΡƒΡ‚ Π²Ρ‹ΠΏΠΎΠ»Π½ΡΡ‚ΡŒΡΡ Π² памяти, Postgres ΠΎΡ‚Π²Π΅Ρ‚ΠΈΡ‚ Π³ΠΎΡ€Π°Π·Π΄ΠΎ быстрСС ΠΈ ΠΊΠ»ΠΈΠ΅Π½Ρ‚ Π±ΡƒΠ΄Π΅Ρ‚ мСньшС ΠΆΠ΄Π°Ρ‚ΡŒ.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Pg_stat_bgwriter – это прСдставлСниС описываСт Ρ€Π°Π±ΠΎΡ‚Ρƒ Π΄Π²ΡƒΡ… Ρ„ΠΎΠ½ΠΎΠ²Ρ‹Ρ… подсистСм PostgreSQL: это checkpointer ΠΈ background writer.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Для Π½Π°Ρ‡Π°Π»Π° Ρ€Π°Π·Π±Π΅Ρ€Π΅ΠΌ ΠΊΠΎΠ½Ρ‚Ρ€ΠΎΠ»ΡŒΠ½Ρ‹Π΅ Ρ‚ΠΎΡ‡ΠΊΠΈ, Ρ‚.Π½. checkpoints. Π§Ρ‚ΠΎ Ρ‚Π°ΠΊΠΎΠ΅ ΠΊΠΎΠ½Ρ‚Ρ€ΠΎΠ»ΡŒΠ½Ρ‹Π΅ Ρ‚ΠΎΡ‡ΠΊΠΈ? ΠšΠΎΠ½Ρ‚Ρ€ΠΎΠ»ΡŒΠ½Π°Ρ Ρ‚ΠΎΡ‡ΠΊΠ° это позиция Π² ΠΆΡƒΡ€Π½Π°Π»Π΅ Ρ‚Ρ€Π°Π½Π·Π°ΠΊΡ†ΠΈΠΉ ΡΠΎΠΎΠ±Ρ‰Π°ΡŽΡ‰Π°Ρ Ρ‡Ρ‚ΠΎ всС измСнСния Π΄Π°Π½Π½Ρ‹Ρ… зафиксированныС Π² ΠΆΡƒΡ€Π½Π°Π»Π° ΡƒΡΠΏΠ΅ΡˆΠ½ΠΎ синхронизированны с Π΄Π°Π½Π½Ρ‹ΠΌΠΈ Π½Π° дискС. ΠŸΡ€ΠΎΡ†Π΅ΡΡ Π² зависимости ΠΎΡ‚ Ρ€Π°Π±ΠΎΡ‡Π΅ΠΉ Π½Π°Π³Ρ€ΡƒΠ·ΠΊΠΈ ΠΈ настроСк ΠΌΠΎΠΆΠ΅Ρ‚ Π±Ρ‹Ρ‚ΡŒ Π΄Π»ΠΈΡ‚Π΅Π»ΡŒΠ½Ρ‹ΠΌΠΈ ΠΈ ΠΏΠΎ большСй части Π·Π°ΠΊΠ»ΡŽΡ‡Π°Π΅Ρ‚ΡΡ Π² синхронизации грязных страниц Π² shared buffers с Π΄Π°Ρ‚Ρ„Π°ΠΉΠ»Π°ΠΌΠΈ Π½Π° дискС. Для Ρ‡Π΅Π³ΠΎ это Π½ΡƒΠΆΠ½ΠΎ? Если Π±Ρ‹ PostgreSQL всС врСмя обращался ΠΊ диску ΠΈ Π±Ρ€Π°Π» ΠΎΡ‚Ρ‚ΡƒΠ΄Π° Π΄Π°Π½Π½Ρ‹Π΅, ΠΈ записывал Π΄Π°Π½Π½Ρ‹Π΅ ΠΏΡ€ΠΈ ΠΊΠ°ΠΆΠ΄ΠΎΠΌ ΠΎΠ±Ρ€Π°Ρ‰Π΅Π½ΠΈΠΈ, это Π±Ρ‹Π»ΠΎ Π±Ρ‹ ΠΌΠ΅Π΄Π»Π΅Π½Π½ΠΎ. ΠŸΠΎΡΡ‚ΠΎΠΌΡƒ Ρƒ PostgreSQL Π΅ΡΡ‚ΡŒ сСгмСнт памяти, Ρ€Π°Π·ΠΌΠ΅Ρ€ ΠΊΠΎΡ‚ΠΎΡ€ΠΎΠ³ΠΎ зависит ΠΎΡ‚ ΠΏΠ°Ρ€Π°ΠΌΠ΅Ρ‚Ρ€ΠΎΠ² Π² ΠΊΠΎΠ½Ρ„ΠΈΠ³ΡƒΡ€Π°Ρ†ΠΈΠΈ. Postgres Ρ€Π°Π·ΠΌΠ΅Ρ‰Π°Π΅Ρ‚ Π² этой памяти ΠΎΠΏΠ΅Ρ€Π°Ρ‚ΠΈΠ²Π½Ρ‹Π΅ Π΄Π°Π½Π½Ρ‹Π΅ для ΠΏΠΎΡΠ»Π΅Π΄ΡƒΡŽΡ‰Π΅ΠΉ ΠΎΠ±Ρ€Π°Π±ΠΎΡ‚ΠΊΠΈ ΠΈΠ»ΠΈ Π²Ρ‹Π΄Π°Ρ‡ΠΈ ΠΏΠΎ запросам. Π’ случаС запросов Π½Π° ΠΈΠ·ΠΌΠ΅Π½Π΅Π½ΠΈΠ΅ Π΄Π°Π½Π½Ρ‹Ρ… происходит ΠΈΡ… ΠΈΠ·ΠΌΠ΅Π½Π΅Π½ΠΈΠ΅. И ΠΌΡ‹ ΠΏΠΎΠ»ΡƒΡ‡Π°Π΅ΠΌ Π΄Π²Π΅ вСрсии Π΄Π°Π½Π½Ρ‹Ρ…. Одна Ρƒ нас Π² памяти, другая Π½Π° дискС. И пСриодичСски Π½ΡƒΠΆΠ½ΠΎ эти Π΄Π°Π½Π½Ρ‹Π΅ ΡΠΈΠ½Ρ…Ρ€ΠΎΠ½ΠΈΠ·ΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ. Нам Π½ΡƒΠΆΠ½ΠΎ Ρ‚ΠΎ, Ρ‡Ρ‚ΠΎ ΠΈΠ·ΠΌΠ΅Π½Π΅Π½ΠΎ Π² памяти, ΡΠΈΠ½Ρ…Ρ€ΠΎΠ½ΠΈΠ·ΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ Π½Π° диск. Для этого Π½ΡƒΠΆΠ½Ρ‹ checkpoint.

Checkpoint ΠΏΡ€ΠΎΡ…ΠΎΠ΄ΠΈΡ‚ ΠΏΠΎ shared buffers, ΠΏΠΎΠΌΠ΅Ρ‡Π°Π΅Ρ‚ грязныС страницы, Ρ‡Ρ‚ΠΎ ΠΎΠ½ΠΈ Π½ΡƒΠΆΠ½Ρ‹ для checkpoint. ΠŸΠΎΡ‚ΠΎΠΌ запускаСт Π²Ρ‚ΠΎΡ€ΠΎΠΉ ΠΏΡ€ΠΎΡ…ΠΎΠ΄ ΠΏΠΎ shared buffers. И страницы, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ ΠΏΠΎΠΌΠ΅Ρ‡Π΅Π½Ρ‹ для checkpoint, ΠΎΠ½ ΠΈΡ… ΡƒΠΆΠ΅ синхронизируСт. Π’Π°ΠΊΠΈΠΌ ΠΎΠ±Ρ€Π°Π·ΠΎΠΌ выполняСтся синхронизация Π΄Π°Π½Π½Ρ‹Ρ… ΡƒΠΆΠ΅ с диском.

Π•ΡΡ‚ΡŒ Π΄Π²Π° Ρ‚ΠΈΠΏΠ° ΠΊΠΎΠ½Ρ‚Ρ€ΠΎΠ»ΡŒΠ½Ρ‹Ρ… Ρ‚ΠΎΡ‡Π΅ΠΊ. Один checkpoint выполняСтся ΠΏΠΎ Ρ‚Π°ΠΉΠΌ-Π°ΡƒΡ‚Ρƒ. Π­Ρ‚ΠΎ checkpoint ΠΏΠΎΠ»Π΅Π·Π½Ρ‹ΠΉ ΠΈ Ρ…ΠΎΡ€ΠΎΡˆΠΈΠΉ – checkpoint_timed. И Π΅ΡΡ‚ΡŒ checkpoints ΠΏΠΎ Ρ‚Ρ€Π΅Π±ΠΎΠ²Π°Π½ΠΈΡŽ – checkpoint required. Вакая ΠΊΠΎΠ½Ρ‚Ρ€ΠΎΠ»ΡŒΠ½Π°Ρ Ρ‚ΠΎΡ‡ΠΊΠ° происходит ΠΊΠΎΠ³Π΄Π° Ρƒ нас ΠΈΠ΄Π΅Ρ‚ ΠΎΡ‡Π΅Π½ΡŒ большая запись Π΄Π°Π½Π½Ρ‹Ρ…. ΠœΡ‹ записали ΠΎΡ‡Π΅Π½ΡŒ ΠΌΠ½ΠΎΠ³ΠΎ ΠΆΡƒΡ€Π½Π°Π»ΠΎΠ² Ρ‚Ρ€Π°Π½Π·Π°ΠΊΡ†ΠΈΠΉ. И PostgreSQL считаСт, Ρ‡Ρ‚ΠΎ Π΅ΠΌΡƒ Π½ΡƒΠΆΠ½ΠΎ всС это ΠΊΠ°ΠΊ ΠΌΠΎΠΆΠ½ΠΎ быстрСС ΡΠΈΠ½Ρ…Ρ€ΠΎΠ½ΠΈΠ·ΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ, ΡΠ΄Π΅Π»Π°Ρ‚ΡŒ ΠΊΠΎΠ½Ρ‚Ρ€ΠΎΠ»ΡŒΠ½ΡƒΡŽ Ρ‚ΠΎΡ‡ΠΊΡƒ ΠΈ ΠΆΠΈΡ‚ΡŒ дальшС.

И Ссли Π²Ρ‹ посмотрСли статистику pg_stat_bgwriter ΠΈ ΡƒΠ²ΠΈΠ΄Π΅Π»ΠΈ, Ρ‡Ρ‚ΠΎ Ρƒ вас checkpoint_req Π³ΠΎΡ€Π°Π·Π΄ΠΎ большС, Ρ‡Π΅ΠΌ checkpoint_timed, Ρ‚ΠΎ это ΠΏΠ»ΠΎΡ…ΠΎ. ΠŸΠΎΡ‡Π΅ΠΌΡƒ ΠΏΠ»ΠΎΡ…ΠΎ? Π­Ρ‚ΠΎ Π·Π½Π°Ρ‡ΠΈΡ‚, Ρ‡Ρ‚ΠΎ PostgreSQL находится Π² постоянной стрСссовой ситуации, ΠΊΠΎΠ³Π΄Π° Π΅ΠΌΡƒ Π½ΡƒΠΆΠ½ΠΎ Π·Π°ΠΏΠΈΡΡ‹Π²Π°Ρ‚ΡŒ Π΄Π°Π½Π½Ρ‹Π΅ Π½Π° диск. Checkpoint ΠΏΠΎ Ρ‚Π°ΠΉΠΌΠ°ΡƒΡ‚Ρƒ ΠΌΠ΅Π½Π΅Π΅ стрСссовый ΠΈ выполняСтся согласно Π²Π½ΡƒΡ‚Ρ€Π΅Π½Π½Π΅ΠΌΡƒ Ρ€Π°ΡΠΏΠΈΡΠ°Π½ΠΈΡŽ ΠΈ ΠΊΠ°ΠΊ Π±Ρ‹ растянут ΠΏΠΎ Π²Ρ€Π΅ΠΌΠ΅Π½ΠΈ. Π£ PostgreSQL Π΅ΡΡ‚ΡŒ Π²ΠΎΠ·ΠΌΠΎΠΆΠ½ΠΎΡΡ‚ΡŒ ΡΠ΄Π΅Π»Π°Ρ‚ΡŒ ΠΏΠ°ΡƒΠ·Ρ‹ Π² Ρ€Π°Π±ΠΎΡ‚Π΅ ΠΈ Π½Π΅ Π½Π°ΠΏΡ€ΡΠ³Π°Ρ‚ΡŒ Π΄ΠΈΡΠΊΠΎΠ²ΡƒΡŽ подсистСму. Π­Ρ‚ΠΎ для PostgreSQL ΠΏΠΎΠ»Π΅Π·Π½ΠΎ. И запросы, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ Π²Ρ‹ΠΏΠΎΠ»Π½ΡΡŽΡ‚ΡΡ Π²ΠΎ врСмя checkpoint Π½Π΅ Π±ΡƒΠ΄ΡƒΡ‚ ΠΈΡΠΏΡ‹Ρ‚Ρ‹Π²Π°Ρ‚ΡŒ стрСссы ΠΎΡ‚ Ρ‚ΠΎΠ³ΠΎ, Ρ‡Ρ‚ΠΎ дисковая подсистСма занята.

И для Ρ€Π΅Π³ΡƒΠ»ΠΈΡ€ΠΎΠ²ΠΊΠΈ checkpoint Π΅ΡΡ‚ΡŒ Ρ‚Ρ€ΠΈ ΠΏΠ°Ρ€Π°ΠΌΠ΅Ρ‚Ρ€Π°:

  • сheckpoint_segments.

  • сheckpoint_timeout.

  • сheckpoint_competion_target.

Они ΠΏΠΎΠ·Π²ΠΎΠ»ΡΡŽΡ‚ Ρ€Π΅Π³ΡƒΠ»ΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ Ρ€Π°Π±ΠΎΡ‚Ρƒ ΠΊΠΎΠ½Ρ‚Ρ€ΠΎΠ»ΡŒΠ½Ρ‹Ρ… Ρ‚ΠΎΡ‡Π΅ΠΊ. Но Π½Π΅ Π±ΡƒΠ΄Ρƒ Π½Π° Π½ΠΈΡ… Π·Π°Π΄Π΅Ρ€ΠΆΠΈΠ²Π°Ρ‚ΡŒΡΡ. Π˜Ρ… влияниС – это ΡƒΠΆΠ΅ ΠΎΡ‚Π΄Π΅Π»ΡŒΠ½Π°Ρ Ρ‚Π΅ΠΌΠ°.

Π’Π½ΠΈΠΌΠ°Π½ΠΈΠ΅: РассматриваСмая Π² Π΄ΠΎΠΊΠ»Π°Π΄Π΅ вСрсия 9.4 ΡƒΠΆΠ΅ Π½Π΅Π°ΠΊΡ‚ΡƒΠ°Π»ΡŒΠ½Π°. Π’ соврСмСнных вСрсиях PostgreSQL ΠΏΠ°Ρ€Π°ΠΌΠ΅Ρ‚Ρ€ checkpoint_segments Π·Π°ΠΌΠ΅Π½Π΅Π½ ΠΏΠ°Ρ€Π°ΠΌΠ΅Ρ‚Ρ€Π°ΠΌΠΈ min_wal_size ΠΈ max_wal_size.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Π‘Π»Π΅Π΄ΡƒΡŽΡ‰Π°Ρ подсистСма это Ρ„ΠΎΠ½ΠΎΠ²Ρ‹ΠΉ ΠΏΠΈΡΠ°Ρ‚Π΅Π»ΡŒ β€” background writer. Π§Ρ‚ΠΎ ΠΎΠ½ Π΄Π΅Π»Π°Π΅Ρ‚? Он Ρ€Π°Π±ΠΎΡ‚Π°Π΅Ρ‚ постоянно Π² бСсконСчном Ρ†ΠΈΠΊΠ»Π΅. Π‘ΠΊΠ°Π½ΠΈΡ€ΡƒΠ΅Ρ‚ страницы Π² shared buffers ΠΈ странички грязныС, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ ΠΎΠ½ нашСл, сбрасываСт Π½Π° диск. Π’Π°ΠΊΠΈΠΌ ΠΎΠ±Ρ€Π°Π·ΠΎΠΌ ΠΎΠ½ ΠΏΠΎΠΌΠΎΠ³Π°Π΅Ρ‚ checkpointer’Ρƒ Π΄Π΅Π»Π°Ρ‚ΡŒ мСньшС Ρ€Π°Π±ΠΎΡ‚Ρ‹ Π² процСссС выполнСния ΠΊΠΎΠ½Ρ‚Ρ€ΠΎΠ»ΡŒΠ½Ρ‹Ρ… Ρ‚ΠΎΡ‡Π΅ΠΊ.

Для Ρ‡Π΅Π³ΠΎ ΠΎΠ½ Π΅Ρ‰Π΅ Π½ΡƒΠΆΠ΅Π½? Он обСспСчиваСт ΠΏΠΎΡ‚Ρ€Π΅Π±Π½ΠΎΡΡ‚ΡŒ Π² чистых страницах Π² shared buffers Ссли ΠΎΠ½ΠΈ Π²Π΄Ρ€ΡƒΠ³ ΠΏΠΎΡ‚Ρ€Π΅Π±ΡƒΡŽΡ‚ΡΡ (Π² большом количСствС ΠΈ сразу) для размСщСния Π΄Π°Π½Π½Ρ‹Ρ…. ΠŸΡ€Π΅Π΄ΠΏΠΎΠ»ΠΎΠΆΠΈΠΌ Π²ΠΎΠ·Π½ΠΈΠΊΠ»Π° ситуация ΠΊΠΎΠ³Π΄Π° для выполнСния запроса ΠΏΠΎΡ‚Ρ€Π΅Π±ΠΎΠ²Π°Π»ΠΈΡΡŒ чистыС страницы ΠΈ ΠΎΠ½ΠΈ ΡƒΠΆΠ΅ Π΅ΡΡ‚ΡŒ Π² shared buffers. ΠŸΠΎΡΡ‚Π³Ρ€Π΅ΡΠΎΠ²Ρ‹ΠΉ backend просто Π±Π΅Ρ€Π΅Ρ‚ ΠΈΡ… ΠΈ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅Ρ‚, Π΅ΠΌΡƒ Π½Π΅ Π½Π°Π΄ΠΎ самому Π½ΠΈΡ‡Π΅Π³ΠΎ Ρ‡ΠΈΡΡ‚ΠΈΡ‚ΡŒ. Но Ссли Π²Π΄Ρ€ΡƒΠ³ Ρ‚Π°ΠΊΠΈΡ… страниц Π½Π΅Ρ‚, бэкСнд приостанавливаСт Ρ€Π°Π±ΠΎΡ‚Ρƒ ΠΈ Π½Π°Ρ‡ΠΈΠ½Π°Π΅Ρ‚ поиск страниц Ρ‡Ρ‚ΠΎΠ±Ρ‹ ΡΠ±Ρ€ΠΎΡΠΈΡ‚ΡŒ ΠΈΡ… Π½Π° диск ΠΈ Π²Π·ΡΡ‚ΡŒ для своих Π½ΡƒΠΆΠ΄ β€” Ρ‡Ρ‚ΠΎ Π½Π΅Π³Π°Ρ‚ΠΈΠ²Π½ΠΎ сказываСтся Π½Π° Π²Ρ€Π΅ΠΌΠ΅Π½ΠΈ Π²Ρ‹ΠΏΠΎΠ»Π½ΡΡŽΡ‰Π΅Π³ΠΎΡΡ Π² Π΄Π°Π½Π½Ρ‹ΠΉ ΠΌΠΎΠΌΠ΅Π½Ρ‚ запроса. Если Π²Ρ‹ Π²ΠΈΠ΄ΠΈΡ‚Π΅, Ρ‡Ρ‚ΠΎ Ρƒ вас ΠΏΠ°Ρ€Π°ΠΌΠ΅Ρ‚Ρ€ maxwritten_clean большой, это Π·Π½Π°Ρ‡ΠΈΡ‚, Ρ‡Ρ‚ΠΎ background writer Π½Π΅ справляСтся со своСй Ρ€Π°Π±ΠΎΡ‚ΠΎΠΉ ΠΈ Π½ΡƒΠΆΠ½ΠΎ ΡƒΠ²Π΅Π»ΠΈΡ‡ΠΈΠ²Π°Ρ‚ΡŒ ΠΏΠ°Ρ€Π°ΠΌΠ΅Ρ‚Ρ€Ρ‹ bgwriter_lru_maxpages, Ρ‡Ρ‚ΠΎΠ±Ρ‹ ΠΎΠ½ смог Π·Π° ΠΎΠ΄ΠΈΠ½ Ρ†ΠΈΠΊΠ» ΡΠ΄Π΅Π»Π°Ρ‚ΡŒ большС Ρ€Π°Π±ΠΎΡ‚Ρ‹, большС ΠΎΡ‡ΠΈΡΡ‚ΠΈΡ‚ΡŒ страничСк.

И Π΄Ρ€ΡƒΠ³ΠΎΠΉ ΠΎΡ‡Π΅Π½ΡŒ ΠΏΠΎΠ»Π΅Π·Π½Ρ‹ΠΉ ΠΏΠΎΠΊΠ°Π·Π°Ρ‚Π΅Π»ΡŒ – это buffers_backend_fsync. БэкСнды Π½Π΅ Π΄Π΅Π»Π°ΡŽΡ‚ fsync, ΠΏΠΎΡ‚ΠΎΠΌΡƒ Ρ‡Ρ‚ΠΎ это ΠΌΠ΅Π΄Π»Π΅Π½Π½ΠΎ. Они ΠΏΠ΅Ρ€Π΅Π΄Π°ΡŽΡ‚ fsync Π²Ρ‹ΡˆΠ΅ ΠΏΠΎ IO stack checkpointer’у. Π£ checkpointer Π΅ΡΡ‚ΡŒ своя ΠΎΡ‡Π΅Ρ€Π΅Π΄ΡŒ, ΠΎΠ½ пСриодичСски fsync ΠΎΠ±Ρ€Π°Π±Π°Ρ‚Ρ‹Π²Π°Π΅Ρ‚ ΠΈ страницы Π² памяти синхронизируСт с Ρ„Π°ΠΉΠ»Π°ΠΌΠΈ Π½Π° дискС. Если ΠΎΡ‡Π΅Ρ€Π΅Π΄ΡŒ большая Ρƒ checkpointer ΠΈ Π·Π°ΠΏΠΎΠ»Π½Π΅Π½Π°, Ρ‚ΠΎ бэкСнд Π²Ρ‹Π½ΡƒΠΆΠ΄Π΅Π½ сам Π΄Π΅Π»Π°Ρ‚ΡŒ fsync ΠΈ это замСдляСт Ρ€Π°Π±ΠΎΡ‚Ρƒ бэкСнда, Ρ‚. Π΅. ΠΊΠ»ΠΈΠ΅Π½Ρ‚ ΠΏΠΎΠ»ΡƒΡ‡ΠΈΡ‚ ΠΎΡ‚Π²Π΅Ρ‚ ΠΏΠΎΠ·ΠΆΠ΅, Ρ‡Π΅ΠΌ ΠΌΠΎΠ³ Π±Ρ‹. Если Π²Ρ‹ Π²ΠΈΠ΄ΠΈΡ‚Π΅, Ρ‡Ρ‚ΠΎ Ρƒ вас это Π·Π½Π°Ρ‡Π΅Π½ΠΈΠ΅ большС нуля, Ρ‚ΠΎ это ΡƒΠΆΠ΅ ΠΏΡ€ΠΎΠ±Π»Π΅ΠΌΠ° ΠΈ Π½ΡƒΠΆΠ½ΠΎ ΠΎΠ±Ρ€Π°Ρ‚ΠΈΡ‚ΡŒ Π²Π½ΠΈΠΌΠ°Π½ΠΈΠ΅ Π½Π° настройки background writer’Π° ΠΈ Ρ‚Π°ΠΊΠΆΠ΅ ΠΎΡ†Π΅Π½ΠΈΡ‚ΡŒ ΠΏΡ€ΠΎΠΈΠ·Π²ΠΎΠ΄ΠΈΡ‚Π΅Π»ΡŒΠ½ΠΎΡΡ‚ΡŒ дисковой подсистСмы.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Π’Π½ΠΈΠΌΠ°Π½ΠΈΠ΅: _Π‘Π»Π΅Π΄ΡƒΡŽΡ‰ΠΈΠΉ тСкст описываСт статистичСскиС прСдставлСния связанныС с Ρ€Π΅ΠΏΠ»ΠΈΠΊΠ°Ρ†ΠΈΠ΅ΠΉ. Π‘ΠΎΠ»ΡŒΡˆΠ°Ρ Ρ‡Π°ΡΡ‚ΡŒ ΠΈΠΌΠ΅Π½ прСдставлСний ΠΈ Ρ„ΡƒΠ½ΠΊΡ†ΠΈΠΉ Π±Ρ‹Π»Π° ΠΏΠ΅Ρ€Π΅ΠΈΠΌΠ΅Π½ΠΎΠ²Π°Π½Π° Π² Postgres 10. Π‘ΡƒΡ‚ΡŒ ΠΏΠ΅Ρ€Π΅ΠΈΠΌΠ΅Π½ΠΎΠ²Π°Π½ΠΈΠΉ сводилась ΠΊ Π·Π°ΠΌΠ΅Π½Π΅ xlog Π½Π° wal ΠΈ location Π½Π° lsn Π² ΠΈΠΌΠ΅Π½Π°Ρ… Ρ„ΡƒΠ½ΠΊΡ†ΠΈΠΉ/прСдставлСний ΠΈ Ρ‚.ΠΏ. Частный ΠΏΡ€ΠΈΠΌΠ΅Ρ€, функция pg_xlog_location_diff() Π±Ρ‹Π»Π° ΠΏΠ΅Ρ€Π΅ΠΈΠΌΠ΅Π½ΠΎΠ²Π°Π½Π° Π² pg_wal_lsn_diff()._

Π’ΡƒΡ‚ Ρ‚ΠΎΠΆΠ΅ Ρƒ нас ΠΌΠ½ΠΎΠ³ΠΎ всСго. Но понадобятся Π½Π°ΠΌ всСго лишь ΠΏΡƒΠ½ΠΊΡ‚Ρ‹, связанныС с location.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Если ΠΌΡ‹ Π²ΠΈΠ΄ΠΈΠΌ, Ρ‡Ρ‚ΠΎ всС значСния Ρ€Π°Π²Π½Ρ‹, Ρ‚ΠΎ это ΠΈΠ΄Π΅Π°Π»ΡŒΠ½Ρ‹ΠΉ Π²Π°Ρ€ΠΈΠ°Π½Ρ‚ ΠΈ Ρ€Π΅ΠΏΠ»ΠΈΠΊΠ° Π½Π΅ отстаСт ΠΎΡ‚ мастСра.

Π’ΠΎΡ‚ эта ΡˆΠ΅ΡΡ‚Π½Π°Π΄Ρ†Π°Ρ‚Π΅Ρ€ΠΈΡ‡Π½Π°Ρ позиция – это позиция Π² ΠΆΡƒΡ€Π½Π°Π»Π΅ Ρ‚Ρ€Π°Π½Π·Π°ΠΊΡ†ΠΈΠΉ. Она постоянно увСличиваСтся, Ссли Π² Π±Π°Π·Π΅ Π΅ΡΡ‚ΡŒ какая-Ρ‚ΠΎ Π°ΠΊΡ‚ΠΈΠ²Π½ΠΎΡΡ‚ΡŒ: inserts, deletes ΠΈ Ρ‚. Π΄.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

сколько записано xlog Π² Π±Π°ΠΉΡ‚Π°Ρ…
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
Π»Π°Π³ Ρ€Π΅ΠΏΠ»ΠΈΠΊΠ°Ρ†ΠΈΠΈ Π² Π±Π°ΠΉΡ‚Π°Ρ…
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
Π»Π°Π³ Ρ€Π΅ΠΏΠ»ΠΈΠΊΠ°Ρ†ΠΈΠΈ Π² сСкундах
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());

Если эти Π²Π΅Ρ‰ΠΈ ΠΎΡ‚Π»ΠΈΡ‡Π°ΡŽΡ‚ΡΡ, Π·Π½Π°Ρ‡ΠΈΡ‚ Π΅ΡΡ‚ΡŒ ΠΊΠ°ΠΊΠΎΠΉ-Ρ‚ΠΎ Π»Π°Π³. Π›Π°Π³ – это отставаниС Ρ€Π΅ΠΏΠ»ΠΈΠΊΠΈ ΠΎΡ‚ мастСра, Ρ‚. Π΅. Π΄Π°Π½Π½Ρ‹Π΅ ΠΎΡ‚Π»ΠΈΡ‡Π°ΡŽΡ‚ΡΡ ΠΌΠ΅ΠΆΠ΄Ρƒ сСрвСрами.

Π•ΡΡ‚ΡŒ Ρ‚Ρ€ΠΈ ΠΏΡ€ΠΈΡ‡ΠΈΠ½Ρ‹ отставания:

  • Π­Ρ‚ΠΎ дисковая подсистСма Π½Π΅ справляСтся с записью синхронизации Ρ„Π°ΠΉΠ»ΠΎΠ².
  • Π­Ρ‚ΠΎ Π²ΠΎΠ·ΠΌΠΎΠΆΠ½Ρ‹Π΅ ошибки сСти, Π»ΠΈΠ±ΠΎ ΠΏΠ΅Ρ€Π΅Π³Ρ€ΡƒΠ·ΠΊΠ° сСти, ΠΊΠΎΠ³Π΄Π° Π΄Π°Π½Π½Ρ‹Π΅ Π½Π΅ ΡƒΡΠΏΠ΅Π²Π°ΡŽΡ‚ Π΄ΠΎΠ΅Π·ΠΆΠ°Ρ‚ΡŒ Π΄ΠΎ Ρ€Π΅ΠΏΠ»ΠΈΠΊΠΈ ΠΈ ΠΎΠ½ Π½Π΅ ΠΌΠΎΠΆΠ΅Ρ‚ ΠΈΡ… воспроизвСсти.
  • И процСссор. ΠŸΡ€ΠΎΡ†Π΅ΡΡΠΎΡ€ – это ΠΎΡ‡Π΅Π½ΡŒ Ρ€Π΅Π΄ΠΊΠΈΠΉ случай. И я Π²ΠΈΠ΄Π΅Π» Ρ‚Π°ΠΊΠΎΠ΅ Π΄Π²Π° ΠΈΠ»ΠΈ Ρ‚Ρ€ΠΈ Ρ€Π°Π·Π°, Π½ΠΎ Ρ‚Π°ΠΊΠΎΠ΅ Ρ‚ΠΎΠΆΠ΅ ΠΌΠΎΠΆΠ΅Ρ‚ Π±Ρ‹Ρ‚ΡŒ.

И Π²ΠΎΡ‚ Ρ‚Ρ€ΠΈ запроса, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ Π½Π°ΠΌ ΠΏΠΎΠ·Π²ΠΎΠ»ΡΡŽΡ‚ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ статистику. ΠœΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ ΠΎΡ†Π΅Π½ΠΈΡ‚ΡŒ, сколько записано Ρƒ нас Π² ΠΆΡƒΡ€Π½Π°Π»Π΅ Ρ‚Ρ€Π°Π½Π·Π°ΠΊΡ†ΠΈΠΈ. Π•ΡΡ‚ΡŒ такая функция pg_xlog_location_diff ΠΈ ΠΌΠΎΠΆΠ΅ΠΌ ΠΎΡ†Π΅Π½ΠΈΡ‚ΡŒ Π»Π°Π³ Ρ€Π΅ΠΏΠ»ΠΈΠΊΠ°Ρ†ΠΈΠΈ Π² Π±Π°ΠΉΡ‚Π°Ρ… ΠΈ сСкундах. ΠœΡ‹ Ρ‚ΠΎΠΆΠ΅ для этого ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅ΠΌ Π·Π½Π°Ρ‡Π΅Π½ΠΈΠ΅ ΠΈΠ· этого прСдставлСния (VIEWs).

ΠŸΡ€ΠΈΠΌΠ΅Ρ‡Π°Π½ΠΈΠ΅: _ВмСсто pg_xlog_locationdiff() Ρ„ΡƒΠ½ΠΊΡ†ΠΈΠΈ ΠΌΠΎΠΆΠ½ΠΎ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ ΠΎΠΏΠ΅Ρ€Π°Ρ‚ΠΎΡ€ вычитания ΠΈ Π²Ρ‹Ρ‡ΠΈΡ‚Π°Ρ‚ΡŒ ΠΎΠ΄ΠΈΠ½ location ΠΈΠ· Π΄Ρ€ΡƒΠ³ΠΎΠ³ΠΎ. Π£Π΄ΠΎΠ±Π½ΠΎ.

Π‘ Π»Π°Π³ΠΎΠΌ, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹ΠΉ Π² сСкундах, Π΅ΡΡ‚ΡŒ ΠΎΠ΄ΠΈΠ½ ΠΌΠΎΠΌΠ΅Π½Ρ‚. Если Π½Π° мастСрС Π½Π΅ происходит Π½ΠΈΠΊΠ°ΠΊΠΎΠΉ активности, транзакция Ρ‚Π°ΠΌ Π±Ρ‹Π»Π° Π³Π΄Π΅-Ρ‚ΠΎ 15 ΠΌΠΈΠ½ΡƒΡ‚ Π½Π°Π·Π°Π΄ ΠΈ активности Π½ΠΈΠΊΠ°ΠΊΠΎΠΉ Π½Π΅Ρ‚, ΠΈ Ссли ΠΌΡ‹ Π½Π° Ρ€Π΅ΠΏΠ»ΠΈΠΊΠ΅ посмотрим этот Π»Π°Π³, Ρ‚ΠΎ ΠΌΡ‹ ΡƒΠ²ΠΈΠ΄ΠΈΠΌ Π»Π°Π³ Π² 15 ΠΌΠΈΠ½ΡƒΡ‚. Об этом стоит ΠΏΠΎΠΌΠ½ΠΈΡ‚ΡŒ. И это ΠΌΠΎΠΆΠ΅Ρ‚ Π²Π²ΠΎΠ΄ΠΈΡ‚ΡŒ Π² ступор, ΠΊΠΎΠ³Π΄Π° Π²Ρ‹ посмотрСли этот Π»Π°Π³.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Pg_stat_all_tables – Π΅Ρ‰Π΅ ΠΎΠ΄Π½ΠΎ ΠΏΠΎΠ»Π΅Π·Π½ΠΎΠ΅ прСдставлСниС. Оно ΠΏΠΎΠΊΠ°Π·Ρ‹Π²Π°Π΅Ρ‚ статистику ΠΏΠΎ Ρ‚Π°Π±Π»ΠΈΡ†Π°ΠΌ. Когда Ρƒ нас Π² Π±Π°Π·Π΅ Π΅ΡΡ‚ΡŒ Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹, с Π½ΠΈΠΌ Π΅ΡΡ‚ΡŒ какая-Ρ‚ΠΎ Π°ΠΊΡ‚ΠΈΠ²Π½ΠΎΡΡ‚ΡŒ, ΠΊΠ°ΠΊΠΈΠ΅-Ρ‚ΠΎ дСйствия, ΠΌΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ эту ΠΈΠ½Ρ„ΠΎΡ€ΠΌΠ°Ρ†ΠΈΡŽ ΠΏΠΎΠ»ΡƒΡ‡ΠΈΡ‚ΡŒ ΠΈΠ· этого прСдставлСния.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

select
relname,
pg_size_pretty(pg_relation_size(relname::regclass)) as size,
seq_scan, seq_tup_read,
seq_scan / seq_tup_read as seq_tup_avg
from pg_stat_user_tables
where seq_tup_read > 0 order by 3,4 desc limit 5;

ΠŸΠ΅Ρ€Π²ΠΎΠ΅, Ρ‡Ρ‚ΠΎ ΠΌΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ, это ΠΏΠΎΡΠ»Π΅Π΄ΠΎΠ²Π°Ρ‚Π΅Π»ΡŒΠ½Ρ‹Π΅ сканирования ΠΏΠΎ Ρ‚Π°Π±Π»ΠΈΡ†Π΅. Π‘Π°ΠΌΠΎ число послС этих ΠΏΡ€ΠΎΡ…ΠΎΠ΄ΠΎΠ² Π΅Ρ‰Π΅ Π½Π΅ ΠΎΠ±ΡΠ·Π°Ρ‚Π΅Π»ΡŒΠ½ΠΎ ΠΏΠ»ΠΎΡ…ΠΎ ΠΈ Π½Π΅ ΠΏΠΎΠΊΠ°Π·Π°Ρ‚Π΅Π»ΡŒ Ρ‚ΠΎΠ³ΠΎ, Ρ‡Ρ‚ΠΎ Π½Π°ΠΌ Π½ΡƒΠΆΠ½ΠΎ ΡƒΠΆΠ΅ Ρ‡Ρ‚ΠΎ-Ρ‚ΠΎ ΠΏΡ€Π΅Π΄ΠΏΡ€ΠΈΠ½ΠΈΠΌΠ°Ρ‚ΡŒ.

Однако Π΅ΡΡ‚ΡŒ вторая ΠΌΠ΅Ρ‚Ρ€ΠΈΠΊΠ° – seq_tup_read. Π­Ρ‚ΠΎ количСство строк, Π²ΠΎΠ·Π²Ρ€Π°Ρ‰Π΅Π½Π½Ρ‹Ρ… Π² Ρ€Π΅Π·ΡƒΠ»ΡŒΡ‚Π°Ρ‚Π΅ ΠΏΠΎΡΠ»Π΅Π΄ΠΎΠ²Π°Ρ‚Π΅Π»ΡŒΠ½ΠΎΠ³ΠΎ сканирования. Если усрСднСнноС число ΠΏΡ€Π΅Π²Ρ‹ΡˆΠ°Π΅Ρ‚ 1 000, 10 000, 50 000, 100 000, Ρ‚ΠΎ это ΡƒΠΆΠ΅ ΠΏΠΎΠΊΠ°Π·Π°Ρ‚Π΅Π»ΡŒ, Ρ‡Ρ‚ΠΎ Π²ΠΎΠ·ΠΌΠΎΠΆΠ½ΠΎ Π²Π°ΠΌ Π½ΡƒΠΆΠ½ΠΎ Π³Π΄Π΅-Ρ‚ΠΎ ΠΏΠΎΡΡ‚Ρ€ΠΎΠΈΡ‚ΡŒ индСкс, Ρ‡Ρ‚ΠΎΠ±Ρ‹ обращСния Π±Ρ‹Π»ΠΈ ΠΏΠΎ индСксу, Π»ΠΈΠ±ΠΎ Π²ΠΎΠ·ΠΌΠΎΠΆΠ½ΠΎ ΠΎΠΏΡ‚ΠΈΠΌΠΈΠ·ΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ запросы ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΡŽ Ρ‚Π°ΠΊΠΈΠ΅ ΠΏΠΎΡΠ»Π΅Π΄ΠΎΠ²Π°Ρ‚Π΅Π»ΡŒΠ½Ρ‹Π΅ сканирования, Ρ‡Ρ‚ΠΎΠ±Ρ‹ Ρ‚Π°ΠΊΠΎΠ³ΠΎ Π½Π΅ Π±Ρ‹Π»ΠΎ.

ΠŸΡ€ΠΎΡΡ‚ΠΎΠΉ ΠΏΡ€ΠΈΠΌΠ΅Ρ€ – допустим, запрос с большим OFFSET ΠΈ LIMIT стоит. К ΠΏΡ€ΠΈΠΌΠ΅Ρ€Ρƒ сканируСтся 100 000 строк Π² Ρ‚Π°Π±Π»ΠΈΡ†Π΅ ΠΈ послС этого бСрСтся 50 000 Π½ΡƒΠΆΠ½Ρ‹Ρ… строк, Π° ΠΏΡ€Π΅Π΄Ρ‹Π΄Ρ‰ΠΈΠ΅ отсканированныС строки ΠΎΡ‚Π±Ρ€Π°ΡΡ‹Π²Π°ΡŽΡ‚ΡΡ. Π­Ρ‚ΠΎ Ρ‚ΠΎΠΆΠ΅ ΠΏΠ»ΠΎΡ…ΠΎΠΉ кСйс. И Ρ‚Π°ΠΊΠΈΠ΅ запросы Π½ΡƒΠΆΠ½ΠΎ ΠΎΠΏΡ‚ΠΈΠΌΠΈΠ·ΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ. И здСсь Π²ΠΎΡ‚ Ρ‚Π°ΠΊΠΎΠΉ простой SQL-запрос, Π½Π° ΠΊΠΎΡ‚ΠΎΡ€ΠΎΠΌ ΠΌΠΎΠΆΠ½ΠΎ это ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ ΠΈ ΠΎΡ†Π΅Π½ΠΈΡ‚ΡŒ ΠΏΠΎΠ»ΡƒΡ‡Π΅Π½Π½Ρ‹Π΅ Ρ†ΠΈΡ„Ρ€Ρ‹.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

select
relname,
pg_size_pretty(pg_total_relation_size(relname::regclass)) as
full_size,
pg_size_pretty(pg_relation_size(relname::regclass)) as
table_size,
pg_size_pretty(pg_total_relation_size(relname::regclass) -
pg_relation_size(relname::regclass)) as index_size
from pg_stat_user_tables
order by pg_total_relation_size(relname::regclass) desc limit 10;

Π Π°Π·ΠΌΠ΅Ρ€Ρ‹ Ρ‚Π°Π±Π»ΠΈΡ† Ρ‚Π°ΠΊΠΆΠ΅ ΠΌΠΎΠΆΠ½ΠΎ ΠΏΠΎΠ»ΡƒΡ‡ΠΈΡ‚ΡŒ с ΠΏΠΎΠΌΠΎΡ‰ΡŒΡŽ этой Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹ ΠΈ с ΠΏΠΎΠΌΠΎΡ‰ΡŒΡŽ Π΄ΠΎΠΏΠΎΠ»Π½ΠΈΡ‚Π΅Π»ΡŒΠ½Ρ‹Ρ… Ρ„ΡƒΠ½ΠΊΡ†ΠΈΠΉ pg_total_relation_size(), pg_relation_size().

Π’ΠΎΠΎΠ±Ρ‰Π΅, Π΅ΡΡ‚ΡŒ ΠΌΠ΅Ρ‚Π°ΠΊΠΎΠΌΠ°Π½Π΄Ρ‹ dt ΠΈ di, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ ΠΌΠΎΠΆΠ½ΠΎ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ Π² PSQL ΠΈ Ρ‚Π°ΠΊΠΆΠ΅ ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ Ρ€Π°Π·ΠΌΠ΅Ρ€Ρ‹ Ρ‚Π°Π±Π»ΠΈΡ† ΠΈ индСксов.

Однако использованиС Ρ„ΡƒΠ½ΠΊΡ†ΠΈΠΉ ΠΏΠΎΠΌΠΎΠ³Π°Π΅Ρ‚ Π½Π°ΠΌ ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ Ρ€Π°Π·ΠΌΠ΅Ρ€Ρ‹ Ρ‚Π°Π±Π»ΠΈΡ† Π΅Ρ‰Π΅ с ΡƒΡ‡Π΅Ρ‚ΠΎΠΌ индСксов, Π»ΠΈΠ±ΠΎ Π±Π΅Π· ΡƒΡ‡Π΅Ρ‚ΠΎΠ² индСксов ΠΈ ΡƒΠΆΠ΅ Π΄Π΅Π»Π°Ρ‚ΡŒ ΠΊΠ°ΠΊΠΈΠ΅-Ρ‚ΠΎ ΠΎΡ†Π΅Π½ΠΊΠΈ Π½Π° основС роста Π±Π°Π·Ρ‹ Π΄Π°Π½Π½Ρ‹Ρ…, Ρ‚. Π΅. ΠΊΠ°ΠΊ ΠΎΠ½Π° Ρƒ нас растСт, с ΠΊΠ°ΠΊΠΎΠΉ ΠΈΠ½Ρ‚Π΅Π½ΡΠΈΠ²Π½ΠΎΡΡ‚ΡŒΡŽ ΠΈ Π΄Π΅Π»Π°Ρ‚ΡŒ ΡƒΠΆΠ΅ ΠΊΠ°ΠΊΠΈΠ΅-Ρ‚ΠΎ Π²Ρ‹Π²ΠΎΠ΄Ρ‹ ΠΎΠ± ΠΎΠΏΡ‚ΠΈΠΌΠΈΠ·Π°Ρ†ΠΈΠΈ Ρ€Π°Π·ΠΌΠ΅Ρ€ΠΎΠ².

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

ΠΠΊΡ‚ΠΈΠ²Π½ΠΎΡΡ‚ΡŒ Π½Π° запись. Π§Ρ‚ΠΎ Ρ‚Π°ΠΊΠΎΠ΅ запись? Π”Π°Π²Π°ΠΉΡ‚Π΅ рассмотрим ΠΎΠΏΠ΅Ρ€Π°Ρ†ΠΈΡŽ UPDATE – ΠΎΠΏΠ΅Ρ€Π°Ρ†ΠΈΡŽ обновлСния строк Π² Ρ‚Π°Π±Π»ΠΈΡ†Π΅. По сути, update – это Π΄Π²Π΅ ΠΎΠΏΠ΅Ρ€Π°Ρ†ΠΈΠΈ (Π° Ρ‚ΠΎ ΠΈ Π΅Ρ‰Ρ‘ большС). Π­Ρ‚ΠΎ вставка Π½ΠΎΠ²ΠΎΠΉ вСрсии строки ΠΈ ΠΏΠΎΠΌΠ΅Ρ‚ΠΊΠ° старой вСрсии строки ΠΊΠ°ΠΊ ΡƒΡΡ‚Π°Ρ€Π΅Π²ΡˆΠ΅ΠΉ. Π’ послСдствии ΠΏΡ€ΠΈΠ΄Π΅Ρ‚ Π°Π²Ρ‚ΠΎΠ²Π°ΠΊΡƒΡƒΠΌ ΠΈ Π²ΠΎΡ‚ эти ΡƒΡΡ‚Π°Ρ€Π΅Π²ΡˆΠΈΠ΅ вСрсии строк вычистит, ΠΏΠΎΠΌΠ΅Ρ‚ΠΈΡ‚ это мСсто ΠΊΠ°ΠΊ доступноС для ΠΏΠΎΠ²Ρ‚ΠΎΡ€Π½ΠΎΠ³ΠΎ использования.

ΠšΡ€ΠΎΠΌΠ΅ Ρ‚ΠΎΠ³ΠΎ, update – это Π½Π΅ Ρ‚ΠΎΠ»ΡŒΠΊΠΎ ΠΎΠ±Π½ΠΎΠ²Π»Π΅Π½ΠΈΠ΅ Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹. Π­Ρ‚ΠΎ Π΅Ρ‰Π΅ ΠΎΠ±Π½ΠΎΠ²Π»Π΅Π½ΠΈΠ΅ индСксов. Если Ρƒ вас Π½Π° Ρ‚Π°Π±Π»ΠΈΡ†Π΅ ΠΌΠ½ΠΎΠ³ΠΎ индСксов, Ρ‚ΠΎ ΠΏΡ€ΠΈ update всС индСксы, Π² ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Ρ… ΡƒΡ‡Π°ΡΡ‚Π²ΡƒΡŽΡ‚ поля, обновляСмыС Π² запросС, Π½ΡƒΠΆΠ½ΠΎ Π±ΡƒΠ΄Π΅Ρ‚ Ρ‚Π°ΠΊΠΆΠ΅ ΠΎΠ±Π½ΠΎΠ²ΠΈΡ‚ΡŒ. Π’ этих индСксах Ρ‚Π°ΠΊΠΆΠ΅ Π±ΡƒΠ΄ΡƒΡ‚ ΡƒΡΡ‚Π°Ρ€Π΅Π²ΡˆΠΈΠ΅ вСрсии строк, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ Π½ΡƒΠΆΠ½ΠΎ Π±ΡƒΠ΄Π΅Ρ‚ ΠΏΠΎΡ‡ΠΈΡΡ‚ΠΈΡ‚ΡŒ.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

select
s.relname,
pg_size_pretty(pg_relation_size(relid)),
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\d+)') as
r(v) limit 1) AS fillfactor
from pg_stat_all_tables s
join pg_class c ON c.oid=relid
order by total_writes desc limit 50;

И Π·Π° счСт свСго Π΄ΠΈΠ·Π°ΠΉΠ½Π°, UPDATE – это тяТСловСсныС ΠΎΠΏΠ΅Ρ€Π°Ρ†ΠΈΠΈ. Но ΠΈΡ… ΠΌΠΎΠΆΠ½ΠΎ ΠΎΠ±Π»Π΅Π³Ρ‡ΠΈΡ‚ΡŒ. Π•ΡΡ‚ΡŒ hot updates. Они появились Π² PostgreSQL вСрсии 8.3. И Ρ‡Ρ‚ΠΎ это Ρ‚Π°ΠΊΠΎΠ΅? Π­Ρ‚ΠΎ лСгковСсный update, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹ΠΉ Π½Π΅ Π²Ρ‹Π·Ρ‹Π²Π°Π΅Ρ‚ пСрСстроСниС индСксов. Π’. Π΅. ΠΌΡ‹ ΠΎΠ±Π½ΠΎΠ²ΠΈΠ»ΠΈ запись, Π½ΠΎ ΠΏΡ€ΠΈ этом обновилась Ρ‚ΠΎΠ»ΡŒΠΊΠΎ запись Π² страничкС (которая ΠΏΡ€ΠΈΠ½Π°Π΄Π»Π΅ΠΆΠΈΡ‚ Ρ‚Π°Π±Π»ΠΈΡ†Π΅), Π° индСксы ΠΏΠΎ-ΠΏΡ€Π΅ΠΆΠ½Π΅ΠΌΡƒ ΡƒΠΊΠ°Π·Ρ‹Π²Π°ΡŽΡ‚ Π½Π° Ρ‚Ρƒ ΠΆΠ΅ ΡΠ°ΠΌΡƒΡŽ запись Π² страницС. Π’Π°ΠΌ Π½Π΅ΠΌΠ½ΠΎΠ³ΠΎ такая интСрСсная Π»ΠΎΠ³ΠΈΠΊΠ° Ρ€Π°Π±ΠΎΡ‚Ρ‹, ΠΊΠΎΠ³Π΄Π° ΠΏΡ€ΠΈΡ…ΠΎΠ΄ΠΈΡ‚ Π²Π°ΠΊΡƒΡƒΠΌ, Ρ‚ΠΎ ΠΎΠ½ эти Ρ†Π΅ΠΏΠΎΡ‡ΠΊΠΈ hot пСрСстраиваСт ΠΈ всС ΠΏΡ€ΠΎΠ΄ΠΎΠ»ΠΆΠ°Π΅Ρ‚ Ρ€Π°Π±ΠΎΡ‚Π°Ρ‚ΡŒ Π±Π΅Π· обновлСния индСксов, ΠΈ происходит всС с мСньшСй Ρ‚Ρ€Π°Ρ‚ΠΎΠΉ рСсурсов.

И ΠΊΠΎΠ³Π΄Π° Ρƒ вас n_tup_hot_upd большоС, Ρ‚ΠΎ это ΠΎΡ‡Π΅Π½ΡŒ Ρ…ΠΎΡ€ΠΎΡˆΠΎ. Π­Ρ‚ΠΎ Π·Π½Π°Ρ‡ΠΈΡ‚, Ρ‡Ρ‚ΠΎ лСгковСсныС updates ΠΏΡ€Π΅ΠΎΠ±Π»Π°Π΄Π°ΡŽΡ‚ ΠΈ это ΠΏΠΎ рСсурсам Π²Ρ‹Ρ…ΠΎΠ΄ΠΈΡ‚ Π½Π°ΠΌ дСшСвлС ΠΈ всС прСкрасно.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

ALTER TABLE table_name SET (fillfactor = 70);

Как ΡƒΠ²Π΅Π»ΠΈΡ‡ΠΈΡ‚ΡŒ объСм hot updateΠΎΠ²? ΠœΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ fillfactor. Он опрСдСляСт Ρ€Π°Π·ΠΌΠ΅Ρ€ Ρ€Π΅Π·Π΅Ρ€Π²ΠΈΡ€ΡƒΠ΅ΠΌΠΎΠ³ΠΎ свободного мСстС ΠΏΡ€ΠΈ Π·Π°ΠΏΠΎΠ»Π½Π΅Π½ΠΈΠΈ страницы Π² Ρ‚Π°Π±Π»ΠΈΡ†Π΅ с ΠΏΠΎΠΌΠΎΡ‰ΡŒΡŽ INSERT’ΠΎΠ². Когда Π² Ρ‚Π°Π±Π»ΠΈΡ†Ρƒ ΠΈΠ΄ΡƒΡ‚ inserts, Ρ‚ΠΎ ΠΎΠ½ΠΈ ΠΏΠΎΠ»Π½ΠΎΡΡ‚ΡŒΡŽ Π·Π°ΠΏΠΎΠ»Π½ΡΡŽΡ‚ страничку, Π½Π΅ ΠΎΡΡ‚Π°Π²Π»ΡΡŽΡ‚ Π² Π½Π΅ΠΉ пустого мСста. ΠŸΠΎΡ‚ΠΎΠΌ выдСляСтся новая страничка. Π‘Π½ΠΎΠ²Π° Π΄Π°Π½Π½Ρ‹Π΅ Π·Π°ΠΏΠΎΠ»Π½ΡΡŽΡ‚ΡΡ. И это ΠΏΠΎΠ²Π΅Π΄Π΅Π½ΠΈΠ΅ ΠΏΠΎ ΡƒΠΌΠΎΠ»Ρ‡Π°Π½ΠΈΡŽ, fillfactor = 100 %.

ΠœΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ ΡΠ΄Π΅Π»Π°Ρ‚ΡŒ fillfactor Π² 70 %. Π’. Π΅. ΠΏΡ€ΠΈ inserts Π²Ρ‹Π΄Π΅Π»ΠΈΠ»Π°ΡΡŒ новая страничка, Π½ΠΎ заполнилось всСго лишь 70 % странички. И 30 % Ρƒ нас ΠΎΡΡ‚Π°Π»ΠΎΡΡŒ Π½Π° Ρ€Π΅Π·Π΅Ρ€Π². Когда Π½ΡƒΠΆΠ½ΠΎ Π±ΡƒΠ΄Π΅Ρ‚ ΡΠ΄Π΅Π»Π°Ρ‚ΡŒ update, Ρ‚ΠΎ ΠΎΠ½ с высокой Π΄ΠΎΠ»Π΅ΠΉ вСроятности ΠΏΡ€ΠΎΠΈΠ·ΠΎΠΉΠ΄Π΅Ρ‚ Π² Ρ‚ΠΎΠΉ ΠΆΠ΅ самой страничкС, ΠΈ новая вСрсия строки помСстится Π² Ρ‚Ρƒ ΠΆΠ΅ страничку. И Π±ΡƒΠ΄Π΅Ρ‚ сдСлан hot_update. Π’Π°ΠΊΠΈΠΌ ΠΎΠ±Ρ€Π°Π·ΠΎΠΌ облСгчаСтся запись Π½Π° Ρ‚Π°Π±Π»ΠΈΡ†Π°Ρ….

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

ΠžΡ‡Π΅Ρ€Π΅Π΄ΡŒ Π°Π²Ρ‚ΠΎΠ²Π°ΠΊΡƒΡƒΠΌΠ°. Автовакуум – это такая подсистСма, ΠΏΠΎ ΠΊΠΎΡ‚ΠΎΡ€ΠΎΠΉ статистикС Π² PostgreSQL ΠΎΡ‡Π΅Π½ΡŒ ΠΌΠ°Π»ΠΎ. ΠœΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ Π² Ρ‚Π°Π±Π»ΠΈΡ†Π°Ρ… Ρ‚ΠΎΠ»ΡŒΠΊΠΎ Π² pg_stat_activity ΡƒΠ²ΠΈΠ΄Π΅Ρ‚ΡŒ, сколько Ρƒ нас Π²Π°ΠΊΡƒΡƒΠΌΠΎΠ² длятся Π² Π΄Π°Π½Π½Ρ‹ΠΉ ΠΌΠΎΠΌΠ΅Π½Ρ‚. Однако ΠΏΠΎΠ½ΡΡ‚ΡŒ, сколько Ρ‚Π°Π±Π»ΠΈΡ† Π² ΠΎΡ‡Π΅Ρ€Π΅Π΄ΠΈ Ρƒ Π½Π΅Π³ΠΎ с Ρ…ΠΎΠ΄Ρƒ ΠΎΡ‡Π΅Π½ΡŒ слоТно.

ΠŸΡ€ΠΈΠΌΠ΅Ρ‡Π°Π½ΠΈΠ΅: _Начиная с вСрсии Postgres 10 ситуация с отслСТиваниСм Π²Π°Ρ‚ΠΎΠ²Π°ΠΊΡƒΡƒΠΌΠ° сильно ΡƒΠ»ΡƒΡ‡ΡˆΠΈΠ»Π°ΡΡŒ β€” появилось прСдставлСниС pg_stat_progressvacuum, ΠΊΠΎΡ‚ΠΎΡ€ΠΎΠ΅ сущСствСнно ΡƒΠΏΡ€ΠΎΡ‰Π°Π΅Ρ‚ вопрос ΠΌΠΎΠ½ΠΈΡ‚ΠΎΡ€ΠΈΠ½Π³Π° Π°Π²Ρ‚ΠΎΠ²Π°ΠΊΡƒΡƒΠΌΠ°.

ΠœΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ Π²ΠΎΡ‚ Ρ‚Π°ΠΊΠΎΠΉ ΡƒΠΏΡ€ΠΎΡ‰Π΅Π½Π½Ρ‹ΠΉ запрос. И ΠΌΠΎΠΆΠ΅ΠΌ ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ, ΠΊΠΎΠ³Π΄Π° Π΄ΠΎΠ»ΠΆΠ΅Π½ Π±ΡƒΠ΄Π΅Ρ‚ сдСлан Π²Π°ΠΊΡƒΡƒΠΌ. Но, ΠΊΠ°ΠΊ ΠΈ ΠΊΠΎΠ³Π΄Π° Π΄ΠΎΠ»ΠΆΠ΅Π½ Π·Π°ΠΏΡƒΡΡ‚ΠΈΡ‚ΡŒΡΡ Π²Π°ΠΊΡƒΡƒΠΌ? Π’ΠΎΡ‚ эти ΡƒΡΡ‚Π°Ρ€Π΅Π²ΡˆΠΈΠ΅ вСрсии строк, ΠΎ ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Ρ… я Π³ΠΎΠ²ΠΎΡ€ΠΈΠ» Ρ€Π°Π½ΡŒΡˆΠ΅. Update ΠΏΡ€ΠΎΠΈΠ·ΠΎΡˆΠ΅Π», новая вСрсия строки Π²ΡΡ‚Π°Π²ΠΈΠ»Π°ΡΡŒ. Появилась ΡƒΡΡ‚Π°Ρ€Π΅Π²ΡˆΠ°Ρ вСрсия строки. Π’ Ρ‚Π°Π±Π»ΠΈΡ†Π΅ pg_stat_user_tables Π΅ΡΡ‚ΡŒ Ρ‚Π°ΠΊΠΎΠΉ ΠΏΠ°Ρ€Π°ΠΌΠ΅Ρ‚Ρ€ n_dead_tup. Он ΠΏΠΎΠΊΠ°Π·Ρ‹Π²Π°Π΅Ρ‚ количСство "ΠΌΠ΅Ρ€Ρ‚Π²Ρ‹Ρ…" строк. И ΠΊΠ°ΠΊ Ρ‚ΠΎΠ»ΡŒΠΊΠΎ количСство ΠΌΠ΅Ρ€Ρ‚Π²Ρ‹Ρ… строк стало большС, Ρ‡Π΅ΠΌ ΠΎΠΏΡ€Π΅Π΄Π΅Π»Π΅Π½Π½Ρ‹ΠΉ ΠΏΠΎΡ€ΠΎΠ³, ΠΊ Ρ‚Π°Π±Π»ΠΈΡ†Π΅ ΠΏΡ€ΠΈΠ΄Π΅Ρ‚ Π°Π²Ρ‚ΠΎΠ²Π°ΠΊΡƒΡƒΠΌ.

И ΠΊΠ°ΠΊ рассчитываСтся этот ΠΏΠΎΡ€ΠΎΠ³? Π­Ρ‚ΠΎ Π²ΠΏΠΎΠ»Π½Π΅ ΠΊΠΎΠ½ΠΊΡ€Π΅Ρ‚Π½ΠΎΠ΅ ΠΏΡ€ΠΎΡ†Π΅Π½Ρ‚Π½ΠΎΠ΅ ΠΎΡ‚Π½ΠΎΡˆΠ΅Π½ΠΈΠ΅ ΠΎΡ‚ ΠΎΠ±Ρ‰Π΅Π³ΠΎ числа строк Π² Ρ‚Π°Π±Π»ΠΈΡ†Π΅. Π•ΡΡ‚ΡŒ ΠΏΠ°Ρ€Π°ΠΌΠ΅Ρ‚Ρ€ autovacuum_vacuum_scale_factor. Он ΠΈ опрСдСляСт ΠΏΡ€ΠΎΡ†Π΅Π½Ρ‚Π½ΠΎΠ΅ ΠΎΡ‚Π½ΠΎΡˆΠ΅Π½ΠΈΠ΅. Допустим, 10 % + Ρ‚Π°ΠΌ Π΄ΠΎΠΏΠΎΠ»Π½ΠΈΡ‚Π΅Π»ΡŒΠ½Ρ‹ΠΉ Π±Π°Π·ΠΎΠ²Ρ‹ΠΉ ΠΏΠΎΡ€ΠΎΠ³ Π² 50 строк. И Ρ‡Ρ‚ΠΎ получаСтся? Когда Ρƒ нас ΠΌΠ΅Ρ€Ρ‚Π²Ρ‹Ρ… строк стало большС Ρ‡Π΅ΠΌ "10 % + 50" ΠΎΡ‚ всСх строк Π² Ρ‚Π°Π±Π»ΠΈΡ†Π΅, Ρ‚ΠΎ ΠΌΡ‹ ставим Ρ‚Π°Π±Π»ΠΈΡ†Ρƒ Π½Π° Π°Π²Ρ‚ΠΎΠ²Π°ΡƒΡƒΠΌ.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

Однако Ρ‚ΡƒΡ‚ Π΅ΡΡ‚ΡŒ ΠΎΠ΄ΠΈΠ½ ΠΌΠΎΠΌΠ΅Π½Ρ‚. Π‘Π°Π·ΠΎΠ²Ρ‹Π΅ ΠΏΠΎΡ€ΠΎΠ³ΠΈ Ρƒ ΠΏΠ°Ρ€Π°ΠΌΠ΅Ρ‚Ρ€ΠΎΠ² av_base_thresh ΠΈ av_scale_factor ΠΌΠΎΠ³ΡƒΡ‚ Π½Π°Π·Π½Π°Ρ‡Π°Ρ‚ΡŒΡΡ ΠΈΠ½Π΄ΠΈΠ²ΠΈΠ΄ΡƒΠ°Π»ΡŒΠ½ΠΎ. И, соотвСтствСнно, ΠΏΠΎΡ€ΠΎΠ³ Π±ΡƒΠ΄Π΅Ρ‚ Π½Π΅ Π³Π»ΠΎΠ±Π°Π»ΡŒΠ½Ρ‹ΠΉ, Π° ΠΈΠ½Π΄ΠΈΠ²ΠΈΠ΄ΡƒΠ°Π»ΡŒΠ½Ρ‹ΠΉ для Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹. ΠŸΠΎΡΡ‚ΠΎΠΌΡƒ Ρ‡Ρ‚ΠΎΠ±Ρ‹ Ρ€Π°ΡΡΡ‡ΠΈΡ‚Π°Ρ‚ΡŒ, Ρ‚Π°ΠΌ Π½ΡƒΠΆΠ½ΠΎ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ ухищрСния ΠΈ ΡƒΠ»ΠΎΠ²ΠΊΠΈ. И Ссли Π²Π°ΠΌ интСрСсно, Ρ‚ΠΎ Π²Ρ‹ ΠΌΠΎΠΆΠ΅Ρ‚Π΅ ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ Π½Π° ΠΎΠΏΡ‹Ρ‚ Π½Π°ΡˆΠΈΡ… ΠΊΠΎΠ»Π»Π΅Π³ ΠΈΠ· Avito (ссылка Π½Π° слайдС Π½Π΅Π΄Π΅ΠΉΡΡ‚Π²ΠΈΡ‚Π΅Π»ΡŒΠ½ΠΎ ΠΈ ΠΎΠ±Π½ΠΎΠ²Π»Π΅Π½Π° Π² тСкстС).

Они написали для munin plugin, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ ΡƒΡ‡ΠΈΡ‚Ρ‹Π²Π°Π΅Ρ‚ эти Π²Π΅Ρ‰ΠΈ. Π’Π°ΠΌ портянка Π½Π° Π΄Π²Π° листа. Но считаСт ΠΎΠ½ ΠΊΠΎΡ€Ρ€Π΅ΠΊΡ‚Π½ΠΎ ΠΈ довольно эффСктивно позволяСт ΠΎΡ†Π΅Π½ΠΈΡ‚ΡŒ, Π³Π΄Π΅ Ρƒ нас Π²Π°ΠΊΡƒΡƒΠΌΠ° ΠΌΠ½ΠΎΠ³ΠΎ трСбуСтся для Ρ‚Π°Π±Π»ΠΈΡ†, Π³Π΄Π΅ ΠΌΠ°Π»ΠΎ.

Π§Ρ‚ΠΎ ΠΌΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ с этим ΡΠ΄Π΅Π»Π°Ρ‚ΡŒ? Если Ρƒ нас ΠΎΡ‡Π΅Ρ€Π΅Π΄ΡŒ большая ΠΈ Π°Π²Ρ‚ΠΎΠ²Π°ΠΊΡƒΡƒΠΌ Π½Π΅ справляСтся, Ρ‚ΠΎ ΠΌΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ ΠΏΠΎΠ΄Π½ΡΡ‚ΡŒ количСство Π²ΠΎΡ€ΠΊΠ΅Ρ€ΠΎΠ² Π²Π°ΠΊΡƒΡƒΠΌΠ°, Π»ΠΈΠ±ΠΎ просто ΡΠ΄Π΅Π»Π°Ρ‚ΡŒ Π²Π°ΠΊΡƒΡƒΠΌ агрСссивнСС, Ρ‡Ρ‚ΠΎΠ±Ρ‹ ΠΎΠ½ триггСрился Ρ€Π°Π½ΡŒΡˆΠ΅, ΠΎΠ±Ρ€Π°Π±Π°Ρ‚Ρ‹Π²Π°Π» Ρ‚Π°Π±Π»ΠΈΡ†Ρƒ малСнькими кусочками. И Ρ‚Π΅ΠΌ самым ΠΎΡ‡Π΅Ρ€Π΅Π΄ΡŒ Π±ΡƒΠ΄Π΅Ρ‚ ΡƒΠΌΠ΅Π½ΡŒΡˆΠ°Ρ‚ΡŒΡΡ. β€” Π“Π»Π°Π²Π½ΠΎΠ΅ здСсь ΡΠ»Π΅Π΄ΠΈΡ‚ΡŒ Π·Π° Π½Π°Π³Ρ€ΡƒΠ·ΠΊΠΎΠΉ Π½Π° диски, Ρ‚.ΠΊ. Π²Π°ΠΊΡƒΡƒΠΌ ΡˆΡ‚ΡƒΠΊΠ° нСбСсплатная, хотя с появлСниСм SSD/NVMe устройств ΠΏΡ€ΠΎΠ±Π»Π΅ΠΌΠ° стала ΠΌΠ΅Π½Π΅Π΅ Π·Π°ΠΌΠ΅Ρ‚Π½ΠΎΠΉ.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Pg_stat_all_indexes – это статистика ΠΏΠΎ индСксам. Она нСбольшая. И ΠΌΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ ΠΏΠΎ Π½Π΅ΠΉ ΠΏΠΎΠ»ΡƒΡ‡ΠΈΡ‚ΡŒ ΠΈΠ½Ρ„ΠΎΡ€ΠΌΠ°Ρ†ΠΈΡŽ ΠΏΠΎ использованию индСксов. И Π½Π°ΠΏΡ€ΠΈΠΌΠ΅Ρ€ ΠΌΠΎΠΆΠ΅ΠΌ ΠΎΠΏΡ€Π΅Π΄Π΅Π»ΠΈΡ‚ΡŒ ΠΊΠ°ΠΊΠΈΠ΅ индСксы Ρƒ нас лишниС.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Как я ΡƒΠΆΠ΅ Π³ΠΎΠ²ΠΎΡ€ΠΈΠ», update – это Π½Π΅ Ρ‚ΠΎΠ»ΡŒΠΊΠΎ ΠΎΠ±Π½ΠΎΠ²Π»Π΅Π½ΠΈΠ΅ Ρ‚Π°Π±Π»ΠΈΡ†, это Π΅Ρ‰Π΅ ΠΈ ΠΎΠ±Π½ΠΎΠ²Π»Π΅Π½ΠΈΠ΅ индСксов. БоотвСтствСнно, Ссли Ρƒ нас Π½Π° Ρ‚Π°Π±Π»ΠΈΡ†Π΅ ΠΌΠ½ΠΎΠ³ΠΎ индСксов Ρ‚ΠΎ ΠΏΡ€ΠΈ ΠΎΠ±Π½ΠΎΠ²Π»Π΅Π½ΠΈΠΈ строк Π² Ρ‚Π°Π±Π»ΠΈΡ†Π΅, индСксы проиндСксированных ΠΏΠΎΠ»Π΅ΠΉ Ρ‚Π°ΠΊΠΆΠ΅ Π½ΡƒΠΆΠ½ΠΎ ΠΎΠ±Π½ΠΎΠ²ΠΈΡ‚ΡŒ, ΠΈ Ссли Ρƒ нас Π΅ΡΡ‚ΡŒ Π½Π΅ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅ΠΌΡ‹Π΅ индСксы, ΠΏΠΎ ΠΊΠΎΡ‚ΠΎΡ€Ρ‹ΠΌ Π½Π΅Ρ‚ индСксовых сканирований, Ρ‚ΠΎ ΠΎΠ½ΠΈ Ρƒ нас висят балластом. И ΠΎΡ‚ Π½ΠΈΡ… Π½ΡƒΠΆΠ½ΠΎ ΠΈΠ·Π±Π°Π²Π»ΡΡ‚ΡŒΡΡ. Для этого Π½Π°ΠΌ Π½ΡƒΠΆΠ½ΠΎ ΠΏΠΎΠ»Π΅ idx_scan. ΠœΡ‹ просто смотрим количСство индСксных сканирований. Если Ρƒ индСксов ноль сканирований Π·Π° ΠΎΡ‚Π½ΠΎΡΠΈΡ‚Π΅Π»ΡŒΠ½ΠΎ Π΄Π»ΠΈΠ½Π½Ρ‹ΠΉ ΠΏΠ΅Ρ€ΠΈΠΎΠ΄ хранСния статистики (Π½Π΅ ΠΌΠ΅Π½Π΅Π΅ Ρ‡Π΅ΠΌ 2-3 Π½Π΅Π΄Π΅Π»ΠΈ), Ρ‚ΠΎ вСроятнСй всСго это ΠΏΠ»ΠΎΡ…ΠΈΠ΅ индСксы, Π½Π°ΠΌ Π½ΡƒΠΆΠ½ΠΎ ΠΎΡ‚ Π½ΠΈΡ… ΠΈΠ·Π±Π°Π²ΠΈΡ‚ΡŒΡΡ.

ΠŸΡ€ΠΈΠΌΠ΅Ρ‡Π°Π½ΠΈΠ΅: ΠŸΡ€ΠΈ поискС Π½Π΅ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅ΠΌΡ‹Ρ… индСксов Π² случаС кластСров ΠΏΠΎΡ‚ΠΎΠΊΠΎΠ²ΠΎΠΉ Ρ€Π΅ΠΏΠ»ΠΈΠΊΠ°Ρ†ΠΈΠΈ Π½ΡƒΠΆΠ½ΠΎ ΠΏΡ€ΠΎΠ²Π΅Ρ€ΡΡ‚ΡŒ всС ΡƒΠ·Π»Ρ‹ кластСра, Ρ‚.ΠΊ. статистика Π½Π΅ глобальная, ΠΈ Ссли индСкс Π½Π΅ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅Ρ‚ΡΡ Π½Π° мастСрС, Ρ‚ΠΎ ΠΎΠ½ ΠΌΠΎΠΆΠ΅Ρ‚ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒΡΡ Π½Π° Ρ€Π΅ΠΏΠ»ΠΈΠΊΠ°Ρ… (Ссли Ρ‚Π°ΠΌ Π΅ΡΡ‚ΡŒ Π½Π°Π³Ρ€ΡƒΠ·ΠΊΠ°).

Π”Π²Π΅ ссылки:

https://github.com/dataegret/pg-utils/blob/master/sql/low_used_indexes.sql

http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html

Π­Ρ‚ΠΎ Π±ΠΎΠ»Π΅Π΅ ΠΏΡ€ΠΎΠ΄Π²ΠΈΠ½ΡƒΡ‚Ρ‹Π΅ ΠΏΡ€ΠΈΠΌΠ΅Ρ€Ρ‹ запросов для Ρ‚ΠΎΠ³ΠΎ, ΠΊΠ°ΠΊ ΠΈΡΠΊΠ°Ρ‚ΡŒ Π½Π΅ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅ΠΌΡ‹Π΅ индСксы.

Вторая ссылка – это довольно интСрСсный запрос. Π’Π°ΠΌ ΠΎΡ‡Π΅Π½ΡŒ Π½Π΅Ρ‚Ρ€ΠΈΠ²ΠΈΠ°Π»ΡŒΠ½Π°Ρ Π»ΠΎΠ³ΠΈΠΊΠ° Π·Π°Π»ΠΎΠΆΠ΅Π½Π°. Π Π΅ΠΊΠΎΠΌΠ΅Π½Π΄ΡƒΡŽ Π΅Π³ΠΎ для ознакомлСния.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Π§Ρ‚ΠΎ Π΅Ρ‰Π΅ стоит ΠΏΠΎΠ΄Ρ‹Ρ‚ΠΎΠΆΠΈΡ‚ΡŒ ΠΏΠΎ индСксам?

  • ΠΠ΅ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅ΠΌΡ‹Π΅ индСксы – это ΠΏΠ»ΠΎΡ…ΠΎ.

  • Π—Π°Π½ΠΈΠΌΠ°ΡŽΡ‚ мСсто.

  • Π—Π°ΠΌΠ΅Π΄Π»ΡΡŽΡ‚ ΠΎΠΏΠ΅Ρ€Π°Ρ†ΠΈΠΈ обновлСния.

  • Π›ΠΈΡˆΠ½ΡΡ Ρ€Π°Π±ΠΎΡ‚Π° для Π²Π°ΠΊΡƒΡƒΠΌΠ°.

Если ΠΌΡ‹ ΡƒΠ΄Π°Π»ΠΈΠΌ Π½Π΅ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅ΠΌΡ‹Π΅ индСксы, Ρ‚ΠΎ ΠΌΡ‹ сдСлаСм Π±Π°Π·Π΅ Ρ‚ΠΎΠ»ΡŒΠΊΠΎ Π»ΡƒΡ‡ΡˆΠ΅.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Π‘Π»Π΅Π΄ΡƒΡŽΡ‰Π΅Π΅ прСдставлСниС – это pg_stat_activity. Π­Ρ‚ΠΎ Π°Π½Π°Π»ΠΎΠ³ ΡƒΡ‚ΠΈΠ»ΠΈΡ‚Ρ‹ ps, Ρ‚ΠΎΠ»ΡŒΠΊΠΎ Π² PostgreSQL. Если ps‘ΠΎΠΌ Π²Ρ‹ смотритС процСссы Π² ΠΎΠΏΠ΅Ρ€Π°Ρ†ΠΈΠΎΠ½Π½ΠΎΠΉ систСмС, Ρ‚ΠΎ pg_stat_activity Π²Π°ΠΌ ΠΏΠΎΠΊΠ°ΠΆΠ΅Ρ‚ Π°ΠΊΡ‚ΠΈΠ²Π½ΠΎΡΡ‚ΡŒ Π²Π½ΡƒΡ‚Ρ€ΠΈ PostgreSQL.

Π§Ρ‚ΠΎ ΠΌΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ ΠΎΡ‚Ρ‚ΡƒΠ΄Π° ΠΏΠΎΠ»Π΅Π·Π½ΠΎΠ³ΠΎ Π²Π·ΡΡ‚ΡŒ?

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;

ΠœΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ ΠΎΠ±Ρ‰ΡƒΡŽ Π°ΠΊΡ‚ΠΈΠ²Π½ΠΎΡΡ‚ΡŒ, Ρ‡Ρ‚ΠΎ происходит Π² Π±Π°Π·Π΅. МоТСм ΡΠ΄Π΅Π»Π°Ρ‚ΡŒ Π½ΠΎΠ²Ρ‹ΠΉ Π΄Π΅ΠΏΠ»ΠΎΠΉ. Π£ нас Ρ‚Π°ΠΌ всС Π²Π·ΠΎΡ€Π²Π°Π»ΠΎΡΡŒ, ΠΊΠΎΠ½Π½Π΅ΠΊΡ‚Ρ‹ Π½ΠΎΠ²Ρ‹Π΅ Π½Π΅ ΠΏΡ€ΠΈΠ½ΠΈΠΌΠ°ΡŽΡ‚ΡΡ, ошибки ΡΡ‹ΠΏΠ»ΡŽΡ‚ΡΡ Π² ΠΏΡ€ΠΈΠ»ΠΎΠΆΠ΅Π½ΠΈΠΈ.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;

ΠœΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ Π²Ρ‹ΠΏΠΎΠ»Π½ΠΈΡ‚ΡŒ Π²ΠΎΡ‚ Ρ‚Π°ΠΊΠΎΠΉ запрос ΠΈ ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ ΠΎΠ±Ρ‰ΠΈΠΉ ΠΏΡ€ΠΎΡ†Π΅Π½Ρ‚ ΠΏΠΎΠ΄ΠΊΠ»ΡŽΡ‡Π΅Π½ΠΈΠΉ ΠΎΡ‚Π½ΠΎΡΠΈΡ‚Π΅Π»ΡŒΠ½ΠΎ максимального Π»ΠΈΠΌΠΈΡ‚Π° ΠΏΠΎΠ΄ΠΊΠ»ΡŽΡ‡Π΅Π½ΠΈΠΉ ΠΈ ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ, ΠΊΡ‚ΠΎ Ρƒ нас Π·Π°Π½ΠΈΠΌΠ°Π΅Ρ‚ большС всСго ΠΊΠΎΠ½Π½Π΅ΠΊΡ‚ΠΎΠ². И Π² Π΄Π°Π½Π½ΠΎΠΌ ΠΏΡ€ΠΈΠ²Π΅Π΄Π΅Π½Π½ΠΎΠΌ случаС ΠΌΡ‹ Π²ΠΈΠ΄ΠΈΠΌ, Ρ‡Ρ‚ΠΎ user cron_role ΠΎΡ‚ΠΊΡ€Ρ‹Π» 508 ΠΊΠΎΠ½Π½Π΅ΠΊΡ‚ΠΎΠ². И Ρ‡Ρ‚ΠΎ-Ρ‚ΠΎ с Π½ΠΈΠΌ Ρ‚Π°ΠΌ ΠΏΡ€ΠΎΠΈΠ·ΠΎΡˆΠ»ΠΎ. НуТно с Π½ΠΈΠΌ Ρ€Π°Π·Π±ΠΈΡ€Π°Ρ‚ΡŒΡΡ ΠΈ ΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ. И Π²ΠΏΠΎΠ»Π½Π΅ Π²ΠΎΠ·ΠΌΠΎΠΆΠ½ΠΎ, Ρ‡Ρ‚ΠΎ это ΠΊΠ°ΠΊΠΎΠ΅-Ρ‚ΠΎ аномальноС число ΠΏΠΎΠ΄ΠΊΠ»ΡŽΡ‡Π΅Π½ΠΈΠΉ.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Если Ρƒ нас Π½Π°Π³Ρ€ΡƒΠ·ΠΊΠ° OLTP, запросы Π΄ΠΎΠ»ΠΆΠ½Ρ‹ Π²Ρ‹ΠΏΠΎΠ»Π½ΡΡ‚ΡŒΡΡ быстро, ΠΎΡ‡Π΅Π½ΡŒ быстро ΠΈ Π½Π΅ Π΄ΠΎΠ»ΠΆΠ½ΠΎ Π±Ρ‹Ρ‚ΡŒ Π΄ΠΎΠ»Π³ΠΈΡ… запросов. Однако, Ссли Π²ΠΎΠ·Π½ΠΈΠΊΠ°ΡŽΡ‚ Π΄ΠΎΠ»Π³ΠΈΠ΅ запросы, Ρ‚ΠΎ Π² краткосрочной пСрспСктивС Π½ΠΈΡ‡Π΅Π³ΠΎ ΡΡ‚Ρ€Π°ΡˆΠ½ΠΎΠ³ΠΎ Π½Π΅Ρ‚, Π½ΠΎ Π² долгосрочной пСрспСктивС Π΄ΠΎΠ»Π³ΠΈΠ΅ запросы врСдят Π±Π°Π·Π΅, ΠΎΠ½ΠΈ ΡƒΠ²Π΅Π»ΠΈΡ‡ΠΈΠ²Π°ΡŽΡ‚ bloat эффСкт Ρ‚Π°Π±Π»ΠΈΡ†, ΠΊΠΎΠ³Π΄Π° происходит фрагмСнтация Ρ‚Π°Π±Π»ΠΈΡ†. И ΠΎΡ‚ bloat, ΠΈ ΠΎΡ‚ Π΄ΠΎΠ»Π³ΠΈΡ… запросов Π½ΡƒΠΆΠ½ΠΎ ΠΈΠ·Π±Π°Π²Π»ΡΡ‚ΡŒΡΡ.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

select
client_addr, usename, datname,
clock_timestamp() - xact_start as xact_age,
clock_timestamp() - query_start as query_age,
query
from pg_stat_activity order by xact_start, query_start;

ΠžΠ±Ρ€Π°Ρ‚ΠΈΡ‚Π΅ Π²Π½ΠΈΠΌΠ°Π½ΠΈΠ΅: Π²ΠΎΡ‚ Ρ‚Π°ΠΊΠΈΠΌ запросом ΠΌΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ ΠΎΠΏΡ€Π΅Π΄Π΅Π»ΡΡ‚ΡŒ Π΄ΠΎΠ»Π³ΠΈΠ΅ запросы ΠΈ Ρ‚Ρ€Π°Π½Π·Π°ΠΊΡ†ΠΈΠΈ. ΠœΡ‹ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅ΠΌ Ρ„ΡƒΠ½ΠΊΡ†ΠΈΡŽ clock_timestamp() для опрСдСлСния Π²Ρ€Π΅ΠΌΠ΅Π½ΠΈ Ρ€Π°Π±ΠΎΡ‚Ρ‹. Π”ΠΎΠ»Π³ΠΈΠ΅ запросы, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ ΠΌΡ‹ нашли, ΠΌΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ ΠΈΡ… Π·Π°ΠΏΠΎΠΌΠ½ΠΈΡ‚ΡŒ, Π²Ρ‹ΠΏΠΎΠ»Π½ΠΈΡ‚ΡŒ explain, ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ ΠΏΠ»Π°Π½Ρ‹ ΠΈ ΠΊΠ°ΠΊ-Ρ‚ΠΎ ΠΎΠΏΡ‚ΠΈΠΌΠΈΠ·ΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ. Π’Π΅ΠΊΡƒΡ‰ΠΈΠ΅ Π΄ΠΎΠ»Π³ΠΈΠ΅ запросы ΠΌΡ‹ отстрСливаСм ΠΈ дальшС ΠΆΠΈΠ²Π΅ΠΌ.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

ΠŸΠ»ΠΎΡ…ΠΈΠ΅ Ρ‚Ρ€Π°Π½Π·Π°ΠΊΡ†ΠΈΠΈ – это Ρ‚Ρ€Π°Π½Π·Π°ΠΊΡ†ΠΈΠΈ Π² состоянии idle in transaction ΠΈ idle in transaction (aborted).

Π§Ρ‚ΠΎ это Π·Π½Π°Ρ‡ΠΈΡ‚? Π’Ρ€Π°Π½Π·Π°ΠΊΡ†ΠΈΠΈ ΠΈΠΌΠ΅ΡŽΡ‚ нСсколько состояний. И ΠΎΠ΄Π½ΠΎ ΠΈΠ· этих состояний ΠΌΠΎΠ³ΡƒΡ‚ ΠΏΡ€ΠΈΠ½ΠΈΠΌΠ°Ρ‚ΡŒ Π² любой ΠΌΠΎΠΌΠ΅Π½Ρ‚ Π²Ρ€Π΅ΠΌΠ΅Π½ΠΈ. Для опрСдСлСния состояний Π΅ΡΡ‚ΡŒ ΠΏΠΎΠ»Π΅ state Π² этом прСдставлСнии. И ΠΌΡ‹ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅ΠΌ Π΅Π³ΠΎ для опрСдСлСния состояния.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

И, ΠΊΠ°ΠΊ я ΡƒΠΆΠ΅ сказал Π²Ρ‹ΡˆΠ΅, эти Π΄Π²Π° состояния idle in transaction ΠΈ idle in transaction (aborted) – это ΠΏΠ»ΠΎΡ…ΠΎ. Π§Ρ‚ΠΎ это Ρ‚Π°ΠΊΠΎΠ΅? Π­Ρ‚ΠΎ ΠΊΠΎΠ³Π΄Π° ΠΏΡ€ΠΈΠ»ΠΎΠΆΠ΅Π½ΠΈΠ΅ ΠΎΡ‚ΠΊΡ€Ρ‹Π»ΠΎ Ρ‚Ρ€Π°Π½Π·Π°ΠΊΡ†ΠΈΡŽ, сдСлало ΠΊΠ°ΠΊΠΈΠ΅-Ρ‚ΠΎ дСйствия ΠΈ ΡƒΡˆΠ»ΠΎ ΠΏΠΎ своим Π΄Π΅Π»Π°ΠΌ. Вранзакция ΠΎΡΡ‚Π°Π»Π°ΡΡŒ открытая. Она висит, Π² Π½Π΅ΠΉ Π½ΠΈΡ‡Π΅Π³ΠΎ Π½Π΅ происходит, ΠΎΠ½Π° Π·Π°Π½ΠΈΠΌΠ°Π΅Ρ‚ ΠΊΠΎΠ½Π½Π΅ΠΊΡ‚, Π±Π»ΠΎΠΊΠΈΡ€ΠΎΠ²ΠΊΠΈ Π½Π° ΠΈΠ·ΠΌΠ΅Π½Π΅Π½Π½Ρ‹Π΅ строки ΠΈ ΠΏΠΎΡ‚Π΅Π½Ρ†ΠΈΠ°Π»ΡŒΠ½ΠΎ Π΅Ρ‰Π΅ ΡƒΠ²Π΅Π»ΠΈΡ‡ΠΈΠ²Π°Π΅Ρ‚ bloat Π΄Ρ€ΡƒΠ³ΠΈΡ… Ρ‚Π°Π±Π»ΠΈΡ†, ΠΈΠ·-Π·Π° Π°Ρ€Ρ…ΠΈΡ‚Π΅ΠΊΡ‚ΡƒΡ€Ρ‹ Ρ‚Ρ€Π°Π½Π·Π°ΠΊΡ†ΠΈΠΎΠ½Π½ΠΎΠ³ΠΎ Π΄Π²ΠΈΠΆΠΊΠ° Postrges’Π°. И Ρ‚Π°ΠΊΠΈΠ΅ Ρ‚Ρ€Π°Π½Π·Π°ΠΊΡ†ΠΈΠΈ Ρ‚ΠΎΠΆΠ΅ слСдуСт ΠΎΡ‚ΡΡ‚Ρ€Π΅Π»ΠΈΠ²Π°Ρ‚ΡŒ, ΠΏΠΎΡ‚ΠΎΠΌΡƒ Ρ‡Ρ‚ΠΎ ΠΎΠ½ΠΈ Π²Ρ€Π΅Π΄Π½Ρ‹Π΅ Π²ΠΎΠΎΠ±Ρ‰Π΅, ΠΏΡ€ΠΈ любом раскладС.

Если Π²Ρ‹ Π²ΠΈΠ΄ΠΈΡ‚Π΅, Ρ‡Ρ‚ΠΎ ΠΈΡ… Ρƒ вас Π² Π±Π°Π·Π΅ большС 5-10-20, Ρ‚ΠΎ Π½ΡƒΠΆΠ½ΠΎ ΡƒΠΆΠ΅ ΠΎΠ±Π΅ΡΠΏΠΎΠΊΠΎΠΈΡ‚ΡŒΡΡ ΠΈ Π½Π°Ρ‡ΠΈΠ½Π°Ρ‚ΡŒ с Π½ΠΈΠΌΠΈ Ρ‡Ρ‚ΠΎ-Ρ‚ΠΎ Π΄Π΅Π»Π°Ρ‚ΡŒ.

Π—Π΄Π΅ΡΡŒ ΠΌΡ‹ Ρ‚Π°ΠΊΠΆΠ΅ для Π²Ρ€Π΅ΠΌΠ΅Π½ΠΈ вычислСния ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅ΠΌ clock_timestamp(). Π’Ρ€Π°Π½Π·Π°ΠΊΡ†ΠΈΠΈ отстрСливаСм, ΠΏΡ€ΠΈΠ»ΠΎΠΆΠ΅Π½ΠΈΠ΅ ΠΎΠΏΡ‚ΠΈΠΌΠΈΠ·ΠΈΡ€ΡƒΠ΅ΠΌ.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Как я ΡƒΠΆΠ΅ Π³ΠΎΠ²ΠΎΡ€ΠΈΠ» Π²Ρ‹ΡˆΠ΅, Π±Π»ΠΎΠΊΠΈΡ€ΠΎΠ²ΠΊΠΈ – это ΠΊΠΎΠ³Π΄Π° Π΄Π²Π΅ ΠΈ большС Ρ‚Ρ€Π°Π½Π·Π°ΠΊΡ†ΠΈΠΉ Π±ΠΎΡ€ΡŽΡ‚ΡΡ Π·Π° ΠΎΠ΄ΠΈΠ½ ΠΈΠ»ΠΈ Π³Ρ€ΡƒΠΏΠΏΡƒ рСсурсов. Для этого Ρƒ нас Π΅ΡΡ‚ΡŒ ΠΏΠΎΠ»Π΅ waiting с Π±ΡƒΠ»Π΅Π²Ρ‹ΠΌ Π·Π½Π°Ρ‡Π΅Π½ΠΈΠ΅ΠΌ true ΠΈΠ»ΠΈ false.

True – это Π·Π½Π°Ρ‡ΠΈΡ‚, Ρ‡Ρ‚ΠΎ процСсс находится Π² ΠΎΠΆΠΈΠ΄Π°Π½ΠΈΠΈ, Π½ΡƒΠΆΠ½ΠΎ Ρ‡Ρ‚ΠΎ-Ρ‚ΠΎ Π΄Π΅Π»Π°Ρ‚ΡŒ. Когда процСсс находится Π² ΠΎΠΆΠΈΠ΄Π°Π½ΠΈΠΈ, Π·Π½Π°Ρ‡ΠΈΡ‚, ΠΊΠ»ΠΈΠ΅Π½Ρ‚, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹ΠΉ ΠΈΠ½ΠΈΡ†ΠΈΠΈΡ€ΠΎΠ²Π°Π» этот процСсс Ρ‚ΠΎΠΆΠ΅ ΠΆΠ΄Π΅Ρ‚. ΠšΠ»ΠΈΠ΅Π½Ρ‚ Π² Π±Ρ€Π°ΡƒΠ·Π΅Ρ€Π΅ сидит ΠΈ Ρ‚ΠΎΠΆΠ΅ ΠΆΠ΄Π΅Ρ‚.

Π’Π½ΠΈΠΌΠ°Π½ΠΈΠ΅: _Начиная с вСрсии Postgres 9.6 ΠΏΠΎΠ»Π΅ waiting ΡƒΠ΄Π°Π»Π΅Π½ΠΎ ΠΈ вмСсто Π½Π΅Π³ΠΎ Π΄ΠΎΠ±Π°Π²Π»Π΅Π½Ρ‹ Π΄Π²Π° Π±ΠΎΠ»Π΅Π΅ ΠΈΠ½Ρ„ΠΎΡ€ΠΌΠ°Ρ‚ΠΈΠ²Π½Ρ‹Ρ… поля wait_event_type ΠΈ wait_event._

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Π§Ρ‚ΠΎ Π΄Π΅Π»Π°Ρ‚ΡŒ? Если Π²Ρ‹ Π²ΠΈΠ΄ΠΈΡ‚Π΅ true, Π² Ρ‚Π΅Ρ‡Π΅Π½ΠΈΠ΅ Π΄ΠΎΠ»Π³ΠΎ Π²Ρ€Π΅ΠΌΠ΅Π½ΠΈ Ρ‚ΠΎ Π·Π½Π°Ρ‡ΠΈΡ‚, ΠΎΡ‚ Ρ‚Π°ΠΊΠΈΡ… запросов Π½Π°Π΄ΠΎ ΠΈΠ·Π±Π°Π²Π»ΡΡ‚ΡŒΡΡ. ΠœΡ‹ просто Ρ‚Π°ΠΊΠΈΠ΅ Ρ‚Ρ€Π°Π½Π·Π°ΠΊΡ†ΠΈΠΈ отстрСливаСм. Π Π°Π·Ρ€Π°Π±ΠΎΡ‚Ρ‡ΠΈΠΊΠ°ΠΌ пишСм, Ρ‡Ρ‚ΠΎ Π½ΡƒΠΆΠ½ΠΎ ΠΊΠ°ΠΊ-Ρ‚ΠΎ ΠΎΠΏΡ‚ΠΈΠΌΠΈΠ·ΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ, Ρ‡Ρ‚ΠΎΠ±Ρ‹ Π½Π΅ Π±Ρ‹Π»ΠΎ Π³ΠΎΠ½ΠΊΠΈ Π·Π° рСсурсами. И дальшС Ρ€Π°Π·Ρ€Π°Π±ΠΎΡ‚Ρ‡ΠΈΠΊΠΈ ΠΎΠΏΡ‚ΠΈΠΌΠΈΠ·ΠΈΡ€ΡƒΡŽΡ‚ ΠΏΡ€ΠΈΠ»ΠΎΠΆΠ΅Π½ΠΈΠ΅, Ρ‡Ρ‚ΠΎΠ±Ρ‹ Ρ‚Π°ΠΊΠΎΠ³ΠΎ Π½Π΅ Π²ΠΎΠ·Π½ΠΈΠΊΠ°Π»ΠΎ.

И ΠΊΡ€Π°ΠΉΠ½ΠΈΠΉ, Π½ΠΎ ΠΏΡ€ΠΈ этом ΠΏΠΎΡ‚Π΅Π½Ρ†ΠΈΠ°Π»ΡŒΠ½ΠΎ Π½Π΅ Ρ„Π°Ρ‚Π°Π»ΡŒΠ½Ρ‹ΠΉ случай – это Π²ΠΎΠ·Π½ΠΈΠΊΠ½ΠΎΠ²Π΅Π½ΠΈΠ΅ deadlocks. Π”Π²Π΅ Ρ‚Ρ€Π°Π½Π·Π°ΠΊΡ†ΠΈΠΈ ΠΎΠ±Π½ΠΎΠ²ΠΈΠ»ΠΈ Π΄Π²Π° рСсурса, ΠΏΠΎΡ‚ΠΎΠΌ ΠΎΠ±Ρ€Π°Ρ‰Π°ΡŽΡ‚ΡΡ ΠΊ Π½ΠΈΠΌ снова, ΡƒΠΆΠ΅ ΠΊ ΠΏΡ€ΠΎΡ‚ΠΈΠ²ΠΎΠΏΠΎΠ»ΠΎΠΆΠ½Ρ‹ΠΌ рСсурсам. PostgreSQL Π² этом случаС Π±Π΅Ρ€Π΅Ρ‚ ΠΈ сам отстрСливаСт Ρ‚Ρ€Π°Π½Π·Π°ΠΊΡ†ΠΈΡŽ, Ρ‡Ρ‚ΠΎΠ±Ρ‹ другая ΠΌΠΎΠ³Π»Π° ΠΏΡ€ΠΎΠ΄ΠΎΠ»ΠΆΠΈΡ‚ΡŒ Ρ€Π°Π±ΠΎΡ‚Ρƒ. Π­Ρ‚ΠΎ тупиковая ситуация ΠΈ ΠΎΠ½Π° сама Π½Π΅ разбираСтся. ΠŸΠΎΡΡ‚ΠΎΠΌΡƒ PostgreSQL Π²Ρ‹Π½ΡƒΠΆΠ΄Π΅Π½ ΠΏΡ€ΠΈΠ½ΠΈΠΌΠ°Ρ‚ΡŒ ΠΊΡ€Π°ΠΉΠ½ΠΈΠ΅ ΠΌΠ΅Ρ€Ρ‹.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/c4_06_show_locked_queries.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_95.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_96.sql

http://big-elephants.com/2013-09/exploring-query-locks-in-postgres/

И Π²ΠΎΡ‚ Π΄Π²Π° запроса, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ ΠΏΠΎΠ·Π²ΠΎΠ»ΡΡŽΡ‚ ΠΎΡ‚ΡΠ»Π΅ΠΆΠΈΠ²Π°Ρ‚ΡŒ Π±Π»ΠΎΠΊΠΈΡ€ΠΎΠ²ΠΊΠΈ. ΠœΡ‹ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅ΠΌ прСдставлСниС pg_locks, которая позволяСт ΠΎΡ‚ΡΠ»Π΅ΠΆΠΈΠ²Π°Ρ‚ΡŒ тяТСловСсныС Π±Π»ΠΎΠΊΠΈΡ€ΠΎΠ²ΠΊΠΈ.

И пСрвая ссылка – это сам тСкст запроса. Он довольно-Ρ‚Π°ΠΊΠΈ Π΄Π»ΠΈΠ½Π½Ρ‹ΠΉ.

И вторая ссылка – это ΡΡ‚Π°Ρ‚ΡŒΡ ΠΏΠΎ locks. Π•Π΅ ΠΏΠΎΠ»Π΅Π·Π½ΠΎ ΠΏΠΎΡ‡ΠΈΡ‚Π°Ρ‚ΡŒ, ΠΎΠ½Π° ΠΎΡ‡Π΅Π½ΡŒ интСрСсная.

Π˜Ρ‚Π°ΠΊ, Ρ‡Ρ‚ΠΎ ΠΌΡ‹ Π²ΠΈΠ΄ΠΈΠΌ? ΠœΡ‹ Π²ΠΈΠ΄ΠΈΠΌ Π΄Π²Π° запроса. Вранзакция с ALTER TABLE – это Π±Π»ΠΎΠΊΠΈΡ€ΡƒΡŽΡ‰Π°Ρ транзакция. Она Π·Π°ΠΏΡƒΡΡ‚ΠΈΠ»Π°ΡΡŒ, Π½ΠΎ Π½Π΅ Π·Π°Π²Π΅Ρ€ΡˆΠΈΠ»Π°ΡΡŒ ΠΈ ΠΏΡ€ΠΈΠ»ΠΎΠΆΠ΅Π½ΠΈΠ΅ Π·Π°ΠΏΡΡ‚ΠΈΠ²ΡˆΠ΅Π΅ эту Ρ‚Ρ€Π°Π½Π·Π°ΠΊΡ†ΠΈΡŽ Π³Π΄Π΅-Ρ‚ΠΎ занимаСтся Π΄Ρ€ΡƒΠ³ΠΈΠΌΠΈ Π΄Π΅Π»Π°ΠΌΠΈ. И Π²Ρ‚ΠΎΡ€ΠΎΠΉ запрос – update. Он ΠΆΠ΄Π΅Ρ‚, ΠΊΠΎΠ³Π΄Π° закончится alter table, Ρ‡Ρ‚ΠΎΠ±Ρ‹ ΠΏΡ€ΠΎΠ΄ΠΎΠ»ΠΆΠΈΡ‚ΡŒ свою Ρ€Π°Π±ΠΎΡ‚Ρƒ.

Π’ΠΎΡ‚ Ρ‚Π°ΠΊ ΠΌΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ Π²Ρ‹ΡΡΠ½ΡΡ‚ΡŒ, ΠΊΡ‚ΠΎ ΠΊΠΎΠ³ΠΎ Π·Π°Π»ΠΎΡ‡ΠΈΠ», Π΄Π΅Ρ€ΠΆΠΈΡ‚ ΠΈ ΠΌΠΎΠΆΠ΅ΠΌ Ρ€Π°Π·Π±ΠΈΡ€Π°Ρ‚ΡŒΡΡ с этим дальшС.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Π‘Π»Π΅Π΄ΡƒΡŽΡ‰ΠΈΠΉ ΠΌΠΎΠ΄ΡƒΠ»ΡŒ – это pg_stat_statements. Как я ΡƒΠΆΠ΅ сказал, это ΠΌΠΎΠ΄ΡƒΠ»ΡŒ. Π§Ρ‚ΠΎΠ±Ρ‹ ΠΈΠΌ Π²ΠΎΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒΡΡ Π½ΡƒΠΆΠ½ΠΎ ΠΏΠΎΠ΄Π³Ρ€ΡƒΠ·ΠΈΡ‚ΡŒ Π΅Π³ΠΎ Π±ΠΈΠ±Π»ΠΈΠΎΡ‚Π΅ΠΊΡƒ Π² ΠΊΠΎΠ½Ρ„ΠΈΠ³ΡƒΡ€Π°Ρ†ΠΈΠΈ, ΠΏΠ΅Ρ€Π΅Π·Π°ΠΏΡƒΡΡ‚ΠΈΡ‚ΡŒ PostgreSQL, ΡƒΡΡ‚Π°Π½ΠΎΠ²ΠΈΡ‚ΡŒ ΠΌΠΎΠ΄ΡƒΠ»ΡŒ (ΠΎΠ΄Π½ΠΎΠΉ ΠΊΠΎΠΌΠ°Π½Π΄ΠΎΠΉ) ΠΈ дальшС Ρƒ нас появится Π½ΠΎΠ²ΠΎΠ΅ прСдставлСниС.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

CΡ€Π΅Π΄Π½Π΅Π΅ врСмя запроса Π² милисСкундах
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;

Π‘Π°ΠΌΡ‹Π΅ Π°ΠΊΡ‚ΠΈΠ²Π½ΠΎ ΠΏΠΈΡˆΡƒΡ‰ΠΈΠ΅ (Π² shared_buffers) запросы
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;

Π§Ρ‚ΠΎ ΠΌΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ ΠΎΡ‚Ρ‚ΡƒΠ΄Π° Π²Π·ΡΡ‚ΡŒ? Если Π³ΠΎΠ²ΠΎΡ€ΠΈΡ‚ΡŒ ΠΎ простых Π²Π΅Ρ‰Π°Ρ…, ΠΌΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ Π²Π·ΡΡ‚ΡŒ срСднСС врСмя выполнСния запроса. ВрСмя растСт, Π·Π½Π°Ρ‡ΠΈΡ‚, Ρƒ нас PostgreSQL ΠΎΡ‚Π²Π΅Ρ‡Π°Π΅Ρ‚ ΠΌΠ΅Π΄Π»Π΅Π½Π½ΠΎ ΠΈ Π½ΡƒΠΆΠ½ΠΎ Ρ‡Ρ‚ΠΎ-Ρ‚ΠΎ ΠΏΡ€Π΅Π΄ΠΏΡ€ΠΈΠ½ΠΈΠΌΠ°Ρ‚ΡŒ.

МоТСм ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ самыС Π°ΠΊΡ‚ΠΈΠ²Π½Ρ‹Π΅ ΠΏΠΈΡˆΡƒΡ‰ΠΈΠ΅ Ρ‚Ρ€Π°Π½Π·Π°ΠΊΡ†ΠΈΠΈ Π² Π±Π°Π·Π΅ Π΄Π°Π½Π½Ρ‹Ρ…, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ ΠΌΠ΅Π½ΡΡŽΡ‚ Π΄Π°Π½Π½Ρ‹Π΅ Π² shared buffers. ΠŸΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ, ΠΊΡ‚ΠΎ Ρƒ нас Ρ‚Π°ΠΌ обновляСт ΠΈΠ»ΠΈ удаляСт Π΄Π°Π½Π½Ρ‹Π΅.

И ΠΌΠΎΠΆΠ΅ΠΌ просто ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ Ρ€Π°Π·Π½ΡƒΡŽ статистику ΠΏΠΎ этим запросам.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

https://github.com/dataegret/pg-utils/blob/master/sql/global_reports/query_stat_total.sql

ΠœΡ‹ pg_stat_statements ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅ΠΌ для построСния ΠΎΡ‚Ρ‡Π΅Ρ‚ΠΎΠ². Π Π°Π· Π² сутки сбрасываСм статистику. НакапливаСм Π΅Π΅. ΠŸΠ΅Ρ€Π΅Π΄ сбросом статистики Π² ΡΠ»Π΅Π΄ΡƒΡŽΡ‰ΠΈΠΉ Ρ€Π°Π·, строим ΠΎΡ‚Ρ‡Π΅Ρ‚. Π’ΠΎΡ‚ ссылка Π½Π° ΠΎΡ‚Ρ‡Π΅Ρ‚. Π’Ρ‹ ΠΌΠΎΠΆΠ΅Ρ‚Π΅ Π΅Π³ΠΎ ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Π§Ρ‚ΠΎ ΠΌΡ‹ Π΄Π΅Π»Π°Π΅ΠΌ? ΠœΡ‹ подсчитываСм ΠΎΠ±Ρ‰ΡƒΡŽ статистику ΠΏΠΎ всСм запросам. Π—Π°Ρ‚Π΅ΠΌ для ΠΊΠ°ΠΆΠ΄ΠΎΠ³ΠΎ запроса ΠΌΡ‹ считаСм Π΅Π³ΠΎ ΠΈΠ½Π΄ΠΈΠ²ΠΈΠ΄ΡƒΠ°Π»ΡŒΠ½Ρ‹ΠΉ Π²ΠΊΠ»Π°Π΄ Π² эту ΠΎΠ±Ρ‰ΡƒΡŽ статистику.

И Ρ‡Ρ‚ΠΎ ΠΌΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ? ΠœΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ суммарноС врСмя выполнСния всСх запросов ΠΊΠΎΠ½ΠΊΡ€Π΅Ρ‚Π½ΠΎΠ³ΠΎ Ρ‚ΠΈΠΏΠ° Π½Π° Ρ„ΠΎΠ½Π΅ всСх ΠΎΡΡ‚Π°Π»ΡŒΠ½Ρ‹Ρ… запросов. МоТСм ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ использованиС рСсурсов процСссора ΠΈ Π²Π²ΠΎΠ΄Π°-Π²Ρ‹Π²ΠΎΠ΄Π° ΠΎΡ‚Π½ΠΎΡΠΈΡ‚Π΅Π»ΡŒΠ½ΠΎ ΠΎΠ±Ρ‰Π΅ΠΉ ΠΊΠ°Ρ€Ρ‚ΠΈΠ½Ρ‹. И ΡƒΠΆΠ΅ ΠΎΠΏΡ‚ΠΈΠΌΠΈΠ·ΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ эти запросы. ΠœΡ‹ строим Ρ‚ΠΎΠΏ запросов ΠΏΠΎ этому ΠΎΡ‚Ρ‡Π΅Ρ‚Ρƒ ΠΈ ΡƒΠΆΠ΅ ΠΏΠΎΠ»ΡƒΡ‡Π°Π΅ΠΌ ΠΏΠΈΡ‰Ρƒ для Ρ€Π°Π·ΠΌΡ‹ΡˆΠ»Π΅Π½ΠΈΡ, Ρ‡Ρ‚ΠΎ ΠΎΠΏΡ‚ΠΈΠΌΠΈΠ·ΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Π§Ρ‚ΠΎ Ρƒ нас ΠΎΡΡ‚Π°Π»ΠΎΡΡŒ Π·Π° ΠΊΠ°Π΄Ρ€ΠΎΠΌ? ΠžΡΡ‚Π°Π»ΠΎΡΡŒ Π΅Ρ‰Π΅ нСсколько прСдставлСний, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ я Π½Π΅ стал Ρ€Π°ΡΡΠΌΠ°Ρ‚Ρ€ΠΈΠ²Π°Ρ‚ΡŒ, ΠΏΠΎΡ‚ΠΎΠΌΡƒ Ρ‡Ρ‚ΠΎ врСмя ΠΎΠ³Ρ€Π°Π½ΠΈΡ‡Π΅Π½ΠΎ.

Π•ΡΡ‚ΡŒ pgstattuple – это Ρ‚ΠΎΠΆΠ΅ Π΄ΠΎΠΏΠΎΠ»Π½ΠΈΡ‚Π΅Π»ΡŒΠ½Ρ‹ΠΉ ΠΌΠΎΠ΄ΡƒΠ»ΡŒ ΠΈΠ· стандартного ΠΏΠ°ΠΊΠ΅Ρ‚Π° contribs. Он позволяСт ΠΎΡ†Π΅Π½ΠΈΡ‚ΡŒ bloat Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹, Ρ‚.Π½. Ρ„Ρ€Π°Π³ΠΌΠ΅Π½Ρ‚Π°Ρ†ΠΈΡŽ Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹. И Ссли фрагмСнтация большая, Π½ΡƒΠΆΠ½ΠΎ Π΅Π΅ ΡƒΠ±ΠΈΡ€Π°Ρ‚ΡŒ, ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ Ρ€Π°Π·Π½Ρ‹Π΅ инструмСнты. И функция pgstattuple Ρ€Π°Π±ΠΎΡ‚Π°Π΅Ρ‚ Π΄ΠΎΠ»Π³ΠΎ. И Ρ‡Π΅ΠΌ большС Ρ‚Π°Π±Π»ΠΈΡ†, Ρ‚Π΅ΠΌ дольшС ΠΎΠ½Π° Π±ΡƒΠ΄Π΅Ρ‚ Ρ€Π°Π±ΠΎΡ‚Π°Ρ‚ΡŒ.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Π‘Π»Π΅Π΄ΡƒΡŽΡ‰ΠΈΠΉ contrib – это pg_buffercache. Он позволяСт ΠΏΡ€ΠΎΠ²ΠΎΠ΄ΠΈΡ‚ΡŒ ΠΈΠ½ΡΠΏΠ΅ΠΊΡ†ΠΈΡŽ shared buffers: насколько интСнсивно ΠΈ ΠΏΠΎΠ΄ ΠΊΠ°ΠΊΠΈΠ΅ Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹ ΡƒΡ‚ΠΈΠ»ΠΈΠ·ΠΈΡ€ΡƒΡŽΡ‚ΡΡ страницы Π±ΡƒΡ„Π΅Ρ€Π°. И просто позволяСт Π·Π°Π³Π»ΡΠ½ΡƒΡ‚ΡŒ Π² shared buffers ΠΈ ΠΎΡ†Π΅Π½ΠΈΡ‚ΡŒ происходящСС Ρ‚Π°ΠΌ.

Π‘Π»Π΅Π΄ΡƒΡŽΡ‰ΠΈΠΉ ΠΌΠΎΠ΄ΡƒΠ»ΡŒ – это pgfincore. Он позволяСт ΠΏΡ€ΠΎΠ²ΠΎΠ΄ΠΈΡ‚ΡŒ Π½ΠΈΠ·ΠΊΠΎΡƒΡ€ΠΎΠ²Π½Π΅Π²Ρ‹Π΅ ΠΎΠΏΠ΅Ρ€Π°Ρ†ΠΈΠΈ с Ρ‚Π°Π±Π»ΠΈΡ†Π°ΠΌΠΈ Ρ‡Π΅Ρ€Π΅Π· систСмный Π²Ρ‹Π·ΠΎΠ² mincore(), Ρ‚. Π΅. ΠΎΠ½ позволяСт Π·Π°Π³Ρ€ΡƒΠ·ΠΈΡ‚ΡŒ Ρ‚Π°Π±Π»ΠΈΡ†Ρƒ Π² ΡˆΠ°Ρ€Π΅Π΄Π½Ρ‹Π΅ Π±ΡƒΡ„Π΅Ρ€Π°, Π»ΠΈΠ±ΠΎ Π΅Π΅ Π²Ρ‹Π³Ρ€ΡƒΠ·ΠΈΡ‚ΡŒ. И позволяСт ΠΏΠΎΠΌΠΈΠΌΠΎ ΠΏΡ€ΠΎΡ‡Π΅Π³ΠΎ ΠΏΡ€ΠΎΠ²ΠΎΠ΄ΠΈΡ‚ΡŒ ΠΈΠ½ΡΠΏΠ΅ΠΊΡ†ΠΈΡŽ страничного кэша ΠΎΠΏΠ΅Ρ€Π°Ρ†ΠΈΠΎΠ½Π½ΠΎΠΉ систСмы, Ρ‚. Π΅. Π² ΠΊΠ°ΠΊΠΎΠΌ объСмС Ρƒ нас Ρ‚Π°Π±Π»ΠΈΡ†Π° Π·Π°Π½ΠΈΠΌΠ°Π΅ΠΌ Π² page cache, Π² shared buffers ΠΈ просто позволяСт ΠΎΡ†Π΅Π½ΠΈΡ‚ΡŒ Π·Π°Π³Ρ€ΡƒΠΆΠ΅Π½Π½ΠΎΡΡ‚ΡŒ Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹.

Π‘Π»Π΅Π΄ΡƒΡŽΡ‰ΠΈΠΉ ΠΌΠΎΠ΄ΡƒΠ»ΡŒ – pg_stat_kcache. Он Ρ‚Π°ΠΊΠΆΠ΅ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅Ρ‚ систСмный Π²Ρ‹Π·ΠΎΠ² getrusage(). И выполняСт Π΅Π³ΠΎ ΠΏΠ΅Ρ€Π΅Π΄ ΠΈ послС выполнСния запроса. И Π² ΠΏΠΎΠ»ΡƒΡ‡Π΅Π½Π½ΠΎΠΉ статистикС позволяСт ΠΎΡ†Π΅Π½ΠΈΡ‚ΡŒ, сколько Ρƒ нас запрос Π·Π°Ρ‚Ρ€Π°Ρ‚ΠΈΠ» Π½Π° Π²Ρ‹ΠΏΠΎΠ»Π½Π΅Π½ΠΈΠ΅ дискового Π²Π²ΠΎΠ΄Π°-Π²Ρ‹Π²ΠΎΠ΄Π°, Ρ‚. Π΅. ΠΎΠΏΠ΅Ρ€Π°Ρ†ΠΈΠΈ с Ρ„Π°ΠΉΠ»ΠΎΠ²ΠΎΠΉ систСмой ΠΈ смотрит использованиС процСссора. Однако ΠΌΠΎΠ΄ΡƒΠ»ΡŒ ΠΌΠΎΠ»ΠΎΠ΄ΠΎΠΉ (ΠΊΡ…Π΅-ΠΊΡ…Π΅) ΠΈ для своСй Ρ€Π°Π±ΠΎΡ‚Ρ‹ ΠΎΠ½ Ρ‚Ρ€Π΅Π±ΡƒΠ΅Ρ‚ PostgreSQL 9.4 ΠΈ pg_stat_statements, ΠΎ ΠΊΠΎΡ‚ΠΎΡ€ΠΎΠΌ я Π³ΠΎΠ²ΠΎΡ€ΠΈΠ» Ρ€Π°Π½Π΅Π΅.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

  • Π£ΠΌΠ΅Π½ΠΈΠ΅ ΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒΡΡ статистикой – ΠΏΠΎΠ»Π΅Π·Π½ΠΎ. Π’Π°ΠΌ Π½Π΅ Π½ΡƒΠΆΠ½Ρ‹ сторонниС ΠΏΡ€ΠΎΠ³Ρ€Π°ΠΌΠΌΡ‹. Π’Ρ‹ ΠΌΠΎΠΆΠ΅Ρ‚Π΅ сами Π·Π°Π³Π»ΡΠ½ΡƒΡ‚ΡŒ, ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ, Ρ‡Ρ‚ΠΎ-Ρ‚ΠΎ ΡΠ΄Π΅Π»Π°Ρ‚ΡŒ, Π²Ρ‹ΠΏΠΎΠ»Π½ΠΈΡ‚ΡŒ.

  • ΠŸΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒΡΡ статистикой нСслоТно, это ΠΎΠ±Ρ‹Ρ‡Π½Ρ‹ΠΉ SQL. Π’Ρ‹ собрали запрос, составили, ΠΎΡ‚ΠΏΡ€Π°Π²ΠΈΠ»ΠΈ, посмотрСли.

  • Бтатистика ΠΏΠΎΠΌΠΎΠ³Π°Π΅Ρ‚ ΠΎΡ‚Π²Π΅Ρ‚ΠΈΡ‚ΡŒ Π½Π° вопросы. Если Ρƒ вас Π²ΠΎΠ·Π½ΠΈΠΊΠ°ΡŽΡ‚ вопросы, Π²Ρ‹ ΠΎΠ±Ρ€Π°Ρ‰Π°Π΅Ρ‚Π΅ΡΡŒ ΠΊ статистикС – смотритС, Π΄Π΅Π»Π°Π΅Ρ‚Π΅ Π²Ρ‹Π²ΠΎΠ΄Ρ‹, Π°Π½Π°Π»ΠΈΠ·ΠΈΡ€ΡƒΠ΅Ρ‚Π΅ Ρ€Π΅Π·ΡƒΠ»ΡŒΡ‚Π°Ρ‚Ρ‹.

  • И экспСримСнтируйтС. Запросов ΠΌΠ½ΠΎΠ³ΠΎ, Π΄Π°Π½Π½Ρ‹Ρ… ΠΌΠ½ΠΎΠ³ΠΎ. ВсСгда ΠΌΠΎΠΆΠ½ΠΎ ΠΎΠΏΡ‚ΠΈΠΌΠΈΠ·ΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ ΠΊΠ°ΠΊΠΎΠΉ-Ρ‚ΠΎ ΡƒΠΆΠ΅ ΡΡƒΡ‰Π΅ΡΡ‚Π²ΡƒΡŽΡ‰ΠΈΠΉ запрос. МоТно ΡΠ΄Π΅Π»Π°Ρ‚ΡŒ свою Π²Π΅Ρ€ΡΠΈΡŽ запроса, которая ΠΏΠΎΠ΄Ρ…ΠΎΠ΄ΠΈΡ‚ Π²Π°ΠΌ большС, Ρ‡Π΅ΠΌ ΠΎΡ€ΠΈΠ³ΠΈΠ½Π°Π» ΠΈ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ Π΅Π³ΠΎ.

Deep dive into PostgreSQL internal statistics. АлСксСй ЛСсовский

Бсылки

Π“ΠΎΠ΄Π½Ρ‹Π΅ ссылки, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ Π²ΡΡ‚Ρ€Π΅Ρ‡Π°Π»ΠΈΡΡŒ Π² ΡΡ‚Π°Ρ‚ΡŒΠ΅, ΠΏΠΎ ΠΌΠ°Ρ‚Π΅Ρ€ΠΈΠ°Π»Π°ΠΌ ΠΊΠΎΡ‚ΠΎΡ€ΠΎΠΉ, Π±Ρ‹Π»ΠΈ Π² Π΄ΠΎΠΊΠ»Π°Π΄Π΅.

Автор пиши Π΅Ρ‰Ρ‘
https://dataegret.com/news-blog (eng)

The Statistics Collector
https://www.postgresql.org/docs/current/monitoring-stats.html

System Administration Functions
https://www.postgresql.org/docs/current/functions-admin.html

Contrib modules
https://www.postgresql.org/docs/current/pgstatstatements.html
https://www.postgresql.org/docs/current/pgstattuple.html
https://www.postgresql.org/docs/current/pgbuffercache.html
https://github.com/klando/pgfincore
https://github.com/dalibo/pg_stat_kcache

SQL utils and sql code examples
https://github.com/dataegret/pg-utils

ВсСм спасибо Π·Π° Π²Π½ΠΈΠΌΠ°Π½ΠΈΠ΅!

Π˜ΡΡ‚ΠΎΡ‡Π½ΠΈΠΊ: habr.com