MySQL Forums
Forum List  »  InnoDB

Re: Cannot add column to partitioned table
Posted by: Rick James
Date: August 01, 2015 05:21PM

How much RAM do you have? Hopefully 4GB, otherwise your 1GB innodb_buffer_pool_size is too big.

Rule of Thumb: Don't PARTITION unless you anticipate more than a million rows in the table.

Rule of Thumb: Don't use more than about 60 PARTITIONs in a table.

If you do SHOW TABLE STATUS, you will see that Data_free is rather large. This is because there is "free" space in each partition -- probably more than 50% for your "small" partitions.

I have rarely found a need for partitioning summary tables.

More advice:
http://mysql.rjweb.org/doc.php/partitionmaint

A similar forum entry:
http://forums.mysql.com/read.php?115,608949,608949

How much disk do you have free? How big are the tables?

SHOW VARIABLES LIKE '%open%';

Bottom line:

1. Check the buffer_pool

2.
Plan A: remove partitioning.
Plan B: IF you will be purging "old" data, keep partitioning, but fewer partitions.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Cannot add column to partitioned table
1237
August 01, 2015 05:21PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.