Not the answer you need?
Register and ask your own question!

Major performance difference between standalone and clustered MySQL

[NUT][NUT] ContributorCurrent User Role Beginner
Hi people,

<snip>

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

Comments

  • [NUT][NUT] Contributor Current User Role Beginner
    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...
  • krunalbauskarkrunalbauskar Advisor Inactive User Role Novice
    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.
  • [NUT][NUT] Contributor Current User Role Beginner
    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.


    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?
  • [NUT][NUT] Contributor Current User Role Beginner
    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.
  • [NUT][NUT] Contributor Current User Role Beginner
    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...
  • krunalbauskarkrunalbauskar Advisor Inactive User Role Novice
    Also for internal temp-table MySQL has switched from Memory To InnoDB

    http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_internal_tmp_disk_storage_engine

    If you get chance to try you can check this aspect too.
  • [NUT][NUT] Contributor Current User Role Beginner
    Also for internal temp-table MySQL has switched from Memory To InnoDB

    http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_internal_tmp_disk_storage_engine

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

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

    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? ;) )

    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?
  • [NUT][NUT] Contributor Current User Role Beginner
    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...
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.