Mysql – open files limit

First you need to do is run this query:

SELECT user,host FROM mysql.user
WHERE super_priv='Y' AND
CONCAT(user,'@',host) <> 'root@localhost';

This will list all users that have SUPER privilege. Most users that do application-related DB processing do not require this privilege. According to the MySQL Documentation, those with SUPER privilege can do the following:

• Run CHANGE MASTER TO for controlling replication coordinates
• KILL or mysqladmin kill to kill threads belonging to other accounts
• PURGE BINARY LOGS to systemically delete binary logs
• Make configuration changes using SET GLOBAL to modify global system variables
• mysqladmin debug command
• enabling or disabling logging
• performing updates even if the *read_only* system variable is enabled
• starting and stopping replication on slave servers
• specification of any account in the DEFINER attribute of stored programs and views
• HERE IS THE MOST IMPORTANT ONE FOR YOUR PROBLEM: : Enables you to connect (once) even if the connection limit controlled by the max_connections system variable is reached.

You will need to login as root@localhost and revoke SUPER privilege as follows:

UPDATE mysql.user SET super_priv='N'
WHERE super_priv='Y' AND
CONCAT(user,'@',host) <> 'root@localhost';
FLUSH PRIVILEGES;

Once you do this, whenever all users flood mysql connections, only root@localhost can login. After all, if everybody and his grandmother had SUPER privilege, this would bar root@localhost from ever connecting ahead of everybody else. If max_connections is at 200 and you need to raise it to 300 without having to restart mysqld, you can dynamically increase the max_connections with this command:

mysql> SET GLOBAL max_connections = 300;

That will allow more connections effective immediately, but don't just arbitrarily increase the number on whim. You have to make sure mysql has enough RAM to accommodate the increase.

CAVEAT : If you change max_connections dynamically to 300, please put it in /etc/my.cnf

[mysqld]
max_connections=300

You can run mysqltuner.pl on your MySQL DB Server. If you do not have it, then run the following:

cd
wget mysqltuner.pl
perl mysqltuner.pl

The 3rd line under Performance Metrics has this

-------- Performance Metrics -------------------------------------------------
[--] Up for: 8d 20h 46m 22s (8M q [10.711 qps], 129K conn, TX: 90B, RX: 19B)
[--] Reads / Writes: 4% / 96%
[--] Total buffers: 2.1G global + 5.4M per thread (2000 max threads)
[OK] Maximum possible memory usage: 12.6G (80% of installed RAM)

See the 5.4M per thread? That is multipled by max_connections. In this example, that would be a maximum of about 10.8G of RAM. Therefore, each time you bump up max_connections, you should run mysqltuner.pl and check if you are pressing the OS for too much memory.

In any case, limiting who has SUPER privileges give such users opportunity to mitigate flooding mysqld with DB Connections.

You didn't say, in your question what the "file not present" message was, but I'm guessing it's this:

ERROR 1126 (HY000) at line 29: Can't open shared library 'lib_mysqludf_sys.so' 
(errno: 0 /usr/local/mysql/lib/plugin/lib_mysqludf_sys.so: cannot open shared 
object file: No such file or directory)
ERROR: unable to install the UDF

That makes sense, because you're telling gcc to write the file to /usr/lib/lib_mysqludf_sys.so... that's the -o option in your command line. Give gcc the path MySQL is expecting and the rest of the installation should work.

gcc -m64 -fPIC -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c \ 
-o /usr/local/mysql/lib/plugin/lib_mysqludf_sys.so \
-L/usr/lib/x86_64-linux-gnu/libstdc++.so.6

Also, whatever you're planning to do with this... don't say I didn't recommend against it. You're introducing both a potential security vulnerability and potentional performance and stability liability if these tools are deployed. This is not because there's anything wrong with the utility, but because of the amount of somewhat unorthodox functionality it opens up.

It's pretty cool, I admit. The lib_mysqludf_sys group of user-defined functions allow some interesting but easily-misappropriated capabilities, letting you spawn system commands and get either their generated output with sys_eval() or their return value with sys_exec() but really, I tend to suspect there's a reason why MySQL doesn't have these capabilities built in.

mysql> select sys_eval('df -k | grep xvda | tr -d "\n"') as cool_function_but_bad_idea;
+-----------------------------------------------------+
| cool_function_but_bad_idea                          |
+-----------------------------------------------------+
| /dev/xvda1       8256952   1216636   6620888  16% / |
+-----------------------------------------------------+
1 row in set (0.81 sec)

mysql> select sys_exec('/bin/false'), sys_exec('/bin/true');
+------------------------+-----------------------+
| sys_exec('/bin/false') | sys_exec('/bin/true') |
+------------------------+-----------------------+
|                    256 |                     0 |
+------------------------+-----------------------+
1 row in set (1.59 sec)

Update to address this error message:

gcc -Wall -m64 -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o 
/usr/lib/lib_mysqludf_sys.so -fPIC gcc: error: lib_mysqludf_sys.c: No such 
file or directory gcc: fatal error: no input files compilation terminated

The statement in question is directing "gcc" to compile a 64-bit version from the source code, which is found in lib_mysqludf_sys.c. This is one of the files you downloaded, so, to compile it, you need to be inside the directory where you downloaded the lib_mysqludf_sys files. This error suggests that you aren't. The download package isn't limited to 32 bit, it's just that it only builds a 32 bit version unless you use the gcc -m64 -fPIC ... statement.