主页 / dba / 问题 / 97560
Accepted
Glenn Plas
Asked: 2015-04-11 07:49:29 +0800 CST

MariaDB Galera 集群与 MariaDB 的 SELECT 性能很糟糕

  • 772

我们正在评估一个 Galera 设置，到目前为止，我们还没有注意到很多缺点，除了一些读取性能很差的查询，我无法指出它。

查询本身并没有真正优化，但它在生产盒上的返回时间不到 0.20 秒。并且在强大的 galera 3 节点设置上需要几分钟。（实际上在更强大的硬件上）。

这些版本适用于 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)

对于“旧”的产品机器

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

查询：

    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]>

像这样运行它会回来：

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

MariaDB [ticketing]>

当我们在 galera 集群上运行它时，它的减速是疯狂的：

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

跟踪此过程表明该过程非常繁忙，但 cpu 使用率和内存使用率很低（3% ， 2% ）我希望性能与生产机器一致（当 galera 登台节点正在做时，它也很忙什么都没有）

一些服务器配置变量，每个 galera 节点有 12Gigs 的 ram，而 prod 机器只有 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

平台是 Ubuntu 14:04 LTS for galera，生产是 12.04

加莱拉 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

我了解查询优化，因此有很多关于让开发人员编写更好的查询的说法，但作为系统管理员，我必须能够解释为什么次优查询在非 galera 数据库上执行得更好。5 分钟与 0.17 毫秒相比真的很长。

感谢您对此的所有意见。

更新

Tx 已经发表了评论，我也问了这些问题，这里有一些答案：

数据库是从旧服务器导入的，导入正常，没有警告。AFAIK 的解释完全相同，这可能是我转向堆栈的主要原因。内容略有不同，但分期和制作之间的差异是由于时间造成的，而且这在导入之后就已经发生了。

所有 Galera 节点也表现出相同的行为。至少它是一致的。

这是生产说明

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)

这是关于分期（galera）的解释。在我看来是一样的。

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                                  |
+------+-------------+----------+--------+-------------------------------------------------------------------+-----------------+---------+-------------------------------------+-------+----------------------------------------------+

加莱拉：

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

和

    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/'

最重要的是，这也是生产版本。评论说明很奇怪恕我直言。在表模式中放入多么奇怪的东西

玛丽亚数据库 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

和

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/'

缓存 vars 5.5 ，生产

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                  |
+-------------------------------+----------------------+

加莱拉

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                  |
+--------------------------------+----------------------+

我总是从同一个节点获取这些信息。

这也可能是一个有趣的事实，查询在延迟期间坚持 cpy 到临时表

| 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 个回答

  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

相关问题