Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.10
-
None
-
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.
Attachments
Activity
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 
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...
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.