MySQL Forums
Forum List  »  Partitioning

file per table - innodb - high memory usage
Posted by: Markus Schindler
Date: May 05, 2008 02:44PM

All,

I was wondering if anyone else had an similar experiance as I have:

- I have an application (designed before partitioning was available)
- I create a table each day for storing log data for this day (kind of what partitioning could do now for me)
- to keep amount of date equal the oldest Date Table is deleted
- every few minutes new data is inserted in the new created table (from different servers spread in different TimeZones)
- so different date tables are accessed similtanious
- each table has around 500! columns and 4 key columns (Region, City, element id, subpart)
- all columns but the id's are float
- Last two keys can also be NULL (not the best DB design, but the easiest application)
- each daytable is around 4 Million rows
- NOW PARTITIONING CAME AVAILABLE
- wanted to add minor change to partition now also on Region (List 10 regions) and subpartition on Hash (Element ID - 10 subs)
- most queries have either one element only or one region at no time multiple regions

- SPEED GAIN WAS FANTASTIC BY THE WAY, but is not usable becasue of the problem below :(

So far to the environment, now to the actual problem:
- when designing above with file_per_table in innodb
- during night the new Table is created (100 files on file system 10 LIST * 10 Subs)
- table creation takes like 1min and memory usage of mysql process increases by around 900MB
- as it is a dedicated DB server (8GB phy mem), it was designed that mysql process take 90% of physical memory with 1 GB swap enabled
- during first night I did not recognize anything, but after second the server crashed as the SWAP was used completely...

I worked around above by swithcing to myisam tables where it is not happening, and also with archive table it does not happen, but want to use innodb because recovery and concurrency...

Any ideas what could cause the above behaviour? Is it a bug or is it expected?
I was reading the InnoDB dictionary needs 4K per table and innodb cashes like every accesed table without limt, but I think this was before partition, so could be with 100files easily be 400K per table, but not 800-900MB SWAP...

Options: ReplyQuote


Subject
Views
Written By
Posted
file per table - innodb - high memory usage
5568
May 05, 2008 02:44PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.