Maximum Open Files

Recently I was discussing with some colleagues the possibility of consolidating some MySQL servers. While the servers are not heavily loading (averaging less than 1,000 queries a second) they are pretty large in terms of storage requirements. Each server has roughly 200 databases on each with approximatley 50 tables. Thats 10,000 tables per server.  Each server contains up to 1 terabyte of data so if you consolidated servers at a 10:1 ratio you would have 10 terabytes of data, 2,000 databases and 100,000 tables with 10,000 queries per second average load.

Alright, that's a lot. And without testing I don't know if it would work. It probably wouldn't.  But it might. And if it did, it would save the company a significant amount of money.  But, while discussing this,  someone brought up that open files limit might be a problem. Open files limit is the maximum number of files the operating system or the MySQL server can have open at any time. I'm sure other apps have the same setting - in fact I'm quite certain Apache does. I'm just most familiar with MySQL.  After figuring out the rough numbers above I looked up the open file limits for RedHat 5 and it was just a bit over a million maximum. That's good. Then I look up MySQL's open_files_limit variable. I thought the maximum amount might be a problem but couldn't remember what it was. I (re)discovered the maximum number of open files for MySQL (any 5.X version) is 65,535. Hmmmmm......so it's less than 1/10th of the operating system maximum. Well under the number of tables we would have in this configuration. So we won't even bother testing it.

So I can buy four terabyte enterprise-grade drives (http://news.cnet.com/8301-17938_105-57521020-1/wd-ships-4tb-enterprise-grade-hard-drive/), stick six of them in a server and have 12 terabytes of RAID 10 space but I am limited to 65k open files by MySQL? I can buy a server from Dell that has a ridiculous number of cpus/cores and supports 128 - 768 GB of RAM. And yet I can't consolidate ten servers that have one CPU and 8 gigs of RAM down to one server because of a very low limit on open_files_limit? Please don't arbitrarily limit what I can do with my server. While you can't have as many open files in the application as you can in OS  I see no reason for a dedicated MySQL server that the maximum for this shouldn't be 650,000 instead of 65k. 

Am I the only one who thinks this is strange? Maybe I'm out in left field on this..but I doubt I'm the only person who has ever experienced this problem.

Comments

[Ammon]

It seems like you really want multiple mysql instances here. I have had good success putting a large number of instances, each with many thousands of tables and a high (8000-10000) open files limit. You hit major scaling issues on the CPU side anyway if you try for one instance. Besides, MySQL doesn't really "like" having that high a number of open tables (yes, I know, .frm files, indexes, logs, etc, etc to add in) so it's unlikely that the number of files you can efficiently make use of inside MySQl's internal data structures can even approach the 65535 limit without severe performance degradation.

[winmutt]

I believe the documentation is incorrect. I know for a fact that we have surpassed that as I had to raise the limit from 150k to over 300k.

[Keith Murphy]

Ammon,

You are probably correct that it wouldn't work (or at least work ideally). And using mulitple instances is probably the way to go. It's just really? Why is this an issue? And hopefully they would be working on resolving the issue of degradation as your open file count goes up.

Winmutt,

Wouldn't suprise me one bit. Either way its a bug in my book :)