mysql - kern.maxfiles limit exceeded by uid 500 please see tuning(7) - MySQL

[none]

Hi, I have a freebsd 4.10 server (x86) and I run a
shell script as a cron job every 15 minutes. This
scripts enter some data in the mysql database.

The server works fine for a few days, but after
5 days it crashes. I only get this error and cannot
do anything else (except rebooting)
kern.maxfiles limit exceeded by uid 500 please see tuning(7)

uid 500 is mysql, so I would like to know why
mysql is causing this error? why does it need
to open that much files? my current kern.maxfiles
is 1064, shouldn't this be enough?

What should I do this fix that?

add sysctl kern.maxfiles=4256 to /etc/rc.local?
or should I recompile the kernel with a new maxfiles
value?

Is there a way to configure mysql to open
less files?

Can my shell script be the culprit? It does something like
this:

if [ "`echo | ${mysqlbin} 2> /dev/stdout`" ]
then
echo "CREATE DATABASE ${mysqlname};" | ${mysqlpath}
--user=${mysqluser} --password=${mysqlpass}
echo "CREATE TABLE Average (Time DATETIME, AverageBar TINYTEXT,
Average FLOAT);" | ${mysqlbin}
echo "CREATE TABLE DBInfo (No TINYINT UNSIGNED, Server TINYTEXT);" |
${mysqlbin}
fi

echo "INSERT INTO Average (Time, AverageBar, Average) VALUES
('${sqltime}', LOAD_FILE('${tmpfile}.win'), '${avgtotal}');" |
${mysqlbin}

Thanks

[Gordon Burditt]

>Hi, I have a freebsd 4.10 server (x86) and I run a

>shell script as a cron job every 15 minutes. This
>scripts enter some data in the mysql database.

>The server works fine for a few days, but after
>5 days it crashes. I only get this error and cannot
>do anything else (except rebooting)
>kern.maxfiles limit exceeded by uid 500 please see tuning(7)

The server is caching too many open files (tables).
To see this, you might try using a program like 'lsof'.
It is possible that mysqld has a file descriptor leak.
Or it is just caching too many tables.

>uid 500 is mysql, so I would like to know why
>mysql is causing this error? why does it need
>to open that much files? my current kern.maxfiles
>is 1064, shouldn't this be enough?

What is the setting on the mysqld variable 'open_files_limit'?
You might want to set this lower (definitely lower than
kern.maxfiles) to prevent it from taking over the system.

>What should I do this fix that?
>
>add sysctl kern.maxfiles=4256 to /etc/rc.local?

One possibility.

>or should I recompile the kernel with a new maxfiles
>value?

>Is there a way to configure mysql to open
>less files?

Yes, I think you can set the variable open_files_limit
lower.

>Can my shell script be the culprit? It does something like
>this:

If your shell script doesn't have a huge number of copies of
itself running at once (due to server locking or just the
queries take a lot of CPU and disk I/O to finish), probably
not.

>
>if [ "`echo | ${mysqlbin} 2> /dev/stdout`" ]
>then
> echo "CREATE DATABASE ${mysqlname};" | ${mysqlpath}
>--user=${mysqluser} --password=${mysqlpass}
> echo "CREATE TABLE Average (Time DATETIME, AverageBar TINYTEXT,
>Average FLOAT);" | ${mysqlbin}
> echo "CREATE TABLE DBInfo (No TINYINT UNSIGNED, Server TINYTEXT);" |
>${mysqlbin}
>fi
>
>echo "INSERT INTO Average (Time, AverageBar, Average) VALUES
>('${sqltime}', LOAD_FILE('${tmpfile}.win'), '${avgtotal}');" |
>${mysqlbin}
>
>Thanks
>

Gordon L. Burditt

[none]

Gordon Burditt wrote:

> What is the setting on the mysqld variable 'open_files_limit'?
> You might want to set this lower (definitely lower than
> kern.maxfiles) to prevent it from taking over the system.

Thanks Gordon, I'm trying to change the open_files_limit of
mysql to see if it works. Right now I have added it to my
rc.local mysql startup string:

/bin/sh -c 'cd /usr/local/mysql; ./bin/mysqld_safe --user=username
--open-files-limit=512 &'

I would like to had the value permanently in my.cnf but
I do not have any /etc/my.cnf, I only found these config
files (with no open-files-limit declaration in it):
in mysqldirectory/support-files
my-small.cnf
my-medium.cnf
my-large.cnf
my-innodb-heavy-4G.cnf
my-huge.cnf
Should I use one of these files and copy it to the /etc/my.cnf? I
didn't found any information on it. So I can add the open-files-limits
in it.

Is there a way to view the value of open-files-limit in the mysql
shell? I tried to find how to do it but I was out of luck.

Thanks

[Gordon Burditt]

>> What is the setting on the mysqld variable 'open_files_limit'?

>> You might want to set this lower (definitely lower than
>> kern.maxfiles) to prevent it from taking over the system.

>
>Thanks Gordon, I'm trying to change the open_files_limit of
>mysql to see if it works. Right now I have added it to my
>rc.local mysql startup string:
>
>/bin/sh -c 'cd /usr/local/mysql; ./bin/mysqld_safe --user=username
>--open-files-limit=512 &'
>
>I would like to had the value permanently in my.cnf but
>I do not have any /etc/my.cnf, I only found these config
>files (with no open-files-limit declaration in it):
>in mysqldirectory/support-files
>my-small.cnf
>my-medium.cnf
>my-large.cnf
>my-innodb-heavy-4G.cnf
>my-huge.cnf
>Should I use one of these files and copy it to the /etc/my.cnf? I
>didn't found any information on it. So I can add the open-files-limits
>in it.

It's perfectly possible to start with an empty file, add a [mysqld]
section, and set variables that you like. Some of the tuning stuff
from my-small.cnf might be appropriate if you don't want mysqld
taking over the whole system.

>Is there a way to view the value of open-files-limit in the mysql
>shell? I tried to find how to do it but I was out of luck.

show variables like 'open-files-limit';

There's a bunch of other things in 'show variables' that you might
want to look at current values before dragging in defaults from
some of the sample my-*.cnf files.

Gordon L. Burditt

[none]

Thanks, my mySQL is installed in the /usr/local directory. if I create
a my.cnf
file, should I put it in the /etc/ or /usr/local/etc directory? I guess
I should put
it in the /usr/local/etc directory, but I just want to be sure.

Also I start mysql using bin/mysqld_safe, should I use /bin/mysqld
instead to
be sure the my.cnf file is loaded? I saw that mysqld_safe is the best
way to
start mysql.

Thanks again

[none]

I tried to copy my-small.cnf to my.cnf and added this in the
[mysqld_safe] section:
open_files_limit = 256

I also tried it in the [mysqld] section

I tried everything, placing the my.cnf file in /etc/, /usr/local/etc/,
/usr/local/mysql/, /usr/local/mysql/data (DATADIR)

But none of them seems to be used by mysqld_safe, the open_files_limits
is not changed. I also tried to call mysqld_safe with this option:
--defaults-file=/usr/local/mysql/my.cnf but it doesnt work.

I didn't find any information about this problem on the net, why my
my.cnf file is not used?

Thanks

[none]

Ok, it seems the file is read but not every options are taken into
account (like the open_files_limit). I just tried to put my.cnf in
the /etc/ directory and add this line to the file:
skip-innodb

When I rebooted mysql took 10Meg less RAM. It doesn't work if I
put the file in the /usr/local/etc/ directory. I didn't test it in
/usr/local/mysql and /usr/local/mysql/data/.

I still don't understant why some options do not change anything,
many of them like:
sort_buffer_size
net_buffer_length
table_cache
key_buffer_size
thread_stack
open_files_limit

By the way anyone know if innoDB is really usefull for a small
mySQL database? I would really like to free 10 megs from memory

Thanks