InnoDB u mysql (was: firebird vs. postgres)

[Ing. Pavel PaJaSoft Janousek]

> jsem prijemne prekvapen, ze jsou stejne rychle jako myisam (byl to jeden
> slozity select, tusim ze jsem o to referoval sem). Jine zkusenosti
> nebo reference nemam, samozrejme jsem precetl oddil v mysql dokumentaci.

	MySQL 3 roky nepouzivam, proto cerpam jen informace z www.mysql.org a z
www.innodb.com. Ale i to mi celkem postacuje pro podporu nize uvedenych
tvrzeni.

	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...

	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...:-(

	Toto omezeni je silne neprakticke (a navic mi neni jasne, proc tomu 
tak musi byt - tedy krome omezenosti autoru MySQL):
Both tables have to be InnoDB type and there must be an index where the
foreign key and the referenced key are listed as the first columns.
InnoDB does not auto-create indexes on foreign keys or referenced keys:
you have to create them explicitly. 

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. 

- docela pozde nemyslite?
- navic chybi uplne zakladni volba a sice odmitnuti delete... - mam k
dispozici pouze DELETE CASCASE a DELETE SET NULL... - to neni ani dle
ANSI SQL92...

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

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... 

	Dalsi vytka se tyka zalohovani - pokud jsem to dobre pochopil, pak
InnoDB databaze se musi zalohovat jinak... to je super pro
administratora, ktery umi administrovat datastor, ale nevi o datovem
modelovani (proc by to mel vedet?) a o strukture databazi uvnitr
datastoru... Tuto vytku jsem mohl smerovat i na PostgreSQL z duvodu LO,
jenze ty tam byly z historickeho hlediska (a nyni se vesmes jiz
nepouzivaji), navic uz to neni pravda, a co hlavne neimplementuji se
tyto veci takto blbe v dnesni dobe...

	Dale jsem si prostudoval dokument - 
http://www.mysql.org/documentation/mysql/bychapter/manual_Introduction.html

	a krome mist, kde jsem si mohl rici, no co uz, neda se nic delat, mne
zaujalo toto (a obcas dost nadzdvihlo ze zidle):

Foreign keys...
Disadvantages: 

Mistakes, which are easy to make in designing key relations, can cause
severe problems na -for example, circular rules, or the wrong combination
of cascading deletes. 

- to neni pravda, od toho tu je detekce cyklu a dead-locku, aby si to
databaze ohlidala... - aplikace smi delat cokoli, databaze vsak muze
povolit jen neco...

A properly written application will make sure internally that it is not
violating referential integrity constraints before proceding with a
query. Thus, additional checks on the database level will only slow down
performance for such an application. 

- tak to je fakt zduvodneni... neni to nic jineho nez vyhovorka!

It is not uncommon for a DBA to make such a complex topology of
relations that it becomes very difficult, and in some cases impossible,
to back up or restore individual tables

- coze?!?!?! Muze nekdo toto tvrzeni necim podeprit?

Na jinem miste...

In 4.1
Foreign keys, including cascading delete. 

- o chybejicich moznostech nic, viz vyse...

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

.
.
.

Nasleduje docela hodne stupidni srovnani s PostgreSQL (to same pro mSQL,
ale to prilis neznam a asi tak 7 let jsem nepouzil):

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

- tak a tady si troufam rici, ze lzou! Srovnejte language-binding, 
srovnejte v cem lze psat stored-procedury/triggery, kde mame 
EmbeddedSQL atd...?

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. 

- lez! - mam databaze, ktere vacuum nepotrebuji roky a stale zabiraji 
ekvivalentni misto svemu zaplneni. 

MySQL Server supports more of the standard ODBC functions than
PostgreSQL. 

- je to pravda? Ja to nevim, ODBC nepouzivam...

MySQL Server has a much more sophisticated ALTER TABLE. 

- ale co na to standardy? Dival jsem se na syntaxi a fakt nic moc...

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. 

- co robustnost?
- tvrzeni, ze context switching je rychlejsi je otazka implementace
threadu, ne otazka vykonu databaze...

MySQL Server supports a compressed client/server protocol which improves
performance over slow links. 

- 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...

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. 

- nevsiml jsem si, ze bych mohl provozovat nativne databaze, ktere byly
vytvoreny verzi 3.X a honil na tom binarku verze 4.X... je to stejny
problem. Vetu proto povazuji za lez.

Standard usage in PostgreSQL is closer to ANSI SQL in some cases. 

- to 'in some cases' je silne podhodnoceno... - srovnani si muze udelat
kazdy sam, staci se divat na syntaxi co podporuje A OPRAVDU PROVADI
MySQL a na syntaxi v sql92.txt - BNF forma je pomerne citelna...

Much slower INSERT, DELETE, and UPDATE. 
- to uz jsme probirali, pri jednom klientovi to je pravda, pri vicero je
to lez! Empiricky prokazano - navic neexistence transakci a zpusob,
jakym se to ma na aplikacni urovni nahrazovat - je to popsano rovnez na
www.mysql.org je silne perfomance degradujici (zamykani celych
tabulek)... - navic a to si zrejme nikdo neuvedomuje je fakt, ze pokud
nemam 1 aplikaci, ktera k DB pristupuje => napr. zamykani dela ve
stejnem poradi, ale vice, moznost dead-locku, starvingu a jinych
'postrannich efektu' soubehu je vice nez realna...

	Toliko za mne. Dekuji, ale MySQL a jeji smer vyvoje nepovazuji za
datastor hodny nasazeni v beznem databazovem prostredi, jinem od
adresarovych sluzeb - jenze na toto mame normovane LDAP...

	Nevim proc, ale nemohu se zbavit dojmu, ze oficialni Dokumentace k
MySQL v nekterych mistech pripomina marketingove libive blaboly (jsme
lepsi nez konkurence) nez skutecne technicke informace. Takto si
technickou dokumentaci skutecne nepredstavuji...

	Pripoustim, ze v tomto srovnani je nekolik emoci, mam PostgreSQL rad,
netajim se tim. Necht pripadny oponent prosim vezme na vedomi, ze misto
PostgreSQL mohu dosadit Oracle, Sybase, zrejme i jine a mohu napsat
temer zcela shodny dokument. Proto prosim, nechci vyvolavat flame,
pojdme se bavit o technickych a provoznich aspektech vyse jmenovanych
veci a ne domnenkach a pomnenkach '-).

-----------------------------------------------------------------------
Ing. Pavel Janousek (PaJaSoft)                 FoNet, spol. s r. o.
Vyvoj software, Intranet / Internet          Sokolova 67, 619 00 Brno
E-mail: mailto:Janousek na FoNet.Cz             Tel.: +420  5  4324 4749
SMS:    mailto:P.Janousek na SMS.Paegas.Cz      Fax.: +420  5  4324 4751
WWW:    http://WWW.FoNet.Cz/               E-mail: mailto:Info na FoNet.Cz
-----------------------------------------------------------------------