1

I'm getting "Can't open file: './databasename/tablename.frm' (errno: 24)" errors from MySQL but as far as I can tell, I've set the necessary limits far higher than I need.

Other than the kern.maxfiles and kern.maxfilesperproc sysctls and the open_files_limit MySQL variable, are there any other limits which would be kicking in and causing this?

$ mysql -u root -p -e 'SHOW STATUS' | grep '^Open'
Enter password: 
Open_files  356
Open_streams    0
Open_tables 245
Opened_tables   0

$ mysql -u root -p -e 'SHOW GLOBAL VARIABLES' | grep 'open_files_limit'
Enter password: 
open_files_limit    18000

$ sysctl -a | grep kern.maxfiles
kern.maxfiles: 20000
kern.maxfilesperproc: 18000

$ limits -n -U mysql
Resource limits for class default:
  openfiles        infinity
Conor McDermottroe
  • 948
  • 1
  • 7
  • 17
  • what shows `sysctl kern.openfiles` ? – SaveTheRbtz Nov 23 '09 at 19:31
  • Right now, 867 and we're over 50% of average peak queries per second. I haven't been able to trap the value of it when the failure occurs (I'm wary of shelling out inside the vBulletin error handler), but I have munin running and the open_files plugin records kern.maxfiles and kern.openfiles every 5 minutes. Peak for kern.openfiles is approx 2000. – Conor McDermottroe Nov 24 '09 at 09:11

2 Answers2

2

Issue looks more like a bug than bumping into a limit.
Only suggestion is to update to latest stable MySQL version, or even to FreeBSD 7.2 (they higher almost all limits on FreeBSD 7.2 amd64)

SaveTheRbtz
  • 5,691
  • 4
  • 32
  • 45
  • The machine in question is a replication slave so I'm hesitant to upgrade past 5.0 without upgrading the master and other slaves as well. Since it's in production, I can't afford the downtime. As for FreeBSD 7.2, there's a bug (PR:135673) which we ran into on another slave. We patched it out, but the patch only reduces the frequency of the lockups, not eliminate them. Thanks for the suggestions though, they may be worth re-examining. – Conor McDermottroe Nov 24 '09 at 14:11
0

what is your max_connections Setting? For every connection one (or more) files are to be opened. Try to reduce. Also table_cache Setting can influence the number of open files. See mysql manual for more help.

CU, arnep

  • Both max_connections and table_cache have been tuned to be as small as possible while leaving us some headroom for spikes. Thanks for the answer. – Conor McDermottroe Nov 24 '09 at 14:21