mysql overhead with innodb tables

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • ifedi
    New Member
    • Jan 2008
    • 60
    #1

    mysql overhead with innodb tables

    Hey guys,
    I've a mysql database with about 112 tables and views. All of the tables are INNODB. I manage this with phpmyadmin, and stuff have been pretty much fine over the last one and a half years.
    Suddenly I noticed this problem:
    I needed to transfer the installation to another network server (running WinXP); I used the export feature in phpmyadmin to get out the table structure, and recreate them in the new database. This all happend well, and the eventual performance of queries is unhindered. Problem is, as soon as the tables had been created, even without any data, I noticed a rather high 'Overhead' figure (in red color) underneath each table where it says 'Space Usage'. The same figure (something like 5,012kb) was display as 'overhead' under ALL the tables. To confuse me further, on the main database page (showing all the tables in a list and with one of the columns titled 'Overhead'), there's nothing (only a dash) displayed as overhead on any of the table rows!
    I've dropped and recreated everything several times with no success.
    Kindly help out, anyone!
    Regards,
    Ifedi
    Tags: None
    • r035198x
      MVP
      • Sep 2006
      • 13225
      #2
      Does a mysqldump and restore show the same behavior?

        Comment

        • ifedi
          New Member
          • Jan 2008
          • 60
          #3
          Originally posted by r035198x
          Does a mysqldump and restore show the same behavior?
          Yes.
          As a matter of fact, just to investigate, I created a new database 'test2'. Creating a new INNODB table in test2 with a single column resulted in the same output (the same number of bytes of "overhead" as was in each of the other tables in the other databases). When I changed this new table to MyISAM, all seemed well.

          All INNODB settings I are default.

            Comment

            • r035198x
              MVP
              • Sep 2006
              • 13225
              #4
              What happens if you run the OPTIMIZE TABLE query on the table?

                Comment

                • ifedi
                  New Member
                  • Jan 2008
                  • 60
                  #5
                  Originally posted by r035198x
                  What happens if you run the OPTIMIZE TABLE query on the table?
                  I'm not sure if I've quite done that. And right now I'm a couple of miles from my server machine... so I'll do that ASAP.
                  Anyhow, my reservations:
                  Should I really think I'd need to run OPTIMIZE TABLE on each of 112 tables to sort the problem out?
                  Why should a brand new table with with only one column and no data need OPTIMIZE?
                  It seems to me that something at a higher level is wrong with INNODB tables, or perhaps their interaction with the environment.
                  Thanks.

                    Comment

                    • r035198x
                      MVP
                      • Sep 2006
                      • 13225
                      #6
                      Or perhaps even the setup of MySQL on that server itself.
                      OPTIMIZE should fix the issue but not real problem that may exist with your MySQL installation.
                      Perhaps check your system specs against requirements for INNODB or try backing up with the INNODB hot backup program if you have it.

                      r035198x(<----just guessing now

                        Comment

                        Previous template Next
                        Working...