6

We have a mysql table called posts_content.

The structure is as follows :

CREATE TABLE IF NOT EXISTS `posts_content` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `forum_id` int(11) NOT NULL,
  `content` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=79850 ;

The problem is that the table is getting pretty huge. Many giga-bytes of data ( we have a crawling engine ).

We keep inserting data into the table on a daily bases but seldom do we retrieve the data. Now as the table is getting pretty huge its getting difficult to handle the table.

We discussed two possibilities

  1. Use MySQL's partitioning feature to partition the table using the forum_id ( there are about 50 forum_ids so there would be about 50 partitions. Note that even each partition if made so will eventually grow to again many giga-bytes of data maybe even eventually need its own drive
  2. Create separate tables for each forum_id and split the data like that.

I hope I have clearly explained the problem. WHat I need to know is which of the above two would be a better solution in the long run. What are the adv. dis adv. of both the cases.

Thanking you

Improve this question
2
  • I got the question, but the solutions you have presented, look the same to me, except that in one MySQL is providing the solution and in case 2 it seems you will be building your own. Have I got it right? Commented Jun 27, 2011 at 11:03
  • Ozair Kafray - there are differences, some I know. For example when you use partitioning you query the table the same way you used to before, to the end user it still is one table, although its separated into many files. As the the other way solution if you need to query the data you would need to bring back all tables together first with joins, or temporary tables or something like that. With any feature you never know...the link posted by Darhazer is interesting Commented Jun 28, 2011 at 5:42

4 Answers 4

Reset to default
4

The difference is that in the first case you leave MySQL to do the sharding, and in the second case you are doing it on your own. MySQL won't scan any shards that do not contain the data, however if you have a query WHERE forum_id IN(...) it may need to scan several shards. As far as I remember, in that case the operation is syncronous, e.g. MySQL queries one partition at a time, and you may want to implement it asyncronously. Generally, if you do the partitioning on your own, you are more flexible, but for simple partitioning, based on the forum_id, if you query only 1 forum_id at a time, MySQL partitioning is OK.

My advice is to read the MySQL documentation on partitioning, especially the restrictions and limitations section, and then decide.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

ok this might be a dumb one. I read that mysql partitioning does not support foreign keys. Does that mean I cannot partition based on the forum_id in the above table? If not then how do I partition based on the forum_id? I cannot understand the key, hash etc. Also documentation say that you cannot use ranges with Sub-partitions. Does that mean I will not be able to subpartition my data? Or can I subpartition on post_id using hash?
The documentation says that you cannot use foreign key constraints. This is because MySQL will need to search every partition for matching value, in order to ensure that the value exists. If you partition the table on your own, you won't be able to use foreign keys, referencing the partitioned table as well, but will be able to use foreign key in the table itself. But in sharded application, and in NoSQL at general, the consistency is managed mostly by the application, not by the database server.
FOREIGN KEYs are not a requirement. Just be sure to have suitable index(es) to replace them.
1

Although this is an old post, caveat with regards to partitioning if your engine is still MyISAM. MySQL 8.0 no longer supports partitioning other than Innodb or NDB storage engines only. In that case, you have to convert your MyISAM table to InnoDB or NDB but you need to remove partitioning first before converting it, else it cannot be used afterwards.

Improve this answer

Comments

0

here you have a good answer for your question: https://dba.stackexchange.com/a/24705/15243

Basically, let your system grow and while you get familiarized with partitioning, and when your system really need to be "cropped in pieces", do it with partitioning.

Improve this answer

Comments

0

A quick solution for 3x space shrinkage (and probably a speedup) is to compress the content and put it into a MEDIUMBLOB. Do the compression in the client, not the server; this saves on bandwidth and allows you to distribute the computation among the many client servers you have (or will have).

"Sharding" is separating the data across multiple servers. See MariaDB and Spider. This allows for size growth and possibly performance scaling. If you end up sharding, the forum_id may be the best. But that assumes no forum is too big to fit on one server.

"Partitioning" splits up the data, but only within a single server; it does not appear that there is any advantage for your use case. Partitioning by forum_id will not provide any performance.

Remove the FOREIGN KEYs; debug your application instead.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.