mysql - soporta - Consulta lenta INSERT en la tabla de 200m

optimizar insert masivo sql server

Question

Tenemos la siguiente tabla con aproximadamente 200 millones de registros:

CREATE TABLE IF NOT EXISTS `history` (
  `airline` char(2) NOT NULL,
  `org` char(3) NOT NULL,
  `dst` char(3) NOT NULL,
  `departat` timestamp NOT NULL DEFAULT ''0000-00-00 00:00:00'',
  `arriveat` timestamp NOT NULL DEFAULT ''0000-00-00 00:00:00'',
  `validon` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `price` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE  COLUMNS(org)
(PARTITION p0 VALUES LESS THAN (''AHI'') ENGINE = MyISAM,
 PARTITION p1 VALUES LESS THAN (''ARO'') ENGINE = MyISAM,
 ...
 PARTITION p39 VALUES LESS THAN (''WMA'') ENGINE = MyISAM,
 PARTITION p40 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */;

--
-- Indexes for table `history`
--
ALTER TABLE `history`
 ADD KEY `tail` (`org`,`dst`,`departat`);

Estamos realizando inserciones masivas de algunos VALUES frecuencia, por lo general hasta 1000 registros en consultas INSERT simples, sin ninguna decoración, como ON DUPLICATE KEY (el índice no es único de todos modos).

A veces, cuando voy al estado del servidor en phpMyAdmin, veo un montón de INSERT esperándonos unas a otras, a veces hasta por 300-400 segundos. Nada más parece estar sucediendo en el servidor en el momento en particular. Obtuvimos 32 GB y de otro modo un excelente rendimiento.

¿Cómo solucionar este problema? Gracias por la ayuda.

Answer 1

Hay varias cosas que puede hacer para optimizar inserciones masivas. Una de las cosas es establecer estas variables si está seguro de que sus datos no contienen duplicados (no olvide configurarlos en 1 después de completar la carga):

SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS = 0; SET UNIQUE_CHECKS = 0;

También debe verificar si ningún otro usuario está accediendo a la mesa. También puede intentar usar Innodb ya que se dice que es mejor que MyISAM que maneja inserciones masivas con datos que ya están en él.

También puede verificar la fragmentación en sus tablas, a veces la sobrecarga que le da el sistema operativo al asignar espacio libre en unidades fragmentadas es la causa de la demora.

Answer 2

Probablemente el primer paso es hacer un par de pruebas con perfilado .

Por lo general, harías algo como:

SET LOCAL PROFILING=ON;
-- run your INSERT, like:
INSERT INTO yourtable (id) VALUES (1),(2),(3);

SHOW PROFILES;
+----------+------------+------------------------------------------------+
| Query_ID | Duration   | Query                                          |
+----------+------------+------------------------------------------------+
|     1012 | 6.25220000 | INSERT INTO yourtable (id) VALUES (1),(2),(3); |
+----------+------------+------------------------------------------------+

Esto le dice información muy básica, como la duración de la consulta (6.25 segundos en este caso). Para obtener los detalles reales, necesita desplegar el perfil de dicha consulta:

SHOW PROFILE FOR QUERY 1025; 
+------------------------------+----------+
| Status                       | Duration |
+------------------------------+----------+
| starting                     | 0.004356 |
| checking permissions         | 0.000015 |
| Opening tables               | 6.202999 |
| System lock                  | 0.000017 |
| init                         | 0.000342 |
| update                       | 0.023951 |
| Waiting for query cache lock | 0.000008 |
| update                       | 0.000007 |
| end                          | 0.000011 |
| query end                    | 0.019984 |
| closing tables               | 0.000019 |
| freeing items                | 0.000304 |
| logging slow query           | 0.000006 |
| cleaning up                  | 0.000181 |
+------------------------------+----------+

Puede notar que ''Abrir tablas'' tomó mucho tiempo. En este ejemplo, la ejecución de la consulta se retrasó al bloquear la tabla ( LOCK TABLES ) por otro proceso para retrasar la ejecución. Más información sobre los estados está disponible en el manual .

Answer 3

establece 0 por defecto para los campos de marca de tiempo e intenta

p.ej:

departat timestamp NOT NULL DEFAULT 0,
arriveat timestamp NOT NULL DEFAULT 0,

La marca de tiempo almacenará un valor como entero (marca de tiempo media del tiempo de paso), no guardará registro como fecha y hora.

En su caso, ha establecido el valor predeterminado como formato de fecha y hora en el tipo de campo timestap