Real-time database replication

Question

We have a client who needs to perform several queries in the database in a short period of time, something approaching 40,000 requests in less than 2 hours.

I created a program that bridges the gap between the customer’s turnstiles and our database to validate the accesses, but the response time is horrible and many times the turnstiles fall and are dead (Henry 7x turnstiles).

I need to find a way to operate locally, copying the accesses of the external bank and making them available locally in real time (or almost). However, it needs to be safe and reliable and valid access data needs to go back to the external server to know which tickets were used.

What they tell me?

-------------------------------- EDIT --------------------------------

Turnstiles -> Database

Code that communicates the turnstiles with the database:

public partial class CatracasVNJ : Form
{

    private Alternativo kernel7x; //Declarando Kernel
    private DBConnect ConexaoBanco; //Instanciando Kernel


    int eventIndex;


    delegate void ViewLine(TextBox textBox, string texto);


    public CatracasVNJ()
    {
        InitializeComponent();
        kernel7x = new Alternativo(); //Instanciando Kernel
        ConexaoBanco = new DBConnect(); //Instanciando Kernel

        kernel7x.OnRegistro += onlineRegistryEventHandler;
        kernel7x.OnProgresso += this.progressEventHandler;

        this.eventIndex = -1; 
    }


    /*=======================================================================
     * 
     *                      ROTINAS AUXILIARES
     * 
     *=======================================================================*/

    private short booltoshort(bool value)  {
        if (value) return 1; else return 0;
     }

    private void preeche(TextBox textBox, string texto)
    {
        addViewLine(texto);
        return;
    }

    private void addViewLine(String linha)
    {
        var data = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
        txtMemo.AppendText(data + " - " + linha + "\r\n");
    }


    /*=======================================================================
     * 
     *                          ROTINAS DO FORM
     * 
     *=======================================================================*/

    public static string[] explode(string separator, string source)
    {
        return source.Split(new string[] { separator }, StringSplitOptions.None);
    }

    //BOTÕES DA INTERFACE
    private void btnIniciarCatracas_Click(object sender, EventArgs e)
    {
        // ações para preparar o botão e as rotas do inicia/para
        this.btnIniciarCatracas.Enabled = false;
        this.btnIniciarCatracas.Text = "PARAR";
        this.btnIniciarCatracas.Click -= new System.EventHandler(this.btnIniciarCatracas_Click);
        this.btnIniciarCatracas.Click += new System.EventHandler(this.btnPararCatracas_Click);

        // ação original do botão
        int i = 3000;
        string[] ListaIPS = ConexaoBanco.ListaCatracas(Int32.Parse("5"));
        foreach (string index in ListaIPS)
        {
            string[] resultado = explode(":", index);

            SComConfig _rConfig;
            _rConfig.IsCatraca = 0;

            _rConfig.ModoComunicacao = SModoComunicacao.cmcOnOff;
            _rConfig.TipoComunicacao = STipoComunicacao.ctcTcpIp;
            _rConfig.Tcp.Ip = resultado[0].ToString(); 
            _rConfig.Tcp.MAC = "";
            _rConfig.Tcp.Porta = i;
            _rConfig.Serial.NumeroRelogio = 1;

            int indexAux = kernel7x.get_AdicionaCardTcpIp(_rConfig.Tcp.Ip,
                _rConfig.Tcp.MAC, _rConfig.Tcp.Porta, false, _rConfig.ModoComunicacao);

            if (indexAux >= 0)
            {
                kernel7x.SetSincronizar(indexAux, false);

                addViewLine("Catraca adicionada: " + indexAux + " IP: " + resultado[0] + " Porta: " + i + "\n\r");
                listaCatracas.Items.Add("Catraca " + indexAux, 3);
            }
            else
            {
                addViewLine("Falha: " +
                    kernel7x.ErrorDescription(kernel7x.KernelLastError));
            }
            i++;
        }
        this.btnIniciarCatracas.Enabled = true;
    }

    private void btnPararCatracas_Click(object sender, EventArgs e)
    {
        this.btnIniciarCatracas.Enabled = false;
        string[] ListaIPS = ConexaoBanco.ListaCatracas(Int32.Parse("5"));
        int i = 0;
        foreach (string index in ListaIPS)
        {
            string[] resultado = explode(":", index);
            kernel7x.get_RemoveCard(i);
            addViewLine("Catraca removida: " + resultado[0].ToString() + "\n\r");
            i++;
        }
        this.btnIniciarCatracas.Text = "INICIAR";
        this.btnIniciarCatracas.Click -= new System.EventHandler(this.btnPararCatracas_Click);
        this.btnIniciarCatracas.Click += new System.EventHandler(this.btnIniciarCatracas_Click);

        this.btnIniciarCatracas.Enabled = true;
    }


    private void escreveListaIPS(String linha)
    {
        //textBox1.AppendText(linha + "\r\n");
    }


    /*=======================================================================
     * 
     *                  TRATAMENTO DE EVENTOS DO KERNEL 
     * 
     *=======================================================================*/


    public void progressEventHandler(int pThreadIndex, int pByte, int pByteMax, int pBuffer, int pBufferMax)
    {

        Application.DoEvents();
    }

    private void onlineRegistryEventHandler(int pThreadIndex) {
        if (this.eventIndex == -1)
        {
            this.eventIndex = pThreadIndex;
            Thread regThread = new Thread(this.onlineRegistryEventHandlerThd);
            regThread.Start();

        }
    }

    private void onlineRegistryEventHandlerThd()
    {

        int pThreadIndex = eventIndex;


        ViewLine viewL = new ViewLine(this.preeche);

        try
        {
            //Recebe solicitação do kernel
            SRegistro registro;
            /*
             * Variáveis do Sregistros
             */
            bool Saida = false;
            bool MasterLiberou = false;
            bool FuncaoLiberou = false;
            bool AcessoNegado = false;


            //txtMemo.Invoke(viewL, txtMemo, "Pedido de acesso recebido : " + pThreadIndex);

            //Seu tratamento de acesso e ponto
            SResposta resposta = new SResposta();

            //Recebe do relógio ou catraca em tempo real
            this.kernel7x.RegistroOn(pThreadIndex,
                out registro.NumeroRelogio,
                out registro.Funcao,
                out registro.Matricula,
                out registro.DataHora,
                out registro.Flag,
                out Saida,
                out MasterLiberou,
                out FuncaoLiberou,
                out AcessoNegado,
                out registro.Tipo.FonteEntrada,
                out registro.Tipo.TipoNegado);
            registro.Tipo.Saida = booltoshort(Saida);
            registro.Tipo.MasterLiberou = booltoshort(MasterLiberou);
            registro.Tipo.FuncaoLiberou = booltoshort(FuncaoLiberou);
            registro.Tipo.AcessoNegado = booltoshort(AcessoNegado);

            resposta.Mensagem = "***** VNJ ***** Acesso Liberado!";
            resposta.Tempo = Convert.ToByte(1);

            int catraca = pThreadIndex;
            int arena;
            int.TryParse("5", out arena);

            long acessoConvertido;
            var acesso = registro.Matricula;
            try
            {
                acessoConvertido = Convert.ToInt64(acesso);

                //txtMemo.Invoke(viewL, txtMemo, "Catraca : " + catraca + "Acesso : " + acessoConvertido + "Arena : " + arena);
                string[] RespostaAcesso = ConexaoBanco.LiberaCatracaUnificada(catraca, arena, acessoConvertido);

                if (Int32.Parse(RespostaAcesso[0]) == 0)
                {
                    kernel7x.RespostaOn(pThreadIndex, SAcessoOnline.canLibEntrada, resposta.Mensagem, resposta.Tempo);
                    // Retorno pro programa
                    txtMemo.Invoke(viewL, txtMemo, "Acesso LIBERADO para : " + acesso);
                } 
                else
                {
                    kernel7x.RespostaOn(pThreadIndex, SAcessoOnline.canNegado, RespostaAcesso[1].ToString(), resposta.Tempo);
                    // Retorno pro programa
                    txtMemo.Invoke(viewL, txtMemo, "Acesso bloqueado para : " + acesso);
                }
            }  

            catch (OverflowException)
            {
                Console.WriteLine("{0} Esta fora da range permitida para este tipo de variável.", acesso);
            }
            catch (FormatException)
            {
                Console.WriteLine("Não foi possível converter.");
            }

        }
        catch (Exception e) {
           txtMemo.Invoke(viewL, txtMemo, "Exceção: " + e.Message);
        }

        eventIndex = -1;
    }

    private void button1_Click(object sender, EventArgs e)
    {
        // TESTA STORED PROCEDURE
        string[] RespostaAcesso = ConexaoBanco.LiberaCatracaUnificada(1, Int32.Parse("5"), 1);

        addViewLine(RespostaAcesso[0].ToString() + "\n\r");
        addViewLine(RespostaAcesso[1].ToString() + "\n\r");

        /// SEPARADOR //////////////////////////////////////
        txtMemo.AppendText("=========================================================== \n");
        ///////////////////////////////////////////////////

        // TESTA CONVERSÃO DE VARIAVEL
        long acessoConvertido;
        var acesso = "000000088973229041253";
        try
        {
            acessoConvertido = Convert.ToInt64(acesso);
            addViewLine("Convertio com sucesso: " + acessoConvertido);
            Console.WriteLine("Converted the {0} acesso '{1}' to the {2} acesso {3}.", acesso.GetType().Name, acesso, acessoConvertido.GetType().Name, acessoConvertido);
        }
        catch (OverflowException)
        {
            Console.WriteLine("{0} Esta fora da range permitida para este tipo de variável.", acesso);
        }
        catch (FormatException) 
        {
            Console.WriteLine("A {0} acesso '{1}' não está disponível para conversão.", acesso.GetType().Name, acesso);
        }

        // TESTA COMUNICAÇÃO COM A CATRACA
        string[] ListaIPS = ConexaoBanco.ListaCatracas(Int32.Parse("5"));
        int i = 0;
        foreach (string index in ListaIPS)
        {
            txtMemo.AppendText("=========================================================== \n");
            //
            kernel7x.RecebeTipoCatraca(i, out SStatusGiro pStatusGiro, out byte pTempoLiberacao);
            kernel7x.RecebeConfigDSP(i, out int pNivelSeguranca, out int pVelocidade, out int p2, out int p3, out bool p4, out bool pb);

            addViewLine("Catraca número: " + i);
            addViewLine("-");
            addViewLine("Status giro: " + pStatusGiro + "   Tempo de liberação: " + pTempoLiberacao);
            addViewLine("Nivel de segurança: " + pNivelSeguranca + "       Status: " + pb);
            i++;
        }
    }
}

Function connecting on the bank

Function code that is used to validate ticket or wallet access on turnstiles:

        //////////////////////////////////////////////
    // LiberaCatracaUnificada( Nrdispositivo , arena , acesso );
    //
    // Parametros:
    // @ pThreadIndex: Número da catraca
    // @ Arena: Número de identificação da arena
    // @ Acesso: Número do ingresso ou mifare
    //
    // Retornos da função:
    // [0] = Resposta da Stored Procedures
    // [1] = Mensagem de resposta da Stored Procedure
    //////////////////////////////////////////////
    public string[] LiberaCatracaUnificada(int pThreadIndex, int arena, long acesso)
    {
        // Pega o NrDispositivo da catraca
        var ListaIPs = ListaCatracas(arena);
        string[] IP = ListaIPs[pThreadIndex].TrimStart(':').Split(':');
        int NrDispositivo = Int32.Parse(IP[1]);

        // Pega a data atual
        var data = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

        // Prepara a query
        string query = "CALL permite_acesso('" + arena + "', '" + acesso + "', '" + NrDispositivo + "', '" + data + "')";

        // Abre conexão com o db
        if (this.OpenConnection() == true)
        {
            MySqlCommand cmd = new MySqlCommand(query, connection);
            MySqlDataReader dataReader = cmd.ExecuteReader();

            while (dataReader.Read())
            {
                string[] retornoStoredProcedure = new string[2];
                retornoStoredProcedure[0] = dataReader["RESPOSTA"].ToString();
                retornoStoredProcedure[1] = dataReader["MENSAGEM"].ToString();

                dataReader.Close();
                this.CloseConnection();

                return retornoStoredProcedure;
            }
        }
        else
        {
            string[] resposta = new string[] { "99", "FALHA AO CONSULTAR STORED PROCEDURE" };
            return resposta;
        }
        string[] resposta1 = new string[] { "98", "FALHA ENCONTRADA" };
        return resposta1;
    }

-------------------------------- EDIT 2 --------------------------------

Thanks for the return, so friend at first it is not possible to exchange the database at the time, we already felt the need to exchange it but due to several factors it is not possible to do this at the time.

I was thinking of doing something similar to what I said above, create a sort of queue and go getting the data and after the game send them back to the server.

However, the ideal scenario was to work fully online, but if it is possible to create in an effective way this "queue" can solve the problem I think.

I will post below the code of the Stored Procedure and the architecture of the access table.

Stored Procedure

BEGIN
DECLARE RESPOSTA INT DEFAULT -1;
DECLARE _tpingresso_id INT DEFAULT 0;
DECLARE _data INT DEFAULT 0;
DECLARE _tituloso_id INT DEFAULT 0;
DECLARE _ingressocategoria_id INT DEFAULT 0;
DECLARE _acesso_id INT DEFAULT 0;
DECLARE _acesso_status INT DEFAULT -1;
DECLARE _titulosociedade_acessostatus varchar(10) default '';
DECLARE _evento_id INT DEFAULT 0;
DECLARE _entrada_nome VARCHAR(100);
DECLARE _catraca_id INT DEFAULT 0;
DECLARE _entrada_id INT DEFAULT 0;
DECLARE _titulosociedade_id INT DEFAULT 0;
DECLARE _observacao varchar(200) default '';
DECLARE _num_acessos_permitidos int;
DECLARE _num_acessos_atual int;

SET _acesso_id = 0; 
set _catraca_id = 0;
set _num_acessos_permitidos = 1;

-- BUSCA DADOS DO EVENTO
select evento.evento_id into _evento_id from  evento WHERE DATA_REF BETWEEN evento.iniAcesso AND evento.fimAcesso AND evento.arena_id = ARENA limit 1;

-- SE EXISTIR O EVENTO
IF(_evento_id > 0) THEN

    -- BUSCA DADOS DA CATRACA
    SELECT arena_catraca.catraca_id, trim(arena_entrada.nome) into _catraca_id, _entrada_nome FROM arena_catraca inner join arena_entrada on arena_entrada.entrada_id = arena_catraca.entrada_id WHERE arena_catraca.nrDispositivo = CATRACA AND arena_catraca.arena_id = ARENA limit 1;

    -- SE ENCONTRADA A CATRACA
    IF(_catraca_id > 0) THEN 

        -- BUSCA ACESSO DO TIPO INGRESSO
        SELECT acesso_id INTO _acesso_id FROM acesso WHERE acesso.chaveAcesso = CODIGO and acesso.evento_id = _evento_id order by status limit 1;

            -- select count(*) into _num_acessos_atual from acesso where acesso.evento_id = _evento_id and acesso.chaveAcesso = CODIGO limit 1; 

        -- BUSCA DADOS DE SOCIEDADE
        IF (COALESCE(_acesso_id,0) <= 0) THEN

            -- BUSCA A CARTEIRINHA
            SELECT titulosociedade.tituloso_id,tpsocio.tpingresso_id
            INTO _tituloso_id, _tpingresso_id
            FROM titulosocar
            INNER JOIN titulosociedade on titulosocar.tituloso_id = titulosociedade.tituloso_id
            INNER JOIN tpsocio on tpsocio.tpsocio_id = titulosociedade.tpsocio_id
            WHERE titulosocar.nroMifare = CODIGO
            LIMIT 1;

            IF(_tituloso_id > 0) THEN 
                -- INSERE ACESSO DO SÓCIO
                insert into acesso (evento_id, tpingresso_id, tituloso_id, chaveAcesso, entidade_id, nroCarteira,status) 
                values(_evento_id, _tpingresso_id, _tituloso_id, CODIGO, 10, CODIGO,0);

                -- SELECIONA O ACESSO INSERIDO
                SELECT acesso_id INTO _acesso_id FROM acesso WHERE acesso.chaveAcesso = CODIGO and acesso.evento_id = _evento_id order by status limit 1;

            END IF;
        END IF;

        IF (COALESCE(_acesso_id,0) > 0) THEN



            -- BUSCA STATUS DO ACESSO
            select distinct case when coalesce(titulosociedade.statusAcesso,'L') in ('L') then coalesce(acesso.status,0) else 5 end, acesso.tpingresso_id
            into  _acesso_status, _tpingresso_id
            from acesso
            left join titulosociedade on titulosociedade.tituloso_id = acesso.tituloso_id
            where acesso.acesso_id = _acesso_id;

            -- VERIFICA O STATUS DO ACESSO
            if (coalesce(_acesso_status,-1) >= 0 ) THEN

                CASE
                    -- LIBERADO
                    WHEN _acesso_status = 0 THEN 
                        SET _entrada_id = 0;

                        -- BUSCA ENTRADA - SE PERMITIDO O ACESSO RETORNA UM INTEIRO
                        SELECT distinct arena_catraca_setor.entrada_id
                        INTO _entrada_id
                        FROM ( SELECT arena_catraca.* FROM arena_catraca WHERE arena_catraca.nrDispositivo = CATRACA AND arena_catraca.arena_id = ARENA) arena_catraca
                        inner join arena_entrada on arena_entrada.entrada_id = arena_catraca.entrada_id
                        inner join arena_catraca_setor on arena_catraca_setor.catraca_id = arena_catraca.catraca_id and arena_catraca_setor.setor_id in (select setor_id from arena_setortpingresso where tpingresso_id = _tpingresso_id);

                        -- SE ACESSO PERMITIDO
                        IF(coalesce(_entrada_id,0) > 0 ) THEN
                            SET _observacao = CONCAT( _observacao,'Acesso Permitido');
                            SET RESPOSTA = 0;

                            update acesso set status = 1 where acesso_id = _acesso_id;
                        -- SE ACESSO NEGADO PARA ESSE SETOR
                        else
                            SET _observacao = CONCAT(_observacao,'Acesso negado para esse setor');
                            SET RESPOSTA = 15;
                        end if;

                    -- TENTATIVA DUPLICADA DE ACESSO
                    WHEN _acesso_status = 1 THEN 
                        set _observacao = CONCAT(_observacao, 'Tentantiva duplicada de acesso');
                        set RESPOSTA = 1;
                    WHEN _acesso_status = 5 THEN 
                        set _observacao = CONCAT(_observacao, 'Pendências Financeiras');
                        set RESPOSTA = 5;
                    ELSE 
                        set _observacao = CONCAT(_observacao, 'Erro de leitura');
                        set RESPOSTA = 99;
                END CASE;
            ELSE
                SET _observacao = CONCAT( _observacao,'Status de Acesso inválido!');
                SET RESPOSTA = 22;
            END IF;
        ELSE
            IF(CODIGO = 3579281461 OR CODIGO = 82282900 OR CODIGO = 82282596 OR CODIGO = 82282580 OR CODIGO = 82282868 OR CODIGO = 82282564 OR CODIGO = 82282852 OR CODIGO = 82282548 OR CODIGO = 82282884) THEN
                INSERT INTO outrosacessos(evento_id) VALUES(_evento_id);
                SET _observacao = CONCAT( _observacao,'ACESSO MASTER');
                SET RESPOSTA = 0;
            ELSE
                SET _observacao = CONCAT( _observacao,'Acesso Não Encontrado!');
                SET RESPOSTA = 22;
            END IF;
        END IF;

        IF(_acesso_id > 0 and _catraca_id > 0 and _evento_id > 0) THEN
            insert into acesso_log (acesso_id, observacao, data, catraca_id, evento_id, resultado) values(_acesso_id, _observacao, DATA_REF, _catraca_id, _evento_id, RESPOSTA);
        END IF;
    ELSE
        set _observacao = 'Catraca não encontrada!';
        set RESPOSTA = 20;
    END IF;
ELSE
    set _observacao = 'Evento não encontrado!';
    set RESPOSTA = 20;
END IF;

SELECT RESPOSTA RESPOSTA, _observacao MENSAGEM, coalesce(_acesso_id,0) ACESSO_ID;

Table

DROP TABLE IF EXISTS acesso; CREATE TABLE acesso ( acesso_id int(11) NOT NULL AUTO_INCREMENT, tpingresso_id int(11) DEFAULT NULL, tpcategoria_id int(11) DEFAULT NULL, voucherkey int(11) DEFAULT NULL, chaveAcesso bigint(200) DEFAULT NULL, pedidoiteming_id int(11) DEFAULT NULL, entidade_id int(11) DEFAULT NULL, cadeira_id int(11) DEFAULT NULL, operador_id int(11) DEFAULT NULL, dtUtilizacao datetime DEFAULT NULL, nroCarteira int(11) DEFAULT NULL, valor decimal(15,2) DEFAULT NULL, vlDesconto decimal(15,4) DEFAULT NULL, vlTaxa decimal(15,4) DEFAULT NULL, vlTotal decimal(15,4) DEFAULT NULL, status int(11) DEFAULT NULL, evento_id int(11) DEFAULT NULL, tituloso_id int(11) DEFAULT NULL, tipoGeracao varchar(1) CHARACTER SET utf8 DEFAULT NULL, motivo varchar(200) CHARACTER SET utf8 DEFAULT NULL, nroMifare varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL, caixa_id int(11) DEFAULT NULL, pedidoitem_id int(11) DEFAULT NULL, ingressocategoria_id int(11) DEFAULT NULL, PRIMARY KEY (acesso_id), KEY tpingresso_id (tpingresso_id), KEY entidade_id (entidade_id), KEY cadeira_id (cadeira_id), KEY evento_id (evento_id), CONSTRAINT acesso_ibfk_1 FOREIGN KEY (tpingresso_id) REFERENCES arena_tipoingresso (tpingresso_id), CONSTRAINT acesso_ibfk_4 FOREIGN KEY (entidade_id) REFERENCES entidade (entidade_id), CONSTRAINT acesso_ibfk_5 FOREIGN KEY (cadeira_id) REFERENCES arena (arena_id), CONSTRAINT acesso_ibfk_7 FOREIGN KEY (evento_id) REFERENCES evento (evento_id) ) ENGINE=Innodb AUTO_INCREMENT=4624 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Answer 1

40,000 requests in two hours is not much, even if it was in 15 minutes, it would be about 50 requests per second.

So I think there’s plenty of room for improvement in your database, analyze your data structure, some index that’s missing, some unnecessary restriction.

If you really need to work with a distributed system, I have two recommendations, the first is using the SQLite-sync or go to a Nosql that has synchronization support like Realm, But before hand, you will have to implement changes in your structure, such as using Guid instead of Int32 for your keys.

Finally, if performance is really important to you, consider the possibility of using a PostgresQL in place of MySQL.

Another recommendation, if you can use the .net 4.6.1, is to adopt the Entity Framework Core 2.0 as ORM, as it will have to use the Sqlite in the client and PostgresQL/MySQL on a centralised basis, the EF Core 2.0 may help with the partitioning of queries, when applying global filters, as well as with the perfomance system, when using pre-compiled queries.