List:General Discussion« Previous MessageNext Message »
From:John McCaskey Date:January 13 2006 5:20pm
Subject:Huge number of tables with InnoDB
View as plain text  
Hi everyone,

 

I'm running MySQL 4.0.18 on Debian with a 2.6 linux kernel using ext3 as
the underlying filesystem for the database storage.

 

I currently have some InnoDB tables with the following structure:

 

Log_20060101 {

            Monitor_id medium int,

            Timestamp timestamp,

            Avg float,

PRIMARY KEY Monitor_id, Timestamp

}

 

We partition these tables by date as you can see as they grow very large
and they get to be slow to insert and query to over time.  We have the
idea to change the partitioning so the tables are as follows:

 

Log_[monitor_id] {

            Timestamp,

            Avg float,

PRIMARY KEY Timestamp

}

 

This seems to have several key advantages:

 

1)       Reduced disk space usage 

2)       Easier querying of data across time (but not across individual
id's, it turns out that doesn't ever really happen in our data usage
anyway though)

3)       Smaller tables, resulting in faster reads/writes, also smaller
data volume hopefully also resulting in faster reads/writes due to less
disk io neccesary

 

However, in our actual testing the 'faster writes' expectation is
getting shot down.  With 20,000 unique monitor id's and 8928 unique
timestamps inserting to the old set of tables (20051201-20051231 in this
case) is taking me about 4 hours 20 minutes.  Inserting to the 20,000
new tables (Log_0-Log_20000) is taking about 10 hours.  I expected this
to be much faster as I hoped finding the right table for an insert would
be a Hash type lookup taking linear time, while inserting into the large
tree structure in the old tables which have a much higher volume of rows
would be logarithmic time.  Where did I go wrong?  My only real thought
so far is the disk subsystem of the OS being slow with large numbers of
files, but I thought it wouldn't matter for InnoDB as the data storage
is all one file.  Does anyone know what would cause the inserts to be so
much slower?  

 

John A. McCaskey

Software Development Engineer

Klir Technologies, Inc.

johnm@stripped

206.902.2027

 


Thread
Huge number of tables with InnoDBJohn McCaskey13 Jan
  • Re: Huge number of tables with InnoDBAlec.Cawley13 Jan
    • Re: Huge number of tables with InnoDBPeter Brawley13 Jan
RE: Huge number of tables with InnoDBJohn McCaskey13 Jan