Emergency

Bypassing SST in Percona XtraDB Cluster with incremental backups

Latest MySQL Performance Blog posts - July 16, 2015 - 2:00am
Beware the SST

In Percona XtraDB Cluster (PXC) I often run across users who are fearful of SSTs on their clusters. I’ve always maintained that if you can’t cope with a SST, PXC may not be right for you, but that doesn’t change the fact that SSTs with multiple Terabytes of data can be quite costly.

SST, by current definition, is a full backup of a Donor to Joiner.  The most popular method is Percona XtraBackup, so we’re talking about a donor node that must:

  1. Run a full XtraBackup that reads its entire datadir
  2. Keep up with Galera replication to it as much as possible (though laggy donors don’t send flow control)
  3. Possibly still be serving application traffic if you don’t remove Donors from rotation.

So, I’ve been interested in alternative ways to work around state transfers and I want to present one way I’ve found that may be useful to someone out there.

Percona XtraBackup and Incrementals

It is possible to use Percona XtraBackup Full and Incremental backups to build a datadir that might possibly SST.  First we’ll focus on the mechanics of the backups, preparing them and getting the Galera GTID and then later discuss when it may be viable for IST.

Suppose I have fairly recent full Xtrabackup and and one or more incremental backups that I can apply on top of that to get VERY close to realtime on my cluster (more on that ‘VERY’ later).

# innobackupex --no-timestamp /backups/full ... sometime later ... # innobackupex --incremental /backups/inc1 --no-timestamp --incremental-basedir /backups/full ... sometime later ... # innobackupex --incremental /backups/inc2 --no-timestamp --incremental-basedir /backups/inc1

In my proof of concept test, I now have a full and two incrementals:

# du -shc /backups/* 909M full 665M inc1 812M inc2 2.4G total

To recover this data, I follow the normal Xtrabackup incremental apply process:

# cp -av /backups/full /backups/restore # innobackupex --apply-log --redo-only --use-memory=1G /backups/restore ... xtrabackup: Recovered WSREP position: 1663c027-2a29-11e5-85da-aa5ca45f600f:35694784 ... # innobackupex --apply-log --redo-only /backups/restore --incremental-dir /backups/inc1 --use-memory=1G # innobackupex --apply-log --redo-only /backups/restore --incremental-dir /backups/inc2 --use-memory=1G ... xtrabackup: Recovered WSREP position: 1663c027-2a29-11e5-85da-aa5ca45f600f:46469942 ... # innobackupex --apply-log /backups/restore --use-memory=1G

I can see that as I roll forward on my incrementals, I get a higher and higher GTID. Galera’s GTID is stored in the Innodb recovery information, so Xtrabackup extracts it after every batch it applies to the datadir we’re restoring.

We now have a datadir that is ready to go, we need to copy it into the datadir of our joiner node and setup a grastate.dat. Without a grastate, starting the node would force an SST no matter what.

# innobackupex --copy-back /backups/restore # ... copy a grastate.dat from another running node ... # cat /var/lib/mysql/grastate.dat # GALERA saved state version: 2.1 uuid: 1663c027-2a29-11e5-85da-aa5ca45f600f seqno: -1 cert_index: # chown -R mysql.mysql /var/lib/mysql/

If I start the node now, it should see the grastate.dat with the -1 seqo and run –wsrep_recover to extract the GTID from Innodb (I could have also just put that directly into my grastate.dat).

This will allow the node to startup from merged Xtrabackup incrementals with a known Galera GTID.

But will it IST?

That’s the question.  IST happens when the selected donor has all the transactions the joiner needs to get it fully caught up inside of the donor’s gcache.  There are several implications of this:

  • A gcache is mmap allocated and does not persist across restarts on the donor.  A restart essentially purges the mmap.
  • You can query the oldest GTID seqno on a donor by checking the status variable ‘wsrep_local_cached_downto’.  This variable is not available on 5.5, so you are forced to guess if you can IST or not.
  • most PXC 5.6 will auto-select a donor based on IST.  Prior to that (i.e., 5.5) donor selection was not based on IST candidacy at all, meaning you had to be much more careful and do donor selection manually.
  • There’s no direct mapping from the earliest GTID in a gcache to a specific time, so knowing at a glance if a given incremental will be enough to IST is difficult.
  • It’s also difficult to know how big to make your gcache (set in MB/GB/etc.)  with respect to your backups (which are scheduled by the day/hour/etc.)

All that being said, we’re still talking about backups here.  The above method will only work if and only if:

  • You do frequent incremental backups
  • You have a large gcache (hopefully more on this in a future blog post)
  • You can restore a backup faster than it takes for your gcache to overflow

The post Bypassing SST in Percona XtraDB Cluster with incremental backups appeared first on MySQL Performance Blog.

--log-bin-trust-function-creators=1 percona master-master cluster -> safe?

Lastest Forum Posts - July 16, 2015 - 1:17am
hi,

one of our web application (I-DOIT) wants to create a function:

Database error : Query error: '
CREATE FUNCTION alphas(str CHAR(100)) RETURNS CHAR(100) DETERMINISTIC READS SQL DATA
BEGIN
DECLARE i, len SMALLINT DEFAULT 1;
DECLARE ret CHAR(100) DEFAULT '';
DECLARE c CHAR(1);
SET len = CHAR_LENGTH( str );
REPEAT
BEGIN
SET c = MID( str, i, 1 );
IF c REGEXP '[[:alpha:]]' THEN
SET ret=CONCAT(ret,c);
END IF;
SET i = i + 1;
END;
UNTIL i > len END REPEAT;
RETURN ret;
END':
You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

is it save to enable the variable on all nodes? The application connects via HAproxy the cluster (roundrobin).

============================== =
[...]
[mysqld]

# GENERAL #
port = 3306
user = mysql
default-storage-engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid
# MUST DO UTF8 #
collation-server = utf8_unicode_ci
character-set-server = utf8
init_connect = 'SET NAMES utf8, collation_connection=utf8_unicode_ci'

# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP

# SAFETY #
max-allowed-packet = 25M
max-connect-errors = 1000000
skip-name-resolve
#sql-mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ AUTO_VALUE_ON_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,ON LY_FULL_GROUP_BY
sql-mode = NO_ENGINE_SUBSTITUTION
# dekativiert, wegen i-doit
innodb-strict-mode = 0

# DATA STORAGE #
datadir = /var/lib/mysql/

# BINARY LOGGING #
binlog_format = ROW
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 2
max_binlog_size = 100M
sync-binlog = 1

# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 4096

# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 6G
innodb_autoinc_lock_mode = 2

# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log

# Cluster Configuration #
wsrep_provider = /usr/lib/galera2/libgalera_smm.so
wsrep_provider_options = "pc.ignore_sb = yes; gcache.size=2G; gcache.page_size=1G"
wsrep_cluster_name = "XtraDB_Cluster"
wsrep_sst_method = xtrabackup-v2
wsrep_slave_threads = 16
wsrep_sst_auth = "sstuser:secret"
wsrep_cluster_address = "gcomm://......"

[sst]
streamfmt = xbstream

!includedir /etc/mysql/conf.d/
==================================


cu denny

startup script - any reason to exit if mysqld isn't running?

Lastest Forum Posts - July 15, 2015 - 12:49pm
With cluster in version 5.6 with auto-crash recovery, I'm looking at one of the support scripts: Percona-XtraDB-Cluster-5.6.24-72.2/support-files/mysql.server.sh ..

As it stands, if you have a cluster that died from a power cut, that script would prevent it from starting and being able to take advantage of auto-recovery. My question (as the topic states) - is there a reason to just bail if theres a pid file, but no matching process for it ? Why not just continue the start up ?

The function is "check_running()" and place in question is line 308 & 309.

If you go to the start case- line 339, abort unless ext_status=3 ..

Any thoughts appreciated.

Thanks
-b

working ss_get_by_ssh script, broken graph images

Lastest Forum Posts - July 15, 2015 - 11:22am
I'm having a strange issue on Centos 7 and Cacti 0.8.8b. I have the ss_get_by_ssh.php script working (from command line) but the cacti graphs are blank. Here's output from the script:

[root@ip-x.x.x.x ~]# sudo -u cacti php /usr/share/cacti/scripts/ss_get_by_ssh.php --type proc_stat --host y.y.y.y --items gw,gx,gy,gz
gw:2561826 gx:13105 gy:5533113 gz:2389303747

Every single Percona graph on Cacti is a broken image. Any ideas?

what is best way to setup pt-kill to kill queries 1+ min and report 40+ seconds?

Lastest Forum Posts - July 15, 2015 - 10:00am
hi,

What would be best way to setup pt-kill to do two tasks: report 40+ seconds queries and kill 1+ min queries? Just setup two different pt-kill with different params?

percona clister not replicating new created database

Lastest Forum Posts - July 15, 2015 - 3:00am
I am running a percona xtradb cluster of three nodes (per1, per2, per3). So far everything is working as expected however i an having an issue. I created a new database "testdb" on one of the servers (per1) and then i also created a new user "testuser" and gave full permissions on the new database.

Thing is the new database is not replicated on the other servers. I have checked my.cnf and i dont see anywhere specifying a specific database to be replicated. Apart from this, so far the other databases are properly replicated.

Any ideas?

MySQL QA Episode 6: Analyzing and Filtering

Latest MySQL Performance Blog posts - July 15, 2015 - 12:00am

Welcome to MySQL QA Episode #6!

Today we will look into analyzing and filtering our QA run. We’ll use tools like pquery-prep-red.sh, pquery-clean-known.sh & pquery-results.sh

1. Quick re-cap and QA run setup
2. pquery-prep-red.sh
3. pquery-clean-known.sh
4. pquery-results.sh
5. Bonus: pquery reach – pquery-reach++.sh

We’ll also introduce the text_string.sh tool which extracts a most-specific text string from the error log in order to classify a bug/issue.

Full-screen viewing @ 720p resolution recommended

The post MySQL QA Episode 6: Analyzing and Filtering appeared first on MySQL Performance Blog.

XtraBackup | Replication | Table 'mysql.slave_master_info' cannot be opened.

Lastest Forum Posts - July 14, 2015 - 8:04am
Hello - I am looking for some advise, I used Xtrabackup with no issues, fantastic bit of kit.
I took a backup of my database from the master and then moved the files over to the slave without issue

It's running but I am unable to get replication working now, it had worked before but the DB was fluffed!

2015-07-13 00:29:02 57003 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/...eshooting.html for how you can resolve the problem.
2015-07-13 00:29:02 57003 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
2015-07-13 00:29:02 57003 [ERROR] Error in checking mysql.slave_master_info repository info type of TABLE.
2015-07-13 00:29:02 57003 [ERROR] Error creating master info: Error checking repositories.
2015-07-13 00:29:02 57003 [ERROR] Failed to create or recover replication info repository.
2015-07-13 00:29:02 57003 [Note] Check error log for additional messages. You will not be able to start replication until the issue is resolved.

It can not read the following tables for some reason:

| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |

mysql> SELECT * from slave_worker_info;
ERROR 1146 (42S02): Table 'mysql.slave_worker_info' doesn't exist
mysql>

The issue is extremely similar to to:
http://dba.stackexchange.com/questio...ts/48189#48189

I have tried to drop these and re-create them but it keeps saying it exists.
I have dropped the tables, deleted the files from /var/lib/mysql/mysql and then ran the mysql_install_db

rm -rf slave_master_info.ibd slave_relay_log_info.frm slave_relay_log_info.ibd slave_worker_info.frm slave_worker_info.ibd slave_master_info.frm

I have also tried the above steps, minus the install_db and tried to create the tables however MySQL says the tables already exist?

They are no longer showing below but I cant create them?! as it says they exist

mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec)

mysql> CREATE TABLE `innodb_index_stats` ( `database_name` varchar(64) COLLATE utf8_bin NOT NULL, `table_name` varchar(64) COLLATE utf8_bin NOT NULL, `index_name` varchar(64) COLLATE utf8_bin NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `stat_name` varchar(64) COLLATE utf8_bin NOT NULL, `stat_value` bigint(20) unsigned NOT NULL, `sample_size` bigint(20) unsigned DEFAULT NULL, `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_n ame`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
ERROR 1050 (42S01): Table '`mysql`.`innodb_index_stats`' already exists

Can anyone help me?

MongoDB benchmark: sysbench-mongodb IO-bound workload comparison

Latest MySQL Performance Blog posts - July 14, 2015 - 7:26am

In this post I’ll share the results of a sysbench-mongodb benchmark I performed on my server. I compared MMAP, WiredTiger, RocksDB and TokuMXse (based on MongoDB 3.0) and TokuMX (based on MongoDB 2.4) in an IO-intensive workload.

The full results are available here, and below I’ll just share the summary chart:

I would like to highlight that this benchmark was designed to emulate a heavy IO load on a (relatively) slow IO subsystem. This use case, I believe, is totally valid and represents frequently used “cloud” setups with limited memory and slow IO.

The WiredTiger engine, as B-Tree based, is expected to perform worse comparing to RocksDB and Toku Fractal Trees, which, are designed to handle IO-intensive workloads. My assumption is that WiredTiger will perform better (or even outperform others) for CPU intensive in-memory workloads (see for example Mark Callaghan’s results). Also WiredTiger is expected to perform better with faster storage.

The post MongoDB benchmark: sysbench-mongodb IO-bound workload comparison appeared first on MySQL Performance Blog.

Upgrade MASTER-SLAVE MySQL 5.5 to fresh new MASTER-SLAVE Percona 5.6

Lastest Forum Posts - July 14, 2015 - 5:37am
Hi all,

I have a running Master-Slave MySQL 5.5 servers today - running on VERY high tense.
Running on Debian 6.0 64bit.
In parallel of checking on ways to optimize my code, I want to fresh up the hardware as well.

I have now a 2 new fresh servers (Dual x8 cores + 64GB ram + SSD RAID 1 and Dual x6 cores + 32GB ram + SSD RAID 1) . with Debian 8.0 64bit + Percona 5.6 .

Can you recommend on the BEST smooth way to upgrade from old setup to new setup with minimal impact on the production ?

mysqldump and restore is too slow for me

Thought maybe add both as slaves to the old setup , and somehow , in a blink of an eye, they become a master - slave , and drop the others.

Appreciate your help !

Percona 5.6 server: documentation flaw. Where to report?

Lastest Forum Posts - July 13, 2015 - 10:19am
Hi all,

I downloaded the server source code and want to compile it, so I also
downloaded the docco.

In Section 2.5 Compiling Percona Server from source.

The key part - the actual compile command - appears as

cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DBUILD_CONFIG=mysql_release -DFEATURE_SET=community -DWI

Note the part at the end - there are cmake options that are missing.
This occurs in both evince and okular.

Just thought I'd let you know, and I'd like the correct options for the default
compile :-)

Please feel free to contact me should you require any further information.

Rgs,


Pól

Error: option 'datadir' has different values

Lastest Forum Posts - July 13, 2015 - 8:40am
Hi everyone,
I'm running percona-xtrabackup in a test environment and I'm getting that weird error :
innobackupex: Error: option 'datadir' has different values:
'/var/lib/mysql' in defaults file
'/var/lib/mysql/' in SHOW VARIABLES Here is the full output :
root@percona:~# innobackupex --host=10.10.25.163 --user=percona --password=mypassword /data/percona/db/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

150713 17:26:04 innobackupex: Executing a version check against the server...
150713 17:26:04 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;ho st=10.10.25.163' as 'percona' (using password: YES).
150713 17:26:04 innobackupex: Connected to MySQL server
150713 17:26:04 innobackupex: Done.
150713 17:26:04 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;ho st=10.10.25.163' as 'percona' (using password: YES).
150713 17:26:04 innobackupex: Connected to MySQL server
150713 17:26:04 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".

innobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 3692
main::init() called at /usr/bin/innobackupex line 1574
innobackupex: Error: option 'datadir' has different values:
'/var/lib/mysql' in defaults file
'/var/lib/mysql/' in SHOW VARIABLES I created a local file percona.cnf with the following line :
: datadir = /var/lib/mysql/ Then ran innobackupex :
: innobackupex --host=10.10.25.163 --user=percona --password=mypassword --defaults-file=percona.cnf /data/percona/db/ But know I get this :
[...]
xtrabackup: Can't change dir to '/var/lib/mysql/' (Errcode: 2 - No such file or directory)
xtrabackup: cannot my_setwd /var/lib/mysql/ What can I do ?

Thanks

Xavier C.

How can we track prepare statement leak on MySQL 5.5

Lastest Forum Posts - July 13, 2015 - 7:29am
On one of our production servers, we are suspecting prepare statement leak. This instance is used my more than 100 customers each having individual database. So, on MySQL 5.5 how can we trace who is causing the leak. I think on MySQL 5.6 we can use performance_schema to do so. But other than using general queries is there a way to trace it in 5.5 ?

With Regards
Raghupradeep

MySQL QA Episode 5: Preparing Your QA Run with pquery

Latest MySQL Performance Blog posts - July 13, 2015 - 12:00am

Welcome to MySQL QA Episode #5! In this episode we’ll be setting up and running pquery for the first time… and I’ll also trigger some actual bugs (fun guaranteed)! I’ll also introduce you to mtr_to_sql.sh and pquery-run.sh.

pquery-run.sh (the main wrapper around pquery) is capable of generating 80-120 MySQL Server crashes – per hour! See how it all works in this episode…

Full-screen viewing @ 720p resolution recommended

The post MySQL QA Episode 5: Preparing Your QA Run with pquery appeared first on MySQL Performance Blog.

How to create a rock-solid MySQL database backup & recovery strategy

Latest MySQL Performance Blog posts - July 10, 2015 - 8:05am

Have you ever wondered what could happen if your MySQL database goes down?

Although it’s evident such a crash will cause downtime – and surely some business impact in terms of revenue – can you do something to reduce this impact?

The simple answer is “yes” by doing regular backups (of course) but are you 100% sure that your current backup strategy will really come through when an outage occurs? And how much precious time will pass (and how much revenue will be lost) before you get your business back online?

I usually think of backups as the step after HA fails. Let’s say we’re in M<>M replication and something occurs that kills the db but the HA can’t save the day. Let’s pretend that the UPS fails and those servers are completely out. You can’t failover; you have to restore data. Backups are a key piece of “Business Continuity.” Also factor in the frequent need to restore data that’s been altered by mistake. No ‘WHERE’ clause or DROP TABLE in prod instead of DEV. These instances are where backups are invaluable.

Let’s take some time and discuss the possible backup strategies with MySQL…  how to make backups efficiently and also examine the different tools that are available. We’ll cover these topics and more during my July 15  webinar: “Creating a Best-in-Class Backup and Recovery System for Your MySQL Environment” starting at 10 a.m. Pacific time.

On a related note, did you know that most online backups are possible with mysqldump and you can save some space on backups by using simple Linux tools? I’ll also cover this so be sure to join me next Wednesday. Oh, and it’s a free webinar, too!

Stay tuned!

The post How to create a rock-solid MySQL database backup & recovery strategy appeared first on MySQL Performance Blog.

Is it advisable to take full backup from slave after switchover?

Lastest Forum Posts - July 10, 2015 - 4:08am
Hello

We have two mysql servers, 1 as master and 2 as slave (using GTID replication 5.6.21)
Currently we take full back from master server from Mysql enterprise backup
We will be performing an activity to make slave as master and master as slave

Wanted to know if there is any harm if we take backup from newly made slave (which was master earlier)after switchover?

Is it advisable to take full backup from slave after switchover?

Lastest Forum Posts - July 10, 2015 - 4:08am
Hello

We have two mysql servers, 1 as master and 2 as slave (using GTID replication 5.6.21)
Currently we take full back from master server from Mysql enterprise backup
We will be performing an activity to make slave as master and master as slave

Wanted to know if there is any harm if we take backup from newly made slave (which was master earlier)after switchover?

mysqldump: unknown option '--END OF FILE

Lastest Forum Posts - July 10, 2015 - 2:12am
While taking dump of database from mysqldump I get below error

mysqldump -u root -p db_test > /home/test.sql

mysqldump: unknown option '--END OF FILE

Could anyone please help?

PXC 5.6.24 - BF applier failed to open_and_lock_tables

Lastest Forum Posts - July 9, 2015 - 10:29am
I am running a 3 node cluster of PXC and I keep getting random crashes on all 3 nodes.

Setup:
Ubuntu 14.04.2 LTS
- 60 GB RAM
- SSD RAID 10 (130GB)
PXC 5.6.24-72.2-56-log - Percona XtraDB Cluster (GPL), Release rel72.2, Revision 43abf03, WSREP version 25.11, wsrep_25.11
>>
[mysqld]

# GENERAL #
bind-address = 0.0.0.0
character-set-server = utf8
collation-server = utf8_general_ci
default_storage_engine = InnoDB
event-scheduler = ON
pid-file = /var/run/mysqld/mysqld.pid
port = 3306
server-id = 1
socket = /var/run/mysqld/mysqld.sock
user = mysql

# MyISAM #
key-buffer-size = 32M
myisam-recover-options = FORCE,BACKUP

# SAFETY #
innodb = FORCE
innodb-strict-mode = 1
max-allowed-packet = 64M
max-connect-errors = 1000000
skip-external-locking
skip-host-cache
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VA LUE_ON_ZERO,NO_ENGINE_SUBSTITUTION
sysdate-is-now = 1

# DATA STORAGE #
datadir = /var/lib/mysql

# BINARY LOGGING #
expire-logs-days = 14
log-bin = /var/lib/mysql/mysql-bin
log-slave-updates
sync-binlog = 1

# CACHES AND LIMITS #
back-log = 1000
connect-timeout = 20
interactive-timeout = 30
join-buffer-size = 8M
max-binlog-size = 100M
max-connections = 2000
max-heap-table-size = 32M
open-files-limit = 65535
preload-buffer-size = 65536
query-cache-size = 0
query-cache-type = 0
sort-buffer-size = 2M
read-buffer-size = 4M
read-rnd-buffer-size = 4M
table-definition-cache = 4096
table-open-cache = 5000
thread-cache-size = 100
thread-stack = 256K
tmp-table-size = 32M
wait-timeout = 30

# INNODB #
innodb-buffer-pool-instances = 8
innodb-buffer-pool-size = 40G
innodb-file-per-table = 1
innodb-flush-log-at-trx-commit = 1
innodb-flush-method = O_DIRECT
innodb-lock-wait-timeout = 15
innodb-log-files-in-group = 2
innodb-log-file-size = 512M

# LOGGING *
log-error = /var/log/mysql/mysql-error.log
log-queries-not-using-indexes = 0
slow-query-log = 0

# WSREP #
wsrep_provider = /usr/lib/galera3/libgalera_smm.so
wsrep_cluster_address = gcomm://<redacted>,<redacted>,<redacted>
binlog_format = ROW
innodb_autoinc_lock_mode = 2
wsrep_node_address = <redacted>
wsrep_node_name = "db01"
wsrep_sst_method = xtrabackup-v2
wsrep_cluster_name = <redacted>
wsrep_sst_auth = "<redacted>"
wsrep_slave_threads = 8
wsrep_notify_cmd = /etc/mysql/wsrep_notify
<<

I was getting crashes every 1-3 days on all 3 nodes until the release of PXC 5.6.24. Now I get them about once a week. At first, I thought it was a specific cron job because the crash timestamps had similar minutes, so I looked at the job but I couldn't replicate the error manually. Then the crash timestamps started to be different so I haven't been able to track any pattern. When the nodes crash, I get the same error across the board:

2015-07-09 00:44:36 19638 [Warning] WSREP: BF applier failed to open_and_lock_tables: 1615, fatal: 0 wsrep = (exec_mode: 1 conflict_state: 5 seqno: 46677454)
2015-07-09 00:44:36 19638 [Warning] WSREP: RBR event 3 Write_rows apply warning: 1615, 46677454
2015-07-09 00:44:36 19638 [Warning] WSREP: Failed to apply app buffer: seqno: 46677454, status: 1
at galera/src/trx_handle.cpp:apply():351
Retrying 2th time
2015-07-09 00:44:36 19638 [Warning] WSREP: BF applier failed to open_and_lock_tables: 1615, fatal: 0 wsrep = (exec_mode: 1 conflict_state: 5 seqno: 46677454)
2015-07-09 00:44:36 19638 [Warning] WSREP: RBR event 3 Write_rows apply warning: 1615, 46677454
2015-07-09 00:44:36 19638 [Warning] WSREP: Failed to apply app buffer: seqno: 46677454, status: 1
at galera/src/trx_handle.cpp:apply():351
Retrying 3th time
2015-07-09 00:44:36 19638 [Warning] WSREP: BF applier failed to open_and_lock_tables: 1615, fatal: 0 wsrep = (exec_mode: 1 conflict_state: 5 seqno: 46677454)
2015-07-09 00:44:36 19638 [Warning] WSREP: RBR event 3 Write_rows apply warning: 1615, 46677454
2015-07-09 00:44:36 19638 [Warning] WSREP: Failed to apply app buffer: seqno: 46677454, status: 1
at galera/src/trx_handle.cpp:apply():351
Retrying 4th time
2015-07-09 00:44:36 19638 [Warning] WSREP: BF applier failed to open_and_lock_tables: 1615, fatal: 0 wsrep = (exec_mode: 1 conflict_state: 5 seqno: 46677454)
2015-07-09 00:44:36 19638 [Warning] WSREP: RBR event 3 Write_rows apply warning: 1615, 46677454
2015-07-09 00:44:36 19638 [Warning] WSREP: failed to replay trx: source: f3b46697-1ff6-11e5-af61-0b245f7246eb version: 3 local: 1 state: REPLAYING flags: 129 conn_id: 6286358 trx_id: 366113962 seqnos (l: 6786080, g: 46677454, s: 46677452, d: 46677453, ts: 2721954221674858)
2015-07-09 00:44:36 19638 [Warning] WSREP: Failed to apply trx 46677454 4 times
2015-07-09 00:44:36 19638 [ERROR] WSREP: trx_replay failed for: 6, query: void
2015-07-09 00:44:36 19638 [ERROR] Aborting

After the node fails, it ALWAYS has to do a SST (instead of an IST). I have tried to Google around and I have found several people having the same issue, but no resolutions. Is this a configuration problem? A bug that needs to be reported?

Any help would be appreciated. Thanks in advance!

How to get a performance upgrade to this complicated Select...

Lastest Forum Posts - July 9, 2015 - 8:20am
Hey
First of all, I am really new here in this Forum and so, the first I have to say is: HELLO
Ok - I have the following Problem with a Select.
I create temporary tables, because I have to concat some data. - The second query, has 4 UNION ALL Statements.
I can't Change the database tables. - All I can do is to perform the Select statments and/or create Indexes.
I do have parallel a MS SQL Server (not in production - the mySQL Server is the production Server) - There the statment runs all in all within half a second. - On the mySQL Server the same Statement Needs about 4 seconds... (same Hardware)
I hope somebody can help me with Tuning of this (Please don't doubt on the Statement, it is put togegther from my Software, where, like in this case, some values are the same. - so it Looks like there is the same Statement over and over, but this depends on the customers Needs!)
So, here is the Statement

DROP TEMPORARY TABLE IF EXISTS tmpGlaeser;
DROP TEMPORARY TABLE IF EXISTS tmpSchicht;

CREATE TEMPORARY TABLE tmpGlaeser (
PRIMARY KEY (ID),
INDEX (hst_code_grundglas),
INDEX (lieferbar_ab),
INDEX (lieferbar_bis)
) ENGINE = MEMORY
SELECT DISTINCT
iprolenstype.*
FROM iprolensrange iprolensrange
INNER JOIN iprolenstype iprolenstype
ON iprolensrange.hst_code_grundglas = iprolenstype.hst_code_grundglas
WHERE (1.5 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100)
AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100)
AND iprolenstype.hst_code_grundglas IN (SELECT
hst_code_grundglas
FROM iprolensrange
WHERE (1.5 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100)
AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100))
AND (CURDATE() BETWEEN IFNULL(iprolenstype.lieferbar_ab, CURDATE()) AND IFNULL(iprolenstype.lieferbar_bis, CURDATE()))
AND (0 <= iprolensrange.prisma_bis)
AND (0 <= iprolensrange.prisma_bis
);
CREATE TEMPORARY TABLE tmpSchicht(
INDEX (grundglas),
INDEX (photo),
INDEX (schicht))
ENGINE = MEMORY
SELECT
grundglas,
photo,
schicht
FROM (SELECT DISTINCT
i1.hst_code_grundglas AS grundglas,
iprooptions.Phototrop AS photo,
iprooptions.hst_code_schicht AS schicht
FROM iprooptions
LEFT JOIN iprocombination i1
ON (iprooptions.hst_code_schicht = i1.hst_code_schicht1
AND i1.hst_code_grundglas IN (SELECT DISTINCT
iprolenstype.hst_code_grundglas
FROM iprolensrange iprolensrange
INNER JOIN iprolenstype iprolenstype
ON iprolensrange.hst_code_grundglas = iprolenstype.hst_code_grundglas
WHERE (1.5 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100)
AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100)
AND iprolenstype.hst_code_grundglas IN (SELECT
hst_code_grundglas
FROM iprolensrange
WHERE (1.5 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100)
AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100))
AND (CURDATE() BETWEEN IFNULL(iprolenstype.lieferbar_ab, CURDATE()) AND IFNULL(iprolenstype.lieferbar_bis, CURDATE()))
AND (0 <= iprolensrange.prisma_bis)
AND (0 <= iprolensrange.prisma_bis))
AND i1.hst_code_schicht1 <> '******'
AND i1.lieferbarkeit = 2
AND iprooptions.manufacturer_code = i1.manufacturer_code)
WHERE iprooptions.Farbe > 0
UNION ALL
SELECT DISTINCT
i2.hst_code_grundglas AS grundglas,
iprooptions.Phototrop AS photo,
iprooptions.hst_code_schicht AS schicht
FROM iprooptions
LEFT JOIN iprocombination i2
ON (iprooptions.hst_code_schicht = i2.hst_code_schicht2
AND i2.hst_code_grundglas IN (SELECT DISTINCT
iprolenstype.hst_code_grundglas
FROM iprolensrange iprolensrange
INNER JOIN iprolenstype iprolenstype
ON iprolensrange.hst_code_grundglas = iprolenstype.hst_code_grundglas
WHERE (1.5 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100)
AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100)
AND iprolenstype.hst_code_grundglas IN (SELECT
hst_code_grundglas
FROM iprolensrange
WHERE (1.5 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100)
AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100))
AND (CURDATE() BETWEEN IFNULL(iprolenstype.lieferbar_ab, CURDATE()) AND IFNULL(iprolenstype.lieferbar_bis, CURDATE()))
AND (0 <= iprolensrange.prisma_bis)
AND (0 <= iprolensrange.prisma_bis))
AND i2.hst_code_schicht1 <> '******'
AND i2.lieferbarkeit = 2
AND iprooptions.manufacturer_code = i2.manufacturer_code)
WHERE iprooptions.Farbe > 0
UNION ALL
SELECT DISTINCT
i3.hst_code_grundglas AS grundglas,
iprooptions.Phototrop AS photo,
iprooptions.hst_code_schicht AS schicht
FROM iprooptions
LEFT JOIN iprocombination i3
ON (iprooptions.hst_code_schicht = i3.hst_code_schicht1
AND i3.hst_code_grundglas IN (SELECT DISTINCT
iprolenstype.hst_code_grundglas
FROM iprolensrange iprolensrange
INNER JOIN iprolenstype iprolenstype
ON iprolensrange.hst_code_grundglas = iprolenstype.hst_code_grundglas
WHERE (1.5 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100)
AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100)
AND iprolenstype.hst_code_grundglas IN (SELECT
hst_code_grundglas
FROM iprolensrange
WHERE (1.5 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100)
AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100))
AND (CURDATE() BETWEEN IFNULL(iprolenstype.lieferbar_ab, CURDATE()) AND IFNULL(iprolenstype.lieferbar_bis, CURDATE()))
AND (0 <= iprolensrange.prisma_bis)
AND (0 <= iprolensrange.prisma_bis))
AND i3.hst_code_schicht1 <> '******'
AND i3.lieferbarkeit = 2
AND iprooptions.manufacturer_code = i3.manufacturer_code)
WHERE iprooptions.Farbe > 0) AS newTable
WHERE grundglas IS NOT NULL;
UPDATE tmpGlaeser g, tmpSchicht s
SET g.Phototrop = s.photo
WHERE g.hst_code_grundglas = s.grundglas
AND g.Phototrop < s.photo;
SELECT
*
FROM tmpGlaeser ORDER BY manufacturer_code, hst_code_grundglas;