Thursday Jun 11, 2009

Changing process.max-file-descriptor using 'ulimit -n' can cause MySQL to change table_open_cache value

Before I get into details here is the bottom line. If you start MySQL on Solaris as a non-root (ie, mysql) user and for some reason you need to adjust the file descriptor resource limit for the parent shell, never use 'ulimit -n'. This will set both the soft and hard limit and may cause MySQL to adjust the max_connections and table_open_cache configuration variables upon next startup.

Use either:

 ulimit -S -n 1024

or something like:

  prctl -n process.max-file-descriptor -t basic -v  1024 -r -i process $$

The Details

The default 'basic' privilege value for the resource control process.max-file-descriptor is 256. This control represents the soft ulimit for file descriptors per process. The default 'privileged' privilege is set to 65535, which represents the hard ulimit. A non-root user can adjust the soft limit down or up to the hard limit. Unless it has PRIV_SYS_RESOURCE a non-root user can only decrease the hard limit.

Not being aware of the default values I started a sysbench run with 256 threads. It failed with the following message:

FATAL: unable to connect to MySQL server, aborting...
FATAL: error 2001: Can't create UNIX socket (24)
FATAL: failed to connect to database server!
FATAL: thread#252: failed to connect to database server, aborting...

Sysbench fails because the action for the basic privilege process.max-file-descriptors is deny. I was running sysbench logged in as mysql. I made the mistake of increasing the ulimit to what I thought was a reasonable 1024 via 'ulimit -n 1024'. When restarting mysqld it inherited a soft and hard limit of 1024. This had the effect of causing mysqld to recalculate the values for max_connections and table_open_cache as displayed in the error log:

090610  9:44:11 [Warning] Changed limits: max_open_files: 1024  max_connections: 886  table_cache: 64

This value is much too small for table_open_cache and is guaranteed to cause heavy contention on LOCK_open as concurrent threads increase.

Why did MySQL change table_open_cache and max_connections?

Upon startup mysqld will calculate the number of open files it wants selecting the greatest of:

  • myisam's requirement for 2 file handles per connection
    • 10+max_connections+table_cache_size\*2
  • max_connnections\*5
  • open_files_limit

For the purpose of this discussion let's call this max_open_files.

It then compares max_open_files with the soft resource limit for file descriptors per process. If the soft limit is less than max_open_files both the soft and hard limit will be set to max_open_files via setrlimit. If setrlimit fails the requested max_open_files is modified to the old soft limit. If the returned max_open_files is less than myisam's requirement for 2 file handles per connection and open_files_limit has not been specified, max_connections and table_open_cache will be recalculated to fit within the new boundaries.

Now take the case where I inadvertently set both the soft and hard limit via 'ulimit -n 1024'. I had set  open_table_cache=4096 and max_connections=2049, arbitrary large values so I could set them once and forget about them. In this case max_open_files=max_connections\*5=10245. When trying to increase the limits to 10245, the setrlimit call fails because I started mysqld as the mysql user and it does not have the privilege to increase the hard limit from 1024 to 10245. In this case max_open_files is set to 1024, which causes mysqld to recalculate max_connections and table_open_cache:

090610  9:44:11 [Warning] Changed limits: max_open_files: 1024  max_connections: 886  table_cache: 64

How to observe process.max-file-descriptor

You can use either prctl or ulimit to observe the current values of process.max-file-descriptor:

$ prctl -n process.max-file-descriptor  -i process $$
process: 20773: -ksh
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
        basic             256       -   deny                             20773
        privileged      65.5K       -   deny                                 -
        system          2.15G     max   deny                                 -
$ ulimit -S -n ### soft limit
$ ulimit -H -n ### hard limit

Wednesday Apr 22, 2009

MySQL DTrace presentation slides

For some reason my presentation slides have not been posted to on MySQL Users Conference site. Here it is, a pdf format of "Introduction to Using DTrace with MySQL".

Tuesday Apr 21, 2009

mysql dtrace provider now enabled in MySQL 5.4

Over the last year there has been alot of work done to instrument MySQL with static dtrace probes. The mysql provider first became available in MySQL 6.0.8 but required a build with the --enable-dtrace flag to enable it. Starting with MySQL 5.4 the mysql provider and it's static probes are now enabled by default and ready to be used on Solaris 10, OpenSolaris and Mac OS X.

The static probes in MySQL 5.4 have been designed to follow the execution path of a query

with the level getting deeper as the query execution proceeds. However the probes are implemented so that you don't have to traverse the entire hierarchy to get the information you want.

They are also implemented in sets. The -start probe exposes pertinent information such as the query text. The -done probe returns the status of the probe operation. With probe sets time spent within an operation can be captured.

Check out MC Brown's MySQL University presentation "Using DTrace with MySQL" for details. MC and I will also be presenting a simlar presentation at the MySQL User's Conference on Tuesday 4/21 at 4:25pm (shameless plug). If you can't make that session there is another session on DTrace and MySQL on Wed 4/22 at 4:25pm.

For examples and ideas check out Jenny Chen's Blueprint on DTrace and MySQL . There is also a detailed section in the MySQL 6.0 documentation on using the MySQL static probes.

Tuesday Nov 04, 2008

Why isn't MySQL using the my.cnf settings I've specified?

You are just getting started with MySQL on OpenSolaris. You've installed the OpenSolaris Community Edition and CoolStack MySQL. To explore this new environment you decide to run some tests using the sysbench benchmark. After running a number of tests you realize that for some reason the options you are setting in /etc/my.cnf are not getting used.

What is going on? Not to worry, you've just hit a problem common to new users of MySQL on OpenSolaris.

Run this command:

 # /opt/coolstack/mysql/bin/mysqladmin | more

page down until you see:

 Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /opt/coolstack/mysql/etc/my.cnf ~/.my.cnf

This shows the order that my.cnf option files are read. Now do a copy and paste and 'ls -l' of these files:

$ ls -l /etc/my.cnf /etc/mysql/my.cnf /opt/coolstack/mysql/etc/my.cnf ~/.my.cnf
/opt/coolstack/mysql/etc/my.cnf: No such file or directory
/opt/coolstack/mysql/.my.cnf: No such file or directory
-rw-r--r--   1 mysql    mysql        676 Oct 23 15:21 /etc/my.cnf
lrwxrwxrwx   1 root     root          10 Nov  3 09:08 /etc/mysql/my.cnf -> 5.0/my.cnf

You expect to see /etc/my.cnf, after all you put it there. But how did /etc/mysql/my.cnf get there? It's there because by default MySQL is installed in /etc/mysql when you install OpenSolaris. Due to the order that my.cnf files are read any parameter specified in /etc/mysql/my.cnf will override the same setting in /etc/my.cnf.

So what to do? Well you could just delete the /etc/mysql/my.cnf symbolic link, but the best practice we've established in the MySQL Performance and Scalability project is to explicitly specify the my.cnf file as part of MySQL startup using the --defaults-file parameter:

# mysqld_safe --defaults-file=/etc/my.cnf &

This guarantees that only the configuration parameters you expect are used by the MySQL instance you started. When using the --defaults-file there is no restriction on the name and location of the configruation file. This allows better sharing of a system environment where multiple instances of MySQL are being run with different configuration parameters.


Vince Carbone


« July 2016