Minimizing database size (Many small tables)

Question

Our database is interesting in that we have a large amount of tables that take up a correspondingly large amount of space, but when we do a MySQL dump of said database, it is surprisingly small.

Specifically, the database is about 50GB, and contains about 50k tables. When dumped, it takes up about 5GB. (These numbers aren't spot on, but they're close enough for our discussion here). It's particularly odd to me that the "binary" DB takes up so much more space than the SQL-dumped DB.

The vast majority of the tables (like 49990) are relatively small Wordpress Multisite tables. These are seldom-used sites with very little content on them.

If it's relevant, we're using innodb_file_per_table.

What is the best way to minimize the size of our database? Does minimizing the size of the database come at a cost of lower performance? Ultimately, I'd like to reduce the size of the DB in order to increase performance (e.g. with regard to doing backup/restore operations).

---

Update: The structure of the tables is essentially just the default Wordpress Multisite layout: http://pastie.org/private/iufzw8z9zlyidqw8b7wggw Note that I looked into some more accurate numbers, and it appears we have about 9k Multisite instances for a total of nearly 80k tables. The larger numbers are partially due to our service continuing to grow and add new customers.

Answer 1

"Does minimizing the size of the database come at a cost of lower performance?"

Typically databases are IO limited, unless they are regularly recalculating reporting style queries. (in which case pre-calculated views can be added to again shift cpu to IO.)

Minimizing the size of the database typically minimizes the IO needed against the disk, as a higher % of all data can be in cache.

Compressed storage can also help here; if 1 disk iop can retrieve more rows, then that too can increase performance, without needing to logically re-arrange the data. (many compression schemes are cpu-efficient enough that it is possible to actually see a decrease in cpu usage, due to having to handle less data pages.)

So, in general, minimizing the size of the database does indeed increase performance, but always benchmark as there are many counter-examples to any generic statements like this one (note, the answer by RolandoMySQLDBA lists some downsides of compression concerning memory pressure).

some of the tradeoffs due to data compression are listed at http://dev.mysql.com/doc/refman/5.5/en/innodb-compression-internals.html#innodb-compression-internals-storage

Answer 2

ASPECT #1 : Use of BIGINT

BIGINT takes up 8 bytes. You should change the entire schema to use INT UNSIGNED

To verify this, let's pick one table : wp_1234_term_taxonomy

Run this query

SELECT term_taxonomy_id,term_id,parent FROM wp_1234_term_taxonomy PROCEDURE ANALYSE();

This will not spill out all the rows. What PROCEDURE ANALYSE() does is scan the data and recommend the appropriate type for each column along with the min value, max value, and so forth.

Smaller INT columns will definitely increase read and write performance.

I have suggested using PROCEDURE ANALYSE() many times

• Nov 13, 2014 : Should I use data type SERIAL for table id columns?
• Aug 27, 2014 : Why are simple SELECTs on InnoDB 100x slower than on MyISAM?
• Jun 13, 2012 : Change in datatype?
• Dec 10, 2011 : Do I have duplicate key indexes?
• Aug 12, 2011 : Which DBMS is good for super-fast reads and a simple data structure?
• Jun 07, 2011 : How should I Optimize storage for this table?
• May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
• Mar 25, 2011 : Performance implications of MySQL VARCHAR sizes

ASPECT #2 : Redundant Indexes

You would be surprised how many indexes are created with repeated column patterns in WordPress, Drupal, Magento, and products like these.

Please download Percona Toolkit. Then, use pt-duplicate-key-checker. The output will tell you what indexes can be dropped and still maintain all your search needs. Tables have to load much faster with less indexes to populate and manage. Trust me, I have gained nice results doing this for my in-house Magento clients in terms of reducing database size and maintain searchability.

ASCECT #3 : InnoDB Buffer Pool

Here is a Pictorial Representation of InnoDB (from Percona CTO Vadim Tkachenko)

Note the InnoDB Buffer Pool in the upper left corner

Most people don't realize that up to 25% of Buffer Pool (called the Insert Buffer) is dedicated to handling changes to non-unique indexes. Those get written to the Insert Buffer within the system tablespace (ibdata1). Since available memory within the InnoDB Buffer Pool is at a premium, smaller INTs will allow more data and index pages to fit into the Buffer Pool.

ASPECT #4 : Data Compression

Some have benefited from compressing data for the sake of storage by using the Barracuda storage format, but there can be a performance cost if you do not have enough RAM. Why ?

Back on Mar 02, 2012, I wrote my answer to innodb_file_format Barracuda. In detail, I explained the following: When a compress page is accessed, InnoDB Buffer interacts will decompressed the compressed page. This bloats the Buffer Pool. So, if you cannot significantly increase innodb_buffer_pool_size to accommodate compressed and uncompressed pages, then using Barracuda is not for you.

Even if you had enough RAM, performance can still take a small hit if there are lots of pages to decompress in addition to the normal LRU pruning of old pages.

Short Answer : DON'T DO IT !!!