Re: daemon crash when shutting down large databases
David,
man pthread_mutex_destroy says:
"
...
pthread_mutex_destroy destroys a mutex object, freeing the
resources it might hold. The mutex must be unlocked on
entrance. In the LinuxThreads implementation, no resources
areassociated with mutex objects, thus
pthread_mutex_destroy actually does nothing except check
ing that the mutex is unlocked.
...
The pthread_mutex_destroy function returns the following
error code on error:
EBUSY the mutex is currently locked.
...
"
pthread mutexes are in InnoDB only created in a few places:
[EMAIL PROTECTED]:~/mysql-5.0/innobase> grep -n os_fast_mutex_init ./*/*.c
./os/os0sync.c:137: os_fast_mutex_init(&(event->os_mutex));
./os/os0sync.c:453: os_fast_mutex_init(mutex);
./os/os0sync.c:562:os_fast_mutex_init(
./srv/srv0srv.c:930:os_fast_mutex_init(&srv_conc_mutex);
./srv/srv0start.c:1619: os_fast_mutex_init(&srv_os_test_mutex);
./sync/sync0sync.c:212: os_fast_mutex_init(&(mutex->os_fast_mutex));
./ut/ut0mem.c:51:os_fast_mutex_init(&ut_list_mutex);
In srv0start.c we clean up at a mysqld shutdown:
"
/* 3. Free all InnoDB's own mutexes and the os_fast_mutexes inside
them */
sync_close();
/* 4. Free the os_conc_mutex and all os_events and os_mutexes */
srv_free();
os_sync_free();
/* Check that all read views are closed except read view owned
by a purge. */
if (UT_LIST_GET_LEN(trx_sys->view_list) > 1) {
fprintf(stderr,
"InnoDB: Error: all read views were not closed before shutdown:\n"
"InnoDB: %lu read views open \n",
UT_LIST_GET_LEN(trx_sys->view_list) - 1);
}
/* 5. Free all allocated memory and the os_fast_mutex created in
ut0mem.c */
ut_free_all_mem();
"
I think that, besides you, also someone else has reported the assertion
failure. I have suspected an OS bug but it might also be an InnoDB bug.
Maybe memory corruption.
Did you receive the exact same assertion failure also on x86?
How easy is it to repeat the crash?
I have now added some diagnostic code to InnoDB-4.1.15.
Regards,
Heikki
- Original Message -
From: "David Sparks" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Friday, September 23, 2005 9:03 PM
Subject: daemon crash when shutting down large databases
mysql usually crashes when being shutdown. The machine is a dual AMD64
w 8GB RAM running mysql-4.1.14 on Gentoo linux with a ~40GB database. I
had similar crashes running 4.0.24 on an x86 running a ~275GB database.
I always use `mysqladmin shutdown` rather than the init scripts to
shutdown the daemon.
Are there any known problems with shutting down large databases?
Thanks,
ds
050923 10:41:58 InnoDB: Starting shutdown...
050923 10:44:00InnoDB: Assertion failure in thread 1174235488 in file
os0sync.c line 634
InnoDB: Failing assertion: 0 == pthread_mutex_destroy(fast_mutex)
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.
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=2147483648
read_buffer_size=33550336
max_used_connections=217
max_connections=768
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections
= 77591546 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: daemon crash when shutting down large databases
Hello. Sorry for a delayed answer. Could you provide a whole error message with a resolved stack trace? Check if the problem remains when you switch to the official binaries. David Sparks wrote: > Gleb Paharenko wrote: > >> Hello. >> >> > = 77591546 K >> >> >> Really - something is wrong with your memory settings - MySQL is using >> about 77G of memory (or you have such a cool server :)! Please send >> the output of 'SHOW VARIABLES' statement, 'SHOW STATUS' statement and >> your configuration file. Include the amount of physical memory. > > > Thanks for pointing that out ... the machine only has 8GB ram. I > massaged the config values to get under the 8GB limit but still suffer > the same problem: > > mysqld got signal 11; > ... > key_buffer_size=1073741824 > read_buffer_size=4190208 > max_used_connections=257 > max_connections=256 > threads_connected=0 > It is possible that mysqld could use up to > key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections > = 4193278 K > bytes of memory > Hope that's ok; if not, decrease some variables in the equation. > -->% > > > The server is dual AMD64 w/ 8GB RAM w/ 500GB disk. It has 4 databases, > 1 mostly innodb and 3 myisam. The innodb and one of the myisam dbs see > 100x the usage of the other 2 dbs. If anyone spots any problems in the > my.cnf I'd love to hear about it. :) > > > > Hopefully this isn't line-wrapped into oblivion: > >%-- > [client] > #password = your_password > port= 3306 > socket = > /var/run/mysqld/mysqld.sock > > [mysql] > character-sets-dir=latin1 > default-character-set=latin1 > > [mysqladmin] > character-sets-dir=latin1 > default-character-set=latin1 > > [mysqlcheck] > character-sets-dir=latin1 > default-character-set=latin1 > > [mysqldump] > character-sets-dir=latin1 > default-character-set=latin1 > > [mysqlimport] > character-sets-dir=latin1 > default-character-set=latin1 > > [mysqlshow] > character-sets-dir=latin1 > default-character-set=latin1 > > [myisamchk] > character-sets-dir=latin1 > > [myisampack] > character-sets-dir=latin1 > > # use [safe_mysqld] with mysql-3 > [mysqld_safe] > err-log = /var/log/mysql/mysql.err > > # add a section [mysqld-4.1] or [mysqld-5.0] for specific configurations. > [mysqld] > max_connections = 256 > character-set-server= latin1 > default-character-set = latin1 > user= mysql > port= 3306 > socket = > /var/run/mysqld/mysqld.sock > pid-file= > /var/run/mysqld/mysqld.pid > log-error = /var/log/mysql/mysqld.err > basedir = /usr > datadir = /var/lib/mysql > #skip-locking > #skip-innodb > key_buffer = 1024M > max_allowed_packet = 1M > table_cache = 1556 > sort_buffer_size= 8M > net_buffer_length = 64K > read_buffer_size= 4M > read_rnd_buffer_size= 1M > myisam_sort_buffer_size = 8M > language= /usr/share/mysql/english > myisam_data_pointer_size= 6 > > # daves: this may not quite work ... > join_buffer_size= 8M > long_query_time = 10 > log-long-format > log-slow-queries > max_binlog_size = 10G > binlog_cache_size = 1M > expire_logs_days= 1 > tmp_table_size = 64M > > query_cache_limit = 1M > query_cache_size= 64M > query_cache_type= 1 > > > #security: > #using "localhost" in connects use sockets by default > #skip-networking > #bind-address = 127.0.0.1 > > log-bin > server-id = 1 > > # Point the following paths to different dedicated disks > tmpdir = /tmp/ > #log-update = > /path-to-dedicated-directory/hostname > > # you need debug use flag enabled to use this ones. > # if needed uncomment them, start the server and issue > # #tail -f /tmp/mysqld.sql /tmp/mysqld.trace > # this will show you *exactly* what's appening in your server ;) > > #log= /tmp/mysql
Re: daemon crash when shutting down large databases
Gleb Paharenko wrote: >Hello. > > > = 77591546 K > > >Really - something is wrong with your memory settings - MySQL is using >about 77G of memory > Unfortunately getting the daemon to not go above the theoretical limit has tanked performance. In reality I never see the daemon go above 45% RAM usage when using the settings that can theoretically go to 77GB RAM usage. What if I added a 80GB swap file? Would this not make sure there is available RAM if the daemon really needs it and eliminate all memory exhaustion cases from the crash? Cheers, ds > (or you have such a cool server :)! Please send the >output of 'SHOW VARIABLES' statement, 'SHOW STATUS' statement and your >configuration file. Include the amount of physical memory. > > > >David Sparks wrote: > > >>mysql usually crashes when being shutdown. The machine is a dual AMD64 >>w 8GB RAM running mysql-4.1.14 on Gentoo linux with a ~40GB database. I >>had similar crashes running 4.0.24 on an x86 running a ~275GB database. >> >>I always use `mysqladmin shutdown` rather than the init scripts to >>shutdown the daemon. >> >>Are there any known problems with shutting down large databases? >> >>Thanks, >> >>ds >> >>050923 10:41:58 InnoDB: Starting shutdown... >>050923 10:44:00InnoDB: Assertion failure in thread 1174235488 in file >>os0sync.c line 634 >>InnoDB: Failing assertion: 0 == pthread_mutex_destroy(fast_mutex) >>InnoDB: We intentionally generate a memory trap. >>InnoDB: Submit a detailed bug report to http://bugs.mysql.com. >>InnoDB: If you get repeated assertion failures or crashes, even >>InnoDB: immediately after the mysqld startup, there may be >>InnoDB: corruption in the InnoDB tablespace. Please refer to >>InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html >>InnoDB: about forcing recovery. >>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=2147483648 >>read_buffer_size=33550336 >>max_used_connections=217 >>max_connections=768 >>threads_connected=0 >>It is possible that mysqld could use up to >>key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections >>= 77591546 K >>bytes of memory >>Hope that's ok; if not, decrease some variables in the equation. >> >> >> >> > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: daemon crash when shutting down large databases
I forgot to include the output of show variables and show status in the last message :( mysql> show variables\G *** 1. row *** Variable_name: back_log Value: 50 *** 2. row *** Variable_name: basedir Value: /usr/ *** 3. row *** Variable_name: binlog_cache_size Value: 1048576 *** 4. row *** Variable_name: bulk_insert_buffer_size Value: 8388608 *** 5. row *** Variable_name: character_set_client Value: latin1 *** 6. row *** Variable_name: character_set_connection Value: latin1 *** 7. row *** Variable_name: character_set_database Value: latin1 *** 8. row *** Variable_name: character_set_results Value: latin1 *** 9. row *** Variable_name: character_set_server Value: latin1 *** 10. row *** Variable_name: character_set_system Value: utf8 *** 11. row *** Variable_name: character_sets_dir Value: /usr/share/mysql/charsets/ *** 12. row *** Variable_name: collation_connection Value: latin1_swedish_ci *** 13. row *** Variable_name: collation_database Value: latin1_swedish_ci *** 14. row *** Variable_name: collation_server Value: latin1_swedish_ci *** 15. row *** Variable_name: concurrent_insert Value: ON *** 16. row *** Variable_name: connect_timeout Value: 5 *** 17. row *** Variable_name: datadir Value: /var/lib/mysql/ *** 18. row *** Variable_name: date_format Value: %Y-%m-%d *** 19. row *** Variable_name: datetime_format Value: %Y-%m-%d %H:%i:%s *** 20. row *** Variable_name: default_week_format Value: 0 *** 21. row *** Variable_name: delay_key_write Value: ON *** 22. row *** Variable_name: delayed_insert_limit Value: 100 *** 23. row *** Variable_name: delayed_insert_timeout Value: 300 *** 24. row *** Variable_name: delayed_queue_size Value: 1000 *** 25. row *** Variable_name: expire_logs_days Value: 1 *** 26. row *** Variable_name: flush Value: OFF *** 27. row *** Variable_name: flush_time Value: 0 *** 28. row *** Variable_name: ft_boolean_syntax Value: + -><()~*:""&| *** 29. row *** Variable_name: ft_max_word_len Value: 84 *** 30. row *** Variable_name: ft_min_word_len Value: 4 *** 31. row *** Variable_name: ft_query_expansion_limit Value: 20 *** 32. row *** Variable_name: ft_stopword_file Value: (built-in) *** 33. row *** Variable_name: group_concat_max_len Value: 1024 *** 34. row *** Variable_name: have_archive Value: NO *** 35. row *** Variable_name: have_bdb Value: NO *** 36. row *** Variable_name: have_blackhole_engine Value: NO *** 37. row *** Variable_name: have_compress Value: YES *** 38. row *** Variable_name: have_crypt Value: YES *** 39. row *** Variable_name: have_csv Value: NO *** 40. row *** Variable_name: have_example_engine Value: NO *** 41. row *** Variable_name: have_geometry Value: NO *** 42. row *
Re: daemon crash when shutting down large databases
Gleb Paharenko wrote: Hello. > = 77591546 K Really - something is wrong with your memory settings - MySQL is using about 77G of memory (or you have such a cool server :)! Please send the output of 'SHOW VARIABLES' statement, 'SHOW STATUS' statement and your configuration file. Include the amount of physical memory. Thanks for pointing that out ... the machine only has 8GB ram. I massaged the config values to get under the 8GB limit but still suffer the same problem: mysqld got signal 11; ... key_buffer_size=1073741824 read_buffer_size=4190208 max_used_connections=257 max_connections=256 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 4193278 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. -->% The server is dual AMD64 w/ 8GB RAM w/ 500GB disk. It has 4 databases, 1 mostly innodb and 3 myisam. The innodb and one of the myisam dbs see 100x the usage of the other 2 dbs. If anyone spots any problems in the my.cnf I'd love to hear about it. :) Hopefully this isn't line-wrapped into oblivion: >%-- [client] #password = your_password port= 3306 socket = /var/run/mysqld/mysqld.sock [mysql] character-sets-dir=latin1 default-character-set=latin1 [mysqladmin] character-sets-dir=latin1 default-character-set=latin1 [mysqlcheck] character-sets-dir=latin1 default-character-set=latin1 [mysqldump] character-sets-dir=latin1 default-character-set=latin1 [mysqlimport] character-sets-dir=latin1 default-character-set=latin1 [mysqlshow] character-sets-dir=latin1 default-character-set=latin1 [myisamchk] character-sets-dir=latin1 [myisampack] character-sets-dir=latin1 # use [safe_mysqld] with mysql-3 [mysqld_safe] err-log = /var/log/mysql/mysql.err # add a section [mysqld-4.1] or [mysqld-5.0] for specific configurations. [mysqld] max_connections = 256 character-set-server= latin1 default-character-set = latin1 user= mysql port= 3306 socket = /var/run/mysqld/mysqld.sock pid-file= /var/run/mysqld/mysqld.pid log-error = /var/log/mysql/mysqld.err basedir = /usr datadir = /var/lib/mysql #skip-locking #skip-innodb key_buffer = 1024M max_allowed_packet = 1M table_cache = 1556 sort_buffer_size= 8M net_buffer_length = 64K read_buffer_size= 4M read_rnd_buffer_size= 1M myisam_sort_buffer_size = 8M language= /usr/share/mysql/english myisam_data_pointer_size= 6 # daves: this may not quite work ... join_buffer_size= 8M long_query_time = 10 log-long-format log-slow-queries max_binlog_size = 10G binlog_cache_size = 1M expire_logs_days= 1 tmp_table_size = 64M query_cache_limit = 1M query_cache_size= 64M query_cache_type= 1 #security: #using "localhost" in connects use sockets by default #skip-networking #bind-address = 127.0.0.1 log-bin server-id = 1 # Point the following paths to different dedicated disks tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # you need debug use flag enabled to use this ones. # if needed uncomment them, start the server and issue # #tail -f /tmp/mysqld.sql /tmp/mysqld.trace # this will show you *exactly* what's appening in your server ;) #log= /tmp/mysqld.sql #gdb #debug = d:t:i:o,/tmp/mysqld.trace #one-thread # Uncomment the following if you are using BDB tables #bdb_cache_size = 4M #bdb_max_lock = 1 # The following is the InnoDB configuration # if you wish to disable innodb instead # uncomment just the next line #skip-innodb # # the rest of the innodb config follows: # don't eat too much memory, we're trying to be safe on 64Mb boxes. # you might want to bump this up a bit on boxes with more RAM innodb_buffer_pool_size = 1024M # this is the default, increase if you have lo
Re: daemon crash when shutting down large databases
Hello. > = 77591546 K Really - something is wrong with your memory settings - MySQL is using about 77G of memory (or you have such a cool server :)! Please send the output of 'SHOW VARIABLES' statement, 'SHOW STATUS' statement and your configuration file. Include the amount of physical memory. David Sparks wrote: > mysql usually crashes when being shutdown. The machine is a dual AMD64 > w 8GB RAM running mysql-4.1.14 on Gentoo linux with a ~40GB database. I > had similar crashes running 4.0.24 on an x86 running a ~275GB database. > > I always use `mysqladmin shutdown` rather than the init scripts to > shutdown the daemon. > > Are there any known problems with shutting down large databases? > > Thanks, > > ds > > 050923 10:41:58 InnoDB: Starting shutdown... > 050923 10:44:00InnoDB: Assertion failure in thread 1174235488 in file > os0sync.c line 634 > InnoDB: Failing assertion: 0 == pthread_mutex_destroy(fast_mutex) > InnoDB: We intentionally generate a memory trap. > InnoDB: Submit a detailed bug report to http://bugs.mysql.com. > InnoDB: If you get repeated assertion failures or crashes, even > InnoDB: immediately after the mysqld startup, there may be > InnoDB: corruption in the InnoDB tablespace. Please refer to > InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html > InnoDB: about forcing recovery. > 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=2147483648 > read_buffer_size=33550336 > max_used_connections=217 > max_connections=768 > threads_connected=0 > It is possible that mysqld could use up to > key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections > = 77591546 K > bytes of memory > Hope that's ok; if not, decrease some variables in the equation. > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
daemon crash when shutting down large databases
mysql usually crashes when being shutdown. The machine is a dual AMD64 w 8GB RAM running mysql-4.1.14 on Gentoo linux with a ~40GB database. I had similar crashes running 4.0.24 on an x86 running a ~275GB database. I always use `mysqladmin shutdown` rather than the init scripts to shutdown the daemon. Are there any known problems with shutting down large databases? Thanks, ds 050923 10:41:58 InnoDB: Starting shutdown... 050923 10:44:00InnoDB: Assertion failure in thread 1174235488 in file os0sync.c line 634 InnoDB: Failing assertion: 0 == pthread_mutex_destroy(fast_mutex) InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. 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=2147483648 read_buffer_size=33550336 max_used_connections=217 max_connections=768 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 77591546 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
