Results 1 to 25 of 26
-
11-02-2009, 05:34 AM #1Web Hosting Evangelist
- Join Date
- Aug 2005
- Posts
- 531
mysql issues - too many open files
guys i've been busting my balls over this issue for over a week now.
Basically mysql is behaving very very intermittently. Crashes were every 4 hours, I've brought them down to once every 8 or so hours but mysql keeps dying.
the error log will show the same routine each time.
on mysql start:
091101 21:58:03 [Warning] option 'open_files_limit': unsigned value 120000 adjusted to 65535
091101 21:58:03 [Warning] Could not increase number of max_open_files to more than 65535 (request: 200110)
091101 21:58:03 [Note] /usr/sbin/mysqld: ready for connections.
091102 0:33:07 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './<nameofdatabase.frm>'
091102 0:36:35 [ERROR] /usr/sbin/mysqld: Can't open file: '>another database here.frm>
091102 0:36:36 [ERROR] /usr/sbin/mysqld: Sort aborted
091102 0:36:52 [ERROR] /usr/sbin/mysqld: Sort aborted
091102 0:43:00 [ERROR] Error in accept: Too many open files
-
11-02-2009, 07:19 AM #2Junior Guru
- Join Date
- Jun 2009
- Location
- Kochi,India
- Posts
- 179
Try to run this as your cron job
mysqlcheck --auto-repair mysqlmysqlcheck --auto-repair mysql
Also find which query is using this much of resource by
mysqladmin proc
-
11-02-2009, 07:24 AM #3Web Hosting Evangelist
- Join Date
- Aug 2005
- Posts
- 531
have run the mysqlcheck and myisamcheck already. some of these tables just do seem corrupted, it says they cannot be repaired.
in terms of mysqladmin proc practically no connections. definitely the handful now is no comparison to the thousands required to cause issue?
-
11-02-2009, 08:48 AM #4Junior Guru Wannabe
- Join Date
- Oct 2009
- Posts
- 58
You can increase the open files limit in mysql configuration file my.cnf Add the following to your my.cnf file
----
open-files-limit=XXXXX, where X indicates the value needed.
Note: they are hyphens instead of underscoresWindows/Linux Expert; Plesk/Cpanel/Ensim Guru.
..and an expert spam tracker.
-
11-02-2009, 09:03 AM #5Web Hosting Evangelist
- Join Date
- Aug 2005
- Posts
- 531
-
11-02-2009, 09:11 AM #6Web Hosting Master
- Join Date
- Mar 2009
- Posts
- 2,222
-
11-02-2009, 09:16 AM #7Web Hosting Evangelist
- Join Date
- Aug 2005
- Posts
- 531
i had just imported a number of accounts across from an old server. old server mysql has no issues, and this server had no issues prior to the import.
i thought it was to do with corrupt mysql tables but would that cause mysql to crash??
i'm keeping an eye out on the .err file to see what tables cause issues and will just drop them and reimport them or something...
-
11-02-2009, 09:58 AM #8Web Hosting Master
- Join Date
- Mar 2009
- Posts
- 2,222
-
11-02-2009, 12:41 PM #9Web Hosting Evangelist
- Join Date
- Aug 2005
- Posts
- 531
the accounts were nicely packaged cpanel accounts migrated directly from the old server... imported accounts work fine, just a few of these tables have gone haywire. permissions etc were set by cpanel on account restore, but from what i can see they look ok too.
-
11-03-2009, 06:49 AM #10Web Hosting Evangelist
- Join Date
- Aug 2005
- Posts
- 531
ah this is so frustrating. mysql is going out with "too many open files" errors now, even with no complaints on bad tables.
I really need to increase this open file limit - I think it's an OS thing, not a mysql thing. I hear whispers of ulimit and stuff like that, any idea how to allow the OS to have more open files?
-
11-03-2009, 07:24 AM #11Web Hosting Master
- Join Date
- Apr 2003
- Location
- San Jose, CA.
- Posts
- 1,624
How many databases do you have on this server?
How many tables does each database have?
Do you see anything that stands out as excessive there?
Cuz, to me this seems more like a stupid user/malicious user problem... you could try and make the system able to open more inodes... but you'll probably just make the whole system become sluggish to unresponsive much faster.Daved @ Lightwave Networking, LLC.
AS1426 https:/www.lightwave.net
Primary Bandwidth: EGIHosting (NLayer, NTT, HE, Cogent)
Xen PV VPS Hosting
-
11-03-2009, 07:36 AM #12Web Hosting Evangelist
- Join Date
- Aug 2005
- Posts
- 531
there are quite a few databases / tables - but in terms of USAGE it's not high. when I run the proclist for mysql majority of the times i check it'll only be root doing the processlist command, and during peak hours i may see 1-2 other users. that is not heavy. also in terms of the system itself, load is low (<1/2 of capacity), no high io wait, no high cpu, the system is quite relaxed. even with the mysql outages, if i have a top window running there's absolutely nothing out of the ordinary load wise.
according to munin, inode usage is <20% for each one of my mounts, i'm not hitting the limit there. if i look at mysql throughput / number of queries / slow queries / threads in the munin logs, once again no spikes in any of those.
-
11-03-2009, 07:55 AM #13Web Hosting Master
- Join Date
- Apr 2003
- Location
- San Jose, CA.
- Posts
- 1,624
That's nice, but you didn't answer any of the questions.
fwiw the hard max is 65k, so you're not going to get above that unless you want to try and change MySQL source code and recompile.
MySQL is saying it wants to open 200k simultaneous inodes...
Someone either has a really bad designed database, or you've screwed up one of the other variables like max_connections which is used in the system guessing how many file descriptors it should try and reserve.Daved @ Lightwave Networking, LLC.
AS1426 https:/www.lightwave.net
Primary Bandwidth: EGIHosting (NLayer, NTT, HE, Cogent)
Xen PV VPS Hosting
-
11-03-2009, 08:01 AM #14Web Hosting Evangelist
- Join Date
- Aug 2005
- Posts
- 531
1,160 databases total. How do I check how many tables there are?
my my.cnf:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-innodb
max_connections = 100
key_buffer = 128M
myisam_sort_buffer_size = 128M
long_query_time = 10
join_buffer_size = 4M
read_buffer_size = 2M
sort_buffer_size = 4M
table_cache = 100000
thread_cache_size = 128
wait_timeout = 10
connect_timeout = 5
max_allowed_packet = 10M
max_connect_errors = 5
query_cache_limit = 1M
query_cache_size = 128M
tmp_table_size = 256M
max_heap_table_size = 384M
open-files-limit=120000
log-slow-queries = /var/log/mysql-slow.log
[mysqld_safe]
err-log=/var/log/mysqld.log
open-files-limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 32M
read_buffer = 16M
write_buffer = 16M
host system has 6gb of RAM.
-
11-03-2009, 08:22 AM #15Web Hosting Master
- Join Date
- Apr 2003
- Location
- San Jose, CA.
- Posts
- 1,624
The easiest way for me is to just look at the server with phpMyAdmin...
You could try editing /etc/init.d/mysqld
and adding a ulimit -n 4096
or something in there... but I still worry that putting an excessive number in there will cause more problems than it helps.Daved @ Lightwave Networking, LLC.
AS1426 https:/www.lightwave.net
Primary Bandwidth: EGIHosting (NLayer, NTT, HE, Cogent)
Xen PV VPS Hosting
-
11-03-2009, 08:26 AM #16Web Hosting Evangelist
- Join Date
- Aug 2005
- Posts
- 531
yep i did look through phpmyadmin. i clicked on "databases" and i see 1,160 databases there. but in each of those there will be tables, and i have no idea how many tables there are in all - whether its below 65k.
added the ulimit parameter, lets see if that helps. is there no OS limit though as well that I could modify?
-
11-03-2009, 08:27 AM #17Web Hosting Master
- Join Date
- Mar 2009
- Posts
- 2,222
You could list the open files with 'lsof', and see if that casts any light on what's happening.
Actually, it's worth counting them, with 'lsof | wc' , just to make sure you are not hitting the Linux limit. It's pretty unlikely; you can check the Linux limit with
'cat /proc/sys/fs/file-max' to find the current limit
By the way, I think the 'open file limit' is actually a limit on file descriptors, so it includes pipes and sockets as well as disk files.
You can list open sockets with 'netstat'
You may find there are many sockets in 'CLOSE_WAIT'.
-
11-03-2009, 08:30 AM #18Web Hosting Evangelist
- Join Date
- Aug 2005
- Posts
- 531
lsof | wc: 64895 589488 8513619
cat /proc/sys/fs/file-max: 300,000
netstat: not many close_waits but heaps of time_waits and fin_waits. would that cause an issue?
-
11-03-2009, 08:32 AM #19Web Hosting Master
- Join Date
- Apr 2003
- Location
- San Jose, CA.
- Posts
- 1,624
the "window" on the left side might say something like:
* cacti (48)
* information_schema (28)
* mysql (23)
* test (0)
the number in parens is how many tables the database has...
but it's possible your system doesnt have it displayed the same...Daved @ Lightwave Networking, LLC.
AS1426 https:/www.lightwave.net
Primary Bandwidth: EGIHosting (NLayer, NTT, HE, Cogent)
Xen PV VPS Hosting
-
11-03-2009, 08:34 AM #20Web Hosting Evangelist
- Join Date
- Aug 2005
- Posts
- 531
it does but i'd have to go through 12 pages of databases and calculate for the 1,160 databases Taking a quick browse through I realize I need an average over 50 tables per database for each one of those 1160 to hit the 65k limit, and i'm quite certain i'm nowhere close to that.
-
11-03-2009, 08:44 AM #21Web Hosting Master
- Join Date
- Apr 2003
- Location
- San Jose, CA.
- Posts
- 1,624
I wouldn't expect the average to seem unusual... but perhaps look for one which seems excessively high.
Daved @ Lightwave Networking, LLC.
AS1426 https:/www.lightwave.net
Primary Bandwidth: EGIHosting (NLayer, NTT, HE, Cogent)
Xen PV VPS Hosting
-
11-03-2009, 08:46 AM #22Web Hosting Evangelist
- Join Date
- Aug 2005
- Posts
- 531
still goign through them.
p.s. can anyone make sense of this:
http://mail-index.netbsd.org/netbsd-...9/02/0000.html
seems like an identical issue where he resolved it.
-
11-03-2009, 08:56 AM #23Web Hosting Master
- Join Date
- Apr 2003
- Location
- San Jose, CA.
- Posts
- 1,624
hard to say there... a little hard to compare when that's talking about a netbsd kernel from 2002
Daved @ Lightwave Networking, LLC.
AS1426 https:/www.lightwave.net
Primary Bandwidth: EGIHosting (NLayer, NTT, HE, Cogent)
Xen PV VPS Hosting
-
11-03-2009, 08:58 AM #24Web Hosting Evangelist
- Join Date
- Aug 2005
- Posts
- 531
lol fair enough.
right now i'm tailing my mysql .err log. i noticed before the most recent crash (couple of minutes ago) it said incorrect information in file. i dug around for this file and seems that phpmyadmin shows it as "in use" and the mysql folder has no MYI for the file. I've dropped that table. lets see if there are any others - i found 3 such occurrences so far.
-
11-03-2009, 12:53 PM #25Web Hosting Master
- Join Date
- Mar 2009
- Posts
- 2,222
Similar Threads
-
mysql open files limit
By jon-f in forum Hosting Security and TechnologyReplies: 7Last Post: 01-04-2011, 03:22 AM -
[warn-phpd] mmap cache can't open foo.php - Too many open files
By guarriman in forum VPS HostingReplies: 5Last Post: 03-27-2008, 03:05 PM -
Too many open files
By rainy_day in forum Hosting Security and TechnologyReplies: 8Last Post: 10-07-2005, 06:28 AM -
How to open files from MSDOS 3 !!
By asmar in forum Programming DiscussionReplies: 9Last Post: 03-04-2005, 04:43 AM -
How to open .dmg and s.it files
By lokodezine in forum Web Hosting LoungeReplies: 7Last Post: 10-29-2003, 10:03 PM