Lidando com crescimento de tabelas no MySQL (parte 2)

criado por Ronaldo Menezes em 19/07/2007 5:41am

Tenho que controlar 1400 discos diariamente, sendo que este número pode variar, pois pode crescer ou diminuir dependendo das necessidades de recursos (espaço disponível). Elaborei um processo onde a cada novo disco inserido, uma nova base é criada e sua respectiva tabela. Exemplo:

Inserido disco MBA001 no dia 2007-07-16. Como expliquei acima a cada nova inclusão crio uma nova base e sua respectiva tabela.

CREATE DATABASE IF NOT EXISTS BAS001;

CREATE TABLE IF NOT EXISTS BAS001_TABELA;

Hoje tenho perto de 2000 bases e suas respectivas tabelas, o que torna o controle difícil devido ao número de Bases e Tabelas. Cada tabela pode ter centenas ou milhares de registros dependendo do disco e de quais arquivos ira guardar.

Logo pela manha preciso saber se a produção precisou de mais recursos a noite, ( se precisou de mais discos ) , quais foram e saber quantas linhas tem cada tabela , de forma a disponibilizar informações a gerência e ao final de cada ano projectar as necessidades do ano posterior com bases em informações recolhidas .

Não vou detalhar o conteúdo de cada tabela, mais preciso também controlar o conteúdo de cada tabela, ou seja o conteúdo de cada disco, pois acompanho o crescimento de cada arquivo dentro de cada disco., aproximadamente 300,000 arquivos dia , distribuídos entre arquivos normais e arquivos de bases de dados.

A criação das tabelas e suas respectivas cargas ocorrem sempre que um disco é inserido. A tabela contem um resumo (como se fosse a FAT) do disco, onde posso ver as informações de cada arquivo.

A decisão de dividir em N bases deve-se ao facto da enorme quantidade de dados gerada diariamente, com a divisão obtenho query’s mais rápidas. Todo o controle do processo é feito de forma dinâmica.

Dados aproximadamente 350.000 dia , 10.500.000 mês , 126.000.000 ano. Bases sobre arquivos e discos .

Todas as Bases/ tabelas tem a mesma estrutura :

BASE = VOLUME
TABELA = VOLUME_TABELA

Preciso saber todos os dias :
- Quais discos foram incluídos ?
- Qual a quantidade de arquivos em cada disco ?
- Quando foi a ultima actualização do disco ?

Criação da Tabela “estatística”:

USE producao;

#
# tabela  'estatistica'
#

DROP TABLE IF EXISTS estatistica;
CREATE TABLE estatistica (
  data varchar(10) default NULL,            data do dia       
  db varchar(30) default '0',               database              
  tabela varchar(30) default '0',           tabela 
  linhas int(9) unsigned default '0',       linhas
  data_criacao varchar(14) default NULL,    data criacao da tabela
  data_update varchar(14) default NULL      data ultimo update
) TYPE=MyISAM;

Com o script abaixo obtenho os dados de todas as bases e tabelas e dou carga na tabela “estatística” :

<?php
set_time_limit(0);
$datah = date("Y-m-d");
$link = mysql_connect("localhost", "root", "")
        or die("Não pude conectar: " . mysql_error());
$sql = "SHOW DATABASES";
$y = 0;
$resultd = mysql_query($sql);
while ($rowd = mysql_fetch_row($resultd)) {
    mysql_select_db("$rowd[0]") or die("Não pude selecionar o banco de dados");
    $sqlt = "SHOW TABLES FROM $rowd[0]";
    $resulta = mysql_query($sqlt);
    $tt = count($resulta);

    while ($rowt = mysql_fetch_row($resulta)) {
        $df =  "SHOW TABLE STATUS like " . "'$rowt[0]'" ;
        $resultr = mysql_query($df);
        $arr = mysql_fetch_array($resultr);
        $rt = count($arr);
        $database[] = $rowd[0];
        $tabela[] = $rowt[0];
        $linhas[] = $arr[3];

        $data_criacao[] = substr($arr[10],0,10) ;
        $data_update[] = substr($arr[11],0,10);
    }
}

$db = "use producao";
$rs = mysql_query($db);

for ($i = 0; $i < $todos; $i++) {
    $sq = "insert into estatistica(data,db,tabela,linhas,data_criacao,data_update) values
          ('$datah','$database[$i]','$tabela[$i]','$linhas[$i]','$data_criacao[$i]','$data_update[$i]')";
    $res = mysql_query($sq);
}
?>

O resultado da após execução do script :

Abaixo dados da carga :

USE producao;

#
# Table structure for table 'estatistica'
#

DROP TABLE IF EXISTS estatistica;
CREATE TABLE estatistica (
  data varchar(10) default NULL,
  db varchar(30) default '0',
  tabela varchar(30) default '0',
  linhas int(9) unsigned default '0',
  data_criacao varchar(14) default NULL,
  data_update varchar(14) default NULL
) TYPE=MyISAM;

#
# Dumping data for table 'estatistica'
#

INSERT INTO estatistica (data, db, tabela, linhas, data_criacao, data_update) VALUES("2007-07-16", "bas670", "bas670_tabela", "55738", "2007-01-23", "2007-07-16");
INSERT INTO estatistica (data, db, tabela, linhas, data_criacao, data_update) VALUES("2007-07-16", "bas671", "bas671_tabela", "43122", "2007-01-23", "2007-07-16");
INSERT INTO estatistica (data, db, tabela, linhas, data_criacao, data_update) VALUES("2007-07-16", "bas672", "bas672_tabela", "51979", "2007-01-23", "2007-07-16");
INSERT INTO estatistica (data, db, tabela, linhas, data_criacao, data_update) VALUES("2007-07-16", "bas673", "bas673_tabela", "46989", "2007-01-23", "2007-07-16");
INSERT INTO estatistica (data, db, tabela, linhas, data_criacao, data_update) VALUES("2007-07-16", "bas674", "bas674_tabela", "59966", "2007-01-23", "2007-07-16");
INSERT INTO estatistica (data, db, tabela, linhas, data_criacao, data_update) VALUES("2007-07-16", "bas675", "bas675_tabela", "69566", "2007-01-23", "2007-07-16");
INSERT INTO estatistica (data, db, tabela, linhas, data_criacao, data_update) VALUES("2007-07-16", "bas676", "bas676_tabela", "57494", "2007-01-23", "2007-07-16");
INSERT INTO estatistica (data, db, tabela, linhas, data_criacao, data_update) VALUES("2007-07-16", "bas677", "bas677_tabela", "63658", "2007-01-23", "2007-07-16");
INSERT INTO estatistica (data, db, tabela, linhas, data_criacao, data_update) VALUES("2007-07-16", "bas678", "bas678_tabela", "57646", "2007-01-23", "2007-07-16");
INSERT INTO estatistica (data, db, tabela, linhas, data_criacao, data_update) VALUES("2007-07-16", "bas679", "bas679_tabela", "56457", "2007-01-23", "2007-07-16");
INSERT INTO estatistica (data, db, tabela, linhas, data_criacao, data_update) VALUES("2007-07-16", "bas680", "bas680_tabela", "55897", "2007-01-23", "2007-07-16");
INSERT INTO estatistica (data, db, tabela, linhas, data_criacao, data_update) VALUES("2007-07-16", "bas681", "bas681_tabela", "45637", "2007-01-23", "2007-07-16");
INSERT INTO estatistica (data, db, tabela, linhas, data_criacao, data_update) VALUES("2007-07-16", "bas682", "bas682_tabela", "52803", "2007-01-23", "2007-07-16");
INSERT INTO estatistica (data, db, tabela, linhas, data_criacao, data_update) VALUES("2007-07-16", "bas683", "bas683_tabela", "53865", "2007-01-23", "2007-07-16");
INSERT INTO estatistica (data, db, tabela, linhas, data_criacao, data_update) VALUES("2007-07-16", "bas684", "bas684_tabela", "52457", "2007-01-23", "2007-07-16");
INSERT INTO estatistica (data, db, tabela, linhas, data_criacao, data_update) VALUES("2007-07-16", "bas685", "bas685_tabela", "45885", "2007-01-23", "2007-07-16");
INSERT INTO estatistica (data, db, tabela, linhas, data_criacao, data_update) VALUES("2007-07-16", "bas686", "bas686_tabela", "39598", "2007-01-23", "2007-07-16");
INSERT INTO estatistica (data, db, tabela, linhas, data_criacao, data_update) VALUES("2007-07-16", "bas687", "bas687_tabela", "59219", "2007-01-23", "2007-07-16");
INSERT INTO estatistica (data, db, tabela, linhas, data_criacao, data_update) VALUES("2007-07-16", "bas688", "bas688_tabela", "45558", "2007-01-23", "2007-07-16");
INSERT INTO estatistica (data, db, tabela, linhas, data_criacao, data_update) VALUES("2007-07-16", "bas689", "bas689_tabela", "41640", "2007-01-23", "2007-07-16");
INSERT INTO estatistica (data, db, tabela, linhas, data_criacao, data_update) VALUES("2007-07-16", "bas690", "bas690_tabela", "48532", "2007-01-23", "2007-07-16");
INSERT INTO estatistica (data, db, tabela, linhas, data_criacao, data_update) VALUES("2007-07-16", "bas691", "bas691_tabela", "69241", "2007-01-23", "2007-07-16");
INSERT INTO estatistica (data, db, tabela, linhas, data_criacao, data_update) VALUES("2007-07-16", "bas692", "bas692_tabela", "65544", "2007-01-23", "2007-07-16");
INSERT INTO estatistica (data, db, tabela, linhas, data_criacao, data_update) VALUES("2007-07-16", "bas693", "bas693_tabela", "59678", "2007-01-23", "2007-07-16");

Respostas as consultas propostas :

1. Discos incluídos em 2007-07-16?

SELECT db FROM estatistica WHERE data_criacao = ‘2007-07-16’;

2. Linhas (arquivos ) no disco ?

SELECT db, linhas FROM estatistica WHERE data = ‘2007-07-16’;

3. Quando a ultima actualização ( disco pode não estar sendo usado ) ?

SELECT data_update FROM estatistica where data_update != ‘2007-07-16’;

Ronaldo Menezes

Comentários:

Nenhum comentário foi enviado ainda.

Lidando com crescimento de tabelas no MySQL (parte 2)

Comentários:

Novo Comentário: