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 18:17	Modified:	27 Mar 2008 7:13
Reporter:	Michael Bacarella
Status:	In progress
Category:	Server: Federated	Severity:	S2 (Serious)
Version:	mysql-5.1.22-rc	OS:	Linux
Assigned to:	Ramil Kalimullin	Target Version:
Tags:	federated, OOM, crash, 5.1, swap
Triage:	Triaged: D1 (Critical)

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.