[MDEV-6019] Creating CONNECT PIVOT table on large base table crashes server Created: 2014-04-03  Updated: 2014-04-04  Resolved: 2014-04-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.10
Fix Version/s: 10.0.11

Type: Bug Priority: Major
Reporter: Kolbe Kegel (Inactive) Assignee: Olivier Bertrand
Resolution: Fixed Votes: 0
Labels: connect-engine
Environment:

Mac OS X, CentOS 6.5


 Description   

I have a table with a couple million rows in it:

CREATE TABLE `big_table` (
 
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 
  `c1` char(32) DEFAULT NULL,
 
  `c2` char(32) DEFAULT NULL,
 
  `c3` char(32) DEFAULT NULL,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB;

Each row of the table has different values in the character columns, but all three character columns are the same for each row:

mysql 10.0.10-MariaDB (root) [test]> select * from big_table limit 10;
 
+---------+----------------------------------+----------------------------------+----------------------------------+
 
| id      | c1                               | c2                               | c3                               |
 
+---------+----------------------------------+----------------------------------+----------------------------------+
 
| 1960001 | a64f35b091f92301e81abd6c881efa4c | a64f35b091f92301e81abd6c881efa4c | a64f35b091f92301e81abd6c881efa4c |
 
| 1960002 | 3cddf70e775e566a270b3a80ebeb635f | 3cddf70e775e566a270b3a80ebeb635f | 3cddf70e775e566a270b3a80ebeb635f |
 
| 1960003 | f3f75c6448ae94cc20c78bcff17b0d92 | f3f75c6448ae94cc20c78bcff17b0d92 | f3f75c6448ae94cc20c78bcff17b0d92 |
 
| 1960004 | f0f39711ef82cb8f99443b59ad12f940 | f0f39711ef82cb8f99443b59ad12f940 | f0f39711ef82cb8f99443b59ad12f940 |

I tried creating a really simple pivot table:

create table big_table_pivot engine=connect table_type=pivot tabname=big_table;

Shortly after executing that create table statement, mysqld crashes:

 #0  0x00007ffff6a907fe in __longjmp () from /lib64/libc.so.6
 
 #1  0x4830f0d22fbc1ec0 in ?? ()

It crashes even after removing quite a lot of rows. Now with <200,000 rows in the table, the CREATE TABLE statement appears to be hanging, rather than crashing.

 Comments   
Comment by Olivier Bertrand [ 2014-04-04 ]

Well, PIVOT is probably not meant to be used on very big tables. It requires a lot of storage to find the distinct PIVOT column names and, if there are many, can produce tables with too many columns.
However, a bug caused exhausted memory to crash the server instead of raising an error condition with a proper message.
This was fixed and it should not crash anymore.
By the way, it is not hanging on big tables but just takes a very long time.

Comment by Kolbe Kegel (Inactive) [ 2014-04-04 ]

I thought it was maybe some problem with memory usage, but it didn't seem like mysqld memory usage really went up that much.

I had success using my own hand-rolled "pivot" approach for this large table where I did GROUP BY and GROUP_CONCAT(IF(pivotCol=1,factCol,null)) as value_1, repeating that pattern for each value of the pivot column. I was hoping I could use the pivot feature in connect to do that heavy lifting for me. I'm still sort of figuring out how pivot in connect works, so maybe I can get it to do what I want if I keep poking at it enough

Comment by Olivier Bertrand [ 2014-04-04 ]

Ooops, I did not quite understand what you did...
However, it's normal that mysqld memory usage did not went up because CONNECT has its own memory allocation system (in fact sub-allocation) that is limited by the size of the area in which it works. What I am working on is to add a system variable allowing to change the size of this work area. For instance increasing it from 64M (the default) to 256M enabled me to create a pivot table on a 10 million rows table. It is created but still fails to be used, more work to do...

Generated at Thu Feb 08 07:08:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.