TUNING MYSQL

GUSTAVOCANALLI 19/09/2010 21:08:48
#353377
BOA NOITE.

ESTOU COM UMA TABELA COM A SEGUINTE ESTRUTURA:

CREATE TABLE 'mdl_user' (
'id' bigint(10) unsigned NOT NULL AUTO_INCREMENT,
'auth' varchar(20) NOT NULL DEFAULT [ô]manual[ô],
'confirmed' tinyint(1) NOT NULL DEFAULT [ô]0[ô],
'policyagreed' tinyint(1) NOT NULL DEFAULT [ô]0[ô],
'deleted' tinyint(1) NOT NULL DEFAULT [ô]0[ô],
'mnethostid' bigint(10) unsigned NOT NULL DEFAULT [ô]0[ô],
'username' varchar(100) NOT NULL DEFAULT [ô][ô],
'password' varchar(32) NOT NULL DEFAULT [ô][ô],
'idnumber' varchar(255) NOT NULL DEFAULT [ô][ô],
'firstname' varchar(100) NOT NULL DEFAULT [ô][ô],
'lastname' varchar(100) NOT NULL DEFAULT [ô][ô],
'email' varchar(100) NOT NULL DEFAULT [ô][ô],
'emailstop' tinyint(1) unsigned NOT NULL DEFAULT [ô]0[ô],
'icq' varchar(15) NOT NULL DEFAULT [ô][ô],
'skype' varchar(50) NOT NULL DEFAULT [ô][ô],
'yahoo' varchar(50) NOT NULL DEFAULT [ô][ô],
'aim' varchar(50) NOT NULL DEFAULT [ô][ô],
'msn' varchar(50) NOT NULL DEFAULT [ô][ô],
'phone1' varchar(20) NOT NULL DEFAULT [ô][ô],
'phone2' varchar(20) NOT NULL DEFAULT [ô][ô],
'institution' varchar(40) NOT NULL DEFAULT [ô][ô],
'department' varchar(30) NOT NULL DEFAULT [ô][ô],
'address' varchar(70) NOT NULL DEFAULT [ô][ô],
'city' varchar(20) NOT NULL DEFAULT [ô][ô],
'country' varchar(2) NOT NULL DEFAULT [ô][ô],
'lang' varchar(30) NOT NULL DEFAULT [ô]en_utf8[ô],
'theme' varchar(50) NOT NULL DEFAULT [ô][ô],
'timezone' varchar(100) NOT NULL DEFAULT [ô]99[ô],
'firstaccess' bigint(10) unsigned NOT NULL DEFAULT [ô]0[ô],
'lastaccess' bigint(10) unsigned NOT NULL DEFAULT [ô]0[ô],
'lastlogin' bigint(10) unsigned NOT NULL DEFAULT [ô]0[ô],
'currentlogin' bigint(10) unsigned NOT NULL DEFAULT [ô]0[ô],
'lastip' varchar(15) NOT NULL DEFAULT [ô][ô],
'secret' varchar(15) NOT NULL DEFAULT [ô][ô],
'picture' tinyint(1) NOT NULL DEFAULT [ô]0[ô],
'url' varchar(255) NOT NULL DEFAULT [ô][ô],
'description' text,
'mailformat' tinyint(1) unsigned NOT NULL DEFAULT [ô]1[ô],
'maildigest' tinyint(1) unsigned NOT NULL DEFAULT [ô]0[ô],
'maildisplay' tinyint(2) unsigned NOT NULL DEFAULT [ô]2[ô],
'htmleditor' tinyint(1) unsigned NOT NULL DEFAULT [ô]1[ô],
'ajax' tinyint(1) unsigned NOT NULL DEFAULT [ô]1[ô],
'autosubscribe' tinyint(1) unsigned NOT NULL DEFAULT [ô]1[ô],
'trackforums' tinyint(1) unsigned NOT NULL DEFAULT [ô]0[ô],
'timemodified' bigint(10) unsigned NOT NULL DEFAULT [ô]0[ô],
'trustbitmask' bigint(10) unsigned NOT NULL DEFAULT [ô]0[ô],
'imagealt' varchar(255) DEFAULT NULL,
'screenreader' tinyint(1) NOT NULL DEFAULT [ô]0[ô],
PRIMARY KEY ('id'),
UNIQUE KEY 'mdl_user_mneuse_uix' ('mnethostid','username'),
KEY 'mdl_user_del_ix' ('deleted'),
KEY 'mdl_user_con_ix' ('confirmed'),
KEY 'mdl_user_fir_ix' ('firstname'),
KEY 'mdl_user_las_ix' ('lastname'),
KEY 'mdl_user_cit_ix' ('city'),
KEY 'mdl_user_cou_ix' ('country'),
KEY 'mdl_user_las2_ix' ('lastaccess'),
KEY 'mdl_user_ema_ix' ('email'),
KEY 'mdl_user_aut_ix' ('auth'),
KEY 'mdl_user_idn_ix' ('idnumber')
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT=[ô]One record for each person[ô] AUTO_INCREMENT=33 ;

CADASTREI 400 MIL REGISTROS E ESTA LEVANDO CERTA DE 30 SEGUNDOS PARA ACESSAR OS DADOS.

COMO EU PODERIA REDUZIR O TEMPO DE ACESSO SIGNIFICATIVAMENTE ATRAVES DE UM INDICE?
NETMANIA 20/09/2010 08:55:55
#353384
Existem vários fatores que possam influenciar o desempenho:

  • HD da máquina (se é um de 5400 ou 7200 RPM)
  • Interface de comunicação do HD (se é IDE ou SATA, etc)
  • Quandade de processadores no equipamento
  • Etc

    Se você está achando 30 segundos para uma consulta ser executada? Eu tenho uma base de dados na emrpesa que tem mais de 84 milhões de alarmes, dependendo da consulta, esta retornar os dados em 5 minutos (com uma máquina Pentium HT e HD 7200 RPM SATA).

    Um jeito de podermos ajudar você, postando o SQL para ajudar no processo de análise.
    •
    GUSTAVOCANALLI 20/09/2010 12:22:46
    #353410
    SIM. MAS QUAL A MELHOR FORMA DE CRIAR UM ÍNDICE NESTA TABELA PARA MELHORAR O DESEMPENHO?

    OBRIGADO!!!
    AJSO 20/09/2010 13:29:04
    #353418
    Olha garantia de criação de indice não a melhor solução, vc teria que ter uma garantia de não exclusão de registro pois é o melhor processo para normalizar sua tabela não deixar excluir registros.

    Criação de indices é meio complicada e simples ao mesmo tempo. Tabelas em cascata, indices com UUID() (Complexos)

    Indice tendo Chave Primária e Incremento de 1 é o ideal para controle de indice mas cada caso é um caso.

    Outra dica sobre Chave Primária é evitar chaves primarias complexas. Como o UUID() de chave Primária de sua tabela.

    Acredito que todas as consultas devem partitr de (procedures ou até View) de seu Banco de Dados para o retorno de melhor performance. No MySql o mais Usual de Base de dados com informações em alta performance seja o InnoDB.

    Outra dica se vc tem conhecimento sobre o MySql 5.1 ou superior pode melhorar o desempenho dele ([Ô]Muito Cuidado nisso[Ô]) faça com ajuda de um DBA.

    1 - Derrube o serviço do MySQL
    2- Localize o seu arquivo my.cnf ou my.ini (em /etc ou C:\Program Files\MySQL\MySQL Server 5.1 )
    3- Modifique os parâmetros abaixo de [mysqld] :
    max_connections = 60
    max_user_connections = 60
    key_buffer_size = mude para um valor entre um terço e metade da memória RAM disponível (exemplo: 128M)
    4 - Inicie o serviço do MySQL.


    Boa Sorte....
    NETMANIA 20/09/2010 14:16:27
    #353423
    E outra coisa. Não adianta criar um índice olhando somente a estrutura da tabela, pois não sabemos como serão as consultas que serão executadas sobre esta tabela.
    GUSTAVOCANALLI 20/09/2010 23:01:49
    #353473
    ENTAO. VOU MANDAR A ESTRUTURA DAS TABELAS QUE PARTICIPARAO DO SELECT E EM SEGUIDA O SELECT.

    --TABELA ALUNO
    CREATE TABLE 'mdl_user' (
    'id' bigint(10) unsigned NOT NULL AUTO_INCREMENT,
    'firstname' varchar(100) NOT NULL DEFAULT [ô][ô],
    'phone1' varchar(20) NOT NULL DEFAULT [ô][ô],
    'phone2' varchar(20) NOT NULL DEFAULT [ô][ô],
    'address' varchar(70) NOT NULL DEFAULT [ô][ô],
    'city' varchar(20) NOT NULL DEFAULT [ô][ô],
    'country' varchar(2) NOT NULL DEFAULT [ô][ô],
    PRIMARY KEY ('id'),
    UNIQUE KEY 'mdl_user_mneuse_uix' ('mnethostid','username'),
    KEY 'mdl_user_del_ix' ('deleted'),
    KEY 'mdl_user_con_ix' ('confirmed'),
    KEY 'mdl_user_fir_ix' ('firstname'),
    KEY 'mdl_user_las_ix' ('lastname'),
    KEY 'mdl_user_cit_ix' ('city'),
    KEY 'mdl_user_cou_ix' ('country'),
    KEY 'mdl_user_idn_ix' ('idnumber')
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT=[ô]One record for each person[ô] AUTO_INCREMENT=33 ;

    --TABELA CURSO
    CREATE TABLE 'mdl_course' (
    'id' bigint(10) unsigned NOT NULL AUTO_INCREMENT,
    'fullname' varchar(254) NOT NULL DEFAULT [ô][ô],
    'idnumber' varchar(100) NOT NULL DEFAULT [ô][ô],
    PRIMARY KEY ('id'),
    KEY 'mdl_cour_idn_ix' ('idnumber'),
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT=[ô]Central course table[ô] AUTO_INCREMENT=7 ;

    --TABELA CONTEXT (INSTANCEID é O CODIGO DO CURSO DA TABELA MDL_COURSE)
    CREATE TABLE 'mdl_context' (
    'id' bigint(10) unsigned NOT NULL AUTO_INCREMENT,
    'contextlevel' bigint(10) unsigned NOT NULL DEFAULT [ô]0[ô],
    'instanceid' bigint(10) unsigned NOT NULL DEFAULT [ô]0[ô],
    'path' varchar(255) DEFAULT NULL,
    'depth' tinyint(2) unsigned NOT NULL DEFAULT [ô]0[ô],
    PRIMARY KEY ('id'),
    UNIQUE KEY 'mdl_cont_conins_uix' ('contextlevel','instanceid'),
    KEY 'mdl_cont_ins_ix' ('instanceid'),
    KEY 'mdl_cont_pat_ix' ('path')
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT=[ô]one of these must be set[ô] AUTO_INCREMENT=66 ;

    --TABELA DE RELECAO ENTRE O ALUNO E O CURSO
    CREATE TABLE 'mdl_role_assignments' (
    'id' bigint(10) unsigned NOT NULL AUTO_INCREMENT,
    'roleid' bigint(10) unsigned NOT NULL DEFAULT [ô]0[ô],
    'contextid' bigint(10) unsigned NOT NULL DEFAULT [ô]0[ô],
    'userid' bigint(10) unsigned NOT NULL DEFAULT [ô]0[ô],
    PRIMARY KEY ('id'),
    UNIQUE KEY 'mdl_roleassi_conroluse_uix' ('contextid','roleid','userid'),
    KEY 'mdl_roleassi_rol_ix' ('roleid'),
    KEY 'mdl_roleassi_con_ix' ('contextid'),
    KEY 'mdl_roleassi_use_ix' ('userid')
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT=[ô]assigning roles to different context[ô] AUTO_INCREMENT=41 ;

    A TABELA MDL_USER TEM 400000 REGISTROS.
    A TABELA CURSO TEM 4 REGISTROS.
    CADA ALUNO ESTÁ MATRICULADO EM UM CURSO.

    O SELECT FAZ O SEUINTE: TRAZ 1 LINHA PARA CADA USUÁRIO. ONDE MOSTRA O ID, NOME, ENDERECO E O NOME DO CURSO QUE ESTÁ MATRICULADO.

    SEGUE ABAIXO O SELECT:

    SELECT ALUNO.ID AS [ô]ID ALUNO[ô],
    ALUNO.FIRSTNAME AS [ô]NOME DO ALUNO[ô],
    ALUNO.PHONE1 AS [ô]TELEONE RES.[ô],
    ALUNO.PHONE2 AS [ô]TELEONE CEL.[ô],
    ALUNO.ADDRESS AS [ô]ENDEREÇO[ô],
    ALUNO.CITY AS [ô]CIDADE[ô],
    ALUNO.COUNTRY AS [ô]PAÍS[ô],
    CURSO.ID AS [ô]ID CURSO[ô],
    CURSO.FULLNAME AS [ô]NOME DO CURSO[ô]
    FROM
    MDL_USER ALUNO,
    MDL_COURSE CURSO,
    MDL_CONTEXT CONTEXT,
    MDL_ROLE_ASSIGNMENTS ITEMCURSO
    WHERE
    ALUNO.ID = ITEMCURSO.USERID AND
    ITEMCURSO.CONTEXTID = CONTEXT.ID AND
    CONTEXT.INSTANCEID = CURSO.ID;

    COMO PODERIA MELHORAR O DESEMPENHO DA CONSULTA ATRAVéS DE UM ÍNDICE?

    ESTOU ENVIANDO O BANCO EM ANEXO.

    OBRIGADO PELA ATENÇÃO.
    OTAVIOFAVERO 21/09/2010 09:41:30
    #353483
    amigo tem algumas maneiras de vc turbinar o seu mysql

    mas nao so isso mas sim via programaçao limitando o seu recordset ou o seu dataset

    outra maneira eu ja vi , o pessoal fazendo [Ô]stored procedures[Ô] com mysql

    http://imasters.uol.com.br/artigo/7556/mysql/stored_procedures_no_mysql/

    isso sim diminuiria o seu tempo de processo em uma consulta!

    mais uma maneira de vc ter um resultado melhor nas consultas e assim olha esse exemplo : [Ô]select * from estoque_produtos where excluido=[ô]nao[ô][Ô]

    esse exemplo assima e a maneira mais lenta de usar


    agora esse abaixo

    [Ô]select excluido from estoque_produtos where excluido=[ô]nao[ô][Ô]

    essa ja e a maneira mais rapida

    ao tirar o * vc esta limitando a sua consulta a buscar em um unico campo assim fazendo o mysql ou sql responder um pouco mais rapido!

    sao coizinhas assim que fazem a diferença


    abç
    GUSTAVOCANALLI 21/09/2010 17:40:41
    #353529
    COM RELAÇÃO A CRIAÇÃO DE ÍNDICE. TERIA ALGUMA FORMA DE CRIAR UM ÍNDICE NAS TABELAS PARA MELHORAR O DESEMPENHO?
    JESUEL.OLIVEIRA 02/10/2010 19:39:41
    #354318
    amigo da uma olhada no link abaixo

    dev média

    dev media 1

    espero ter ajudado.
    GUSTAVOCANALLI 05/10/2010 12:35:56
    #354440
    PARA ACESSAR TEM QUE SER CADASTRADO NO SQL MAGAZINE Né?

    é GRATIZ PRA SE REGISTRAR?
    Tópico encerrado , respostas não são mais permitidas
    Voltar