Сәлем.
Менің атым Ваня, мен Java әзірлеушісімін. Мен PostgreSQL-пен көп жұмыс істеймін - дерекқорды орнату, құрылымды, өнімділікті оңтайландыру және демалыс күндері аздап DBA ойнау.
Жақында мен микросервистеріміздегі бірнеше дерекқорды реттеп, java кітапханасын жаздым
Жауапкершіліктен бас тарту
Мен жұмыс істейтін PostgreSQL негізгі нұсқасы - 10. Мен қолданатын барлық SQL сұраулары да 11 нұсқада тексерілген. Қолдау көрсетілетін ең аз нұсқасы - 9.6.
тарихын
Мұның бәрі бір жыл бұрын мен үшін оғаш болған жағдайдан басталды: индексті бәсекелестікпен жасау қателікпен аяқталды. Индекстің өзі, әдеттегідей, дерекқорда жарамсыз күйде қалды. Журналды талдау жетіспеушілікті көрсетті
Бірінші мәселе - әдепкі конфигурация
Барлығы кофеқайнатқышта жұмыс істей алатын Postgres туралы метафорадан әбден шаршаған шығар, бірақ... әдепкі конфигурация шынымен де бірқатар сұрақтар тудырады. Кем дегенде, назар аударған жөн техникалық_жұмыс_жады, temp_file_limit, мәлімдеме_күту уақыты и lock_timeout.
Біздің жағдайда техникалық_жұмыс_жады әдепкі 64 МБ болды және temp_file_limit шамамен 2 ГБ нәрсе - бізде үлкен үстелде индекс жасау үшін жеткілікті жад болмады.
Сондықтан, в pg-индекс-денсаулық Мен серия жинадым
Екінші мәселе – қайталанатын индекстер
Біздің дерекқорлар SSD дискілерінде тұрады және біз пайдаланамыз HA-бірнеше деректер орталықтары бар конфигурация, басты хост және n- көшірмелердің саны. Дискілік кеңістік біз үшін өте құнды ресурс болып табылады; бұл өнімділік пен процессорды тұтынудан кем емес маңызды. Сондықтан, бір жағынан, бізге жылдам оқу үшін индекстер қажет, ал екінші жағынан, дерекқорда қажет емес индекстерді көргіміз келмейді, өйткені олар бос орынды жеп, деректерді жаңартуды баяулатады.
Енді барлығын қалпына келтірді
Үшінші есеп – қиылысатын индекстер
Көптеген жаңадан әзірлеушілер индекстерді бір бағанда жасайды. Бірте-бірте бұл бизнестің дәмін татып, адамдар сұрауларын оңтайландыра бастайды және бірнеше бағандарды қамтитын күрделі индекстерді қосады. Бағандардағы индекстер осылай пайда болады A, A + B, A + B + C және т.б. Бұл индекстердің алғашқы екеуін қауіпсіз түрде шығаруға болады, өйткені олар үшіншінің префиксі болып табылады. Бұл сонымен қатар дискілік кеңістікті үнемдейді және бұл үшін диагностика бар
Төртінші мәселе – индекстері жоқ сыртқы кілттер
Postgres сізге қосалқы индексті көрсетпей-ақ сыртқы кілт шектеулерін жасауға мүмкіндік береді. Көптеген жағдайларда бұл проблема емес, тіпті өзін көрсетпеуі де мүмкін... Әзірге...
Бізде де солай болды: белгілі бір уақытта кестеге сәйкес жұмыс істейтін және сынақ тапсырыстарының дерекқорын тазартатын жұмыс басты хост арқылы бізге «қоса» бастады. CPU және IO ысырап болды, сұраулар баяулады және күту уақыты аяқталды, қызмет бес жүз болды. Жылдам талдау
delete from <table> where id in (…)
Бұл жағдайда, әрине, мақсатты кестеде id бойынша индекс болды және шартқа сәйкес өте аз жазбалар жойылды. Барлығы жұмыс істеуі керек сияқты көрінді, бірақ, өкінішке орай, олай болмады.
Керемет адам көмекке келді талдауды түсіндіру және мақсатты кестедегі жазбаларды жоюдан басқа, сілтеме тұтастығын тексеру бар екенін және қатысты кестелердің бірінде бұл тексеру сәтсіз аяқталатынын айтты. дәйекті сканерлеу сәйкес көрсеткіштің болмауына байланысты. Осылайша диагностика дүниеге келді
Бесінші мәселе – индекстердегі нөлдік мән
Әдепкі бойынша, Postgres btree индекстеріндегі нөлдік мәндерді қамтиды, бірақ олар әдетте қажет емес. Сондықтан мен бұл нөлдерді (диагностика where <A> is not null
. Осылайша мен индекстеріміздің бірінің өлшемін 1877 МБ-тан 16 КБ-қа дейін азайта алдым. Ал қызметтердің бірінде деректер қорының көлемі индекстерден нөлдік мәндерді алып тастауға байланысты жалпы алғанда 16%-ға (абсолюттік сандарда 4.3 ГБ-қа) азайды. Өте қарапайым өзгертулермен дискілік кеңістікте орасан зор үнемдеу. 🙂
Алтыншы мәселе – бастапқы кілттердің болмауы
Механизмнің табиғатына байланысты
Бір күні керемет көші-қон үлкен және белсенді түрде пайдаланылатын кестедегі барлық жазбаларды алып, жаңартты. Біз күтпеген жерден кесте өлшеміне +100 ГБ алдық. Бұл өте ұят болды, бірақ біздің келеңсіздіктер мұнымен бітпеді. Осы үстелдегі автовакуум 15 сағаттан кейін аяқталғаннан кейін физикалық орналасу қайтып келмейтіні белгілі болды. Біз қызметті тоқтатып, ВАКУМДЫ ТОЛЫҚ жасай алмадық, сондықтан біз пайдалануды шештік
Кітапхана нұсқасында 0.1.5 Кестелер мен индекстерден деректерді жинау және оларға дер кезінде жауап беру мүмкіндігі қосылды.
Жеті және сегізінші есептер – жеткіліксіз көрсеткіштер және пайдаланылмаған индекстер
Келесі екі диагностика:
Мен жазғанымдай, біз бірнеше көшірмелері бар конфигурацияны қолданамыз және әртүрлі хосттардағы оқу жүктемесі түбегейлі ерекшеленеді. Нәтижесінде кейбір хосттардағы кейбір кестелер мен индекстер іс жүзінде пайдаланылмайтыны белгілі болды, ал талдау үшін кластердегі барлық хосттардан статистика жинау керек.
Бұл тәсіл ешқашан пайдаланылмаған индекстерді жою, сондай-ақ сирек қолданылатын кестелерге жетіспейтін индекстерді қосу арқылы бірнеше ондаған гигабайтты үнемдеуге мүмкіндік берді.
Қорытынды ретінде
Әрине, барлық дерлік диагностика үшін конфигурациялауға болады
Кейбір диагностикалар дерекқорды көшірулерді таратқаннан кейін бірден функционалдық сынақтарда орындалуы мүмкін. Бұл менің кітапханамның ең күшті мүмкіндіктерінің бірі болуы мүмкін. Қолдану мысалын мына жерден табуға болады
Тек нақты дерекқорда пайдаланылмаған немесе жетіспейтін индекстерді, сондай-ақ bloat үшін тексерулерді орындау мағынасы бар. Жиналған мәндерді жазуға болады
Мен бұған шынымен үміттенемін pg-индекс-денсаулық пайдалы және сұранысқа ие болады. Сондай-ақ табылған мәселелер туралы хабарлау және жаңа диагностика ұсыну арқылы кітапхананың дамуына үлес қоса аласыз.
Ақпарат көзі: www.habr.com