Configuración de MySQL óptima para consultas que entregan grandes cantidades de datos

Trabajo como científico y he usado MySQL como almacenamiento para los resultados de mis simulaciones numéricas. Normalmente tengo un conjunto de datos obtenidos por mi experimento y un conjunto de control. Estos dos conjuntos de datos se almacenan en una tabla. Un campo de indicador me dice si un registro proviene de un experimento o de un conjunto de control. Esta tabla generalmente tiene ~ 100 millones de registros. 50 millones de experimentos y 50 millones de controles.

Cuando realizo el procesamiento posterior de mis datos, mi tarea típica consiste en emitir primero las dos consultas siguientes:

select b0,t0 from results_1mregr_c_ew_f where RC='E' and df>60 /// getting experiments data 

y

 select b0,t0 from results_1mregr_c_ew_f where RC='C' and df>60 /// getting controls data 

Tengo un índice de varias columnas en RC, df. Estas consultas toman mucho tiempo y las consultas pasan la mayor parte del tiempo “Enviando datos”

Estoy ejecutando esto en 8core MacPro con 12 GB de RAM. Soy un usuario único de esta máquina y esta tarea es la tarea principal, por lo tanto, puedo dedicar toda la memoria RAM a MySQL. Todas las tablas son MyISAM (puedo convertirlas si eso aumenta la velocidad de mis consultas).

Agradecería cualquier recomendación sobre cómo agilizar estas consultas. ¿Debo cambiar algunas configuraciones, índices, consultas …?

En cada una de estas consultas espero obtener ~ 50 millones de registros. Tenga en cuenta que dividir la tabla en dos tablas, una que contenga una observación experimental y otra que contenga control, no es una opción debido a razones administrativas.

Aquí está la salida de:

 explain select b0, t0 from results_1mregr_c_ew_f where RC="C" and df>60 +----+-----------+---------------------+-----+-------------+---+-------+----+-------+-----------+ | id |select_type|table |type |possible_keys|key|key_len|ref |rows |Extra | +----+-----------+---------------------+-----+-------------+---+-------+----+-------+-----------+ | 1 |SIMPLE |results_1mregr_c_ew_f|range|ff |ff |11 |NULL|6251121|Using where| +----+-----------+---------------------+-----+-------------+---+-------+----+-------+-----------+ 

Aquí está la salida de:

 show indexes from results_1mregr_c_ew_f; +-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | results_1mregr_c_ew_f | 0 | PRIMARY | 1 | id | A | 50793996 | NULL | NULL | | BTREE | | | results_1mregr_c_ew_f | 1 | ff | 1 | RC | A | 3 | NULL | NULL | | BTREE | | | results_1mregr_c_ew_f | 1 | ff | 2 | df | A | 120 | NULL | NULL | | BTREE | | +-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 

Aquí está la salida de:

 CREATE TABLE `results_1mregr_c_ew_f` ( `b0` double NOT NULL COMMENT ' ', `s0` double NOT NULL, `t0` double NOT NULL, `b1` double NOT NULL, `s1` double NOT NULL, `t1` double NOT NULL, `b2` double NOT NULL, `s2` double NOT NULL, `t2` double NOT NULL, `b3` double NOT NULL, `s3` double NOT NULL, `t3` double NOT NULL, `b4` double NOT NULL, `s4` double NOT NULL, `t4` double NOT NULL, `AD` char(4) NOT NULL, `chisq` double NOT NULL, `RC` char(7) NOT NULL, `colq` varchar(255) NOT NULL, `df` int(11) NOT NULL, `ncol` int(11) NOT NULL, `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `p1` float NOT NULL, `p2` float NOT NULL, `p3` float NOT NULL, `p4` float NOT NULL, PRIMARY KEY (`id`), KEY `ff` (`RC`,`df`) ) ENGINE=MyISAM AUTO_INCREMENT=50793997 DEFAULT CHARSET=ascii | 

Algo tiene que ser seriamente incorrecto para que su consulta tome 2 horas para ejecutarse cuando puedo hacer lo mismo en menos de 60 segundos en un hardware similar.

Algunos de los siguientes pueden ser útiles …

Sintoniza MySQL para tu motor

Verifique la configuración de su servidor y optimice según corresponda. Algunos de los siguientes recursos deberían ser útiles.

Ahora para el menos obvio …

Considere usar un procedimiento almacenado para procesar el lado del servidor de datos

¿Por qué no procesa todos los datos dentro de MySQL para que no tenga que enviar grandes cantidades de datos a su capa de aplicación? El siguiente ejemplo utiliza un cursor para recorrer y procesar 50M filas del lado del servidor en menos de 2 minutos. No soy un gran admirador de los cursores, especialmente en MySQL, donde son muy limitados, pero supongo que estarás iterando el conjunto de resultados y haciendo algún tipo de análisis numérico, por lo que el uso de un cursor es justificable en este caso.

Tabla de resultados de myisam simplificada: claves basadas en la tuya.

 drop table if exists results_1mregr_c_ew_f; create table results_1mregr_c_ew_f ( id int unsigned not null auto_increment primary key, rc tinyint unsigned not null, df int unsigned not null default 0, val double(10,4) not null default 0, ts timestamp not null default now(), key (rc, df) ) engine=myisam; 

Genere 100M filas de datos con los campos clave que tienen aproximadamente la misma cardinalidad que en su ejemplo:

 show indexes from results_1mregr_c_ew_f; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Index_type ===== ========== ======== ============ =========== ========= =========== ========== results_1mregr_c_ew_f 0 PRIMARY 1 id A 100000000 BTREE results_1mregr_c_ew_f 1 rc 1 rc A 2 BTREE results_1mregr_c_ew_f 1 rc 2 df A 223 BTREE 

Procedimiento almacenado

Creé un procedimiento almacenado simple que recupera los datos requeridos y lo procesa (usa el mismo estado donde está tu ejemplo)

 drop procedure if exists process_results_1mregr_c_ew_f; delimiter # create procedure process_results_1mregr_c_ew_f ( in p_rc tinyint unsigned, in p_df int unsigned ) begin declare v_count int unsigned default 0; declare v_done tinyint default 0; declare v_id int unsigned; declare v_result_cur cursor for select id from results_1mregr_c_ew_f where rc = p_rc and df > p_df; declare continue handler for not found set v_done = 1; open v_result_cur; repeat fetch v_result_cur into v_id; set v_count = v_count + 1; -- do work... until v_done end repeat; close v_result_cur; select v_count as counter; end # delimiter ; 

Se observaron los siguientes tiempos de ejecución:

 call process_results_1mregr_c_ew_f(0,60); runtime 1 = 03:24.999 Query OK (3 mins 25 secs) runtime 2 = 03:32.196 Query OK (3 mins 32 secs) call process_results_1mregr_c_ew_f(1,60); runtime 1 = 04:59.861 Query OK (4 mins 59 secs) runtime 2 = 04:41.814 Query OK (4 mins 41 secs) counter ======== 23000002 (23 million rows processed in each case) 

Hmmmm, el rendimiento es un poco decepcionante, así que pasa a la siguiente idea.

Considere usar el motor innodb (shock horror)

¿Por qué innodb? ¡porque tiene índices agrupados! Encontrarás una inserción más lenta usando innodb, pero ojalá sea más rápido de leer, por lo que es una compensación que podría valer la pena.

El acceso a una fila a través del índice agrupado es rápido porque los datos de la fila están en la misma página a la que conduce la búsqueda del índice. Si una tabla es grande, la architecture de índice agrupado a menudo guarda una operación de E / S de disco en comparación con las organizaciones de almacenamiento que almacenan datos de fila utilizando una página diferente del registro de índice. Por ejemplo, MyISAM usa un archivo para las filas de datos y otro para los registros de índice.

Más información aquí:

Tabla de resultados de innodb simplificada

 drop table if exists results_innodb; create table results_innodb ( rc tinyint unsigned not null, df int unsigned not null default 0, id int unsigned not null, -- cant auto_inc this !! val double(10,4) not null default 0, ts timestamp not null default now(), primary key (rc, df, id) -- note clustered (innodb only !) composite PK ) engine=innodb; 

Un problema con innodb es que no admite campos de autoincorporación que forman parte de una clave compuesta por lo que tendría que proporcionar el valor clave incremental usted mismo usando un generador de secuencia, disparador o algún otro método, quizás en la aplicación que llena la tabla de resultados. ??

De nuevo, generé 100M filas de datos con los campos clave que tienen aproximadamente la misma cardinalidad que en su ejemplo. No se preocupe si estas cifras no coinciden con el ejemplo de myisam ya que innodb estima las cardinalidades, por lo que no serán exactamente iguales. (pero son – el mismo conjunto de datos utilizado)

 show indexes from results_innodb; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Index_type ===== ========== ======== ============ =========== ========= =========== ========== results_innodb 0 PRIMARY 1 rc A 18 BTREE results_innodb 0 PRIMARY 2 df A 18 BTREE results_innodb 0 PRIMARY 3 id A 100000294 BTREE 

Procedimiento almacenado

El procedimiento almacenado es exactamente el mismo que el ejemplo de myisam anterior pero selecciona datos de la tabla innodb.

 declare v_result_cur cursor for select id from results_innodb where rc = p_rc and df > p_df; 

Los resultados son los siguientes:

 call process_results_innodb(0,60); runtime 1 = 01:53.407 Query OK (1 mins 53 secs) runtime 2 = 01:52.088 Query OK (1 mins 52 secs) call process_results_innodb(1,60); runtime 1 = 02:01.201 Query OK (2 mins 01 secs) runtime 2 = 01:49.737 Query OK (1 mins 50 secs) counter ======== 23000002 (23 million rows processed in each case) 

¡Aproximadamente 2-3 minutos más rápido que la implementación del motor myisam! (innodb FTW)

Divide y conquistaras

Procesar los resultados en un procedimiento almacenado del lado del servidor que utiliza un cursor podría no ser una solución óptima, especialmente porque MySQL no tiene soporte para cosas como matrices y estructuras de datos complejas que están fácilmente disponibles en lenguajes 3GL como C # etc. o incluso en otras bases de datos como Oracle PL / SQL.

Entonces, la idea aquí es devolver lotes de datos a una capa de aplicación (C # lo que sea) que luego puede agregar los resultados a una estructura de datos basada en recostackción y luego procesar los datos internamente.

Procedimiento almacenado

El procedimiento almacenado toma 3 parámetros rc, df_low y df_high que le permiten seleccionar un rango de datos de la siguiente manera:

 call list_results_innodb(0,1,1); -- df 1 call list_results_innodb(0,1,10); -- df between 1 and 10 call list_results_innodb(0,60,120); -- df between 60 and 120 etc... 

Obviamente, cuanto más alto sea el rango df, más datos extraerá.

 drop procedure if exists list_results_innodb; delimiter # create procedure list_results_innodb ( in p_rc tinyint unsigned, in p_df_low int unsigned, in p_df_high int unsigned ) begin select rc, df, id from results_innodb where rc = p_rc and df between p_df_low and p_df_high; end # delimiter ; 

También aniquilé una versión de myisam que es idéntica, excepto por la tabla que se usa.

 call list_results_1mregr_c_ew_f(0,1,1); call list_results_1mregr_c_ew_f(0,1,10); call list_results_1mregr_c_ew_f(0,60,120); 

Basado en el ejemplo de cursor anterior, esperaría que la versión de innodb superara a la de myisam.

Desarrollé una aplicación C # rápida y sucia de subprocesos múltiples que llamará al procedimiento almacenado y agregará los resultados a una colección para el procesamiento posterior a la consulta. No tiene que usar subprocesos, el mismo enfoque de consulta por lotes podría realizarse de forma secuencial sin mucha pérdida de rendimiento.

Cada hilo (QueryThread) selecciona un rango de datos df, bucles el conjunto de resultados y agrega cada resultado (fila) a la colección de resultados.

 class Program { static void Main(string[] args) { const int MAX_THREADS = 12; const int MAX_RC = 120; List signals = new List(); ResultDictionary results = new ResultDictionary(); // thread safe collection DateTime startTime = DateTime.Now; int step = (int)Math.Ceiling((double)MAX_RC / MAX_THREADS) -1; int start = 1, end = 0; for (int i = 0; i < MAX_THREADS; i++){ end = (i == MAX_THREADS - 1) ? MAX_RC : end + step; signals.Add(new AutoResetEvent(false)); QueryThread st = new QueryThread(i,signals[i],results,0,start,end); start = end + 1; } WaitHandle.WaitAll(signals.ToArray()); TimeSpan runTime = DateTime.Now - startTime; Console.WriteLine("{0} results fetched and looped in {1} secs\nPress any key", results.Count, runTime.ToString()); Console.ReadKey(); } } 

Tiempo de ejecución observado de la siguiente manera:

 Thread 04 done - 31580517 Thread 06 done - 44313475 Thread 07 done - 45776055 Thread 03 done - 46292196 Thread 00 done - 47008566 Thread 10 done - 47910554 Thread 02 done - 48194632 Thread 09 done - 48201782 Thread 05 done - 48253744 Thread 08 done - 48332639 Thread 01 done - 48496235 Thread 11 done - 50000000 50000000 results fetched and looped in 00:00:55.5731786 secs Press any key 

Así que 50 millones de filas fueron extraídas y agregadas a una colección en menos de 60 segundos.

Intenté lo mismo con el procedimiento myisam almacenado, que tardó 2 minutos en completarse.

 50000000 results fetched and looped in 00:01:59.2144880 secs 

Mudarse a innodb

En mi sistema simplificado, la tabla myisam no funciona tan mal que no vale la pena migrar a innodb. Si decide copiar sus datos de resultados en una tabla innodb, hágalo de la siguiente manera:

 start transaction; insert into results_innodb select  from results_1mregr_c_ew_f order by ; commit; 

Ordenar el resultado por el PK de innodb antes de insertarlo y envolverlo en una transacción acelerará las cosas.

Espero que algo de esto sea útil.

Buena suerte