3

I'm running MySQL (5.0 but I don't think it matters for anything after 4.1) and I've seen advice about using the innodb_file_per_table configuration option for InnoDB tables. This is usually done so you can better control how much disk space is used by InnoDB tables since that space isn't ever recovered even if that table is later dropped.

But every time I see that piece of advice is says to use it if you don't have a lot of tables (here's an example). So my question is, how many tables is too many?

I have an application that is running for lots of different clients (about 50, but likely to grow closer to 100) and each database has 135 tables in it. Is 13,500 tables too many?

mpeters
  • 233
  • 2
  • 11

1 Answers1

4

That is a nice number of tables, but the thing to remember here is that not all of them are going to be open at once - most times you'll set the MySQL parameter for open files to around 300-500, and MySQL itself will keep the ones most active on your system in the memory table pool (cache, if you will) much like how the kernel keeps memory pages for applications around.

I personally am a firm believer in the file per table as a systems management tool; while it does have it's tradeoffs as you rightly bring up, the ability to keep each client's tables distinct (you could even spread them out to different disks - RAID, SAN, DAS, etc. - by customer database!), back the raw ones up by hand if needed, optimize them if needed and so forth goes a lot longer at the end of the day. The main price you pay is more fsync() calls during writes which can degrade performance if you have a lot of tables being written too, but in general it's not too bad.