After moving the database from Linux 2.4 to FreeBSD 6.2, the mysqld crash very frequently! I think the problem is on FreeBSD when myusql is heaving loading. I have another same machine with lower loading, the mysql is stable. ===== 8< =============== Version: '5.0.37-log' socket: '/tmp/mysql2.sock' port: 3307 MySQL Community Server (GPL) 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=402653184 read_buffer_size=2093056 max_used_connections=69 max_connections=130 threads_connected=53 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections 1191414 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. ===== 8< =============== db1# limits Resource limits (current): cputime infinity secs filesize infinity kB datasize 2621440 kB stacksize 524288 kB coredumpsize infinity kB memoryuse infinity kB memorylocked infinity kB maxprocesses 5547 openfiles 11095 sbsize infinity bytes vmemoryuse infinity kB db1# ===== 8< =============== The configuration in my.cnf: [mysqld2] port = 3307 socket = /tmp/mysql2.sock character-set-server = utf8 datadir = /db/data2 max_connections = 130 interactive_timeout = 20 nice=-15 thread_concurrency = 8 skip-locking key_buffer = 256M max_allowed_packet = 1M table_cache = 400 sort_buffer_size = 4M read_buffer_size = 2M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 32 query_cache_size= 64M join_buffer_size = 4M server-id = 233 log-bin = mysql-bin skip-bdb innodb_data_home_dir = /db/data2/ innodb_data_file_path = ibdata1:2G:autoextend innodb_buffer_pool_size = 512M innodb_additional_mem_pool_size = 8M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 64M innodb_log_buffer_size = 6M ##innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 30 replicate-ignore-table=friend.SendVKiss replicate-wild-ignore-table=friend.fc_% replicate-wild-ignore-table=friend._search_result_% ===== 8< =============== Last, the FreeBSD boot message: Copyright (c) 1992-2007 The FreeBSD Project. Copyright (c) 1979, 1980, 1983, 1986, 1988, 1989, 1991, 1992, 1993, 1994 The Regents of the University of California. All rights reserved. FreeBSD is a registered trademark of The FreeBSD Foundation. FreeBSD 6.2-RELEASE #0: Thu Apr 26 15:13:29 CST 2007 root@db3.topfong.com:/usr/src/sys/i386/compile/db3 Timecounter "i8254" frequency 1193182 Hz quality 0 CPU: Intel(R) Xeon(TM) CPU 2.80GHz (2791.78-MHz 686-class CPU) Origin = "GenuineIntel" Id = 0xf27 Stepping = 7 Features=0xbfebfbff<FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,CLFLUSH,DTS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,PBE> real memory = 4160684032 (3967 MB) avail memory = 4078329856 (3889 MB) ACPI APIC Table: <A M I OEMAPIC > FreeBSD/SMP: Multiprocessor System Detected: 2 CPUs cpu0 (BSP): APIC ID: 0 cpu1 (AP): APIC ID: 6 ioapic0 <Version 2.0> irqs 0-23 on motherboard ioapic1 <Version 2.0> irqs 24-47 on motherboard ioapic2 <Version 2.0> irqs 48-71 on motherboard ioapic3 <Version 2.0> irqs 72-95 on motherboard ioapic4 <Version 2.0> irqs 96-119 on motherboard kbd1 at kbdmux0 acpi0: <A M I OEMRSDT> on motherboard acpi0: Power Button (fixed) Timecounter "ACPI-fast" frequency 3579545 Hz quality 1000 acpi_timer0: <24-bit timer at 3.579545MHz> port 0x408-0x40b on acpi0 cpu0: <ACPI CPU> on acpi0 acpi_throttle0: <ACPI CPU Throttling> on cpu0 cpu1: <ACPI CPU> on acpi0
On Tue, May 01, 2007 at 09:11:28AM +0800, Ken Chen wrote:> After moving the database from Linux 2.4 to FreeBSD 6.2, the mysqld crash > very frequently! I think the problem is on FreeBSD when myusql is heaving > loading. > > I have another same machine with lower loading, the mysql is stable.I can confirm this problem. Normally sig11 is an indication that you have hardware-related problems, but in this particular case (at least in my experience), it can also be caused by some lack-of loader.conf tunables permitting mysqld to allocate the amount of memory you're claiming in my.cnf. My loader.conf comments may not be absolutely correct (folks who know the innards of the VM will probably correct me in my claims), but I can confirm that increasing kern.maxdsiz/dfldsiz/maxssiz relieved all bizarre sig11 issues we were seeing. I'll use our production SQL server as an example: # dmesg | grep ' memory' real memory = 1073676288 (1023 MB) avail memory = 1041801216 (993 MB) # top -d 1 -U mysql Mem: 207M Active, 598M Inact, 138M Wired, 42M Cache, 111M Buf, 11M Free Swap: 8192M Total, 16K Used, 8192M Free PID USERNAME THR PRI NICE SIZE RES STATE TIME WCPU COMMAND 914 mysql 6 20 0 754M 173M kserel 31:54 0.00% mysqld 877 mysql 1 8 0 1812K 900K wait 0:00 0.00% sh # pkg_info | grep ^mysql mysql-client-5.0.27 Multithreaded SQL database (client) mysql-server-5.0.27 Multithreaded SQL database (server) (The mysql ports were not build with any special options; all were literally their defaults.) # mysqladmin version | grep 'Server version' Server version 5.0.27 /boot/loader.conf : # Increase maximum allocatable memory on a process to 768MB. # (We don't choose 1GB (our max RAM) since that would exhaust # all memory, and result in a kernel panic.) # Set default memory size as 768MB. # Maximum stack size is 128MB. # kern.maxdsiz="768M" kern.dfldsiz="768M" kern.maxssiz="128M" my.cnf : set-variable = tmp_table_size=64M set-variable = max_allowed_packet=32M set-variable = table_cache=256 set-variable = key_buffer_size=64M set-variable = join_buffer_size=8M set-variable = sort_buffer_size=8M set-variable = read_buffer_size=8M set-variable = query_cache_size=64M set-variable = query_cache_limit=32M set-variable = innodb_buffer_pool_size=512M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_file_size=128M set-variable = innodb_log_buffer_size=8M The my.cnf settings you see above were chosen somewhat carefully. Initially I had all the *_buffer_size variables set to something much higher, but after coming across some tuning documentation on MySQL's site, I read that there's actually a "sweet spot" as far as what you set the sizes to vs. how much memory you have. Anyways, I hope this helps. If not, I would definitely recommend running memtest86 (not a 100% failsafe way to test memory, but it usually catches obvious things) and/or swapping out some hardware. -- | Jeremy Chadwick jdc at parodius.com | | Parodius Networking http://www.parodius.com/ | | UNIX Systems Administrator Mountain View, CA, USA | | Making life hard for others since 1977. PGP: 4BD6C0CB |
Greetings everyone... I am observing what appears to be a similar problem as well... I have a dedicated server with *very* light traffic. It looks like every day or so my server load slowly climbs from 0.20 to 14.00 and then stays there indefinitely.... locking down the system [until I do an "apachectl restart"] after which the server load drops back down. I'm not getting any weird Fatal Errors from PHP or any Warnings [though a lot of PHP Notices.]. The apache httpd_error.log shows the following error, usually, when the slow climb to 14.00 server load begins: httpd in free(): error: recursive call httpd in free(): error: recursive call [Usually it only shows about three or four of these repeated errors.] I am using FreeBSD 6.2 and MySQL 4.1. I am trying to use the libthr threading mechanism through the libmap.conf setting, as to earlier in this thread post, as a possible fix. [Though, I don't know if I have in fact been successful in switching to libthr or not... because I'm not sure if I need to recompile / reboot? I don't know if mysql was install from a port or not.] In any event, my libmap.conf settings are now [located in /etc/libmap.conf]: [mysqld] libc_r.so libthr.so libc_r.so.6 libthr.so.2 libthr.so.2 libthr.so.2 libpthread.so libthr.so libpthread.so.2 libthr.so.2 and I also added ... WITH_LIBMAP= yes to my make.conf file. Is there something else I need to do [e.g., recompile? / reboot?] in order to activate libthr? The problem remains even with these adjustments. Blessings, Albert Wong www.ithou.org PS. The mysql_logfile is completely empty... I don't know if that is unusual or not. PPS. Here's the my.cnf settings for this machine: [mysqld] safe-show-database skip-innodb max_connections = 500 key_buffer = 32M myisam_sort_buffer_size = 64M join_buffer_size = 1M read_buffer_size = 1M sort_buffer_size = 2M table_cache = 1800 thread_cache_size = 384 wait_timeout = 90 connect_timeout = 10 tmp_table_size = 64M max_heap_table_size = 64M max_allowed_packet = 16M max_connect_errors = 10 read_rnd_buffer_size = 524288 bulk_insert_buffer_size = 8M query_cache_limit = 3M query_cache_size = 80M query_cache_type = 1 query_prealloc_size = 163840 query_alloc_block_size = 32768 skip-name-resolve [mysqld_safe] open_files_limit = 8192 [mysqldump] quick max_allowed_packet = 16M [myisamchk] key_buffer = 16M sort_buffer = 16M read_buffer = 16M write_buffer = 16M [mysqlhotcopy] interactive-timeout log = /var/log/mysql/mysql_logfile
Greetings everyone... I am observing what appears to be a similar problem as well... [referring to the posts from earlier this month with the same subject]... I have a dedicated server with *very* light traffic. It looks like every day or so my server load slowly climbs from 0.20 to 14.00 and then stays there indefinitely.... locking down the system [until I do an "apachectl restart"] after which the server load drops back down. I'm not getting any weird Fatal Errors from PHP or any Warnings [though a lot of PHP Notices.]. The apache httpd_error.log shows the following error, usually, when the slow climb to 14.00 server load begins: httpd in free(): error: recursive call httpd in free(): error: recursive call [Usually it only shows about three or four of these repeated errors.] I am using FreeBSD 6.2 and MySQL 4.1. I am trying to use the libthr threading mechanism through the libmap.conf setting, as to earlier in this thread post, as a possible fix. [Though, I don't know if I have in fact been successful in switching to libthr or not... because I'm not sure if I need to recompile / reboot? I don't know if mysql was install from a port or not.] In any event, my libmap.conf settings are now [located in /etc/libmap.conf]: [mysqld] libc_r.so libthr.so libc_r.so.6 libthr.so.2 libthr.so.2 libthr.so.2 libpthread.so libthr.so libpthread.so.2 libthr.so.2 and I also added ... WITH_LIBMAP= yes to my make.conf file. Is there something else I need to do [e.g., recompile? / reboot?] in order to activate libthr? The problem remains even with these adjustments. Blessings, Albert Wong www.ithou.org PS. The mysql_logfile is completely empty... I don't know if that is unusual or not. PPS. Here's the my.cnf settings for this machine: [mysqld] safe-show-database skip-innodb max_connections = 500 key_buffer = 32M myisam_sort_buffer_size = 64M join_buffer_size = 1M read_buffer_size = 1M sort_buffer_size = 2M table_cache = 1800 thread_cache_size = 384 wait_timeout = 90 connect_timeout = 10 tmp_table_size = 64M max_heap_table_size = 64M max_allowed_packet = 16M max_connect_errors = 10 read_rnd_buffer_size = 524288 bulk_insert_buffer_size = 8M query_cache_limit = 3M query_cache_size = 80M query_cache_type = 1 query_prealloc_size = 163840 query_alloc_block_size = 32768 skip-name-resolve [mysqld_safe] open_files_limit = 8192 [mysqldump] quick max_allowed_packet = 16M [myisamchk] key_buffer = 16M sort_buffer = 16M read_buffer = 16M write_buffer = 16M [mysqlhotcopy] interactive-timeout log = /var/log/mysql/mysql_logfile
Yes... this is possible.... Currently, I am on MySQL 4.1.20 ... but I believe that MySQL is backwards compatible... so I can definitely try this. Do you know if this bug is squashed with MySQL 5.0.37 ? Thanks, Albert> -----Original Message----- > From: Abdullah Ibn Hamad Al-Marri [mailto:almarrie@gmail.com] > Sent: Thursday, May 17, 2007 1:17 PM > To: Albert Wong > Cc: freebsd-stable@freebsd.org > Subject: Re: mysql frequently crash on 6.2 > > On 5/17/07, Albert Wong <ajwwong@gmail.com> wrote: > > > <snip> > > Can you go for MySQL 5.0.37? > > -- > Regards, > > -Abdullah Ibn Hamad Al-Marri > Arab Portal > http://www.WeArab.Net/
On 5/17/07, Albert Wong <ajwwong@gmail.com> wrote:> Yes... this is possible.... Currently, I am on MySQL 4.1.20 ... but I > believe that MySQL is backwards compatible... so I can definitely try this. > > Do you know if this bug is squashed with MySQL 5.0.37 ? > > Thanks, > Albert > > > -----Original Message----- > > From: Abdullah Ibn Hamad Al-Marri [mailto:almarrie@gmail.com] > > Sent: Thursday, May 17, 2007 1:17 PM > > To: Albert Wong > > Cc: freebsd-stable@freebsd.org > > Subject: Re: mysql frequently crash on 6.2 > > > > On 5/17/07, Albert Wong <ajwwong@gmail.com> wrote: > > > > > <snip> > > > > Can you go for MySQL 5.0.37? > > > > -- > > Regards, > > > > -Abdullah Ibn Hamad Al-Marri > > Arab Portal > > http://www.WeArab.Net/I run MySQL 5.0.x since ages with heavily load in UP and SMP boxes with libthr and they never crashed at all. -- Regards, -Abdullah Ibn Hamad Al-Marri Arab Portal http://www.WeArab.Net/
On 5/17/07, Albert Wong <ajwwong@gmail.com> wrote:><snip> Can you go for MySQL 5.0.37? -- Regards, -Abdullah Ibn Hamad Al-Marri Arab Portal http://www.WeArab.Net/
Hello
In general This is what I use for my libmap.conf , it works fine
for me any you may want to give it a try.
[mysqld]
libpthread.so libthr.so
libpthread.so.2 libthr.so.2
Note nothing in MySQL 4.1 compiled on FreeBSD 6.2 should use libc_r.so .
The other thing in your libmap that looks odd is the line "libthr.so.2
libthr.so.2" not sure what that will do.
>
> In any event, my libmap.conf settings are now [located in
/etc/libmap.conf]:
>
>
> [mysqld]
> libc_r.so libthr.so
> libc_r.so.6 libthr.so.2
> libthr.so.2 libthr.so.2
> libpthread.so libthr.so
> libpthread.so.2 libthr.so.2
One thing I normally turn off in MySQL is the query_cache . If your DB
is constantly receiving updates the query_cache is flushed to with each
update. This creates alot of useless overhead, in fact if set it very
high like to 1G or so you will see some nasty side effects of how MySQL
tries to flush the cache.
As for you log you may want to move that in to the "[mysqld]" section
of
the config. In this setup I beleive that mysqlhotcopy would be the only
mysql* command using that directive.
table_cache should not be greater the amount of tables your databases
have .
read_rnd_buffer_size = 524288 looks way to high your key_buffer_size is
only 32M Maybe you want to lower this to 64M of even leave it undefined
to see what the default does for you.
> PPS. Here's the my.cnf settings for this machine:
>
> [mysqld]
> safe-show-database
> skip-innodb
> max_connections = 500
> key_buffer = 32M
> myisam_sort_buffer_size = 64M
> join_buffer_size = 1M
> read_buffer_size = 1M
> sort_buffer_size = 2M
> table_cache = 1800
> thread_cache_size = 384
> wait_timeout = 90
> connect_timeout = 10
> tmp_table_size = 64M
> max_heap_table_size = 64M
> max_allowed_packet = 16M
> max_connect_errors = 10
> read_rnd_buffer_size = 524288
> bulk_insert_buffer_size = 8M
> query_cache_limit = 3M
> query_cache_size = 80M
> query_cache_type = 1
> query_prealloc_size = 163840
> query_alloc_block_size = 32768
> skip-name-resolve
>
> [mysqld_safe]
> open_files_limit = 8192
>
> [mysqldump]
> quick
> max_allowed_packet = 16M
>
> [myisamchk]
> key_buffer = 16M
> sort_buffer = 16M
> read_buffer = 16M
> write_buffer = 16M
>
> [mysqlhotcopy]
> interactive-timeout
>
> log = /var/log/mysql/mysql_logfile
--
Mark Saad
Hello Albert, Hello @list, first, i be a native german speaker, so please excuse my ugly english. Greetings everyone... I am observing what appears to be a similar problem as> well... > > I have a dedicated server with *very* light traffic. It looks like every > day > or so my server load slowly climbs from 0.20 to 14.00 and then stays there > indefinitely.... locking down the system [until I do an "apachectl > restart"] > after which the server load drops back down. > > I'm not getting any weird Fatal Errors from PHP or any Warnings [though a > lot of PHP Notices.]. The apache httpd_error.log shows the following > error, > usually, when the slow climb to 14.00 server load begins: > > httpd in free(): error: recursive call > httpd in free(): error: recursive call > > [Usually it only shows about three or four of these repeated errors.] >----------8<---snip---8<----------- I have experiences with an similar Problem with apache in combination with php4 and it's modules. If you have the wrong modules / order of modules in the extensions.ini so that apache disappears with segfault ( my be signal 6 or 11 ) if you try an reload. hope this helps a little bit. greetings michael -- === michael-schuh.net ==Michael Schuh Preu?enstr. 13 66111 Saarbr?cken phone: 0681/8319664 mobil: 0177/9738644 @: michael.schuh@gmail.com === Ust-ID: DE251072318 ===