Início / dba / Perguntas / 97560
Accepted
Glenn Plas
Asked: 2015-04-11 07:49:29 +0800 CST

Desempenho de SELECT no cluster MariaDB Galera vs. MariaDB é terrível

  • 772

Estamos avaliando uma configuração do Galera e até agora não notamos muitas desvantagens, exceto algumas consultas que têm um desempenho de leitura terrível, não consigo identificar.

A consulta por si só não é realmente otimizada, mas retorna em menos de 0,20 segundos na caixa de produção. E leva vários minutos em uma configuração robusta de 3 nós da galera. (na verdade, em hardware muito mais robusto).

As versões são para galera:

mysqld  Ver 10.0.16-MariaDB-1~trusty-wsrep-log for 
debian-linux-gnu on x86_64 (mariadb.org binary distribution, wsrep_25.10.r4144)

e para a 'antiga' máquina de prod

mysqld  Ver 5.3.12-MariaDB-mariadb122~maverick for 
debian-linux-gnu on x86_64 ((MariaDB - http://mariadb.com/))

A pergunta:

    MariaDB [ticketing]> EXPLAIN SELECT DISTINCT `purchase`.`id`,
 `purchase`.`invoiceid`, `purchase`.`userid`, `purchase`.`currencyid`, `purchase`.`purchasestatusid`, `purchase`.`isdeleted`,
 `purchase`.`emailshistory`, `purchase`.`created`,
 `purchase`.`paymentfee` FROM `purchase`  
INNER JOIN `payment` ON payment.purchaseid = purchase.id 
WHERE (invoiceid IS NULL) AND (purchasetypeid = 1) 
AND (purchase.created >= '2015-01-19 10:40:17') 
AND (paymenttypeid = 15) ORDER BY `created` DESC;
    +------+-------------+----------+--------+-------------------------------------------------------------------+-----------------+---------+------------------------------+-------+----------------------------------------------+
    | id   | select_type | table    | type   | possible_keys                                                     | key             | key_len | ref                          | rows  | Extra                                        |
    +------+-------------+----------+--------+-------------------------------------------------------------------+-----------------+---------+------------------------------+-------+----------------------------------------------+
    |    1 | SIMPLE      | payment  | ref    | purchaseid,paymenttypeid_2                                        | paymenttypeid_2 | 4       | const                        | 56344 | Using index; Using temporary; Using filesort |
    |    1 | SIMPLE      | purchase | eq_ref | PRIMARY,invoiceid,purchasetypeid,idx_active_purchases,idx_created | PRIMARY         | 4       | ticketing.payment.purchaseid |     1 | Using where                                  |
    +------+-------------+----------+--------+-------------------------------------------------------------------+-----------------+---------+------------------------------+-------+----------------------------------------------+
    2 rows in set (0.00 sec)

    MariaDB [ticketing]>

Executando assim volta:

    +---------+-----------+--------+------------+------------------+-----------+---------------+---------------------+------------+
850 rows in set (0.17 sec)

MariaDB [ticketing]>

Quando executamos isso no cluster galera, a lentidão é insana:

+----------+--------------+---------+
1970 rows in set (5 min 16.64 sec)

Rastrear esse processo mostra que o processo está bastante ocupado, mas o uso da CPU e da memória é baixo (3% , 2%) nada mesmo)

Algumas variáveis ​​de configuração do servidor, cada nó da galera tem 12Gigs de ram enquanto a máquina prod tem apenas 4G

# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
default_storage_engine  = InnoDB
# you can't just change log file size, requires special procedure
#innodb_log_file_size   = 50M
innodb_buffer_pool_size = 4096M
innodb_log_buffer_size  = 8M
innodb_file_per_table   = 1
innodb_open_files       = 1600
innodb_io_capacity      = 400
innodb_flush_method     = O_DIRECT

Plataforma é Ubuntu 14:04 LTS pra galera, produção é 12.04

Galera conf:

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=0
query_cache_size=0
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=1G"

# Galera Cluster Configuration
wsrep_cluster_name="my_test_cluster"
wsrep_cluster_address="gcomm://192.168.128.76,192.168.128.74,192.168.128.83"

# Galera Synchronization Congifuration
wsrep_sst_method=rsync
#wsrep_sst_auth=user:pass

# Galera Node Configuration
wsrep_node_address="192.168.128.74"
wsrep_node_name="ttmasterdb1"

wsrep_slave_threads=16

Entendo otimizações de consulta, então há muito o que dizer sobre como fazer com que os desenvolvedores escrevam consultas melhores, mas como administrador de sistemas, tenho que ser capaz de explicar por que uma consulta abaixo do ideal tem um desempenho muito melhor no banco de dados não-galera. 5 minutos é muito tempo contra 0,17 ms.

Obrigado por todas as informações sobre isso.

ATUALIZAR

Tx para os comentários já, fiz essas perguntas também, aqui estão algumas respostas:

O banco de dados foi importado do servidor antigo, bem importado sem avisos. AFAIK, a explicação é exatamente a mesma, provavelmente a principal razão pela qual viro para empilhar. O conteúdo difere um pouco, mas a divergência entre montagem e produção é devido ao tempo e isso já acontecia desde a importação de qualquer maneira.

Todos os nós do Galera também exibem o mesmo comportamento. Pelo menos é consistente.

Isso é explicação de produção

MariaDB [ticketing]> EXPLAIN SELECT DISTINCT `purchase`.`id`, `purchase`.`invoiceid`, `purchase`.`userid`, `purchase`.`currencyid`, `purchase`.`purchasestatusid`, `purchase`.`isdeleted`, `purchase`.`emailshistory`, `purchase`.`created`, `purchase`.`paymentfee` FROM `purchase`  INNER JOIN `payment` ON payment.purchaseid = purchase.id WHERE (invoiceid IS NULL) AND (purchasetypeid = 1) AND (purchase.created >= '2015-01-19 10:40:17') AND (paymenttypeid = 15) ORDER BY `created` DESC;
+------+-------------+----------+--------+-------------------------------------------------------------------+-----------------+---------+------------------------------+-------+----------------------------------------------+
| id   | select_type | table    | type   | possible_keys                                                     | key             | key_len | ref                          | rows  | Extra                                        |
+------+-------------+----------+--------+-------------------------------------------------------------------+-----------------+---------+------------------------------+-------+----------------------------------------------+
|    1 | SIMPLE      | payment  | ref    | purchaseid,paymenttypeid_2                                        | paymenttypeid_2 | 4       | const                        | 56472 | Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | purchase | eq_ref | PRIMARY,invoiceid,purchasetypeid,idx_active_purchases,idx_created | PRIMARY         | 4       | ticketing.payment.purchaseid |     1 | Using where                                  |
+------+-------------+----------+--------+-------------------------------------------------------------------+-----------------+---------+------------------------------+-------+----------------------------------------------+
2 rows in set (0.00 sec)

Esta é a explicação sobre encenação (galera). Parece o mesmo para mim.

MariaDB [ticketingstaging]> EXPLAIN 
SELECT  DISTINCT `purchase`.`id`, `purchase`.`invoiceid`, `purchase`.`userid`,
        `purchase`.`currencyid`, `purchase`.`purchasestatusid`,
        `purchase`.`isdeleted`, `purchase`.`emailshistory`, `purchase`.`created`,
        `purchase`.`paymentfee`
    FROM  `purchase`
    INNER JOIN  `payment` ON payment.purchaseid = purchase.id
    WHERE  (invoiceid IS NULL)
      AND  (purchasetypeid = 1)
      AND  (purchase.created >= '2015-01-19 10:40:17')
      AND  (paymenttypeid = 15)
    ORDER BY  `created` DESC; 
+------+-------------+----------+--------+-------------------------------------------------------------------+-----------------+---------+-------------------------------------+-------+----------------------------------------------+
| id   | select_type | table    | type   | possible_keys                                                     | key             | key_len | ref                                 | rows  | Extra                                        |
+------+-------------+----------+--------+-------------------------------------------------------------------+-----------------+---------+-------------------------------------+-------+----------------------------------------------+
|    1 | SIMPLE      | payment  | ref    | purchaseid,paymenttypeid_2                                        | paymenttypeid_2 | 4       | const                               | 62898 | Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | purchase | eq_ref | PRIMARY,invoiceid,purchasetypeid,idx_active_purchases,idx_created | PRIMARY         | 4       | ticketingstaging.payment.purchaseid |     1 | Using where                                  |
+------+-------------+----------+--------+-------------------------------------------------------------------+-----------------+---------+-------------------------------------+-------+----------------------------------------------+

Galera:

Create Table: CREATE TABLE `purchase` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `isdeleted` tinyint(1) NOT NULL DEFAULT '0',
  `currencyid` int(11) NOT NULL,
  `channelid` int(11) DEFAULT NULL,
  `mediapartnerid` int(11) DEFAULT NULL,
  `distributionid` int(11) DEFAULT NULL,
  `organizerid` int(11) DEFAULT NULL,
  `vattypeid` int(11) DEFAULT NULL,
  `purchasestatusid` int(11) NOT NULL DEFAULT '1',
  `purchasetypeid` int(11) NOT NULL DEFAULT '1',
  `userid` int(11) DEFAULT NULL,
  `sellerid` int(11) DEFAULT NULL,
  `mailingid` int(11) DEFAULT NULL,
  `invoiceid` int(11) DEFAULT NULL,
  `selectedpaymenttypeid_obsolete` int(11) DEFAULT NULL,
  `organizerpaymentid` int(11) DEFAULT NULL,
  `partnerpaymentid` int(11) DEFAULT NULL,
  `paymentfee` decimal(11,3) NOT NULL DEFAULT '0.000',
  `ticketsreleased` tinyint(1) NOT NULL DEFAULT '0',
  `invoicenum` char(11) DEFAULT NULL,
  `invoicemailed` tinyint(1) NOT NULL DEFAULT '0',
  `invoicerequested` tinyint(1) NOT NULL DEFAULT '0',
  `ogoneredirectedto` varchar(255) DEFAULT NULL,
  `ogoneredirectresponse` text,
  `adyenredirectresponse` text,
  `organizercomment` text,
  `systemcomment` text,
  `organizermailcomment` text,
  `paymenthistory` text,
  `isairmiles` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `emailshistory` text,
  `hasvouchers` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `reminderdate` datetime DEFAULT NULL,
  `onlyreleaseafter` datetime DEFAULT NULL,
  `ip` varchar(255) DEFAULT NULL,
  `sessionid` varchar(255) DEFAULT NULL,
  `ogoneclientcallbackurls` text,
  `shopid` int(11) DEFAULT NULL,
  `created` timestamp NULL DEFAULT NULL,
  `lastchange` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `userid` (`userid`),
  KEY `invoiceid` (`invoiceid`),
  KEY `currencyid` (`currencyid`),
  KEY `purchasetypeid` (`purchasetypeid`),
  KEY `purchasestatusid` (`purchasestatusid`),
  KEY `channelid` (`channelid`),
  KEY `selectedpaymenttypeid` (`selectedpaymenttypeid_obsolete`),
  KEY `mediapartnerid` (`mediapartnerid`),
  KEY `idx_active_purchases` (`purchasetypeid`,`isdeleted`,`ticketsreleased`,`purchasestatusid`),
  KEY `organizerpaymentid` (`organizerpaymentid`),
  KEY `partnerpaymentid` (`partnerpaymentid`),
  KEY `sellerid` (`sellerid`),
  KEY `idx_created` (`created`),
  KEY `idx_comb_purch_id` (`ticketsreleased`,`purchasetypeid`,`purchasestatusid`),
  KEY `purchase_ibfk_14` (`shopid`),
  KEY `distributionid` (`distributionid`),
  KEY `vattypeid` (`vattypeid`),
  KEY `organizerid` (`organizerid`),
  KEY `ticketsreleased` (`ticketsreleased`),
  KEY `isdeleted` (`isdeleted`),
  CONSTRAINT `FK_purchase_distribution123` FOREIGN KEY (`distributionid`) REFERENCES `distribution` (`id`),
  CONSTRAINT `FK_purchase_user` FOREIGN KEY (`organizerid`) REFERENCES `user` (`id`),
  CONSTRAINT `FK_purchase_vattype123` FOREIGN KEY (`vattypeid`) REFERENCES `vattype` (`id`),
  CONSTRAINT `purchase_ibfk_1` FOREIGN KEY (`currencyid`) REFERENCES `currency` (`id`),
  CONSTRAINT `purchase_ibfk_10` FOREIGN KEY (`organizerpaymentid`) REFERENCES `organizerpayment` (`id`),
  CONSTRAINT `purchase_ibfk_11` FOREIGN KEY (`partnerpaymentid`) REFERENCES `partnerpayment` (`id`),
  CONSTRAINT `purchase_ibfk_12` FOREIGN KEY (`sellerid`) REFERENCES `user` (`id`),
  CONSTRAINT `purchase_ibfk_14` FOREIGN KEY (`shopid`) REFERENCES `shop` (`id`),
  CONSTRAINT `purchase_ibfk_3` FOREIGN KEY (`invoiceid`) REFERENCES `invoice` (`id`),
  CONSTRAINT `purchase_ibfk_4` FOREIGN KEY (`purchasetypeid`) REFERENCES `purchasetype` (`id`),
  CONSTRAINT `purchase_ibfk_6` FOREIGN KEY (`purchasestatusid`) REFERENCES `purchasestatus` (`id`),
  CONSTRAINT `purchase_ibfk_7` FOREIGN KEY (`channelid`) REFERENCES `channel` (`id`),
  CONSTRAINT `purchase_ibfk_9` FOREIGN KEY (`mediapartnerid`) REFERENCES `mediapartner` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2510030 DEFAULT CHARSET=utf8

e

    MariaDB [ticketingstaging]> show create table payment\G
*************************** 1. row ***************************
       Table: payment
Create Table: CREATE TABLE `payment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `paymenttypeid` int(11) NOT NULL,
  `purchaseid` int(11) NOT NULL,
  `currencyid` int(11) NOT NULL,
  `distributionid` int(11) DEFAULT NULL,
  `banktransactionid` int(11) DEFAULT NULL,
  `reimbursementv2id` int(11) DEFAULT NULL,
  `paymentfeeschemeid` int(11) DEFAULT NULL,
  `isactive` tinyint(4) NOT NULL DEFAULT '1',
  `amount` decimal(11,3) DEFAULT NULL,
  `paymentfee` decimal(11,3) DEFAULT NULL,
  `totalamount` decimal(11,3) DEFAULT NULL,
  `isconfirmed` tinyint(4) NOT NULL DEFAULT '0',
  `confirmeddatetime` datetime DEFAULT NULL,
  `confirmedbyuserid` int(11) DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL,
  `datereceivedbypaymentprovider` date DEFAULT NULL,
  `paymentproviderreportmatchinfo` text,
  `banktransactionmatchinfo` text,
  `comment` text,
  `instructioncomment` text,
  `created` timestamp NULL DEFAULT NULL,
  `lastchange` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `purchaseid` (`purchaseid`),
  KEY `banktransactionid` (`banktransactionid`),
  KEY `paymenttypeid_2` (`paymenttypeid`,`purchaseid`),
  KEY `currencyid` (`currencyid`),
  KEY `created` (`created`),
  KEY `distributionid` (`distributionid`),
  KEY `reimbursementv2id` (`reimbursementv2id`),
  KEY `paymentfeeschemeid` (`paymentfeeschemeid`),
  KEY `confirmedbyuserid` (`confirmedbyuserid`),
  KEY `isconfirmed` (`isconfirmed`,`confirmeddatetime`),
  CONSTRAINT `FK_payment_confirmedbyuserid` FOREIGN KEY (`confirmedbyuserid`) REFERENCES `user` (`id`),
  CONSTRAINT `FK_payment_distribution` FOREIGN KEY (`distributionid`) REFERENCES `distribution` (`id`),
  CONSTRAINT `FK_payment_paymentfeescheme` FOREIGN KEY (`paymentfeeschemeid`) REFERENCES `paymentfeescheme` (`id`),
  CONSTRAINT `FK_payment_reimbursementv2` FOREIGN KEY (`reimbursementv2id`) REFERENCES `reimbursementv2` (`id`),
  CONSTRAINT `payment_ibfk_1` FOREIGN KEY (`paymenttypeid`) REFERENCES `paymenttype` (`id`),
  CONSTRAINT `payment_ibfk_2` FOREIGN KEY (`purchaseid`) REFERENCES `purchase` (`id`),
  CONSTRAINT `payment_ibfk_3` FOREIGN KEY (`banktransactionid`) REFERENCES `banktransaction` (`id`),
  CONSTRAINT `payment_ibfk_4` FOREIGN KEY (`currencyid`) REFERENCES `currency` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1174354 DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 2228224 kB; (`paymenttypeid`) REFER `ticketing/'

Por falar nisso, aqui está a versão de produção também. A nota COMMENT é bastante estranha, imho. Que coisa estranha de colocar no esquema da tabela

Mariadb 5.5

Create Table: CREATE TABLE `purchase` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `isdeleted` tinyint(1) NOT NULL DEFAULT '0',
  `currencyid` int(11) NOT NULL,
  `channelid` int(11) DEFAULT NULL,
  `mediapartnerid` int(11) DEFAULT NULL,
  `distributionid` int(11) DEFAULT NULL,
  `organizerid` int(11) DEFAULT NULL,
  `vattypeid` int(11) DEFAULT NULL,
  `purchasestatusid` int(11) NOT NULL DEFAULT '1',
  `purchasetypeid` int(11) NOT NULL DEFAULT '1',
  `userid` int(11) DEFAULT NULL,
  `sellerid` int(11) DEFAULT NULL,
  `mailingid` int(11) DEFAULT NULL,
  `invoiceid` int(11) DEFAULT NULL,
  `selectedpaymenttypeid_obsolete` int(11) DEFAULT NULL,
  `organizerpaymentid` int(11) DEFAULT NULL,
  `partnerpaymentid` int(11) DEFAULT NULL,
  `paymentfee` decimal(11,3) NOT NULL DEFAULT '0.000',
  `ticketsreleased` tinyint(1) NOT NULL DEFAULT '0',
  `invoicenum` char(11) DEFAULT NULL,
  `invoicemailed` tinyint(1) NOT NULL DEFAULT '0',
  `invoicerequested` tinyint(1) NOT NULL DEFAULT '0',
  `ogoneredirectedto` varchar(255) DEFAULT NULL,
  `ogoneredirectresponse` text,
  `adyenredirectresponse` text,
  `organizercomment` text,
  `systemcomment` text,
  `organizermailcomment` text,
  `paymenthistory` text,
  `isairmiles` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `emailshistory` text,
  `hasvouchers` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `reminderdate` datetime DEFAULT NULL,
  `onlyreleaseafter` datetime DEFAULT NULL,
  `ip` varchar(255) DEFAULT NULL,
  `sessionid` varchar(255) DEFAULT NULL,
  `ogoneclientcallbackurls` text,
  `shopid` int(11) DEFAULT NULL,
  `created` timestamp NULL DEFAULT NULL,
  `lastchange` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `userid` (`userid`),
  KEY `invoiceid` (`invoiceid`),
  KEY `currencyid` (`currencyid`),
  KEY `purchasetypeid` (`purchasetypeid`),
  KEY `purchasestatusid` (`purchasestatusid`),
  KEY `channelid` (`channelid`),
  KEY `selectedpaymenttypeid` (`selectedpaymenttypeid_obsolete`),
  KEY `mediapartnerid` (`mediapartnerid`),
  KEY `idx_active_purchases` (`purchasetypeid`,`isdeleted`,`ticketsreleased`,`purchasestatusid`),
  KEY `organizerpaymentid` (`organizerpaymentid`),
  KEY `partnerpaymentid` (`partnerpaymentid`),
  KEY `sellerid` (`sellerid`),
  KEY `idx_created` (`created`),
  KEY `idx_comb_purch_id` (`ticketsreleased`,`purchasetypeid`,`purchasestatusid`),
  KEY `purchase_ibfk_14` (`shopid`),
  KEY `distributionid` (`distributionid`),
  KEY `vattypeid` (`vattypeid`),
  KEY `organizerid` (`organizerid`),
  KEY `ticketsreleased` (`ticketsreleased`),
  KEY `isdeleted` (`isdeleted`),
  CONSTRAINT `FK_purchase_distribution123` FOREIGN KEY (`distributionid`) REFERENCES `distribution` (`id`),
  CONSTRAINT `FK_purchase_user` FOREIGN KEY (`organizerid`) REFERENCES `user` (`id`),
  CONSTRAINT `FK_purchase_vattype123` FOREIGN KEY (`vattypeid`) REFERENCES `vattype` (`id`),
  CONSTRAINT `purchase_ibfk_1` FOREIGN KEY (`currencyid`) REFERENCES `currency` (`id`),
  CONSTRAINT `purchase_ibfk_10` FOREIGN KEY (`organizerpaymentid`) REFERENCES `organizerpayment` (`id`),
  CONSTRAINT `purchase_ibfk_11` FOREIGN KEY (`partnerpaymentid`) REFERENCES `partnerpayment` (`id`),
  CONSTRAINT `purchase_ibfk_12` FOREIGN KEY (`sellerid`) REFERENCES `user` (`id`),
  CONSTRAINT `purchase_ibfk_14` FOREIGN KEY (`shopid`) REFERENCES `shop` (`id`),
  CONSTRAINT `purchase_ibfk_3` FOREIGN KEY (`invoiceid`) REFERENCES `invoice` (`id`),
  CONSTRAINT `purchase_ibfk_4` FOREIGN KEY (`purchasetypeid`) REFERENCES `purchasetype` (`id`),
  CONSTRAINT `purchase_ibfk_6` FOREIGN KEY (`purchasestatusid`) REFERENCES `purchasestatus` (`id`),
  CONSTRAINT `purchase_ibfk_7` FOREIGN KEY (`channelid`) REFERENCES `channel` (`id`),
  CONSTRAINT `purchase_ibfk_9` FOREIGN KEY (`mediapartnerid`) REFERENCES `mediapartner` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2519375 DEFAULT CHARSET=utf8

e

Create Table: CREATE TABLE `payment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `paymenttypeid` int(11) NOT NULL,
  `purchaseid` int(11) NOT NULL,
  `currencyid` int(11) NOT NULL,
  `distributionid` int(11) DEFAULT NULL,
  `banktransactionid` int(11) DEFAULT NULL,
  `reimbursementv2id` int(11) DEFAULT NULL,
  `paymentfeeschemeid` int(11) DEFAULT NULL,
  `isactive` tinyint(4) NOT NULL DEFAULT '1',
  `amount` decimal(11,3) DEFAULT NULL,
  `paymentfee` decimal(11,3) DEFAULT NULL,
  `totalamount` decimal(11,3) DEFAULT NULL,
  `isconfirmed` tinyint(4) NOT NULL DEFAULT '0',
  `confirmeddatetime` datetime DEFAULT NULL,
  `confirmedbyuserid` int(11) DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL,
  `datereceivedbypaymentprovider` date DEFAULT NULL,
  `paymentproviderreportmatchinfo` text,
  `banktransactionmatchinfo` text,
  `comment` text,
  `instructioncomment` text,
  `created` timestamp NULL DEFAULT NULL,
  `lastchange` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `purchaseid` (`purchaseid`),
  KEY `banktransactionid` (`banktransactionid`),
  KEY `paymenttypeid_2` (`paymenttypeid`,`purchaseid`),
  KEY `currencyid` (`currencyid`),
  KEY `created` (`created`),
  KEY `distributionid` (`distributionid`),
  KEY `reimbursementv2id` (`reimbursementv2id`),
  KEY `paymentfeeschemeid` (`paymentfeeschemeid`),
  KEY `confirmedbyuserid` (`confirmedbyuserid`),
  KEY `isconfirmed` (`isconfirmed`,`confirmeddatetime`),
  CONSTRAINT `FK_payment_confirmedbyuserid` FOREIGN KEY (`confirmedbyuserid`) REFERENCES `user` (`id`),
  CONSTRAINT `FK_payment_distribution` FOREIGN KEY (`distributionid`) REFERENCES `distribution` (`id`),
  CONSTRAINT `FK_payment_paymentfeescheme` FOREIGN KEY (`paymentfeeschemeid`) REFERENCES `paymentfeescheme` (`id`),
  CONSTRAINT `FK_payment_reimbursementv2` FOREIGN KEY (`reimbursementv2id`) REFERENCES `reimbursementv2` (`id`),
  CONSTRAINT `payment_ibfk_1` FOREIGN KEY (`paymenttypeid`) REFERENCES `paymenttype` (`id`),
  CONSTRAINT `payment_ibfk_2` FOREIGN KEY (`purchaseid`) REFERENCES `purchase` (`id`),
  CONSTRAINT `payment_ibfk_3` FOREIGN KEY (`banktransactionid`) REFERENCES `banktransaction` (`id`),
  CONSTRAINT `payment_ibfk_4` FOREIGN KEY (`currencyid`) REFERENCES `currency` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1178154 DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 2228224 kB; (`paymenttypeid`) REFER `ticketing/'

Cache vars 5.5 , produção

MariaDB [ticketing]> show variables like '%cache%';
+-------------------------------+----------------------+
| Variable_name                 | Value                |
+-------------------------------+----------------------+
| aria_pagecache_age_threshold  | 300                  |
| aria_pagecache_buffer_size    | 134217728            |
| aria_pagecache_division_limit | 100                  |
| binlog_cache_size             | 32768                |
| binlog_stmt_cache_size        | 32768                |
| have_query_cache              | YES                  |
| join_cache_level              | 2                    |
| key_cache_age_threshold       | 300                  |
| key_cache_block_size          | 1024                 |
| key_cache_division_limit      | 100                  |
| key_cache_segments            | 0                    |
| max_binlog_cache_size         | 18446744073709547520 |
| max_binlog_stmt_cache_size    | 18446744073709547520 |
| metadata_locks_cache_size     | 1024                 |
| query_cache_limit             | 131072               |
| query_cache_min_res_unit      | 4096                 |
| query_cache_size              | 33554432             |
| query_cache_strip_comments    | OFF                  |
| query_cache_type              | ON                   |
| query_cache_wlock_invalidate  | OFF                  |
| stored_program_cache          | 256                  |
| table_definition_cache        | 400                  |
| table_open_cache              | 400                  |
| thread_cache_size             | 128                  |
+-------------------------------+----------------------+

Galera

MariaDB [(none)]> show variables like '%cache%';
+--------------------------------+----------------------+
| Variable_name                  | Value                |
+--------------------------------+----------------------+
| aria_pagecache_age_threshold   | 300                  |
| aria_pagecache_buffer_size     | 134217728            |
| aria_pagecache_division_limit  | 100                  |
| aria_pagecache_file_hash_size  | 512                  |
| binlog_cache_size              | 32768                |
| binlog_stmt_cache_size         | 32768                |
| have_query_cache               | YES                  |
| host_cache_size                | 128                  |
| innodb_disable_sort_file_cache | OFF                  |
| innodb_ft_cache_size           | 8000000              |
| innodb_ft_result_cache_limit   | 2000000000           |
| innodb_ft_total_cache_size     | 640000000            |
| join_cache_level               | 2                    |
| key_cache_age_threshold        | 300                  |
| key_cache_block_size           | 1024                 |
| key_cache_division_limit       | 100                  |
| key_cache_file_hash_size       | 512                  |
| key_cache_segments             | 0                    |
| max_binlog_cache_size          | 18446744073709547520 |
| max_binlog_stmt_cache_size     | 18446744073709547520 |
| metadata_locks_cache_size      | 1024                 |
| query_cache_limit              | 131072               |
| query_cache_min_res_unit       | 4096                 |
| query_cache_size               | 0                    |
| query_cache_strip_comments     | OFF                  |
| query_cache_type               | OFF                  |
| query_cache_wlock_invalidate   | OFF                  |
| stored_program_cache           | 256                  |
| table_definition_cache         | 400                  |
| table_open_cache               | 400                  |
| thread_cache_size              | 100                  |
+--------------------------------+----------------------+

Eu sempre pego o mesmo nó para obter essas informações.

Isso também pode ser um fato interessante, a consulta fica em cpy na tabela temporária durante o atraso

| 714 | root        | server9:59763 | ticketingstaging | Query   |    24 | Copying to tmp table | SELECT DISTINCT `purchase`.`id`, `purchase`.`invoiceid`, `purchase`.`userid`, `purchase`.`currencyid |    0.000 |

After switching on the cache per suggestion (which looks like off by default on 14.04 ubuntu) I managed to speed it up considerably but still not acceptable for a web transaction. It's a start, I didn't realize this could have such an effect.

950 rows in set (41.98 sec)

datasizes

MariaDB [(none)]> SELECT table_name AS "Tables",  round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"  FROM information_schema.TABLES  WHERE table_schema = "ticketingstaging" ORDER BY (data_length + index_length) DESC;

+--------------+------------+
| Tables       | Size in MB |
+--------------+------------+
| purchase     |    4383.97 |
| ticket       |    2028.42 |
| ticketsync   |    1637.00 |
| promotioncode|    1501.23 |
| airmileslog  |     693.09 |
| barcode      |     550.19 |
| seat         |     458.63 |
| payment      |     408.13 |
| user         |     363.63 |
| answer       |     284.48 |

prod

+--------------+------------+
| Tables       | Size in MB |
+--------------+------------+
| purchase     |    4706.97 |
| ticket       |    2391.44 |
| ticketsync   |    2126.61 |
| promotioncode|    1603.97 |
| airmileslog  |     758.52 |
| barcode      |     649.20 |
| seat         |     544.67 |
| payment      |     485.27 |
| user         |     383.58 |
mysql mariadb

1 respostas

  1. Best Answer

    I managed to solve this -for the most part- by changing a few parameters. apparently it looks like the database needs to warm up by opening the tables at least once. This is behavior I am familiar with since mysql 5 with plenty of tables and databases.

    The query still runs slow when it's the first time it runs (TABLE CACHE IS OFF). By changing the parameters below, this only happens on the first run of the query. Any additional attempts to run it after that does not exhibit this slow down anymore. The following parameters helped

    [mysqld_safe]
open-files-limit = 65535

    And 

    [mysqld]
innodb_open_files       = 16384

table_cache = 800
table_open_cache = 40000
table_definition_cache = 3000
thread_cache_size = 50

    I believe the reason is that the information_scheme is the bottleneck here. Afaik, a known problem when having plenty of tables around.

    • 2

relate perguntas