tablas soporta segundo por optimizar lentas grandes eficientes cuantas consultas con avanzadas mysql sql performance elasticsearch innodb

mysql - soporta - optimizar consultas sql server



Mysql: 7 billones de registros en una tabla. (3)

Necesito guardar alrededor de 7.8 billones de registros en una tabla Mysql. La tabla es de lectura y escritura intensivas. Tengo que mantener al menos 0,02 mil millones de registros por hora de inserción. Mientras que la búsqueda en la mesa no debe tomar más de 10 segundos. Tenemos una interfaz de usuario desde donde los usuarios pueden buscar en función de diferentes propiedades de columnas.

La consulta mayoritariamente buscada puede ser como:

  1. select * from mytable where prop1=''sip:+100008521149'' and prop2=''asdsa'' order by event_timestamp desc limit 10;

  2. select * from mytable where prop1=''sip:+100008521149'' order by event_timestamp desc limit 10;

  3. select * from mytable where prop2=''asdsa'' order by event_timestamp desc limit 10;

Actualmente hay 2 índices en la tabla:

1- idx_1(prop1,event_timestamp) 2- idx_2(prop2,event_timestamp)

Los ajustes de InnoDB son los siguientes:

innodb_buffer_pool_size = 70G innodb_log_file_size = 4G innodb_io_capacity=2000 innodb_io_capacity_max=6000 innodb_lru_scan_depth=2000 innodb_flush_log_at_trx_commit=2 innodb_log_buffer_size=16M innodb_thread_concurrency = 0 innodb_read_io_threads = 64 innodb_write_io_threads = 64 innodb_autoinc_lock_mode = 2 bulk_insert_buffer_size=33554432 query_cache_type=1 query_cache_size=64M innodb_flush_neighbors=0 expire_logs_days=10 max-connections=500 long_query_time = 5 read_buffer_size=16M sort_buffer_size=16M read_rnd_buffer_size=16M innodb_doublewrite = 0 innodb_flush_method=O_DIRECT

Machine''s RAM size is 99 GB.

Una vez iniciado, el sistema fue rápido, pero el rendimiento se reduce mucho cuando el récord alcanza los 0,22 mil millones. Aunque estamos usando LOAD INFILE, la tasa de inserción fue muy lenta. La búsqueda fue bastante rápida mientras se buscaba en parámetros indexados. Parece que la agrupación de almacenamiento intermedio no es suficiente.

Tengo algunas preguntas:

  1. ¿Es posible soportar este tipo de datos con esta configuración?

  2. ¿Cuál debería ser el tamaño ideal y práctico de la agrupación de almacenamiento intermedio para 7 mil millones de registros?

  3. El tamaño de DATA + INDEX se acerca a los 150 GB con solo 0,22 mil millones de registros. Parece que necesito tener TB de carnero.
  4. Estamos pensando en la configuración Maestro / Esclavo para hacer que el congif para lectura y escritura sea dominante en los servidores respectivos.
  5. ¿Alguna otra forma mejor de diseñar esta solución?
  6. Aumentar más índices hace que la búsqueda de UI sea mejor, pero aumentar un solo índice está reduciendo la velocidad de inserción en muchos pliegues.

UPADTE: 1

Q- La tabla es mucho más grande que la memoria RAM, ¿correcto? El buffer_pool no puede ser lo suficientemente grande, debe ser más pequeño que el ram, o el rendimiento sufre.

A- El tamaño de la memoria RAM es de 100 GB, el grupo de búferes es de 70 G. Sí, el tamaño de los datos es demasiado grande que la memoria RAM.

Q- Por favor provea SHOW CREATE TABLE; Hay varios temas que necesito investigar. (tipos de datos, tamaños de campo, etc.)

A- Todos los campos son de tipo cadena. Hemos utilizado varchar (127) para todos. PK es autogenerado id bigint (20).

P- ¿Cuántos registros hay en el CARGADOR DE DATOS? ¿CARGAS directamente en la mesa? ¿Con qué frecuencia es la carga?

A- 100000 registros por archivo. Múltiples hilos están cargando datos desde un archivo CSV a DB. En la migración inicial, tenemos que cargarla continuamente hasta 0,65 mil millones de registros. Después de esa frecuencia se reducirá a alrededor de cada 15 minutos.

Q- Maestro + Esclavo: tenga en cuenta que todas las escrituras se realizan también en el Esclavo. Si tiene muchas lecturas, entonces más de un esclavo difundiría las lecturas, obteniendo así una escala.

A- Estamos probando actualmente con el enfoque MASTER / SLAVE.

Hicimos MASTER con MYISAM y sin índices. MASTER se utilizará para inserciones. ESCLAVO que tiene INNODB y con 2 índices. La búsqueda se llevará a cabo en él. Ambas son máquinas diferentes y no comparten RAM o CPU. La aplicación está en la tercera máquina.

Q- ¿Tienes discos giratorios? ¿O SSDs? A- ¿Cómo comprobarlo?

Q- Tus filas parecen ser bastante grandes. ¿Hay TEXTs o BLOBs? Si es así, SELECT * puede ser una seria carga de rendimiento.

A- sí, las filas tienen 50 columnas, pero los datos están en alrededor de 15-20 columnas. No podemos reducir el tamaño de los tipos de datos, ya que todos los campos pueden contener cualquier cantidad de datos alfanuméricos. Todos son TEXTOS no BLOBOS.

Desactivar el caché de consultas: debe purgar todas las entradas en el QC cada vez que se produce un INSERT , ¡eso es 5555 veces por segundo!

query_cache_type = 0 query_cache_size = 0

La primera consulta necesita INDEX(prop1, prop2, event_timestamp) . (Los prop1 y prop2 pueden ser intercambiados.)

Con ese índice agregado, cada una de las tres consultas tocará no más de 10 filas en el índice y no hará más de 10 búsquedas aleatorias (?) En los datos. En el peor de los casos, eso es solo alrededor de 11 golpes de disco. Y la ''evaluación perezosa'' de @Bernd no lo mejorará.

La mesa es mucho más grande que la memoria RAM, ¿correcto? El buffer_pool no puede ser lo suficientemente grande, debe ser más pequeño que el ram, o el rendimiento sufre .

Por favor provea SHOW CREATE TABLE ; Hay varios temas que necesito investigar. (tipos de datos, tamaños de campo, etc.)

¿Cuántos registros en el LOAD DATA INFILE ? ¿ LOAD directamente en la mesa? ¿Con qué frecuencia es la LOAD ?

Maestro + esclavo: tenga en cuenta que todas las escrituras se realizan también en el esclavo. Si tiene muchas lecturas, entonces más de un esclavo difundiría las lecturas , obteniendo así una escala.

¿Tienes discos giratorios? ¿O SSDs?

Tus filas parecen ser bastante grandes. ¿Hay TEXTs o BLOBs ? Si es así, SELECT * puede ser una seria carga de rendimiento.

Esta no es la Respuesta, pero no puedo formatearla en un comentario.

Por favor, intente esto para ver si es más rápido. por lo tanto, MySQL no debe ordenar las filas de agujeros solo la ID (CLAVE principal)

SELECT r.* FROM ( SELECT id FROM mytable WHERE prop1=''sip:+100008521149'' AND prop2=''asdsa'' ORDER BY event_timestamp DESC LIMIT 10 ) AS r LEFT JOIN mytable m ON m.id =r.id ORDER BY r.event_timestamp DESC;

Logré este requisito al reemplazar MYSQL DB con Elasticsearch. Parece un ajuste perfecto para una tasa de inserción rápida y una búsqueda muy rápida. Además, las capacidades de texto completo de Lucene la convierten en una herramienta perfecta. La mejor parte de ES es que tiene requisitos de hardware muy bajos. Se escala horizontalmente en lugar de verticalmente.