1

I have a plain text file with 3 million articles divided with \n. The file is 58G

When I'm adding the articles to an InnoDB table one per row

  • INT id
  • TEXT article_content

the .ibd file of this table (innodb_file_per_table=1) takes up 98G

Can someone explain if such increase in disk space of a table comparing to a text file is normal ? Is there a way to store such content more efficiently ?

0

If the table is that simple (INT and TEXT), you cannot change the design any more.

Suppose your table is called mydb.myarticles. If you run this TableSize Query,

SELECT
    FORMAT(data_length,2)  / POWER(1024,1) DataPagesKB,
    FORMAT(index_length,2) / POWER(1024,1) IndexPagesKB,
    FORMAT(data_length,2)  / POWER(1024,2) DataPagesMB,
    FORMAT(index_length,2) / POWER(1024,2) IndexPagesMB,
    FORMAT(data_length,2)  / POWER(1024,3) DataPagesGB,
    FORMAT(index_length,2) / POWER(1024,3) IndexPagesGB,
    (data_length+index_length) DataAndIndexesInBytes
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='myarticles'\G

You will see how much space the data pages are taking up. If you feel DataPagesGB is significantly lower than 98GB, then run:

OPTIMIZE TABLE mydb.myarticles;

which will actually execute

ALTER TABLE mydb.myarticles ENGINE=InnoDB;
ANALYZE TABLE mydb.myarticles;

This will shrink the table as much as possible. Then, run the TableSize Query again. Chances are, you may not get much shrinkage.

Keep this in mind: The default size of an InnoDB Page is 16KB (InnoDB_page_size). Any TEXT fields over 16KB will cause InnoDB to split the row up into two or more pages. In other words

  • Text field > 32 KB may need up to 3 pages for the one row
  • Text field > 48 KB may need up to 4 pages for the one row
  • ...
  • Text field > 16n KB may need up to (n+1) pages for the one row

At least one of those pages will have lots of wasted space.

CAVEAT

I would strongly advise against changing the InnoDB_page_size for compression purposes because I wrote a post back on Mar 02, 2012 on how InnoDB creates additional wasted space in the Buffer Pool changing to the Barracuda Format, requiring a much larger Buffer Pool. This would be in addition to the chaining of the increased number of InnoDB pages.

Your Original Question

Can someone explain if such increase in disk space of a table comparing to a text file is normal ? Is there a way to store such content more efficiently ?

Yes, it is normal for such an increase especially if many rows have TEXT values bigger than 16KB. If you add any additional indexes (such a FULLTEXT index or a prefix index), you will bloat the Index Pages.

You can estimate the bookkeeping info by subtracting DataAndIndexesInBytes from the size of the .ibd and dividing that difference by 3 million. That answer will give you the bookkeeping info needed per row.

SUGGESTIONS

  • Use a different character set and collation (One byte per character)
  • Limit the characters of the TEXT fields to the most commonly used characters
  • Avoid using multibyte characters
  • Use TEXT field lengths less than 16KB
| improve this answer | |
  • Thanks, great answer. Can you suggest using another DBMS with purpose of disk space economy? The only thing I need from DBMS is to query the articles by id. And store data efficiently in terms of disk space. – Viacheslav May 25 '14 at 13:03
  • 1
    Great answer! Few remarks though. InnoDB requires a record to be less than UNIV_PAGE_SIZE/2-200 bytes (~7kb), so it will store TEXT in external pages earlier before it gets as large as 16kb. Latin chars in UTF8 column still use one byte. – akuzminsky May 26 '14 at 17:17
  • @akuzminsky Thank you for looking over my shoulder. The max row length is 8126 bytes. The breaking up of the text into external pages must be similar to the TOAST tables in PostgreSQL. (For those who don't know, TOAST stands for The Outside Attribute Storage Technique). – RolandoMySQLDBA May 26 '14 at 18:09
0

Imagine that you have a stack of papers 10000 high. You have the choice of:

  • Leaving them in one tall pile (at the cost of having a really hard time finding the exact one you want in an instant).
  • Categorizing them into some order, and placing them into folders, and then again into filing cabinets.

The simple answer is that there is usually some overhead to be able to do book-keeping and there is padding to be able to have fixed offset locations to be able to jump ahead when searching for data.

If you are concerned with the size, maybe try using InnoDB page compression, or compress the blobs in your application before storing them? You'll probably find the TEXT column compresses very well.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.