MySQL: Analizando la performance de un SELECT

Me encontré con la necesidad de saber con exactitud cuánto demora un SELECT de una fila cuando el campo que buscamos no es un índice, o si es un índice secundario o uno primario.

Para explicar mejor la situación, propongo el siguiente ejemplo:

Tenemos la siguiente Tabla de Usuarios:

Id Name UserName Telephone Notes

 

La configuración que primero se nos ocurre es que el campo “Id” sea un índice primario, pero qué sucede si por alguna razón, tenemos que hacer siempre un SELECT donde en el WHERE se use con la columna UserName, por ejemplo, si tenemos que loguear al usuario y el dato que tenemos es el username, la consulta sería:

SELECT * FROM Users WHERE UserName='pepe';

En cualquier sistema, hasta en un servidor con escasos recursos, esta consulta no tarda mucho y poco importa si tarda unos milisegundos más o menos…  pero en ciertas ocasiones, con tablas de miles de registros,  dependiendo del entorno,  los milisegundos comienzan a tomar importancia, sobre todo si no tenemos mucho poder de procesamiento, el timing comienza a ser de mucha importancia.

Las bases de datos actuales, para resumirlo de una forma brutal y básica, guardan la información en estructuras de datos ordenadas (árboles avanzados) por índices, lo cual nos hace pensar que si buscamos por el campo “Id” es más rápido que si buscamos por el campo “UserName” ya que el campo “Id” está indexado.

Qué podemos hacer entonces??

Hagamos que UserName sea un índice!!!  Muy bonito, pero, cuando la base de datos busca, comparar un INTEGER (“Id”) es muchísimo más rápido que comparar un VARCHAR (“UserName”) de unos 20 caracteres de longitud, por lo tanto, cualquier operación en la tabla se vuelve un poco más lenta…

Entonces, que conviene más?  Usar un índice que haga la tabla más lenta en general, pero que devuelva una fila mucho más rápido? o usar un índice ágil, pero luego tendremos demoras al buscar una sola fila por otro campo?

La respuesta a esta última pregunta, depende un 100% del entorno del sistema en el que estemos…
En un sistema con pocos usuarios, podría no ser un inconveniente, pero cuando tenemos un servidor con escasos recursos, como por ejemplo la Raspberry Pi o similares y en nuestra aplicación una diferencia de milisegundos importa, se convierte en un inconveniente.

Se me ocurrieron algunas opciones para medir el rendimiento y realizar algunos benchmarks de MySQL corriendo con bajos recursos…

  • Comparar los resultados usando:

    • a)  El campo Id como Índice Primario y el resto campos comunes. (Situación normal)
    • b)  El campo Id como Índice Primario y el campo UserName como Índice Secundario
    • c)  El campo UserName como Índice Primario y el campo Id como Índice Secundario
  • Comparar el comportamiento de las configuraciones anteriores con distintas cantidades de registros:

    • a)  100 (cien) registros
    • b)  1000 (mil) registros
    • c)  10000 (diez mil) registros
    • d)  100000 (cien mil) registros
    • f )  1000000 (un millón) de registros
  • Ya que estamos haciendo benchmarks, comparemos los resultados anteriores con los dos motores más populares de MySQL:

    • a)  MyISAM
    • b)  InnoDB

Cómo hago estos benchmarks???

Para ello, escribí unas pocas lineas en PHP, que me permitieron llenar una BD de pruebas con información aleatoria y precisa para las pruebas…  si, en total generé más de 6.500.00 filas! casi 1GB de registros aleatorios. (demoró varios minutos)
Por si a alguien le interesa, al final del post les dejo la descarga del archivo PHP utilizado para generar los datos aleatorios, la estructura de la base de datos sin registros y la bd llena de datos.

Condiciones de las pruebas:

  • Las consultas se hacen en PhpMyAdmin.
  • Se busca un registro a la mitad del total de la tabla.
  • El tiempo es obtenido de PhpMyAdmin.
  • El servidor MySQL corre en una Raspberry Pi.
  • El sistema operativo es Raspbian ‘Wheezy’.
  • Las configuraciones de Apache/PHP/MySQL son las que vienen por defecto al instalarlos.
  • El cache MySQL es reseteado antes de cada prueba.
  • El campo UserName es un VARCHAR de 25 caracteres.
Ejemplo de consultas:

Basta de palabras!!!   quiero ver los números!

Todos los valores de tiempos están expresados en mS (milisegundos).

Aquí tenemos dos tablas, una con los resultados de MyISAM y otra con los de InnoDB. En ambos casos comparamos las tres configuraciones de índices mencionadas anteriormente (a, b, c) en distintas cantidades de registros. Primero buscando por el campo ID y luego por el campo UserName.

MyISAM

 ID Indice PrimarioUserName Índice SecundarioUserName Índice Primario
WHERE->Id='x'UserName='x'Id='x'UserName='x'Id='x'UserName='x'
1003.74.43.84.43.93.8
1.0003.710.53.84.54.14.0
10.0003.776.13.94.54.24.1
100.0004.2789.14.14.84.34.2
1.000.0005.28158.85.15.46.14.8

InnoDB

 ID Indice PrimarioUserName Índice SecundarioUserName Índice Primario
WHERE->Id='x'UserName='x'Id='x'UserName='x'Id='x'UserName='x'
1004.37.04.45.04.23.7
1.0004.435.44.45.04.43.9
10.0004.6312.24.55.14.64.3
100.0004.62802.84.65.24.74.6
1.000.0007.035883.88.09.45.04.8

Resultados:

A primera vista, podemos notar la diferencia entre InnoDB y MyISAM en cuanto a la performance de los SELECTs.  Si tomamos como referencia las primer columna, donde ID es un Índice Primario, en InnoDB buscar un indice en 1.000.000 de registros, es un 35% más lento, pero si buscamos un registro no indexado, InnoDB es un 440% más lento que MyISAM.

Entonces, descartamos InnoDB para el resto de las comparaciones.

Evidentemente, buscar un registro que no es un índice, en un millón de filas, demora 8158.8 milisegundos, eso es es más de 8 segundos!!!  De hecho en 10.000 filas, demora casi 100 milisegundos, lo cual, en algunos casos, puede ocasionar problemas. Obviamente y como podíamos imaginar, si el tiempo es clave, ésta es la peor configuración.

Nos quedan dos opciones, utilizar el campo UserName como un indice secundario, o como un indice primario.

Como se puede ver en la tabla, si buscamos la mejor velocidad, nos conviene hacerlo un índice primario, pero en contra, la busqueda por ‘Id’ demora un par de milisegundos más.

Conclusión Final:

Como conclusión final, voy a elegir la configuración de “UserName como índice secundario“.  Porqué? Porque en promedio, parece tener los mejores tiempos, ningun tiempo supera los 6ms, sin importar si es índice primario o secundario. Realmente, muy buenos timings.

Como montar un Servidor Ubuntu Server COMPLETO, con ISPConfig 3 Parte 1

Este post está dedicado a instalar un servidor para hosting multi-cuentas, en una VPS, por ello, no explicare temas relacionados al hardware ni a la instalación del sistema operativo.

Si quieres saber mas sobre las VPS, y cómo elegir una VPS, mira este link.

Este post, está basado en las guías Perfect Server de HowToForge, pero con algunas mejoras.

Ubuntu Server

Para mi, Ubuntu Server, es uno de las mejores distros para iniciarse en el tema de servidores linux, debido a la gran cantidad de información y tutoriales que hay sobre el en la red.

Bueno, en este tutorial usaremos lo siguiente:

  • Ubuntu Server 11.04 como sistema operativo.
  • ISPConfig 3 como panel de administración para cuentas de Hosting.
  • Apache, PHP, MySQL
  • SquirreMail, PostFix
  • PureFTP
  • BIND 9

Comencemos…

Antes que nada, para este tutorial, usaremos server1.example.com como hostname, y supondremos que yas estamos logueados como root. (sino, anteponer sudo a cada comando)

Yo uso nano para editar los archivos, pero pueden usar vi, o cualquier otro.

Para instalar nano, hacemos:
apt-get install nano
Para utilizar nano, se abren los archivos con nano archivo luego, para guardar, se presiona F2, luego decimos que si, (Y) y luego aceptamos con ENTER.

Ahora, cambiamos el hostname:

echo server1.example.com > /etc/hostname
hostname server1.example.com
/etc/init.d/hostname restart

Click Aqui para ver la Parte 2 | Click Aqui para ver la Parte 3

Como montar un Servidor Ubuntu Server COMPLETO, con ISPConfig 3 Parte 2

Parte 2

Procederemos a editar la lista de repositorios, para poder tener los paquetes mas actualizados…
nano /etc/apt/sources.list
Borramos todo el contenido, y copiamos allí el contenido de este archivo.

Ahora, para actualizar nuestra BD de paquetes, y de paso, actualizar algunas versiones de los mismos, hacemos:
apt-get update
apt-get upgrade
Responder que si (Y) a cualquier pregunta.

Configuramos la hora del sistema:
dpkg-reconfigure tzdata
Seguir las instrucciones en pantalla.

Ahora, empezamos a instalar algunos paquetes:
apt-get install postfix postfix-mysql postfix-doc mysql-client mysql-server courier-authdaemon courier-authlib-mysql courier-pop courier-pop-ssl courier-imap courier-imap-ssl libsasl2-2 libsasl2-modules libsasl2-modules-sql sasl2-bin libpam-mysql openssl getmail4 rkhunter binutils maildrop

El sistema nos va a hacer las siguientes preguntas:

New password for the MySQL “root” user: tucontraseñasql
Repeat password for the MySQL “root” user: tucontraseñasql
General type of mail configuration: Internet Site
System mail name: server1.example.com
Create directories for web-based administration? No
SSL certificate required Ok

Preparamos y optimizamos MySQL para una VPS con pocos recursos de RAM:
nano /etc/mysql/my.cnf 
Comentar la linea bind-address=127.0.0.1, tiene que quedar asi: #bind-address = 127.0.0.1 (esto es para poder acceder al servidor MySQL desde otros servers/hosts.)
y cambiar los valores  de algunas de las variables, por los de aqui abajo:

[mysqld] key_buffer = 16K
max_allowed_packet = 1M
table_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K

También, si no vamos a usar innodb ni Berkeley DB, o ni sabes lo que es eso, agrega estas dos lineas al final del my.conf:
skip-bdb
skip-innodb

Y ahora, reiniciamos el servicio MySQL:
/etc/init.d/mysql restart

Ahora, prepararemos las claves SSL para el servicio de Mail.
cd /etc/courier
rm -f /etc/courier/imapd.pem
rm -f /etc/courier/pop3d.pem

nano /etc/courier/imapd.cnf
Cambiar CN=server1.example.com por tu hostname real

nano /etc/courier/pop3d.cnf
Cambiar CN=server1.example.com por tu hostname real

Ahora, generamos los certificados
mkimapdcert
mkpop3dcert

Y luego reiniciamos los servicios:
/etc/init.d/courier-imap-ssl restart
/etc/init.d/courier-pop-ssl restart

Ahora, instalamos apache, php y algunos modulos y paquetes extras que neesitaremos como phpMyAdmin, suExec, Pear y mcrypt:
apt-get install apache2 apache2.2-common apache2-doc apache2-mpm-prefork apache2-utils libexpat1 ssl-cert libapache2-mod-php5 php5 php5-common php5-gd php5-mysql php5-imap phpmyadmin php5-cli php5-cgi libapache2-mod-fcgid apache2-suexec php-pear php-auth php5-mcrypt mcrypt php5-imagick imagemagick libapache2-mod-suphp libruby libapache2-mod-ruby sudo zip

El sistema nos va a hacer las siguientes preguntas:

Web server to reconfigure automatically: apache2
Configure database for phpmyadmin with dbconfig-common? No

Ahora, ejecutamos los siguientes comandos para habilitar los modulos de apache:
a2enmod suexec rewrite ssl actions include
a2enmod dav_fs dav auth_digest
/etc/init.d/apache2 restart

Click Aqui para ver la Parte 1 Click Aqui para ver la Parte 3

Como montar un Servidor Ubuntu Server COMPLETO, con ISPConfig 3 Parte 3

Parte 3

Continuando con la parte 3 de este tutorial, Instalamos el FTP

apt-get install pure-ftpd-common pure-ftpd-mysql quota quotatool
nano /etc/default/pure-ftpd-common
Cambiar a STANDALONE_OR_INETD=standalone
Cambiar a VIRTUALCHROOT=true

Ahora, habilitamos TLS, y generamos su certificado SSL:

echo 1 > /etc/pure-ftpd/conf/TLS
mkdir -p /etc/ssl/private/
openssl req -x509 -nodes -days 7300 -newkey rsa:2048 -keyout /etc/ssl/private/pure-ftpd.pem -out /etc/ssl/private/pure-ftpd.pem

chmod 600 /etc/ssl/private/pure-ftpd.pem
/etc/init.d/pure-ftpd-mysql restart

Continuamos instalando BIND, y las web stats:

apt-get install bind9 dnsutils
apt-get install vlogger webalizer awstats

Editamos el archivo cron.d,  y eliminamos awstats, ya que luego, ISPConfig 3, insertará sus propias lineas en el cron.d
nano  /etc/cron.d/awstats

(hay que comentar las unicas 2 lineas del archivo, y tiene que quedar asi:)

Ahora, instalamos Jailkit, que sirve para que cada usuario SSH, no se pueda salir de su directorio /home propio.
apt-get install build-essential autoconf automake1.9 libtool flex bison debhelper

cd /tmp
wget http://olivier.sessink.nl/jailkit/jailkit-2.14.tar.gz
tar xvfz jailkit-2.14.tar.gz

cd jailkit-2.14
./debian/rules binary
cd ..
dpkg -i jailkit_2.14-1_*.deb
rm -rf jailkit-2.14*

Instalamos y configuramos SquirreMail, para poder acceder a nuestros webmails:

apt-get install squirrelmail
ln -s /usr/share/squirrelmail/ /var/www/webmail
squirrelmail-configure 

Aqui, sigue las instrucciones de este archivo, para contestar las preguntas del sistema y configurar el squirreMail.

Una vez configurado el cliente para el webmail, antes de probarlo, configuraremos un par de cosas para hacer que el email funcione sin amavis y clamd, que son sumamente pesados para VPS con bajos recursos.
nano /etc/postfix/main.cf
Comentamos las siguientes lineas:

content_filter = amavis:[127.0.0.1]:10024
receive_override_options = no_address_mappings

Tienen que quedar asi:

#content_filter = amavis:[127.0.0.1]:10024
#receive_override_options = no_address_mappings

nano /etc/postfix/master.cf

Comentamos todas las lineas debajo de  amavis unix – – – – 2 smtp

Tiene que quedar asi:
#amavis unix – – – – 2 smtp
# -o smtp_data_done_timeout=1200
# -o smtp_send_xforward_command=yes

#127.0.0.1:10025 inet n – – – – smtpd
# -o content_filter=
# -o local_recipient_maps=
# -o relay_recipient_maps=
# -o smtpd_restriction_classes=
# -o smtpd_client_restrictions=
# -o smtpd_helo_restrictions=
# -o smtpd_sender_restrictions=
# -o smtpd_recipient_restrictions=permit_mynetworks,rej ect
# -o mynetworks=127.0.0.0/8
# -o strict_rfc821_envelopes=yes
# -o receive_override_options=no_unknown_recipient_chec ks,no_header_body_checks
# -o smtpd_bind_address=127.0.0.1

Y por ultimo reiniciamos postfix:
/etc/init.d/postfix restart

Bueno, hemos llegado al ultimo paso, instalar ISPConfig 3. Si todo ha salido bien, con ejecutar estas ultimas 5 lineas, nuestro servidor estará perfectamente configurado, y listo para alojar paginas web!

cd /tmp
wget http://www.ispconfig.org/downloads/ISPConfig-3-stable.tar.gz
tar xfz ISPConfig-3-stable.tar.gz
cd ispconfig3_install/install/
php -q install.php 
 

Ahora, se ejecutara el instalador de ISPConfig3, hay que presionar enter para dejar los valores por defecto, menos cuando nos pida la clave para MySQL, ahi hay que escribirla, y luego presionar enter…

Y eso es todo! 

Ya podemos acceder a nuestro hosting desde el navegador,  mediante la IP de nuestro server, y al puerto 8080, http://xxx.xxx.xxx.xxx:8080 ahi, hay que loguearse con admin:admin

Bueno gente, espero que les haya servido este tutorial…

Acepto comentarios y criticas!

Saludos

Click Aqui para ver la Parte 1Click Aqui para ver la Parte 2

Solución a los ataques SQL-Injection y de etiquetas html, php etc.

Este pequeño script debe ir arriba de TODOS los archivos php que requieran interacción del usuario por medio de los metodos POST, GET y REQUEST.

Esto, eliminar las etiquetas html  que puedan ser ingresadas en los campos de datos y tambien evita las sql-injection.

foreach( $_POST as $variable ){
$_POST [ $variable ] = mysql_real_escape_string($variable);
$_POST [ $variable ] = str_replace ( array("< ",">","[","]","*","^"), "" , $_POST[ $variable ]);
//$variable=$_POST [ $variable ];
//echo "POST:$variable";
}
foreach( $_REQUEST as $variable){
$_REQUEST [ $variable ] = mysql_real_escape_string($variable);
$_REQUEST [ $variable ] = str_replace ( array("< ",">","[","]","*","^"), "" , $_REQUEST[ $variable ]);
//$variable=$_REQUEST [ $variable ];
//echo "REQUEST:$variable";
}
foreach( $_GET as $variable){
$_GET [ $variable ] = mysql_real_escape_string($variable);
$_GET [ $variable ] = str_replace ( array("< ",">","[","]","*","^"), "" , $_GET[ $variable ]);
//$variable=$_GET [ $variable ];
//echo "GET:$variable";
}

Básicamente lo que el script hace es leer variable por variable, de cualquiera de los 3 métodos y añadir un caracter de escape, “” a cualquier caracter peligroso para mysql, y eliminar etiquetas html y demás.

Por ejemplo:
El texto: ‘OR=’ ‘ es convertido a ‘OR=’ ‘ por lo que pierde efecto.
El texto <h1>HOLA</h1> es convertido a h1HOLAh1 por lo que también pierde efecto.

Con este script nos evitamos tener que proteger todos los campos manualmente uno por uno.

Recuerda que si te sirvió, comentá.

Saludos y safe programming!

Reparar InnoDB en Ubuntu…

Errores con InnoDB en Ubuntu

Muchas veces, nos encontramos queriendo hacer una Tabla para una Base de datos  MYSQL en Ubuntu con claves foraneas, y nos damos por sorpresa que InnoDB no esta activado.

Lo primero que cualquier persona hace es: Google–> ¿Como activar InnoDB en Ubuntu?    a lo que Google responde… que hay que editar el archio de configuracion, comentando la linea que dice Skip-InnoDB, y reiniciando el servicio de mysql…

PERO AVECES, ESO NO FUNCIONA, como lo fue en mi caso  Mysql, sigue iniciando con InnoDB desactivado!

Ni siquiera ningun mensaje de error me tira…

el problema radica en  los logs de InnoDB. al iniciar de forma incorrecta, se genera un LOG CORRUPTO, que sencillamente, no deja que se inicie InnoDB.

solucion:

ir hasta la carpeta:

<strong>/var/lib/mysql</strong>

y eliminar por completo los siguientes archivos:

<strong>ibdata1 </strong>
<strong>ib_logfile0</strong>
<strong> ib_logfile1</strong>

despues reinicias mysql con la siguiente linea:

<strong>sudo /etc/init.d/mysql start</strong>

Y LISTO ahora InnoDB va a funcionar perfectamente!