FreeBSD 5.3 SMP freezes with MySQL 4.1

[Michael Vince]

   I am running 1 mildly busy new MySQL server thats running fine on
   5.3-RELEASE-p5 #5: Sat Jan 22 04:54:07 EST 2005 from the generic conf
   kernel
   Its a Dell 1850 Dual P4 Xeon CPU 3.00GHz EMT64 with HTT enabled
   FYI I actually have a Dell 2650 thats not doing anything at the moment
   because it had sluggish performance when I started  to put some
   serious burden on it.
   I recently updated to the latest MySQL to 4.1.10a from 4.1.5 with
   using this set of settings (I hate using ports manually)
   portupgrade -Rfri  -m 'BUILD_OPTIMIZED=yes BUILD_STATIC=yes'
   /var/db/pkg/mysql-server-4.1*
   I copied the default large.cnf file to /var/db/mysql/my.cnf for better
   performance but thats about it, I am still evaluating MySQL
   performance.

   To give you a remote idea how busy this MySQL server is, here are some
   bits running "mysqladmin extended-status"
   | Bytes_received           | 49227436   |
   | Bytes_sent               | 71933703   |
   | Threads_connected        | 25         |
   | Threads_created          | 42         |
   | Uptime                   | 101775     |
   According to MySQL manual Threads_created gives an idea of the load on
   the MySQL server.
   phpMyAdmin lists MySQL status in a much nicer way
   This MySQL server has been running for 1 days, 4 hours, 38 minutes and
   28 seconds.
   Query statistics: Since its startup, 335,544 queries have been sent to
   the server.

    Total     ø per hour   ø per minute   ø per second
    335,544    11,715.47         195.26           3.25

   select   204,621   7,144.31   61.04 %

   insert   29,149   1,017.73   8.70 %

    show keys   85,395   2,981.55   25.48 %

   This server is doing more things then I originally planned it to do,
   its also running a Postgres 7.4 server that has over 500megs of data
   and almost constant 100% usage of disk IO according to top via "m", I
   have statistics enabled on postgres but no way to show some simple
   summaries.
   I run Apache2 in prefork mode and currently has around 350 average
   apache daemons
   ps -auxww | grep -c httpd
   356
   Its doing over 1 million dynamic page loads a day (some page loads
   don't use database)
   This server also is running 12 separate Java processes each at around
   200megs of size.
   Since cvsuping to the latest 5_3 for release security patches and
   critical updates the server is been perfectly stable, before that I
   did have kernel panic reboot problems that I believe were caused be
   massive thread usage from the java processes.
   Although I have rebooted just a little while ago the servers uptime is
   currently 33days.
   With your server how have you been updating your server to 5.3-P5
   release? Its possible you have a similar problem.
   I am emailing this in HTML format in the hope the tables come out more
   nicely.
   Regards,
   Mike
   Young Lee wrote:

I have try your solution yesterday, so far it is stable, and will
observe the stability for some days. btw, i turn "debug.mpsafenet=0" 
in /boot/loader.conf to evade the possible network stack deadlock 
under SMP.