[MDEV-16249] CHECKSUM TABLE for a spider table is not parallel and saves all data in memory in the spider head by default Created: 2018-05-22  Updated: 2020-08-25  Resolved: 2019-06-10

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Spider
Affects Version/s: 10.3
Fix Version/s: 10.4.6

Type: Bug Priority: Critical
Reporter: Mattias Jonsson Assignee: Kentoku Shiba (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux

Attachments: PDF File heap_profile.pdf    
Issue Links:
Problem/Incident
causes MDEV-19842 Crash while creating statistics for S... Closed
Relates
relates to MDEV-16520 Out-Of-Memory running big aggregate q... Closed
relates to MDEV-16880 Provide checksum aggregate functions,... Open

 Description   

When doing 'CHECKSUM TABLE t' on a partitioned spider table it will fetch all rows from the different data nodes sequentially and store the result on the spider head. On very large tables the mysqld process will be killed due to OOM (without a trace in the error log).

One suggested workaround is to set spider_quick_mode = 3 before running such statement, but we would prefer that the command is sent to each data node and executed in parallel and then aggregate (xor?) the result on the spider head.

This appears to be a specific case of the more general issue that a large result may cause an Out-Of-Memory on the spider head. This should never be the case, and thus we would prefer that spider have an upper limit on how much results it can cache on the spider head, or some other way to avoid a valid query causing a server crash due to out of memory.

 Comments   
Comment by Mattias Jonsson [ 2018-05-22 ]

The attached heap_profile.pdf was created by:
export HEAPPROFILE=/tmp/mybin.hprof
export LD_PRELOAD="/usr/lib64/libtcmalloc_and_profiler.so.4"

start mysqld

ran the CHECKSUM TABLE big_table and then generated a pdf by:
sudo pprof --base=/tmp/mybin.hprof.0554.heap --pdf /usr/local/mysql/bin/mysqld /tmp/mybin.hprof.0632.heap > ~/heap_profile.pdf

Comment by Eric Herman [ 2018-05-23 ]

Checksum per-partition has been requested for Oracle MySQL as well: https://bugs.mysql.com/bug.php?id=78733

Comment by Mattias Jonsson [ 2018-06-19 ]

Probably the same issue as the more generic queries in MDEV-16520?

Comment by Mattias Jonsson [ 2018-06-19 ]

Also the server crashes if using spider_quick_mode = 3: MDEV-16521

Comment by Kentoku Shiba (Inactive) [ 2019-03-03 ]

Add handler methods for pushing down checksum table with options.
This changes are included small server side changes, please review about this.
60ed52f

Comment by Sergei Golubchik [ 2019-03-26 ]

I'd suggest a different approach. Make existing handler::checksum() method to calculate the checksum in the engine, the slow way. Move current implementation from sql_table.cc to handler::checksum().

And to get the fast checksum value from the engine — use handler::info(), not handler::checksum().

Comment by Kentoku Shiba (Inactive) [ 2019-04-05 ]

serg

> And to get the fast checksum value from the engine — use handler::info(), not handler::checksum().

Currently, hsndler::info() does not return checksum value. Should I add checksum value into ha_statistics class?
Also, I think it requires to move logic from handler::checksum() to handler::info() of all storage engines. Would it possible to I avoid it?

Comment by Sergei Golubchik [ 2019-04-22 ]

Yes, that's what I mean, moving checksum to handler::info(), ha_statistics looks like a good place.

Only myisam and aria support live checksum, there are no other engines that are affected, as far as I can see.

Comment by Sergei Golubchik [ 2019-05-07 ]

checksum API refactoring is pushed into 10.3, commit ffb83ba6502

Comment by Kentoku Shiba (Inactive) [ 2019-05-23 ]

serg
I added checksum_null parameter to ha_statistics, because Spider have to set null value if Spider get null from data nodes. Would you please review this again?
a252067

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