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.