我们正在评估一个 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 |