I've been doing some mySQL optimisation of late, and after a bit of reading around decided to up the table cache (the number of tables mySQL holds open at any one time) from its current value of 512 up to 768 to allow for a few more tables.
This was how things were in /etc/my.cnf :
table_cache=512
so I changed it using
mysql> SET GLOBAL table_cache=768
job done. Or so I thought. Trying to login to SPIDER this morning was taking an age. ssh to server, all seemed ok, but mysql was barely responding.
root> service mysqld restart
all seemed to be ok again. Checking /var/log/mysqld.log showed a number of lines similar to this:
4:06:08 [ERROR] Error in accept: Too many open files
So upping the table cache was causing too many open files... Google the error, seems that open_file_limit is a factor. However, none of the reading about table_cache I had done mentioned that this could cause a problem if the open_file_limit variable was not also increased to account for this. It seems that open_file_limit needs tweaking.
mysql> show GLOBAL VARIABLES LIKE "open%";
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| open_files_limit | 1134 |
+-------------------+----------+
1134 > 768, so what gives? It turns out that each open table uses 2 files:
1134 !> (768*2) => contention in mysqld = slow to crawl.
Ok, easy enough to fix, just increase the open_files_limit:
mysql> SET GLOBAL open_files_limit=2048;
ERROR 1193 (HY000): Unknown system variable 'open_files_limit'
hmm, not what we wanted to see. Off to the web, see what the MySQL docs say about this:
--open-files-limit and ulimit can increase the number of file descriptors, but only up to the limit imposed by the operating system.
ulimit? new to me. Google to the rescue. ulimit is used to "limit the use of system-wide resources".
> ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 71680
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 10240
cpu time (seconds, -t) unlimited
max user processes (-u) 1024
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
Aha, seems there is a system limit of 1024 for open files. Need to change that.
> ulimit -n 2048
> ulimit -a
open files 2048
looks good, except setting values via ulimit this way only works for the current shell, no use... you need to change
/etc/security/limits.conf
in order for the changes to 'stick':
mysql soft nofile 2048
mysql hard nofile 2048
nofile = open file it seems
> service mysqld restart
mysql> show GLOBAL VARIABLES LIKE "open%";
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| open_files_limit | 2048 |
+-------------------+----------+
success. Now I should be able to up the limits to get the table_cache value I want. Set the open files to 2048 on the mirror, intend to put it up to 4096 on the live system so there is a bit more room for growth.
| 
[16:22] This Week's Tweaks