List:MySQL on Win32« Previous MessageNext Message »
From:Leigh Sharpe Date:August 12 2004 5:09am
Subject:Re: Out of resources error - RESOLVED
View as plain text  
2k Server.
 Running very little other than Apache, mysql and a handful of Perl scripts
which are collecting and storing information in the MySQL database.
There's also a pile of CGIs accessing the SQL tables.
Dunno if it's relevant or not, but the Database is on a RAID0 drive, and
isn't on the primary partition of the HDD's.

This bit of the mysql doc's was a dead giveaway, really, had I read it
properly in the first place:

From http://dev.mysql.com/doc/mysql/en/Not_enough_file_handles.html
<snip>

The problem here is that mysqld is trying to keep open too many files
simultaneously. You can either tell mysqld not to open so many files at once
or increase the number of file descriptors available to mysqld.

To tell mysqld to keep open fewer files at a time, you can make the table
cache smaller by reducing the value of the table_cache system variable (the
default value is 64). Reducing the value of max_connections also will reduce
the number of open files (the default value is 100).

</snip>

My table_cache was 512, though, probably because I was using the my.huge INI
file.


----- Original Message -----
From: "Armando" <dijital@stripped>
To: "'MySQL Win32 List'" <win32@stripped>
Sent: Thursday, August 12, 2004 2:32 PM
Subject: RE: Out of resources error - RESOLVED


> Hi Leigh,
>
> I'm glad you were able to get it resolved before I got a chance to
> reply. I've actually never encountered someone who had to reduce the
> table cache before, so that's pretty weird. If you don't mind me asking,
> is this 2K Pro or 2K Server and what other type of applications is it
> running simultaneously with MySQL? I'd love to tuck that information
> away for future reference! Cheers.
>
> Armando
>
> -----Original Message-----
> From: Leigh Sharpe [mailto:lsharpe@stripped]
> Sent: August 11, 2004 6:19 PM
> To: Armando; 'MySQL Win32 List'
> Subject: Re: Out of resources error - RESOLVED
>
>
> Looks like I've found the problem.
>  The table_cache was actually too high.
> By reducing it to 200, I've stopped the problem.
> Seems Win2K doesn't actually have that many file handle to give out
> after all. Thanks for your help.
>
> ----- Original Message -----
> From: "Leigh Sharpe" <lsharpe@stripped>
> To: "Armando" <dijital@stripped>; "'MySQL Win32 List'"
> <win32@stripped>
> Sent: Wednesday, August 11, 2004 9:37 AM
> Subject: Re: Out of resources error
>
>
> > Still struggling here, I'm afraid.
> >  * Set open_files_limit to 10000. Mysqld refused to start altogether.
> > *Removed setting and increased table_cache to 1200 and re-started
> > mysqld.
> No
> > difference. I have about 520 or so tables which are being accessed
> > continuously, simultaneously. According to 'show variables', the
> table_cache
> > is 1200, but I run into problems whenever the I reach 512 open tables.
> >
> > Any ideas?
> >
> > ----- Original Message -----
> > From: "Armando" <dijital@stripped>
> > To: "'MySQL Win32 List'" <win32@stripped>
> > Sent: Saturday, August 07, 2004 8:02 AM
> > Subject: RE: Out of resources error
> >
> >
> > > Win2k Server I believe has a maximum of 16384. I can't recall off
> > > the top of my head if 2K Pro is the same. I would probably start by
> > > doubling your table-cache to see what that does for you. That will
> > > give you a good increase to 2148 file descriptors, and yet still
> > > leave a fair amount for Windows. As for how much is too much?
> > > Depends on what else your server is running. If it's strictly for
> > > the database, then you can increase the value a lot as there will be
>
> > > little else running that will require them. Cheers.
> > >
> > > Armando
> > >
> > > -----Original Message-----
> > > From: Leigh Sharpe [mailto:lsharpe@stripped]
> > > Sent: August 5, 2004 11:40 PM
> > > To: Armando; 'MySQL Win32 List'
> > > Subject: Re: Out of resources error
> > >
> > >
> > > Any idea what the maximum number of file descriptors is for Win2K?
> > > My table_cache is currently 512 and max_connections is 100.
> > > according to your formula, that gives a value of 1124 for file
> > > descriptors. If I increase this too far, am I going to strike
> > > trouble with Windows not having enough file descriptors to give to
> > > MySQL? And how much is too much?
> > >
> > > ----- Original Message -----
> > > From: "Armando" <dijital@stripped>
> > > To: "'MySQL Win32 List'" <win32@stripped>
> > > Sent: Friday, August 06, 2004 1:47 PM
> > > Subject: RE: Out of resources error
> > >
> > >
> > > > Hi Leigh,
> > > >
> > > > As you have probably already figured out, the error means there
> > > > are "Too many open files" which I agree is somewhat terse and not
> > > > very descriptive - it basically means that too many file
> > > > descriptors are being used by the mysql daemon than are actually
> > > > avaialble to it. HDD and RAM space are not the issue. The first
> > > > thing I would try is increasing your open_files_limit system
> > > > variable to allow the mysql daemon to use more file descriptors. I
>
> > > > believe by default the value is
> > >
> > > > 0 which means MySQL will allocate a value equivalent to
> > > > max_connections+5 or max_connections+table_cache*2, whichever is
> > > > larger. Try setting a larger static value to see if the error
> > > > persists. Cheers.
> > > >
> > > > Armando
> > > >
> > > > -----Original Message-----
> > > > From: Leigh Sharpe [mailto:lsharpe@stripped]
> > > > Sent: August 4, 2004 6:01 PM
> > > > To: Armando; 'MySQL Win32 List'
> > > > Subject: Re: Out of resources error
> > > >
> > > >
> > > > Temporary tables vary from about 10 rows to a max of 500000.
> > > > Typically
> > >
> > > > around the 100-500 mark. Running version 4.0.15 on Win2K. The
> > > > machine has 1GB of RAM, of which about half is free. There's about
>
> > > > 1.5GB of space on the temp drive to play with, and over 50GB of
> > > > space on the drive the database is stored on. I have noticed that
> > > > regularly issuing
> > >
> > > > a 'flush tables' command stops the problem, but I'm not sure if
> > > > this is a satisfactory solution.
> > > >
> >
> >
> >
> > --
> > MySQL Windows Mailing List
> > For list archives: http://lists.mysql.com/win32
> > To unsubscribe:
> http://lists.mysql.com/win32?unsub=1
> >
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:    http://lists.mysql.com/win32?unsub=1
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:
http://lists.mysql.com/win32?unsub=1
>

Thread
Out of resources errorLeigh Sharpe4 Aug
  • RE: Out of resources errorArmando4 Aug
    • Re: Out of resources errorLeigh Sharpe5 Aug
      • RE: Out of resources errorArmando6 Aug
        • Re: Out of resources errorLeigh Sharpe6 Aug
          • RE: Out of resources errorArmando7 Aug
            • Re: Out of resources errorLeigh Sharpe11 Aug
            • Re: Out of resources error - RESOLVEDLeigh Sharpe12 Aug
              • RE: Out of resources error - RESOLVEDArmando12 Aug
                • Re: Out of resources error - RESOLVEDLeigh Sharpe12 Aug
  • Re: Out of resources errorFriedhelm Betz5 Aug