0

I have a table in MYSQL holding the messages in a message system. My table is :

messages(from_id, message, date, hash)

I know that a table can hold a certain number of records. What can I do if my table holding my messages is full? Can I create a new table like that I already have and start moving the next messages to it? Any ideas how can I do this? Thanks

000
  • 26,951
  • 10
  • 71
  • 101
user2491321
  • 673
  • 10
  • 32
  • Define 'full'? Disk out of space? – ethrbunny Jun 19 '13 at 15:39
  • What makes you think your table will get full? It won't, until it exceeds the maximum file size (measured in terrabytes on 64-bit operating systems). – Ken White Jun 19 '13 at 15:40
  • 1
    The MyISAM storage engine supports up to 2^32 rows and InnoDB supports a total table size of up to 64 Terabytes. How many rows do you have that your table is becoming "full"? See: http://stackoverflow.com/questions/2716232/maximum-number-of-records-in-a-mysql-database-table – Bad Wolf Jun 19 '13 at 15:40
  • You can partition your table, and have the partition with newer messages stored on a different drive. – N.B. Jun 19 '13 at 15:40
  • suppose that I have in my system 1.000.000.000 members and they exchange messages every day one with the other. Imagine the amount of messages that will be created and stored every day in table called "messages". Will it be full or not after a period of time? – user2491321 Jun 19 '13 at 15:44

3 Answers3

0

The only way the table you describe can be full is if your disk is full. Why not delete older messages?

Tassos Bassoukos
  • 16,017
  • 2
  • 36
  • 40
0

There's no real size limit on MySQL tables (see documentation). At some point, maybe you can archive old messages.

xlecoustillier
  • 16,183
  • 14
  • 60
  • 85
  • suppose that I have in my system 1.000.000.000 members and they exchange messages every day one with the other. Imagine the amount of messages that will be created and stored every day in table called "messages". Will it be full or not after a large period of time? – user2491321 Jun 19 '13 at 15:49
  • Of course. But you'll have charge and bandwidth problems long before having storage problems. Maybe you can start from the beginning. It took some time to Facebook to reach 1 billion users. Don't worry about that right now. – xlecoustillier Jun 19 '13 at 16:06
  • Facebook doesn't have a billion members, so I don't know why you're starting out with an impossible number. If you had a million, which is much more reasonable, you will need to **shard**. You do not have an option here. – tadman Jun 19 '13 at 16:08
0

What do you mean, full? You've reached the max range supported by from_id? Then you've got two main choices:

  • convert from_id from signed -> unsigned, which will double your supported ID range (0->2x instead of -x -> +x)
  • convert from_id from int -> bigint, which takes you from a 32bit number to 64bit.
Marc B
  • 356,200
  • 43
  • 426
  • 500