[Maria-discuss] Question about MariaDB global reference In high complexity app and big DB Size
hi I am some question, i want some opinion below question We planning ERP solution convert oracle to mariaDB so erp application pattern and BIG DB SIZING IS ISSUE. Our IT background & goal 1) Apppliciation Complexity & Pattern: ERP Level complexity (high , OLTP & many batch) 2) DB SIZE : 10 TB Over (Single DB , NO sharding) 3) replication Gap issue ** My Question ** 1) Any Global reference is in MariaDB (on ERP level complexity and application pattern) 2) Any some reference in BIG DATA SIZING ? (EX: 5TB DB Exists in XX COMPARY e-commerce soluntion..) IS Only solution is DB Sharding? no big data global reference ?? 3) Replication performance : Our test result 2MB/SEC binlog written in master , so slave node get replication lag glorwing. (in row binlog format) ( 1master - 1 slave ) Any Body have experience some reference max transaction in no replication gap ? ( ex : 20MB / SEC but no replication gap our application experience..) : parellel slave thread is good performance in single domain-id and ordered method replicaiton ? ( how much better performance in parallel replication? (ex single repliation 2MB/SEC BINLOG => Replication GAP GROW but, parellel replicaiton is good in 10MB/Sec binlog write Environment) thanks a lot. regards, seung hoon yoo.
Hi, I wouldn't try to use MySQL for a 10TB single instance DB unless most of the data is historical data that isn't accessed frequently. Sharding generally won't work for such an application, because every table will likely need partitioned by different keys (ie you have a very large order table and invoice table and it doesn't make sense to shard the invoice table by order number when the most frequent access is by invoice number). MySQL is not good for batch processing of large amounts of data (ie ,year over year reports) because it lacks intra-query parallelism. MySQL could be a fit if the last say 90 days of data fit into the buffer pool. In such a case you could use a slave for OLAP reporting. You could partition the tables using the partitioning option and report on them using Shard-Query (http://shardquery.com) to get intra-query parallelism as it can access partitions of tables in parallel, and it knows how to efficiently join tables partitioned on different keys on a single node. --Justin On Wed, Mar 9, 2016 at 5:06 AM, Seung Hoon Yoo <sehyoo96@gmail.com> wrote:
hi I am some question, i want some opinion below question
We planning ERP solution convert oracle to mariaDB so erp application pattern and BIG DB SIZING IS ISSUE.
Our IT background & goal 1) Apppliciation Complexity & Pattern: ERP Level complexity (high , OLTP & many batch) 2) DB SIZE : 10 TB Over (Single DB , NO sharding) 3) replication Gap issue
** My Question ** 1) Any Global reference is in MariaDB (on ERP level complexity and application pattern)
2) Any some reference in BIG DATA SIZING ? (EX: 5TB DB Exists in XX COMPARY e-commerce soluntion..)
IS Only solution is DB Sharding? no big data global reference ??
3) Replication performance : Our test result 2MB/SEC binlog written in master , so slave node get replication lag glorwing. (in row binlog format) ( 1master - 1 slave ) Any Body have experience some reference max transaction in no replication gap ? ( ex : 20MB / SEC but no replication gap our application experience..)
: parellel slave thread is good performance in single domain-id and ordered method replicaiton ?
( how much better performance in parallel replication?
(ex single repliation 2MB/SEC BINLOG => Replication GAP GROW but, parellel replicaiton is good in 10MB/Sec binlog write Environment)
thanks a lot.
regards, seung hoon yoo.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Hi, Maybe Maxscale can help you to (auto-)shared on a dozen of servers, take a look on it. Regards, Christophe De : Maria-discuss [mailto:maria-discuss-bounces+christophe.le.roux=fr.clara.net@lists.launchpad.net] De la part de Seung Hoon Yoo Envoyé : mercredi 9 mars 2016 11:07 À : mariadb-discuss <maria-discuss@lists.launchpad.net> Objet : [Maria-discuss] Question about MariaDB global reference In high complexity app and big DB Size hi I am some question, i want some opinion below question We planning ERP solution convert oracle to mariaDB so erp application pattern and BIG DB SIZING IS ISSUE. Our IT background & goal 1) Apppliciation Complexity & Pattern: ERP Level complexity (high , OLTP & many batch) 2) DB SIZE : 10 TB Over (Single DB , NO sharding) 3) replication Gap issue ** My Question ** 1) Any Global reference is in MariaDB (on ERP level complexity and application pattern) 2) Any some reference in BIG DATA SIZING ? (EX: 5TB DB Exists in XX COMPARY e-commerce soluntion..) IS Only solution is DB Sharding? no big data global reference ?? 3) Replication performance : Our test result 2MB/SEC binlog written in master , so slave node get replication lag glorwing. (in row binlog format) ( 1master - 1 slave ) Any Body have experience some reference max transaction in no replication gap ? ( ex : 20MB / SEC but no replication gap our application experience..) : parellel slave thread is good performance in single domain-id and ordered method replicaiton ? ( how much better performance in parallel replication? (ex single repliation 2MB/SEC BINLOG => Replication GAP GROW but, parellel replicaiton is good in 10MB/Sec binlog write Environment) thanks a lot. regards, seung hoon yoo.
Hi, Do you mean schema routing? Because that won't allow you to join between schemata. You generally need to join OE to INV to PO, etc in an ERP system. Sharding by schema won't help. You would end up having to use multi-master replication to get all the data back on a single node, then report on it, and you would be again be stuck with single threaded queries for large reports. I very much doubt that max scale could help with this, but if you have some sort of proof-of-concept or example to back up the suggestion it would be welcome as I'm unaware of such a solution. --Justin On Wed, Mar 9, 2016 at 6:47 AM, Christophe Le Roux < christophe.le.roux@fr.clara.net> wrote:
Hi,
Maybe Maxscale can help you to (auto-)shared on a dozen of servers, take a look on it.
Regards,
Christophe
*De :* Maria-discuss [mailto:maria-discuss-bounces+christophe.le.roux= fr.clara.net@lists.launchpad.net] *De la part de* Seung Hoon Yoo *Envoyé :* mercredi 9 mars 2016 11:07 *À :* mariadb-discuss <maria-discuss@lists.launchpad.net> *Objet :* [Maria-discuss] Question about MariaDB global reference In high complexity app and big DB Size
hi I am some question, i want some opinion below question
We planning ERP solution convert oracle to mariaDB
so erp application pattern and BIG DB SIZING IS ISSUE.
Our IT background & goal
1) Apppliciation Complexity & Pattern: ERP Level complexity (high , OLTP & many batch)
2) DB SIZE : 10 TB Over (Single DB , NO sharding)
3) replication Gap issue
** My Question **
1) Any Global reference is in MariaDB (on ERP level complexity and application pattern)
2) Any some reference in BIG DATA SIZING ? (EX: 5TB DB Exists in XX COMPARY e-commerce soluntion..)
IS Only solution is DB Sharding? no big data global reference ??
3) Replication performance
: Our test result 2MB/SEC binlog written in master , so slave node get replication lag glorwing. (in row binlog format)
( 1master - 1 slave )
Any Body have experience some reference max transaction in no replication gap ?
( ex : 20MB / SEC but no replication gap our application experience..)
: parellel slave thread is good performance in single domain-id and ordered method replicaiton ?
( how much better performance in parallel replication?
(ex single repliation 2MB/SEC BINLOG => Replication GAP GROW but, parellel replicaiton is good in 10MB/Sec binlog write Environment)
thanks a lot.
regards, seung hoon yoo.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Hi, And generally BOM is implemented using an adjacency model, and MySQL/MariaDB lack SQL:2003 support for recursive queries, and lack CONNECT BY .. - thus there is no easy way to access a BOM in MariaDB. --Justin On Wed, Mar 9, 2016 at 6:59 AM, Justin Swanhart <greenlion@gmail.com> wrote:
Hi,
Do you mean schema routing? Because that won't allow you to join between schemata. You generally need to join OE to INV to PO, etc in an ERP system. Sharding by schema won't help. You would end up having to use multi-master replication to get all the data back on a single node, then report on it, and you would be again be stuck with single threaded queries for large reports.
I very much doubt that max scale could help with this, but if you have some sort of proof-of-concept or example to back up the suggestion it would be welcome as I'm unaware of such a solution.
--Justin
On Wed, Mar 9, 2016 at 6:47 AM, Christophe Le Roux < christophe.le.roux@fr.clara.net> wrote:
Hi,
Maybe Maxscale can help you to (auto-)shared on a dozen of servers, take a look on it.
Regards,
Christophe
*De :* Maria-discuss [mailto:maria-discuss-bounces+christophe.le.roux= fr.clara.net@lists.launchpad.net] *De la part de* Seung Hoon Yoo *Envoyé :* mercredi 9 mars 2016 11:07 *À :* mariadb-discuss <maria-discuss@lists.launchpad.net> *Objet :* [Maria-discuss] Question about MariaDB global reference In high complexity app and big DB Size
hi I am some question, i want some opinion below question
We planning ERP solution convert oracle to mariaDB
so erp application pattern and BIG DB SIZING IS ISSUE.
Our IT background & goal
1) Apppliciation Complexity & Pattern: ERP Level complexity (high , OLTP & many batch)
2) DB SIZE : 10 TB Over (Single DB , NO sharding)
3) replication Gap issue
** My Question **
1) Any Global reference is in MariaDB (on ERP level complexity and application pattern)
2) Any some reference in BIG DATA SIZING ? (EX: 5TB DB Exists in XX COMPARY e-commerce soluntion..)
IS Only solution is DB Sharding? no big data global reference ??
3) Replication performance
: Our test result 2MB/SEC binlog written in master , so slave node get replication lag glorwing. (in row binlog format)
( 1master - 1 slave )
Any Body have experience some reference max transaction in no replication gap ?
( ex : 20MB / SEC but no replication gap our application experience..)
: parellel slave thread is good performance in single domain-id and ordered method replicaiton ?
( how much better performance in parallel replication?
(ex single repliation 2MB/SEC BINLOG => Replication GAP GROW but, parellel replicaiton is good in 10MB/Sec binlog write Environment)
thanks a lot.
regards, seung hoon yoo.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Yeah I’m sorry, i have mixed the postgresql-XC partitionning feature and the Maxscale one. Regards, Christophe De : Justin Swanhart [mailto:greenlion@gmail.com] Envoyé : mercredi 9 mars 2016 12:59 À : Christophe Le Roux <christophe.le.roux@fr.clara.net> Cc : Seung Hoon Yoo <sehyoo96@gmail.com>; mariadb-discuss <maria-discuss@lists.launchpad.net> Objet : Re: [Maria-discuss] Question about MariaDB global reference In high complexity app and big DB Size Hi, Do you mean schema routing? Because that won't allow you to join between schemata. You generally need to join OE to INV to PO, etc in an ERP system. Sharding by schema won't help. You would end up having to use multi-master replication to get all the data back on a single node, then report on it, and you would be again be stuck with single threaded queries for large reports. I very much doubt that max scale could help with this, but if you have some sort of proof-of-concept or example to back up the suggestion it would be welcome as I'm unaware of such a solution. --Justin On Wed, Mar 9, 2016 at 6:47 AM, Christophe Le Roux <christophe.le.roux@fr.clara.net<mailto:christophe.le.roux@fr.clara.net>> wrote: Hi, Maybe Maxscale can help you to (auto-)shared on a dozen of servers, take a look on it. Regards, Christophe De : Maria-discuss [mailto:maria-discuss-bounces+christophe.le.roux<mailto:maria-discuss-bounces%2Bchristophe.le.roux>=fr.clara.net@lists.launchpad.net<mailto:fr.clara.net@lists.launchpad.net>] De la part de Seung Hoon Yoo Envoyé : mercredi 9 mars 2016 11:07 À : mariadb-discuss <maria-discuss@lists.launchpad.net<mailto:maria-discuss@lists.launchpad.net>> Objet : [Maria-discuss] Question about MariaDB global reference In high complexity app and big DB Size hi I am some question, i want some opinion below question We planning ERP solution convert oracle to mariaDB so erp application pattern and BIG DB SIZING IS ISSUE. Our IT background & goal 1) Apppliciation Complexity & Pattern: ERP Level complexity (high , OLTP & many batch) 2) DB SIZE : 10 TB Over (Single DB , NO sharding) 3) replication Gap issue ** My Question ** 1) Any Global reference is in MariaDB (on ERP level complexity and application pattern) 2) Any some reference in BIG DATA SIZING ? (EX: 5TB DB Exists in XX COMPARY e-commerce soluntion..) IS Only solution is DB Sharding? no big data global reference ?? 3) Replication performance : Our test result 2MB/SEC binlog written in master , so slave node get replication lag glorwing. (in row binlog format) ( 1master - 1 slave ) Any Body have experience some reference max transaction in no replication gap ? ( ex : 20MB / SEC but no replication gap our application experience..) : parellel slave thread is good performance in single domain-id and ordered method replicaiton ? ( how much better performance in parallel replication? (ex single repliation 2MB/SEC BINLOG => Replication GAP GROW but, parellel replicaiton is good in 10MB/Sec binlog write Environment) thanks a lot. regards, seung hoon yoo. _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net<mailto:maria-discuss@lists.launchpad.net> Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Thanks for answers, Justin and Christophe. How about below link article https://en.wikipedia.org/wiki/List_of_ERP_software_packages some erp open source s/w is developing in Mysql/MariaDB or PostgreSQL . windows function and recursive SQL is hard thing but i think using user variables is helpful that make sense ( posssible, but Ineffective) http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/ . shard-query is helpful i think in reporting job in ERP. . and how about my question above mail , max replication latency? in binlog write measure. ( 2MB / sec binlog written, so replication gap glow is make sense? i think Depending on the environment of network and hardware.) and parallel slave thread is helpful in 10~20mb/sec bin log file has written ??) 2016-03-09 21:47 GMT+09:00 Christophe Le Roux < christophe.le.roux@fr.clara.net>:
Yeah I’m sorry, i have mixed the postgresql-XC partitionning feature and the Maxscale one.
Regards,
Christophe
how about postgreSQL-XC and Maxscale mixing one ? that was working good? what is your application ? sorry, i am not good English, so am i understanding your reply ? :)
*De :* Justin Swanhart [mailto:greenlion@gmail.com] *Envoyé :* mercredi 9 mars 2016 12:59 *À :* Christophe Le Roux <christophe.le.roux@fr.clara.net> *Cc :* Seung Hoon Yoo <sehyoo96@gmail.com>; mariadb-discuss < maria-discuss@lists.launchpad.net> *Objet :* Re: [Maria-discuss] Question about MariaDB global reference In high complexity app and big DB Size
Hi,
Do you mean schema routing? Because that won't allow you to join between schemata. You generally need to join OE to INV to PO, etc in an ERP system. Sharding by schema won't help. You would end up having to use multi-master replication to get all the data back on a single node, then report on it, and you would be again be stuck with single threaded queries for large reports.
I very much doubt that max scale could help with this, but if you have some sort of proof-of-concept or example to back up the suggestion it would be welcome as I'm unaware of such a solution.
--Justin
On Wed, Mar 9, 2016 at 6:47 AM, Christophe Le Roux < christophe.le.roux@fr.clara.net> wrote:
Hi,
Maybe Maxscale can help you to (auto-)shared on a dozen of servers, take a look on it.
Regards,
Christophe
*De :* Maria-discuss [mailto:maria-discuss-bounces+christophe.le.roux= fr.clara.net@lists.launchpad.net] *De la part de* Seung Hoon Yoo *Envoyé :* mercredi 9 mars 2016 11:07 *À :* mariadb-discuss <maria-discuss@lists.launchpad.net> *Objet :* [Maria-discuss] Question about MariaDB global reference In high complexity app and big DB Size
hi I am some question, i want some opinion below question
We planning ERP solution convert oracle to mariaDB
so erp application pattern and BIG DB SIZING IS ISSUE.
Our IT background & goal
1) Apppliciation Complexity & Pattern: ERP Level complexity (high , OLTP & many batch)
2) DB SIZE : 10 TB Over (Single DB , NO sharding)
3) replication Gap issue
** My Question **
1) Any Global reference is in MariaDB (on ERP level complexity and application pattern)
2) Any some reference in BIG DATA SIZING ? (EX: 5TB DB Exists in XX COMPARY e-commerce soluntion..)
IS Only solution is DB Sharding? no big data global reference ??
3) Replication performance
: Our test result 2MB/SEC binlog written in master , so slave node get replication lag glorwing. (in row binlog format)
( 1master - 1 slave )
Any Body have experience some reference max transaction in no replication gap ?
( ex : 20MB / SEC but no replication gap our application experience..)
: parellel slave thread is good performance in single domain-id and ordered method replicaiton ?
( how much better performance in parallel replication?
(ex single repliation 2MB/SEC BINLOG => Replication GAP GROW but, parellel replicaiton is good in 10MB/Sec binlog write Environment)
thanks a lot.
regards, seung hoon yoo.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (3)
-
Christophe Le Roux -
Justin Swanhart -
Seung Hoon Yoo