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.

A simple script

As I discussed in the last post there is a virtual requirement for scripting ability in a large scale environment. The following is a simple script that could be used deploy scripts to pool of server.

--------------------------------------------------

#!/bin/bash

script=path/to/script/file.sql

for i in {1..100}
do
  (
  for dc in 'chi' 'la' 'sf'
  do
    for db in `mysql -u dba -h mysql${i}-${dc} -e "SHOW DATABASES"`
 do
      if [[ -z `mysql -u dba -s -rB -h  mysql${i}-${dc} $db  < $script` >> output/mysql.success ]]
      then echo p-cs${i}-${dc} $db >> output/mysql.broken;
      fi
    done;
  done;
  )&
done;

This isn't a tutorial on bash programming, but I want to point out a couple of things here that are relevant.

1) A reasonable naming scheme for your servers is important. Don't call them Gandolf, Bilbo and Frodo as cool as those names are. Once your pool of servers grows past six this just doesn't scale and much it more difficult to programmatically access in a loop. There are numerous naming strategies available but you should probably at least include what server type it is (dev, testing or production) and what data center it is located in. EVEN if you are only in one data center right now there is always the chance for growth down the road and redundancy (ie multiple data centers) is important.

2) "Parallelize" if possible. In this case I forked off the loop so that the script is executed against each server at the same time instead of serially. The & sign is used to do this in a bash script.

3) This may be obvious, but you should always write some type of output to a file. Log errors AND successes.

There is much more but I don't want to make this post to long. Start solving your small problems now programmatically so that you gain experience and build a bag of "tricks" to use down the road.

What's just as important as backups?

In a discussion today about backup strategy it was brought up that restorations of the backups are not made unless there is an emergency.

Huh.

So what happens when you have an emergency and find out that the backup wasn't made properly? Maybe a filesystem was corrupted. Maybe the wrong option was used for the backup tool. Who knows.

It is critical that you perform periodic, planned restorations of your backups. My recommendation is that you script a weekly restore of a full backup and AT LEAST quarterly you manually take one of your backups and perform a restore and then verify that it is working properly.

You will thank me later.

km