Side effect of large number of MySQL tables in a database

Is it OK to keep 10000+ tables in a MySQL database? I'm making a messaging/chat script, so I'm thinking about partitioning data's over several tables as it will be a huge amount of data after some days. IS IT OK? Or it has some effect? Well, as a table can hold millions of rows so I was thinking maybe a database can hold large number of tables too

or, the question could be like, how does Facebook stores their huge amount of daily chat messages?

I'm a newbie in MySQL, please help

Answers


MySQL has no limit on the number of tables. The underlying file system may have a limit on the number of files that represent tables. Individual storage engines may impose engine-specific constraints. InnoDB permits up to 4 billion tables.

Even so, the typical DBMS will 'handle' such large databases, but there is more strain on the system catalog than usual in such systems.

I have about huge tables in one database with no ill effects, other than displaying the table list in phpMyAdmin taking a while


It's possible, but I would avoid it unless you have a really good use case for it. It raises all kinds of scalability and maintainability issues. Your table size is mainly limited by available disk space.

If you really need to do it...

You'll need to increase the maximum number of file descriptors that your OS will allow to have open, since MyISAM tables use two file descriptors per table. (If you're using Linux then read the section about ulimit in the man page for bash for how to do this).

Also, there's a MySQL config value called table_cache that limits the number of allowed tables. You'll need to make sure that's large enough to support the number of tables you need.

You won't want to use the standard "flush tables" anymore (unless you're the kind of person that likes to watch paint dry) so you'll need to flush each table individually (e.g. before shutdown).

Again, I would avoid using so many tables. You're probably better off making your schema support what you need in a handful of tables, and consider archiving, warehousing (or deleting!) old data if you're concerned about storing too much data.


Need Your Help

armv7s executable size increase

objective-c ios xcode armv7

With XCode4.5 and armv7+armv7s arch, the executable size increased from 3MB to 7MB. Rebuild with armv7 only, the binary size is back to 3MB.

Logical Error in a program that selects a letter based on given probabilities

c++ algorithm c++11 code-review probability

I am writing a program where I have 6 items called A, B, C, D, E and F. These are stored in one array. Another array stores the respective probability of each item.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.