Bug #33250 SELECT * FROM really_big_federated_table eats lots of virtual memory (OOM)
Submitted: 14 Dec 2007 17:17 Modified: 21 Dec 2009 5:00
Reporter: Michael Bacarella Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Federated storage engine Severity:S2 (Serious)
Version:mysql-5.1.22-rc OS:Linux
Assigned to: CPU Architecture:Any
Tags: 5.1, crash, federated, OOM, swap
Triage: Triaged: D1 (Critical)

[14 Dec 2007 17:17] Michael Bacarella
Description:
Short: 50GB federated table means 50GB of VM used.

Long:

SELECT * FROM really_big_federated_table; will use up at least size-of-table virtual memory on the host with the ENGINE=FEDERATED table.  If the table is bigger than the amount of virtual memory available on the system it will cause OOM.

I've been able to demonstrate this on Linux but believe it would affect any OS.

How to repeat:
On serverA create a_really_big_table.  Fill it with 50GB of data.

On serverB create a_really_big_table with ENGINE=FEDERATED, referencing a_really_big_table on serverA.

Connect to serverB and SELECT * FROM a_really_big_table;  On serverB, run top and watch VSIZE increase rapidly as mysqld pulls a_really_big_table into VM from across the network.  If you exhaust RAM and swap, Linux will run the OOM handler and kill mysqld.

Suggested fix:
The federated handler should spool the remote table to a local temporary file instead of into VM.  Then, if it exhausts physical disk, it could gracefully abort the query and clean up the temporary table.

This is much better than causing an OOM condition on the host and corrupting MyISAM tables if the kernel force-kills mysqld.
[17 Dec 2007 14:09] Susanne Ebrecht
The crash occurs because of too less RAM.

Do I understand this right?
[17 Dec 2007 17:20] Michael Bacarella
Yes.

If you have tables that are hundreds of gigabytes in size, you must have hundreds of gigabytes of RAM (or swap space) if you want to use federated tables.
[4 Apr 2012 15:45] K Vargo
Yes, we're seeing this too... 5.1.62.  I can't tell, but it appears as though this isn't being worked on?
I agree that the FEDERATED engine should handle the FEDERATED content in a more sane manner than filling up all available memory.