MySQL Bugs: #33250: SELECT * FROM really_big_federated_table eats lots of virtual memory (OOM)

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

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.

The crash occurs because of too less RAM.

Do I understand this right?

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.

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.