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 |
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
And
I believe the reason is that the information_scheme is the bottleneck here. Afaik, a known problem when having plenty of tables around.