Tengo una consulta que se ejecuta en unos 20 segundos en un servidor MySQL 5.1, pero tarda casi 15 minutos en un servidor MariaDB 5.5. Los sospechosos habituales como key_buffer_size y tmp_table_size y max_heap_table_size son todos iguales (128M). La mayoría de las configuraciones son iguales por lo que puedo ver (query_cache, etc.)

La consulta:

SELECT  products.id, 
concat(publications.company_name,' [',publications.quote,'] ', products.name) as n, 
products.impressions, 
products.contacts, 
is_channel, 
sl.i, 
count(*) 
FROM products 
LEFT JOIN publications ON products.publications_id = publications.id 
LEFT OUTER JOIN (  
    SELECT adspace.id AS i, 
    slots.products_id FROM adspace 
    LEFT JOIN  slots ON adspace.slots_id = slots.id 
        AND adspace.end > '2016-01-25 10:28:49' 
        WHERE adspace.active = 1) AS sl 
    ON sl.products_id = products.id  
WHERE 1 = 1 
AND publications.active=1 
GROUP BY products.id 
ORDER BY n ASC;

La única diferencia está en la fase de explicación:

Servidor antiguo (MySQL 5.1)

+----+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+
| id | select_type | table        | type   | possible_keys | key     | key_len | ref                                     | rows   | Extra                           |
+----+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+
|  1 | PRIMARY     | products     | ALL    | NULL          | NULL    | NULL    | NULL                                    |   6568 | Using temporary; Using filesort |
|  1 | PRIMARY     | publications | eq_ref | PRIMARY       | PRIMARY | 4       | db.products.publications_id |      1 | Using where                                 |
|  1 | PRIMARY     | <derived2>   | ALL    | NULL          | NULL    | NULL    | NULL                                    |  94478 |                                 |
|  2 | DERIVED     | adspace      | ALL    | NULL          | NULL    | NULL    | NULL                                    | 101454 | Using where                     |
|  2 | DERIVED     | slots        | eq_ref | PRIMARY       | PRIMARY | 4       | db.adspace.slots_id         |      1 |                                             |
+----+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+

Nuevo servidor (MariaDB 5.5)

+------+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+
| id   | select_type | table        | type   | possible_keys | key     | key_len | ref                                     | rows   | Extra                           |
+------+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+
|    1 | SIMPLE      | products     | ALL    | test_idx      | NULL    | NULL    | NULL                                    |   6557 | Using temporary; Using filesort |
|    1 | SIMPLE      | publications | eq_ref | PRIMARY       | PRIMARY | 4       | db.products.publications_id |      1 | Using where                                 |
|    1 | SIMPLE      | adspace      | ALL    | NULL          | NULL    | NULL    | NULL                                    | 100938 | Using where                     |
|    1 | SIMPLE      | slots        | eq_ref | PRIMARY       | PRIMARY | 4       | db.adspace.slots_id         |      1 | Using where                                 |
+------+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+

Se agregó un índice a la tabla de productos en el nuevo servidor para acelerar las cosas, pero fue en vano.

Variables del motor:

Servidor antiguo:

mysql> show variables like '%engine%';
+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| engine_condition_pushdown | ON     |
| storage_engine            | MyISAM |
+---------------------------+--------+

mysql> show variables like '%buffer_pool%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_buffer_pool_size | 8388608 |
+-------------------------+---------+

Nuevo servidor:

MariaDB [db]> show variables like '%engine%';
+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| default_storage_engine    | InnoDB |
| engine_condition_pushdown | OFF    |
| storage_engine            | InnoDB |
+---------------------------+--------+


MariaDB [db]> show variables like '%buffer_pool%';
+---------------------------------------+-----------+
| Variable_name                         | Value     |
+---------------------------------------+-----------+
| innodb_blocking_buffer_pool_restore   | OFF       |
| innodb_buffer_pool_instances          | 1         |
| innodb_buffer_pool_populate           | OFF       |
| innodb_buffer_pool_restore_at_startup | 0         |
| innodb_buffer_pool_shm_checksum       | ON        |
| innodb_buffer_pool_shm_key            | 0         |
| innodb_buffer_pool_size               | 134217728 |
+---------------------------------------+-----------+

Todas las tablas utilizadas en la consulta son MyISAM (servidor antiguo y nuevo)

El perfil mostró que la consulta anterior tarda alrededor de 16 segundos en 'copiar a la tabla tmp' y el nuevo servidor alrededor de 800 segundos en esta fase.

Todos los servidores nuevos tienen discos SSD para almacenamiento y los servidores antiguos tienen discos normales.

Editar : también tengo un servidor MySQL 5.5 y allí la consulta solo toma alrededor de 10 segundos. También con la misma configuración por lo que puedo ver.

Traté de resumirlo en una tabla:

Location:       Customer                    Own                     Customer
MySQL Type:     MySQL                       MySQL                   MariaDB
Mysql Version:  5.1.56-community-log        5.5.39-1-log (Debian)   5.5.44-MariaDB-log
HDD:            Normal                      Normal                  SSD
Type:           Virtual                     Real                    Virtual
Query time:     ~15s                        ~10s                    ~15min
DB engine:      MyISAM                      InnoDB                  InnoDB
Table Engine:   MyISAM                      MyISAM                  MyISAM

No quiero reescribir la consulta (aunque podría necesitar algo de trabajo) pero quiero encontrar la diferencia entre las 2 máquinas, supongo que es una configuración que no es ideal en MariaDB pero no puedo encontrarla.

11
darkownage 26 ene. 2016 a las 13:17

2 respuestas

La mejor respuesta

Puede que esta no sea la respuesta, pero MariaDB 5.5 usa un algoritmo diferente para realizar una unión. Hasta donde yo sé en MariaDB 5.5 Se introdujo la combinación de acceso de clave por lotes. Las versiones anteriores de MySQL o MariaDB usan una diferente. Aunque la nueva versión debería ser más rápida en la mayoría de los casos, es posible que sus tablas específicas funcionen mejor con la anterior.

Editar: esta respuesta podría ser obvia, ya que mencionó que utilizó diferentes motores de almacenamiento.

1
Leon 26 ene. 2016 a las 10:37