Home / Questions / 177036
Accepted
Bernardo Harreguy
Asked: 2020-07-01 12:46:24 +0800 CST 2020-07-01 12:46:24 +0800 CST

Optimize a MySQL query that puts the server at 100% CPU

  • 772

I have a query that makes the MariaDB server use 100% CPU for more than 10 minutes and finally the page times out.

The query in question is the following:

SELECT 
  formasdepago.nombre as 'Forma de pago', 
  SUM(transaccion) AS Total 
FROM contabilidad 
INNER JOIN formasdepago ON formasdepago.id = contabilidad.formadepago 
WHERE contabilidad.anulado = 0  
Group By formadepago

The corresponding tables are the following:

Accounting :

CREATE TABLE `contabilidad` (
    `anulado` TINYINT(1) NOT NULL DEFAULT '0',
    `cuentacorriente` INT(11) NOT NULL DEFAULT '0',
    `cuotas` DECIMAL(10,2) NOT NULL DEFAULT '0',
    `empleado` INT(11) NOT NULL DEFAULT '0',
    `fecha` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `formadepago` INT(11) NOT NULL DEFAULT '1',
    `id` INT(11) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
    `idgasto` INT(11) NOT NULL DEFAULT '0',
    `interesfinanciero` DECIMAL(10,2) NOT NULL DEFAULT '0',
    `interesformadepago` DECIMAL(10,2) NOT NULL DEFAULT '0',
    `ticketid` INT(11) NOT NULL DEFAULT '0',
    `tipodetransaccion` VARCHAR(50) NOT NULL,
    `transaccion` DECIMAL(10,2) NOT NULL DEFAULT '0',
    `valordecuota` DECIMAL(10,2) NOT NULL DEFAULT '0',
    `valorfinal` DECIMAL(10,2) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    INDEX `fecha` (`fecha`),
    INDEX `ticketid` (`ticketid`),
    INDEX `idgasto` (`idgasto`),
    INDEX `formadepago` (`formadepago`),
    FULLTEXT INDEX `tipodetransaccion` (`tipodetransaccion`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=13975947
;

Form of payment :

CREATE TABLE `formasdepago` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `habilitado` INT(1) NOT NULL DEFAULT '1',
    `eliminado` INT(1) NOT NULL DEFAULT '0',
    `simbolo` VARCHAR(5) NOT NULL,
    `diferencia` DECIMAL(11,2) NOT NULL DEFAULT '0',
    `imagen` MEDIUMBLOB NULL DEFAULT NULL,
    `nombre` VARCHAR(50) NOT NULL,
    `preciodelista` INT(1) NOT NULL DEFAULT '0',
    `numeropreciodelista` INT(5) NOT NULL DEFAULT '1',
    `financiacion` INT(5) NOT NULL DEFAULT '0',
    `imagenmini` BLOB NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `id` (`id`),
    INDEX `eliminado` (`eliminado`),
    INDEX `habilitado` (`habilitado`),
    FULLTEXT INDEX `nombre` (`nombre`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=8
;

Accounting has 1.5 GB of information, while payment method only has 3 records.

With such a large amount of data, I separate it by date to perform a smaller query, but I don't understand what the delay is.

Could anyone give me some clue as to what the server is doing that is taking so long? I do not understand, really. I hope someone can help me, thank you very much.

mysql

1 Answers

  1. Best Answer

    Your query cannot be optimized without reducing the number of records added by date, using parallelism as I will explain at the end or another similar system.

    The problem you have is that you are going through millions of records to make the sum of each and every one of them (except those anuladowith 0, which will be few), so the time it takes for the server to show you the results will depend on the hardware available.

    Typically you will be limited by the speed of a single CPU core because the query will be resolved in a single thread/process over the hard drive read speed or amount of RAM.

    In an environment in which I have generated 8 million random records, I have executed the query before and after adding anuladoas an index:

    SELECT
  SUM(transaccion)
FROM contabilidad
WHERE
  anulado = 0

    The execution time of the query has been, in both cases, 32 seconds.

    If we do a DESCRIBEwe get:

    id select_type table        type possible_keys key     key_len ref   rows    Extra
1  SIMPLE      contabilidad ref  anulado       anulado 1       const 4025130

    Which means you have to iterate over 4 million records ( field rows). It is approximately 126k sums of registers per second.

    Your query, unmodified, in my environment takes 36 seconds. It supposes, approximately, 12% more than doing the sum of all the values ​​without relating them to the table of payment methods.

    The only way to improve response times, and only if your server has multiple cores, is to run the sums of each of the payment methods in parallel:

    # Prueba base:
$ time mysql pruebas -e 'SELECT SUM(transaccion) Total FROM contabilidad WHERE contabilidad.anulado = 0'
+---------------+
| Total         |
+---------------+
| 1147206229.96 |
+---------------+

real    0m32.477s
user    0m0.000s
sys 0m0.000s

# Prueba completa:
$ time mysql pruebas -e "SELECT 
>   formasdepago.nombre as 'Forma de pago', 
>   SUM(transaccion) AS Total 
> FROM contabilidad 
> INNER JOIN formasdepago ON formasdepago.id = contabilidad.formadepago 
> WHERE contabilidad.anulado = 0  
> Group By formadepago"
+---------------+--------------+
| Forma de pago | Total        |
+---------------+--------------+
| VISA          | 382738111.90 |
| PAYPAL        | 382286232.86 |
| METÁLICO      | 382181885.20 |
+---------------+--------------+

real    0m36.378s
user    0m0.000s
sys 0m0.000s

# Paralelismo:
$ time ( for i in 1 2 3 ; do time mysql pruebas -e 'SELECT formadepago, SUM(transaccion) Total FROM contabilidad WHERE contabilidad.anulado = 0 AND formadepago = '$i & done ; for pid in $(jobs -p); do wait $pid; done )
formadepago Total
2   382286232.86
real    0m9.414s
user    0m0.000s
sys 0m0.000s

formadepago Total
1   382738111.90
real    0m9.414s
user    0m0.000s
sys 0m0.000s

formadepago Total
3   382181885.20
real    0m9.415s
user    0m0.000s
sys 0m0.000s

real    0m9.416s
user    0m0.000s
sys 0m0.000s

    As you can see the time has been reduced to just 9 seconds.

    In PHP the parallelism could be obtained using Thread.

    • 1