mysql partitioning database-performance sharding

MySQL Partitioning/Sharding/Splitting: ¿qué camino tomar?



database-performance (9)

¿Qué hace la gran mesa?

Si vas a dividirlo, tienes algunas opciones:
- Dividirlo usando el sistema de base de datos (no sé mucho sobre eso)
- Dividirlo por fila.
- dividirlo por columna.

Dividirlo por filas solo sería posible si sus datos se pueden separar fácilmente en fragmentos. Por ejemplo, algo como Basecamp tiene múltiples cuentas que están completamente separadas. Puede mantener el 50% de las cuentas en una tabla y el 50% en una tabla diferente en una máquina diferente.

Dividir por columna es bueno para situaciones en las que el tamaño de fila contiene campos de texto grandes o BLOB. Si tiene una tabla con (por ejemplo) una imagen de usuario y un gran bloque de texto, puede cultivar la imagen en una tabla completamente diferente. (en una máquina diferente)

Se rompe la normalización aquí, pero no creo que cause demasiados problemas.

Tenemos una base de datos InnoDB de unos 70 GB y esperamos que crezca a varios cientos de GB en los próximos 2 o 3 años. Alrededor del 60% de los datos pertenecen a una sola tabla. Actualmente, la base de datos funciona bastante bien ya que tenemos un servidor con 64 GB de RAM, por lo que casi toda la base de datos se adapta a la memoria, pero nos preocupa el futuro cuando la cantidad de datos será considerablemente mayor. En este momento estamos considerando alguna manera de dividir las tablas (especialmente la que representa la mayor parte de los datos) y ahora me pregunto cuál sería la mejor manera de hacerlo.

Las opciones que conozco actualmente son

  • Usando el Particionamiento de MySQL que viene con la versión 5.1
  • Uso de algún tipo de biblioteca de terceros que encapsula la partición de los datos (como fragmentos de hibernación)
  • Implementándolo nosotros mismos dentro de nuestra aplicación

Nuestra aplicación está construida en J2EE y EJB 2.1 (con suerte cambiaremos a EJB 3 algún día).

¿Qué sugieres?

EDITAR (2011-02-11):
Solo una actualización: actualmente el tamaño de la base de datos es de 380 GB, el tamaño de datos de nuestra tabla "grande" es de 220 GB y el tamaño de su índice es de 36 GB. Entonces, aunque toda la tabla ya no cabe en la memoria, el índice sí lo hace.
El sistema sigue funcionando bien (todavía en el mismo hardware) y todavía estamos pensando en dividir los datos.

EDITAR (2014-06-04): Una actualización más: el tamaño de toda la base de datos es 1.5 TB, el tamaño de nuestra tabla "grande" es 1.1 TB. Actualizamos nuestro servidor a una máquina de 4 procesadores (Intel Xeon E7450) con 128 GB de RAM. El sistema sigue funcionando bien. Lo que estamos planificando hacer ahora es poner nuestra gran mesa en un servidor de base de datos separado (ya hemos hecho los cambios necesarios en nuestro software) al tiempo que actualizamos a un nuevo hardware con 256 GB de RAM.

Se supone que esta configuración durará dos años. Entonces tendremos que finalmente comenzar a implementar una solución de fragmentación o simplemente comprar servidores con 1 TB de RAM que nos mantendrán activos por un tiempo.

EDITAR (2016-01-18):

Desde entonces, hemos puesto nuestra gran mesa en su propia base de datos en un servidor separado. Actualmente el tamaño de esta base de datos es de aproximadamente 1.9 TB, el tamaño de la otra base de datos (con todas las tablas excepto la "grande") es de 1.1 TB.

Configuración actual del hardware:

  • HP ProLiant DL 580
  • 4 x Intel (R) Xeon (R) CPU E7- 4830
  • 256 GB de RAM

El rendimiento está bien con esta configuración.

En primer lugar, no importa tanto dividir las tablas a menos que también mueva algunas de las tablas a un volumen físico por separado.

En segundo lugar, no es necesariamente la tabla con el tamaño físico más grande que desea mover. Puede tener una tabla mucho más pequeña que obtiene más actividad, mientras que su tabla grande permanece bastante constante o solo agrega datos.

Hagas lo que hagas, no lo implementes tú mismo. Deje que el sistema de base de datos lo maneje.

Hace un tiempo en un evento de Microsoft ArcReady, vi una presentación sobre los patrones de escala que podrían serle útiles. Puede ver las diapositivas en línea.

Si crees que vas a estar vinculado a IO / memoria, no creo que la partición sea útil. Como de costumbre, la evaluación comparativa primero te ayudará a descubrir la mejor dirección. Si no tiene servidores de repuesto con 64 GB de memoria dando vueltas, siempre puede pedirle a su proveedor una ''unidad de demostración''.

Me inclinaría por sharding si no espera 1 informe global de consulta. Supongo que harías fragmentos de toda la base de datos y no solo de tu gran mesa: es mejor mantener entidades enteras juntas. Bueno, si tu modelo se divide muy bien, de todos modos.

Como de costumbre, la evaluación comparativa primero te ayudará a descubrir la mejor dirección.

Eso es lo que la mayoría de la gente me dice, así que creo que finalmente tendré que tomar esa píldora ...

Probablemente quieras dividir esa gran mesa eventualmente. Probablemente desee colocarlo en un disco duro por separado antes de pensar en un segundo servidor. Hacerlo con MySQL es la opción más conveniente. Si es capaz, entonces ve por ello.

PERO

Todo depende de cómo se usa realmente tu base de datos. Estadística.

Definitivamente comenzarás a tener problemas en esa tabla de 42 GB una vez que ya no cabe en la memoria. De hecho, tan pronto como ya no cabe en la memoria, el rendimiento se degradará extremadamente rápido. Una forma de probar es poner esa tabla en otra máquina con menos RAM y ver qué tan pobre funciona.

En primer lugar, no importa tanto dividir las tablas a menos que también mueva algunas de las tablas a un volumen físico por separado.

Esto es incorrecto. La partición (ya sea a través de la característica en MySQL 5.1, o lo mismo con las tablas MERGE) puede proporcionar importantes beneficios de rendimiento, incluso si las tablas están en la misma unidad.

Como ejemplo, digamos que está ejecutando consultas SELECT en su gran mesa utilizando un rango de fechas. Si la tabla es completa, la consulta se verá obligada a escanear toda la tabla (y en ese tamaño, incluso utilizando índices puede ser lenta). La ventaja de la partición es que sus consultas solo se ejecutarán en las particiones donde sea absolutamente necesario. Si cada partición tiene un tamaño de 1 GB y su consulta solo necesita acceder a 5 particiones para cumplirse, la tabla combinada de 5 GB es mucho más fácil de manejar para MySQL que una versión monstruosa de 42 GB.

Una cosa que debe preguntarse es cómo está consultando los datos. Si existe la posibilidad de que sus consultas solo necesiten acceder a ciertos fragmentos de datos (es decir, un rango de fechas o un rango de ID), la partición de algún tipo resultará beneficiosa.

He escuchado que todavía hay algunos errores con la partición de MySQL 5.1, particularmente los relacionados con MySQL que eligen la clave correcta. Las tablas MERGE pueden proporcionar la misma funcionalidad, aunque requieren un poco más de sobrecarga.

¡Espero que ayude, buena suerte!

Me gustaría ir a MariaDB InnoDB + Particiones (ya sea por clave o por fecha, dependiendo de sus consultas).

Hice esto y ahora ya no tengo problemas con la Base de datos.

MySQL puede reemplazarse con MariaDB en segundos ... todos los archivos de la base de datos permanecen iguales.