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

Percona XtraDB 5.6 ( 5.6.32-25.17-1.trusty ) major performance difference

electricalelectrical EntrantCurrent User Role Beginner
Hi all, at work we are now testing with percona XtraDB and are currently doing some performance tests with sysbench.
We tried to compare insert, update and read performances from mysql 5.6, percona 5.6 and xtradb 3 node cluster.
We found out that our percona cluster would give about 10% performance compared to a single server of mysql and percona 5.6
Initially I thought that this is caused by the sync replication to the other nodes and some other factors.

So i decided to bootstrap a single node as a cluster without any other nodes and do the same performance tests.
What i didn't expect is that i got the same performance out of it as with the 3 node cluster.
Since with a single node it doesn't have to do any replication and wait on the other hosts I expected the performance to be much higher.

Obviously i expected to hand in some performance, but not 90% even on a single node compared to a normal mysql/percona server.
So this feels like a bug somewhere or I'm completely missing something.

Anyone have the same experience or tips/advice?

Comments

  • wagnerbianchiwagnerbianchi Remote DBA Current User Role Patron
    Hello Electrical,

    First of all, I like the way you showed worried about performance and this is very hot topic indeed as you cannot move to a solution that will present some regression considering the workload you systems presents currently. What I would like to highlight is that, even using just one node, as you said, the certification process part of the virtual synchronicity will be present on each transaction commit, making it to have the same throughput, if it's what you're considering here. It's hard to compare a standalone instance with a instance + Galera API as there are much more work involved to get cluster members with the same state. In this case, my advice to you is to check the below:
    • what's the problem you're trying to solve moving to a cluster, you're thinking about scaling reads or writes?
    • you cannot compare one bootstrap member with a standalone instance because it's nota real use case, can you test you issue you're trying to scale with a three members cluster?
    • what's the version you're testing, 5.6.X or 5.7.X?
    • what's the Ubuntu's OS kernel?
    Let's keep this conversation, look forward to hear from you.
  • atluriatluri Entrant Current User Role Beginner
    Hi Wagner,

    Had similar issue, we are looking to use percona cluster to improve high availability.

    we had run sysbench on c3.8x large machine (AWS) on standalone as well as cluster. In cluster we had started only one server with bootstrap.we had completely shutdown second node.

    Sysbech standalone server - 3000/sec transactions
    Sysbench single boostrap cluster - 438/sec transactions.

    We are testing cluster 5.6 on ubuntu 16.04

    our wsrep settings
    wsrep_provider = /usr/lib/libgalera_smm.so
    #wsrep_provider = none
    wsrep_provider_options = "gcache.size=2G; gcache.page_size=1G;gcs.fc_limit = 500; gcs.fc_factor = 0.99;"
    wsrep_cluster_address = gcomm://172.16.0.239
    wsrep_node_address = 172.16.0.239
    binlog_format = ROW
    default_storage_engine = InnoDB
    innodb_autoinc_lock_mode = 2
    innodb_locks_unsafe_for_binlog=1
    wsrep_sst_method = xtrabackup-v2
    wsrep_cluster_name = test
    wsrep_sst_auth = "evsstuser:s3cretPass"
    wsrep_slave_threads = 16
    wsrep_replicate_myisam
    transaction-isolation = READ-COMMITTED
    wsrep_sync_wait=0
    wsrep_retry_autocommit=4


    What might be the reason for such degrade in performance?
  • electricalelectrical Entrant Current User Role Beginner
    Hi wagnerbianchi, thank you for your response.
    I agree that the comparison ( single node vs 3 node cluster ) is slightly skewed but we needed a comparison setup to ensure we don't regress to much compared to our current setup.

    The reasons for moving to XtraDB:
    1. Multi master; easier to switch to an other node for writes if we have to do maintenance.
    2. Easier to rebuild a node; using the built in SST its much easier and faster to rebuild / add a node.
    3. Expand read capacity; Our app is mainly read heavy and need a nice way to expand the capacity.

    With regards to item 3; our app is fairly time sensitive and at times does weird things so the sync replication is advantages for us in this case.

    In the current test setup we have 1 node acting as a write host where the other 2 act as read hosts.
    We are currently testing against XtraDB 5.6 and mysql 5.6
    Ubuntu 14.04 with kernel 4.4.0-31-generic

    Hope this clarifies our intentions a bit.

    Side note; we are also intending to implement ProxySQL to actually spread the load across the hosts.
  • electricalelectrical Entrant Current User Role Beginner
    So, today we've been doing some more testing. It seems that for some of our queries the query plan changed if we compare it between our Mysql5.6 setup and Percona XtraDB 5.6 which is causing the slowdown most likely.
    Will be doing some more performance testing next week.
  • electricalelectrical Entrant Current User Role Beginner
    wagnerbianchi Is the query planner change something that you would expect to happen between mysql 56 and percona xtradb 56 ?
    We are looking into ways to improve the queries but are lacking the knowledge to effectively do something. any tips?
  • krunalbauskarkrunalbauskar Advisor Inactive User Role Novice
    Can you help us highlight some of the queries where you see query-plan changes if you use

    a. PXC in cluster mode
    b. PXC in non-cluster mode (standalone) wsrep_provider=none
    c. PS (operates only in standalone mode)


    As such there is no technically known incident of query plan change due to use of cluster mode.
  • [NUT][NUT] Contributor Current User Role Beginner
    Hi guys,

    I'm actually seeing exactly the same performance degradation between MySQL 5.7 standalone and Percona XtraDB 5.7, in some cases even worse.

    I'm gathering information from a simple table (in layout) with 205 million rows of data:

    Doing so on a standalone MySQL server it takes roughly 40 seconds after a restart of MySQL and almost 9 seconds whenever the query is re-run after that (indicating the caches have been filled and are used).

    Doing the same (untouched) query on a fresh Percona XtraDB 5.7 cluster it runs in to the 180 second query timeout the first time I try to run it, then after that initial failure I still have to wait around 130 seconds to get a result the second time I try to run it... after some tinkering with the query I got it to speed up to 52 seconds but now the fetch (sending data) stage is taking 550 seconds to complete (!) ...

    The percona mysql config:
    [client]
    port        = 3306
    socket        = /var/run/mysqld/mysqld.sock
    
    [mysqld_safe]
    socket        = /var/run/mysqld/mysqld.sock
    nice        = 0
    
    [mysqld]
    #
    # * Basic Settings
    #
    user        = mysql
    pid-file    = /var/run/mysqld/mysqld.pid
    socket        = /var/run/mysqld/mysqld.sock
    port        = 3306
    basedir        = /usr
    datadir        = /storage/mysql
    tmpdir        = /tmp
    lc-messages-dir    = /usr/share/mysql
    skip-external-locking
    sql_mode        = ''
    
    #
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    bind-address        = 127.0.0.1
    bind-address        = 192.168.***********
    #
    # * Fine Tuning
    # * Query Cache Configuration
    #
    query_cache_limit = 512M
    query_cache_size = 2147483648
    query_cache_type = 1
    query_cache_min_res_unit = 40960
    query_prealloc_size= 81920
    
    optimizer_search_depth=4
    
    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
    
    long_query_time = 8
    slow_query_log_file = /storage/logs/mysql/mysql-slowqueries
    slow-query-log = 1
    
    skip-name-resolve
    
    myisam_recover_options    = BACKUP
    general_log_file        = /storage/logs/mysql/mysql.log
    general_log             = 1
    log_error = /storage/logs/mysql/error.log
    
    server-id        = 1
    log_bin            = /storage/logs/mysql/mysql-bin.log
    expire_logs_days    = 5
    max_binlog_size         = 512M
    
    # Path to Galera library
    wsrep_provider=/usr/lib/libgalera_smm.so
    
    # Cluster connection URL contains the IPs of node#1, node#2 and node#3
    wsrep_cluster_address=gcomm://192.168.*******,192.168.******
    
    # In order for Galera to work correctly binlog format should be ROW
    binlog_format=ROW
    
    # MyISAM storage engine has only experimental support
    default_storage_engine=myisam
    
    # This InnoDB autoincrement locking mode is a requirement for Galera
    innodb_autoinc_lock_mode=2
    innodb_locks_unsafe_for_binlog=1
    
    # Node #1 address
    wsrep_node_address=***************
    
    # SST method
    wsrep_sst_method=xtrabackup-v2
    
    # Cluster name
    wsrep_cluster_name=my_
    
    # Authentication for SST method
    wsrep_sst_auth="sstuser:***************"
    
    wsrep_slave_threads = 16
    #wsrep_replicate_myisam
    transaction-isolation = READ-COMMITTED
    wsrep_sync_wait=0
    wsrep_retry_autocommit=4
    
    innodb_buffer_pool_size=17179869184
    innodb_log_file_size=512M
    innodb_flush_log_at_trx_commit=0
    innodb_read_io_threads=16
    innodb_sort_buffer_size=10485760
    innodb_flush_method=O_DIRECT
    innodb_buffer_pool_instances=16
    innodb_io_capacity=5000
    
    [mysqldump]
    quick
    quote-names
    max_allowed_packet    = 128M
    
    [mysql]
    #no-auto-rehash    # faster start of mysql but no tab completition
    #net_write_timeout=600
    #net_read_timeout=600
    max_allowed_packet    = 128M
    
    [isamchk]
    # Obsolete key_buffer option renamed to key_buffer_size by maintainer script
    #key_buffer_size        = 16M
    
    #
    # * IMPORTANT: Additional settings that can override those from this file!
    #   The files must end with '.cnf', otherwise they'll be ignored.
    #
    !includedir /etc/mysql/conf.d/
    

    The create statements (the original server has less indexes, I've been trying my best to improve the performance!)
    CREATE TABLE `dcenergy_measurement` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `dcenergy_meter_id` int(11) NOT NULL,
      `collected` datetime NOT NULL,
      `value` decimal(13,3) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `meter_id` (`dcenergy_meter_id`,`id`),
      KEY `datetimemeter` (`collected`,`dcenergy_meter_id`),
      KEY `datetimeid` (`collected`,`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=205638189 DEFAULT CHARSET=latin1;
    
    CREATE TABLE `dcenergy_meter` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `dcenergy_device_id` int(11) NOT NULL,
      `measurement_group_id` int(11) DEFAULT NULL,
      `measurement_group_name` varchar(45) DEFAULT NULL,
      `measurement_group_automated` tinyint(1) DEFAULT '1',
      `group_switch` decimal(3,0) DEFAULT NULL,
      `measurement_name` varchar(45) NOT NULL,
      `measurement_unit` varchar(45) NOT NULL,
      `latest_dcenergy_measurement_id` int(11) NOT NULL,
      `latest_dcenergy_measurement` datetime DEFAULT NULL,
      `company_details_id` int(11) NOT NULL,
      `dcpowerbar_id` int(11) DEFAULT NULL,
      `dccabinet_id` int(11) DEFAULT NULL,
      `socketlocation_dccabinet_id` int(11) DEFAULT NULL,
      `feed` varchar(1) DEFAULT NULL,
      `maxva` varchar(45) NOT NULL,
      `vacap` varchar(45) NOT NULL,
      `socket_type_id` int(11) DEFAULT NULL,
      `cable_type_id` int(11) DEFAULT NULL,
      `phase_id` tinyint(1) DEFAULT NULL,
      `trigger_load` decimal(5,2) NOT NULL DEFAULT '5.00',
      `internalnotes` text,
      `active` tinyint(1) NOT NULL DEFAULT '1',
      PRIMARY KEY (`id`),
      KEY `meter_id` (`dcenergy_device_id`),
      KEY `company_id` (`company_details_id`),
      KEY `dccabinet_id` (`dccabinet_id`),
      KEY `socketlocation_id` (`socketlocation_dccabinet_id`),
      KEY `groupswitch` (`group_switch`),
      KEY `measurement` (`measurement_name`,`measurement_unit`,`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3007 DEFAULT CHARSET=latin1;
    
    CREATE TABLE `dcenergy_device` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `device_name` varchar(45) NOT NULL,
      `device_local_id` int(11) NOT NULL,
      `device_type_id` int(11) NOT NULL,
      `device_type_name` varchar(45) NOT NULL,
      `lastseen` timestamp NULL DEFAULT NULL,
      `active` tinyint(1) NOT NULL DEFAULT '1',
      `role` varchar(1) NOT NULL DEFAULT 's',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=80 DEFAULT CHARSET=latin1;
    

    The hardware specs:
    2x Intel(R) Xeon(R) CPU E5-2620 v4 @ 2.10GHz
    32 GB RAM
    RAID Controller: Logic / Symbios Logic MegaRAID SAS-3 3108
    RAID 10 on 4 Intel 520 (512Gb) 6Gbit/s SATA SSD's

    The link between the 2 nodes (and a garbd as 3rd quorum member) is 10Gbit.

    (The queries come in a second post, as this one exceeded the 10.000 char max on this forum ;) )
  • [NUT][NUT] Contributor Current User Role Beginner
    The unmodified 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'
            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'))
            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 modified query:
    SELECT
        `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`,
        `measurement`.`collected`
    FROM
        `dcenergy_device` AS `device`
            LEFT JOIN
        `dcenergy_meter` AS `meter` ON meter.dcenergy_device_id = device.id
            LEFT JOIN
        `dcenergy_measurement` AS `measurement` ON measurement.dcenergy_meter_id = meter.id
    WHERE
        (device.role = 's')
        AND (meter.measurement_name = 'real_energy_kwh')
        AND (measurement.collected BETWEEN '2016-04-28 00:00:00' AND '2016-04-28 00:30:00')
        AND (meter.active = '1')
        AND (meter.group_switch > '0');
    
  • krunalbauskarkrunalbauskar Advisor Inactive User Role Novice
    Did you got chance to compare PS-5.7 standalone and PXC-5.7 standalone (single node).
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.