Používejte správné datové typy

Poznámka o datových typech a SQL.

Po zprovoznění IPv6, či lépe řečeno přepnutí z dlouhodobého nestabilního testovacího stavu do (doufejme) již stabilního ostrého, jsem i tomuto svému blogu přiřadil také AAAA záznam (takže pokud máte funkční připojení přes IPv6, právě čtete tyto stránky pomocí šestky).

Měl jsem tu také nasazený plugin Counterize pro statistiky přístupů. Chtěl jsem zjistit, kolik lidí sem přes šestku chodí.

Novou IP adresu jsem skutečně našel a to v podobě 2a02:7a00:4:11:e což je i pro nějakou zkrácenou formu nemyslné (samostatné :e znamená 000e, přičemž v adrese je ec06). Ořezání na 16 znaků dávalo tušit cosi nekalého.

A opravdu. Sloupec v tabulce statistik je definován jako `IP` varchar(16) NOT NULL.

Všechno špatně. IP adresa je číslo, nikoliv řetězec. IPv4 je 32b číslo, IPv6 je 128b číslo. Lze k tomu přidat i masku podsítě. IP adresy jako čísla lze snadno řadit (zkuste si to s řetězcem), a dělat s ní bitové operace (to už s řetězcem neuděláte), tak jak to dělají routery. Dalším důvodem jsou nároky na úložný prostor. Zatímco VARCHAR(16) bude mít v plné ASCII palbě 17B, tak 32b číslo 4B (nebo pokud to už chceme mít úplně košér a použijeme na to odpovídající datový typ tak 7B). Vynásobte si to očekávaným počtem záznamů v tabulce. U IPv6 by to bylo 44B vs. 16B (reps. 19B u typu inet). A to nemluvím o indexech.

Pozn: Kdyby ten plugin vůbec neuměl zpracovat návštěvy přicházející po IPv6, tak by mi to nevadilo. Vadí mi, že s IP adresami nakládá nevhodným způsobem a s ipv6 zcela špatně (všechny návštěvy z nesmyslné adresy 2a02:7a00:4:11:e spadnou do jednoho chlívku ve statistice).

Pokud by daný program použil odpovídající datový typ (v PostgreSQL inet), tak by jednak mohl využít všech funkcí, ale také by byl (alespoň částečně) připraven i na nový typ ip adres a nebylo by třeba upravovat schema.

Příspěvek byl publikován v rubrice Databáze. Můžete si uložit jeho odkaz mezi své oblíbené záložky.

15 komentářů: Používejte správné datové typy

  1. lzap napsal:

    Ja souhlasim, patch uz jsi jim poslal? :-)

    Ovsem doplnim ze to neni tak jednoznacne a mozna je jista denormalizace v tomto pripade i na miste. Ano, usetril bys par bajtu na zaznam, nicmene paklize tam neni index (pochybuji) tak to vubec nehraje roli a naopak ukladani by mohlo byt pomalejsi. Predpokladam ze ta PHP aplikace ip adresu dostava pres promennou prostredi jako text. Musis ji tedy prevest do ciselne reprezentace, coz je narocna operace per request.

    V pripade logu je denormalizace casto pouzivana, protoze take zjednodusuje zobrazovani. Opet bys musel vsechno prepocitavat pro kazdy zaznam zvlast, nehlede o nutnosti napsat takovy kod – bude obsahovat chyby. Co za to dostanes? Usetrenych par bajtu – da se spocitat kolik a moc to nebude – a zminenou moznost bitovych operaci. Ta je evidentne na nic – je to prece http access log a ne network management software Autori se zachovali celkem racionalne.

    Takze naprosto souhlasim, ale denormalizace neni nutne vzdy zlo. Zrovna ty ip adresy se velmi casto ukladaji do textu z vyse uvedenych duvodu. A pripadu znam vic.

    • Heron napsal:

      Pokoušel jsem se najít ve fóru, zda se tam již IPv6 řeší, ale nemají tam vyhledávání (asi je za přihlášením) a vytvářet účet jen pro hledání si tam fakt nebudu. ;-)

      Co se týče té denormalizace (krom toho, že se tento výraz většinou používá v kontextu normálních forem a faktorizace), tak osobně považuji použití odpovídajícího datového typu za základ.

      K tomu zbytku. Benchmark jsem nedělal, jistě převod ze string do čísla a zpět bude něco stát, ale stále si myslím, že výhody prostě převažují:

      CREATE TABLE xxx (addr inet);
      insert into xxx VALUES (‚192.168.10.10‘), (‚fe80::1e6f:65ff:fe47:4965‘);

      select * from xxx;
      addr
      —————————
      192.168.10.10
      fe80::1e6f:65ff:fe47:4965

      Tj. pro aplikaci je converze z a do stringu naprosto bez práce a věřím, že ta DB to zpracuje velmi rychle. Žádné „přepočítávání“ v aplikaci netřeba provádět.

      • lzap napsal:

        Pojem denormalizace jsem pouzil proto, ze se casto napriklad spojuji sloupce dohromady do stringu. U jednoho sloupce by zrejme o denormalizaci neslo. Ale o to ted nejde.

        Vedel jsem ze argumentujes tim, ze je zde ten specialni typ. Ty jsi proste typicky administrator a databazista. :-) Pohled z druhe strany. Na to ja musim jako vyvojar odpovedet jednoznacne. Varchar funguje vsude. :-)

        Skoda ze jsi jim to neposlal, protoze jsem si jisty, ze by to odmitli a jen prodlouzili varchar. Proste praxe je v tomto pripade jina.

        • Heron napsal:

          Nad tím varcharem se bude fakt dobře groupovat, získávat distinct hodnoty apod ;-) Ne, že by to nešlo, ale chudák server.

          Navíc DB je tu od konzistence hodnot. Proč odmítat kontrolu typu?

          • Zdeněk Burda napsal:

            Ukaž mi vývojáře, který bere ohled na nějakého chudáka servera ;-) Znáš to, dnes je HW tak levný, že se vyplatí koupit letpší CPU a víc RAM…

            • lzap napsal:

              Jenze v tomto pripade je ten varchar zaroven i rychlejsi… :-)

              • Heron napsal:

                Není ;-)

                Jestli budu mít čas (jakože nebudu), tak udělám pořádný benchmark. Zatím na 2mil záznamů v jedné transkaci je inet verze o 10s rychlejší, než varchar. Jasně, jedno měření a navíc takto krátké nic neznamená a také tím nechci moc argumentovat.

                Je to dané tím, že tabulka s inet je o něco menší (86MB vs. 108MB) — PostgreSQL má ještě další „neviditelné“ sloupce (pro čísla transakcí), které pro takto úzkou tabulku představují velkou režii.

                Samotná data v pro verzi inet mají 26MB a pro verzi varchar 49MB.

                Méně dat se rychleji uloží, na CPU se v tomto případě fakt nečeká.

                Mimochodem, vytvoření indexu nad inet trvalo 4s a nad varchar asi 77s. ;-)

                • lzap napsal:

                  Ano samozrejme. Ja pracoval s tim, ze se data nebudou dale zpracovavat a index nad ip adresou neni potreba. Nad varcharem je pochopitelne pomalejsi, ale v MySQL jina moznost neni, jak jsem psal.

                  Jinak delat benchmark tak ze nechas vkladat miliony zaznamu v transakci nema opravdu smysl. V realnem nasazeni to bude jeden insert na http request. Db ma takovou rezii, ze par bajtu navic nehraje roli.

                  • Heron napsal:

                    S tím benchmarkem souhlas. Lepší jsem za těch pár minut nenaimplementovala tohle je skutečně nerelevantní.

          • lzap napsal:

            Ale ty umele vytvaris problemy tam, kde nejsou! Proboha je to access log tabulka. Zadne grupovani ten software nedela. To ze bys to mohl prece mozna potencionalne udelat pres sql konzoli vyvojare nezajima. Musime se pohybovat v ramci moznosti.

            Nehlede na to, ze WordPress je urcen pro MySQL databazi a pluginy musi pouzivat jen to, co je v MySQL. A hadej co – tam typ pro ip adresu neni.

            Vyvojar a db admin budou vzdy ve sporu, nedelam si iluze. :-) Ale v tomto konkretnim pripade se musim postavit za toho vyvojare. Spravne reseni je tedy prodlouzit varchar.

            Neber to osobne, tvoje db prispevky ctu rad. Trosku empatie by neskodilo :-)

            • Heron napsal:

              „Zadne grupovani ten software nedela“

              To není pravda. Poskytuje statistiky návštěvnosti pro jednotlivé IP v daných časových intervalech. Takže něco jako

              select sloupce from tab
              where date between interval
              group by ipaddr;

              Osobně to neberu, rád znám také názory ostatních. Těžko jako technik a správce db můžu mít empatii k někomu, jehož stupidní dotaz zbytečně drtí db.

              • lzap napsal:

                No tak paklize se tam grupuje tak neni co resit. Cele to na me delalo dojem ze jde o access log bez dalsiho zpracovavani.

                Stale mi to ale pride vuci vyvojarovi nefer. On dela soft pro MySQL s moznosti behu na jinych db serverech. Takze je to cele dost nerelevantni – jedina rozumna moznost jsou explicitni prevody. Zbytecne slozite, nachylne k chybam, casove narocne a neprakticke. Proste varchar s indexem to zvladne.

                On je ten pohled db admina vzdycky zjednodusujici. Vyvoj neni cernobily svet, musi se pocitat s vecma jako je rozpocet projektu, cas, prenositelnost a podobne. Programatorovi takovouhle upravu rozumny sef, napriklad ja :-), neschvali. Zejmena ve vztahu k velikosti daneho projektu – pluginu.

                Takze vzhledem k novy skutecnostem je muj verdikt – prodlouzit varchar a pridat index. :-)

                • Heron napsal:

                  Jak to tak čtu, tak dneska se mi bude fakt blbě spát.

                  Nevím jaké projekty máš na starosti ty (můžem někdy pokecat u pivka), ale:

                  Kámoš tuhle v práci cosi řešil a neustále narážel na „limity a vlastnosti“ MySQL. Řešil to reálného času několik měsíců (kolik je ve výkazu hodin fakt nevím), neustále nějaké workaroundy, přepočítávání v aplikaci, obcházení nefunkčních transakcí, triggerů apod.

                  Tak jsem mu poradil, ať opustí MySQL. Tak uvidíme. Nevím, kolik stály ty workaroundy, ale vím (a tohle padá na hlavu vedení projektu), že kdyby použil kompetentní DB (jakoukoliv), tak má značně usnadněný život.

                  Já, ač to tak možná nevypadá, nejsem PostgreSQL fanatik. Tohle vůbec není o PG.

                  Na konferenci P2D2 to Pavel řekl naprosto správně. Za rozmachem tzv. NoSQL mohou chyby MySQL. Věci jako „varchar“ na všechno k tomu patří také.

            • Heron napsal:

              Ještě k tomuto:

              „Nehlede na to, ze WordPress je urcen pro MySQL“

              Jestli mají podepsanou exkluzivní smlouvu, tak je fakt lituju.

              On wordpress a jeho pluginy jsou vůbec dobrá studnice nápadů „jak se to nemá dělat“.

              Tuhle jsem tu měl nějaký plugin, který zaznamenával chyby 404 (jakože url, které nebylo nalezeno). Šikovná věc. Jenže také to začalo více než málo zatěžovat DB a zejména disky.

              Dopátral jsem se, že ten seznam 404 URL to nemá jako jednotlivé záznamy v tabulce, ale jako jedno velké XML uložené do jednoho záznamu. To XML mělo už asi 8MB, to znamená pro každé nové URL se musí přečíst, v paměti appendovat nové pár bajtové URL a opět to celé uložit a fsyncount na disk!

              To už v hloposti překoná snad jedině tohle:
              https://www.destroyallsoftware.com/talks/wat

              • lzap napsal:

                WP není zrovna ukázkově psaný software, ale tak nějak dělá to, co má, když ho používáš ;-)

                Jinak já jsem původně napsal pro to, že jsem si myslel že se jedná o jednoduchou tabulku přístupů bez dalších statistik přímo v programu. Ten plugin neznám a moje fantazie zabrala naplno.

                Ta motivace byla, že věci jako jsou různé logy a velké objemy dat bez dalšího zpracovávání se někdy ukládají „tak jak jsou“ (tj. do „špatných typů“, se závislostma mezi sloupcemi a podobně) dokonce i bez indexů (dělají se nad nima třeba pak OLAP kostky). Jde tam o maximální výkon při ukládání. Proto mi to nepřipadalo tak jednoznačné. Rád vyhledávám výjimky z pravidel a zabývám se jimi.

                Že se mají používat v databázích správné typy (pokud je to možné), dávat je do normálních forem, správně vytvářet indexy a podobně – z toho jaksi vycházím.

                Nicméně funčnost pluginu byla jinde – mé poznámky jsou tudíž nerelevantní. Navíc jsme zabředli do diskuse na téma PostgreSQL vs MySQL nebo kvalita kódu WordPressu. Ti chlapci nemají na výběr – mají prostě MySQL a to musejí podporovat. Bylo by chválihodné to přeportovat, ale moc dobře vím, jak je portace db platformy těžká – sám jsem v Red Hatu začal portací projektu Spacewalk (spacewalk.redhat.com) z Oracle na PostgreSQL (350 uložených procedur – stále to ještě není hotovo).

                Jó kdyby se to dělalo na zelené louce, asi by bylo všechno jinak. Já sám jsem veliký zastánce RDBMs, na našem aktuálním projektu (Katello.org) začali kluci s NoSQL a já jsem byl jedním z těch, kteří si nejvíc stěžovali. Nyní jedeme nad PostgreSQL. :-)

                Ale to už se zase rozepisuju. Bugzilly čekají. Čau.

Komentáře nejsou povoleny.