Geek Answers Handbook

Database replication for redundancy using a free database and Java with the Spring & Hibernate web application

I mean this:

On each server: (they are all configured the same way)

I place a load balancer in front of the servers and change the load balancer in case my main load balancer goes down.

I use Terracotta to replicate session information between servers. If the server goes down, the user should be able to continue working on another server, ideally, as if nothing had happened. What remains to be "solved" (since I did not actually test this and, for example, I do not know what I should use as load balancing), database replication is required.

If the user interacts with the application and the database changes, this change must be replicated to the database servers on other servers. How can I do it? Should I use MySQL PostgreSQL or something else (which is ideally free since we have a limited budget)? Does other things mean common sense?

Clarification: I am a cluster in order to get high availability in the first place, and I want to be able to add servers and use them all at the same time to get high scalability.

+6
java database mysql postgresql redundancy
source share
5 answers

Since you already use Terracotta , and you think the second database is a good idea (agreed upon), you can consider expanding the role of Terracotta. We have clients who use Terracotta to replicate the database. Here is a brief example / description, but I think they have stopped supporting customers for this product .:

http://www.terracotta.org/web/display/orgsite/TCCS+Asynchronous+Data+Replication

+4
source share

You are trying to create replication with multiple masters, which is a very bad idea, since any change in any database should be copied to any other database. This is terribly slow - on a single server you can get several hundred transactions per second using a pair of fast disks and RAID1 or RAID10. This can be a lot more if you have a good RAID controller with a cache with a battery. If you add the overhead of communication with all your servers, you will receive no more than a dozen transactions per second.

If you need high availability, you should go for a warm backup solution where you have a server that is replicated but not in use - when the main server dies, the replacement takes over. You may lose some recent transactions if your primary server dies.

You can also use one master, asynchronous replication of multiple slaves. Each database change must be performed on one core server. But you can have several read-only slave servers. Data on these slave servers can be multiple transactions per master, so you may also lose some recent transactions in the event of a server death.

PostgreSQL has both types of replication - warm standby using log shipping and one master, multiple slaves using slony.

Only if you have a very small number of records can you go for synchronous replication. It can also be installed for PostgreSQL using PgPool-II or Sequoia.

For more information, see the "High Availability, Load Balancing, and Replication" section of the Postgres documentation.

+2
source share

For my (Perl-managed) website, I use MySQL on two servers with database replication. Each MySQL server is slave and master at the same time. I did this for thoughtfulness, and not for performance, but the installation worked fine for the last 3 years, we had almost no downtime during this period.

Regarding Kent's question / comment: I use standard replication that comes with MySQL.

Regarding the failover mechanism: I am using DNSMadeEasy.com recovery functionality. I have a Perl script that runs every 5 minutes through cron, which checks if replication continues (as well as many other things like server loading, hard disk hardness, RAM usage, etc.). During normal operation, the faster of the two servers provides all the web pages. If the script detects that something is wrong with the server (or if the server is simply disconnected), DNSMadeEasy switches the DNS records so that the secondary server becomes the primary. After restoring the "real" primary server, MySQL automatically captures the missing database changes, and DNSMadeEasy automatically switches back.

+1
source share

Here is an idea. Read Theo Schlossnagul's book Accessible Internet Architecture .

What you offer is not a good idea.

Load balancers are expensive and not as valuable as they appear. Use something simpler for load balancing between servers (something like Wackamole ).

Instead of spoofing database replication, spend money on a reliable database server separately from your front-end web servers. Perform regular backups and in the very unlikely event of a database failure, return from regular backups as quickly as possible.

0
source share

AFAIK, MySQL makes work more scalable. See Documentation http://dev.mysql.com/doc/mysql-ha-scalability/en/ha-overview.html

And there is a blog where you can take a look at real life examples: http://highscalability.com/tags/mysql

0
source share

More articles:


All Articles