Мен Владимир Ситниковтың 2016 жылдың басындағы «PostgreSQL және JDBC барлық шырынды сығып жатыр» баяндамасының транскрипциясын оқуды ұсынамын.
Қайырлы күн! Менің атым Владимир Ситников. Мен NetCracker-те 10 жыл жұмыс істеймін. Ал мен көбінесе өнімділікпен айналысамын. Java-ға қатысты барлық нәрсе, SQL-ге қатысты барлық нәрсе маған ұнайды.
Ал бүгін мен PostgreSQL-ті дерекқор сервері ретінде пайдалана бастағанда компанияда не кездестірдік. Ал біз көбіне Java-мен жұмыс істейміз. Бірақ мен бүгін айтайын дегенім тек Java туралы емес. Тәжірибе көрсеткендей, бұл басқа тілдерде де кездеседі.
Біз сөйлесеміз:
- мәліметтерді іріктеу туралы.
- Деректерді сақтау туралы.
- Сондай-ақ өнімділік туралы.
- Ал онда көмілген су асты тырмалары туралы.
Қарапайым сұрақтан бастайық. Негізгі кілт негізінде кестеден бір жолды таңдаймыз.
Деректер базасы бір хостта орналасқан. Ал бұл шаруаның барлығы 20 миллисекундты алады.
Бұл 20 миллисекунд өте көп. Егер сізде осындай 100 сұрау болса, онда сіз секундына осы сұрауларды айналдыруға уақыт жұмсайсыз, яғни біз уақытты босқа өткіземіз.
Біз мұны ұнатпаймыз және бұл үшін база бізге не ұсынатынын қарастырамыз. Дерекқор бізге сұрауларды орындаудың екі нұсқасын ұсынады.
Бірінші нұсқа - қарапайым сұрау. Оның несі жақсы? Біз оны алып жібереміз, одан басқа ештеңе жоқ.
Дерекқорда күрделірек, бірақ функционалдырақ кеңейтілген сұрау бар. Талдау, орындау, айнымалы байланыстыру және т.б. сұрауды бөлек жіберуге болады.
Өте кеңейтілген сұрау - біз ағымдағы есепте қарастырмайтын нәрсе. Біз, мүмкін, дерекқордан бірдеңе алғымыз келеді және қандай да бір түрде жасалған тілектер тізімі бар, яғни бұл біз қалайтынымыз, бірақ қазір және келесі жылы бұл мүмкін емес. Сондықтан біз оны жай ғана жазып алдық және біз негізгі адамдарды шайқаймыз.
Біз жасай алатын нәрсе - қарапайым сұрау және кеңейтілген сұрау.
Әрбір тәсілдің ерекшелігі неде?
Қарапайым сұрау бір реттік орындау үшін жақсы. Бір рет орындалып, ұмытылады. Мәселе мынада, ол екілік деректер пішімін қолдамайды, яғни ол кейбір жоғары өнімді жүйелер үшін жарамсыз.
Кеңейтілген сұрау – талдауға уақытты үнемдеуге мүмкіндік береді. Міне, біз осылай жасадық және қолдана бастадық. Бұл бізге шынымен көмектесті. Талдау кезінде үнемдеу ғана емес. Деректерді тасымалдауда үнемдеу бар. Деректерді екілік форматта тасымалдау әлдеқайда тиімді.
Жаттығуға көшейік. Әдеттегі қолданба осылай көрінеді. Бұл Java болуы мүмкін және т.б.
Біз мәлімдеме жасадық. Пәрменді орындады. Жақын құрылды. Бұл жерде қателік қайда? Мәселе неде? Мәселе жоқ. Бұл барлық кітаптарда айтылған. Осылай жазу керек. Максималды өнімділікті қаласаңыз, осылай жазыңыз.
Бірақ тәжірибе көрсеткендей, бұл жұмыс істемейді. Неліктен? Өйткені бізде «жақын» әдіс бар. Біз мұны істегенде, дерекқор тұрғысынан бұл дерекқормен жұмыс істейтін темекі шегуге ұқсайтыны белгілі болды. Біз «PARSE EXECUTE DEALLOCATE» дедік.
Неліктен бұл қосымша мәлімдемелерді жасау және түсіру? Олар ешкімге керек емес. Бірақ әдетте PreparedStatements ішінде болатын нәрсе мынада, біз оларды жапқанда, олар дерекқордағы барлығын жабады. Бұл біздің қалағанымыз емес.
Біз дені сау адамдар сияқты базамен жұмыс жасағымыз келеді. Арызды бір рет алып, дайындадық, кейін талай рет орындадық. Шын мәнінде, көптеген рет - бұл қолданбалардың бүкіл өмірінде бір рет - олар талданған. Әртүрлі REST-де бірдей мәлімдеме идентификаторын қолданамыз. Бұл біздің мақсатымыз.
Бұған қалай қол жеткізе аламыз?
Бұл өте қарапайым - мәлімдемелерді жабудың қажеті жоқ. Біз оны былай жазамыз: «дайындау» «орындау».
Егер біз осындай нәрсені іске қоссақ, бір жерде бір нәрсе толып кететіні анық. Егер түсініксіз болса, оны пайдаланып көруге болады. Осы қарапайым әдісті қолданатын эталонды жазайық. Мәлімдеме жасаңыз. Біз оны драйвердің кейбір нұсқасында іске қосамыз және оның барлық жадты жоғалтуымен тез бұзылатынын анықтаймыз.
Мұндай қателер оңай түзетілетіні анық. Мен олар туралы айтпаймын. Бірақ мен жаңа нұсқаның әлдеқайда жылдам жұмыс істейтінін айтайын. Әдіс ақымақ, бірақ бәрібір.
Қалай дұрыс жұмыс істеу керек? Бұл үшін не істеуіміз керек?
Іс жүзінде қолданбалар әрқашан мәлімдемелерді жабады. Барлық кітаптарда оны жабу керек дейді, әйтпесе жады ағып кетеді.
Ал PostgreSQL сұрауларды кэштеуді білмейді. Әрбір сеанс осы кэшті өзі үшін жасауы қажет.
Біз де талдауға уақыт жоғалтқымыз келмейді.
Ал бізде әдеттегідей екі нұсқа бар.
Бірінші нұсқа - біз оны алып, бәрін PgSQL-ге орап алайық деп айтамыз. Онда кэш бар. Ол бәрін кэштейді. Бұл керемет болады. Біз мұны көрдік. Бізде 100500 өтініш бар. Жұмыс істемейді. Біз сұрауларды қолмен процедураға айналдыруға келіспейміз. Жоқ Жоқ.
Бізде екінші нұсқа бар - оны алыңыз және оны өзіміз кесіңіз. Біз көздерді ашып, кесуді бастаймыз. Көрдік, көрдік. Мұны істеу соншалықты қиын емес екені белгілі болды.
Бұл 2015 жылдың тамызында пайда болды. Енді заманауи нұсқасы бар. Және бәрі тамаша. Ол соншалықты жақсы жұмыс істейді, біз қолданбада ештеңені өзгертпейміз. Біз тіпті PgSQL бағытында ойлауды доғардық, яғни бұл барлық үстеме шығындарды нөлге дейін азайтуға жеткілікті болды.
Сәйкесінше, әрбір бір реттік сұрау бойынша дерекқордағы жадты босқа кетірмеу үшін серверде дайындалған мәлімдемелер 5-ші орындалуда белсендіріледі.
Сіз сұрай аласыз - сандар қайда? Сіз не алып жатырсыз? Бұл жерде мен сандарды бермеймін, өйткені әр сұраныстың өзіндік ерекшелігі бар.
Біздің сұрауларымыз соншалық, OLTP сұрауларын талдауға шамамен 20 миллисекунд жұмсадық. Орындау үшін 0,5 миллисекунд, талдау үшін 20 миллисекунд болды. Сұраныс – 10 КБ мәтін, 170 жол жоспар. Бұл OLTP сұрауы. Ол 1, 5, 10, кейде одан да көп жолдарды сұрайды.
Бірақ біз 20 миллисекундты босқа өткізгіміз келмеді. Біз оны 0-ге дейін азайттық. Бәрі тамаша.
Сіз бұл жерден не ала аласыз? Егер сізде Java болса, онда сіз драйвердің заманауи нұсқасын алып, қуанасыз.
Егер сіз басқа тілде сөйлесеңіз, ойланыңыз - мүмкін бұл сізге керек пе? Өйткені соңғы тіл тұрғысынан, мысалы, PL 8 немесе сізде LibPQ болса, онда сіз орындауға емес, талдауға уақыт жұмсайтыныңыз анық емес және бұл тексеруге тұрарлық. Қалай? Барлығы тегін.
Қателер мен кейбір ерекшеліктерді қоспағанда. Ал біз қазір олар туралы айтатын боламыз. Оның басым бөлігі өнеркәсіптік археология туралы, нені тапқанымыз, нені кездестіргеніміз туралы болмақ.
Егер сұрау динамикалық түрде жасалса. Болады. Біреу жолдарды бір-біріне жабыстырады, нәтижесінде SQL сұрауы пайда болады.
Ол неге жаман? Бұл жаман, өйткені біз әр жолы әртүрлі жолдармен аяқталамыз.
Және бұл әртүрлі жолдың хэшкодын қайта оқу керек. Бұл шын мәнінде CPU тапсырмасы - бар хэште ұзақ сұрау мәтінін табу оңай емес. Сондықтан, қорытынды қарапайым - сұраныстарды жасамаңыз. Оларды бір айнымалыда сақтаңыз. Және қуаныңыз.
Келесі мәселе. Деректер түрлері маңызды. Қандай NULL бар екені маңызды емес, қандай да бір түрі болсын деп айтатын ORM бар. Егер Int болса, онда setInt дейміз. Ал егер NULL болса, онда ол әрқашан VARCHAR болсын. Ал соңында NULL дегеннің қандай айырмашылығы бар? Дерекқордың өзі бәрін түсінеді. Және бұл сурет жұмыс істемейді.
Іс жүзінде дерекқорға мүлдем мән бермейді. Егер сіз бірінші рет бұл сан деп, екінші рет бұл VARCHAR деп айтқан болсаңыз, онда Сервер дайындаған мәлімдемелерді қайта пайдалану мүмкін емес. Ал бұл жағдайда біз өз мәлімдемемізді қайта жасауымыз керек.
Егер сіз бірдей сұрауды орындасаңыз, бағаныңыздағы деректер түрлерінің шатастырылмағанына көз жеткізіңіз. NULL мәніне назар аудару керек. Бұл PreparedStatements пайдалана бастағаннан кейін жиі кездесетін қате
Жарайды, қосылды. Мүмкін олар жүргізушіні алып кеткен шығар. Ал өнімділік төмендеді. Жағдай нашарлап кетті.
Бұл қалай болады? Бұл қате немесе мүмкіндік пе? Өкінішке орай, бұл қате немесе функция екенін түсіну мүмкін болмады. Бірақ бұл мәселені қайта шығарудың өте қарапайым сценарийі бар. Ол күтпеген жерден бізді қуып жіберді. Және ол бір кестеден тура мағынада іріктеуден тұрады. Бізге, әрине, мұндай өтініштер көбірек болды. Әдетте, олар екі немесе үш кестені қамтиды, бірақ мұндай ойнату сценарийі бар. Дерекқордан кез келген нұсқаны алып, оны ойнатыңыз.
Мәселе мынада, бізде екі баған бар, олардың әрқайсысы индекстелген. Бір NULL бағанында миллион жол бар. Ал екінші бағанда небәрі 20 жол бар. Шектелген айнымалыларсыз орындаған кезде бәрі жақсы жұмыс істейді.
Шектелген айнымалылармен орындауды бастасақ, яғни «?» командасын орындаймыз. немесе сұрауымыз үшін «$1», біз не аламыз?
Бірінші орындалу күтілгендей. Екіншісі сәл жылдамырақ. Бірдеңе кэштелді. Үшінші, төртінші, бесінші. Содан кейін жарылыс - және сол сияқты нәрсе. Ең сорақысы, бұл алтыншы өлім жазасына кесілгенде болады. Нақты орындау жоспарының не екенін түсіну үшін дәл алты өлім жазасын орындау қажет екенін кім білген?
Кім кінәлі? Не болды? Деректер базасында оңтайландыру бар. Және ол жалпы жағдай үшін оңтайландырылған сияқты. Және, тиісінше, бір сәттен бастап, ол жалпы жоспарға ауысады, өкінішке орай, басқаша болуы мүмкін. Бұл бірдей болуы мүмкін немесе басқаша болуы мүмкін. Және бұл мінез-құлыққа әкелетін қандай да бір шекті мән бар.
Сіз бұл туралы не істей аласыз? Бұл жерде, әрине, бірдеңені болжау қиынырақ. Біз қолданатын қарапайым шешім бар. Бұл +0, OFFSET 0. Сіз мұндай шешімдерді білесіз. Біз оны алып, сұрауға «+0» қосамыз және бәрі жақсы. Мен сізге кейінірек көрсетемін.
Және тағы бір нұсқа бар - жоспарларды мұқият қарастырыңыз. Әзірлеуші тек сұраныс жазып қана қоймай, сонымен қатар 6 рет «талдауды түсіндір» деуі керек. Егер ол 5 болса, ол жұмыс істемейді.
Ал үшінші нұсқа бар - pgsql-хакерлерге хат жазу. Мен жаздым, бірақ бұл қате немесе мүмкіндік пе екені белгісіз.
Бұл қате немесе мүмкіндік пе деп ойлап жатқанда, оны түзетейік. Өтінішімізді қабылдап, «+0» қосамыз. Барлығы жақсы. Екі таңба және оның қалай немесе не екенін ойлаудың қажеті жоқ. Өте оңай. Біз жай ғана дерекқорға осы бағандағы индексті пайдалануға тыйым салдық. Бізде «+0» бағанында индекс жоқ, міне, дерекқор индексті пайдаланбайды, бәрі жақсы.
Бұл 6 ережесін түсіндіреді. Енді ағымдағы нұсқаларда айнымалылар шектелген болса, оны 6 рет орындау керек. Егер сізде шектелген айнымалылар болмаса, біз мұны істейміз. Соңында дәл осы өтініш орындалмайды. Бұл қиын нәрсе емес.
Бұл қаншалықты мүмкін сияқты? Қате мұнда, қате бар. Шын мәнінде, қате барлық жерде.
Толығырақ қарастырайық. Мысалы, бізде екі схема бар. S кестесімен А схемасы және S кестесімен В диаграммасы. Сұраныс – кестеден деректерді таңдау. Бұл жағдайда бізде не болады? Бізде қате болады. Бізде жоғарыда айтылғандардың барлығы болады. Ереже - қате барлық жерде, бізде жоғарыда айтылғандардың барлығы болады.
Енді сұрақ: «Неге?» Егер бізде схема болса, кестені қайдан іздеу керектігін көрсететін "іздеу_жолы" айнымалысы бар екенін көрсететін құжаттама бар сияқты. Айнымалы бар сияқты.
Мәселе неде? Мәселе мынада, сервер дайындаған мәлімдемелер іздеу_жолын біреу өзгерте алады деп күдіктенбейді. Бұл мән дерекқор үшін тұрақты болып қала береді. Ал кейбір бөліктер жаңа мағыналарды қабылдамауы мүмкін.
Әрине, бұл сіз сынап жатқан нұсқаға байланысты. Кестелеріңіздің қаншалықты ерекшеленетініне байланысты. Ал 9.1 нұсқасы жай ғана ескі сұрауларды орындайды. Жаңа нұсқалар қатені анықтап, сізде қате бар екенін айтуы мүмкін.
Оны қалай емдеуге болады? Қарапайым рецепт бар - оны жасамаңыз. Қолданба жұмыс істеп тұрғанда search_path өзгерту қажет емес. Өзгертсеңіз, жаңа байланыс жасаған дұрыс.
Сіз талқылауға, яғни ашуға, талқылауға, қосуға болады. Мүмкін біз дерекқор әзірлеушілерін біреу мәнді өзгерткенде, дерекқор клиентке бұл туралы айтуы керек екеніне сендіре аламыз: «Міне, сіздің мәніңіз осы жерде жаңартылды. Мүмкін сізге мәлімдемелерді қалпына келтіріп, оларды қайта жасау керек пе?» Енді дерекқор жасырын әрекет етеді және мәлімдемелер ішіндегі бір жерде өзгергені туралы ешқандай хабар бермейді.
Мен тағы да баса айтайын - бұл Java-ға тән емес нәрсе. Біз PL/pgSQL-де бірдей нәрсені көреміз. Бірақ сол жерде қайта шығарылады.
Тағы да деректерді таңдауға тырысайық. Біз таңдаймыз және таңдаймыз. Бізде миллион жолдан тұратын кесте бар. Әрбір жол бір килобайт. Шамамен гигабайт деректер. Ал бізде Java машинасында 128 мегабайт жұмыс жады бар.
Біз, барлық кітаптарда ұсынылғандай, ағынды өңдеуді қолданамыз. Яғни, біз resultsSet ашамыз және сол жерден деректерді біртіндеп оқимыз. Бұл жұмыс істей ме? Ол жадтан түсіп қалады ма? Кішкене оқисың ба? Мәліметтер базасына сенейік, Postgres-ке сенейік. Біз сенбейміз. Біз OutOFMemory жоғаламыз ба? OutOfMemory тәжірибесін кім көрді? Одан кейін оны кім жөндеді? Біреу оны жөндеп алды.
Егер сізде миллион жол болса, сіз жай ғана таңдай алмайсыз. OFFSET/LIMIT қажет. Бұл опция үшін кім? Автокоммитпен ойнауды кім қолдайды?
Мұнда, әдеттегідей, ең күтпеген нұсқа дұрыс болып шығады. Егер сіз кенеттен autoCommit өшірсеңіз, ол көмектеседі. Неге бұлай? Бұл туралы ғылым білмейді.
Бірақ әдепкі бойынша Postgres дерекқорына қосылатын барлық клиенттер барлық деректерді алады. PgJDBC осыған қатысты ерекшелік емес, ол барлық жолдарды таңдайды.
FetchSize тақырыбының вариациясы бар, яғни бөлек мәлімдеме деңгейінде айта аласыз, бұл жерде деректерді 10, 50 арқылы таңдаңыз. Бірақ бұл автоматты түрде орындауды өшірмейінше жұмыс істемейді. AutoCommit өшірілді - ол жұмыс істей бастайды.
Бірақ кодты қарап шығу және setFetchSize параметрін барлық жерде орнату ыңғайсыз. Сондықтан біз бүкіл қосылым үшін әдепкі мәнді айтатын параметрді жасадық.
Біз осылай дедік. Параметр конфигурацияланды. Ал біз не алдық? Егер біз шағын сомаларды таңдасақ, мысалы, бір уақытта 10 жолды таңдасақ, онда бізде өте үлкен үстеме шығындар болады. Сондықтан бұл мәнді шамамен жүзге қою керек.
Ең дұрысы, әрине, сіз әлі де оны байтпен шектеуді үйренуіңіз керек, бірақ рецепт мынадай: defaultRowFetchSize-ді жүзден астам етіп орнатыңыз және бақытты болыңыз.
Деректерді енгізуге көшейік. Кірістіру оңайырақ, әртүрлі нұсқалар бар. Мысалы, INSERT, VALUES. Бұл жақсы нұсқа. «INSERT SELECT» деуге болады. Іс жүзінде бұл бірдей нәрсе. Өнімділікте ешқандай айырмашылық жоқ.
Кітаптарда Batch операторын орындау керек дейді, кітаптарда бірнеше жақшамен күрделірек пәрмендерді орындауға болатыны айтылады. Ал Postgres-тің тамаша мүмкіндігі бар - сіз КӨШІРУ жасай аласыз, яғни оны тезірек жасай аласыз.
Егер сіз оны өлшесеңіз, сіз қайтадан қызықты жаңалықтар жасай аласыз. Мұның қалай жұмыс істегенін қалаймыз? Біз қажетсіз пәрмендерді талдауды және орындамауды қалаймыз.
Іс жүзінде TCP бізге мұны істеуге мүмкіндік бермейді. Клиент сұрауды жіберумен бос емес болса, онда дерекқор бізге жауаптарды жіберу әрекеттерінде сұрауларды оқымайды. Ақырғы нәтиже клиент дерекқордың сұрауды оқуын күтеді, ал дерекқор клиенттің жауапты оқуын күтеді.
Сондықтан клиент мезгіл-мезгіл синхрондау пакетін жіберуге мәжбүр. Қосымша желілік өзара әрекеттесу, қосымша уақытты ысырап ету.
Ал біз оларды неғұрлым көп қоссақ, соғұрлым нашарлайды. Драйвер өте пессимистік және оларды жиі қосады, сызықтардың өлшеміне байланысты шамамен 200 жолға бір рет және т.б.
Сіз тек бір жолды түзетесіз және бәрі 10 есе жылдамдатады. Болады. Неліктен? Әдеттегідей, мұндай тұрақты бір жерде қолданылған. Ал «128» мәні топтаманы пайдаланбауды білдіреді.
Бұл ресми нұсқаға енбегені жақсы. Шығарылым басталғанға дейін табылды. Мен беретін мағыналардың барлығы заманауи нұсқаларға негізделген.
Оны киіп көрейік. InsertBatch қарапайым өлшейміз. InsertBatch-ті бірнеше рет өлшейміз, яғни бірдей нәрсе, бірақ көптеген мәндер бар. Күрделі қозғалыс. Мұны бәрі бірдей жасай алмайды, бірақ бұл өте қарапайым, КӨШІРУге қарағанда оңайырақ.
Сіз КӨШІРУ жасай аласыз.
Сіз мұны құрылымдарда жасай аласыз. Пайдаланушының әдепкі түрін жариялаңыз, массивті жіберіңіз және кестеге тікелей INSERT.
Егер сіз мына сілтемені ашсаңыз: pgjdbc/ubenchmsrk/InsertBatch.java, онда бұл код GitHub жүйесінде болады. Онда нақты қандай сұраулар жасалғанын көруге болады. Ештене етпейді.
Біз іске қостық. Біз түсінген бірінші нәрсе, партияны пайдаланбау мүмкін емес. Барлық пакеттік опциялар нөлге тең, яғни орындау уақыты бір реттік орындаумен салыстырғанда іс жүзінде нөлге тең.
Біз деректерді енгіземіз. Бұл өте қарапайым кесте. Үш баған. Ал біз мұнда не көріп тұрмыз? Біз бұл үш нұсқаның барлығы шамамен салыстырмалы екенін көреміз. Және COPY, әрине, жақсырақ.
Бұл біз бөліктерді енгізген кезде. Біз бір VALUES мәнін, екі VALUES мәнін, үш VALUES мәнін айтқан кезде немесе олардың 10 мәнін үтірмен бөліп көрсеттік. Бұл енді көлденең. 1, 2, 4, 128. Көгілдір түспен сызылған Batch Insert оның өзін әлдеқайда жақсы сезінетінін көруге болады. Яғни, бір уақытта біреуін енгізгенде немесе тіпті төртеуін бір уақытта енгізгенде, ол екі есе жақсы болады, өйткені біз VALUES ішіне аздап көбірек кіргіздік. EXECUTE операциялары азырақ.
Кішкентай көлемде COPY пайдалану өте келешегі жоқ. Алғашқы екеуіне де сурет салмадым. Олар көкке барады, яғни КӨШІРУ үшін осы жасыл сандар.
COPY кемінде жүз деректер жолы болған кезде қолданылуы керек. Бұл қосылымды ашудың үстеме шығыны үлкен. Шынымды айтсам, мен бұл бағытта қазбалаған жоқпын. Мен пакетті оңтайландырдым, бірақ КӨШІРУ емес.
Әрі қарай не істейміз? Біз оны қолданып көрдік. Біз не құрылымдарды, не бірнеше мағыналарды біріктіретін ақылды бактіні пайдалану керектігін түсінеміз.
Бүгінгі баяндамадан нені алып тастау керек?
- PreparedStatement - бұл біздің бәріміз. Бұл өнімділікке көп нәрсе береді. Ол жақпадағы үлкен флопты тудырады.
- Және 6 рет ТҮСІНДІРУ ТАЛДАУ керек.
- Бізге OFFSET 0 мәнін және проблемалық сұрауларымыздың қалған пайызын түзету үшін +0 сияқты амалдарды сұйылту керек.
Ақпарат көзі: www.habr.com