mySQL INNODB relaties tussen verschillende database

[Verwijderd]

Voor een project waar ik aan werk ben ik aan het experimenteren met database optimalisatie. Op dit moment lijkt de database te groot te worden waarop wij de database willen opdelen in meerdere database. Dit is mogelijk door iedere gebruiker een eigen database te geven. De gegevens welke generiek zijn staan natuurlijk in de algemene database. Hierin staan bijvoorbeeld de beschikbare talen en landen.

Nu bouw ik de nieuwe database volledig op met INNODB relaties. De onderstaande relatie is al succesvol aangemaakt:

MySQL:

ALTER TABLE `productsText` ADD FOREIGN KEY ( `productIdProduct` ) REFERENCES `client`.`products` (`productId`) ON DELETE CASCADE ON UPDATE CASCADE;

De onderstaande code zou ik willen uitvoeren maar geeft foutmelding.

MySQL:

ALTER TABLE `productsText` ADD FOREIGN KEY ( `productIdLanguage` ) REFERENCES `main`.`languages` (`languageId`) ON DELETE CASCADE ON UPDATE CASCADE;

code:

#1005 - Can't create table './client/#sql-4d2d_1732bc.frm' (errno: 150)

Kan iemand mij vertellen of hetgeen ik wil bereiken uberhaubt mogelijk is?

[ Voor 1% gewijzigd door Verwijderd op 28-04-2011 23:40 . Reden: Verkeerde query geplakt ]

[RobIII]

Ik kan me vergissen maar zover ik weet kan geen enkel RDBMS dat. Wat als database B offline gaat als A relaties naar B heeft?

Verwijderd schreef op donderdag 28 april 2011 @ 23:36:
Op dit moment lijkt de database te groot te worden

In terms of MB's of aantallen tabellen o.i.d.?

Verwijderd schreef op donderdag 28 april 2011 @ 23:36:
Dit is mogelijk door iedere gebruiker een eigen database te geven.

Kun je daar wat specifieker in zijn? Want dit klinkt als user_a.table_a en user_b.table_a achtige constructies. Waarom zou dit niet gewoon in "main.table_x" kunnen met een FK naar de users tabel?

Als je het hebt over "lokale databases" oid. kun je dan niet beter naar replicatie kijken? En anders moet je eens kijken naar zaken als partitioning ofzo. Het zou dan ook handig zijn als je "de database te groot te worden waarop wij de database willen opdelen in meerdere database" beter qualificeert. Wat is "te groot" en waarom kom je dan bij "opdelen in meerdere databases"? Die laatste conclusie heb ik nog nooit getrokken in ieder geval; ook niet bij huge-ass databases.

[ Voor 141% gewijzigd door RobIII op 29-04-2011 00:00 ]

[storeman]

Ik snap sowieso niet zo goed hoe dit performanceproblemen kan geven. Waneer je de databases kan scheiden, kun je de tabellen scheiden. Hierdoor wordt de dataset beperkt en zou je performance weer op niveau moeten komen. Hoe zou het scheiden op database niveau (maar met behoud van relaties) de performance moeten verbeteren?

Wanneer is je performance zo slecht dat je aan dergelijke optimalisaties zit te denken?

Kijk eens naar postgresql, bij ingewikkelde zaken en zwaardere load een stuk betrouwbaarder. Daarnaast ondersteund postgresql schema's wat een soort van oplossing voor jou probleem kan zijn.

[NMe]

Splitsen in meerdere databases gaat alleen maar meer overhead geven, niet minder.

En wat jij wil kan vast wel als je een view maakt naar de tabellen van database 1 in database 2 en andersom, maar stiekem schiet je daar dus niks mee op in performance.

[RobIII]

NMe schreef op vrijdag 29 april 2011 @ 00:03:
En wat jij wil kan vast wel als je een view maakt naar de tabellen van database 1 in database 2 en andersom

Ook dat kan, AFAIK althans, niet.

quote: http://dev.mysql.com/doc/...eign-key-constraints.html

Foreign keys definitions are subject to the following conditions:
- Both tables must be InnoDB tables and they must not be TEMPORARY tables.
- InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan.*
...
Note that it will fail if you try to insert data into a table that has a foreign key constraint where the foreign table is a view.

* Dat zou evt. misschien nog met een materialized view kunnen, maar dan nog; en voor je referential integrity ga je dan al helemaal nat omdat een materialized view niet gegarandeerd "bij" is...

[ Voor 67% gewijzigd door RobIII op 29-04-2011 00:12 ]

[ReenL]

Als je de database opsplitst doe het dan helemaal.

Databases zijn niet gemaakt om bewust van elkaar te zijn, je moet elke database zien als een losse planeet, tuurlijk kan je informatie en materie tussen 2 planeten uitwisselen, maar daar heb je wel een "tool" voor nodig.

Hiermee wil ik zeggen dat het niet handig is om data uit te wisselen tussen je 2 "gebruikers". Afhankelijk van je applicatie is dat een goed of een slecht idee. Zo zal een applicatie als facebook niet voor elke gebruiker een nieuwe "planeet" maken.

Als het echter gebruikers alsin "bedrijven" dan zou je elk bedrijf een eigen tabel kunnen geven om zo de security wat op te schroeven door het bedrijf alleen rechten te geven binnen zijn eigen database. Het kan ook handig zijn als de applicatie op verschillende plaatsen, verschillende "gebruikers" zou moeten gaan draaien.

Om terug te komen op jou oplossing:
Stel ik wil productsText joinen met languages, moet ik dan de connectie naar de database "client" of de database "main" connecten? Dit is ook gelijk de reden waarom het niet kan.

[borft]

hoe groot is je db al, dat je schalingsproblemen krijgt? Meestal zijn performance problemen bij grote tabellen het gevolg van slecht indeces en/of slechte queries (die de indeces niet gebruiken).

Uit ervaring kan ik stellen dan met innodb tabellen met 1 miljard records nog prima snel kunnen zijn. Overigens kan je overwegen om bij hele grote tabellen je foreign keys uit te zetten, dat scheelt flink op je performance. Je moet je alleen dan wel realiseren dat je op applicatie niveau consistentie af moet dwingen.