PostgreSQL срещу панди - как да балансирате задачите между страната на сървъра и клиента

От Тина Венцел и Кавия Гупта.

Първата стъпка в изграждането на приложения, управлявани от данни, често включва писане на много заявки в SQL. Докато премествате анализа си към производство, зависи от разработчика да балансира задачите оптимално между извличането на данни от сървъра на базата данни и анализа на данни в клиент, напр. Python или R. По време на тази стъпка за автоматизация искате да балансирате задачите на сървъра и клиента оптимално, тъй като вашият избор може да има голямо влияние върху производителността.

И така, как го правите?

Всеки ден - някъде на земята: типичен дебат „от страна на сървъра срещу клиента“

В общността за анализи и разработчици има преобладаващи мнения: някои смятат, че сървърът трябва да направи колкото е възможно повече, други искат да направят всичко от страна на клиента. И двете са твърде абсолютни, за да бъдат полезни. Фактът, че можете да направите всичко в SQL или python, не е нито добра причина, нито извинение за това, че всъщност правите всичко, използвайки един и същ инструмент.

SQL е най-разпространеният език за обработка на данни (DML), използван за извличане и манипулиране на данни в релационна база данни. Той е предназначен за извършване на рутинни процедури на базата данни (напр. Присъединяване, избор, филтриране, групиране) и фини CRUD (създаване, четене, актуализиране и изтриване) операции. Поради това е необходимата първа стъпка, за да вкарате данни в използваем формат - и можете да направите много повече с него.

Вградените аналитични функции (регекс, класиране, квантил, изоставане или олово) ви позволяват да извървите дълъг път и ако наистина искате, можете да стартирате регресии в SQL с UDF. Няма ограничение за вашите (неудобни) фантазии и със сигурност е възможно да се опитате да направите твърде много в SQL.

И все пак, за много учени и разработчици на данни SQL е необходимо зло и видяхме много случаи на опит да се направи твърде много в „код“, който би трябвало да се направи в SQL. Всъщност, пандите също така поддържат всички основни задачи за манипулиране на данни (присъединяване, филтриране, групиране), но това не превръща пандата спрямо SQL избора на въпрос на вкус. За мнозина пандите са просто пътят на най-малко съпротивление, тъй като изглежда по-бързо да вземете суровите данни и да срежете и анализирате в python.

Ключът е по този начин да се намери интелигентен баланс между двете. Поне това си помислихме и решихме да напишем тази публикация в блога за SQL срещу пандите, за да проучим къде трябва да приключи подготовката на данни в SQL и къде трябва да започне анализът в python.

Отговорът, според нас, трябва да се ръководи от това, което е най-поддържано и ефективно в работата.

Експеримент 1 - загряване

Решихме да проведем някои експерименти и започнахме с обикновено сравнение на стандартните операции с база данни, т.е. присъединяване, филтриране, сортиране и групиране между панди и локална база данни postgres. За да се даде представа за размера на симулираните данни, най-голямата таблица съдържаше 10 милиона редове и 3 колони ~ 277MB.

Подобно сравнение беше направено от tianhuil, който определи ориентировъчните панди към SQLite и установи, че SQLite надминава пандите за филтра и сортиране, но е по-бавен за групата и да се присъедини при размер на таблицата от 10 милиона реда.

Обърнете внимание, че нашето измерване на времето за изпълнение се различава от анализа на tianhuil. Той определи времето на операцията, напр. филтрирайте след зареждане на данните в Python. Наградихме операцията select * от таблицата и филтъра, за да огледаме по-отблизо какво всъщност ще се случи на практика.

Друга хубава справочна база за база данни, която обхваща пандите и R спрямо всички бази данни, е szilar. Той открива, че пандите са с 30 секунди по-бързи от postgres за присъединяване и почти 3 минути (!) По-бързи от postgres forgroupby. Заслужава да се отбележи, че szilar не конфигурира postgres, което обяснява лошата производителност на postgres, както споменаваме по-долу. Интересното е също, че той откри, че базата данни с отворен код GPD MapD може да се присъедини за по-малко от 1 секунда, което е добре да знаете!

Сега към нашите резултати: По-долу са показани най-бавните, най-бързите и средни за 30 писти:

таблица 1 - резултати от експеримент 1 (работи на 16GB лаптоп, Ubuntu)
Открихме, че postgreSQL превъзхожда пандите в 4/4 тестови случая. Той е много по-бърз за присъединяване, филтриране и групови. сортирането беше незначително по-добро от пандите, въпреки че това беше силно зависимо от конфигурациите на работната памет. Без настройване на конфигурацията, postgres сортира много по-бавно от пандите, ако наборът от данни е> 1MB.

Тъй като ние поддържаме резултат, това е 1-0 за postgreSQL и време за експеримент номер 2.

Експеримент 2 - към реалния свят

След това взехме проста SQL заявка, за да получим кръстосани конфигурации на модела на автомобила, т.е. колко потребители проявиха интерес към определена двойка автомобили, конфигурирайки и двете. Тази заявка може да се използва за препоръчване на подобни автомобили на потребителите. (Ако току-що сме ви изгубили, погледнете нашия избор на кола за конфигуриране на автомобил.)

def do_it_in_sql ():
     sql1 = "" "
     с recent_views като (
             изберете user_id, име на модел
             от car_config_table
             където е създадено_at> current_date - интервал '2 месеца'
     ),
     popular_models като (
             изберете име на модел като плужек,
                    конфигурации count (отделни user_id)
             от recent_configurations
             групиране по 1
             като имам брой (отделен user_id)> 3
     ),
     popular_configurations като (
             изберете C.model_name, C.user_id, M.configs
             от recent_configurations C
             присъединете се към popular_models M в M.slug = C.model_name
     )
     SELECT
     C1.model_name model_name,
     C1.configs model_configs,
     C2.model_name препоръчано_модел_име,
     C2.configs препоръчани_model_configs,
     count (отделно C1.user_id) combo_configs
     от popular_configurations C1
          присъединете се към popular_configurations C2 на C1.user_id = C2.user_id
     където
     C1.model_name <> C2.model_name
     група по 1,2,3,4
     подредете по име на модел
     "" "
     df = Execute_to_postgres (sql1)
     връщане df
def do_it_in_pandas ():
    sql = "" "
    изберете user_id, име на модел
    от car_config_table
    където е създадено_at> current_date - интервал '2 месеца'
    "" "
    df = query_postgres (sql)
    df ['configs'] = df.groupby (['model_name']) ['user_id']. transform ('nunique')
    df = df [df ['configs']> 3]
    crossdf = df.merge (df, on = 'user_id', how = 'external')
    crossdf = crossdf [crossdf.model_name_y! = crossdf.model_name_x]
    crossdf ['combo_configs'] = crossdf.groupby (['model_name_x', 'model_name_y']) ['user_id']. transform ('nunique')
    crossdf = crossdf [['model_name_x', 'users_x', 'model_name_y', 'users_y', 'combo_configs']]. drop_duplicates (). sort_values ​​('model_name_x')
    връщане на кръстосан текст

Първата функция (do_it_in_sql) изпълнява sql оператор чрез конектор (например psycopg2). Първо издърпва всички конфигурации през последните 2 месеца в recent_views, след това намира общия брой конфигурации по модел в popular_models.Стъпки 1 и 2 след това се свързват в таблицата с темповете popular_configurations, използвани за генериране на крайния кръстосан резултат от конфигурациите.

Втората функция (do_it_in_pandas) възпроизвежда същото в пандите, като дърпа всички конфигурации от 2 месеца в паметта и след това конструира таблицата с кръстосани конфигурации. И така, кой според вас беше по-бърз?

Резултатът беше малко неочакван: SQL изпълнен за 7.5 и панди за 6 секунди.

Така че оценките вече се изравняват на 1-1.

Тъга сред някои привърженици

За да проучим това допълнително и / или да спестим изяществото на SQL, направихме още няколко настройки на заявки и написахме още 2 оптимизирани SQL заявки, за да се състезаваме срещу панди:

  • sql2 използва прозоречна функция вместо подзапроси, използвани в основната заявка sql1 (вижте do_it_in_sqlfuntion);
  • sql3, от своя страна, използва таблица / материализиран изглед, за да добави броя на отделните потребители за всеки модел (наречен model_configs).

В допълнение, ние задаваме подходящи индекси, вакуумираме съответните таблици на базата данни и допълнително конфигурираме postgres, за да настроите производителността, напр. чрез блъскане на споделени_буфери за кеширане на данни и work_mem за по-големи видове в паметта.

Резултатите от 30 изпълнения са показани по-долу.

таблица 2 - експеримент с резултати 2 (работи на лаптоп с 16 GB, Ubuntu)
sql2 = "" "
  с recent_configurations като (
    изберете user_id,
           име на модела,
           COUNT (user_id) OVER (PARTITION BY model_name) като конфигурира
    от car_config_table
    където е създадено_at> current_date - интервал '2 месеца'
    група по 1,2
  )
  ИЗБЕРЕТЕ C1.model_name model_name,
         C1.configs model_configs,
         C2.model_name препоръчано_модел_име,
         C2.configs препоръчани_model_configs,
         count (отделно C1.user_id) combo_configs
  от recent_configurations C1
       присъединете се към recent_configurations C2 на C1.user_id = C2.user_id
  където C1.model_name <> C2.model_name
  група по 1,2,3,4
  като имам брой (различен C1.user_id)> 3
 "" "
sql3 = "" "
 падаща таблица, АКО съществува
 създайте таблица analytics.mv като (
    изберете user_id,
           име на модела,
           COUNT (user_id) OVER (PARTITION BY model_name) като конфигурира
    от car_config_table
    където е създадено_at> current_date - интервал '2 месеца'
    група по 1,2);
 
 ИЗБЕРЕТЕ C1.model_name model_name,
        C1.configs model_configs,
        C2.model_name препоръчано_модел_име,
        C2.configs препоръчани_model_configs,
        count (отделно C1.user_id) combo_configs
 от analytics.mv C1
 присъединете към analytics.mv C2 на C1.user_id = C2.user_id
 където C1.model_name <> C2.model_name
 група по 1,2,3,4
 като имам брой (различен C1.user_id)> 3
 "" "

Оптимизация на заявките - капка в океана

Както можем да видим от таблица 2, оптимизацията на заявките помогна малко - sql2 (който използва функциите на прозореца) е с около 8 ms по-бърз от оригиналната заявка (sql1). Tweaking конфигурации също ни спечели средно 5ms за самия sql1, което премина от 7,5 до 7,0 секунди.

И все пак, не е достатъчно да победите пандите.

Това, което този експеримент демонстрира, е колко аспекта има към изпълнението на SQL заявките. Оптимизацията на заявките е полезна, но времето, прекарано за конфигурации на базата данни, правилното индексиране, ключовете за разпространение / сортиране, намаляване на спора, управление на натоварването и т.н., правят по-голяма разлика от самата настройка на заявките. За да получите пълна картина, също трябва да се вземат предвид пропускателната способност / мрежовата пропускателна способност и натоварването на БД.

Pandas е просто пакет и няма сложността на база данни. Той е оптимизиран за скоростта извън кутията и е готов за състезание след инсталиране на пипс. Postgres от друга страна е сънливост извън кутията и дяволът се крие в конфигурацията. Той дори не използва quicksort по подразбиране. Работната памет по подразбиране, разпределена на postgres, е 1MB! Ако имате повече от това, той разсипва данните на диск, за да ги сортира, което е ужасно за изпълнение на заявките и ефектно безполезно за база данни, която може да обработва стотици терабайти!

postgres конфигурация по подразбиране

Настройката на postgres конфигурации за висока производителност е решаваща, тъй като въздействието върху производителността на заявките е много голямо. Като каза това, правилната конфигурация е трудна - твърде много или твърде малко от нея води до ефективност на заявките, толкова надеждна и променлива, колкото времето. Късмет!

широчина на честотната лента - „Никога не подценявайте широчината на честотната лента на вагон, пълен с ленти, спускащи се по магистралата.“ (А. С. Таненбаум)

Друг важен аспект е размерът на данните и широчината на честотната лента между SQL сървър и (python) клиент.

Дали парче код се изпълнява по-бързо на сървъра или в панди, зависи дали данните, които базата данни изпраща обратно на клиента, се увеличават или намаляват по време на стъпката на обработка.

Например, recent_configurations в sql1 има размер (843k x 3), който се използва за конструиране на окончателната кръстосана конфигурационна таблица (699k x 5). Следователно в do_it_in_sqlfunction увеличихме количеството данни, което SQL сървърът изпраща обратно на клиента.

Докато го изпращате между SQL сървър и python клиент, комуникационната връзка се влияе от честотната лента, която обикновено е малка, но може да се превърне в пречка. Следователно зареждането на по-малък набор от данни и изчисляването на локално ниво в python може да бъде по-бързо от изчисляването му на сървъра и изпращането на допълнителни данни по канал с ниска честотна лента като WiFi.

Сега в нашия случай честотната лента на мрежата не беше проблем, тъй като ние управлявахме базата данни postgres и python на същата машина. Въпреки това, данните все още трябва да се прехвърлят между SQL и python процеса, тъй като те не споделят памет.

Мащаб - Ограничението на Panda е вашата памет - SQL мащабира по-ефективно

С увеличаването на натовареността случаят за БД става по-ясен. Докато изпълнението на задача от страна на клиента може да бъде по-бързо, стига да се вписва в паметта, можете да мащабирате много по-добре в база данни. Разбира се, можете да мащабирате локално с инструменти, които ви позволяват да правите това като Spark или извън основните инструменти за изчисляване като Dask. Но дори и да заредите всички данни в Spark, все пак в крайна сметка ги зареждате в паметта. И така, какво спечелихте, като го доставихте от БД, различна от някои ограничения в честотната лента? Не много.

Spark е двигател за разпределена обработка на данни, а не база данни, но ако го използвате за операции с база данни като присъединяване, задаване на операции и заявки, напр. като подготовка за някаква задача на ML, има сериозен случай за поддържане на тежкото повдигане, напр. AWS Redshift колкото е възможно повече. Това ще ви даде по-бърз отговор, независимо дали db клиентът се свързва чрез локални сокети или дистанционно по мрежата. Също така, допълнителната сложност и поддръжка, въведена от Spark, вероятно е повече от тази на база данни.

Поддържаемост и четимост

Намирането на правилния баланс между SQL и пандите е компромис между производителност и поддръжка. Експеримент 1 показа, че SQL превъзхожда пандите, когато разглежда самостоятелни операции като филтър, groupby, sortand, но експеримент 2 показа, че може да бъде по-бавен в реални примерни заявки. Защо?

Е, до известна степен това е така, защото база данни ви предоставя неща, за които не искате, като едновременност, заключване, ограничения и т.н., което струва известно време за изпълнение и ви кара да правите неща, за които смятате, че не е нужно да се притеснявате. като грешки в конфигурацията. Но на по-фундаментално ниво SQL също не е проектиран за трансформиране на данни по определени начини и има много операции, които надхвърлят възможностите на SQL. SQL не е добре да описва набори от редове, напр. претеглена подвижна средна стойност, манипулация на низове, регекс и въртящи се таблици. Процедурните подходи като UDF също не са толкова добри, колкото в реалните процедурни езици и голяма част от форматирането, математиката или статистиката могат да бъдат по-ефективно изпълнени на език с общо предназначение като python.

И все пак, на практика, все пак ще използвате много от тези функции в SQL, ако направите повече за един път. Така че ползите от запазването на повече задачи от страна на сървъра надхвърлят самото време за изпълнение. Подобренията в практическата ефективност идват от минимизирането на данни, изпращани по мрежата (което е по-сигурно и по-малко I / O-тежко), стандартизацията, надеждността, мащабируемостта и по-доброто сътрудничество. Следователно, при разумна производителност, SQL все още е за предпочитане, тъй като е по-поддържан в много отношения.

На практика SQL е по-структуриран и по-читаем подход за получаване на данните в използваем формат. Присъединяването в SQL изглежда много по-добре от присъединяване / сливане в панди и всеки нетехнически член на екипа също може да разбере SQL много бързо. В допълнение, SQL е стандарт, така че всеки технически човек ще има известни знания за него и ще може да го схване, без да се нуждае от специализирани знания. Тъй като SQL е почти самодокументиран и по-четим, той е и по-поддържан. Това е голямо предимство, тъй като при дълготрайните приложения поддръжката е огромна цена и простотата често диктува дизайна.

В крайна сметка познаването на SQL също така ще ускори работния ви процес, който (за много анализатори на данни) често включва писане на много заявки в SQL в началото и тъй като така или иначе данните са в релационна база данни, обикновено има смисъл да придържай се към него. Ще си спестите много време и болка в бъдеще, като не го дърпате локално всеки път и след това ще го заредите в Python, за да продължите.

SQL също така се интегрира по-добре с производствения код, който може да бъде написан на някакъв друг език за програмиране. Така че, когато полезен ad-hoc анализ стане част от производството, вече имате голяма част от него в SQL, което спестява време да не се налага да преобразувате напред и назад между SQL и езика на приложението.

За екипа от данни в carwow използването на SQL също има положителни странични ефекти за съвместна работа, тъй като SQL заявки, написани от други, могат да бъдат маркирани и използвани повторно за свързани анализи.

Заключения

SQL е най-добрият инструмент за използване за извличане на основни данни и колкото по-добре го знаете, толкова повече ще ускори работния процес. По-усъвършенствани задачи за манипулиране на данни могат да се изпълняват както на сървъра, така и на клиента и зависи от анализатора да балансира натоварването оптимално.

Може би най-голямата болка при извършването на усъвършенствани манипулиране на данни от страна на сървъра в SQL е, че той въвежда сложността на пълноценна база данни, която е висока в поддръжката. Тя изисква внимателна конфигурация на средата на базата данни и поддържането на оптимална производителност е работа на пълен работен ден.

Най-голямото предимство на изпълнението му в панди е, че той е добре конфигуриран извън кутията, за да изпълнява много бързо задачи за манипулиране на данни за „малки“ данни. И все пак пандите не трябва да се използват за извършване на основни операции с база данни (обобщаване на данни или заявки), тъй като добавят още една I / O операция, са ограничени от паметта ви и по този начин не мащабират добре. Той се интегрира по-малко гладко с други рамки в производството и също така е по-малко четим. Това прави кода по-труден за разбиране и по този начин по-малко поддържан.

„Балансът е моментът, в който получавате най-висока поддръжка с приемливи показатели“

Сладкото място за това как да балансирате най-добре задачите от страна на сървъра и от страна на клиента е мястото, където получавате най-висока поддръжка с приемлива производителност. Как най-добре да стигнете до него зависи от много фактори, включително натоварване на DB, честотна лента, ключове за разпределение и сортиране, зададени в базата данни и по-обща настройка на заявките. Надяваме се, че нашето обсъждане на тези аспекти ще ви помогне да определите къде най-добре да начертаете границата между процесите на сървъра и клиента за вашия случай на използване. Ако не, проверете нашите правила в края на публикацията.

SQL е необходимо зло и трябва да го прегърнете!

SQL е необходимо зло - прегърнете го!

И така, какви трябва да бъдат вашите приоритети за балансиране на задачи от страна на сървъра и клиента?

Ето някои правила:

  1. Ако извършването на задача в SQL може да намали количеството данни, върнати на клиента (например филтриране до по-малък набор от данни), тогава задачата принадлежи на сървъра.
  2. Ако количеството данни, върнати на клиента, остане непроменено или нараства (например добавяне на сложни изчислени колони; кръстосани съединения и т.н.), като го направите в SQL, задачата принадлежи към кода на страната на клиента.
  3. Тествайте различни настройки от страна на сървъра и клиента, за да видите коя е по-ефективна. В най-лошия случай ще научите нещо.
  4. Никога не правите в кода това, което SQL сървърът може да ви помогне: извличане на данни (CRUD, присъединяване и задаване на операции) и прост анализ на данни.
  5. Ако е болезнено или грозно, направете го в код от страна на клиента: Сложният анализ на данни принадлежи към код. Оставете форматиране или математика за страната на клиента. Базата данни съществува главно за улесняване на бързо извличане на данни.
  6. Минимизиране на сложността на SQL: Разделяне на твърде сложни, неефективни заявки. Две по-прости заявки ще спестят главоболието от поддържането на една мега-заявка. Друга възможност е да го разделите на обикновена заявка и да се справите със сложността в кода от страна на клиента.
  7. Минимизирайте обиколки на базата данни: Опитайте се да направите колкото можете повече в една операция. Всеки запетайка е едно пътуване и добавя още една I / O операция.
  8. Конфигурирайте внимателно вашата база данни, напр. за постгреси. В противен случай, вие по подразбиране на под-оптимални алгоритми, което е скъпо.
  9. Добре е да инвестирате време в оптимизацията на схемата на базата данни.
  10. Същото се отнася и за настройка на оптимални чужди / сортиращи / разпределителни ключове и правилно нормализирани таблици за поддържане целостта на данните.

Интересувате ли се да направите въздействие? Присъединете се към екипа на carwow!
Чувствате ли се социални? Свържете се с нас в Twitter и LinkedIn :-)