# Should database be on its own hardware?



## fred974 (Sep 17, 2018)

Hello all,

I have 1 PowerEdge R610 Production server that hosts 24 WordPress websites, 2 ticket system and 2 CRM and 1 postfix email server and 1 /databases/mariadb101-server database
Each application is hosted inside a jail so I have  24 jails for the website, 1 for email 1 for database etc..
All the moment ALL my application use the single  /databases/mariadb101-server jail server.
I am worried that if anything happens to that database jail, I'll lose everything.
I am working on a solution to develop a more resilient system using 2 servers in a CARP scenario where the server is mirrored..

My question is do I need to move the database to its own dedicated hardware or can I keep them inside the jail and create more DB jail in a cluster ?


----------



## SirDice (Sep 17, 2018)

fred974 said:


> can I keep them inside the jail and create more DB jail in a cluster


Think of what would happen if this single server dies completely. Then everything will be gone. Plan for a complete meltdown of a server. Hardware tends to break, usually at the worst possible time.


----------



## fred974 (Sep 17, 2018)

SirDice said:


> Think of what would happen if this single server dies completely


yes but I will have 2 servers tough.. Bellow is the concept


----------



## jbo (Sep 17, 2018)

fred974 said:


> I am worried that if anything happens to that database jail, I'll lose everything.


Maybe I am stating the obvious here - but just to make sure that the ground work is done before we dive deep(er) into HA and/or replication: Do you have a proper backup system in place? No matter how many servers you have running in parallel, you must (I know, "should") always have at least one off-site backup that allows you to restore everything.

Even if you move your database stuff to a dedicated server: This still applies. A cluster is never a replacement for a backup!


----------



## SirDice (Sep 17, 2018)

I'd put the databases on separate machines. Database servers have a different load-type than webservers and need to be tweaked differently. By splitting them up you can more easily tune machines for specific purposes.


----------



## fred974 (Sep 17, 2018)

joel.bodenmann said:


> Do you have a proper backup system in place?


Yes, we have a zfs differential backup script for that. We have a local snapshot for a quick recovery and another copy is sent offsite in case we lose the server.


----------



## fred974 (Sep 17, 2018)

SirDice said:


> I'd put the databases on separate machines


will you dedicate the whole machine as 1 database or create cluster in jail?
Also would you use one or 2 server? do you do master to master or master / slave


----------



## SirDice (Sep 17, 2018)

fred974 said:


> will you dedicate the whole machine as 1 database or create cluster in jail?


Multiple databases on the same server. 



fred974 said:


> Also would you use one or 2 server? do you do master to master or master / slave


More is better. A client of mine actually has 3 or 4 (plus one additional off-site). One master, one slave (can be made master), one slave lagging one hour behind and one 'slave' for historic data.


----------



## fred974 (Sep 17, 2018)

SirDice what technology do you use for the cluster? Do you use gallera or something else? How do you load balance between databases? I am aware if maxscale but I don't think it exist in FreeBSD.


----------



## SirDice (Sep 17, 2018)

No clustering. No load-balancing on the databases. The webservers are balanced using HAproxy, the databases are managed by hand (their choice). Their setup is a little different due to the nature of their websites. Normally you'd have 90% read and 10% writes on a database, we have it exactly the other way around, 90% write and 10% read.


----------



## fred974 (Sep 17, 2018)

SirDice said:


> Multiple databases on the same server.


How many database would you create on Dell R610 with 48GB RAM and 6x146GB  sas?
Would the database in jails?


----------



## SirDice (Sep 17, 2018)

fred974 said:


> How many database would you create on Dell R610 with 48GB RAM and 6x146GB sas?


That depends on the size of the databases. Ideally a whole database should fit into memory but that's not always possible. Our database servers have at least 380 GB RAM and even that isn't enough to fit everything in memory.


----------



## fred974 (Sep 17, 2018)

Hi,
Do you create the databases in separate jails? 
I currently have 25 individual databases in my database jail with a total of  2.5GB so far.


----------



## SirDice (Sep 17, 2018)

fred974 said:


> Do you create the databases in separate jails?


Nope, no need. Each database has its own set of credentials. (accounts from one database cannot be used to access other databases).


----------



## fred974 (Sep 17, 2018)

ok got it..in my scenario, do you think hardware database server is beneficial?


----------

