blog documentation FAQ followers forum marketplace services youtube

Additional DBs

The Jamroom Network » Forum » Jamroom Developers » Page » Additional DBs
alt=
DannyA
@dannya
7 years ago
584 posts
Is there a way to easily register an additional db and still preserve JR's datastore? I'm looking to separate my transaction data into a different database for report generation and some analytics.
updated by @dannya: 08/07/17 04:27:10PM
brian
@brian
7 years ago
10,143 posts
You'd want to setup a MySQL replication slave server and run your queries from that slave server:

https://dev.mysql.com/doc/refman/5.7/en/replication-setup-slaves.html


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
alt=
DannyA
@dannya
7 years ago
584 posts
Don't you end up with the same data on both servers? The idea was to separate transactions data because it's a lot of microtransactions. It's causing a big load on main DB when we run reports
updated by @dannya: 05/08/17 10:49:47AM
brian
@brian
7 years ago
10,143 posts
DannyA:
Don't you end up with the same data on both servers? The idea was to separate transactions data because it's a lot of microtransactions. It's causing a big load on main DB when we run reports

That's correct - based on what you posted I thought was what you were looking for?


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
alt=
DannyA
@dannya
7 years ago
584 posts
I may have mis-communicated then. There are many microtransactions and very detailed reporting and analytics. So I want to separate that data so it doesn't slow down the main db when a report is generated. However, I still want to be able to query the new db using the Proxima APIs
brian
@brian
7 years ago
10,143 posts
DannyA:
I may have mis-communicated then. There are many microtransactions and very detailed reporting and analytics. So I want to separate that data so it doesn't slow down the main db when a report is generated. However, I still want to be able to query the new db using the Proxima APIs

Moving the data to a different DB won't do anything for performance - you need to either get a bigger server that can handle the load, or stream the data off to a replication slave. The reason is that in MySQL databases are just a logical abstraction - if your server is already suffering under load, splitting the data into multiple DB's will actually make a bit worse, since there is extra overhead for each DB that is "open".

Let me know if that helps.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
alt=
DannyA
@dannya
7 years ago
584 posts
So am I not getting the concept of a data warehouse?
brian
@brian
7 years ago
10,143 posts
A "data warehouse" would be a completely separate server from your live server, that would allow you run ad hoc queries on the data without worrying about messing up your production data.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
alt=
DannyA
@dannya
7 years ago
584 posts
Right. Isn't that my original question?

DannyA:
I'm looking to separate my transaction data into a different database for report generation and some analytics.
brian
@brian
7 years ago
10,143 posts
DannyA:
Right. Isn't that my original question?

No - it's not :)

You're confusing "database" with "server". A single MySQL server can contain hundreds of databases. Moving your "data warehouse" to a different database on the same server is not going to lessen the load on the server. You need to create a second server and load your data warehouse data on the second server. This way the queries you run against your data warehouse do not impact the performance of your main server.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
alt=
DannyA
@dannya
7 years ago
584 posts
You are correct. I was using them interchangeably , as I only run a single database on a server. So let me try again.

Is there a JR function that would allow me to connect to a different database on a different server. I want to preserve JR's ability to access that data via the Proxima API as well as by other modules; particularly around generating reports from transaction data.
brian
@brian
7 years ago
10,143 posts
You would have to do something custom - there's nothing in Jamroom that allows it to work with 2 different databases at the same time. Sorry!


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
alt=
DannyA
@dannya
7 years ago
584 posts
Whew. Ok thanks.

Tags