Description:
We use large query and table caches on machine having 8GB RAM, 4 CPU's.
We found we have to periodically flush table cache by a cronjob(every hour), otherwise mysql connections are outsourced(mysql(1) client exits that it cannot connect to the server). Connection which are present at that time are unusable(the prompt in mysql(1) does not respond/perform queries). This is reproducible.
Additionally, twice (so maybe not reproducible) we flushed table cache, server died with the followin message. Please note the other messages from previous or later startups.
We have set the number of files in server side my.cnf to 32768. Please note in the error output below the number 32878(it has been rounded up!) by mysqld.
The system documentation says on Tru64Unix 5.x is possible to have set max open files to only 4096 for the system ! Any help on that side? But the documenation also says that with setrlimit one can raise the max-open-files for a process .
Please check the URL for details pertaining to the above comment
http://h30097.www3.hp.com/docs/base_doc/DOCUMENTATION/V51A_HTML/ARH9GBTE/CHLMTSXX.HTM#fs-l...
Also mysqladmin variables shows
| new | OFF
|
| open_files_limit | 32768
|
| pid_file | /mysql1/sql1/clu3.gsf.de.pid
Definitely mysqld should report the current value which is neither 32878 nor 32768.
cat /sql01/mysqldb2/clu2.gsf.de.err
030717 13:56:14 mysqld started
Warning: Ignoring user change to 'mysql' because the user was set to 'mysql' earlier on the command line
030717 13:56:14 Warning: setrlimit couldn't increase number of open files to more than 4086 (request: 32878)
030717 13:56:14 Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13)
030717 13:56:14 Aborting
030717 13:56:14 /usr/local/mysql2-4013/bin/mysqld: Shutdown Complete
030717 13:56:14 mysqld ended
030717 13:58:38 mysqld started
030717 13:58:38 Warning: setrlimit couldn't increase number of open files to more than 4096 (request: 32878)
Warning: One can only use the --user switch if running as root
/usr/local/mysql2-4013/bin/mysqld: ready for connections.
Version: '4.0.13-standard' socket: '/tmp/mysqldb2.sock' port: 12002
030717 15:56:28 Aborted connection 9 to db: 'adb_rice_indica' user: 'karlowsk' host: `wallaby.gsf.de' (Got an error writing communication packets)
030721 16:00:01 Fatal error: Can't open privilege tables: Out of memory (Needed 2048 bytes)
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.
key_buffer_size=536870912
read_buffer_size=2093056
sort_buffer_size=2097144
max_used_connections=3
max_connections=100
threads_connected=3
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 933487 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
030721 16:02:45 mysqld restarted
030721 16:02:45 Warning: setrlimit couldn't increase number of open files to more than 4096 (request: 32878)
Warning: One can only use the --user switch if running as root
/usr/local/mysql2-4013/bin/mysqld: ready for connections.
Version: '4.0.13-standard' socket: '/tmp/mysqldb2.sock' port: 12002
030722 10:48:40 /usr/local/mysql2-4013/bin/mysqld: Normal shutdown
030722 10:48:41 /usr/local/mysql2-4013/bin/mysqld: Forcing close of thread 554 user: 'karlowsk'
030722 10:54:19 mysqld started
030722 10:54:20 Warning: setrlimit couldn't increase number of open files to more than 4096 (request: 32878)
Warning: One can only use the --user switch if running as root
/usr/local/mysql2-4013/bin/mysqld: ready for connections.
Version: '4.0.13-standard' socket: '/tmp/mysqldb2.sock' port: 12002
/usr/local/mysql2-4013/bin/mysqld: Out of memory (Needed 3336 bytes)
030728 08:26:12 mysqld restarted
030728 8:26:12 Warning: setrlimit couldn't increase number of open files to more than 4096 (request: 32878)
Warning: One can only use the --user switch if running as root
/usr/local/mysql2-4013/bin/mysqld: ready for connections.
Version: '4.0.13-standard' socket: '/tmp/mysqldb2.sock' port: 12002
030728 09:50:22 mysqld started
Warning: Ignoring user change to 'mysql' because the user was set to 'mysql' earlier on the command line
030728 9:50:22 Warning: setrlimit couldn't increase number of open files to more than 4086 (request: 32878)
030728 9:50:22 Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13)
030728 9:50:22 Aborting
030728 9:50:22 /usr/local/mysql2-4013/bin/mysqld: Shutdown Complete
030728 09:50:22 mysqld ended
030728 09:50:36 mysqld started
030728 9:50:36 Warning: setrlimit couldn't increase number of open files to more than 4096 (request: 32878)
Warning: One can only use the --user switch if running as root
/usr/local/mysql2-4013/bin/mysqld: ready for connections.
Version: '4.0.13-standard' socket: '/tmp/mysqldb2.sock' port: 12002
030728 12:09:53 Out of memory; Check if mysqld or some other process uses all available memory. If not you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
030728 14:13:44 Out of memory; Check if mysqld or some other process uses all available memory. If not you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
030729 9:37:36 /usr/local/mysql2-4013/bin/mysqld: Normal shutdown
030729 9:37:37 /usr/local/mysql2-4013/bin/mysqld: Shutdown Complete
030729 09:37:37 mysqld ended
030729 09:37:50 mysqld started
030729 9:37:50 Warning: setrlimit couldn't increase number of open files to more than 4096 (request: 32878)
Warning: One can only use the --user switch if running as root
/usr/local/mysql2-4013/bin/mysqld: ready for connections.
Version: '4.0.13-standard' socket: '/tmp/mysqldb2.sock' port: 12002
030729 9:53:03 Aborted connection 1 to db: 'adb_bes6_maize' user: 'karlowsk' host: `nelsonia.gsf.de' (Got an error writing communication packets)
030729 15:16:25 /usr/local/mysql2-4013/bin/mysqld: Normal shutdown
030729 15:16:26 /usr/local/mysql2-4013/bin/mysqld: Shutdown Complete
030729 15:16:26 mysqld ended
030729 15:16:32 mysqld started
Warning: Ignoring user change to 'mysql' because the user was set to 'mysql' earlier on the command line
030729 15:16:33 Warning: setrlimit couldn't increase number of open files to more than 4086 (request: 32878)
030729 15:16:33 Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13)
030729 15:16:33 Aborting
030729 15:16:33 /usr/local/mysql2-4013/bin/mysqld: Shutdown Complete
030729 15:16:33 mysqld ended
How to repeat:
OSF1 clu3.gsf.de V5.1 1885 alpha alpha
Snippet from my.cnf [mysqld] section:
enable-pstack
set-variable = key_buffer=512M
set-variable = max_allowed_packet=128M
set-variable = max_user_connections=250
set-variable = tmp_table_size=512M
set-variable = table_cache=512M
set-variable = query_cache_size=64M
set-variable = sort_buffer=2M
set-variable = record_buffer=2M
set-variable = myisam_sort_buffer_size=512M
set-variable = myisam_max_sort_file_size=256
set-variable = interactive_timeout=60000
set-variable = wait_timeout=60000
set-variable = connect_timeout=60000
set-variable = net_read_timeout=60000
set-variable = open_files_limit=32768
set-variable = thread_concurrency=4
set-variable = thread_cache_size=64
bash-2.04# sysconfig -q proc
proc:
max_proc_per_user = 1024
max_threads_per_user = 4096
per_proc_stack_size = 33554432
max_per_proc_stack_size = 33554432
per_proc_data_size = 268435456
max_per_proc_data_size = 4294967296
max_per_proc_address_space = 10737418240
per_proc_address_space = 4294967296
executable_stack = 0
autonice = 0
autonice_time = 600
autonice_penalty = 4
open_max_soft = 4096
open_max_hard = 4096
ncallout_alloc_size = 8192
round_robin_switch_rate = 0
sched_min_idle = 0
give_boost = 1
maxusers = 4096
num_wait_queues = 4096
num_timeout_hash_queues = 4096
enhanced_core_name = 0
enhanced_core_max_versions = 16
exec_disable_arg_limit = 0
dump_cores = 1
dump_setugid_cores = 0
bash-2.04# sysconfig -q ipc
ipc:
msg_max = 8192
msg_mnb = 16384
msg_mni = 64
msg_tql = 40
shm_max = 4278190080
shm_min = 1024
shm_mni = 256
shm_seg = 128
sem_mni = 16
sem_msl = 25
sem_opm = 10
sem_ume = 10
sem_vmx = 32767
sem_aem = 16384
sem_broadcast_wakeup = 1
max_kernel_ports = 314912
ssm_threshold = 8388608
ssm_enable_core_dump = 1
shm_allocate_striped = 1
shm_enable_core_dump = 1
