InnoDB u mysql (was: firebird vs. postgres)

[Karel Zak]

 Ja si umim nasazeni MySQL celkem predstavit. Napriklad nejaky
 redakcni Web system kde 99% dotazu je cteni apod. Problem je, ze
 dost autoru vola po rychlosti SQL u web aplikaci z duvodu, ze se jim
 nechce/neumi implementovat nektere veci na urovni druhe vrstvy
 (apache) efektivnejsim zpusobem. Nepamatuji se, ze bych videl clanek
 o PHP a tam treba bylo o tom jak vyuzit sdilenou pamet na ukladani
 casto (nebo temer vzdy) stejnych dat. Jak psal drive Stano(?), kde jsou
 vsichni ti dobri programatori a jejich bajecne programy :-)

On Thu, May 30, 2002 at 09:45:19AM +0200, Ing. Pavel PaJaSoft Janousek wrote:
> 
> 	Prvni co mne zarazi je zpusob definovani InnoDB v MySQL
> - proc bych mel coby uzivatel ci administrator definovat veci jako
> auto-extending apod. - vzdyt toto vsechno ma delat databaze sama, ona za
> behu nejlepe vi, co jak kdy se deje a dle toho se chovat...

 Toto je docela zajimava libustka :-) Ale ono u vseho lze nalezt neco.
 Napriklad kdyz jsem poprve videl Interbase tak mne prekvapilo, ze DB
 mam chapat jako cestu k nejakemu souboru apod.

> 	Nejak jsem explicitne nevycetl, zda-li umi jednu InnoDB table pres vice
> souboru => zrejme ne, tedy jedna tabulka muze mit velikost maximalne co
> podporuje FS... a jak na jinem miste vime (precteme si) je to zpravidla
> 2GB, nic moc...:-(

 Nedavno se ozval do PostgreSQL konference clovek s 20GB databazi
 (nebo tak nejak) a ze pry ma tabulku jejiz index ma 3GB.

> Starting from version 3.23.50 you can also associate the ON DELETE
> CASCADE or ON DELETE SET NULL clause with the foreign key constraint. 

 S plnou implementaci FK maji problem i komercni SQL. Pokud mne pamet
 nemate tak DB2 nema ON UPDATE CASCADE, ale jen RESTRICT (pokud to
 neni zalezitost verze). Ono to implemntovat neni snadne.

> Starting from version 3.23.50, InnoDB does not check foreign key
> constraints on those foreign key or referenced key values which contain
> a NULL column. 
>
> - zde si nejsem jist, zda-li to neodporuje norme

 V plne implementaci FK by to melo byt nastavitelne (PostgreSQL):

There are three match types: MATCH FULL, MATCH PARTIAL, and a default match
type if none is specified. MATCH FULL will not allow one column of a
multicolumn foreign key to be NULL unless all foreign key columns are NULL. The
default match type allows some foreign key columns to be NULL while other parts
of the foreign key are not NULL. MATCH PARTIAL is not yet implemented. 
 
> When doing foreign key checks InnoDB sets shared row level locks on
> child or parent records it has to look at. InnoDB checks foreign key
> constraints immediately: the check is not deferred to transaction
> commit. 
> 
> - tak a toto je to HLAVNI omezeni a zde tvrdim, ze v tomto stavu je to
> pro opravdu rozumnou praci naprosto nepouzitelne... - v podstate je to
> to same, jako byste si vsechno hlidali na aplikacni urovni. Jak uz se po
> nekolikate opakuji, ne vsechny 'DML operace' lze zapsat pomoci jednoho
> automicke DML prikazu... k cemu mi je commit, k cemu jsou ty tzv.
> 'transakce'? Ne vzdy je mozno po kazdem DML prikazu mit databazi v
> konzistentnim stavu, od toho tu jsou transakce a jednotlive ISOLATION 
> LEVELy, abych rekl: tak ted zacinam delat ptakoviny, tak ted jsem
> skoncil a
> makej... a vysledkem je bud a) - jo bastim Ti to, b) ses blbej, nauc se
> pracovat poradne... 

 Opet vec v plne implemntaci FK definovatelna:

DEFERRABLE or NOT DEFERRABLE

This controls whether the constraint can be deferred. A constraint that is not
deferrable will be checked immediately after every command. Checking of
constraints that are deferrable may be postponed until the end of the
transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default.
Only foreign key constraints currently accept this clause. All other constraint
types are not deferrable.  

INITIALLY IMMEDIATE or INITIALLY DEFERRED

If a constraint is deferrable, this clause specifies the default time to check
the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after
each statement. This is the default. If the constraint is INITIALLY DEFERRED,
it is checked only at the end of the transaction. The constraint check time can
be altered with the SET CONSTRAINTS command. 

> 	Dalsi vytka se tyka zalohovani - pokud jsem to dobre pochopil, pak
> InnoDB databaze se musi zalohovat jinak... to je super pro

 To jako vazne? To zni divne...

> Standard je pouze ANSI SQL 92 ENTRY Level => nikoli ANSI SQL92 jak se
> bezne 'rika'...

 IMHO u MySQL nema cenu se bavit o standardech. Co treba udela

    SELECT 'aaa' || 'bbb';
vs.
    SELECT 'aaa' + 'bbb';

 Co treba v urcite dobe vyvoje operator LIKE a jeho case (in)sensitive 
chovani? To jsou drobnosti ktere mne osobne dokazou dost prekvapit. 
Nejake FK bych prezil...

> MySQL has more APIs to other languages and is supported by more existing
> programs than PostgreSQL. See section B Contributed Programs. 

 To je srovnani dvou malych deti, ktere se hadaji o to, ktery z 
tatinku dela dulezitejsi praci...
 
> - tak a tady si troufam rici, ze lzou! Srovnejte language-binding, 
> srovnejte v cem lze psat stored-procedury/triggery, kde mame 
> EmbeddedSQL atd...?

 IMHO az jednou bude Java a funkce poradne vracejici radky tak v tomto 
necha PG ostatni dost za sebou :-)

> MySQL Server works on 24/7 heavy-duty systems. In most circumstances you
> never have to run any cleanups on MySQL Server. PostgreSQL doesn't yet
> support 24/7 systems because you have to run VACUUM once in a while to
> reclaim space from UPDATE and DELETE commands and to perform statistics
> analyses that are critical to get good performance with PostgreSQL.
> VACUUM is also needed after adding a lot of new rows to a table. On a
> busy system with lots of changes, VACUUM must be run very frequently, in
> the worst cases even many times a day. During the VACUUM run, which may
> take hours if the database is big, the database is, from a production
> standpoint, practically dead. Please note: in PostgreSQL version 7.2,
> basic vacuuming no longer locks tables, thus allowing normal user access
> during the vacuum. A new VACUUM FULL command does old-style vacuum by
> locking the table and shrinking the on-disk copy of the table. 

 Jeste, ze to "Please note". Jinak opet oblast ktera je u PG vyvojove 
celkem ziva.

> MySQL Server has a much more sophisticated ALTER TABLE. 

 Myslim, ze vyvojari PG zamerne nechteji implementovat DROP COLUMN, 
aby odpurci meli na co ukazovat :-) IMHO cetnost zmen struktury tabulek 
v produkcni a stabilni DB je temer nulova.

> MySQL Server is coded from the start to be multi-threaded, while
> PostgreSQL uses processes. Context switching and access to common
> storage areas is much faster between threads than between separate
> processes. This gives MySQL Server a big speed advantage in multi-user
> applications and also makes it easier for MySQL Server to take full
> advantage of symmetric multiprocessor (SMP) systems. 
 
 Dost ziva/debatovana oblast vyvoje PG. Predpoklada se mozna casem vyuziti 
pre-forked backendu (jako klasicky apache) coz by podle mne vyrazne 
dost vecem pomohlo (cache, cas connectu apod.). Mozna casem i na neco
budou thready. 

> - co robustnost?

 To podle mne nemusi nutne byt problem. Ono pri nejakem memory leaku 
a signalu 11 take nezko si byt jist je-li shared memory OK. Napr.  
PostgreSQL v tomto pripade dela reset te pameti. 

 Ma MySQL nejaky wraper ktery ji v pripade problemu probudi opet k
zivotu?

> - tvrzeni, ze context switching je rychlejsi je otazka implementace
> threadu, ne otazka vykonu databaze...

 Thready zvetsuji slozitost i kdyz je pravda (IMHO), ze pri
poradnem pouziti mohou byt efektivnejsi. Osobne nejsem proti nim.

> MySQL Server supports a compressed client/server protocol which improves
> performance over slow links. 
 
 To je u PostgreSQL soucasti SSL :-)
 
> - to v principu umi kazda konexe, zalezi na 'podhoubi'..., ostatne, 
> takove veci se maji resit na uplne jine nez aplikacni urovni...
> 
> All MySQL table types (except InnoDB) are implemented as files (one
> table per file), which makes it really easy to back up, move, delete,
> and even symlink databases and tables, even when the server is down. 
> 
> - ehm, co treba file-size file-system omezeni? Tyto vyhody osobne v
> dnesni dobe povazuji za spise nevyhody...

 PostgreSQL take pouziva soubory. IMHo to nema nic do cineni s
 omezenim FS, proste se tech souboru udela vice.

> Upgrading MySQL Server is painless. When you are upgrading MySQL Server,
> you don't need to dump/restore your data, as you have to do with most
> PostgreSQL upgrades. 

 To je fakt blbost a zalezitost verze.

> Much slower INSERT, DELETE, and UPDATE. 

 Az bude muset MySQL behem jednoho dotazu udelat to co PostgreSQL tak
 to pujde srovnavat. Takto je to jabka vs. hrusky.

    Karel

-- 
 Karel Zak  <zakkr na zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz