Optimización de Hadware para grandes bases de datos MySQL

Una obsesión de los que trasteamos con grandes bases de datos es la alta accesibilidad, reducir el tiempo de busquedas, updates, inserts y la configuración óptima de hardware que optimize el rendimiento.

En www.pedeefes.com he encontrado varios documentos muy interesantes sobre este tema, que ademasme serviran para optimizar la base de datos de www.pedeefes.com que ya va por 220.000 documentos indexados con un índice de palabras con 120.000.000 de tuplas.

  http://www.percona.com/files/presentations/percona-live/london-2011/PLUK2011-linux-and-hw-optimizations-for-mysql.pdf

   

http://assets.en.oreilly.com/1/event/36/Choosing%20Hardware%20and%20Operating%20Systems%20for%20MySQL%20Presentation.pdf

http://domasmituzas.files.wordpress.com/2011/09/mysqluc2006-wikipedia-domas.pdf

Ref.:

www.pedeefes.com

búsqueda: raid mysql 

Tutorial para instalar dadabik

Tutorial para instalar dadabik.

Yo lo he probado en mi servidor micra, con debian 6.0, apache, php y MySQL instalados.

1) Creamos un subdirectorio ./dadabik en un carpeta en nuestro equipo al que se pueda acceder via web.

2) Descargamos dadabik de http://sourceforge.net/projects/dadabik/files/dadabik/

la versión actual es dadabik_4.4_alpha

3) Descomprimimos el .zip  # unzip dadabik_4.4_alpha.zip

4) Modificamos el fichero ./dadabik_4.4_alpha/program_files/include/config.php
añadiendo como mínimo esta info:

$dbms_type = ' '; // dbms type ('mysql'|'postgres'|'postgres64'|'mssql'|'oci8po'|'sqlite') (mssql is MS SQL Server, oci8po is Oracle, postgres64 is for PostgreSQL vers. <= 6.4)


$host = ' '; // DBMS server host

$db_name = ' ';  // database name

$user = ' ';  // database user

$pass = ' ';  // database password

5) Instalamos dadbik accediendo desde el navegador a la dirección:
http://nuestro_host/dadabik_4.4_alpha/program_files/install.php
 ... ...instalamos dadabik

6) .. y ya podemos acceder en la dirección: 
http://nuestro_host/dadabik_4.4_alpha/program_files/index.php

NO me permite hacer un enlace simbólico desde el directorio con acceso web a otro directorio donde pondría dadabik ya que aparecen errores include al intentar acceder a otros archivos de los directorios de dadabik.

Mi primera impresión ha sido: "para esto ya tengo phpMyAdmin".
Pero puede ser interesante para permitir acceso restringido a ciertas bases de datos por web, permitiendo la consulta y/o modificación de registros segun los casos. Y ademas su puesta en marcha es rapídísima.

DaDaBIK a database front-end maker

Esta aplicacon sirve para crear una aplicación de manejo para bases de datos sin escribir una línea de código: dadabik
Aqui hay un tutorial de como instalar y configurar dadabik

update: my tutorial para instalar dadabik.

Tabla desde Excel a MySQL

Aqui hay un método guardando la tabla excel como .csv, quitar la 1º línea de los campos, hacer una tabla en MySQL con los campos correspondientes e importar datos con:

LOAD DATA INFILE ‘ubicación/del/archivo’ INTO TABLE ‘nombre_de_la_tabla’

FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’;

http://www.puntogeek.com/2011/06/24/de-excel-a-mysql-facilmente-y-sin-romperte-la-cabeza/

... aunque leo en los comentarios que se puede hacer directamente con phpMyadmin, y efectivamente está:

The Natural Joining by SQL

Esto que parece una marca de bodymilk, es la forma ma sencilla de unir dos tabas relacionadas en MySQL o PostgreSQL.
Las tablas tienen que tener un campo comun, no haber incongruencias y del resto se ocupa mr. SQL.

SELECT * FROM tabla NATURAL JOIN tabla2;

Ref: SQL con Calse: Composicion Join

PostgreSQL:Busquedas en campos tipo inet

Si queremos hacer una búsqueda de direcciones IP en un campo PostgreSQL del tipo INET, no podemos hacerlo directamente con LIKE ...

psql=>SELECT * FROM tabla WHERE ip LIKE '192.168.1.%';
ERROR: la sintaxis de entrada no es válida para tipo inet: «192.168.1.%»

...ya que inet no es texto y no lo admite.

SOLUCCIÓN: Utilizar la función HOST(inet) que transforma el campo tipo INET en texto:

psql=>SELECT * FROM tabla WHERE HOST(ip) LIKE '192.168.1.%';

Ref.:
How do I perform a wildcard "like" select on type inet with Postgresql 7.4?
9.10. Network Address Type Functions

Bases de datos MySQL distribuidas en memoria USB

Supongamos que tenemos un servidor enanísimo (micra) y que además le hacemos currar de lo lindo teniendo que atender consultas PostgreSQL y MySQL de forma contínua además de muchas otras cosas.
Esto hace que tenga que acceder al disco constantemente ...

Se me ocurrió que se podría alojar físicamente la base de datos más consultada en una memoria usb, de este modo tal vez perdería algo de velocidad de busqueda pero optimizaría recusos del sistema al buscar y escribir datos en dispositivos diferentes.

Receta:

1. Formateo USB con sistema de ficheros ext3
   # mkfs.ext3 /dev/sda1*
   
   
2. Montamos en USB:
   # mount /dev/sda1 /mnt/USB_512MB
   
   
3. Copio directorio y sus archivos con sus cracterísticas y permisos al USB
   # cp -rpf /var/lib/mysql/basededatos /mnt/USB_512MB/
   
   
4. Borrar (o mejor mover hasta ver que todo funciona) el antiguo directorio de la base de datos en /var/lib/mysql
   # mv /var/lib/mysql/basededatos /backup/basededatos
   
   
5. Hago un enlace simbólico del directorio de antiguo de la basesdedatos al nuevo de USB.
   # ln -s /mnt/USB_512MB/basededatos /var/lib/mysql/basededatos
   
   
6. Cambiar el propietario del enlace simbólico a mysql:mysql
   # chown -h mysql:mysql /var/lib/mysql/basededatos
   
   
7. Reiniciamos MySQL
   # /etc/init.d/mysql restart
   
   
8. Realizamos una consulta SQL de prueba a basededatos ... y ... vemos como parpadea la lucecita del USB.
   
   
9. Hay que tener cuidado que el USB este montado en punto de montaje correcto cuando se conecte o se reinicie el sistema, lo mejor es configurar fstab.

Mediante el uso de enlaces simbólicos también se puede tener bases de datos transportables en un pendrive USB pudiendo usarlas en varias máquinas.

Y de igual modo se puede distribuir físicamente bases de datos en varios discos duros del sistema optimizando el rendimiento.

Ref.:
Cambiar ruta de datos de MySQL

-------------------------------------------------------
... y para PostgreSQL,
¿Donde almacena los datos PostgreSQL?, pues en
/var/lib/postgresql/8.3/main/
Pero aqui esta toda la información junta de todas las bases de datos psql!!!
Cambiar la ubicación de mis bases de datos postgres 8.1

???
en construcción...

Almacenar direcciones IP en MySQL y PostgreSQL

En PostgreSQL está el tipo de variable "net", especial para direcciones ip en formato octal.
Pero en MySQL no he encontrado este tipo de variable.

De modo que en MySQL el campo para almacenar una dirección ip pueden ser una variables del tipo: varchar(15)

ip=xxx.xxx.xxx.xxx = max. 15 caracteres.

En MySQL la transformación de octal a decimal parece sencilla, pudiendo almacenar y trabajar con la direcciones ip de forma decimal:

The best way to store an IP addresses in a RDBMS is by converting it into an INT.
MySQL is especially nice because it will do the conversion between INT and
dotted quad for you.
See the INET_ATON and INET_NTOA functions.
You'll find queries like this all over in my code:
SELECT INET_NTOA(ip) from ips;
or
INSERT INTO ips SET ip=INET_ATON('1.1.1.1');

http://dev.mysql.com/doc/refman/5.1/en/char.html

Otro ejemplillo:

mysql> select INET_ATON('100.100.100.100');

+------------------------------+
| INET_ATON('100.100.100.100') |
+------------------------------+
| 1684300900 |
+------------------------------+
1 row in set (0.00 sec)

mysql> select INET_NTOA(1684300900);
+-----------------------+
| INET_NTOA(1684300900) |
+-----------------------+
| 100.100.100.100 |
+-----------------------+
1 row in set (0.00 sec)

PostgreSQL: Actualizar registros

Actualizar Todos los registros del campo1:
psql=>UPDATE table SET campo1='pp';

Varias campos de todos los registros:
psql=>UPDATE table SET campo1='pp1', campo2='pp2', campo3='pp3';

Actualiza solo los registros que cumplen la condición:
psql=>UPDATE table SET campo1='pp' WHERE campo1='mm';

PostgreSQL: Buscar registros repetidos en una tabla.

Para ver si tenemos datos repetidos en una tabla de una base de datos PostgreSQL lo podemos hacer mediante una consulta que nos agrupe los diferentes registros y nos cuenta cuantos hay de cada uno de ellos:

SELECT pp, COUNT (pp) FROM table GROUP BY pp ORDER BY COUNT DESC;

Y en la primeras tupla nos apareceran los registros repes y el número de repeticiones.

PostgreSQL&Perl. Asignar datos de una consulta a matriz bidimensional

Un ejemplo:

#!/usr/bin/perl -w

use DBI;
use strict;

my $dbh = DBI->connect("dbi:Pg:dbname=ipuniverse",'user');
my $sth = $dbh->prepare("SELECT scann2.ip,scann2.pais,scann2.ciudad FROM scann2 LIMIT 5");

$sth->execute;

my $table = $sth->fetchall_arrayref;
my ($lin, $col);

for $lin ( 0 .. $#{$table} ) {
for $col ( 0 .. $#{$table->[$lin]} ) {
print "$table->[$lin][$col]\t";
}
print "\n";
}

$sth->finish;
$dbh->disconnect;
exit;

Ref:
Manual de referencia de DBI/DBD

Control del scroll en PostgreSQL

Primera e-carta de frank a los corintios usuarios de psql ...

pp wrote:
Hola Frank,
otra cosa que se me olvidó ....
Hay alguna forma para que una consulta desde la consola >psql NO haga un scroll hasta el final,
¿¿¿????
pp))

frank wrote:
Si entras en modo consola prueba con:
\pset pager on
Hay varias opciones para controlar el formatode salida, mira en la ayuda '\?'
Si lo llamas desde un script prueba con hacer antes
export PAGER=more
pero esto hará que tengas que pasar página a página (con la tecla espacio) de manera interactiva.
export PAGER=head -n 50
te mostrará solo las 50 prijmeras líneas.

Bueno, a ver si nos vemos y nos enseñas ese mega sistema que estas creando.

---

Podeis consultar en paz ...
... demos gracias a frank.

PostgreSQL & Perl

Perl permite una conexión directa a las bases de datos PostgreSQL mediante el interface generico DBI, el de PostgreSQL es el DBD::Pg que se conecta a la BDD como cliente tomando las filas, columnas y datos para usarlos en el programa perl.


0) Instalar el módulo DBD::Pg en Debian:
#apt-get install libdbd-pg-perl

1) Comprobar que el modulo DBD::Pg está correctament instalado, accediendo a una bsas de datos psql:

#!/usr/bin/perl
use DBI;
my $VAR01 = DBI->connect("dbi:Pg:databasename",'user','password');
my $VAR02 = $VAR01->disconnect();

-Si sale algun error es que no esta correctamente instalado el módulo perl-

1) Conexion a una BDD psql desde perl:

use DBI; # módulos que vamos a usar

use DBD::Pg; # esto no haría falta

my $DBI = DBI->connect("dbi:Pg:dbname=nombredelabasededatos");

my $dbh = DBI->connect("dbi:Pg:dbname=template1", 'nombreusuario', 'clave');

2) Extraer tuplas con perl de una BDD psql:
Usando los ciclos de preparacion, ejecución y extracción de datos.

#!/usr/bin/perl

use DBI;
use strict;

my $dbh = DBI->connect("dbi:Pg:dbname=ipuniverse",'USER');
$dbh->{RaiseError} => 1; #ref

my $sth = $dbh->prepare("SELECT * FROM scann2 WHERE scann2.px='O' AND scann2.yy='0' LIMIT 1");

$sth->execute;

my @row;
@row = $sth->;fetchrow_array or die "No match for query";

my $var1 = $row[0];
my $var2 = $row[1];
my $var3 = $row[2];
my $var4 = $row[3];

print "$row[0] $row[1] $row[2] $row[3]\n";
print "$var1 $var2 $var3 $var4\n";

$sth->finish;
$dbh->disconnect;
exit;

Con este script hemos extraido los registros de una tupla de una BDD de PostgreSQL y los hemos asignado a las varables en perl.

INSERTando segistros en PostgreSQL desde Perl:
Se recomienda usar el método secuencial de preparación y ejecución por motivos de rendimento especialmente si hay busquedas en la bdd.
También esta el método directo, prepare+execute todo en uno.
Un ejemplo de ambos métodos:

#!/usr/bin/perl
use DBI;
use strict;

#conectamos con la BD

my $dbh = DBI->connect("dbi:Pg:dbname=ipuniverse",'USER');
$dbh->{RaiseError} => 1;

#metodo directo
$dbh->do("INSERT INTO table VALUES ('valores','$variables')");

#método secuencial
my $sth = $dbh->prepare("UPDATE table SET campo1='valor o variable' WHERE campo2='valor o variable'");
$sth->execute;
$sth->finish;

$dbh->disconnect;

REF:

http://www.arsys.info/programacion/bases-de-datos/perl-y-dbi/
http://flanagan.ugr.es/dbi/manual.htm
http://geneura.ugr.es/~jmerelo/tutoriales/bd-sql/
Ejemplos de instrucciones perl-postgresql:
http://linuxbloggers.org/create-insert-select-update-perl-script-and-postgresql-database-examples/
Tutorial PSQL desde Perl:
http://www.felixgers.de/teaching/perl/perl_DBI.html

Tutorial PostgreSQL y PSQL desde Perl

Un tutorial resumido y al grano para PostgreSQL:

• Ejemplos para crear bases de datos.
• Consultas.
• PostgreSQL desde Perl
  

http://geneura.ugr.es/~jmerelo/tutoriales/bd-sql/