MySQL Bugs: #25641: Too much memory used during 'alter table add primary key' with Falcon

Bug #25641	Too much memory used during 'alter table add primary key' with Falcon
Submitted:	16 Jan 2007 2:53	Modified:	28 Apr 2009 4:37
Reporter:	Mark Callaghan	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	5.1.14 Falcon	OS:	Linux (Linux 2.4)
Assigned to:		CPU Architecture:	Any
Tags:	F_MEMORY

Description:
I created a table with engine=Falcon, inserted 10M rows and than created a primary key index. The VM size of mysqld grew from 1.8G to 3.6G while creating the primary key index.

I think this is a known problem because Falcon buffers index changes in memory until commit.

How to repeat:
DDL for the table:
CREATE TABLE C (
  ChId bigint(20) NOT NULL default '0',
  Timestamp datetime NOT NULL default '0000-00-00 00:00:00',
  CuId int(11) NOT NULL default '0',
  CaId int(11) NOT NULL default '0',
  AId int(11) NOT NULL default '0',
  UId int(11) NOT NULL default '0',
  IpAddress varchar(15) NOT NULL default ''
) engine=Falcon;

Data was inserted into the table in primary key order, so the inserts into the index would have been in index order. 

Non-default config variables:
[mysqld]
innodb_flush_log_at_trx_commit = 2
set-variable   = innodb_log_files_in_group=3
set-variable   = innodb_log_buffer_size=10M
set-variable   = innodb_additional_mem_pool_size=50M
set-variable   = innodb_file_io_threads=4
set-variable   = innodb_lock_wait_timeout=50
innodb_data_file_path = innodb_data1:16M:autoextend
set-variable   = innodb_buffer_pool_size=100M
set-variable   = innodb_log_file_size=200M
falcon_max_record_memory=400M
falcon_page_cache_size=1000M

Thank you for the bug report.

Currently all changes to a Falcon table require creating a new table
with the desired change, copying all the data from the old table to
the new one, dropping the old table, and renaming the new one to the
old name.  Since Falcon does not release space back to the file system,
the database file grows.  The released space is available for storing
new records, etc.

We expect to change to on-line add/drop index before beta.

I said "file" but the same comment applies to virtual memory.

Falcon stores all tables and index in a single database file.  When you add the index, the MySQL server creates a new table, copies the row data from the original to the new table, deletes the old table, and renames the new table to the original name.  At one point during this process, both the new and old tables exist in their entirety, doubling the apparent space used.  The extra space, however, is free inside Falcon and available for reuse.

There are two things we need to do to address this issue.  One is to support online "add index" so a table rebuild is unnecessary.  This work is underway.  The other is to provide a mechanism to monitor space using inside Falcon, including the number of free pages.  The space analysis code has already be implemented inside Falcon, but we're still looking for the proper way to expose this through the MySQL server.

Jim and I think alike, I guess.  He too read the report about excessive
memory usage and responded to the increase in database size.  The memory
problem is much the same.  To do the current copy + create index, we
read the original table into memory and create the copy in memory, then
create the index in memory, then flush the new table plus the index to
the log file (also in memory), then move the contents of the log to 
the database.  That uses a lot of memory, all of which will be scavenged
and reused or released, but ...  doing an on-line index create will
help the memory use as well.

Mark,

having ONLINE ALTER capabilities the VM usage on a Linux 64-bit looks like this:

-- After loading 10 mio rows
VM size: 2221MB

-- After creating PRIMARY KEY
VM size: 2661MB

I have used your table DDL and your Python script to generate the 10 mio rows.

I think the memory usage looks a lot better now. What do you think?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Setting this bug to Can't Repeat since it was effectively fixed by the addition of Online Alter