gcache.page. files not removed fast enough for some workloads

[Przemek]

This is copy of initial report: https://github.com/codership/galera/issues/317

For workloads with huge transactions it may happen that gcache.page.xxxx files will fill all the available disk space as due to current removal algorithm, oldest files may remain pretty long.

Here is a quickly reproducible test case:

perl -e '$s="$s\x31"; for my $i (0..26214400) { print $s; } ' > /tmp/blob

use test
CREATE TABLE blob1 (
id int(11) NOT NULL,
a tinyint(4) DEFAULT NULL,
big longblob,
PRIMARY KEY (id)
) ENGINE=InnoDB;
insert into blob1 values (1,1,LOAD_FILE('/tmp/blob'));

for i in {1..50}; do mysql test -e "UPDATE blob1 SET a=$i,big=LOAD_FILE('/tmp/blob')"; done

[Fuel Devops McRobotson (fuel-devops-robot)]

Related fix proposed to branch: master
Change author: Ivan Suzdal <email address hidden>
Review: https://review.fuel-infra.org/16432

[Fuel Devops McRobotson (fuel-devops-robot)]

Reviewed: https://review.fuel-infra.org/16432
Submitter: Pkgs Jenkins <email address hidden>
Branch: master

Commit: e7558b9c8ae2893ebef62cd5dd213b737c0b4f36
Author: Ivan Suzdal <email address hidden>
Date: Mon Jan 25 12:55:55 2016

Fix for gcache.page files removal.

Change-Id: Ia74799e9ea55a04a23cc0f13bab638522d1d08ec
Related-Bug: #1488530

[Fuel Devops McRobotson (fuel-devops-robot)]

Related fix proposed to branch: 8.0
Change author: Ivan Suzdal <email address hidden>
Review: https://review.fuel-infra.org/16499

[Fuel Devops McRobotson (fuel-devops-robot)]

Reviewed: https://review.fuel-infra.org/16499
Submitter: Pkgs Jenkins <email address hidden>
Branch: 8.0

Commit: 8acb72b37f8efa0b7a0911e4fd399dfcf8dadcd7
Author: Ivan Suzdal <email address hidden>
Date: Wed Jan 27 18:09:58 2016

Fix for gcache.page files removal.

Change-Id: Ia74799e9ea55a04a23cc0f13bab638522d1d08ec
Related-Bug: #1488530
(cherry picked from commit e7558b9c8ae2893ebef62cd5dd213b737c0b4f36)

[Timur Nurlygayanov (tnurlygayanov)]

Verified om MOS 8.0 RC1:

mysql> use test
ERROR 1049 (42000): Unknown database 'test'
mysql> CREATE DATABASE test
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> CREATE TABLE blob1 (
    -> id int(11) NOT NULL,
    -> a tinyint(4) DEFAULT NULL,
    -> big longblob,
    -> PRIMARY KEY (id)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into blob1 values (1,1,LOAD_FILE('/tmp/blob'));
ERROR 1118 (42000): The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size.

So, the size of this file was reduced.

issue not reproduced after
root@node-1:~# for i in {1..50}; do mysql test -e "UPDATE blob1 SET a=$i,big=LOAD_FILE('/tmp/blob')"; done
root@node-1:~# for i in {1..5000}; do mysql test -e "UPDATE blob1 SET a=$i,big=LOAD_FILE('/tmp/blob')"; done
root@node-1:~#

Moved to Fix Released.

[Rodion Tikunov (rtikunov)]

In 7.0 galera updated to galera-3 and the bug can not be reproduced.

[Rodion Tikunov (rtikunov)]

Could not reproduce for 6.1

[Alexander Gubanov (ogubanov)]

Verified on MOS 9.0 (build 201) - can't reproduce.
Details: http://pastebin.com/fiKp86nA

[Claudio (claudio-enjoy)]

same issue with :

Percona-XtraDB-Cluster-client-56-5.6.35-26.20.2.el6.x86_64
Percona-XtraDB-Cluster-shared-56-5.6.35-26.20.2.el6.x86_64
Percona-XtraDB-Cluster-56-5.6.35-26.20.2.el6.x86_64
percona-toolkit-2.2.15-2.noarch
Percona-XtraDB-Cluster-galera-3-3.20-2.el6.x86_64
percona-xtrabackup-2.3.5-1.el6.x86_64
Percona-XtraDB-Cluster-server-56-5.6.35-26.20.2.el6.x86_64

both configuration generate 300+ gcache.page.000* logs

1 wsrep_provider_options = gcache.size=2G;gcache.keep_pages_size=100M;gcache.keep_pages_count=20
2 wsrep_provider_options = gcache.size=2G;gcache.keep_pages_size=2GB;gcache.keep_pages_count=10

any suggestion ?

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-1025