tablas - variables mysql

Manejo de datos muy grandes con mysql (2)

Al recopilar miles de millones de filas, es mejor (cuando sea posible) consolidar, procesar, resumir, lo que sea, los datos antes de almacenarlos. Guarde los datos en bruto en un archivo si cree que necesita volver a ellos.

Hacer eso eliminará la mayoría de sus preguntas e inquietudes, además de acelerar el procesamiento.

Lo siento por el largo post!

Tengo una base de datos que contiene ~ 30 tablas (motor InnoDB). Solo dos de estas tablas, a saber, "transacción" y "cambio" son bastante grandes (la primera tiene 1.5 millones de filas y el turno tiene 23k filas). Ahora todo funciona bien y no tengo problemas con el tamaño de la base de datos actual.

Sin embargo, tendremos una base de datos similar (mismos tipos de datos, diseño, ...) pero mucho más grande, por ejemplo, la tabla de "transacciones" tendrá alrededor de mil millones de registros (aproximadamente 2,3 millones de transacciones por día) y estamos pensando en cómo ¿Deberíamos tratar con tal volumen de datos en MySQL? (Es intensivo en lectura y escritura). Leí muchas publicaciones relacionadas para ver si Mysql (y más específicamente el motor InnoDB) puede funcionar bien con miles de millones de registros, pero aún tengo algunas preguntas. Algunas de las publicaciones relacionadas que he leído son las siguientes:

Lo que he entendido hasta ahora para mejorar el rendimiento de tablas muy grandes:

(para las tablas innoDB que es mi caso) aumentando el innodb_buffer_pool_size (por ejemplo, hasta el 80% de la RAM). Además, encontré algunas otras configuraciones de ajuste de rendimiento de MySQL aquí en percona blog
tener índices adecuados en la tabla (usando EXPLAN en consultas)
particionando la mesa
MySQL Sharding o agrupamiento

Aquí están mis preguntas / confusiones:

Sobre la partición, tengo algunas dudas sobre si deberíamos usarla o no. Por un lado, mucha gente lo sugirió para mejorar el rendimiento cuando la mesa es muy grande. Por otro lado, he leído muchas publicaciones que dicen que no mejora el rendimiento de las consultas y que las consultas no se ejecutan más rápido (por ejemplo, here y here ). Además, leí en el Manual de referencia de MySQL que las claves externas de InnoDB y la partición de MySQL no son compatibles (tenemos claves externas).
Con respecto a los índices, en este momento tienen un buen desempeño, pero, según tengo entendido, la indexación de tablas muy grandes es más restrictiva (como mencionó Kevin Bedell en su respuesta aquí ). Además, los índices aceleran las lecturas mientras se ralentizan la escritura (insertar / actualizar). Entonces, para el nuevo proyecto similar que tendremos esta gran base de datos, ¿deberíamos primero insertar / cargar todos los datos y luego crear índices? (para acelerar el inserto)
Si no podemos usar la partición para nuestra tabla grande (tabla de "transacciones"), ¿cuál es una opción alternativa para mejorar el rendimiento? (excepto la configuración de variables de MySQl, como innodb_buffer_pool_size ). ¿Deberíamos usar clusters mysql? (También tenemos muchas uniones)

EDITAR

Esta es la declaración de show create table para nuestra tabla más grande llamada "transacción":

CREATE TABLE `transaction` ( `id` int(11) NOT NULL AUTO_INCREMENT, `terminal_transaction_id` int(11) NOT NULL, `fuel_terminal_id` int(11) NOT NULL, `fuel_terminal_serial` int(11) NOT NULL, `xboard_id` int(11) NOT NULL, `gas_station_id` int(11) NOT NULL, `operator_id` text NOT NULL, `shift_id` int(11) NOT NULL, `xboard_total_counter` int(11) NOT NULL, `fuel_type` int(11) NOT NULL, `start_fuel_time` int(11) NOT NULL, `end_fuel_time` int(11) DEFAULT NULL, `preset_amount` int(11) NOT NULL, `actual_amount` int(11) DEFAULT NULL, `fuel_cost` int(11) DEFAULT NULL, `payment_cost` int(11) DEFAULT NULL, `purchase_type` int(11) NOT NULL, `payment_ref_id` text, `unit_fuel_price` int(11) NOT NULL, `fuel_status_id` int(11) DEFAULT NULL, `fuel_mode_id` int(11) NOT NULL, `payment_result` int(11) NOT NULL, `card_pan` text, `state` int(11) DEFAULT NULL, `totalizer` int(11) NOT NULL DEFAULT ''0'', `shift_start_time` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `terminal_transaction_id` (`terminal_transaction_id`,`fuel_terminal_id`,`start_fuel_time`) USING BTREE, KEY `start_fuel_time_idx` (`start_fuel_time`), KEY `fuel_terminal_idx` (`fuel_terminal_id`), KEY `xboard_idx` (`xboard_id`), KEY `gas_station_id` (`gas_station_id`) USING BTREE, KEY `purchase_type` (`purchase_type`) USING BTREE, KEY `shift_start_time` (`shift_start_time`) USING BTREE, KEY `fuel_type` (`fuel_type`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1665335 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

Gracias por tu tiempo,

¿Puede MySQL razonablemente realizar consultas en miles de millones de filas? - MySQL puede "manejar" miles de millones de filas. "Razonablemente" depende de las consultas; vamos a verlos
¿Es InnoDB (MySQL 5.5.8) la opción correcta para miles de millones de filas? - 5.7 tiene algunas mejoras, pero 5.5 es bastante bueno, a pesar de tener casi 6 años, y al borde de no ser apoyado.
El mejor almacén de datos para miles de millones de filas: si te refieres a ''Motor'', entonces InnoDB.
¿Qué tamaño puede obtener una base de datos MySQL antes de que el rendimiento comience a degradarse? De nuevo, eso depende de las consultas. Puedo mostrarte una tabla de filas de 1K que se derretirá; He trabajado con mesas de miles de millones que zumban a lo largo.
¿Por qué MySQL podría ser lento con tablas grandes? - Las exploraciones de rango conducen a E / S, que es la parte lenta.
¿Puede Mysql manejar tablas que tendrán cerca de 300 millones de registros? - de nuevo sí. El límite está en algún lugar alrededor de un billón de filas.
(para las tablas innoDB que es mi caso) aumentando el innodb_buffer_pool_size (por ejemplo, hasta el 80% de la RAM). Además, encontré otras configuraciones de ajuste de rendimiento de MySQL aquí en el blog de Percona: sí
tener índices adecuados en la tabla (usando EXPLAN en consultas) - bueno, veamoslos. Hay muchos errores que se pueden cometer en esta área crítica .
Particionando la tabla - "¡La partición no es una panacea!" Me arrepiento de eso en mi blog
MySQL Sharding - Actualmente esto es DIY
Clúster de MySQL: actualmente la mejor respuesta es alguna opción basada en Galera (PXC, MariaDB 10, DIY con Oracle)
La partición no es compatible con FOREIGN KEY o "global" UNIQUE .
Los UUID, en la escala de la que está hablando, no solo ralentizarán el sistema, sino que lo matarán. Los UUID tipo 1 pueden ser una solución.
Velocidad de inserción y creación de índices: hay demasiadas variaciones para dar una sola respuesta. Veamos su tentativa CREATE TABLE y cómo piensa alimentar los datos.
Muchas combinaciones: "Normalizar, pero no sobre-normalizar". En particular, no normalice los tiempos de datos o los datos flotantes u otros valores "continuos".
Construye tablas de resumen
2,3 millones de transacciones por día: si eso es 2,3M inserciones (30 / s), entonces no hay mucho problema de rendimiento. Si es más complejo, entonces puede ser necesario RAID, SSD, procesamiento por lotes, etc.
lidiar con dicho volumen de datos: si la mayor parte de la actividad es con las filas "recientes", entonces buffer_pool "almacenará en caché" la actividad, evitando así la E / S. Si la actividad es "aleatoria", entonces MySQL (o cualquier otra persona ) tendrá problemas de E / S.