MySQL Bugs: #37803: Test "partition_alter2_innodb" exhausts resources (time and/or memory)

Bug #37803	Test "partition_alter2_innodb" exhausts resources (time and/or memory)
Submitted:	2 Jul 2008 11:09	Modified:	17 Oct 2008 17:40
Reporter:	Joerg Bruehe	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Tests	Severity:	S3 (Non-critical)
Version:	5.1.26,6.0.6	OS:	Any
Assigned to:	Mattias Jonsson	CPU Architecture:	Any

Description:
The following log extracts are taken from the 5.1.26-rc build,
but similar problems go back to 5.1.19-beta and all versions since,
they were just not specific enough to get reported.

Typically, the test fails due to either (individual) test case timeout,
or to "Out of memory" situations.
I suspect these two might be related, so the same fix might help for both things.
(No, raising the timeout is no solution -
we need to reduce the time for a full test run, and raising the timeout would act against this goal.
Of course, it may be needed to analyze this problem.)

Tests are still running, so more cases will follow.
Until now, 

===== case 1 =====
Out of memory (Needed 1363968 bytes)
mysqltest: In included file "./suite/parts/inc/partition_check.inc": At line 950: unknown error

The result from queries just before the failure was:
< snip >
INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig)
SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1,
'', '', 'was inserted' FROM t0_template
WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4;
ERROR 22012: Division by 0
COMMIT;

# check transactions-8 success:         1
# INFO: Storage engine used for t1 seems to be able to revert
#       changes made by the failing statement.
SET @@session.sql_mode = '';
SET AUTOCOMMIT= 1;
DELETE FROM t1 WHERE f_charbig = 'was inserted';
COMMIT WORK;
UPDATE t1 SET f_charbig = REPEAT('b', 1000);

# check special-1 success:      1
UPDATE t1 SET f_charbig = '';

# check special-2 success:      1

More results from queries before failure can be found in /PATH/mysql-test/var/log/partition_alter2_innodb.log

Stopping All Servers
=====
AIX 5.3 (32 bit), "community" build.

===== case 2 =====
parts.partition_alter2_innodb  [ fail ]

Out of memory (Needed 4456448 bytes)
Out of memory (Needed 4456448 bytes)
...
Out of memory (Needed 4456448 bytes)
Out of memory (Needed 1892352 bytes)
mysqltest: In included file "./suite/parts/inc/partition_layout_check1.inc": At line 15: unknown error

The result from queries just before the failure was:
< snip >
CREATE TRIGGER trg_1 AFTER DELETE ON t0_aux FOR EACH ROW
BEGIN
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
f_charbig = 'updated by trigger'
      WHERE f_int1 = - old.f_int1;
END|
DELETE FROM t0_aux
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));

# check trigger-8 success:      1
DROP TRIGGER trg_1;
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
f_int2 = CAST(f_char1 AS SIGNED INT),
f_charbig = 'just inserted'
   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
;
;
|
;

More results from queries before failure can be found in /PATH/mysql-test/var/log/partition_alter2_innodb.log

Stopping All Servers
=====
FreeBSD 6.0 (x86, 32 bit), "community".

===== case 3 =====
parts.partition_alter2_innodb  [ fail ]

Out of memory (Needed 3211264 bytes)
Out of memory (Needed 3211264 bytes)
...
Out of memory (Needed 3211264 bytes)
Out of memory (Needed 1359872 bytes)
mysqltest: In included file "./suite/parts/inc/partition_layout_check2.inc": At line 66: unknown error

The result from queries just before the failure was:
< snip >
UPDATE t1 SET f_int1 = -f_int1, f_int2 = -f_int2,
f_charbig = 'updated by trigger'
      WHERE f_int1 = - old.f_int1;
END|
DELETE FROM t0_aux
WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1));

# check trigger-8 success:      1
DROP TRIGGER trg_1;
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
f_int2 = CAST(f_char1 AS SIGNED INT),
f_charbig = 'just inserted'
   WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
;
;
|
;

        1
;

More results from queries before failure can be found in /PATH/mysql-test/var/log/partition_alter2_innodb.log

Stopping All Servers
=====
i5os (32 bit), "community"

===== case 4 =====
parts.partition_alter2_innodb  [ fail ]  timeout

Stopping All Servers
=====
RPM SLES 10, x86, "advanced"
RPM generic IA64 (icc), "community"
AIX 5.2 (both 32 and 64 bit), "community"
HP-UX IA64, "community"
Solaris 10 Sparc (both 32 and 64 bit), "community"
OSX 10.5 PPC (32 bit),  "community"

How to repeat:
Run the "parts" suite.

Suggested fix:
Check whether it is possible to simplify the test
(so that it needs less time and memory)
without losing too much functional coverage.

Look at bug#35744 for a similar issue.

bzr gcommit failed to send commit mail since too many/big results file changes, so here is the 'bzr diff -r -2' output

Attachment: b37803.diff.gz (application/x-gzip, text), 262.48 KiB.

Committed a patch (se above):
 2673 Mattias Jonsson	2008-07-11
      Bug#37402: Mysql cant read partitioned table with capital letter in the name
      
      Problem was that ha_partition had HA_FILE_BASED flag set
      (since it uses a .par file), but after open it uses the first partitions
      flags, which results in different case handling for create and for
      open.
      
      Solution was to change the underlying partition name so it was consistent.
      (Only happens when lower_case_table_names = 2, i.e. Mac OS X and storage
      engines without HA_FILE_BASED, like InnoDB and Memory.)
      
      (Recommit after adding rename of check_lowercase_names to
      get_canonical_filename, and moved it from handler.h to mysql_priv.h)
      
      NOTE: if a mixed case name for a partitioned table was created when
      lower_case_table_name = 2 it should be renamed or dropped before using
      the updated version (See bug#37402 for more info)

Ok to push.

Cut down on run time of the test (and the parts suite):
Patched:
parts.partition_alter2_innodb  [ pass ]         113613
parts.partition_alter2_myisam  [ pass ]          61631
Spent 2371.812 of 2541 seconds executing testcases

Original:
parts.partition_alter2_innodb  [ pass ]         304049
parts.partition_alter2_myisam  [ pass ]         134821
Spent 2670.995 of 3926 seconds executing testcases

Back to 'In progress'. I will investigate further why innodb takes more resources, and how much memory/disk/cpu that would be expected and acceptable.

Proposed patch (splitted partition_alter2.inc into two tests)

Attachment: b37803_2.mail.gz (application/x-gzip, text), 122.21 KiB.

I have done a new patch (but since it affects the biggest result file in the framework i had to add it as a compressed file, since it was too big to mail)

The patch just divides the test case into two tests (one for increasing column size and one for decreasing column size)

Here is the change set comment:
 2735 Mattias Jonsson   2008-09-18
      Bug#37803: Test "partition_alter2_innodb" exhausts resources (time and/or memory)

      It is a very big test and as such it takes a lot of time.

      Solution is to divide the test in two parts, one for testing increasing
      column size and one for decreasing size.

      The innodb branch does extended tests (that myisam is not) due to the
      $do_pk_tests variabel, that is the reason why the innodb branch takes
      longer.

      No increase of memory usage in innodb was found when analyzing, (tested
      with looping some millions time of create/drop and alter commands)

      The memory exhaust discovered in the test is due to mysqltest which
      stores the result in memory (result-file) and this was the biggest
      result file in the test framework, so by dividing the test into two
      parts also cuts the memory usage of mysqltest.
removed:
  mysql-test/suite/parts/t/partition_alter2_ndb.test
added:
  mysql-test/suite/parts/inc/partition_alter2_2.inc
  mysql-test/suite/parts/r/partition_alter2_2_innodb.result
  mysql-test/suite/parts/r/partition_alter2_2_myisam.result
  mysql-test/suite/parts/t/partition_alter2_2_innodb.test
  mysql-test/suite/parts/t/partition_alter2_2_myisam.test
renamed:
  mysql-test/suite/parts/inc/partition_alter2.inc => mysql-test/suite/parts/inc/partition_alter2_1.inc
  mysql-test/suite/parts/r/partition_alter2_innodb.result => mysql-test/suite/parts/r/partition_alter2_1_innodb.result
  mysql-test/suite/parts/r/partition_alter2_myisam.result => mysql-test/suite/parts/r/partition_alter2_1_myisam.result
  mysql-test/suite/parts/t/partition_alter2_innodb.test => mysql-test/suite/parts/t/partition_alter2_1_innodb.test
  mysql-test/suite/parts/t/partition_alter2_myisam.test => mysql-test/suite/parts/t/partition_alter2_1_myisam.test
modified:
  mysql-test/suite/parts/t/disabled.def
  mysql-test/suite/parts/inc/partition_alter2_1.inc
  mysql-test/suite/parts/r/partition_alter2_1_innodb.result
  mysql-test/suite/parts/r/partition_alter2_1_myisam.result
  mysql-test/suite/parts/t/partition_alter2_1_innodb.test
  mysql-test/suite/parts/t/partition_alter2_1_myisam.test

Pushed into 5.1.30  (revid:mattias.jonsson@sun.com-20080918090248-3givvc9xqk9xlp1y) (version source revid:kpettersson@mysql.com-20080925170846-pq6nrweqvm2yfiym) (pib:4)

This is actually pushed to 5.1.29, not 5.1.30.

Test case changes. No changelog entry needed.

Setting report to NDI pending push into 6.0.x.

Pushed into 6.0.8-alpha  (revid:mattias.jonsson@sun.com-20080923130815-s8m77qazkowf0oc0) (version source revid:mattias.jonsson@sun.com-20080923130815-s8m77qazkowf0oc0) (pib:5)

No changelog entry needed.

Pushed into 5.1.29-ndb-6.2.17  (revid:mattias.jonsson@sun.com-20080918090248-3givvc9xqk9xlp1y) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)

Pushed into 5.1.29-ndb-6.3.19  (revid:mattias.jonsson@sun.com-20080918090248-3givvc9xqk9xlp1y) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)

Pushed into 5.1.29-ndb-6.4.0  (revid:mattias.jonsson@sun.com-20080918090248-3givvc9xqk9xlp1y) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)