We are evaluating a Galera setup and so far we have not noticed to many drawbacks except a few queries that have terrible READ performance I cannot put my finger on it.
The query by itself isn't really optimized but it comes back under 0.20 seconds on the production box. And takes several minutes on a beefy galera 3-node setup. (on much beefier hardware in fact).
The versions are for 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)
and for the 'old' prod machine
mysqld Ver 5.3.12-MariaDB-mariadb122~maverick for
debian-linux-gnu on x86_64 ((MariaDB - http://mariadb.com/))
The query:
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]>
Running it like this comes back:
+---------+-----------+--------+------------+------------------+-----------+---------------+---------------------+------------+
850 rows in set (0.17 sec)
MariaDB [ticketing]>
When we run this on the galera cluster it's slowdown is insane:
+----------+--------------+---------+
1970 rows in set (5 min 16.64 sec)
Straceing this process shows that the process is quite busy but cpu use and memory use is low (3% , 2% ) I would expect the performance to be inline with the production machine (which is also quite busy while the galera staging nodes are doing nothing at all)
Some server config vars , each galera node has 12Gigs of ram while the prod machine has only 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
Platform is Ubuntu 14:04 LTS for galera, production is 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
I understand query optimizations, so there is much the say about getting the developers to write better queries but as a systems administator I have to be able to explain why a suboptimal query performs so much better on the non-galera DB. 5 Minutes is a really long time vs 0.17ms.
Thanks for all input on this.
UPDATE
Tx for the comments already, I asked those question too, here are some answers:
The database was imported from the old server, imported fine without warnings. AFAIK the explain are exactly the same, probably the main reason I turn to stack. The content differs a bit but the divergence between staging and production is due to time and this already happened since the import anyway.
All Galera nodes exhibit the same behavior too. At-least it's consistent.
This is production explain
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)
This is the explain on staging (galera). It looks the same to me.
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
and
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/'
For the heck of it, here's the production version too. The COMMENT note is quite strange imho. What a weird thing to put in the table schema
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
and
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 , production
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 |
+--------------------------------+----------------------+
I always take the same node to get this information from.
This might also be an interesting fact, the query sticks in cpy to temp table during the delay
| 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 |