InnoDB vs MyISAM en sitio web con importante cantidad de visitas

[Skeletron]

Hola gente.
Les comento que estoy por realizar una base de datos a un sitio web que tiene muchisimas visitas.

Es algo así como un FORO, BLOG, WIKI, CHAT y mas cosas, y todas al mismo tiempo.

Si bien siempre tuve la idea de que para un sitio web mediano, no se notan mucho las diferencias entre MySQL y InnoDB, ahora si me gustaría saber realmente cual es la diferencia en cuanto a optimizacion y esas cosas.

Que datos pueden darme? Algo para tener en cuenta?

[Kase]

innoDB  tiene entidad relacional (o algo asi) que son reglas en la base de datos que mantienen congruentes los datos relacionales (no te permite borrar un dato si se encuentra relacionado con otro a  menos que los definas como borrado en cascada)


myISAM no tiene entidad relacional, asi que el control de congruencias lo tienes que tener en tu aplicacion
por lo tanto es mas rapido
y permite hacer busquedas en campos del tipo text.. no se explicarte esto en este momento y me da flojera buscarlo
pero en si, myISAM te permite hacer tu propio buscador  por cantindad de palabras similares en un text o un varchar

=P 

si elijes myISAM tienes que tener mucho cuidado con la integridad de los datos..

un problema

digamos que un proceso que tienes   hace lo sig:

inserta un valor
toma el ultimo valor insertado e inserta otro valor

eso no deberia causar poblemas, pero si segun tu, tienes muchisimo trafico

podria ser que 2 usuarios hagan el mismo proceso al mismo tiempo
y  los procesos van entrando a una unica pila de ejecucion,
por lo tanto podrias tener algo asi

insert valor //usuario 1
insert valor //usuario 2
toma ultimo valor e inserta otro //usuario 1
toma ultimo valor e inserta otro //usuario 2

cuando deberia ser asi:

//usuario 1
inser valor
toma ultimo valor e inserta otro
//usuario 2
inser valor
toma ultimo valor e inseta otro

hay ya se causo un error de congruencia

el error puede ocurrir en inoDB y en myISAM, pero innoDB tiene mas reglas y metodos para pevenir esto

ahora un error de entidad:

tu tabla es
cliente {idcliente}
telefono{idctelefono, forain key idcliente}

al hacer un delet  cliente en innoDB  te tiraria error, si la clave cliente tiene una relacion con telefono..

en myISSAM  simplemente borra el cliente y le vale madre  la otra tabla

[WHK]

ya no hay problema con el último id insertado ya que desde php existe la función
mysql_insert_id () que obtiene el último id insertado con insert en un integer autoincrementado pero no globalmente desde mysql sino hecha por tu conexión en el contexto que se ejecuta el script, por lo tanto si haces una query con un insert, haces un sleep de 30 segundos y entremedio le haces otra insert desde otro script no debería dar problemas ya que php te debuelve el último valor insertado desde esa conexión a la base de datos, no el valor global insertado desde la función nativa de mysql.

Creo que php crea un caché estadistico interno por cada ejecución de scripts y debuelve esos datos con una función justamente para evitar este tipo de problemas.

Es algo muy similar a mysql_affected_rows(), no te entrega los datos de la query de otro script sino del mismo si le pasas el handle o link de la conexión actual.

http://php.net/manual/es/function.mysql-insert-id.php
http://www.php.net/manual/es/function.mysql-info.php

Todo esto suponiendo que usas php  

[Kase]

 

no sabia eso, auque ami me enseñaron (por no decir latiguearon) a que todo proceso que tenga mas de una instruccion sql lo englobe en un prcedimiento y blokee la tabla.

por ejemplo (y justamente tenia avierto el mysql workbeach)

-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE PROCEDURE InsertUsuario (varUsuario varchar(20), varPassword text, varNombre varchar(100), varEmail varchar(100), varCalle varchar(100), varCol varchar(100),varNum varchar(10),varTelefono int)
BEGIN
declare varIDUsuario int;
START TRANSACTION;
insert into usuario ( usuario, password, nombre, email) values (varUsuario,SHA1(varPassword),varNombre,varEmail);
select last_insert_id() into varIDUsuario;
insert into cliente (telefono,domicilioCalle,domicilioCol,domicilioNum,credito) values (varTelefono,varCalle,varCol,varNum,false);
insert into Usuariocliente (idusuario,idcliente) values (varIDUsuario,last_insert_id());
commit;
END

donde start transaction  blokea la tabla y commit la libera

esto funciona en innoDB no recuerdo si en myISAM se puede blokear la tabla asi...
pero de que se puede blokear se puede blokear

[WHK]

exacto, normalmente debería bloquearse la tabla y te enseñaron bién porque no te estaban enseñando php sino mysql xD y por lo tanto no podían generarte una dependencia al código php como es en el caso de algunos interpretadores como cgi/perl y python.

pero php ya lo hace solo. de hecho simplemachines y otros proyectos webs bloquean las tablas para otras cosas, verificar baneos, actualizar datos, etc, sobre todo en las estadísticas, pero no debería usarse para calcular el último id.

yo en lo personal prefiero myISAM por la flexibilidad y compatibilidad.

[[u]nsigned]

InnoDB tiene soporte real para claves foraneas (comprobociones) en la capa mas baja de la BB.DD. MyISAM no posee esta caracteristica, es decir que l acomrpobacion de clavbes foraneas debe hacerla explicitamente el programador.

Por otro lado MyISAM es mas rapido que InnoDB, y soporta archivos mas grandes (mayor catidad de registros).

Yo te diria que uses MyISAM

Saludos

[Graphixx]

Alguna vez lei algo sobre la diferencia de usar char y varchar a la hora de las consultas sobre la BD, deberias tener eso en cuenta tambien, cito:

Ahora que ya hemos definido nuestra clave primaria, echemos un vistazo a nuestra tabla Usuario. Nuestros nombres de columna serán manejados como strings a los cuales se permite que contengan valores alfanuméricos. Los dos campos básicos para contener strings son CHAR y VARCHAR, los cuales se definen en términos de la cadena de texto máxima que pueden almacenar (definida en caracteres, entre 1 y 255). La diferencia entre CHAR y VARCHAR radica en la manera en que manejan el espacio sin usar. Suponte que consideramos que la cadena más larga para un apellido es de 40 caracteres y un usuario tiene el apellido Winstead (8 caracteres). En un campo CHAR, el campo aún seguiría siendo de 40 bytes de tamaño (1 carácter = 1 byte) y los últimos 32 caracteres serían espacios en blanco utilizados para rellenar la cadena a los 40 caracteres completos (los espacios sobrantes serán eliminados cuando recuperes el valor). Esto significa que no importa qué tan larga sea la cadena en realidad, siempre ocupará los 40 caracteres de espacio completos dentro de la tabla. VARHAR, por el contrario, almacenará sólo los 8 caracteres y ocupará solamente 8 bytes de espacio en la tabla.

Tu primer impulso sería elegir VARCHAR y sacar provecho del menor espacio de almacenamiento requerido en la tabla con el objetivo de aumentar la velocidad en las búsquedas tal como yo lo describí anteriormente pero VARCHAR es la excepción a la regla. Cuando usas VARCHAR en una tabla (al igual que ciertas columnas creadas para grandes cantidades de texto y valores binarios) el ancho de cada fila se hace inconsistente. Una fila podría tener 40 bytes de longitud mientras que otra sólo 8. MySQL puede manejar esto almacenando la longitud de la fila en su sistema de archivos pero el servidor debe leer la longitud de cada fila antes de buscarla y continuar con la siguiente fila, donde deberá verificar la longitud otra vez, ad nauseam. Al definir campos de texto como CHAR ocuparemos más espacio en el disco rígido pero habrá un aumento en la velocidad ya que el servidor sabrá que cada una de las filas tiene exactamente el mismo tamaño (por ejemplo 120 bytes), permitiéndole que busque entre las filas sin verificar su longitud primero. Por tal motivo, recomiendo que definas tus campos de texto como CHAR para mejorar la performance de las consultas, a menos que el espacio de almacenamiento sea una consideración importante (y con los precios de los discos de hoy en día esta no es una gran preocupación, principalmente con nuestra aplicación).

Fuente: http://www.vbmysql.com/articles/espanol/el-tutorial-de-vb-mysql-parte-1

[el-brujo]

¿Qué son muchísimas visitas? Recuerda que todo es relativo, y más que las visitas lo importante serán las consultas por segundo.

Este foro manda unas 200 consultas por segundo (hace tiempo que no lo miro) y si algunas tablas son InnoDB pero la mayoría son MyISAM. MyISAM lee mucho más rapido que InnoDB, con lo que convertir la tabla messages en InnoDB es un error.

SMF recomienda convertir algunas tablas a  InnoDB y otras no, dependiendo del uso o si son tablas que normalmnte se leen más que se escriben.

Aunque lo realmente importante será optimizar-tunear el my.cnf los parámetros típicos del  key buffer, etc, etc.

Ah!! y para buscar fulltext mejor usar Sphinx, como el foro, es mucho más rápido.

Herramientas para Optimizar MySQL
http://foro.elhacker.net/tutoriales_documentacion/herramientas_para_optimizar_mysql-t214396.0.html

¡Adiós Locking Tables!
http://foro.elhacker.net/desarrollo_web/iexcladios_locking_tables-t157260.0.html

[Kase]

es posible tener tablas innodb y myisam en la misma base de datos???

yo lo e intentado en mysql worckbeanch pero al cambiar el tipo de una tabla me lo cambia todo =<

[el-brujo]

Claro, prueba con la query:

Código [Seleccionar] Expandir

ALTER TABLE smf_topics
TYPE=InnoDB;