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

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?

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:
[LIST]
[]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?
[/LIST] Let’s keep this conversation, look forward to hear from you.

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?

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.

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.

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?

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.

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 :wink: )

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

Did you got chance to compare PS-5.7 standalone and PXC-5.7 standalone (single node).