Hello mysql gurus!

Mysql 5.1, windows 7/windows sever 2008.

I have read the following topic:
http://dev.mysql.com/doc/refman/5.1/...s-windows.html

I wonder:
1. The documentation states that for Windows the value is 2048 but
nothing is said about what Win OS version is regarded. Does this mean
that:
Mysql on Windows always uses POSIX AND no matter what version of OS it
is it's always the same "C runtime library" THUS no matter what but if
I am using Windows it is at most 2048 open files.

2. Is this correct that 2048 is the limit for Mysql only and I do not
have to take other processes (that also open files) into the
consideration?

3. What if I have 2 instances of Mysql server running on the same
computer? Does this mean limit of 2048 open files per instance? Or per
computer?

4. Is "open files" the same as "open tables"?

5. Is the command: "show open tables;" the correct way to find out how
many open files are at the moment? Can I monitor this somehow to find
out what the "expected" value for my application is?

Thank you in advance for any help,
Anatoly.

On 11/6/2011 6:04 PM, anatoly71 (AT) gmail (DOT) com wrote:
AFAIK, yes.

No. There may be further limits from the OS, i.e. the maximum number of
files which can be open in all processes. This limit may or may not
limit the number of files MySQL can open.


Yes, if they are two separate processes. But again, system limits may
lower this number.

No. You may have one file per table (i.e. MyISAM) or all tables in one
file (i.e. some InnoDB configurations). In addition you will have other
files open, i.e. logs, temp files, etc.


No.

The real question here is - are you having a problem? 2048 files is a
lot of open files by one process on any system.

Or are you just prematurely optimizing?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

I suspect you'd have to ask Microsoft that one.
The C library does not have to enforce a fixed limit - it may simply
report that the underlying OS wouldn't let it open another file.

There are almost certainly per-process limits *AND* per-system limits
on open files. For the per-system limit, you do have to worry about
other processes. At a mimimum, the per-system limit is the total
amount of system RAM divided by the size of an open file structure, but
this is going to be way high because you need RAM for other stuff too.

The per-system limits can still bite you here. The OS probably
won't let one process suck up all the open files, so the per-system
limit is probably greater than 2048, but it might not be greater
than twice 2048. Or then again, it might be.

No. There are plenty of programs that have open files but have no
tables, and mysqld uses open files for other stuff (like log files
and TCP/IP connections to clients).

For MyISAM tables, it's one open file per table (the index file)
plus one open file *per client that has the table open* per table
(the data file). So if two clients have the spamfilter.blocklist
table open, that's 3 open files.

See "How MySQL Opens and Closes Tables" in the MySQL documentation at
dev.mysql.com.


No. Try an OS tool like "lsof" looking only at the MySQL server process.
I think someone has ported that or something similar to Windows.
One such program is called "Process Explorer" (See Wikipedia "lsof",
under "See Also").

I don't think there is an "expected" value.

"anatoly71 (AT) gmail (DOT) com" <anatoly71 (AT) gmail (DOT) com> wrote:
The 2048 limit is built into Microsofts POSIX layer and AFAIK it's the
same on all versions of Windows. The limit is gone in MySQL 5.5 because
then MySQL no longer uses the POSIX API.

See http://bugs.mysql.com/bug.php?id=24509

The limit is for every program that uses the POSIX API. This is more
likely for programs that have been ported from native POSIX platforms
(aka UNICES) than for programs that have been written for Windows.

On real POSIX platforms, this limit is per process. = MySQL instance.

Not exactly. MyISAM can use many file handles per open table (typically
2). InnoDB with the shared table space uses only few file handles
globally (quite independent from the number of open tables).

No. With some constraints (RTFM) this should work:

SHOW GLOBAL STATUS LIKE 'Open_files';

For real POSIX systems (where files and network sockets share the file
handle limit) the following formula is used to calculate the number of
file handles needed by the MySQL server:

fd_max = 2*table_open_cache + max_connections + 10;

This is however based on the assumption of MyISAM tables and I'm not
sure if it holds for Windoze at all.


XL