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 
)