Transcript ObjectMMRS - Wagner Correa Ramos Blog

CHAR13 UK
Case Study on
Multi-Master/Sharding
Wagner Correa Ramos
Anderson Massaharu Shibata
Application architecture adopted by the Justice
Court of the State of Amazonas in Brazil, using
PostgreSQL in a decentralized approach
Case Study on
Multi-Master/Sharding
Speakers
Wagner Correa Ramos
Anderson Massaharu Shibata
Schedule
• Structure of the Brazilian Legal System and Amazonas State
• Network infrastructure in Amazonas's Justice Court
• Systems of the Amazonas's Justice Court (PJe and
PROJUDI)
• Database replication softwares
• OBJECTMMRS – Project Details
• OBJECTMMRS – Installation process
• Benefits for Amazonas's Justice Court and end-users
• Questions and Comments
Brazilian Legal System
Amazonas's
Justice Court
• 61 counties
• About 70 justice courts (Civel,
Criminal, Family, Special, etc)
• 19 Main Judges
• Population: 3.5 million (2012)
• IT staff for the database replication project design:
IT Director: Messias Augusto L. B. Andrade
IT Coordinators: Mauro Sales and Carlos André
IT Consultants : Alberto, Roberto, Jaidson
Network infrastructure in
Amazonas's Justice Court
• Large territory, the largest in land area in the
country with about 1.5 million km square,
equivalent to the area of France, Spain, Sweden
and Greece together.
• Little wired, only satellite network.
• Constant connectivity problems (lack of
electricity, burning antennas, storms, floods, etc.)
Network infrastructure
A research firm “Economist Intelligence Unit”, attached to the British
magazine “The Economist”, evaluates the preparedness of states to
receive foreign investment. Amazon appears in 23rd position in the
Infrastructure item, just ahead of Tocantins, Roraima, Pará and
Maranhão.
The precarious supply of electricity and the internet in the Amazon are
two bottlenecks that contribute to attest the veracity of the research.
According Assayag. "I am embarrassed when I go anywhere, Natal,
São Paulo, Brasilia ... and I'm on the internet, and I click open, this
speed reduces work time. Here, the time spent is much higher, since
power outages are constant, much more than we hear about, the
result of many years without changing the network” he said.
http://www.mundopositivo.com.br/noticias/20135727-amazonas_e_o_5_pior_estado_em_infraestrutura.html
Network Infrastructure
Systems of the
Amazonas's Justice Court
PJe (CNJ) - Electronic lawsuit
• PostgreSQL and Java
• New system, technologically better but still incomplete, can not
meet all kinds of processes. Its use was discontinued in
Amazonas State
• Database with about 200 tables
• Blobs inside database
PROJUDI – Other Electronic lawsuit
• MySQL and Java (old version)
• PostgreSQL and Java (new version)
• Parallel use of two versions until installing PostgreSQL version in
all counties.
• Database with 334 tables
• 2 Gb data and 226 Gb of BLOBs (pdf files)
• Blobs in filesystem, outside database
Systems of
the Amazonas's
Justice Court
•
•
•
Problems with infrastructure made ​it
impossible to use the systems centrally.
After all unsuccessful attempts to use systems using
centralized mode, such as WAN hardware
accelerators (Riverbed), etc., they started studying
the options for database replication.
The use of the systems in the courts of the Interior of
the State was dependent on the success of the
decentralization project.
Database replication
softwares
Database replication softwares evaluated:
•
•
•
•
•
Tungstein Replicator – Discarded for not working with multi-
master PostgreSQL
Slony I – Also discarded for not working in a multi-master
Bucardo – Worked multi-master, but it was discarded by the low
performance within the network conditions of the Amazon.
Oracle ODI – Worked multi-master, but it was discarded by the
low performance within the network conditions of the Amazon
Oracle GoldenGate – Does not work multi-master with
PostgreSQL
•
ObjectMMRS - “…worked both data replication as blobs in an
unmatchable speed…” (Jaidson)
Database replication
softwares
The first “proof-of-concept” with ObjectMMRS was
with two servers, one in Manaus and another in
Iranduba (Satellite link) running Pje system. The
POC turned into "production" because it was
never off. They replaced the system (PROJUDI
replaced Pje) but the database replication solution
was the same: ObjectMMRS
OBJECTMMRS – Project
Details
• Initial POC made​ with the PJe system, where data
and blobs were stored in the database.
ObjectMMRS configured with 2 engines on each
server, one for tables with data and another for
tables with blobs.
• With the PROJUDI system, blobs are on file system,
no longer in the database.
• Servers to the 61 counties were not available, so we
opted for the gradual implementation.
OBJECTMMRS – Project
Details
• The topology adopted was one central server
concentrating data from all counties and 61 servers
distributed by counties.
• Adopted the horizontal partition database for the
case of blobs (database sharding). Each server
stores its own region blobs (lawsuits scanned, etc)
that are replicated only to the central server.
• The remaining data is replicated to all servers on the
network without the use of horizontal partitioning.
OBJECTMMRS – Project
Details
• Both the PJe as PROJUDI systems were
developed for centralized use. To avoid INSERT
conflicts (resulting from asynchronous
replication), and considering that the tables had
adopted the use of IDs (generated sequences),
we decided to work with different bands
numbering on each server. Since we had 62
servers, the idea was numbered 100 at 100:
ALTER SEQUENCE sequence_name
INCREMENT BY 100 RESTART WITH X
OBJECTMMRS – Project
Details
• There is only one table where INSERT conflict still
occurs, which is a table that sets up the "parts" of
the process. This table has a primary key that is not
an ID generated sequence. It is a personal
document number (CPF).
• Administrators identify this situation by
ObjectMMRS WebAdmin module and do the
necessary maintenance.
OBJECTMMRS – Project
Details
• UPDATE conflicts do not need to be treated
given the nature of the application where 99% of
the updated data belong to the geographic region
where the local server is.
• All databases are prepared by IT in Manaus
before being sent to the Interior
• The ObjectMMRS software if needed can be
configured for automatic management of
UPDATE conflicts
OBJECTMMRS – Project
Details
Impact on business in the absence of network
• Very low, users always working on local network
with the closest server.
• Often a city gets one or even two days without any
network, but the use of the system is not affected,
the data is synchronized successfully after returning
network
Effect of database loss
• No risk of total data loss
• There is the risk of a partial loss of data that was not
replicated (acceptable due to the nature of the
application)
OBJECTMMRS – Installation
Process
System Requirements
•
•
•
Java Sun JVM 1.6 above
ObjectMMRS uses books editors metaphor: Editor:
Master Database, Subscriber: Slave Database,
Publications: Tables to be replicated and
Subscription : relationship from subscriber and
publications.(witch table will replicate to each slaves)
Tables to be replicated are needed for primary key
or unique index.
OBJECTMMRS – Installation
Process
System Requirements
•
•
Ensure that each server has different sequencing
range of primary keys, changing sequence auto
increment or using composite keys.
Since there are existing primary keys in all tables to
be replicated, they chose to change the sequence
auto increment. As we have 61 counties the
sequence auto increment was updated to 100, then
the server whith sequence starting in 1 will be 1, 101,
201, etc and starting in 2 will be 2, 102, 202 etc.
OBJECTMMRS – Installation
Process on PJe
Pj-e CNJ (Electronic Proceeding Juridic)
• Manufactured from CNJ (National Council of
Justice)
• PostgreSQL database
• Star replication topology
• 2 databases to be replicated, Installed with one
ObjectMMRS replication engine on each
database.
1. Simple data database named pje.
2. Complex data database (Blobs) named
pje_bin.
OBJECTMMRS – Installation
Process on PJe
• Do a dump from the existing main database
(pg_dump).
• Restore the dump into all new servers for initial data
load (pg_retore).
• Install ObjectMMRS engine in all servers that will be
Master (unzip).
• Create network aliases (Linux /etc/hosts).
OBJECTMMRS – Installation
Process on PJe
ObjectMMRS Engine properties
(objectmmrs.properties).
OBJECTMMRS – Installation
Process on PJe
ObjectMMRS database connections properties
(objectdb.properties)
OBJECTMMRS – Installation
Process on PJe
Create into objectmmrs schema all tables of
ObjectMMRS data dictionary
OBJECTMMRS – Installation
Process on PJe
Configure Slaves (Subscribers)
OBJECTMMRS – Installation
Process on PJe
Configure ObjectMMRS triggers, and publish all
tables to be replicated (publish) (publicar.sh file).
OBJECTMMRS – Installation
Process on PJe
Example of publicar.sql
OBJECTMMRS – Installation
Process on PJe
Configure each table that will be replicated to each
server
OBJECTMMRS – Installation
Process on PJe
• Update all sequences into servers, before going to
justice courts
• Start ObjectMMRS engine on LAN (Local Area
Network) to sync databases
• Stop ObjectMMRS engine and shutdown server, to
send to justice courts
• When servers are in counties, we just need to
change IP into network aliases (hosts file) and start
ObjectMMRS engine
OBJECTMMRS – Installation
Process on PJe
• Installed for 3 justice courts: Manaus, Iranduba
and Uarini.
• Installed 2 ObjectMMRS engine for each server
(1 engine for each database : pje, pje_bin).
• Utilized from march 2012 until November 2012.
• Challenge: Despite countie of Iranduba being
approximately 30 km from Manaus, the
communication is being done via satellite, then
the communication is very unstable.
• Changed to PROJUDI(PR) by having more
resources than Pje CNJ
OBJECTMMRS – Installation
Process on PROJUDI
PROJUDI
• Manufactured by the staff of the state of Paraná.
• Providing source code (open source).
• Possibility to migrate data from PROJUDI (CNJ)
used with MySQL database (First Software)
• PostgreSQL database.
• Star replication topology
• Replication:
• 1 database to replicate.
• Used rsync to synchronize all files (blobs).
OBJECTMMRS – Installation
Process on PROJUDI
• Initially installed 15 counties servers, because of
the difficult implementation logistic, for instance,
office space and transportation.
• All servers were installed synchronizing into a
LAN, so when it is sent to justice district, the
queue amount of transactions to be replicated is
small. Just transactions occurring when we send
the server to justice courts will be on queue.
• The installation process is the same from Pje
OBJECTMMRS – Installation
Process on PROJUDI
20 Servers, until now (54 installed servers)
• Stop ObjectMMRS engine on central node (Manaus).
• Dump data (pg_dump) from an existing Server
(Backup Server).
• Restore (pg_restore) database on new servers.
• Add new servers on hosts file (network aliases) on
central node(Manaus)
• Add/Configure new servers on central node
(Manaus).
• Create queue tables to new servers on central node
(Manaus).
• Move queue table from existing server, to queue
table to each new server.
OBJECTMMRS – Installation
Process on PROJUDI
• Install and configure ObjectMMRS engine into new server.
• Note: It will not be necessary to create all tables of data
dictionary, publish tables to replicate and configure central
node again since all this "datas" came from dump
(pg_dump).
• Update and configure ObjectMMRS data dictionary with
data from new servers.
• Start ObjectMMRS engine on central node (Manaus).
• Alter sequences auto increment of new servers.
• Start ObjectMMRS engine on new servers to sync
databases.
• Send new servers to justice districts.
• Edit IP on Central node network aliases file.
OBJECTMMRS – Integrating
MySQL and PostgreSQL data
MySQL and PostgreSQL data –
Customization
Benefits for Amazonas's
Justice Court and end-users
From Carlos Andre, actual IT Coordinator:
“Before deploying the PROJUDI (in decentralized
approach) in the interior of Amazonas, these localities were
living in a secular delay…”
“Thus, the constitutional principles are met, such as:
Celerity, Economicity, Transparency, Publicity. The
effective access to justice has been achieved and the
permanent absence of the Judge no more is necessary,
and inversely proportional to the number of cases carried
judged on a number increased by more than ten times the
physical process.”
Benefits for Amazonas's
Justice Court and end-users
“The advantages are numerous to be listed, and in this
brief text we are highlighting some of them. And before
finalizing put on display that information security is one
hundred percent guaranteed, with sites's backup,
redundancy, contingencies and crisis management. In
a past in which to avoid the sanctions of our Justice,
courts have been burned, now the practice of any
unlawful to circumvent and hinder the progress of the
suit was reduced to almost zero.”
The End
Questions
Comments
Thanks to 2Quadrant and all CHAR13 Organizers