Major performance difference between standalone and clustered MySQL

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hi people,

I’m trying to get a XtraDB Cluster 5.7 to perform similar to the standalone 5.5 version. I realise there is some performance degradation to be expected due to the replication/sync of the cluster nodes, but the difference between the 2 is so much (and even on the cluster between types of queries) that I can not imagine that this is the difference to be expected… Plus, it’s not a write action, but a READ action…

When I gather the same set of data using UNION the difference with a standalone server is negligible, but when I try to do so with an OR in the WHERE it slows down badly… from 6 seconds on the original standalone server and the (cluster) union version to 141 seconds and upwards… The source query I was trying to debug makes ‘MySQL gone away’ results every time on the cluster, while the original MySQL 5.5 is doing the same query on (relatively) inferior hardware within 9 seconds… (exactly the same dataset).

I have read so much on the blog about performance optimizations that I probably have a near perfect config for the cluster nodes as they are now, which is much better in specs then the original server.

The config:

query_cache_limit = 512M
query_cache_size =0
query_cache_type = 1
query_cache_min_res_unit = 40960
query_prealloc_size= 81920

max_connections = 5000
thread_cache_size = 16384
max_allowed_packet = 1024M
read_buffer_size = 2048M
max_connect_errors = 90
myisam_sort_buffer_size = 512M
bulk_insert_buffer_size = 512M
tmp_table_size = 2048M
max_heap_table_size = 1024M

innodb_file_per_table=1
innodb_buffer_pool_size=17179869184
innodb_flush_log_at_trx_commit=1
innodb_sort_buffer_size=10485760
innodb_flush_method=O_DIRECT
innodb_buffer_pool_instances=32
innodb_log_file_size=10G
innodb_doublewrite=1
innodb_change_buffering=none
innodb_adaptive_hash_index=OFF
innodb_flush_neighbors=0
innodb_read_io_threads=16
innodb_write_io_threads=16
innodb_lru_scan_depth=8192
innodb_io_capacity=15000
innodb_io_capacity_max=25000
loose-innodb-page-cleaners=4
table_open_cache_instances=64
table_open_cache=5000
loose-innodb-log_checksum-algorithm=crc32
loose-innodb-checksum-algorithm=strict_crc32
loose-performance_schema=ON
loose-performance-schema-instrument='wait/synch/%=ON'

The 141 second query:

SELECT
`pdu`.`id` AS `dcpdu_id`,
`pdu`.`name` AS `dcpdu_name`,
`cabinet`.`id` AS `dccabinet_id`,
CONCAT(row.name, '', cabinet.name) AS `dccabinet_name`,
`socketlocation_cabinet`.`id` AS `socketlocation_dccabinet_id`,
CONCAT(socketlocation_row.name,
'',
socketlocation_cabinet.name) AS `socketlocation_dccabinet_name`,
`location`.`locationtag` AS `dclocationtag`,
`suite`.`name` AS `dcsuite_name`,
`device`.`id` AS `dcenergy_device_id`,
`meter`.`id` AS `dcenergy_meter_id`,
`measurement`.`id` AS `dcenergy_measurement_id`,
`device`.`device_name`,
`device`.`device_local_id`,
`device`.`device_type_id`,
`device`.`device_type_name`,
`device`.`role`,
`meter`.`measurement_group_id`,
`meter`.`measurement_group_name`,
`meter`.`group_switch`,
`meter`.`measurement_name`,
`meter`.`measurement_unit`,
`meter`.`company_details_id`,
`meter`.`socket_type_id`,
`meter`.`cable_type_id`,
`meter`.`phase_id`,
`meter`.`trigger_load`,
`meter`.`internalnotes`,
`company`.`name` AS `company_name`,
`measurement`.`collected`,
(SUM(measurement.value) / COUNT(DISTINCT measurement.id)) AS `value`,
COUNT(DISTINCT measurement.id) AS `values`,
(SUM(msrmnt.value) / COUNT(DISTINCT msrmnt.id)) AS `actualload`
FROM
`dcenergy_meter` AS `meter`
LEFT JOIN
`dcenergy_measurement` AS `measurement` ON measurement.dcenergy_meter_id = meter.id
LEFT JOIN
`dcenergy_device` AS `device` ON device.id = meter.dcenergy_device_id
LEFT JOIN
`dcpdu_parts` AS `pduparts` ON pduparts.dcenergy_device_id = device.id
LEFT JOIN
`dcpdu` AS `pdu` ON pdu.id = pduparts.dcpdu_id
LEFT JOIN
`dccabinets` AS `cabinet` ON cabinet.id = meter.dccabinet_id
LEFT JOIN
`dcrow` AS `row` ON row.id = cabinet.dcrow_id
LEFT JOIN
`dccabinets` AS `socketlocation_cabinet` ON socketlocation_cabinet.id = meter.socketlocation_dccabinet_id
LEFT JOIN
`dcrow` AS `socketlocation_row` ON socketlocation_row.id = socketlocation_cabinet.dcrow_id
LEFT JOIN
`dcsuite` AS `suite` ON suite.id = cabinet.dcsuite_id
LEFT JOIN
`dclocation` AS `location` ON location.id = pdu.dclocation_id
LEFT JOIN
`company_details` AS `company` ON company.id = meter.company_details_id
LEFT JOIN
`dcenergy_meter` AS `mtr` ON `mtr`.group_switch = `meter`.`group_switch`
AND `mtr`.dcenergy_device_id = `device`.`id`
AND `mtr`.measurement_name = 'real_power_total'
LEFT JOIN
`dcenergy_measurement` AS `msrmnt` ON `msrmnt`.dcenergy_meter_id = `mtr`.id
AND `msrmnt`.collected = `measurement`.`collected`
WHERE
(device.role = 's')
AND (meter.measurement_name = 'real_energy_kwh')
AND (measurement.collected = '2016-01-28 00:00:00'
OR measurement.collected = '2016-02-28 00:00:00'
OR measurement.collected = '2016-03-28 00:00:00'
OR measurement.collected = '2016-04-28 00:00:00'
OR measurement.collected = '2016-05-28 00:00:00'
OR measurement.collected = '2016-06-28 00:00:00'
OR measurement.collected = '2016-07-28 00:00:00'
OR measurement.collected = '2016-08-28 00:00:00'
OR measurement.collected = '2016-09-28 00:00:00'
OR measurement.collected = '2016-10-28 00:00:00'
OR measurement.collected = '2016-11-28 00:00:00'
OR measurement.collected = '2016-12-28 00:00:00')
AND (meter.active = '1')
AND (meter.group_switch > '0')
GROUP BY `measurement`.`dcenergy_meter_id` , DATE_FORMAT(`measurement`.`collected`, '%m')
ORDER BY `pdu`.`id` ASC , `meter`.`group_switch` ASC , `measurement`.`collected` ASC;

The original query which ran mostly with less then 9 seconds on the standalone 5.5 server had this measurement.collected instead of the above list, the rest is identical to the one above:

(measurement.collected >= '2016-01-28 00:00:00'
AND measurement.collected <= '2016-01-28 00:30:00')
OR (measurement.collected >= '2016-02-28 00:00:00'
AND measurement.collected <= '2016-02-28 00:30:00')
OR (measurement.collected >= '2016-03-28 00:00:00'
AND measurement.collected <= '2016-03-28 00:30:00')
OR (measurement.collected >= '2016-04-28 00:00:00'
AND measurement.collected <= '2016-04-28 00:30:00')
OR (measurement.collected >= '2016-05-28 00:00:00'
AND measurement.collected <= '2016-05-28 00:30:00')
OR (measurement.collected >= '2016-06-28 00:00:00'
AND measurement.collected <= '2016-06-28 00:30:00')
OR (measurement.collected >= '2016-07-28 00:00:00'
AND measurement.collected <= '2016-07-28 00:30:00')
OR (measurement.collected >= '2016-08-28 00:00:00'
AND measurement.collected <= '2016-08-28 00:30:00')
OR (measurement.collected >= '2016-09-28 00:00:00'
AND measurement.collected <= '2016-09-28 00:30:00')
OR (measurement.collected >= '2016-10-28 00:00:00'
AND measurement.collected <= '2016-10-28 00:30:00')
OR (measurement.collected >= '2016-11-28 00:00:00'
AND measurement.collected <= '2016-11-28 00:30:00')
OR (measurement.collected >= '2016-12-28 00:00:00'
AND measurement.collected <= '2016-12-28 00:30:00')

The 1 second UNION version changes:

(SELECT
(SNIP)
AND (measurement.collected = '2016-01-28 00:00:00')
(SNIP)
GROUP BY `measurement`.`dcenergy_meter_id` , DATE_FORMAT(`measurement`.`collected`, '%m')
UNION
(Repeats for all the months in the year on the same day).
ORDER BY `dcpdu_id` ASC , `group_switch` ASC , `collected` ASC;

The cluster server storage benchmark (simple version), which holds the database files:

$ dd if=/dev/zero of=/storage/test bs=1k count=102400000
102400000+0 records in
102400000+0 records out
104857600000 bytes (105 GB, 98 GiB) copied, 297,296 s, 353 MB/s
$ dd of=/dev/null if=/storage/test bs=1k count=102400000
102400000+0 records in
102400000+0 records out
104857600000 bytes (105 GB, 98 GiB) copied, 66,4824 s, 1,6 GB/s

Ask me for anything else if needed. I want this issue to be resolved, it keeps a 12.000 euro investment unused at the moment, which I want to rectify as soon as possible…

2

I have confirmed this behaviour to be not present on Percona XtraDB Cluster 5.6, after the downgrade to it from 5.7 everything is actually performing better compared to the standalone MySQL server!

Exactly the same queries now register (respectively, same order as the OP Duration/Fetch) 5,520 sec / 0,011 sec, 5,520 sec / 0,011 sec and 1,695 sec / 0,019 sec.

Something is seriously out of wack in the MySQL 5.7 source or the Percona patches on it…

So… problem solved for me, I’d like to use 5.7, but it has some serious issues which forces me to use 5.6 for now…

3

Do you a short reproducible use-case using independent data ?
It would just help debug this problem more quickly.

We will continue to run some other suite but not sure if that will help us pin-point your exact issue.

4

If you like I can give you a copy of these (anonymized) tables? I’d be more then happy to help you guys troubleshoot this issue. The SQL file unpacked is about 9Gb.

I’m currently trying to optimize another query, this one is similar, uses many of the same tables and drags in most of the same content. At the moment this query runs for 89,487 sec / 0,0063 sec (Duration/Fetch), completely untouched so far, the original MySQL 5.5 server still running this (live) database does the same query in 2,361 sec / 0,032 sec. I’ll certainly keep you updated on progress.

The mentioned query:

SELECT
`pdu`.`id` AS `dcpdu_id`,
`pdu`.`name` AS `dcpdu_name`,
`cabinet`.`id` AS `dccabinet_id`,
CONCAT(row.name, '', cabinet.name) AS `dccabinet_name`,
`socketlocation_cabinet`.`id` AS `socketlocation_dccabinet_id`,
CONCAT(socketlocation_row.name,
'',
socketlocation_cabinet.name) AS `socketlocation_dccabinet_name`,
`location`.`locationtag` AS `dclocationtag`,
`suite`.`name` AS `dcsuite_name`,
`device`.`id` AS `dcenergy_device_id`,
`meter`.`id` AS `dcenergy_meter_id`,
`measurement`.`id` AS `dcenergy_measurement_id`,
`device`.`device_name`,
`device`.`device_local_id`,
`device`.`device_type_id`,
`device`.`device_type_name`,
`device`.`role`,
`meter`.`measurement_group_id`,
`meter`.`measurement_group_name`,
`meter`.`group_switch`,
`meter`.`measurement_name`,
`meter`.`measurement_unit`,
`meter`.`company_details_id`,
`meter`.`socket_type_id`,
`meter`.`cable_type_id`,
`meter`.`phase_id`,
`meter`.`trigger_load`,
`meter`.`internalnotes`,
`company`.`name` AS `company_name`,
`measurement`.`collected`,
(SUM(value) / COUNT(DISTINCT measurement.id)) AS `value`,
COUNT(DISTINCT measurement.id) AS `values`
FROM
`dcenergy_measurement` AS `measurement`
LEFT JOIN
`dcenergy_meter` AS `meter` ON meter.id = measurement.dcenergy_meter_id
LEFT JOIN
`dcenergy_device` AS `device` ON device.id = meter.dcenergy_device_id
LEFT JOIN
`dcpdu_parts` AS `pduparts` ON pduparts.dcenergy_device_id = device.id
LEFT JOIN
`dcpdu` AS `pdu` ON pdu.id = pduparts.dcpdu_id
LEFT JOIN
`dccabinets` AS `cabinet` ON cabinet.id = meter.dccabinet_id
LEFT JOIN
`dcrow` AS `row` ON row.id = cabinet.dcrow_id
LEFT JOIN
`dccabinets` AS `socketlocation_cabinet` ON socketlocation_cabinet.id = meter.socketlocation_dccabinet_id
LEFT JOIN
`dcrow` AS `socketlocation_row` ON socketlocation_row.id = socketlocation_cabinet.dcrow_id
LEFT JOIN
`dcsuite` AS `suite` ON suite.id = cabinet.dcsuite_id
LEFT JOIN
`dclocation` AS `location` ON location.id = pdu.dclocation_id
LEFT JOIN
`company_details` AS `company` ON company.id = meter.company_details_id
WHERE
(device.role = 's')
AND (meter.measurement_name = 'real_energy_kwh')
AND (meter.dccabinet_id IS NOT NULL)
AND ((measurement.collected >= '2016-01-01 00:00:00'
AND measurement.collected <= '2016-01-01 00:30:00')
OR (measurement.collected >= '2016-02-01 00:00:00'
AND measurement.collected <= '2016-02-01 00:30:00')
OR (measurement.collected >= '2016-03-01 00:00:00'
AND measurement.collected <= '2016-03-01 00:30:00')
OR (measurement.collected >= '2016-04-01 00:00:00'
AND measurement.collected <= '2016-04-01 00:30:00')
OR (measurement.collected >= '2016-05-01 00:00:00'
AND measurement.collected <= '2016-05-01 00:30:00')
OR (measurement.collected >= '2016-06-01 00:00:00'
AND measurement.collected <= '2016-06-01 00:30:00')
OR (measurement.collected >= '2016-07-01 00:00:00'
AND measurement.collected <= '2016-07-01 00:30:00')
OR (measurement.collected >= '2016-08-01 00:00:00'
AND measurement.collected <= '2016-08-01 00:30:00')
OR (measurement.collected >= '2016-09-01 00:00:00'
AND measurement.collected <= '2016-09-01 00:30:00')
OR (measurement.collected >= '2016-10-01 00:00:00'
AND measurement.collected <= '2016-10-01 00:30:00')
OR (measurement.collected >= '2016-11-01 00:00:00'
AND measurement.collected <= '2016-11-01 00:30:00')
OR (measurement.collected >= '2016-12-01 00:00:00'
AND measurement.collected <= '2016-12-01 00:30:00'))
AND (meter.active = '1')
AND (meter.group_switch > '0')
GROUP BY `measurement`.`dcenergy_meter_id` , DATE_FORMAT(`measurement`.`collected`, '%m')
ORDER BY `suite`.`id` ASC , CONCAT(row.name, cabinet.name) ASC;

Adding these lines to the query speeds it up to 3,562 sec / 0,0041 sec seconds:

LEFT JOIN
`dcenergy_meter` AS `mtr` ON `mtr`.group_switch = `meter`.`group_switch`
AND `mtr`.dcenergy_device_id = `device`.`id`
AND `mtr`.measurement_name = 'real_power_total'
LEFT JOIN
`dcenergy_measurement` AS `msrmnt` ON `msrmnt`.dcenergy_meter_id = `mtr`.id
AND `msrmnt`.collected = `measurement`.`collected`

The EXPLAIN between the 2 versions, first up, the original:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE device ALL PRIMARY NULL NULL NULL 79 "Using where; Using temporary; Using filesort"
1 SIMPLE pduparts ref dcenergydevice dcenergydevice 4 staging_cas.device.id 1 NULL
1 SIMPLE pdu eq_ref PRIMARY PRIMARY 4 staging_cas.pduparts.dcpdu_id 1 NULL
1 SIMPLE meter ref PRIMARY,meter_id,dccabinet_id,groupswitch,measurement meter_id 4 staging_cas.device.id 18 "Using index condition; Using where"
1 SIMPLE cabinet eq_ref PRIMARY PRIMARY 4 staging_cas.meter.dccabinet_id 1 NULL
1 SIMPLE row eq_ref PRIMARY PRIMARY 4 staging_cas.cabinet.dcrow_id 1 NULL
1 SIMPLE socketlocation_cabinet eq_ref PRIMARY PRIMARY 4 staging_cas.meter.socketlocation_dccabinet_id 1 NULL
1 SIMPLE socketlocation_row eq_ref PRIMARY PRIMARY 4 staging_cas.socketlocation_cabinet.dcrow_id 1 NULL
1 SIMPLE suite eq_ref PRIMARY PRIMARY 4 staging_cas.cabinet.dcsuite_id 1 NULL
1 SIMPLE location eq_ref PRIMARY PRIMARY 4 staging_cas.pdu.dclocation_id 1 NULL
1 SIMPLE company eq_ref PRIMARY PRIMARY 4 staging_cas.meter.company_details_id 1 NULL
1 SIMPLE measurement ref meter_id,datetimemeter,datetimeid meter_id 4 staging_cas.meter.id 584 "Using where"

The modified version:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE measurement range meter_id,datetimemeter,datetimeid datetimeid 5 NULL 248362 "Using index condition; Using temporary; Using filesort"
1 SIMPLE meter eq_ref PRIMARY,meter_id,dccabinet_id,groupswitch,measurement PRIMARY 4 staging_cas.measurement.dcenergy_meter_id 1 "Using where"
1 SIMPLE device eq_ref PRIMARY PRIMARY 4 staging_cas.meter.dcenergy_device_id 1 "Using where"
1 SIMPLE pduparts ref dcenergydevice dcenergydevice 4 staging_cas.meter.dcenergy_device_id 1 NULL
1 SIMPLE pdu eq_ref PRIMARY PRIMARY 4 staging_cas.pduparts.dcpdu_id 1 NULL
1 SIMPLE cabinet eq_ref PRIMARY PRIMARY 4 staging_cas.meter.dccabinet_id 1 NULL
1 SIMPLE row eq_ref PRIMARY PRIMARY 4 staging_cas.cabinet.dcrow_id 1 NULL
1 SIMPLE socketlocation_cabinet eq_ref PRIMARY PRIMARY 4 staging_cas.meter.socketlocation_dccabinet_id 1 NULL
1 SIMPLE socketlocation_row eq_ref PRIMARY PRIMARY 4 staging_cas.socketlocation_cabinet.dcrow_id 1 NULL
1 SIMPLE suite eq_ref PRIMARY PRIMARY 4 staging_cas.cabinet.dcsuite_id 1 NULL
1 SIMPLE location ALL PRIMARY NULL NULL NULL 2 "Using where; Using join buffer (Block Nested Loop)"
1 SIMPLE company eq_ref PRIMARY PRIMARY 4 staging_cas.meter.company_details_id 1 NULL
1 SIMPLE mtr ref meter_id,groupswitch,measurement meter_id 4 staging_cas.meter.dcenergy_device_id 18 "Using where"
1 SIMPLE msrmnt ref meter_id,datetimemeter,datetimeid datetimemeter 9 staging_cas.measurement.collected,staging_cas.mtr.id 1 "Using index"

This boggles the mind, how can a query run so slow (on the same server), while gathering less data?

5

The profiling on the 2, in the same order:

Query completed in: 85,319 sec / 0,0063 sec

Status Duration
"Creating sort index" 0.001712
"Waiting for query cache lock" 0.000002
"Waiting on query cache mutex" 0.000001
"Creating sort index" 0.000335
"Waiting for query cache lock" 0.000001
"Waiting on query cache mutex" 0.000001
"Creating sort index" 0.000295
"Waiting for query cache lock" 0.000001
"Waiting on query cache mutex" 0.000001
"Creating sort index" 0.000289
"Waiting for query cache lock" 0.000001
"Waiting on query cache mutex" 0.000001
"Creating sort index" 0.000287
"Waiting for query cache lock" 0.000001
"Waiting on query cache mutex" 0.000001
"Creating sort index" 0.000279
"Waiting for query cache lock" 0.000001
"Waiting on query cache mutex" 0.000001
"Creating sort index" 0.000285
"Waiting for query cache lock" 0.000001
"Waiting on query cache mutex" 0.000001
"Creating sort index" 0.000289
"Waiting for query cache lock" 0.000001
"Waiting on query cache mutex" 0.000001
"Creating sort index" 0.000284
"Waiting for query cache lock" 0.000001
"Waiting on query cache mutex" 0.000001
"Creating sort index" 0.000280
"Waiting for query cache lock" 0.000001
"Waiting on query cache mutex" 0.000001
"Creating sort index" 0.000269
"Waiting for query cache lock" 0.000001
"Waiting on query cache mutex" 0.000001
"Creating sort index" 0.000262
"Waiting for query cache lock" 0.000001
"Waiting on query cache mutex" 0.000000
"Creating sort index" 0.000261
"Waiting for query cache lock" 0.000001
"Waiting on query cache mutex" 0.000001
"Creating sort index" 0.000263
"Waiting for query cache lock" 0.000001
"Waiting on query cache mutex" 0.000001
"Creating sort index" 0.000265
"Waiting for query cache lock" 0.000001
"Waiting on query cache mutex" 0.000001
"Creating sort index" 0.000262
"Waiting for query cache lock" 0.000001
"Waiting on query cache mutex" 0.000001
"Creating sort index" 0.000255
"Waiting for query cache lock" 0.000001
"Waiting on query cache mutex" 0.000001
"Creating sort index" 0.000261
"Waiting for query cache lock" 0.000001
"Waiting on query cache mutex" 0.000001
"Creating sort index" 0.000266
"Waiting for query cache lock" 0.000001
"Waiting on query cache mutex" 0.000001
"Creating sort index" 0.000265
"Waiting for query cache lock" 0.000001
"Waiting on query cache mutex" 0.000001
"Creating sort index" 0.000273
"Waiting for query cache lock" 0.000001
"Waiting on query cache mutex" 0.000001
"Creating sort index" 0.000287
"Waiting for query cache lock" 0.000001
"Waiting on query cache mutex" 0.000001
"Creating sort index" 0.000281
"Waiting for query cache lock" 0.000001
"Waiting on query cache mutex" 0.000000
"Creating sort index" 0.000048
end 0.000003
"removing tmp table" 0.000221
end 0.000002
"removing tmp table" 0.000111
end 0.000011
"removing tmp table" 0.000005
end 0.000007
"removing tmp table" 0.000002
end 0.000002
"query end" 0.000008
ha_commit_one_phase(-1) 0.000011
innobase_commit_low():trx_comm 0.000004
ha_commit_one_phase(-1) 0.000001
"query end" 0.000001
"closing tables" 0.000037
"freeing items" 0.000028
"removing tmp table" 0.000003
"freeing items" 0.000002
"removing tmp table" 0.000002
"freeing items" 0.000007
"Waiting for query cache lock" 0.000001
"Waiting on query cache mutex" 0.000001
"freeing items" 0.000008
"Waiting for query cache lock" 0.000001
"Waiting on query cache mutex" 0.000001
"freeing items" 0.000001
"storing result in query cache" 0.000004
"logging slow query" 0.000002
"logging slow query" 0.000136
"cleaning up" 0.000018

Query completed in 0,00086 sec / 0,0046 sec

Status Duration
starting 0.000015
"Waiting for query cache lock" 0.000003
"Waiting on query cache mutex" 0.000003
init 0.000002
"checking query cache for query" 0.000067
"checking privileges on cached " 0.000003
"checking permissions" 0.000005
"checking permissions" 0.000008
"checking permissions" 0.000004
"checking permissions" 0.000002
"checking permissions" 0.000003
"checking permissions" 0.000003
"checking permissions" 0.000003
"checking permissions" 0.000004
"checking permissions" 0.000003
"checking permissions" 0.000003
"checking permissions" 0.000002
"checking permissions" 0.000003
"checking permissions" 0.000002
"checking permissions" 0.000004
"sending cached result to clien" 0.000376
ha_commit_one_phase(-1) 0.000003
"sending cached result to clien" 0.000041
"cleaning up" 0.000005

While the modified query looks a lot more work for MySQL to process, it’s able to optimize the retrieval of the data quite significantly and it unable to do so on the unmodified query.

6

Okay, I think I found something for the Devs to take a long hard look at:

SET GLOBAL default_tmp_storage_engine='MEMORY';

(which was InnoDB by default in PXC 5.6)

Fixes any and ALL slow queries I throw at it…

I would not be surprised if this (at least partially) fixes it on 5.7 as well…

7

Also for internal temp-table MySQL has switched from Memory To InnoDB

[url]MySQL :: MySQL 5.7 Reference Manual :: 5.1.7 Server System Variables

If you get chance to try you can check this aspect too.

8

Now that I have managed to get it to perform reasonably, I’m actually a bit weary to try 5.7 again :frowning:

This is a bit of a ‘between a rock and a hard place’ situation, as I really do want to use 5.7, but there is no guarantee that it will work trouble free… (heh, having said that, is there ever such a guarantee? :wink: )

I’ll sit on the idea for a bit for now. This project has been waiting to be completed for so long (various issues, not all mysql related) I have to ponder on the extra work.

Do you still want that database copy?

9

I’m testing the same queries on a standalone (Ubuntu 16.04) MySQL 5.7, single ssd system, between the 2 settings on the temporary tables, the difference is almost unnoticeable…

I was hoping to see a difference to help me decide… now it seems I might need to install PS 5.7 and PXC 5.7 (as a single node) to see if it makes any difference…