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
| Field | Original Value | New Value |
|---|---|---|
| Fix Version/s | 10.0.11 [ 15200 ] | |
| Assignee | Olivier Bertrand [ bertrandop ] |
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
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...
| Workflow | defaullt [ 38105 ] | MariaDB v2 [ 42742 ] |
| Workflow | MariaDB v2 [ 42742 ] | MariaDB v3 [ 61749 ] |
| Workflow | MariaDB v3 [ 61749 ] | MariaDB v4 [ 147742 ] |
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.