GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

table sizing

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • table sizing

    Hi,
    I have an innodb table with 3 columns, int(11), bigint(20) and decimal(24,5). Based on what I see in the docs each row should account for 24 bytes.

    Currently this table has 25923814 rows in it (generated from select count(*) from table), therefore I would think that the amount of data should add up to @ 600MB (24*numrows).

    However, the data length that I get from information_schema.tables is 2718744576 (roughly 2.5GB). This table was analyzed very recently as well.

    Could someone point me to a doc or explain what is going on here? The table is very static, its lifetime consists of a bunch of inserts, truncation then more inserts and truncation.

    Any ideas on how I can create a sizing calculation that is more accurate?

    The table is using utf8, but I would not think that has anything to do with the sizing. Haven't tested that theory yet, but I would think that it wouldn't matter what char set I use since this table only consists of numbers.

    I am running 64 bit mysql 5.5.8 on Solaris 10. Should I be using Percona server instead?

    thanks.

  • #2
    Yes, but you also have to have some meta data for each record for internal house holding chores.

    And for InnoDB that meta data is quite large for very small rows like yours. In your case the meta data would be something like 6+6+6+7 = 43 bytes.
    Which gives you a row size about: 43 + your data 24 bytes/row = ~70 bytes/row
    And then you have more space occupied by checksum and meta data for each 16k page InnoDB stores data in and the size of the b-tree made up of the primary key at which leaves InnoDB stores the data etc.
    So yes the physical size of an InnoDB table is quite large.

    If you are running MyISAM on the other hand the overhead per row is very low with just a few bytes at which point your calculation would be more accurate.
    But then you don't have the transaction support, or the robustness that InnoDB provides which is what you are paying for with increased size.

    A very simple rule of thumb for storage need is that InnoDB takes about 2.5-3 times the size of a MyISAM table.

    A good place to read about the InnoDB table format is here:
    http://forge.mysql.com/wiki/MySQL_Internals_InnoDB
    But it's not easy reading.

    Comment


    • #3
      Thanks, this is exactly the type of information I wanted.

      I've been experimenting and reading up a bit more in the mysql docs about this.

      I found that since the primary key is a composite key on the bigint(20) and the int(11) that it is increasing the size of the table quite a bit due to the additional book-keeping. I've also found that it increases the sizing of the index as well.

      Since I don't need the uniqueness and the I/O clustering that primary key provides I just removed it. As a result I've actually found a space savings of about 50%. And as a welcomed side-effect the selects are running quite a bit faster.

      So before the table was constructed like this:

      column1 int(11),
      column2 bigint(20),
      column3 decimal(24,5),
      primary key(column2, column1),
      key (column1)

      after experimenting, I've found this table layout to be much more efficient:

      column1 int(11),
      column2 bigint(20),
      column3 decimal(24,5),
      key (column1),
      key (column2)

      Comment


      • #4
        Aah, yes the too-large-primary-key bump, a not so uncommon problem.

        At the same time, do you really need InnoDB for this table?
        A bunch of inserts, then truncation, a bunch of inserts, truncation, etc sounds like a log table which is when MyISAM table is actually very good.

        MyISAM even has an optimization for an optimized table (if you don't delete or update it will always be optimized) that makes it circumvent the normal MyISAM table level locking since it only writes to the end of the table every time.

        Comment


        • #5
          Interesting point. I think along time ago we did try MyISAM but at that stage in the app these tables needed to be updated. I have overhauled the mechanism over the years so that it only inserts and truncates now.

          Maybe you are on to something. I usually dismiss MyISAM as unusable, but I could be way off base. I'll probably try it out.

          thanks for the advice!

          Comment


          • #6
            Would MariaDB be a better alternative at this point to MyISAM?

            Comment

            Working...
            X